C#导出导入Excel（企业库）

OStack程序员社区-中国程序员成长平台 › 门户 › 编程› Node.js›Node.js教程

原作者: [db:作者] 来自: [db:来源] 收藏邀请

综合参考了网上的方法，生成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: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

稍微改了下


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>" 
                       
                    
                     
                      



鲜花




握手




雷人




路过




鸡蛋