ORM映射框架总结--Excel 操作

　　在很多时候，我们需要将查询的数据做成报表统计，然后生成Excel文档格式的。再此提供了将DataTable 数据导出excel 的方法

 

  1 /**
  2  * 
  3  * 2009-5-2
  4  * 
  5  * 
  6  * 将DataTable导出为excel文件
  7  * */
  8 using System;
  9 using System.Collections.Generic;
 10 using System.Linq;
 11 using System.Text;
 12 using System.Data;
 13 using System.Web;
 14 using System.Web.Security;
 15 using System.Web.UI;
 16 using System.Web.UI.HtmlControls;
 17 using System.Web.UI.WebControls;
 18 using System.Web.UI.WebControls.WebParts;
 19 using System.IO;
 20 using Excel=Microsoft.Office.Interop.Excel;
 21 
 22 namespace CommonData.Application
 23 {
 24     public class DataExcel
 25     {
 26         /// <summary>
 27         /// datatable导出为excel
 28         /// </summary>
 29         /// <param name="table">table 实例</param>
 30         public void ExportExcelStream(DataTable table, string filepath)
 31         {
 32             StringWriter stringWriter = new StringWriter();
 33             HtmlTextWriter htmlWriter = new HtmlTextWriter(stringWriter);
 34             DataGrid excel = new DataGrid();
 35             System.Web.UI.WebControls.TableItemStyle AlternatingStyle = new TableItemStyle();
 36             System.Web.UI.WebControls.TableItemStyle headerStyle = new TableItemStyle();
 37             System.Web.UI.WebControls.TableItemStyle itemStyle = new TableItemStyle();
 38             AlternatingStyle.BackColor = System.Drawing.Color.LightGray;
 39             headerStyle.BackColor = System.Drawing.Color.LightGray;
 40             headerStyle.Font.Bold = true;
 41             headerStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center;
 42             itemStyle.HorizontalAlign = System.Web.UI.WebControls.HorizontalAlign.Center; ;
 43 
 44             excel.AlternatingItemStyle.MergeWith(AlternatingStyle);
 45             excel.HeaderStyle.MergeWith(headerStyle);
 46             excel.ItemStyle.MergeWith(itemStyle);
 47             excel.GridLines = GridLines.Both;
 48             excel.HeaderStyle.Font.Bold = true;
 49             excel.DataSource = table.DefaultView;   //输出DataTable的内容
 50             excel.DataBind();
 51             excel.RenderControl(htmlWriter);
 52 
 53             string filestr = filepath;
 54             int pos = filestr.LastIndexOf("\\");
 55             string file = filestr.Substring(0, pos);
 56             if (!Directory.Exists(file))
 57             {
 58                 Directory.CreateDirectory(file);
 59             }
 60             System.IO.StreamWriter sw = new StreamWriter(filestr);
 61             sw.Write(stringWriter.ToString());
 62             sw.Close();
 63         }
 64 
 65         /// <summary>
 66         /// 将DataTable 导出为excel文件格式
 67         /// </summary>
 68         /// <param name="table">DataTable</param>
 69         /// <param name="filepath">保存文件路径</param>
 70         public void ExportExcelOffice(DataTable table, string filepath)
 71         {
 72             
 73             Excel.Application excel = new Microsoft.Office.Interop.Excel.Application();
 74             excel.Visible = false;
 75             object ms = Type.Missing;
 76             Excel.Workbook wk = excel.Workbooks.Add(ms);
 77             Excel.Worksheet ws = wk.Worksheets[1] as Excel.Worksheet;
 78             for(int i=0;i<table.Columns.Count;i++)
 79             {
 80                 ws.Cells[1, i + 1] = table.Columns[i].ColumnName;
 81             }
 82             for(int i=0;i<table.Rows.Count;i++)
 83             {
 84                 for(int j=0;j<table.Columns.Count;j++)
 85                 {
 86                     ws.Cells[i + 2, j + 1] = table.Rows[i][j].ToString();
 87                 }
 88             }
 89 
 90             if(File.Exists(filepath)==false)
 91             {
 92                 Directory.CreateDirectory(filepath);
 93             }
 94             wk.SaveAs(filepath, ms, ms, ms, ms, ms, Excel.XlSaveAsAccessMode.xlShared, ms, ms, ms, ms, ms);
 95             excel.Quit();
 96             
 97         }
 98     }
 99 }
100 
101 

 

 

　　这里提供了两种方式来导出DataTable 中的数据。第一种是采用流的方式写入，第二种是系统提供的Office操作API，这个可以很好的操作Office文档。

　　DataTable table  是要导出Excel 文件的数据源

　　string filepath    是文件要保存的路径