[研究][ASP.NET]使用 Aspose.Cells 21.2.0 匯出、寫入 .ods 或 .xlsx
2021-02-09
環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 Aspose.Cells 21.2.0
packages.conf
<?xml version="1.0" encoding="utf-8"?>
<packages>
<package id="Aspose.Cells" version="21.2.0" 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="AsposelicFolder" value="D:\Production\Asposelic" />
<add key="TempFolder" value="D:\WWW\Temp" />
</appSettings>
</configuration>
|
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportOdsXlsxByAsposeCellsTest.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_Ods_By_AsposeCells" runat="server"
OnClick="Button_Export_Ods_By_AsposeCells_Click" Text="匯出(.ods)" /><br />
<asp:Label ID="Label_MSG1" runat="server"></asp:Label>
</div>
</form>
</body>
</html>
|
Default.aspx.cs
using Aspose.Cells;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace ExportOdsXlsxByAsposeCellsTest
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Export_Ods_By_AsposeCells_Click(object sender, EventArgs e)
{
// 設定和使用 Aspose.Cells 授權 (Begin)
string asposelicFolder = ConfigurationManager.AppSettings["AsposelicFolder"];
Aspose.Cells.License license = new Aspose.Cells.License();
license.SetLicense(asposelicFolder + "/Aspose.Cells.lic");
// 設定和使用 Aspose.Cells 授權 (End)
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 + ".ods";
// XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
// Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
string dirFileName = fd + mainFileName + "-" +
dateTimeString + ".ods";
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 = "";
Aspose.Cells.Workbook book = new Workbook();
Aspose.Cells.Worksheet sheet = book.Worksheets[0];
Cells cells = sheet.Cells;
DataSet ds = new DataSet("DS1");
DataTable dt = ds.Tables.Add("工作表1");
// 定義欄位名稱列
string[] columnNameArray = { "id", "FieldText", "FieldDateTime", "FieldInt", "FieldBit" };
for (int i=0; i< columnNameArray.Count(); i++)
{
cells[0, i].PutValue(columnNameArray[i]);
}
// 讀取資料，資料寫入「工作表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();
int rowIndex = 0; // 0 是標題列
while (reader.Read())
{
rowIndex++;
for (int i = 0; i < reader.FieldCount; i++)
{
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("\\", "");
cells[rowIndex, i].PutValue(dataText);
}
}
book.Save(dirFileName);
//System.Threading.Thread.Sleep(1000);
//Response.WriteFile(savePath);
Response.TransmitFile(dirFileName);
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>");
}
}
// ----------
}
} |
把 .ods 改成 .xlsx，就可以匯出 .xlsx 檔案。
(完)
沒有留言:
張貼留言