文章转自:http://www.cnblogs.com/sufei/archive/2009/05/23/1487540.html
在asp.net中导出Excel有两种方法,一种是将导出的文件存放在服务器某个文件夹下面,然后将文件地址输出在浏览器上;一种是将文件直接将文件输出流写给浏览器。在Response输出时,t分隔的数据,导出Excel时,等价于分列,n等价于换行。
1、将整个html全部输出Excel
此法将html中所有的内容,如按钮,表格,图片等全部输出到Excel中。
2 Response.Buffer = true ;
3 Response.AppendHeader( " Content-Disposition " , " attachment;filename= " + DateTime.Now.ToString( " yyyyMMdd " ) + " .xls " );
4 Response.ContentEncoding = System.Text.Encoding.UTF8;
5 Response.ContentType = " application/vnd.ms-excel " ;
6 this .EnableViewState = false ;
7
8
这里我们利用了ContentType属性,它默认的属性为text/html,这时将输出为超文本,即我们常见的网页格式到客户端,如果改为ms-excel将将输出excel格式,也就是说以电子表格的格式输出到客户端,这时浏览器将提示你下载保存。ContentType的属性还包括:image/JPEG;text/HTML;image/GIF;vnd.ms-excel/msword 。同理,我们也可以输出(导出)图片、word文档等。下面的方法,也均用了这个属性。
2、将DataGrid控件中的数据导出Excel
上述方法虽然实现了导出的功能,但同时把按钮、分页框等html中的所有输出信息导了进去。而我们一般要导出的是数据,DataGrid控件上的数据。
2 // DataGrid1是你在窗体中拖放的控件
3 HttpContext.Current.Response.AppendHeader( " Content-Disposition " , " attachment;filename=Excel.xls " );
4 HttpContext.Current.Response.Charset = " UTF-8 " ;
5 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
6 HttpContext.Current.Response.ContentType = " application/ms-excel " ;
7 ctl.Page.EnableViewState = false ;
8 System.IO.StringWriter tw = new System.IO.StringWriter() ;
9 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
10 ctl.RenderControl(hw);
11 HttpContext.Current.Response.Write(tw.ToString());
12 HttpContext.Current.Response.End();
13
14
如果你的DataGrid用了分页,它导出的是当前页的信息,也就是它导出的是DataGrid中显示的信息。而不是你select语句的全部信息。
为方便使用,写成方法如下:
2 {
3 HttpContext.Current.Response.AppendHeader( " Content-Disposition " , " attachment;filename=Excel.xls " );
4 HttpContext.Current.Response.Charset = " UTF-8 " ;
5 HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default;
6 HttpContext.Current.Response.ContentType = " application/ms-excel " ;
7 ctl.Page.EnableViewState = false ;
8 System.IO.StringWriter tw = new System.IO.StringWriter() ;
9 System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw);
10 ctl.RenderControl(hw);
11 HttpContext.Current.Response.Write(tw.ToString());
12 HttpContext.Current.Response.End();
13 }
14
15
用法:DGToExcel(datagrid1);
3、将DataSet中的数据导出 Excel
有了上边的思路,就是将在导出的信息,输出(Response)客户端,这样就可以导出了。那么把DataSet中的数据导出,也就是把DataSet中的表中的各行信息,以ms-excel的格式Response到http流,这样就OK了。说明:参数ds应为填充有数据表的DataSet,文件名是全名,包括后缀名,如Excel2006.xls
2 {
3 HttpResponse resp;
4 resp = Page.Response;
5 resp.ContentEncoding = System.Text.Encoding.GetEncoding( " GB2312 " );
6 resp.AppendHeader( " Content-Disposition " , " attachment;filename= " + FileName);
7 string colHeaders = "" , ls_item = "" ;
8
9 // 定义表对象与行对象,同时用DataSet对其值进行初始化
10 DataTable dt = ds.Tables[ 0 ];
11 DataRow[] myRow = dt.Select(); // 可以类似dt.Select("id>10")之形式达到数据筛选目的
12 int i = 0 ;
13 int cl = dt.Columns.Count;
14
15 // 取得数据表各列标题,各标题之间以t分割,最后一个列标题后加回车符
16 for (i = 0 ;i < cl;i ++ )
17 {
18 if (i == (cl - 1 )) // 最后一列,加n
19 {
20 colHeaders += dt.Columns[i].Caption.ToString() + " n " ;
21 }
22 else
23 {
24 colHeaders += dt.Columns[i].Caption.ToString() + " t " ;
25 }
26
27 }
28 resp.Write(colHeaders);
29 // 向HTTP输出流中写入取得的数据信息
30
31 // 逐行处理数据
32 foreach (DataRow row in myRow)
33 {
34 // 当前行数据写入HTTP输出流,并且置空ls_item以便下行数据
35 for (i = 0 ;i < cl;i ++ )
36 {
37 if (i == (cl - 1 )) // 最后一列,加n
38 {
39 ls_item += row[i].ToString() + " n " ;
40 }
41 else
42 {
43 ls_item += row[i].ToString() + " t " ;
44 }
45
46 }
47 resp.Write(ls_item);
48 ls_item = "" ;
49
50 }
51 resp.End();
52 }
53
54
若想实现更加富于变化或者行列不规则的excel导出时,可用本法。
2 {
3 // dv为要输出到Excel的数据,str为标题名称
4 GC.Collect();
5 Application excel; // = new Application();
6 int rowIndex = 4 ;
7 int colIndex = 1 ;
8
9 _Workbook xBk;
10 _Worksheet xSt;
11
12 excel = new ApplicationClass();
13
14 xBk = excel.Workbooks.Add( true );
15
16 xSt = (_Worksheet)xBk.ActiveSheet;
17
18 //
19 // 取得标题
20 //
21 foreach (DataColumn col in dv.Table.Columns)
22 {
23 colIndex ++ ;
24 excel.Cells[ 4 ,colIndex] = col.ColumnName;
25 xSt.get_Range(excel.Cells[ 4 ,colIndex],excel.Cells[ 4 ,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; // 设置标题格式为居中对齐
26 }
27
28 //
29 // 取得表格中的数据
30 //
31 foreach (DataRowView row in dv)
32 {
33 rowIndex ++ ;
34 colIndex = 1 ;
35 foreach (DataColumn col in dv.Table.Columns)
36 {
37 colIndex ++ ;
38 if (col.DataType == System.Type.GetType( " System.DateTime " ))
39 {
40 excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString( " yyyy-MM-dd " );
41 xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; // 设置日期型的字段格式为居中对齐
42 }
43 else
44 if (col.DataType == System.Type.GetType( " System.String " ))
45 {
46 excel.Cells[rowIndex,colIndex] = " ' " + row[col.ColumnName].ToString();
47 xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells[rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; // 设置字符型的字段格式为居中对齐
48 }
49 else
50 {
51 excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString();
52 }
53 }
54 }
55 //
56 // 加载一个合计行
57 //
58 int rowSum = rowIndex + 1 ;
59 int colSum = 2 ;
60 excel.Cells[rowSum, 2 ] = " 合计 " ;
61 xSt.get_Range(excel.Cells[rowSum, 2 ],excel.Cells[rowSum, 2 ]).HorizontalAlignment = XlHAlign.xlHAlignCenter;
62 //
63 // 设置选中的部分的颜色
64 //
65 xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select();
66 xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Interior.ColorIndex = 19 ; // 设置为浅黄色,共计有56种
67 //
68 // 取得整个报表的标题
69 //
70 excel.Cells[ 2 , 2 ] = str;
71 //
72 // 设置整个报表的标题格式
73 //
74 xSt.get_Range(excel.Cells[ 2 , 2 ],excel.Cells[ 2 , 2 ]).Font.Bold = true ;
75 xSt.get_Range(excel.Cells[ 2 , 2 ],excel.Cells[ 2 , 2 ]).Font.Size = 22 ;
76 //
77 // 设置报表表格为最适应宽度
78 //
79 xSt.get_Range(excel.Cells[ 4 , 2 ],excel.Cells[rowSum,colIndex]).Select();
80 xSt.get_Range(excel.Cells[ 4 , 2 ],excel.Cells[rowSum,colIndex]).Columns.AutoFit();
81 //
82 // 设置整个报表的标题为跨列居中
83 //
84 xSt.get_Range(excel.Cells[ 2 , 2 ],excel.Cells[ 2 ,colIndex]).Select();
85 xSt.get_Range(excel.Cells[ 2 , 2 ],excel.Cells[ 2 ,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection;
86 //
87 // 绘制边框
88 //
89 xSt.get_Range(excel.Cells[ 4 , 2 ],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1 ;
90 xSt.get_Range(excel.Cells[ 4 , 2 ],excel.Cells[rowSum, 2 ]).Borders[XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick; // 设置左边线加粗
91 xSt.get_Range(excel.Cells[ 4 , 2 ],excel.Cells[ 4 ,colIndex]).Borders[XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick; // 设置上边线加粗
92 xSt.get_Range(excel.Cells[ 4 ,colIndex],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick; // 设置右边线加粗
93 xSt.get_Range(excel.Cells[rowSum, 2 ],excel.Cells[rowSum,colIndex]).Borders[XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick; // 设置下边线加粗
94 //
95 // 显示效果
96 //
97 excel.Visible = true ;
98
99 // xSt.Export(Server.MapPath(".")+""+this.xlfile.Text+".xls",SheetExportActionEnum.ssExportActionNone,Microsoft.Office.Interop.OWC.SheetExportFormat.ssExportHTML);
100 xBk.SaveCopyAs(Server.MapPath( " . " ) + "" + this .xlfile.Text + " .xls " );
101
102 ds = null ;
103 xBk.Close( false , null , null );
104
105 excel.Quit();
106 System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);
107 System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);
108 System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);
109 xBk = null ;
110 excel = null ;
111 xSt = null ;
112 GC.Collect();
113 string path = Server.MapPath( this .xlfile.Text + " .xls " );
114
115 System.IO.FileInfo file = new System.IO.FileInfo(path);
116 Response.Clear();
117 Response.Charset = " GB2312 " ;
118 Response.ContentEncoding = System.Text.Encoding.UTF8;
119 // 添加头信息,为"文件下载/另存为"对话框指定默认文件名
120 Response.AddHeader( " Content-Disposition " , " attachment; filename= " + Server.UrlEncode(file.Name));
121 // 添加头信息,指定文件大小,让浏览器能够显示下载进度
122 Response.AddHeader( " Content-Length " , file.Length.ToString());
123
124 // 指定返回的是一个不能被客户端读取的流,必须被下载
125 Response.ContentType = " application/ms-excel " ;
126
127 // 把文件流发送到客户端
128 Response.WriteFile(file.FullName);
129 // 停止页面的执行
130
131 Response.End();
132 }
133
134
1、方法1:
2 SqlDataAdapter da = new SqlDataAdapter( " select * from tb1 " ,conn);
3 DataSet ds = new DataSet();
4 da.Fill(ds, " table1 " );
5 DataTable dt = ds.Tables[ " table1 " ];
6 string name = System.Configuration.ConfigurationSettings.AppSettings[ " downloadurl " ].ToString() + DateTime.Today.ToString( " yyyyMMdd " ) + new Random(DateTime.Now.Millisecond).Next( 10000 ).ToString() + " .csv " ; // 存放到web.config中downloadurl指定的路径,文件格式为当前日期+4位随机数
7 FileStream fs = new FileStream(name,FileMode.Create,FileAccess.Write);
8 StreamWriter sw = new StreamWriter(fs,System.Text.Encoding.GetEncoding( " gb2312 " ));
9 sw.WriteLine( " 自动编号,姓名,年龄 " );
10 foreach (DataRow dr in dt.Rows)
11 {
12 sw.WriteLine(dr[ " ID " ] + " , " + dr[ " vName " ] + " , " + dr[ " iAge " ]);
13 }
14 sw.Close();
15 Response.AddHeader( " Content-Disposition " , " attachment; filename= " + Server.UrlEncode(name));
16 Response.ContentType = " application/ms-excel " ; // 指定返回的是一个不能被客户端读取的流,必须被下载
17 Response.WriteFile(name); // 把文件流发送到客户端
18 Response.End();
19
20
21 public void Out2Excel( string sTableName, string url)
22 {
23 Excel.Application oExcel = new Excel.Application();
24 Workbooks oBooks;
25 Workbook oBook;
26 Sheets oSheets;
27 Worksheet oSheet;
28 Range oCells;
29 string sFile = "" ,sTemplate = "" ;
30 //
31 System.Data.DataTable dt = TableOut(sTableName).Tables[ 0 ];
32
33 sFile = url + " myExcel.xls " ;
34 sTemplate = url + " MyTemplate.xls " ;
35 //
36 oExcel.Visible = false ;
37 oExcel.DisplayAlerts = false ;
38 // 定义一个新的工作簿
39 oBooks = oExcel.Workbooks;
40 oBooks.Open(sTemplate,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing,Type.Missing, Type.Missing, Type.Missing);
41 oBook = oBooks.get_Item( 1 );
42 oSheets = oBook.Worksheets;
43 oSheet = (Worksheet)oSheets.get_Item( 1 );
44 // 命名该sheet
45 oSheet.Name = " Sheet1 " ;
46
47 oCells = oSheet.Cells;
48 // 调用dumpdata过程,将数据导入到Excel中去
49 DumpData(dt,oCells);
50 // 保存
51 oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing,Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing);
52 oBook.Close( false , Type.Missing,Type.Missing);
53 // 退出Excel,并且释放调用的COM资源
54 oExcel.Quit();
55
56 GC.Collect();
57 KillProcess( " Excel " );
58 }
59
60 private void KillProcess( string processName)
61 {
62 System.Diagnostics.Process myproc = new System.Diagnostics.Process();
63 // 得到所有打开的进程
64 try
65 {
66 foreach (Process thisproc in Process.GetProcessesByName(processName))
67 {
68 if ( ! thisproc.CloseMainWindow())
69 {
70 thisproc.Kill();
71 }
72 }
73 }
74 catch (Exception Exc)
75 {
76 throw new Exception( "" ,Exc);
77 }
78 }
79
80
2 {
3 gridbind();
4 if (ds1 == null ) return ;
5
6 string saveFileName = "" ;
7 // bool fileSaved=false;
8 SaveFileDialog saveDialog = new SaveFileDialog();
9 saveDialog.DefaultExt = " xls " ;
10 saveDialog.Filter = " Excel文件|*.xls " ;
11 saveDialog.FileName = " Sheet1 " ;
12 saveDialog.ShowDialog();
13 saveFileName = saveDialog.FileName;
14 if (saveFileName.IndexOf( " : " ) < 0 ) return ; // 被点了取消
15 // excelapp.Workbooks.Open (App.path & 工程进度表.xls)
16
17 Excel.Application xlApp = new Excel.Application();
18 object missing = System.Reflection.Missing.Value;
19
20
21 if (xlApp == null )
22 {
23 MessageBox.Show( " 无法创建Excel对象,可能您的机子未安装Excel " );
24 return ;
25 }
26 Excel.Workbooks workbooks = xlApp.Workbooks;
27 Excel.Workbook workbook = workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet);
28 Excel.Worksheet worksheet = (Excel.Worksheet)workbook.Worksheets[ 1 ]; // 取得sheet1
29 Excel.Range range;
30
31
32 string oldCaption = Title_label .Text.Trim ();
33 long totalCount = ds1.Tables[ 0 ].Rows.Count;
34 long rowRead = 0 ;
35 float percent = 0 ;
36
37 worksheet.Cells[ 1 , 1 ] = Title_label .Text.Trim ();
38 // 写入字段
39 for ( int i = 0 ;i < ds1.Tables[ 0 ].Columns.Count;i ++ )
40 {
41 worksheet.Cells[ 2 ,i + 1 ] = ds1.Tables[ 0 ].Columns.ColumnName;
42 range = (Excel.Range)worksheet.Cells[ 2 ,i + 1 ];
43 range.Interior.ColorIndex = 15 ;
44 range.Font.Bold = true ;
45
46 }
47 // 写入数值
48 Caption .Visible = true ;
49 for ( int r = 0 ;r < ds1.Tables[ 0 ].Rows.Count;r ++ )
50 {
51 for ( int i = 0 ;i < ds1.Tables[ 0 ].Columns.Count;i ++ )
52 {
53 worksheet.Cells[r + 3 ,i + 1 ] = ds1.Tables[ 0 ].Rows[r];
54 }
55 rowRead ++ ;
56 percent = (( float )( 100 * rowRead)) / totalCount;
57 this .Caption.Text = " 正在导出数据[ " + percent.ToString( " 0.00 " ) + " %] " ;
58 Application.DoEvents();
59 }
60 worksheet.SaveAs(saveFileName,missing,missing,missing,missing,missing,missing,missing,missing);
61
62 this .Caption.Visible = false ;
63 this .Caption.Text = oldCaption;
64
65 range = worksheet.get_Range(worksheet.Cells[ 2 , 1 ],worksheet.Cells[ds1.Tables[ 0 ].Rows.Count + 2 ,ds1.Tables[ 0 ].Columns.Count]);
66 range.BorderAround(Excel.XlLineStyle.xlContinuous,Excel.XlBorderWeight.xlThin,Excel.XlColorIndex.xlColorIndexAutomatic, null );
67
68 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
69 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].LineStyle = Excel.XlLineStyle.xlContinuous;
70 range.Borders[Excel.XlBordersIndex.xlInsideHorizontal].Weight = Excel.XlBorderWeight.xlThin;
71
72 if (ds1.Tables[ 0 ].Columns.Count > 1 )
73 {
74 range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex = Excel.XlColorIndex.xlColorIndexAutomatic;
75 }
76 workbook.Close(missing,missing,missing);
77 xlApp.Quit();
78 }
79
80
2 /// 常用方法,列之间加\t开。
3 /// </summary>
4 /// <remarks>
5 /// using System.IO;
6 /// </remarks>
7 /// <param name="dgv"></param>
8 private void DataGridViewToExcel(DataGridView dgv)
9 {
10 SaveFileDialog dlg = new SaveFileDialog();
11 dlg.Filter = " Execl files (*.xls)|*.xls " ;
12 dlg.CheckFileExists = false ;
13 dlg.CheckPathExists = false ;
14 dlg.FilterIndex = 0 ;
15 dlg.RestoreDirectory = true ;
16 dlg.CreatePrompt = true ;
17 dlg.Title = " 保存为Excel文件 " ;
18
19 if (dlg.ShowDialog() == DialogResult.OK)
20 {
21 Stream myStream;
22 myStream = dlg.OpenFile();
23 StreamWriter sw = new StreamWriter(myStream, System.Text.Encoding.GetEncoding( - 0 ));
24 string columnTitle = "" ;
25 try
26 {
27 // 写入列标题
28 for ( int i = 0 ; i < dgv.ColumnCount; i ++ )
29 {
30 if (i > 0 )
31 {
32 columnTitle += " \t " ;
33 }
34 columnTitle += dgv.Columns[i].HeaderText;
35 }
36 sw.WriteLine(columnTitle);
37
38 // 写入列内容
39 for ( int j = 0 ; j < dgv.Rows.Count; j ++ )
40 {
41 string columnValue = "" ;
42 for ( int k = 0 ; k < dgv.Columns.Count; k ++ )
43 {
44 if (k > 0 )
45 {
46 columnValue += " \t " ;
47 }
48 if (dgv.Rows[j].Cells[k].Value == null )
49 columnValue += "" ;
50 else
51 columnValue += dgv.Rows[j].Cells[k].Value.ToString().Trim();
52 }
53 sw.WriteLine(columnValue);
54 }
55 sw.Close();
56 myStream.Close();
57 }
58 catch (Exception e)
59 {
60 MessageBox.Show(e.ToString());
61 }
62 finally
63 {
64 sw.Close();
65 myStream.Close();
66 }
67 }
68 }
69
三、附注:
虽然都是实现导出excel的功能,但在asp.net和winform的程序中,实现的代码是各不相同的。在asp.net中,是在服务器端读取数据,在服务器端把数据以ms-excel的格式,以Response输出到浏览器(客户端);而在winform中,是把数据读到客户端(因为winform运行端就是客户端),然后调用客户端安装的office组件,将读到的数据写在excel