2016年3月3日 星期四

[研究][C#][ASP.NET] GridView 匯出成 Excel (.xlsx) (使用 OpenXML SDK 2.5)

[研究][C#][ASP.NET]  GridView 匯出成 Excel (.xlsx) (使用 OpenXML SDK 2.5)

2016-03-03

這種方式是利用 OpenXML SDK 2.5 建立 .xlsx 格式檔案,然後把檔案送出的方法。

優點是電腦不需要安裝 Excel 商業版。

缺點
(1) 需要 3rd Party 元件 OpenXML SDK 2.5
(2) 檔案會在網站上先產生,網站上必須有目錄可以放匯出檔案。
(3) 匯出檔案名稱如果固定,每個人按下按鈕後都回重新產生相同名稱檔案,後操作的檔案會覆蓋掉前人的。匯出檔案名稱如果不固定,網站上會有一堆匯出檔案占用空間。這點要考慮。

Visual Studio 2015 加入參考方法如下







GridViewExportToXLSXDemo.aspx 內容如下


<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="GridViewExportToXLSXDemo.aspx.cs" Inherits="WebApplication3.GridViewExportToXLSXDemo" %>

<!DOCTYPE html>

<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<meta http-equiv="Content-Type" content="text/html; charset=utf-8"/>
    <title></title>
</head>
<body>
    <form id="form1" runat="server">
    <div>
        <asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:TestDBConnectionString %>" SelectCommand="SELECT * FROM [TestTable]"></asp:SqlDataSource>
        <asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataSourceID="SqlDataSource1">
            <Columns>
                <asp:BoundField DataField="no" HeaderText="no" InsertVisible="False" ReadOnly="True" SortExpression="no" />
                <asp:BoundField DataField="myname" HeaderText="myname" SortExpression="myname" />
                <asp:BoundField DataField="addr" HeaderText="addr" SortExpression="addr" />
            </Columns>
        </asp:GridView>
        <asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" />
    </div>
    </form>
</body>
</html>



GridViewExportToXLSXDemo.aspx.cs 內容如下


using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.IO;
using System.Text;

#region OpenXML SDK
// 用 Office OpenXML SDK 2.0.
// http://www.codeproject.com/Tips/366446/Export-GridView-Data-to-Excel-using-OpenXml
//下載和安裝 Open XML SDK 2.5 for Microsoft Office
// https://www.microsoft.com/en-us/download/details.aspx?id=30425
//加入參考  C:\Program Files (x86)\Open XML SDK\V2.5\lib\DocumentFormat.OpenXml.dll
//加入參考  C:\Program Files\Reference Assemblies\Microsoft\Framework\v3.0\WindowsBase.dll
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
#endregion OpenXML SDK

namespace WebApplication3
{
    public partial class GridViewExportToXLSXDemo : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
        }
        protected void Button1_Click(object sender, EventArgs e)
        {
            GridViewExportToXLSX(GridView1);
        }
        protected void GridViewExportToXLSX(GridView gridViewExport)
        {
            DataTable table = new DataTable();   // create an empty data table
            table = CreateDataTable(gridViewExport);
            string exportPath = Server.MapPath("exportedfiles\\");
            if (!Directory.Exists(exportPath))
            {
                Directory.CreateDirectory(exportPath);
            }
            //string excelfile = Path.GetTempPath() +Guid.NewGuid().ToString() + ".xlsx";
            string excelfile = exportPath + "Export.xlsx";
            //ExportDataTable(System.Data.DataTable table, string destination);
            ExportDataTable(table, excelfile);

            string fileName = "匯出檔名";
            Response.Clear();
            //Response.Buffer = true;
            //Response.Charset = "";
            //Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", "Student.xls"));
            //Response.AddHeader("Content-Disposition", "attachment;filename=" + fileName);
            //Response.AddHeader("content-disposition", string.Format("attachment;filename={0}", fileName));
            //Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode("中文測試", System.Text.Encoding.UTF8) + ".xls\"");
            // 解決中文檔案名稱亂碼
            //Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) );
            // 時間有空白,不要編碼比較好
            Response.ContentEncoding = Encoding.GetEncoding(950);//950就是所謂的BIG5
            Response.AddHeader("Content-Disposition", "attachment;filename=\"" + HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx\"");
            //Response.AddHeader("Content-Disposition", "attachment;filename=\"" + excelfile +"\"");
            Response.TransmitFile(excelfile);
            Response.End();
        }
        // ****************************************************************************************            
        private DataTable CreateDataTable(GridView girdViewtest)
        {
            DataTable dt = new DataTable();

            for (int i = 0; i < GridView1.HeaderRow.Cells.Count; i++)
            {
                dt.Columns.Add(GridView1.HeaderRow.Cells[i].Text.Replace("&nbsp;", ""));
            }
            foreach (GridViewRow row in GridView1.Rows)
            {
                DataRow datarow = dt.NewRow();
                for (int i = 0; i < row.Cells.Count; i++)
                {
                    datarow[i] = row.Cells[i].Text.Replace("&nbsp;", "");
                }
                dt.Rows.Add(datarow);
            }
            return dt;
        }
        // ****************************************************************************************
        private void ExportDataTable(System.Data.DataTable table, string destination)
        {
            using (var workbook = SpreadsheetDocument.Create(destination, DocumentFormat.OpenXml.SpreadsheetDocumentType.Workbook))
            {
                var workbookPart = workbook.AddWorkbookPart();

                workbook.WorkbookPart.Workbook = new DocumentFormat.OpenXml.Spreadsheet.Workbook();

                workbook.WorkbookPart.Workbook.Sheets = new DocumentFormat.OpenXml.Spreadsheet.Sheets();

                var sheetPart = workbook.WorkbookPart.AddNewPart<WorksheetPart>();
                var sheetData = new DocumentFormat.OpenXml.Spreadsheet.SheetData();
                sheetPart.Worksheet = new DocumentFormat.OpenXml.Spreadsheet.Worksheet(sheetData);

                DocumentFormat.OpenXml.Spreadsheet.Sheets sheets = workbook.WorkbookPart.Workbook.GetFirstChild<DocumentFormat.OpenXml.Spreadsheet.Sheets>();
                string relationshipId = workbook.WorkbookPart.GetIdOfPart(sheetPart);

                uint sheetId = 1;
                if (sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Count() > 0)
                {
                    sheetId =
                        sheets.Elements<DocumentFormat.OpenXml.Spreadsheet.Sheet>().Select(s => s.SheetId.Value).Max() + 1;
                }

                DocumentFormat.OpenXml.Spreadsheet.Sheet sheet = new DocumentFormat.OpenXml.Spreadsheet.Sheet() { Id = relationshipId, SheetId = sheetId, Name = "TableExport" };
                sheets.Append(sheet);

                DocumentFormat.OpenXml.Spreadsheet.Row headerRow = new DocumentFormat.OpenXml.Spreadsheet.Row();

                List<String> columns = new List<string>();
                foreach (System.Data.DataColumn column in table.Columns)
                {
                    columns.Add(column.ColumnName);

                    DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                    cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                    cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(column.ColumnName);
                    headerRow.AppendChild(cell);
                }


                sheetData.AppendChild(headerRow);

                foreach (System.Data.DataRow dsrow in table.Rows)
                {
                    DocumentFormat.OpenXml.Spreadsheet.Row newRow = new DocumentFormat.OpenXml.Spreadsheet.Row();
                    foreach (String col in columns)
                    {
                        DocumentFormat.OpenXml.Spreadsheet.Cell cell = new DocumentFormat.OpenXml.Spreadsheet.Cell();
                        cell.DataType = DocumentFormat.OpenXml.Spreadsheet.CellValues.String;
                        cell.CellValue = new DocumentFormat.OpenXml.Spreadsheet.CellValue(dsrow[col].ToString());
                        newRow.AppendChild(cell);
                    }

                    sheetData.AppendChild(newRow);
                }

                workbookPart.Workbook.Save();

                workbook.Close();
            }
        }
    }
}
 


(完)

沒有留言:

張貼留言