Asp.Net 导出 Excel 数据的9种方案

简介

Excel 的强大之处在于它不仅仅只能打开Excel格式的文档，它还能打开CSV格式、Tab格式、website table 等多钟格式的文档。它具备自动识别行号，字符，格式化数字等功能，例如：如果你在Excel 单元格中输入数字 "123456789012" 会自动转化为"1.23457E+11"。

背景介绍

正因为Excel的强大和易用，大家都喜欢将数据导出为 Excel 备用。这里我会介绍一系列通过Asp.Net导出Excel数据的方法。将导出文件存储到服务器并提供地址给客户端下载，或重定向到文件下载页面：当Response时，数据列以 "\t" 分隔，行以"\n"分隔。好了，现在给大家展示这是怎么做的。

使用代码导出

方案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=..\..\..\..\Database\demo.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(false, null,null);               excel.Quit();             System.Runtime.InteropServices.Marshal.ReleaseComObject(xBk);             System.Runtime.InteropServices.Marshal.ReleaseComObject(excel);     System.Runtime.InteropServices.Marshal.ReleaseComObject(xSt);             xBk = null;             excel = null;    xSt = null;             GC.Collect();    string path = Server.MapPath(this.xlfile.Text+".xls");    System.IO.FileInfo file = new System.IO.FileInfo(path);    Response.Clear();    Response.Charset="UTF-8";    Response.ContentEncoding=System.Text.Encoding.UTF8;    //Add header, give a default file name for "File Download/Store as"   Response.AddHeader("Content-Disposition", "attachment; filename=" + Server.UrlEncode(file.Name));    //Add header, set file size to enable browser display download progress   Response.AddHeader("Content-Length", file.Length.ToString());       //Set the return string is unavailable reading for client, and must be downloaded   Response.ContentType = "application/ms-excel";     //Send file string to client    Response.WriteFile(file.FullName);    //Stop execute     Response.End(); } 

 

两种 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 workbookoBooks=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 nameoSheet.Name="Sheet1";oCells=oSheet.Cells;//Call dumpdata process and export to ExcelDumpData(dt,oCells);//StoreoSheet.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 resourceoExcel.Quit();GC.Collect();KillProcess("Excel");}private void KillProcess(string processName){System.Diagnostics.Process myproc= new System.Diagnostics.Process();//get all opened progressestry{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()