Excel导出的几种方式

1、html

前台html与js代码（文件：ExportExcelByHtml.aspx）：

 1 <html xmlns="http://www.w3.org/1999/xhtml">
 2 <head runat="server">
 3     <title></title>
 4 </head>
 5 <body>
 6     <form id="form1" runat="server">
 7     <div>
 8         <asp:LinkButton runat="server" OnClientClick="return tiggerAlert();" OnClick="Button1_Click">Matt Cheng</asp:LinkButton>
 9         <br />
10          <asp:HiddenField ID="ExportField" runat="server" />
11     </div>
12     </form>
13 </body>
14 </html>16 <script type="text/javascript">
17     function tiggerAlert() {
18         var html = "<html><head><style type=\"text/css\">#tt{color:green;}</style></head><body>"
19              + "<div>"
20              + "<table>"
21              + "<tr><th style='color:red;'>cheng</th><th>liu</th></tr>"
22              + "<tr><td id='tt'>7845</td><td>666</td></tr>"
23              + "</table>"
24              + "</div>"
25              + "</body><html>";
26         document.getElementById("ExportField").value = escape(html);
27 
28         return true;
29     }
30 </script>

注：注意控件LinkButton点击事件的用法，OnClientClick为前台事件（js），OnClick为后台事件（C#），当用户点击按钮，先响应OnClientClick，若OnClientClick的返回结果为true，则执行OnClick，否则不执行。

后台C#代码（ExportExcelByHtml.aspx.cs）

 1   public partial class ClientClick : System.Web.UI.Page
 2   {
 3       protected void Page_Load(object sender, EventArgs e)
 4       {
 6       }
 7 
 8       protected void Button1_Click(object sender, EventArgs e)
 9       {
10          string fileName = HttpUtility.UrlEncode("想你的夜") + DateTime.Now.ToString("yyyyMMdd") + ".xls";
11          Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);
12          Response.ContentType = "application/vnd.ms-excel";
13          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();
14          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);
15          oHtmlTextWriter.Write(HttpUtility.UrlDecode(this.ExportField.Value));
16          Response.Write(oStringWriter.ToString());
17          //Response.Write(HttpUtility.UrlDecode(this.ExportField.Value));
18          Response.End();
19       }
20   }

注：13-16行的效果和17行相同。但通常采用前一种方式，原因未知。。。。

html导出excel的特点：能够通过样式（内部样式表与内联样式）设置excel的格式。

2、数据源

前台html与js代码（文件：ExportExcelByOle.aspx）：

 1 <html xmlns="http://www.w3.org/1999/xhtml">
 2 <head runat="server">
 3     <title></title>
 4     <script type="text/javascript">
 5         function exportExcel() {
 6             document.getElementById("iframe0").src = "ttttt.aspx?timeTick=" + ((new Date()) - (new Date(1, 1, 1)));
 7         }
 8     </script>
 9 </head>
10 <body>
11     <form id="form1" runat="server">
12     <div>
13     <input type="button" value="button" onclick="exportExcel()" />
14     <iframe id="iframe0" style="display:none;" src="" />
15     </div>
16     </form>
17 </body>
18 </html>

注：将js代码（即<script>标签）放到html后，在onclick中的exportExcel未定义（undefined），原因未知。。。。

web.config配置:

<httpHandlers>
   <add path="ttttt.aspx" verb="*" type="ExcelExportTest.ExcelExportHandler"/>
</httpHandlers>

注：iis对http请求的响应方式详见Http Handler介绍。

后台C#代码（ExcelExportHandler.cs）:

  1 public class ExcelExportHandler : IHttpHandler
  2     {
  3         private HttpContext context;
  4 
  5         public bool IsReusable { get { return true; } }
  6 
  7         public void ProcessRequest(HttpContext context)
  8         {
  9             this.context = context;
 10 
 11             DataRow row;
 12             DataSet ds = new DataSet();
 13             DataTable dt = new DataTable();
 14             dt.TableName = "table";
 15             dt.Columns.Add("cheng");
 16             dt.Columns.Add("liu");
 17             dt.Columns.Add("杨");
 18             row = dt.NewRow();
 19             row["cheng"] = "789";
 20             row["liu"] = "tttt";
 21             row["杨"] = " 面包";
 22             dt.Rows.Add(row);
 23             row = dt.NewRow();
 24             row["cheng"] = "tt";
 25             row["liu"] = "ttpppptt";
 26             row["杨"] = "可乐";
 27             dt.Rows.Add(row);
 28             ds.Tables.Add(dt);
 29 
 30             string rootPath = AppDomain.CurrentDomain.BaseDirectory + "files\\";
 31             if (!Directory.Exists(rootPath))
 32                 Directory.CreateDirectory(rootPath);
 33             string filePath = rootPath + DateTime.Now.Ticks.ToString() + ".xls";
 34             //File.Create(filePath);
 35             DataSetToExcel(ds, filePath);
 36             WriteExcelFile(filePath, "成功");
 37             if (File.Exists(filePath))
 38             {
 39                 FileInfo fInfo = new FileInfo(filePath);
 40                 fInfo.Attributes = FileAttributes.Normal;
 41                 File.Delete(filePath);
 42             }
 43         }
 44 
 45         private void DataSetToExcel(DataSet ds, string filePath)
 46         {
 47             using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=yes;IMEX=0\";"))
 48             {
 49                 if (conn.State != ConnectionState.Open)
 50                     conn.Open();
 51 
 52                 foreach (DataTable dt in ds.Tables)
 53                 {
 54                     OleDbCommand cmdCreateTable = new OleDbCommand("create table [table] ([cheng] varchar, [liu] varchar, [杨] varchar)", conn);
 55                     OleDbCommand cmdInsertRow = new OleDbCommand("insert into [table] ([cheng], [liu], [杨]) values(?, ?, ?)", conn);
 56                     cmdCreateTable.ExecuteNonQuery();
 57 
 58                     foreach (DataColumn dc in dt.Columns)
 59                     {
 60                         cmdInsertRow.Parameters.Add(new OleDbParameter(dc.ColumnName, ""));
 61                     }
 62                     foreach (DataRow dr in dt.Rows)
 63                     {
 64                         foreach (DataColumn dc in dt.Columns)
 65                         {
 66                             cmdInsertRow.Parameters[dc.ColumnName].Value = dr[dc.ColumnName];
 67                         }
 68                         cmdInsertRow.ExecuteNonQuery();
 69                     }
 70                 }
 71             }
 72         }
 73 
 74         private void WriteExcelFile(string filePath, string fileName)
 75         {
 76             if (File.Exists(filePath))
 77             {
 78                 FileStream fStream = new FileStream(filePath, FileMode.Open);
 79                 try
 80                 {
 81                     context.Response.Clear();
 82                     context.Response.ContentType = "application/vnd.ms-excel";
 83                     context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + context.Server.UrlEncode(fileName) + ".xls");
 84                     long len = fStream.Length;
 85                     long lCount = 0;
 86                     int bLen = 10000;
 87                     byte[] buffer = new byte[bLen];
 88                     while (lCount < len)
 89                     {
 90                         lCount += fStream.Read(buffer, 0, bLen);
 91                         context.Response.BinaryWrite(buffer);
 92                         context.Response.Flush();
 93                     }
 94                 }
 95                 finally
 96                 {
 97                     fStream.Close();
 98                 }
 99             }
100         }
101     }

注：OleDbConnection 连接数据源时自动创建文件，写数据的cmd语句和SQL相同。

数据源导出excel特点：可以在一个excel文件中写多个表格（sheet），但格式目前没有找到控制方法

3、Xml

xml的导出的调用方式与数据源导出类似，通过HttpHandler方式实现。

后台代码（ExcelExportByXml.cs）：

using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.IO;
using System.Text;
using System.Xml;

namespace FileExport
{
    public class ExcelExportByXml
    {
        private const string excelTemplate = "excelTable.xml";
        private HttpContext context;

        public ExcelExportByXml(HttpContext httpContext)
        {
            this.context = httpContext;
        }

        public void ExportExcel(object data)
        {
            List<DataEntity> list = data as List<DataEntity>;
            string moduleFile = GetExcelModule();

            StringBuilder tableString = new StringBuilder("<Column ss:Width=\"90\"/>");
            tableString.Append("<Column ss:Width=\"90\"/>");
            tableString.Append("<Column ss:Width=\"90\"/>");

            string[] headers = { "Data1", "Data2", "Data3" };
            SetTableHeader(tableString, headers);

            foreach (DataEntity info in list)
            {
                tableString.Append("<Row>");
                tableString.Append("<Cell ss:StyleID=\"s65\"><Data ss:Type=\"String\">" + info.Data1 + "</Data></Cell>");
                tableString.Append("<Cell ss:StyleID=\"s66\"><Data ss:Type=\"String\">" + info.Data2 + "</Data></Cell>");
                tableString.Append("<Cell ss:StyleID=\"s65\"><Data ss:Type=\"String\">" + info.Data3 + "</Data></Cell>");
                tableString.Append("</Row>");
            }

            moduleFile = String.Format(moduleFile, "", "爱你一万年", tableString.ToString());
            WriteExcelFile(moduleFile, DateTime.Now.Ticks.ToString());
        }

        private string GetExcelModule()
        {
            string res = "";
            string filePath = AppDomain.CurrentDomain.BaseDirectory + excelTemplate;
            using (StreamReader sr = new StreamReader(filePath))
            {
                res = sr.ReadToEnd();
                sr.Close();
            }
            return res;
        }

        private void SetTableHeader(StringBuilder tableString, string[] headers)
        {
            if (tableString != null && headers.Length > 0)
            {
                tableString.Append("<Row ss:Index=\"2\">");
                foreach (string header in headers)
                {
                    tableString.Append("<Cell ss:StyleID=\"s63\"><Data ss:Type=\"String\">");
                    tableString.Append(header);
                    tableString.Append("</Data></Cell>");
                }
                tableString.Append("</Row>");
            }
        }

        private void WriteExcelFile(string fileString, string fileName)
        {
            if (!String.IsNullOrEmpty(fileString))
            {
                XmlDocument doc = new XmlDocument();
                doc.LoadXml(fileString);
                XmlWriterSettings xmlSettings = new XmlWriterSettings();
                xmlSettings.Indent = true;
                xmlSettings.Encoding = Encoding.UTF8;
                xmlSettings.OmitXmlDeclaration = false;
                MemoryStream ms = new MemoryStream();
                using (XmlWriter xw = XmlWriter.Create(ms, xmlSettings))
                {
                    doc.WriteTo(xw);
                    xw.Close();
                }
                WriteFile(ms, fileName);
            }
        }

        private void WriteFile(MemoryStream ms, string fileName)
        {
            try
            {
                this.context.Response.ContentType = "application/vnd.ms-excel";
                this.context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + this.context.Server.UrlEncode(fileName) + ".xls;charset=utf8");
                ms.Position = 0;
                int size = 10*1024; // 10K
                byte[] buffer = new byte[size];
                while (ms.Read(buffer, 0, size) > 0)
                {
                    this.context.Response.BinaryWrite(buffer);
                    this.context.Response.OutputStream.Flush();
                }
                
            }
            catch (Exception ex)
            {
            }
            finally
            {
                ms.Close();
            }
        }
    }

    class DataEntity
    {
        public string Data1 { set; get; }
        public string Data2 { set; get; }
        public string Data3 { set; get; }
    }
}

模板（excelTable.xml）

<?xml version="1.0" encoding="utf-8" ?>
<?mso-application progid="Excel.Sheet"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:o="urn:schemas-microsoft-com:office:office"
 xmlns:x="urn:schemas-microsoft-com:office:excel"
 xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
 xmlns:html="http://www.w3.org/TR/REC-html40">
  <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
    <Title>{0}</Title>
    <LastAuthor>Wind</LastAuthor>
    <Created>2013-11-22T06:50:15Z</Created>
    <LastSaved>2013-11-22T06:50:15Z</LastSaved>
    <Version>12.00</Version>
  </DocumentProperties>
  <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
    <WindowHeight>10005</WindowHeight>
    <WindowWidth>10005</WindowWidth>
    <WindowTopX>120</WindowTopX>
    <WindowTopY>135</WindowTopY>
    <ProtectStructure>False</ProtectStructure>
    <ProtectWindows>False</ProtectWindows>
  </ExcelWorkbook>
  <Styles>
    <Style ss:ID="s63">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000" ss:Bold="1"/>
    </Style>
    <Style ss:ID="s64">
      <Alignment ss:Horizontal="Right" ss:Vertical="Center" ss:WrapText="1"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s65">
      <Alignment ss:Horizontal="Center" ss:Vertical="Center" ss:WrapText="1"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000"/>
    </Style>
    <Style ss:ID="s66">
      <Alignment ss:Horizontal="Left" ss:Vertical="Center" ss:WrapText="1"/>
      <Borders>
        <Border ss:Position="Bottom" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Left" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Right" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
        <Border ss:Position="Top" ss:LineStyle="Continuous" ss:Weight="1"
         ss:Color="#000000"/>
      </Borders>
      <Font ss:FontName="宋体" x:CharSet="134" ss:Color="#000000"/>
    </Style>
  </Styles>
  <Worksheet ss:Name="{1}">
    <Table x:FullColumns="1"
     x:FullRows="1" ss:DefaultColumnWidth="54" ss:DefaultRowHeight="13.5">{2}</Table>
    <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
      <Print>
        <ValidPrinterInfo/>
        <VerticalResolution>0</VerticalResolution>
      </Print>
      <Selected/>
      <Panes>
        <Pane>
          <Number>3</Number>
          <RangeSelection>R1C1:R1C10</RangeSelection>
        </Pane>
      </Panes>
      <ProtectObjects>False</ProtectObjects>
      <ProtectScenarios>False</ProtectScenarios>
    </WorksheetOptions>
  </Worksheet>
</Workbook>

调用方法（在HttpHandler中）：

List<DataEntity> list = new List<DataEntity>();
DataEntity de1 = new DataEntity { Data1 = "pp", Data2 = "rrr", Data3 = "123" };
DataEntity de2 = new DataEntity { Data1 = "pp", Data2 = "rrr", Data3 = "123" };
list.Add(de1);
list.Add(de2);
ExcelExportByXml excelExport = new ExcelExportByXml(this.context);
excelExport.ExportExcel(list);

该方法的优点：可以充分控制excel中表格的样式。

注：该方法使用Office Open XML技术，目前没有找到较好的参考文档，为了查找要达到的效果对应的标签，可以新建excel文档，编辑相应的效果，之后再另存为xml格式的文本，查看对应的标签即可。

4、GridView

该方法同样使用HttpHandler，代码结构与数据源相同。

    // 使用GridView
    if (ds.Tables[0].Rows.Count > 0)
    {
        //当前对话 
        System.Web.HttpContext curContext = System.Web.HttpContext.Current;
        //IO用于导出并返回excel文件 
        System.IO.StringWriter strWriter = null;
        System.Web.UI.HtmlTextWriter htmlWriter = null;

        //设置编码和附件格式 
        //System.Web.HttpUtility.UrlEncode(FileName, System.Text.Encoding.UTF8)作用是方式中文文件名乱码 
        curContext.Response.AddHeader("content-disposition", "attachment;filename=" + System.Web.HttpUtility.UrlEncode("定投排行榜", System.Text.Encoding.UTF8) + ".xls");
        curContext.Response.ContentType = "application nd.ms-excel";
        curContext.Response.ContentEncoding = System.Text.Encoding.UTF8;
        curContext.Response.Charset = "GB2312";

        //导出Excel文件 
        strWriter = new System.IO.StringWriter();
        htmlWriter = new System.Web.UI.HtmlTextWriter(strWriter);

        //为了解决dgData中可能进行了分页的情况,需要重新定义一个无分页的GridView 
        GridView gvExport = new GridView();
        gvExport.DataSource = ds.Tables[0].DefaultView;
        gvExport.AllowPaging = false;
        gvExport.DataBind();

        //下载到客户端 
        gvExport.RenderControl(htmlWriter);
        curContext.Response.Write(strWriter.ToString());
        curContext.Response.End();
    }

该方法的本质与html相同，即生成html代码并输出到前台。

优点：html代码通过控件自动生成，使用简单。

读取excel的一种简单方式：

using System;
using System.Data;
using System.Data.OleDb;

namespace ExcelReading
{
    class Program
    {
        static void Main(string[] args)
        {
            DataSet ds = GetDateSet(@"C:\Users\jcheng.matt\Desktop\test.xlsx");
            DataTable dt = ds.Tables[0];
        }

        public static DataSet GetDateSet(string filePath)
        {
            string fileType = System.IO.Path.GetExtension(filePath);
            if (string.IsNullOrEmpty(fileType))
            {
                return null;
            }

            string connStr = string.Empty;
            if (fileType == ".xls")
                connStr = "Provider=Microsoft.Jet.OLEDB.4.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 8.0;HDR=YES;IMEX=1\"";
            else
                connStr = "Provider=Microsoft.ACE.OLEDB.12.0;" + "Data Source=" + filePath + ";" + ";Extended Properties=\"Excel 12.0;HDR=YES;IMEX=1\"";

            OleDbConnection conn = null;
            OleDbDataAdapter adapter = new OleDbDataAdapter();
            DataSet ds = new DataSet();
            try
            {
                // 初始化连接，并打开
                conn = new OleDbConnection(connStr);
                conn.Open();

                // 获取数据源的表定义元数据                        
                DataTable dtSheetName = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "TABLE" });

                string sheetName = string.Empty;
                string sql = "Select * FROM [{0}]";
                for (int i = 0; i < dtSheetName.Rows.Count; i++)
                {
                    sheetName = (string)dtSheetName.Rows[i]["TABLE_NAME"];
                    adapter.SelectCommand = new OleDbCommand(String.Format(sql, sheetName), conn);
                    DataSet dsItem = new DataSet();
                    adapter.Fill(dsItem, sheetName);
                    ds.Tables.Add(dsItem.Tables[0].Copy());
                }
            }
            catch (Exception ex)
            {
                Console.WriteLine(ex.Message);
            }
            finally
            {
                // 关闭连接
                if (conn.State == ConnectionState.Open)
                {
                    conn.Close();
                    adapter.Dispose();
                    conn.Dispose();
                }
            }
            return ds;
        }
    }
}

View Code

posted @ 2015-03-03 15:18 Matt_Cheng 阅读(1280) 评论(0) 编辑收藏举报

刷新页面返回顶部

Matt_Cheng

知行合一

Excel导出的几种方式

公告