[研究][ASP.NET]使用 OpenXML 2.12.2 匯出、寫入 .xlsx
2021-02-18
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 OpenXML 2.12.2
packages.conf
<?xml version="1.0" encoding="utf-8"?> <packages> <package id="DocumentFormat.OpenXml" version="2.12.2" targetFramework="net472" /> <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" /> </packages> |
Web.Config
<?xml version="1.0" encoding="utf-8"?>
<!--
如需如何設定 ASP.NET 應用程式的詳細資訊，請前往
https://go.microsoft.com/fwlink/?LinkId=169433
-->
<configuration>
<connectionStrings>
<add name="TestDBConnectionString" connectionString="Data Source=.;Initial Catalog=TestDB;User ID=sa;Password=P@ssw0rd"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<compilation debug="true" targetFramework="4.7.2"/>
<httpRuntime targetFramework="4.7.2"/>
</system.web>
<system.codedom>
<compilers>
<compiler language="c#;cs;csharp" extension=".cs"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.CSharpCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:default /nowarn:1659;1699;1701"/>
<compiler language="vb;vbs;visualbasic;vbscript" extension=".vb"
type="Microsoft.CodeDom.Providers.DotNetCompilerPlatform.VBCodeProvider, Microsoft.CodeDom.Providers.DotNetCompilerPlatform, Version=2.0.1.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35"
warningLevel="4" compilerOptions="/langversion:default /nowarn:41008 /define:_MYTYPE=\"Web\" /optionInfer+"/>
</compilers>
</system.codedom>
<appSettings>
<add key="TempFolder" value="D:\WWW\Temp" />
</appSettings>
</configuration> |
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportXlsxByOpenXMLSDKTest.Default" %>
<!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 [Table1]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="False" DataKeyNames="id" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="id" HeaderText="id" InsertVisible="False" ReadOnly="True" SortExpression="id" />
<asp:BoundField DataField="FieldText" HeaderText="FieldText" SortExpression="FieldText" />
<asp:BoundField DataField="FieldDateTime" HeaderText="FieldDateTime" SortExpression="FieldDateTime" />
<asp:BoundField DataField="FieldInt" HeaderText="FieldInt" SortExpression="FieldInt" />
<asp:CheckBoxField DataField="FieldBit" HeaderText="FieldBit" SortExpression="FieldBit" />
</Columns>
</asp:GridView>
<asp:Button ID="Button_Export_Xlsx_By_OpenXML" runat="server"
OnClick="Button_Export_Xlsx_By_OpenXML_Click" Text="匯出(.xlsx)" /><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html> |
Default.aspx.cs
using DocumentFormat.OpenXml; using DocumentFormat.OpenXml.Packaging; using DocumentFormat.OpenXml.Spreadsheet; using System; using System.Collections.Generic; using System.Configuration; using System.Data.SqlClient; using System.IO; using System.Linq; using System.Text; using System.Web; using System.Web.Configuration; using System.Web.UI; using System.Web.UI.WebControls; namespace ExportXlsxByOpenXMLSDKTest { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Export_Xlsx_By_OpenXML_Click(object sender, EventArgs e) { /* Open XML SDK 2.5 for Microsoft Office https://www.microsoft.com/en-us/download/details.aspx?id=30425 微軟官方下載只提供到 2.5 版，NuGet 才能下載到更新版。 另外 OpenXML 在不同版本時，API function method 用法不完全相同。 Welcome to the Open XML SDK 2.5 for Office 2017/11/01 https://docs.microsoft.com/zh-tw/office/open-xml/open-xml-sdk DocumentFormat.OpenXml 2.7.2 https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.7.2 DocumentFormat.OpenXml 2.8.1 https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.8.1 DocumentFormat.OpenXml 2.12.2 https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.12.2 Import https://docs.microsoft.com/en-us/office/open-xml/how-to-parse-and-read-a-large-spreadsheet Export https://docs.microsoft.com/en-us/office/open-xml/how-to-create-a-spreadsheet-document-by-providing-a-file-name Insert text into a cell in a spreadsheet document(Open XML SDK) https://docs.microsoft.com/zh-tw/office/open-xml/how-to-insert-text-into-a-cell-in-a-spreadsheet */ string mainFileName = "匯出檔案名稱"; string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + @"\"; if (!System.IO.Directory.Exists(fd)) { System.IO.Directory.CreateDirectory(fd); } string dateTimeString = DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff"); // 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼 string outFileName = HttpUtility.UrlEncode( mainFileName, System.Text.Encoding.UTF8) + "-" + dateTimeString + ".xlsx"; // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼 // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼 string dirFileName = fd + mainFileName + "-" + dateTimeString + ".xlsx"; try { Response.Clear(); Response.Buffer = true; Response.Charset = ""; #region Export ODS // [EXCEL] Excel打開是亂碼？快速找回資料的最好方法！ // http://blog.e-happy.com.tw/excel-excel%E6%89%93%E9%96%8B%E6%98%AF%E4%BA%82%E7%A2%BC%EF%BC%9F%E5%BF%AB%E9%80%9F%E6%89%BE%E5%9B%9E%E8%B3%87%E6%96%99%E7%9A%84%E6%9C%80%E5%A5%BD%E6%96%B9%E6%B3%95%EF%BC%81/ // Excel開啟CSV時的中文編碼問題補遺 //https://blog.darkthread.net/blog/csv-encoding-again/ //Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet"; // ODF/.ods //Response.AddHeader("content-disposition", "attachment;filename=" + outFileName); using (MemoryStream myMemoryStream = new MemoryStream()) { //StreamWriter odsFileStreamWriter = new StreamWriter(MyMemoryStream); //StreamReader odsFileStreamReader = new StreamReader(new MemoryStream()); string dataText = ""; SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(myMemoryStream, SpreadsheetDocumentType.Workbook, true); WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart(); workbookpart.Workbook = new Workbook(); // Add a WorksheetPart to the WorkbookPart. WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>(); worksheetPart.Worksheet = new Worksheet(new SheetData()); // Add Sheets to the Workbook. Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets()); // Append a new worksheet and associate it with the workbook. Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "工作表1" }; sheets.Append(sheet); // Get the sheetData cell table. SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>(); // Add a column row to the cell table. Row colrow; colrow = new Row() { RowIndex = 1 }; sheetData.Append(colrow); // 定義欄位名稱列 string[] columnNameArray = { "id", "FieldText", "FieldDateTime", "FieldInt", "FieldBit" }; // Column number must be between 1 and 16384 for (int i = 0; i < columnNameArray.Count(); i++) { Cell CReceiptID = new Cell(); CReceiptID.CellValue = new CellValue(columnNameArray[i]); CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String); colrow.InsertAt(CReceiptID, i); //string debugValue = sheetData.Cell(i,j).GetString(); } // 讀取資料，資料寫入「工作表1」 string queryString = @" --DECLARE @FieldText nvarchar(50) --SET @FieldText=N'abc' SELECT * FROM [TestDB].[dbo].[Table1] "; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.Clear(); //command.Parameters.AddWithValue("@FieldText", queryFieldText); connection.Open(); SqlDataReader reader = command.ExecuteReader(); // Add rows to the cell table UInt32Value DataIndex = new UInt32Value(); DataIndex.Value = 2; // 1 是標題列 //int rowIndex = 2; // 1 是標題列 while (reader.Read()) { Row row; row = new Row() { RowIndex = DataIndex.Value }; sheetData.Append(row); for (int i = 0; i < reader.FieldCount; i++) { //reader[i]的 i 要從 0 開始 dataText = reader[i].ToString(); // 規則運算式語言 - 快速參考 | Microsoft Docs // https://docs.microsoft.com/zh-tw/dotnet/standard/base-types/regular-expression-language-quick-reference // 換掉特殊字元 (自己評估) dataText = dataText.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "，").Replace("'", "′").Replace(@"""", "”"); dataText = dataText.Replace("\a", "").Replace("\b", "").Replace("\t", "").Replace("\v", "").Replace("\f", "").Replace("\\", ""); Cell CReceiptID = new Cell(); CReceiptID.CellValue = new CellValue(dataText); CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String); row.InsertAt(CReceiptID, i); } DataIndex.Value++; } Response.Clear(); Response.Buffer = true; Response.Charset = ""; //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=\"" + outFileName); //'Excel 2003 : "application/vnd.ms-excel" //'Excel 2007 : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet" //Extension MIME Type //.xlsx application/vnd.openxmlformats-officedocument.spreadsheetml.sheet //.xltx application/vnd.openxmlformats-officedocument.spreadsheetml.template //.potx application/vnd.openxmlformats-officedocument.presentationml.template //.ppsx application/vnd.openxmlformats-officedocument.presentationml.slideshow //.pptx application/vnd.openxmlformats-officedocument.presentationml.presentation //.sldx application/vnd.openxmlformats-officedocument.presentationml.slide //.docx application/vnd.openxmlformats-officedocument.wordprocessingml.document //.dotx application/vnd.openxmlformats-officedocument.wordprocessingml.template //.xlam application/vnd.ms-excel.addin.macroEnabled.12 //.xlsb application/vnd.ms-excel.sheet.binary.macroEnabled.12 //Response.ContentType = "application/ms-excel"; Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"; //Response.TransmitFile(@"C:\Program Files (x86)\IIS Express\Export.xlsx"); //dirFileName = dirFileName.Replace('\\', '/'); //轉換 Windows, Linux 不同路徑斜線 //dirFileName = dirFileName.Replace("/", "\\"); // 轉換斜線 / 成 \ ，因 \ 是特殊符號，要以 \\ 表示 // 方法1: 直接輸出 worksheetPart.Worksheet.Save(); workbookpart.Workbook.Save(); spreadsheetDocument.SaveAs(dirFileName); // 方法2: 寫入檔案再輸出 spreadsheetDocument.Close(); myMemoryStream.WriteTo(Response.OutputStream); Response.Flush(); // // 方法2: 寫入檔案再輸出 //Response.TransmitFile(dirFileName); // 把存檔輸出 // 3.輸出結束 Response.End(); Label_MSG1.ForeColor = System.Drawing.Color.Green; Label_MSG1.Text = "匯出成功。"; // 上面 Response 輸出檔案，所以下面 JavaScript alert 是不會跳出的 //Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>"); } } #endregion Export ODS } catch (Exception ex) { Label_MSG1.ForeColor = System.Drawing.Color.Red; string exMsg = "不明錯誤。"; if (ex != null) { exMsg = ex.ToString(); } Label_MSG1.Text = exMsg; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出失敗。');</script>"); // Exception Message 內容可能導致 JavaScript alert 無法顯示，所以下面這行可能無法跳出對話盒視窗。 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>"); } } // ---------- } } |
不過根據以往經驗，元件直接產生的 .xlsx，和用 Excel 建立的 .xlsx，實際的檔案骨架 schema 好像沒有 100% 相同，前者用某些元件或版本匯入可能正常或會出錯，但前者用 Microsoft Excel 軟體直接開啟正常。
或可考慮先用 Excel 2016 或 Excel 2019 做好一個沒填寫內容的 活頁簿 (Workbook) 和 工作表1 (Worksheet)，複製後 (避免多人同開開起讀寫)，再拿來寫入、關閉、匯出。
(完)
