[转载].net导出Excel

Biegral 2018/11/6 17:04:43 1398

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

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

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

2、Default10.aspx 

[html] view plaincopyprint?
  1. <%@ Page Language="C#" AutoEventWireup="true" CodeFile="Default10.aspx.cs" Inherits="Default10" %>  
  2.   
  3. <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">  
  4. <html xmlns="http://www.w3.org/1999/xhtml">  
  5. <head id="Head1" runat="server">  
  6.     <title></title>  
  7. </head>  
  8. <body>  
  9.     <form id="form1" runat="server">  
  10.     <div>  
  11.         <asp:Button ID="ExportToExcel" runat="server" Text="导出Excel" OnClick="ExportToExcel_Click" />  
  12.     </div>  
  13.     </form>  
  14. </body>  
  15. </html>  
3、Default10.aspx.cs 
[csharp] view plaincopyprint?
  1. using System;  
  2. using System.Collections.Generic;  
  3. using System.Linq;  
  4. using System.Web;  
  5. using System.Web.UI;  
  6. using System.Web.UI.WebControls;  
  7. using Excel = Microsoft.Office.Interop.Excel; //添加引用  
  8.   
  9. public partial class Default10 : System.Web.UI.Page  
  10. {  
  11.     protected void Page_Load(object sender, EventArgs e)  
  12.     {  
  13.     }  
  14.     protected void ExportToExcel_Click(object sender, EventArgs e)  
  15.     {  
  16.         Excel.Application excel1 = new Excel.Application();  
  17.         excel1.DisplayAlerts = false;  
  18.         Excel.Workbook workbook1 = excel1.Workbooks.Add(Type.Missing);  
  19.         excel1.Visible = false;  
  20.         Excel.Worksheet worksheet1 = (Excel.Worksheet)workbook1.Worksheets["sheet1"];  //表头           
  21.         worksheet1.Cells[1, 1] = "姓名";  //Excel里从第1行，第1列计算            
  22.         worksheet1.Cells[1, 2] = "性别";  
  23.         worksheet1.Cells[1, 3] = "联系电话";  
  24.         System.Data.DataTable dt = GetTestData(100);  
  25.         for (int i = 0; i < dt.Rows.Count; i++)  
  26.         {  
  27.             for (int j = 0; j < dt.Columns.Count; j++)  
  28.                 worksheet1.Cells[i + 2, j + 1] = dt.Rows[i][j].ToString();  
  29.         }  
  30.         string fileName = DateTime.Now.ToString("yyyyMMddHHmmss") + ".xls";  
  31.         string filePath = Server.MapPath("~/" + fileName);  
  32.         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);  
  33.         excel1.Workbooks.Close();  
  34.         excel1.Quit();  
  35.         int generation = GC.GetGeneration(excel1);  
  36.         System.Runtime.InteropServices.Marshal.ReleaseComObject(excel1); excel1 = null; GC.Collect(generation);        //打开要下载的文件，并把该文件存放在FileStream中           
  37.         System.IO.FileStream Reader = System.IO.File.OpenRead(filePath);  
  38.         //文件传送的剩余字节数：初始值为文件的总大小            
  39.         long Length = Reader.Length;  
  40.         HttpContext.Current.Response.Buffer = false;  
  41.         HttpContext.Current.Response.AddHeader("Connection", "Keep-Alive");  
  42.         HttpContext.Current.Response.ContentType = "application/octet-stream";  
  43.         HttpContext.Current.Response.AddHeader("Content-Disposition", "attachment; filename=" + fileName);  
  44.         HttpContext.Current.Response.AddHeader("Content-Length", Length.ToString());  
  45.         byte[] Buffer = new Byte[10000];    //存放欲发送数据的缓冲区            
  46.         int ByteToRead;              //每次实际读取的字节数            
  47.         while (Length > 0)  
  48.         {  
  49.             //剩余字节数不为零，继续传送               
  50.             if (Response.IsClientConnected)  
  51.             {                //客户端浏览器还打开着，继续传送                    
  52.                 ByteToRead = Reader.Read(Buffer, 0, 10000); //往缓冲区读入数据                    
  53.                 HttpContext.Current.Response.OutputStream.Write(Buffer, 0, ByteToRead); //把缓冲区的数据写入客户端浏览器                  HttpContext.Current.Response.Flush();   //立即写入客户端                  
  54.                 Length -= ByteToRead;   //剩余字节数减少                
  55.             }  
  56.             else  
  57.             {                //客户端浏览器已经断开，阻止继续循环                   
  58.                 Length = -1;  
  59.             }  
  60.         }  
  61.         //关闭该文件            
  62.         Reader.Close();  
  63.         if (System.IO.File.Exists(filePath))  
  64.             System.IO.File.Delete(filePath);  
  65.     }  
  66.     System.Data.DataTable GetTestData(int num) //测试数据       
  67.     {  
  68.         System.Data.DataTable dt = new System.Data.DataTable();  
  69.         System.Data.DataRow dr;  
  70.         dt.Columns.Add(new System.Data.DataColumn("ContactName", typeof(String)));  
  71.         dt.Columns.Add(new System.Data.DataColumn("ContactSex", typeof(String)));  
  72.         dt.Columns.Add(new System.Data.DataColumn("ContactPhone", typeof(String)));  
  73.         for (int i = 0; i < num; i++)  
  74.         {  
  75.             Random rnd = new Random(Environment.TickCount * i);  
  76.             dr = dt.NewRow(); dr[0] = "姓名" + rnd.Next(1, num);  
  77.             dr[1] = rnd.Next(1, num) < num / 2 ? "男" : "女"; dr[2] = rnd.Next(1000000, 99999999);  
  78.             dt.Rows.Add(dr);  
  79.         }  
  80.         return dt;  
  81.     }  
  82. }  
另一种利用Excel模板生成Excel方法如下：

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

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

备注：读取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?
  1. File.Copy(HttpContext.Current.Server.MapPath("~/Views/ActivityAdmin/ContactTemplate.xls"), newFileName, true);  
在上面代码的下面加上：
[csharp] view plaincopyprint?
  1. FileInfo f = new FileInfo(newFileName);  
  2.  if (f.Attributes.ToString().IndexOf("ReadOnly") != -1)  
  3.  {  
  4.        f.Attributes = FileAttributes.Normal;  
  5. }  
 ---------------------------------------------------------------------------

2010-12-23 备注：

用企业库读取Excel：


web.config配置：

[html] view plaincopyprint?
  1. <!--test.xls放在App_Data目录下-->  
  2. <!--HDR=yes;IMEX=1表示：第一行不作为数据返回，且以文本方式读取-->  
  3.  <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?
  1. using Microsoft.Practices.EnterpriseLibrary.Data;  
  2.   
  3. Database db = DatabaseFactory.CreateDatabase("testXls");  
  4. //[B0201$A2:C33]表示读取表B0201$的区域范围A2:C33  
  5. 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?
  1. public static void CreateExcel(string strTable, string fileName)  
  2.         {  
  3.             string HEADER = "<html xmlns:x=/"urn:schemas-microsoft-com:office:excel/">" +  
  4.                                           "<meta http-equiv=Content-Type content=/"text/html; charset=/"gb2312/">" +  
  5.                                           "<head>" +  
  6.                                           "<!--[if gte mso 9]><xml>" +  
  7.                                            "<x:ExcelWorkbook>" +  
  8.                                                "<x:ExcelWorksheets>" +  
  9.                                                    "<x:ExcelWorksheet>" +  
  10.                                                        "<x:Name>Sheet1</x:Name>" +  
  11.                                                        "<x:WorksheetOptions>" +  
  12.                                                            "<x:Print>" +  
  13.                                                                "<x:ValidPrinterInfo />" +  
  14.                                                            "</x:Print>" +  
  15.                                                        "</x:WorksheetOptions>" +  
  16.                                                    "</x:ExcelWorksheet>" +  
  17.                                                "</x:ExcelWorksheets>" +  
  18.                                            "</x:ExcelWorkbook>" +  
  19.                                        "</xml>" +  
  20.                                        "<![endif]-->";  
  21.   
  22.             System.Web.HttpContext.Current.Response.ContentEncoding = System.Text.Encoding.GetEncoding("GB2312");  
  23.             System.Web.HttpContext.Current.Response.AppendHeader("Content-Disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8));  
  24.             System.Web.HttpContext.Current.Response.ContentType = "ms-excel/application";  
  25.   
  26.   
  27.             StringBuilder sbHtml = new StringBuilder();  
  28.             sbHtml.AppendFormat(@"{0}</head>  
  29.                          <body>{1}</body>  
  30.                          </html>", HEADER, strTable);  
  31.   
  32.             System.Web.HttpContext.Current.Response.Write(sbHtml.ToString());  
  33.             System.Web.HttpContext.Current.Response.Flush();  
  34.             System.Web.HttpContext.Current.Response.Clear();  
  35.             System.Web.HttpContext.Current.Response.End();  
  36.         }  
本文转载:CSDN博客

阅读排行

分类

归档