湛刚 de BLOG 原文地址 Asp.Net 导出 Excel 数据的9种方案
简介
Excel 的强大之处在于它不仅仅只能打开Excel格式的文档,它还能打开CSV格式、Tab格式、website table 等多钟格式的文档。它具备自动识别行号,字符,格式化数字等功能,例如:如果你在Excel 单元格中输入数字 "123456789012" 会自动转化为"1.23457E+11"。
背景介绍
正因为Excel的强大和易用,大家都喜欢将数据导出为 Excel 备用。这里我会介绍一系列通过Asp.Net导出Excel数据的方法。将导出文件存储到服务器并提供地址给客户端下载,或重定向到文件下载页面:当 Response时,数据列以 " " 分隔,行以" "分隔。好了,现在给大家展示这是怎么做的。
使用代码导出
方案1:导出全部HTML 数据到 Excel
这种方法是将Html中的所有文档内容,包括按钮,表格,图片等所有页面内容导出为 Excel
Response.Clear(); Response.Buffer = true; Response.AppendHeader("Content-Disposition","attachment;filename="+DateTime. Now.ToString("yyyyMMdd")+".xls"); Response.ContentEncoding = System.Text.Encoding.UTF8; Response.ContentType = ""application/ms-excel"; this.EnableViewState = false;
这里我们使用了Page的"ContentType" 属性,它默认为"text/Html",输出到客户端即为Html。如果我们将它改为"ms-excel",页面将输出Excel格式的内容,客户端就可以下载并存储它了。
页面property 还包括:image/JPEG, text/HTML, image/GIF and vnd.ms-excel/msword.
方案2:从DataGrid导出数据到Excel
尽管上面的方法能帮你导出Excel数据,但它导出了所有的HTML内容,包括按钮、图片等,这并不是我们所需要的。通常,我们仅仅需要导出DataGrid中的数据。
System.Web.UI.Control ctl=this.DataGrid1; //DataGrid1 (you created in the windowForm) HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); HttpContext.Current.Response.Charset ="UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.Default; HttpContext.Current.Response.ContentType = "application/ms-excel"; ctl.Page.EnableViewState =false; System.IO.StringWriter tw = new System.IO.StringWriter() ; System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End();
如果你有多个包含DataGrid 并需导出数据的页面,我们可以封装方法:
public void DGToExcel(System.Web.UI.Control ctl) { HttpContext.Current.Response.AppendHeader("Content-Disposition","attachment;filename=Excel.xls"); HttpContext.Current.Response.Charset ="UTF-8"; HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.UTF8; HttpContext.Current.Response.ContentType ="application/ms-excel"; ctl.Page.EnableViewState =false; System.IO.StringWriter tw = new System.IO.StringWriter() ; System.Web.UI.HtmlTextWriter hw = new System.Web.UI.HtmlTextWriter (tw); ctl.RenderControl(hw); HttpContext.Current.Response.Write(tw.ToString()); HttpContext.Current.Response.End(); }
调用方法:DGToExcel(datagrid1);
方案3:自动导出Excel数据
使用此方法,你需要 下载免费的.NET组件 ,并使用如下代码(部分)导出数据:
private void button1_Click(object sender, EventArgs e) { System.Data.OleDb.OleDbConnection oleDbConnection1 = new System.Data.OleDb.OleDbConnection(); oleDbConnection1.ConnectionString = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=........Databasedemo.mdb"; System.Data.OleDb.OleDbCommand oleDbCommand1 = new System.Data.OleDb.OleDbCommand(); oleDbCommand1.CommandText = "select * from parts"; oleDbCommand1.Connection = oleDbConnection1; System.Data.OleDb.OleDbCommand oleDbCommand2 = new System.Data.OleDb.OleDbCommand(); oleDbCommand2.CommandText = "select * from country"; oleDbCommand2.Connection = oleDbConnection1; Spire.DataExport.Delegates.DataParamsEventHandler(this.cellExport3_GetDataParams); oleDbConnection1.Open(); try { cellExport3.SaveToFile(); } finally { oleDbConnection1.Close(); } } private void cellExport3_GetDataParams (object sender, Spire.DataExport.EventArgs.DataParamsEventArgs e) { if ((e.Sheet == 0) && (e.Col == 6)) { e.FormatText = (sender as Spire.DataExport.XLS.WorkSheet).ExportCell.DataFormats.Currency; } }
执行上面的代码,你将得到:
此方案导出的Excel文件可以直接在Excel 2010 中打开、编辑和修改。虽然一些特定的功能不可用,但它能够被Excel 2010使用。
方案4:从DataSet导出Excel数据
依照上面都的方法,我么能很容易的导出DataSet数据到Excel,我们只需要在页面Response 时将DataSet 表中的数据组装为"ms-excel" 格式的数据,并通过Http发送出去。
注:ds 代表Dataset,用它来填充DataTable,文件名包含后缀,例如:excel2006.xls。
public void CreateExcel(DataSet ds,string FileName) { HttpResponse resp; resp = Page.Response; resp.ContentEncoding = System.Text.Encoding.GetEncoding("UTF-8"); resp.AppendHeader("Content-Disposition", "attachment;filename="+FileName); string colHeaders= "", ls_item=""; // Define table object and row object, // and at the same time use DataSet initialize value. DataTable dt=ds.Tables[0]; DataRow[] myRow=dt.Select();//dt.Select("id>10") Data Filer can be used as: dt.Select("id>10") int i=0; int cl=dt.Columns.Count; //Get column titles of each DataTable and divided by "t". Press "enter" after the last column title. for(i=0;i<cl;i++) colheaders+="dt.Columns[i].Caption.ToString()+"t";" for(i="0;i<cl;i++)" if(i="=(cl-1))//(last" +="dt.Columns[i].Caption.ToString()" ls_item+="row[i].ToString()+"t";" />
方案5:从DataView导出Excel数据
如果你想导出不规则的行和列到Excel,你可以使用一下方法:
public void OutputExcel(DataView dv,string str) { //dv presents data which will be exported to Excel, str is the name of title GC.Collect(); Application excel;// = new Application(); int rowIndex=4; int colIndex=1; _Workbook xBk; _Worksheet xSt; excel= new ApplicationClass(); xBk = excel.Workbooks.Add(true); xSt = (_Worksheet)xBk.ActiveSheet; // // Acquire Title // foreach(DataColumn col in dv.Table.Columns) { colIndex++; excel.Cells[4,colIndex] = col.ColumnName; xSt.get_Range(excel.Cells[4,colIndex],excel.Cells [4,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;//Set title format as middle } // //Obtain data from table // foreach(DataRowView row in dv) { rowIndex ++; colIndex = 1; foreach(DataColumn col in dv.Table.Columns) { colIndex ++; if(col.DataType == System.Type.GetType("System.DateTime")) { excel.Cells[rowIndex,colIndex] = (Convert.ToDateTime(row[col.ColumnName].ToString())).ToString("yyyy-MM-dd"); xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells [rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter;// Set the style as middle } else if(col.DataType == System.Type.GetType("System.String")) { excel.Cells[rowIndex,colIndex] = "'"+row[col.ColumnName].ToString(); xSt.get_Range(excel.Cells[rowIndex,colIndex],excel.Cells [rowIndex,colIndex]).HorizontalAlignment = XlVAlign.xlVAlignCenter; // Set the style as middle } else { excel.Cells[rowIndex,colIndex] = row[col.ColumnName].ToString(); } } } // //load a Aggregate line // int rowSum = rowIndex + 1; int colSum = 2; excel.Cells[rowSum,2] = " Aggregate "; xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,2]).HorizontalAlignment = XlHAlign.xlHAlignCenter; // //Set color for the selected content // xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells[rowSum,colIndex]).Select(); xSt.get_Range(excel.Cells[rowSum,colSum],excel.Cells [rowSum,colIndex]).Interior.ColorIndex = 19;//more than 50 types of color for you to choose // //obtain title of the whole excelsheet // excel.Cells[2,2] = str; // //Set title format for the whole excelsheet // xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Bold = true; xSt.get_Range(excel.Cells[2,2],excel.Cells[2,2]).Font.Size = 22; // //Set fittest width // xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Select(); xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Columns.AutoFit(); // //Set the tile as Cross and Middle // xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).Select(); xSt.get_Range(excel.Cells[2,2],excel.Cells[2,colIndex]).HorizontalAlignment = XlHAlign.xlHAlignCenterAcrossSelection; // //Draw borders // xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,colIndex]).Borders.LineStyle = 1; xSt.get_Range(excel.Cells[4,2],excel.Cells[rowSum,2]).Borders [XlBordersIndex.xlEdgeLeft].Weight = XlBorderWeight.xlThick;// Set left line as bold xSt.get_Range(excel.Cells[4,2],excel.Cells[4,colIndex]).Borders [XlBordersIndex.xlEdgeTop].Weight = XlBorderWeight.xlThick;// Set upper line as bold xSt.get_Range(excel.Cells[4,colIndex],excel.Cells[rowSum,colIndex]).Borders [XlBordersIndex.xlEdgeRight].Weight = XlBorderWeight.xlThick;//Set right line as bold xSt.get_Range(excel.Cells[rowSum,2],excel.Cells[rowSum,colIndex]).Borders [XlBordersIndex.xlEdgeBottom].Weight = XlBorderWeight.xlThick;//Set bottom line as bold // //Display effect // excel.Visible=true; //xSt.Export(Server.MapPath(".")+"); xBk.SaveCopyAs(Server.MapPath(".")+""); ds = null; xBk.Close()
两种 WinForms 导出Excel 数据的解决方案
方案6:
SqlConnection conn=new SqlConnection (System.Configuration.ConfigurationSettings.AppSettings["conn"]); SqlDataAdapter da=new SqlDataAdapter("select * from tb1",conn); DataSet ds=new DataSet(); da.Fill(ds,"table1"); DataTable dt=ds.Tables["table1"]; string downloadurl"].ToString()+DateTime.Today.ToString ("yyyyMMdd")+new Random(DateTime.Now.Millisecond).Next (10000).ToString()+".csv";//Store the path of downloadurl in web.config and the format should be set as "date + 4 random number " FileStream fs=new FileStream(name,FileMode.Create,FileAccess.Write); StreamWriter sw=new StreamWriter (fs,System.Text.Encoding.GetEncoding("utf-8"));("utf-8") sw.WriteLine("Auto number, name, age"); foreach(DataRow dr in dt.Rows) { sw.WriteLine(dr["ID"]+","+dr["vName"]+","+dr["iAge"]); } sw.Close(); Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(name)); Response.ContentType = "application/ms-excel"; //Set the return string is unavailable reading for client, and must be downloaded Response.WriteFile(name); //Send file string to client Response.End(); public void Out2Excel(string sTableName,string url) { Excel.Application oExcel=new Excel.Application(); Workbooks oBooks; Workbook oBook; Sheets oSheets; Worksheet oSheet; Range oCells; string sFile="",sTemplate=""; // System.Data.DataTable dt=TableOut(sTableName).Tables[0]; sFile=url+"myExcel.xls"; sTemplate=url+"MyTemplate.xls"; // oExcel.Visible=false; oExcel.DisplayAlerts=false; //define a new workbook oBooks=oExcel.Workbooks; 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); oBook=oBooks.get_Item(1); oSheets=oBook.Worksheets; oSheet=(Worksheet)oSheets.get_Item(1); //Give the sheet a name oSheet.Name="Sheet1"; oCells=oSheet.Cells; //Call dumpdata process and export to Excel DumpData(dt,oCells); //Store oSheet.SaveAs(sFile,Excel.XlFileFormat.xlTemplate,Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing); oBook.Close(false, Type.Missing,Type.Missing); //Exit Excel and free invoking COM resource oExcel.Quit(); GC.Collect(); KillProcess("Excel"); } private void KillProcess(string processName) { System.Diagnostics.Process myproc= new System.Diagnostics.Process(); //get all opened progresses try { foreach (Process thisproc in Process.GetProcessesByName(processName)) { if(!thisproc.CloseMainWindow()) { thisproc.Kill(); } } } catch(Exception Exc) { throw new Exception("",Exc); } }
方案7:
protected void ExportExcel() { gridbind(); if(ds1==null) return; string saveFileName=""; // bool fileSaved=false; SaveFileDialog saveDialog=new SaveFileDialog(); saveDialog.DefaultExt ="xls"; saveDialog.Filter="Excel File|*.xls"; saveDialog.FileName ="Sheet1"; saveDialog.ShowDialog(); saveFileName=saveDialog.FileName; if(saveFileName.IndexOf(":")<0) return; // Cancelled //excelapp.Workbooks.Open (App.path & Progress table.xls) Excel.Application xlApp=new Excel.Application(); object missing=System.Reflection.Missing.Value; if(xlApp==null) { MessageBox.Show("Create Excel object failed, maybe you dont install Excel "); return; } Excel.Workbooks workbooks=xlApp.Workbooks; Excel.Workbook workbook=workbooks.Add(Excel.XlWBATemplate.xlWBATWorksheet); Excel.Worksheet worksheet=(Excel.Worksheet)workbook.Worksheets[1];// Get sheet1 Excel.Range range; string oldCaption=Title_label .Text.Trim (); long totalCount=ds1.Tables[0].Rows.Count; long rowRead=0; float percent=0; worksheet.Cells[1,1]=Title_label .Text.Trim (); //Write text for(int i=0;i<ds1.tables[0].columns.count;i++) worksheet.cells[2,i+1]="ds1.Tables[0].Columns.ColumnName;" range.interior.colorindex="15;" range.font.bold="true;" .visible="true;" r="0;r<ds1.Tables[0].Rows.Count;r++)" i="0;i<ds1.Tables[0].Columns.Count;i++)" worksheet.cells[r+3,i+1]="ds1.Tables[0].Rows[r];" percent="((float)(100*rowRead))/totalCount;" this.caption.visible="false;" this.caption.text=" Exporting Data [" range="(Excel.Range)worksheet.Cells [2,i+1];" range.borders[excel.xlbordersindex.xlinsidehorizontal].colorindex= "Excel.XlColorIndex.xlColorIndexAutomatic;" range.borders[excel.xlbordersindex.xlinsidehorizontal].linestyle= "Excel.XlLineStyle.xlContinuous;" range.borders[excel.xlbordersindex.xlinsidehorizontal].weight= "Excel.XlBorderWeight.xlThin;">1) { range.Borders[Excel.XlBordersIndex.xlInsideVertical].ColorIndex =Excel.XlColorIndex.xlColorIndexAutomatic; } workbook.Close(missing,missing,missing); xlApp.Quit(); }
方案8 (from Cipherlad):
使用DataSet 的GetXml方法,并且使用XSLT将XML转化为标准的Excel格式,你可以使用不同样式模版对应不容版本的Excel,甚至可以用于导出其它文档。
方案9 (from Sergelp):
使用 OOXML 格式的开源库:http://simpleooxml.codeplex.com/
这是一个非常便捷的库,它包含多种格式、字体、颜色订制,你也不需要安装Excel软件,你可以在服务端创建Excel,然后实现下载,如下代码所示:
Dim ms As MemoryStream = ArticleDAL.GetStreamFromDataSet()
Response.Clear()
Response.AddHeader("content-disposition", String.Format("attachment;filename={0}", strFile))
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"
ms.WriteTo(Response.OutputStream)
Response.End()