[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.11 匯出 .ods (MIT)
2024-03-29
環境：Visual Studio 2022 + ASP.NET + WebForm + Web Application + C# + SQL Server 2019 + SQL Server Management Studio (SSMS) 19
********************************************************************************
https://www.nuget.org/packages/FreeDataExports/1.1.11/License
授權是 MIT
********************************************************************************
官方網站
https://github.com/ryankueter/FreeDataExports
官方的程式範例有問題。
Web.Config
<?xml version="1.0" encoding="utf-8"?> <!-- 如需如何設定 ASP.NET 應用程式的詳細資訊，請前往 https://go.microsoft.com/fwlink/?LinkId=169433 --> <configuration> <connectionStrings> <add name="MyDBConnectionString" connectionString="Data Source=.;Initial Catalog=MyDB;User ID=sa;Password=密碼" providerName="System.Data.SqlClient" /> </connectionStrings> <system.web> <compilation debug="true" targetFramework="4.8" /> <httpRuntime targetFramework="4.8" /> </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> </configuration> |
Default.aspx.cs
using FreeDataExports;
protected void Button1_Click(object sender, EventArgs e)
{
string path = @"C:\Temp\2.ods";
var workbook = new DataExport().CreateODSv1_3();
var worksheet = workbook.AddWorksheet("工作表1");
string[] customHeaders = { "Column1", "Column2", "Column3" }; // 依需要替換為實際的列名
// 查詢語句
string query = "SELECT * FROM [MyDB].[dbo].[MyTable]";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
// 新增自訂標題
worksheet.AddRow();
for (int i = 0; i < customHeaders.Length; i++)
{
worksheet.AddCell(customHeaders[i], DataType.String);
}
// 匯出資料到.ods文件
for (int i = 0; i < dataTable.Rows.Count; i++)
{
worksheet.AddRow();
for (int j = 0; j < dataTable.Columns.Count; j++)
worksheet.AddCell(dataTable.Rows[i].ItemArray[j], DataType.String);
}
workbook.Save(path);
}
}
}
}
|
實際測試可以成功。
********************************************************************************
Default.aspx.cs
protected void Button2_Click(object sender, EventArgs e)
{
var workbook = new DataExport().CreateODSv1_3();
var worksheet = workbook.AddWorksheet("工作表1");
string[] customHeaders = { "Column1", "Column2", "Column3" }; // 依需要替換為實際的列名
// 查詢語句
string query = "SELECT * FROM [MyDB].[dbo].[MyTable]";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
// 新增自訂標題
worksheet.AddRow();
for (int i = 0; i < customHeaders.Length; i++)
{
worksheet.AddCell(customHeaders[i], DataType.String);
}
// 匯出資料到.ods文件
for (int i = 0; i < dataTable.Rows.Count; i++)
{
worksheet.AddRow();
for (int j = 0; j < dataTable.Columns.Count; j++)
worksheet.AddCell(dataTable.Rows[i].ItemArray[j], DataType.String);
}
// 使用 MemoryStream 將檔案直接下載
using (MemoryStream stream = new MemoryStream())
{
workbook.Save(stream);
stream.Position = 0;
// 設定回應頭，讓瀏覽器顯示下載提示
Response.Clear();
Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
Response.AddHeader("Content-Disposition", "attachment; filename=ExportedData.ods");
Response.BinaryWrite(stream.ToArray());
Response.Flush();
Response.End();
}
}
}
}
}
|
個錯誤是因為 Save 方法預期的是一個檔案路徑（字串類型），而不是 MemoryStream。可以檢查 CreateODSv1_3 所使用的類別庫是否支援儲存至 MemoryStream，若無，則需要調整程式碼。
********************************************************************************
【先暫存再彈出，成功】
Default.aspx.cs
protected void Button3_Click(object sender, EventArgs e)
{
//string tempFilePath = Server.MapPath("~/Temp/ExportedData.ods"); // 暫存檔案路徑
string tempFilePath = @"C:\temp\ExportedData.ods"; // 暫存檔案路徑
var workbook = new DataExport().CreateODSv1_3();
var worksheet = workbook.AddWorksheet("工作表1");
string[] customHeaders = { "Column1", "Column2", "Column3" }; // 依需要替換為實際的列名
// 查詢語句
string query = "SELECT * FROM [MyDB].[dbo].[MyTable]";
using (SqlConnection connection = new SqlConnection(
WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
using (SqlCommand command = new SqlCommand(query, connection))
{
using (SqlDataAdapter dataAdapter = new SqlDataAdapter(command))
{
DataTable dataTable = new DataTable();
dataAdapter.Fill(dataTable);
// 新增自訂標題
worksheet.AddRow();
for (int i = 0; i < customHeaders.Length; i++)
{
worksheet.AddCell(customHeaders[i], DataType.String);
}
// 匯出資料到.ods文件
for (int i = 0; i < dataTable.Rows.Count; i++)
{
worksheet.AddRow();
for (int j = 0; j < dataTable.Columns.Count; j++)
worksheet.AddCell(dataTable.Rows[i].ItemArray[j], DataType.String);
}
// 儲存至暫存路徑
workbook.Save(tempFilePath);
// 讀取檔案內容並下載
byte[] fileBytes = System.IO.File.ReadAllBytes(tempFilePath);
Response.Clear();
Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet";
Response.AddHeader("Content-Disposition", "attachment; filename=ExportedData.ods");
Response.BinaryWrite(fileBytes);
Response.Flush();
Response.End();
// 刪除暫存檔案
System.IO.File.Delete(tempFilePath);
}
}
}
}
|
********************************************************************************
(完)
相關
[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.11 匯出 .ods
https://shaurong.blogspot.com/2024/10/aspnetwebform-freedataexports-1111-ods.html
[研究]ASP.NET,WebForm,要傳遞 SqlDataSource1.SelectParameters 當時的值https://shaurong.blogspot.com/2024/03/aspnetwebform-sqldatasource1selectparam.html
[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.9 匯出資料庫某資料表成 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-freedataexports-119-ods_29.html
[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.9 匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-freedataexports-119-ods.html
[研究]ASP.NET,WebForm, 把 OdsReadWrite.cs 從 Ionic.Zip 或 DotNetZip 改成用 System.IO.Compression
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreadwritecs-ioniczip.html
[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 共用匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_79.html
[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 匯出 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_23.html
[研究]ASP.NET,WebForm,用 OdsReaderWriter + DotNetZip (Ionic.Zip) 1.16.0 匯入 .ods
https://shaurong.blogspot.com/2024/03/aspnetwebform-odsreaderwriter-dotnetzip_93.html
How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets) - CodeProject
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2
[研究][ASP.NET]讀取、匯出、寫入、匯入 Excel .xlsx .xls ODF .ods
https://shaurong.blogspot.com/2020/04/caspnetwebform-excel-xlsx-xls-odf-ods.html
[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 (Ionic.Zip) 匯出寫入 .ods
https://shaurong.blogspot.com/2021/02/aspnet-odsreaderwriter-dotnetzip-1150_9.html
[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫
https://shaurong.blogspot.com/2021/02/aspnet-odsreaderwriter-dotnetzip-1150.html
[研究][C#]用OdsReaderWrite匯出(寫入)、匯入(讀取) ODF/.ods 檔案 (使用 Ionic.Zip、DotNetZip)
https://shaurong.blogspot.com/2020/09/caspnetwebform-ioniczip-odfods.html
[研究] [C#] 用OdsReadWrite讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)
https://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods-net-45.html
[研究] [C#] 用OdsReaderWrite讀取 LibreOffice 5.1.6 的試算表(Calc) 檔案 (.ods)(使用 DotNetZip)
https://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods.html
NuGet Gallery | OdsReaderWriter 1.0.10
https://www.nuget.org/packages/OdsReaderWriter
sortings/OdsReadWrite at master · Luviz/sortings · GitHub
https://github.com/Luviz/sortings/tree/master/OdsReadWrite
沒有留言:
張貼留言