• [转] Asp.Net 导出 Excel 数据的9种方案


    湛刚 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);
    }
    }
    View Code

    方案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();
    }
    View Code

    方案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()
  • 相关阅读:
    【高级内部资料】.NET数据批量写入性能分析 第二篇
    负载均衡原理与实践详解 第五篇 负载均衡时数据包流程详解
    负载均衡原理与实践详解 第三篇 服务器负载均衡的基本概念网络基础
    如何提高Linq查询的性能(上)
    【全面解析DeepZoom 之二】Silverlight2及Deep Zoom环境的搭建
    关于让WPF软件界面支持全球化和本地化
    在WPF中自定义控件(3) CustomControl (上)
    【全面解析DeepZoom 之一】酷!Deep Zoom
    谈谈我理解的WPF团队模型——在UI Designer与Developer之间
    [WPF疑难]在WPF中显示动态GIF
  • 原文地址:https://www.cnblogs.com/arxive/p/5795586.html
Copyright © 2020-2023  润新知