文章转自：
在asp.net中导出Excel有两种方法，一种是将导出的文件存放在服务器某个文件夹下面，然后将文件地址输出在浏览器上；一种是将文件直接将文件输出流写给浏览器。在Response输出时，t分隔的数据，导出Excel时，等价于分列，n等价于换行。
1、将整个html全部输出Excel此法将html中所有的内容，如按钮，表格，图片等全部输出到Excel中。
代码 1 Response.Clear(); 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控件上的数据。
代码 1 System.Web.UI.Control ctl = this .DataGrid1; 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语句的全部信息。
为方便使用,写成方法如下：
代码 1 public void DGToExcel(System.Web.UI.Control ctl) 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
代码 1 public void CreateExcel(DataSet ds, string FileName) 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
4、将dataview导出excel若想实现更加富于变化或者行列不规则的excel导出时，可用本法。
代码 1 public void OutputExcel(DataView dv, string str) 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
二、winForm中导出Excel的方法：1、方法1：
代码 1 SqlConnection conn = new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings[ " conn " ]); 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、方法2：
代码 1 protected void ExportExcel() 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
6.从DataGridView里导出
代码 1 /// <summary> 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