.net导出Excel

来源：互联网发布：网络阅卷y100edu 编辑：程序博客网时间：2024/05/19 14:37

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

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

2、Default10.aspx

    <%@ 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 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

    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方法如下：

    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);            }

复制代码

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

2010-8-26 备注：

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

File.Copy(HttpContext.Current.Server.MapPath("~/Views/ActivityAdmin/ContactTemplate.xls"), newFileName, true);

在上面代码的下面加上：

FileInfo f = new FileInfo(newFileName);
if (f.Attributes.ToString().IndexOf("ReadOnly") != -1)
{
f.Attributes = FileAttributes.Normal;
}

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

17:11 2010-12-23 备注：

用企业库读取Excel：

web.config配置：

<!--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代码：

using Microsoft.Practices.EnterpriseLibrary.Data;Database db = DatabaseFactory.CreateDatabase("testXls");//[B0201$A2:C33]表示读取表B0201$的区域范围A2:C33DataTable 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

稍微改了下

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();        }

http://www.cnblogs.com/gdjlc/archive/2010/08/13/2086886.html