[转载].net导出Excel

来源：互联网发布：美国自费留学知乎编辑：程序博客网时间：2024/05/17 08:00

http://blog.csdn.net/gdjlc/article/details/5810311

综合参考了网上的方法，生成Excel文件提供下载，然后删除生成的Excel文件。

1、引用Microsoft.Office.Interop.Excel;(属性里的嵌入互操作类型改为Fasle)

2、Default10.aspx

[html] view plaincopyprint?
<%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default10.aspx.cs" Inherits="Default10" %>  
  
<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
<html xmlns="http://www.w3.org/1999/xhtml">  
<head id="Head1" runat="server">  
    <title></title>  
</head>  
<body>  
    <form id="form1" runat="server">  
    <div>  
        <asp:Button ID="ExportToExcel" runat="server" Text="导出Excel" OnClick="ExportToExcel_Click" />  
    </div>  
    </form>  
</body>  
</html>  

3、Default10.aspx.cs

[csharp] view plaincopyprint?
using System;  
using System.Collections.Generic;  
using System.Linq;  
using System.Web;  
using System.Web.UI;  
using System.Web.UI.WebControls;  
using Excel = Microsoft.Office.Interop.Excel; //添加引用  
  
public partial class Default10 : System.Web.UI.Page  
{  
    protected void Page_Load(object sender, EventArgs e)  
    {  
    }  
    protected void ExportToExcel_Click(object sender, EventArgs e)  
    {  
        Excel.Application excel1 = new Excel.Application();  
        excel1.DisplayAlerts = false;  
        Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);  
        excel1.Visible = false;  
        Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"];  //表头           
        worksheet1.Cells[1, 1] = "姓名";  //Excel里从第1行，第1列计算            
        worksheet1.Cells[1, 2] = "性别";  
        worksheet1.Cells[1, 3] = "联系电话";  
        System.Data.DataTable dt = GetTestData(100);  
        for (int i = 0; i < dt.Rows.Count; i++)  
        {  
            for (int j = 0; j < dt.Columns.Count; j++)  
                worksheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();  
        }  
        string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";  
        string filePath = Server.MapPath("~/" + fileName);  
        workbook1.SaveAs(filePath, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Excel.XlSaveAsAccessMode.xlNoChange, Type.Missing, Type.Missing, Type.Missing, Type.Missing, Type.Missing);  
        excel1.Workbooks.Close();  
        excel1.Quit();  
        int generation = GC.GetGeneration(excel1);  
        System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1); excel1 = null; GC.Collect(generation);        //打开要下载的文件，并把该文件存放在FileStream中           
        System.IO.FileStream Reader = System.IO.File.OpenRead(filePath);  
        //文件传送的剩余字节数：初始值为文件的总大小            
        long Length = Reader.Length;  
        HttpContext.Current.Response.Buffer = false;  
        HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");  
        HttpContext.Current.Response.ContentType = "application/octet-stream";  
        HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);  
        HttpContext.Current.Response.AddHeader("Content-Length", Length.ToString());  
        byte[] Buffer = new Byte[10000];    //存放欲发送数据的缓冲区            
        int ByteToRead;              //每次实际读取的字节数            
        while (Length > 0)  
        {  
            //剩余字节数不为零，继续传送               
            if (Response.IsClientConnected)  
            {                //客户端浏览器还打开着，继续传送                    
                ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据                    
                HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器                  HttpContext.Current.Response.Flush();   //立即写入客户端                  
                Length -= ByteToRead;   //剩余字节数减少                
            }  
            else  
            {                //客户端浏览器已经断开，阻止继续循环                   
                Length = -1;  
            }  
        }  
        //关闭该文件            
        Reader.Close();  
        if (System.IO.File.Exists(filePath))  
            System.IO.File.Delete(filePath);  
    }  
    System.Data.DataTable GetTestData(int num) //测试数据       
    {  
        System.Data.DataTable dt = new System.Data.DataTable();  
        System.Data.DataRow dr;  
        dt.Columns.Add(new System.Data.DataColumn("ContactName", typeof(String)));  
        dt.Columns.Add(new System.Data.DataColumn("ContactSex", typeof(String)));  
        dt.Columns.Add(new System.Data.DataColumn("ContactPhone", typeof(String)));  
        for (int i = 0; i < num; i++)  
        {  
            Random rnd = new Random(Environment.TickCount * i);  
            dr = dt.NewRow(); dr[0] = "姓名" + rnd.Next(1, num);  
            dr[1] = rnd.Next(1, num) < num / 2 ? "男" : "女"; dr[2] = rnd.Next(1000000, 99999999);  
            dt.Rows.Add(dr);  
        }  
        return dt;  
    }  
}  

另一种利用Excel模板生成Excel方法如下：

[csharp] view plaincopyprint?
private void ExportToExcel(DataTable dt, string fileName)  
  {  
      //转换为物理路径             
      string newFileName = HttpContext.Current.Server.MapPath("~/" + fileName);  
      //根据模板正式生成该Excel文件            
      File.Copy(HttpContext.Current.Server.MapPath("~/ContactTemplate.xls"), newFileName, true);  
      //建立指向该Excel文件的数据库连接             
      string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + newFileName + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=2'";  
      OleDbConnection Conn = new OleDbConnection(strConn);  //打开连接，为操作该文件做准备            
      Conn.Open();  
      OleDbCommand Cmd = new OleDbCommand("", Conn);  
      foreach (DataRow DR in dt.Rows)  
      {  
          string XSqlString = "insert into [Sheet1$]";  
          XSqlString += "([姓名],[性别],[联系电话]) values(";  
          XSqlString += "'" + DR["ContactName"] + "',";  
          XSqlString += "'" + (DR["ContactSex"].ToString() == "1" ? "男" : "女") + "',";  
          XSqlString += "'" + DR["ContactPhone"] + "')";  
          Cmd.CommandText = XSqlString;  
          Cmd.ExecuteNonQuery();  
      }            //操作结束，关闭连接             
      Conn.Close();  
      //打开要下载的文件，并把该文件存放在FileStream中        
      System.IO.FileStream Reader = System.IO.File.OpenRead(newFileName);  
      //文件传送的剩余字节数：初始值为文件的总大小              
      long Length = Reader.Length;  
      HttpContext.Current.Response.Buffer = false;  
      HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");  
      HttpContext.Current.Response.ContentType = "application/octet-stream";  
      HttpContext.Current.Response.Charset = "utf-8";  
      HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);  
      HttpContext.Current.Response.AddHeader("Content-Length", Length.ToString());  
      byte[] Buffer = new Byte[10000];       //存放欲发送数据的缓冲区            
      int ByteToRead;                            //每次实际读取的字节数        
      while (Length > 0)  
      {  
          //剩余字节数不为零，继续传送                 
          if (Response.IsClientConnected)  
          {  
              //客户端浏览器还打开着，继续传送                      
              ByteToRead = Reader.Read(Buffer, 0, 10000);//往缓冲区读入数据                 
              HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead);   //把缓冲区的数据写入客户端浏览器                     
              HttpContext.Current.Response.Flush();          //立即写入客户端                 
              Length -= ByteToRead;      //剩余字节数减少               
          }  
          else  
          {  
              //客户端浏览器已经断开，阻止继续循环                     
              Length = -1;  
          }  
      }  
      //关闭该文件              
      Reader.Close();            //删除该Excel文件             
      if (File.Exists(newFileName))  
          File.Delete(newFileName);  
  }  

--------------------------------------------------------------------------

备注：读取Excel到DataTable

public static DataTable ExcelToTable(string path)
        {
            string strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + path + ";Extended Properties='Excel 8.0;HDR=yes;IMEX=2'";
            using (OleDbConnection conn = new OleDbConnection(strConn))
            {
                conn.Open();
                string sql = "select * from [Sheet1$]";
                OleDbCommand cmd = new OleDbCommand(sql, conn);

                OleDbDataAdapter adapter = new OleDbDataAdapter(sql, conn);
                DataTable dt = new DataTable();
                adapter.Fill(dt);
                return dt;
            }
        }

--------------------------------------------------------------------------

2010-8-26 备注：

在项目中使用第2种方法时，出现"操作必须使用一个可更新的查询"的错误提示，原因是项目采用TFS管理，使Excel文件属性是只读的，解决方法是去掉只读属性：

[csharp] view plaincopyprint?
File.Copy(HttpContext.Current.Server.MapPath("~/Views/ActivityAdmin/ContactTemplate.xls"), newFileName, true);  

在上面代码的下面加上：

[csharp] view plaincopyprint?
FileInfo f = new FileInfo(newFileName);  
 if (f.Attributes.ToString().IndexOf("ReadOnly") != -1)  
 {  
       f.Attributes = FileAttributes.Normal;  
}  

---------------------------------------------------------------------------

2010-12-23 备注：

用企业库读取Excel：

web.config配置：

[html] view plaincopyprint?
<!--test.xls放在App_Data目录下-->  
<!--HDR=yes;IMEX=1表示：第一行不作为数据返回，且以文本方式读取-->  
 <add name="testXls" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;data source=|DataDirectory|test.xls;Extended Properties='Excel 8.0;HDR=yes;IMEX=1'"   providerName="System.Data.OleDb" />  

*.aspx.cs代码：

[csharp] view plaincopyprint?
using Microsoft.Practices.EnterpriseLibrary.Data;  
  
Database db = DatabaseFactory.CreateDatabase("testXls");  
//[B0201$A2:C33]表示读取表B0201$的区域范围A2:C33  
DataTable dt = db.ExecuteDataSet(CommandType.Text, "select * from [B0201$A2:C33]").Tables[0];  

另一种不错方法：

使用HTML,CSS快速导出数据到Excel

http://www.cnblogs.com/ruinet/archive/2009/10/17/1585320.html

稍微改了下

[csharp] view plaincopyprint?
public static void CreateExcel(string strTable, string fileName)  
        {  
            string HEADER = "<html xmlns:x=/"urn:schemas-microsoft-com:office:excel/">" +  
                                          "<meta http-equiv=Content-Type content=/"text/html; charset=/"gb2312/">" +  
                                          "<head>" +  
                                          "<!--[if gte mso 9]><xml>" +  
                                           "<x:ExcelWorkbook>" +  
                                               "<x:ExcelWorksheets>" +  
                                                   "<x:ExcelWorksheet>" +  
                                                       "<x:Name>Sheet1</x:Name>" +  
                                                       "<x:WorksheetOptions>" +  
                                                           "<x:Print>" +  
                                                               "<x:ValidPrinterInfo />" +  
                                                           "</x:Print>" +  
                                                       "</x:WorksheetOptions>" +  
                                                   "</x:ExcelWorksheet>" +  
                                               "</x:ExcelWorksheets>" +  
                                           "</x:ExcelWorkbook>" +  
                                       "</xml>" +  
                                       "<![endif]-->";  
  
            System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");  
            System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));  
            System.Web.HttpContext.Current.Response.ContentType = "ms-excel/application";  
  
  
            StringBuilder sbHtml = new StringBuilder();  
            sbHtml.AppendFormat(@"{0}</head>  
                         <body>{1}</body>  
                         </html>", HEADER, strTable);  
  
            System.Web.HttpContext.Current.Response.Write(sbHtml.ToString());  
            System.Web.HttpContext.Current.Response.Flush();  
            System.Web.HttpContext.Current.Response.Clear();  
            System.Web.HttpContext.Current.Response.End();  
        }  

0 0