Excel导出的几种方式

2022-09-27 18:49:52

1、html

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

 <html xmlns="http://www.w3.org/1999/xhtml">

 <head runat="server">

     <title></title>

 </head>

 <body>

     <form id="form1" runat="server">

     <div>

         <asp:LinkButton runat="server" OnClientClick="return tiggerAlert();" OnClick="Button1_Click">Matt Cheng</asp:LinkButton>

         <br />

          <asp:HiddenField ID="ExportField" runat="server" />

     </div>

     </form>

 </body>

 </html> <script type="text/javascript">

     function tiggerAlert() {

         var html = "<html><head><style type=\"text/css\">#tt{color:green;}</style></head><body>"

              + "<div>"

              + "<table>"

              + "<tr><th style='color:red;'>cheng</th><th>liu</th></tr>"

              + "<tr><td id='tt'>7845</td><td>666</td></tr>"

              + "</table>"

              + "</div>"

              + "</body><html>";

         document.getElementById("ExportField").value = escape(html);

         return true;

     }

 </script>

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

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

   public partial class ClientClick : System.Web.UI.Page

   {

       protected void Page_Load(object sender, EventArgs e)

       {

       }

       protected void Button1_Click(object sender, EventArgs e)

       {

          string fileName = HttpUtility.UrlEncode("想你的夜") + DateTime.Now.ToString("yyyyMMdd") + ".xls";

          Response.AppendHeader("Content-Disposition", "attachment;filename=" + fileName);

          Response.ContentType = "application/vnd.ms-excel";

          System.IO.StringWriter oStringWriter = new System.IO.StringWriter();

          System.Web.UI.HtmlTextWriter oHtmlTextWriter = new System.Web.UI.HtmlTextWriter(oStringWriter);

          oHtmlTextWriter.Write(HttpUtility.UrlDecode(this.ExportField.Value));

          Response.Write(oStringWriter.ToString());

          //Response.Write(HttpUtility.UrlDecode(this.ExportField.Value));

          Response.End();

       }

   }

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

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

 2、数据源

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

 <html xmlns="http://www.w3.org/1999/xhtml">

 <head runat="server">

     <title></title>

     <script type="text/javascript">

         function exportExcel() {

             document.getElementById("iframe0").src = "ttttt.aspx?timeTick=" + ((new Date()) - (new Date(1, 1, 1)));

         }

     </script>

 </head>

 <body>

     <form id="form1" runat="server">

     <div>

     <input type="button" value="button" onclick="exportExcel()" />

     <iframe id="iframe0" style="display:none;" src="" />

     </div>

     </form>

 </body>

 </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）:

 public class ExcelExportHandler : IHttpHandler

     {

         private HttpContext context;

         public bool IsReusable { get { return true; } }

         public void ProcessRequest(HttpContext context)

         {

             this.context = context;

             DataRow row;

             DataSet ds = new DataSet();

             DataTable dt = new DataTable();

             dt.TableName = "table";

             dt.Columns.Add("cheng");

             dt.Columns.Add("liu");

             dt.Columns.Add("杨");

             row = dt.NewRow();

             row["cheng"] = "";

             row["liu"] = "tttt";

             row["杨"] = " 面包";

             dt.Rows.Add(row);

             row = dt.NewRow();

             row["cheng"] = "tt";

             row["liu"] = "ttpppptt";

             row["杨"] = "可乐";

             dt.Rows.Add(row);

             ds.Tables.Add(dt);

             string rootPath = AppDomain.CurrentDomain.BaseDirectory + "files\\";

             if (!Directory.Exists(rootPath))

                 Directory.CreateDirectory(rootPath);

             string filePath = rootPath + DateTime.Now.Ticks.ToString() + ".xls";

             //File.Create(filePath);

             DataSetToExcel(ds, filePath);

             WriteExcelFile(filePath, "成功");

             if (File.Exists(filePath))

             {

                 FileInfo fInfo = new FileInfo(filePath);

                 fInfo.Attributes = FileAttributes.Normal;

                 File.Delete(filePath);

             }

         }

         private void DataSetToExcel(DataSet ds, string filePath)

         {

             using (OleDbConnection conn = new OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + filePath + ";Extended Properties=\"Excel 8.0;HDR=yes;IMEX=0\";"))

             {

                 if (conn.State != ConnectionState.Open)

                     conn.Open();

                 foreach (DataTable dt in ds.Tables)

                 {

                     OleDbCommand cmdCreateTable = new OleDbCommand("create table [table] ([cheng] varchar, [liu] varchar, [杨] varchar)", conn);

                     OleDbCommand cmdInsertRow = new OleDbCommand("insert into [table] ([cheng], [liu], [杨]) values(?, ?, ?)", conn);

                     cmdCreateTable.ExecuteNonQuery();

                     foreach (DataColumn dc in dt.Columns)

                     {

                         cmdInsertRow.Parameters.Add(new OleDbParameter(dc.ColumnName, ""));

                     }

                     foreach (DataRow dr in dt.Rows)

                     {

                         foreach (DataColumn dc in dt.Columns)

                         {

                             cmdInsertRow.Parameters[dc.ColumnName].Value = dr[dc.ColumnName];

                         }

                         cmdInsertRow.ExecuteNonQuery();

                     }

                 }

             }

         }

         private void WriteExcelFile(string filePath, string fileName)

         {

             if (File.Exists(filePath))

             {

                 FileStream fStream = new FileStream(filePath, FileMode.Open);

                 try

                 {

                     context.Response.Clear();

                     context.Response.ContentType = "application/vnd.ms-excel";

                     context.Response.AppendHeader("Content-Disposition", "attachment;filename=" + context.Server.UrlEncode(fileName) + ".xls");

                     long len = fStream.Length;

                     long lCount = ;

                     int bLen = ;

                     byte[] buffer = new byte[bLen];

                     while (lCount < len)

                     {

                         lCount += fStream.Read(buffer, , bLen);

                         context.Response.BinaryWrite(buffer);

                         context.Response.Flush();

                     }

                 }

                 finally

                 {

                     fStream.Close();

                 }

             }

         }

     }

注：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 > )

            {

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

                int size = *; // 10K

                byte[] buffer = new byte[size];

                while (ms.Read(buffer, , size) > )

                {

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

DataEntity de2 = new DataEntity { Data1 = "pp", Data2 = "rrr", Data3 = "" };

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[].Rows.Count > )

    {

        //当前对话

        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[].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[];

        }

        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 = ; 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[].Copy());

                }

            }

            catch (Exception ex)

            {

                Console.WriteLine(ex.Message);

            }

            finally

            {

                // 关闭连接

                if (conn.State == ConnectionState.Open)

                {

                    conn.Close();

                    adapter.Dispose();

                    conn.Dispose();

                }

            }

            return ds;

        }

    }

}
上一篇：[转，讲的非常精彩]CIDR地址块及其子网划分（内含原始IP地址分类及其子网划分的介绍）


下一篇：orale 函数大全[转]