.net导出Excel

最新推荐文章于 2024-01-19 16:36:54 发布

gdjlc

最新推荐文章于 2024-01-19 16:36:54 发布

阅读量9.4k

点赞数 2

分类专栏： ASP.NET 文章标签： .net excel string buffer generation

本文链接：https://blog.csdn.net/gdjlc/article/details/5810311

版权

ASP.NET 专栏收录该内容

54 篇文章 0 订阅

订阅专栏

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

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

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

备注：读取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文件属性是只读的，解决方法是去掉只读属性：

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

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

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>" +
                                          "<!--[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();
        }

gdjlc

关注

2
点赞
踩
11

收藏

觉得还不错? 一键收藏
3
评论
.net导出Excel

综合参考了网上的方法，生成Excel文件提供下载，然后删除生成的Excel文件。1、引用Microsoft.Office.Interop.Excel;(属性里的嵌入互操作类型改为Fasle)2、Default10.aspx 3、Default10.aspx.cs using System;
复制链接

扫一扫