2020-04-25
2020-04-27 整個 Code 大改 (因為 「非自動生成列」會比「自動生成列」、以及欄位是否 visible 問題)
2020-04-28 匯出檔名強化
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
**********
本文使用下面工具
Visual Studio Enterprise 2019 v16.5.4
NuGet 安裝 OpenXML 2.10. 1 (DocumentFormat.OpenXml)
Default.aspx
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication4.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 [MyTable]"></asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AllowPaging="True" AllowSorting="True"
AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
<%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
<asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
</Columns>
</asp:GridView>
<asp:Button ID="Button1" runat="server" Text="Button" OnClick="Button1_Click" /><br />
<br />
<asp:GridView ID="GridView2" runat="server" AllowPaging="True" AllowSorting="True"
Visible="false"
AutoGenerateColumns="True" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="myID" HeaderText="myID" SortExpression="myID" Visible="false" />
<%--<asp:BoundField DataField="myName" HeaderText="myName" SortExpression="myName" />--%>
<asp:BoundField DataField="purchaseDate" HeaderText="purchaseDate" SortExpression="purchaseDate" />
</Columns>
</asp:GridView>
<asp:Button ID="Button2" runat="server" Text="Button" OnClick="Button2_Click" /><br />
<asp:Label ID="Label1" 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.IO;
using System.Linq;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication4
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button1_Click(object sender, EventArgs e)
{
//不想匯出的列，可以隱藏掉 //GridView1.Columns[0].Visible = false;
GridViewExportToXlsxByOpenXML(GridView1);
}
protected void Button2_Click(object sender, EventArgs e)
{
GridViewExportToXlsxByOpenXML(GridView2);
}
private string GridViewExportToXlsxByOpenXML(GridView gridview)
{
// 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 year = DateTime.Now.Year.ToString();
string mainFileName = Session["jiguanname"].ToString() + "(" + Session["oid"].ToString() + ")" + year + "年設備清冊";
//DateTime.Now.ToString("yyyyMMddHHmmssfff") + ".xlsx";
// 使用者若很多，最好連 fff 也用上
//DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
// 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼
string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
// XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
// Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx";
try
{
GridView gv = gridview;
bool oldAllowPaging = gv.AllowPaging;
bool oldAllowSorting = gv.AllowSorting;
gv.AllowSorting = false; // 支援排序時，標題不是文字，要改抓 LinkButton 的文字，麻煩，不如暫時取消
gv.AllowPaging = false; // 分頁狀態下，只能抓到當頁的10筆，必須取消分頁
gv.DataBind();
SpreadsheetDocument spreadsheetDocument = null;
Response.Clear();
Response.Buffer = true;
Response.Charset = "";
Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
//Response.AddHeader("content-disposition", "attachment;filename=Export.xlsx");
Response.AddHeader("content-disposition", "attachment;filename=" + outFileName );
using (MemoryStream MyMemoryStream = new MemoryStream())
{
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);
int visibleIndex = 0;
// 標題純文字這樣抓
//GridView1.Columns.Count 是「非自動生成列數」。不管是否為 Visible。
//GridView1.HeaderRow.Cells.Count 是「自動生成列數」和「非自動生成列數」的總和。不管是否為 Visible。
//GridView1.Visible = false; 時，GridView1.HeaderRow.Cells[i].Visible 總是會 false。
// 「非自動生成列」會比「自動生成列」先顯示
// HeaderRow.Cells[i].Visible 不管是 true 或 false，回傳都 true
// gv.Columns[i].Visible 才回真正回傳 true 或 false
// 但是 gv.Columns[i] 只能抓「非自動生成列數」
for (int i = 0; i < gv.HeaderRow.Cells.Count; i++)
{
if (i < gv.Columns.Count)
{
if (gv.Columns[i].Visible == true)
{
string columnName = gv.Columns[i].HeaderText;
Cell CReceiptID = new Cell();
CReceiptID.CellValue = new CellValue(columnName);
CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
colrow.InsertAt(CReceiptID, visibleIndex);
visibleIndex++;
}
}
else
{
// GridView1.Visible = true 時，gv.HeaderRow.Cells[i].Visible 也一定 true
// GridView1.Visible = false 時，gv.HeaderRow.Cells[i].Visible 也一定 false
// 若想 GridView1.Visible 為 false 時也一定匯出，下面 if 要註解掉
//if (gv.HeaderRow.Cells[i].Visible == true)
//{
string columnName = gv.HeaderRow.Cells[i].Text;
Cell CReceiptID = new Cell();
CReceiptID.CellValue = new CellValue(columnName);
CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
colrow.InsertAt(CReceiptID, visibleIndex);
visibleIndex++;
//}
}
}
// 資料列這樣抓
// Add rows to the cell table
UInt32Value DataIndex = new UInt32Value();
DataIndex.Value = 2;
for (int i = 0; i < gv.Rows.Count; i++)
{
Row row;
row = new Row() { RowIndex = DataIndex.Value };
sheetData.Append(row);
visibleIndex = 0;
for (int j = 0; j < gv.HeaderRow.Cells.Count; j++)
{
if (j < gv.Columns.Count)
{
if (gv.Columns[j].Visible == true)
{
// 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
string dataText = gv.Rows[i].Cells[j].Text;
// 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
if (gv.Rows[i].Cells[j].HasControls())
{
for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
{
if (gv.Rows[i].Cells[j].Controls[k] is Label)
{
Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
//Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
if (labelControl != null)
{
string labelText = labelControl.Text;
dataText = labelText;
}
}
}
}
dataText = dataText.Replace(" ", "");
Cell CellReceiptID = new Cell();
CellReceiptID.CellValue = new CellValue(dataText);
//CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.Number);
CellReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
row.InsertAt(CellReceiptID, visibleIndex);
visibleIndex++;
}
}
else
{
// GridView1.Visible = true 時，gv.HeaderRow.Cells[i].Visible 也一定 true
// GridView1.Visible = false 時，gv.HeaderRow.Cells[i].Visible 也一定 false
// 若想 GridView1.Visible 為 false 時也一定匯出，下面 if 要註解掉
//if (gv.HeaderRow.Cells[j].Visible == true)
//{
// 未執行【將這個欄位轉為 TemplateField】的欄位這樣就可抓到
string dataText = gv.Rows[i].Cells[j].Text;
// 執行【將這個欄位轉為 TemplateField】的欄位要根據情況改成下面
if (gv.Rows[i].Cells[j].HasControls())
{
for (int k = 0; k < gv.Rows[i].Cells[j].Controls.Count; k++)
{
if (gv.Rows[i].Cells[j].Controls[k] is Label)
{
Label labelControl = gv.Rows[i].Cells[j].Controls[k] as Label;
//Label labelControl = (Label)gv.Rows[i].Cells[j].FindControl("Label1");
if (labelControl != null)
{
string labelText = labelControl.Text;
dataText = labelText;
}
}
}
}
Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + exMsg + "');</script>");
return exMsg;
}
Label_MSG1.ForeColor = System.Drawing.Color.Green; Label_MSG1.Text = "匯出成功。";Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>"); return "OK"; } } } |
匯出後，用 Microsoft Excel 2016 開啟正常，沒有警告或錯誤。
(完)
相關文章
[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入DocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-microsoft-openxml-sdk.html
[研究][C#][ASP.NET][WebForm] 使用 OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsxDocumentFormat.OpenXml, OpenXML
https://shaurong.blogspot.com/2020/04/caspnetwebform-openxml-2101.html
沒有留言:
張貼留言