在很多时候,我们需要将查询的数据做成报表统计,然后生成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
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 是文件要保存的路径