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

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

2016-03-03
2017-11-22 更新
2019-09-21 更新

*********************************************************************************
2017-11-22

目前 NuGet 的 DocumentFormat.OpenXml 提供 v2.7.2 版，Open-XML-SDK 也提供到 v2.7.2，兩者都是微軟提供的；但獨立下載的 OpenXML SDK 仍只有 2.5 版。

*********************************************************************************

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

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

缺點
(1) 需要 Microsoft 元件 OpenXML SDK 2.5
(2) 檔案會在網站上先產生，網站上必須有目錄可以放匯出檔案。

(3) 匯出檔案名稱如果固定，每個人按下按鈕後都回重新產生相同名稱檔案，後操作的檔案會覆蓋掉前人的。匯出檔案名稱如果不固定，網站上會有一堆匯出檔案占用空間。這點要考慮。

歡迎使用 Open XML SDK 2.5 for Office
https://msdn.microsoft.com/zh-tw/library/office/bb448854.aspx

Open XML SDK 2.5 for Microsoft Office 下載
2012/11/20
https://www.microsoft.com/en-us/download/details.aspx?id=30425

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 < girdViewtest.HeaderRow.Cells.Count; i++)
{
dt.Columns.Add(girdViewtest.HeaderRow.Cells[i].Text.Replace(" ", ""));
}

// 如果 GridView 設定為可排序，header 就要改抓 LinkButton 的文字
for (int i = 0; i < gridView.HeaderRow.Cells.Count; i++)
{
if (gridView.HeaderRow.Cells[i].HasControls())
{
// 如果 GridView 設定為可排序，header 就要改抓 LinkButton 的文字
// Linkbutton is in index 0 of the control
if (gridView.HeaderRow.Cells[i].Controls[0] is LinkButton)
{
LinkButton headerControl = gridView.HeaderRow.Cells[i].Controls[0] as LinkButton;
string headerName = headerControl.Text;
}
}
}

foreach (GridViewRow row in girdViewtest.Rows)
{
DataRow datarow = dt.NewRow();
for (int i = 0; i < row.Cells.Count; i++)
{
datarow[i] = row.Cells[i].Text.Replace(" ", "");
}
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();
}
}
}
}

********************************************************************************

2017-03-22 發現 NuGet 有 OpenXML 可以安裝
下圖為 Visual Studio 2017 畫面

專案URL
https://social.msdn.microsoft.com/Forums/office/en-US/home?forum=oxmlsdk

Unoffical packaging of Microosft's OpenXML SDK 2.5 => 顯示這個 NuGet 封裝不是微軟官方提供，但是 OpenXML SDK 2.5 是微軟提供的。

(完)

相關

操作方法：將文字插入試算表文件 (Open XML SDK) 中的儲存格
https://msdn.microsoft.com/zh-tw/library/office/cc861607.aspx

Generating Excel 2010 Workbooks by using the Open XML SDK 2.0
https://msdn.microsoft.com/en-us/library/office/hh180830(v=office.14).aspx

浮雲雅築

2016年3月3日星期四

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

沒有留言:

張貼留言

2016年3月3日 星期四

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

沒有留言:

張貼留言

2016年3月3日星期四