[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.11 匯出 SqlDataSource1 資料成 .ods (MIT)
2024-11-08
環境：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
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="WebApplication1.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">
<asp:SqlDataSource ID="SqlDataSource_Field2" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
SelectCommand="SELECT N'全部' AS [Field2], 0 AS Priority
UNION
SELECT DISTINCT [Field2], 1 AS Priority FROM [MyTable] ORDER BY Priority, [Field2]"></asp:SqlDataSource>
<asp:DropDownList ID="DropDownList_Field2" runat="server"
AutoPostBack="True" DataSourceID="SqlDataSource_Field2"
DataTextField="Field2" DataValueField="Field2">
</asp:DropDownList>
<br />
<asp:Button ID="Button_Export_ODS" runat="server" Text="匯出(.ods)"
OnClick="Button_Export_ODS_Click" /><br />
<asp:Button ID="Button_Export_ODS2" runat="server" Text="匯出2(.ods)"
OnClick="Button_Export_ODS2_Click" /><br />
<asp:Button ID="Button_Export_ODS3" runat="server" Text="匯出3(.ods)"
OnClick="Button_Export_ODS3_Click" /><br />
<asp:Label ID="Label_MSG1" runat="server" Text="Label"></asp:Label>
<br />
<asp:SqlDataSource ID="SqlDataSource1" runat="server"
ConnectionString="<%$ ConnectionStrings:MyDBConnectionString %>"
SelectCommand="--TSQL--
SELECT * FROM [MyTable]
WHERE ([Field2]=@Filed2 OR @Filed2=N'全部');">
<SelectParameters>
<asp:ControlParameter ControlID="DropDownList_Field2" Name="Filed2" PropertyName="SelectedValue" />
</SelectParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server" AutoGenerateColumns="False" DataKeyNames="SN" DataSourceID="SqlDataSource1">
<Columns>
<asp:BoundField DataField="SN" HeaderText="SN" InsertVisible="False" ReadOnly="True" SortExpression="SN" />
<asp:BoundField DataField="Field1" HeaderText="Field1" SortExpression="Field1" />
<asp:BoundField DataField="Field2" HeaderText="Field2" SortExpression="Field2" />
</Columns>
</asp:GridView>
</form>
</body>
</html>
|
Default.aspx.cs
using System;
using System.Data.SqlClient;
namespace WebApplication1
{
public partial class Default : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void Button_Export_ODS_Click(object sender, EventArgs e)
{
string[] columnNameArray = { "流水號", "欄位1", "欄位2" };
SqlParameter[] sqlParameters = OdsCommon.ConvertParameters(SqlDataSource1.SelectParameters, this.Page);
OdsCommon.ExportODS("匯出檔案名稱", columnNameArray, SqlDataSource1.SelectCommand, sqlParameters);
}
protected void Button_Export_ODS2_Click(object sender, EventArgs e)
{
string[] columnNameArray = { "流水號", "欄位1", "欄位2" };
SqlParameter[] sqlParameters = OdsCommon.ConvertParameters(SqlDataSource1.SelectParameters, this.Page);
OdsReaderWriterSystemIOCompressionCommon.ExportODS("匯出檔案名稱", columnNameArray, SqlDataSource1.SelectCommand, sqlParameters);
}
protected void Button_Export_ODS3_Click(object sender, EventArgs e)
{
string[] columnNameArray = { "流水號", "欄位1", "欄位2" };
SqlParameter[] sqlParameters = OdsFreeDataExportsCommon.ConvertParameters(SqlDataSource1.SelectParameters, this.Page);
OdsFreeDataExportsCommon.ExportODS("匯出檔案名稱", columnNameArray, SqlDataSource1.SelectCommand, sqlParameters);
}
}
}
|
Default.aspx.cs
using FreeDataExports;
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;
namespace WebApplication1
{
public class OdsFreeDataExportsCommon
{
//【匯出(.ods)】
#region === public void ExportODS(string odsFilename, string sqlDataSourceSelectCommand) ===
public static void ExportODS(string odsFilename,
string[] columnNameArray,
string sqlDataSourceSelectCommand,
SqlParameter[] parameters)
{
// 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 = odsFilename;
// 若檔案名稱有中文字或特殊符號，Response.AddHeader() 輸出檔案名稱要編碼
string outFileName = HttpUtility.UrlEncode(mainFileName, System.Text.Encoding.UTF8) + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".ods";
// XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
// Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
string dirFileName = @"D:\Production\TempImageFiles\" + mainFileName + "-" +
DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".ods";
try
{
System.Web.HttpContext.Current.Response.Clear();
System.Web.HttpContext.Current.Response.Buffer = true;
System.Web.HttpContext.Current.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.openxmlformats-officedocument.spreadsheetml.sheet";
System.Web.HttpContext.Current.Response.ContentType = "application/vnd.oasis.opendocument.spreadsheet"; // ODF/.ods
System.Web.HttpContext.Current.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 = "";
DataSet ds = new DataSet("DS1");
//DataTable dt = new DataTable();
DataTable dt = ds.Tables.Add("工作表1");
// string[] columnNameArray = {
//"流水號"
//,"欄位1"
//,"欄位2"
// };
foreach (string columnName in columnNameArray)
{
DataColumn dataColumnName = new DataColumn
{
DataType = System.Type.GetType("System.String"),
ColumnName = columnName
};
dt.Columns.Add(dataColumnName);
}
DataRow dataColumnNameRow = dt.NewRow();
int ii = 0;
foreach (string columnName in columnNameArray)
{
dataColumnNameRow[ii] = columnName;
ii++;
}
dt.Rows.Add(dataColumnNameRow);
//string queryString = SqlDataSource.SelectCommand;
string queryString = sqlDataSourceSelectCommand;
using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["MyDBConnectionString"].ConnectionString))
{
SqlCommand command = new SqlCommand(queryString, connection);
//command.Parameters.Clear();
//command.Parameters.AddWithValue("@cname", cname);
//command.Parameters.AddWithValue("@myemail", myemail);
if (parameters != null)
{
foreach (SqlParameter parameter in parameters)
{
command.Parameters.Add(parameter);
}
}
connection.Open();
//Get All column
//var columnNames = Enumerable.Range(0, reader.FieldCount)
// .Select(reader.GetName)
// .ToList();
////Create headers
//sb.Append(string.Join(",", columnNames));
////Append Line
//sb.AppendLine();
try
{
using (SqlDataReader reader = command.ExecuteReader())
{
while (reader.Read())
{
//Console.WriteLine(String.Format("{0}", reader[0]));
DataRow datarow = dt.NewRow();
for (int i = 0; i < reader.FieldCount; i++)
{
dataText = reader[i].ToString();
// 換掉影響 CSV 顯示效果的字元
dataText = dataText.Replace("\r", "").Replace("\n", "").Replace("\t", "").Replace(",", "，").Replace("'", "′").Replace(@"""", "”");
//if (i > 0)
//{
// datarow[i - 1] = dataText;
//}
datarow[i] = dataText;
//if (i == 1)
//{
// //if (csvContent=="")
// // csvContent = dataText;
// //else
// //csvContent = csvContent + "\r\n" + dataText;
// csvContent.Append("\r\n" + dataText);
//}
//else
//{
// //csvContent = csvContent + "," + dataText;
// csvContent.Append("," + dataText);
//}
}
dt.Rows.Add(datarow);
}
}
var workbook = new DataExport().CreateODSv1_3();
var worksheet = workbook.AddWorksheet("工作表1");
DataTable dataTable= ds.Tables[0];
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(dirFileName);
//OdsReaderWriter odsFile = new OdsReaderWriter();
////odsFile.WriteOdsFile(ds, @"C:\TEMP\1.ods");
//odsFile.WriteOdsFile(ds, dirFileName);
//Label_MSG1.ForeColor = System.Drawing.Color.Green;
//string msg = DateTime.Now.ToString() + "匯出成功。";
//Label_MSG1.Text = msg;
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('" + msg + "');</script>");
}
catch (Exception)
{
throw;
}
}
System.Web.HttpContext.Current.Response.TransmitFile(dirFileName);
System.Web.HttpContext.Current.Response.End();
}
#endregion Export ODS
}
catch (Exception)
{
throw;
}
//Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('匯出成功。');</script>");
}
#endregion
//建立參數
#region === public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page) ===
public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page)
{
SqlParameter[] sqlParameters = new SqlParameter[parameters.Count];
for (int i = 0; i < parameters.Count; i++)
{
var parameter = parameters[i];
if (parameter is ControlParameter)
{
ControlParameter controlParameter = (ControlParameter)parameter;
var control = page.FindControl(controlParameter.ControlID);
// 確保找到控制元件
if (control != null)
{
string value = (control as ITextControl)?.Text ?? string.Empty;
sqlParameters[i] = new SqlParameter(parameter.Name, value);
}
else
{
// 找不到控制元件時，應該提供預設值，而不是引發 NullReferenceException
sqlParameters[i] = new SqlParameter(parameter.Name, DBNull.Value);
}
}
else
{
// 如果參數不是控制參數，則使用 DBNull.Value
sqlParameters[i] = new SqlParameter(parameter.Name, DBNull.Value);
}
}
return sqlParameters;
}
#endregion === public static SqlParameter[] ConvertParameters(ParameterCollection parameters, Page page) ===
}
}
|
實際測試可以成功。(按鈕3)
下圖，
********************************************************************************
FreeDataExports 庫本身不支援自定義字型顏色、字體大小或粗體格式設定，因為它設計上是簡單的匯出工具，並沒有提供進階的格式化選項。
********************************************************************************
(完)
相關
[研究]ASP.NET,WebForm,用 FreeDataExports 1.1.11 匯出 SqlDataSource1 資料成 .ods (MIT)
https://shaurong.blogspot.com/2024/11/aspnetwebform-freedataexports-1111.html
[研究]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
