[研究][ASP.NET]讀取、匯出、寫入、匯入 Excel .xlsx .xls ODF .ods

[研究][ASP.NET]讀取、匯出、寫入、匯入 Excel .xlsx .xls ODF .ods

2020-04-09、2020-04-17、2020-04-28、2021-02-18、2023-01-15 更新

Visual Studio + ASP.NET + WebForm + C#

微軟官方並不建議在伺服器端使用直接存取 Excel 物件模型的方式來控制 Excel 檔案，除了上述的資源無法釋放的問題外，還有像是權限的問題，以及安全性問題等等，詳細的資料請參考： Considerations for server-side Automation of Office - Microsoft Support http://support.microsoft.com/default.aspx/kb/257757

在 Server 端控制 Excel 的難處 | Microsoft Learn

2014/06/12

https://learn.microsoft.com/zh-tw/previous-versions/ee818993(v=msdn.10)?redirectedfrom=MSDN

Office 伺服器端自動化的考量因素 - Microsoft Support
https://support.microsoft.com/zh-tw/help/257757/considerations-for-server-side-automation-of-office

Considerations for server-side Automation of Office
https://support.microsoft.com/fi-fi/help/257757/considerations-for-server-side-automation-of-office

Excel 與舊版 Excel 搭配使用

https://support.microsoft.com/zh-tw/office/excel-%E8%88%87%E8%88%8A%E7%89%88-excel-%E6%90%AD%E9%85%8D%E4%BD%BF%E7%94%A8-2fd9ffcb-6fce-485b-85af-fecfd651a5ac

Excel 97-2003 支援256欄寬、65536列高，超出此欄和列限制的儲存格資料將會遺失。

Excel 2007 開始，工作表大小是1048576列高。

Excel 的規格及限制 - Microsoft 365 Excel, Excel 2019, Excel 2016, Excel 2013, Excel 2010, Excel 2007

https://support.microsoft.com/zh-tw/office/excel-%E7%9A%84%E8%A6%8F%E6%A0%BC%E5%8F%8A%E9%99%90%E5%88%B6-1672b34d-7043-467e-8e27-269d656771c3

(下圖)實際測試 Excel 2016，超過 256 欄位資料儲存成 .xls 時候，會有警告。

65536列的限制在 .xls 檔案，不管 Excel 是甚麼版本。

********************************************************************************

匯出、寫入Excel (.xlsx) 

注意元件更新狀況，多年未更新或停止研發的建議別用。

[研究][ASP.NET]使用 OpenXML 2.12.2 匯出、寫入 .xlsx

https://shaurong.blogspot.com/2021/02/aspnet-openxml-2122-xlsx.html

[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx

https://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx_17.html

[研究][ASP.NET]使用 Aspose.Cells 21.2.0 匯出、寫入 .ods 或 .xlsx

https://shaurong.blogspot.com/2021/02/aspnet-asposecells-2120-ods-xlsx.html

[研究][C#][ASP.NET][WebForm] 用 ClosedXML 0.95.0 把 GridView1 匯出成 Excel (.xlsx)
https://shaurong.blogspot.com/2020/04/caspnetwebform-closedxml-0950-gridview1.html
ClosedXML 需要 Microsoft OpenXML SDK ( NuGet 上稱為 DocumentFormat.OpenXml )

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML 2.10.1 (DocumentFormat.OpenXml) 把 GridView 匯出成 .xlsx
https://shaurong.blogspot.com/2020/04/caspnetwebform-openxml-2101.html

[研究][C#][ASP.NET] GridView 匯出成 Excel (.xlsx) (使用 Microsoft OpenXML SDK 2.5)
http://shaurong.blogspot.tw/2016/03/caspnet-gridview-excel-xlsx-openxml-sdk.html

[研究] [C#] 用 ClosedXML 在 Server端操作 Excel，安裝與試用 (NuGet安裝)
http://shaurong.blogspot.com/2017/11/c-closedxml-server-excel-nuget.html

EPPLus 待測
https://www.nuget.org/packages/EPPlus/
目前最新為  2020-04-07 的 v5.1.0 版
From version 5 EPPlus changes the licence model using a dual license, Polyform Non Commercial / Commercial license.

SpreadSheetLight 待測
https://www.nuget.org/packages/SpreadsheetLight/
http://spreadsheetlight.com/
目前最新 2020年11月的 3.5.0 版 ( 目前2021/02/09 )
需要 OpenXMLSDK 2.5

ExcelPackage 1.0.0

https://www.nuget.org/packages/ExcelPackage/

最後釋出 2015年5月22日 (2015/5/22)，目前2021/02/09，已經多年未更新。

public static void WriteToExcel(string[] newInfoArray)
        {
            FileInfo fileInfo = new FileInfo(pathName);

            using (ExcelPackage packge = new ExcelPackage(fileInfo))
            {
                ExcelWorksheet worksheet = packge.Workbook.Worksheets[sheetName];

                for (int i = 0; i < newInfoArray.Length; ++i)
                {
                    worksheet.Cells[selectIndex + 2, i + 1].Value = newInfoArray[i];
                }

                packge.Save();
            }
        }
********************************************************************************

Excel (.xlsx) 匯出、匯入 注意事項

目前拿來匯入的 .xlsx 檔案，若為直接用 Microsoft Excel 建立的，不會有問題。用任何元件匯出產生的 .xlsx，不保證匯入成功；就算此時再拿 Microsoft Excel 另存過一次也不保證匯入成功。

而用任何元件產生的 .xlsx 檔案，用 Microsoft Excel 開啟，也不保證 100% 成功。

有可能是元件本身相容性問題 or Bug or 程式寫法問題。

[研究][C#][ASP.NET][WebForm] ClosedXML 匯入錯誤：參數名稱: The range 工作表1!A1:L2 is already part of table 'Table1'
https://shaurong.blogspot.com/2020/04/closedxml-range-1a1l2-is-already-part.html

********************************************************************************

匯入、讀取Excel (.xlsx) 


[研究][ASP.NET]使用 OpenXML SDK 2.12.1 讀取匯入 .xlsx 到資料庫

http://shaurong.blogspot.com/2021/02/aspnet-openxml-sdk-2121-xlsx.html

DocumentFormat.OpenXml 2.12.1

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫

http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx.html

[研究][ASP.NET]使用 Aspose.Cells 21.1.0 讀取匯入 .xlsx 或 .ods 到資料庫

http://shaurong.blogspot.com/2021/02/aspnet-asposecells-2110-xlsx-ods.html

[研究][ASP.NET] 用 ExcelDataReader 讀取匯入 .xlsx , .xls , csv 檔案到 GridView1
https://shaurong.blogspot.com/2020/04/caspnetwebform-exceldatareader-xlsx-xls.html
Web Server 不需安裝 Office，目前最新為 2019/5/2 的 v3.6.0，支援 .csv, .xls, .xlsx 讀取，不支援寫入

[研究][ASP.NET]使用 NPOI 2.5.2 讀取匯入 .xlsx 到資料庫

https://shaurong.blogspot.com/2021/02/aspnet-npoi-252-xlsx.html

[研究][ASP.NET]使用 ExcelDataReader 3.6.0 讀取匯入 .xlsx 到資料庫

https://shaurong.blogspot.com/2021/02/aspnet-exceldatareader-360-xlsx.html

[研究] [C#] [WinForm] 取得 Excel 的所有 工作表 名稱，及儲存格內容 (使用Access Database Engine 2010)
http://shaurong.blogspot.tw/2016/11/c-winform-excel.html

[研究] Excel 2016 (.xlsx) 匯入 SQL Server 2019
https://shaurong.blogspot.com/2019/12/excel-2016-xlsx-sql-server-2019.html

********************************************************************************

匯出 Excel (.xls)

[研究][C#][ASP.NET][WebForm] 使用 Microsoft OpenXML SDK 2.10.1 ( DocumentFormat.OpenXml ) 把 Excel .xlsx 匯入
https://shaurong.blogspot.com/2020/04/caspnetwebform-microsoft-openxml-sdk.html

[研究][C#][ASP.NET] GridView 匯出成 .xls (使用 Excel)
http://shaurong.blogspot.tw/2016/03/caspnet-gridview-xls-excel.html
電腦要安裝 Office 軟體

[研究][ASP.NET][C#]使用 NPOI 的 RenderDataTableToExcel 匯出資料成 Excel (.xls) 檔案
待測
Server電腦要不安裝 Office 軟體，NOPI 支援 xls, xlsx, docx.

[研究][C#][ASP.NET] 用 NPOI v2.2.1 在 Server 端存取 Excel 檔案 (從官方下載安裝)
http://shaurong.blogspot.com/2017/11/caspnet-npoi-v221-server-excel.html
NOPI 支援 xls, xlsx, docx.

[研究][C#][ASP.NET] 用 NPOI v2.3.0 在 Server 端存取 Excel 檔案 (NuGet安裝)
http://shaurong.blogspot.com/2017/11/caspnet-npoi-server-excel-nuget.html

[研究][C#][ASP.NET] GridView 匯出成 Excel (.xls)(以Html方式輸出)
http://shaurong.blogspot.tw/2016/03/caspnet-gridview-excel-xlshtml.html

EPPlus
https://github.com/JanKallman/EPPlus
EPPlus will from version 5 switch license from LGPL to Polyform Noncommercial 1.0.0 license.
With the new license EPPlus is still free to use in some cases, but will require a commercial license to be used in a commercial business.
EPPlus is a .NET library that reads and writes Excel files using the Office Open XML format (xlsx). EPPlus has no dependencies other than .NET.

EPPlus 待測

********************************************************************************

匯入 Excel (.xls) 

待測

********************************************************************************

開放文件格式 (Open Document Format, ODF)

開放文檔格式 - 維基百科，自由的百科全書
https://zh.wikipedia.org/wiki/%E5%BC%80%E6%94%BE%E6%96%87%E6%A1%A3%E6%A0%BC%E5%BC%8F

ODF 中相對 MS-Excel 的試算表檔案為 .ods (OpenDocument Spreadsheet)

Office 2010 只支援到 ODF 1.1 版，Excel 2010 開啟 LibreOffice Calc 試算表 .ods 內容會失敗，Office 2013 和 2016 才支援目前最新的 ODF 1.2版

Comparison of ODF software - The Document Foundation Wiki

https://wiki.documentfoundation.org/Comparison_of_ODF_software

ODF release dates:

ODF 1.0 May 2005

ODF 1.1 February 2007

ODF 1.2 September 2012

ODF 1.3 January 2020

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets)

28 Jul 2011

https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2

所以 odsreadwrite.zip 可能只支援到 ODF, ODS 1.1 版

Microsoft Office 2007（Service Pack 2或3）開始支援 ODF 1.1（開啟、編輯與儲存) 僅限Windows）

Microsoft Office 2013 開始支援 ODF 1.2 開啟、編輯與儲存；對 ODF 1.1僅開啟與編輯（僅限Windows）

Microsoft Office 2021 開始支援 ODF 1.3（僅限Windows）

https://support.microsoft.com/zh-tw/office/office-2021-%E7%9A%84%E6%96%B0%E5%8A%9F%E8%83%BD-43848c29-665d-4b1b-bc12-acd2bfb3910a

Comparison of ODF software - The Document Foundation Wiki

https://wiki.documentfoundation.org/Comparison_of_ODF_software#Comparison_table

MS-Office 對 ODF 的支援並非100%

Libre Office 5.0 和 Apache OpenOffice 4.1 支援 ODF 1.0. 1.1, 1.2 

Differences between the OpenDocument Spreadsheet (.ods) format and the Excel for Windows (.xlsx) format - Microsoft Support

https://support.microsoft.com/en-us/office/differences-between-the-opendocument-spreadsheet-ods-format-and-the-excel-for-windows-xlsx-format-3db958c8-e0ac-49a5-9965-2c2f8afbd960?ui=en-us&rs=en-us&ad=us

.ods 對 .xlsx 的支援也沒有100%，所以 .xlsx 另存為 .ods，會喪失部分資訊。

History - The Document Foundation Wiki

https://wiki.documentfoundation.org/History

• OpenOffice.org 1.0 (May 2002) ~ 1.1.5 (Sep 2005)
• OpenOffice.org 2.0 (Oct 2005) 支援 ODF ~ 2.4.3 (Sep 2009)
• OpenOffice.org 3.0 (Oct 2008) ~ 3.3.0 (Jan 2011)
• Apache OpenOffice 4.0 (Apr 2011)

LibreOffice - Wikipedia

https://en.wikipedia.org/wiki/LibreOffice

• LibreOffice 3.3.0 (January 25, 2011)  支援 ODF
• LibreOffice 4.0 (February 2013)
• LibreOffice 5.0 (August 2015)
• LibreOffice 6.0 (31 January 2018) 支援 ODF 1.2
• LibreOffice 7.0 (5 August 2020) 支援 ODF 1.3

Apache OpenOffice - Wikipedia

https://en.wikipedia.org/wiki/Apache_OpenOffice

• 3.4.0 2012-05-08，支援ODF
• 3.4.1 2012-08-23
• 4.0.0 2013-07-23，支援ODF 1.2
• 4.0.1 2013-10-01
• 4.1.0 2014-04-29
• 4.1.1 2014-08-21
• 4.1.2 2015-10-28
• 4.1.3        2016-10-12
• 4.1.4 2017-10-19
• 4.1.5 2017-12-30
• 4.1.6 2018-11-18
• 4.1.7 2019-09-21
• 4.1.9 2021-02-07，支援ODF 1.3
• 4.1.10 2021-05-04
• 4.1.11 2021-10-06

Libre Office 
LibreOffice_6.3.5_Win_x64_sdk.msi
https://zh-tw.libreoffice.org/download/libreoffice-still/

OpenOffice 4.1.7 SDK
https://openoffice.apache.org/downloads.html

微軟 Office 軟體對 ODF 的支援 FAQ
https://docs.microsoft.com/zh-tw/archive/blogs/officetw/office-odf-faq

[研究] C# .NET 存取 OpenOffice Calc 試算表 .ods 套件評估
http://shaurong.blogspot.com/2016/12/c-net-openoffice-calc-ods.html

Top 20 NuGet ods Packagesz

https://nugetmusthaves.com/Tag/ODS

********************************************************************************

匯出、寫入ODF/.ODS

目前免費最佳做法是安裝 Java JRE  + LibreOffice + LibreOffice SDK，沒有免費好用元件，或付費買元件用。

[研究][ASP.NET]使用 Aspose.Cells 21.2.0 匯出、寫入 .ods 或 .xlsx

https://shaurong.blogspot.com/2021/02/aspnet-asposecells-2120-ods-xlsx.html

[研究][ASP.NET][C#] Aspose.Cells 評估版限制、訂價、授權、程式使用授權檔

https://shaurong.blogspot.com/2021/01/aspnetc-asposecells.html

試用版只能使用Aspose.Cells庫打開100個Excel文件。如果您的應用程序超出此數目，將引發異常。

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 (Ionic.Zip) 匯出寫入 .ods

https://shaurong.blogspot.com/2021/02/aspnet-odsreaderwriter-dotnetzip-1150_9.html

[研究][C#]匯出(寫入)、匯入(讀取) ODF/.ods 檔案 (使用 OdsReaderWriter、Ionic.Zip、DotNetZip)

http://shaurong.blogspot.com/2020/09/caspnetwebform-ioniczip-odfods.html

[研究]Ionic.Zip 1.9.1.8 即將淘汰，替代套件 DotNetZip >= 1.9.1.8

http://shaurong.blogspot.com/2020/09/ioniczip-1918-dotnetzip-1918.html

[研究][C#][ASP.NET] GridView 匯出成 Excel / .ODF (.ods) (以Html方式輸出)
https://shaurong.blogspot.com/2020/04/caspnet-gridview-excel-odf-ods-html.html

[研究] [C#] 使用 LibreOffice 6.2.6 SDK、OpenOffice 4.1.6 SDK讀取/寫入試算表(Calc) 檔案 (.ods)
https://shaurong.blogspot.com/2019/08/c-libreoffice-626-sdk-calc-ods.html
Libre Office 或 Apache Open Office 當 .ods 的讀寫軟體就算了，它的 SDK 感覺不太穩；且

Apache OpenOffice 4.1.3 SDK (2016-10-12)  只支援 .NET 2，但不支援 .NET 4.x

LibreOffice 5.3.0 SDK (2017-01-30) 支援 .NET 4.x，但不支援 x64

.NET Framework 4.0 於 2010-04-12 釋出，兩份SDK似乎沒有很好支援.NET

NuGet 排名前面的是付費元件 (可能有免費天數限制或功能限制的版本)
https://www.nuget.org/packages?q=ODS

GemBox.Spreadsheet 是商業付費軟體，雖然有30天試用版，但功能有限制

Maximum number of rows per sheet is 150.

Maximum number of sheets per workbook is 5.

GleamTech.DocumentUltimate 是商業付費軟體，有全功能30天試用版

GroupDocs 是商業付費軟體，有全功能xx天試用版

https://purchase.groupdocs.com/temporary-license/

********************************************************************************

匯入、讀取ODF/.ODS

目前免費最佳做法可能是安裝 Java JRE  + LibreOffice + LibreOffice SDK，或使用使用 .NET 4.5 的 ZipFile，沒有免費好用元件，或付費買元件用。

[研究][C#]匯出(寫入)、匯入(讀取) ODF/.ods 檔案 (使用 OdsReaderWriter、Ionic.Zip、DotNetZip)

http://shaurong.blogspot.com/2020/09/caspnetwebform-ioniczip-odfods.html

[研究]Ionic.Zip 1.9.1.8 即將淘汰，替代套件 DotNetZip >= 1.9.1.8

http://shaurong.blogspot.com/2020/09/ioniczip-1918-dotnetzip-1918.html

[研究] [C#] 使用 LibreOffice 6.2.6 SDK、OpenOffice 4.1.6 SDK讀取/寫入試算表(Calc) 檔案 (.ods)
https://shaurong.blogspot.com/2019/08/c-libreoffice-626-sdk-calc-ods.html
這是 Windows Forms 程式

[研究] [C#] 使用 OpenOffice 4.1.3 SDK 讀取試算表(Calc) 檔案 (.ods)
http://shaurong.blogspot.com/2016/12/c-openoffice-413-sdk-calc-ods.html

[研究] [C#] 使用 LibreOffice 5.1.6、5.3.0 SDK 讀取試算表(Calc) 檔案 (.ods)
http://shaurong.blogspot.com/2016/12/c-libreoffice-516-sdk-calc-ods.html

[研究] [C#] 讀取 LibreOffice、OpenOffice 的試算表(Calc) 檔案 (.ods)(使用 .NET 4.5 的 ZipFile)
http://shaurong.blogspot.com/2016/12/c-libreoffice-516-calc-ods-net-45.html

[研究] [C#] 使用 Apose.Cells 讀取 LibreOffice 5.1.6、OpenOffice 4.1.3 試算表(Calc) 檔案 (.ods)
http://shaurong.blogspot.com/2017/01/c-aposecells-libreoffice-516openoffice.html
Apose.Cells 是商業付費元件，但有無限期的試用版，但是限制只能開啟 100 個檔案。
https://docs.aspose.com/display/cellsnet/Licensing#Licensing-EvaluationVersionLimitations

[研究] [C#] 使用 GemBox.Spreadsheet 讀取LibreOffice、OpenOffice試算表(Calc) 檔案 (.ods)
http://shaurong.blogspot.com/2016/12/c-gemboxspreadsheet-libreofficeopenoffi.html
這是商業付費軟體，雖然有30天試用版，但功能有限制
Maximum number of rows per sheet is 150.
Maximum number of sheets per workbook is 5.

[研究] OpenOffice 4.1.3 試算表(Calc) 保護鎖定儲存格不被修改
http://shaurong.blogspot.com/2016/12/openoffice-413-calc.html

[研究] 使用OpenOffice 4.1.3試算表(Calc)建立「下拉式選單」來輸入資料
http://shaurong.blogspot.com/2016/12/openoffice-413calc.html

********************************************************************************

匯出、寫入.CSV

[研究][ASP.NET] 匯出寫入 .csv

https://shaurong.blogspot.com/2021/02/aspnet-csv.html

********************************************************************************

相關文章 (其他)

[研究] 匯出、匯入 Excel 檔案 (.NET)
https://shaurong.blogspot.com/2017/11/excel-net.html

[研究] .ods、.csv 轉 .xlsx 檔案
https://shaurong.blogspot.com/2020/04/odscsv-xlsx.html

(待續)

相關文章

在 Server 端存取 Excel 檔案的利器：NPOI Library
https://msdn.microsoft.com/zh-tw/ee818993.aspx

[研究][ASP.NET]使用 OdsReaderWriter + DotNetZip 1.15.0 讀取匯入 .ods 到資料庫

 [研究][ASP.NET]使用 OdsReaderWriter +  DotNetZip 1.15.0 (Ionic.Zip) 讀取匯入 .ods 到資料庫

2021-02-08

ODF release dates:

• ODF 1.0 May 2005
• ODF 1.1 February 2007
• ODF 1.2 September 2012
• ODF 1.3 January 2020

https://wiki.documentfoundation.org/Comparison_of_ODF_software

How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets)
28 Jul 2011
https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2
所以 odsreadwrite.zip 可能只支援到 ODF, ODS 1.1 版

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 NPOI 2.5.2

暫存目錄要設定程式或 IIS_IUSRS 或 IUSR 可以寫入。

( 如果是 deploy 發佈到 ASP.NET + IIS 網站， IIS_IUSRS 可以寫入即可)

( 如果是 deploy 發佈到  IIS +某種 CGI 網站， IUSR 需可以寫入)

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=\&quot;Web\&quot; /optionInfer+"/> </compilers> </system.codedom> <appSettings> <add key="TempFolder" value="D:\WWW\Temp" /> </appSettings> </configuration>

packages.config

<?xml version="1.0" encoding="utf-8"?> <packages> <package id="DotNetZip" version="1.15.0" targetFramework="net472" /> <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" /> </packages>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByOdsReaderWriterDotNetZipTest.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:FileUpload ID="FileUpload1" runat="server" /><br /> <asp:Button ID="Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip" runat="server" Text="匯入(.ods)" OnClick="Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip_Click" /><br /> <br /> <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:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br /> <asp:Label ID="Label_MSG1" runat="server"></asp:Label> </div> </form> </body> </html>

Default.aspx.cs

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; using OdsReadWrite; // 要 using namespace ，才能存取到 OdsReadWriter 這個 class namespace ImportXlsxByOdsReaderWriterDotNetZipTest { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Import_Xlsx_by_OdsReaderWriter_DotNetZip_IonicZip_Click(object sender, EventArgs e) { /* OdsReaderWriter.cs 和 template.ods 取自下面網站 How to Read and Write ODF/ODS Files (OpenDocument Spreadsheets) https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2 template.ods 請自己用 Excel、OpenOffice 或 LibreOffice 的 Calc 建立一個空的即可。 NuGet 要安裝 Ionic.Zip 套件。(Ionic.Zip 已經淘汰，請改用DotNetZip) */ Label_MSG1.Text = ""; Label_ExportImportMSG.Text = ""; string queryString = ""; string errMsg = ""; string url = ""; string msg = ""; string fieldText = ""; string fieldDateTime = ""; DateTime fieldDateTime2 = DateTime.Now; string fieldInt = ""; string fieldBit = ""; int rowCount = 0; int columnCount = 0; int successCount = 0; int lineNo = 0; try { if (!FileUpload1.HasFile) { Label_ExportImportMSG.Text = "<font color=red>請選擇.ods檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('請選擇.ods檔案。');</script>"); return; } else { string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + @"\"; if (!System.IO.Directory.Exists(fd)) { System.IO.Directory.CreateDirectory(fd); } string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); //if (!((ext == ".ods") || (ext == ".csv"))) if (!(ext == ".ods")) { Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.ods檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + "請上傳副檔名.ods檔案！" + "');</script>"); return; } string fileName = fd + FileUpload1.FileName; FileUpload1.SaveAs(fileName); string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName); //string openFilename = "D:\\WWW\\TempImageFiles\\" + mainFileName + "-匯入結果.ods"; string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".ods"; FileUpload1.SaveAs(openFilename); //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; "; queryString = @"DELETE FROM [dbo].[Table1]; "; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } OdsReaderWriter odsFile = new OdsReaderWriter(); DataSet odsDataSet = odsFile.ReadOdsFile(openFilename); DataTable table = odsDataSet.Tables[0]; // 只抓第一個工作表，第二個工作表是說明 rowCount = table.Rows.Count; columnCount = table.Columns.Count; for (int i = 0; i <= table.Rows.Count - 1; i++) { fieldText = ""; fieldDateTime = ""; fieldInt = ""; fieldBit = ""; lineNo = i; if (i == 0) { // 標題列不讀取 } else { // 非標題列 fieldText = table.Rows[i][0].ToString().Trim(); if (fieldText == "") { // 第一欄位必填，null 或 "" 就假設後面都沒有資料 errMsg = "#1匯入.ods共" + rowCount + "橫列" + columnCount + "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列) 第1欄位必填沒有資料，匯入中斷，若資料已經全部匯入，可不用理會訊息。\\n"; //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>"); Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); GridView1.DataBind(); // 更新畫面 return; } else { // 該列假設有資料 fieldDateTime = table.Rows[i][1].ToString().Trim(); bool convert = DateTime.TryParse(fieldDateTime, out fieldDateTime2); if (convert) { fieldDateTime = fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff"); } else { fieldDateTime = null; } fieldInt = table.Rows[i][2].ToString().Trim(); fieldBit = table.Rows[i][3].ToString().Trim(); // -------------------------------------------------------------------------------- // e.Values 中欄位名稱的大小寫要符合前端 Bind 的欄位名稱 // 檢察欄位長度、必填、關聯要求 // errMsg = CheckRecord(fieldText,fieldDateTime,fieldInt,fieldBit); // if (errMsg != "") // { // Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", // "<script language='javascript' defer>alert('第" + lineNo + // "筆匯入失敗，匯入中斷！" + errMsg + "');</script>"); // return; // } // -------------------------------------------------------------------------------- queryString = @" INSERT INTO [dbo].[Table1] ([FieldText] ,[FieldDateTime] ,[FieldInt] ,[FieldBit]) VALUES (N'" + fieldText + "'" + ",'" + fieldDateTime + "'" + ",'" + fieldInt + "'" + ",'" + fieldBit + "'); "; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.Clear(); //command.Parameters.AddWithValue("@Value", value); connection.Open(); command.ExecuteNonQuery(); successCount++; } } // if (fieldText == "") } // if (i == 0) } // for //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + // Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString(); url = "#"; msg = "#2匯入.ods共" + rowCount + "橫列" + columnCount + "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，成功匯入" + successCount + "筆。\\n"; ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "');location.href='" + url + "';</script>"); } // if (!FileUpload1.HasFile) } catch (Exception ex) { string exceptionMsg = "不明錯誤。"; if (ex != null) { exceptionMsg = ex.Message.ToString(); } Label_MSG1.ForeColor = System.Drawing.Color.Red; Label_MSG1.Text = errMsg + "<br />" + exceptionMsg; errMsg = "#3匯入.ods共" + rowCount + "橫列" + columnCount + "行(欄位) (含標題列，若看不到那麼多橫列，可能填寫了不可見字元)，第" + (lineNo - 1) + "筆 (Excel 第" + lineNo.ToString() + "列)匯入失敗。\\n"; //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>"); Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); // 會因為 Exception 內容關係，JavaScript alert 對話盒視窗可能不會跳出顯示 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + exceptionMsg + "');</script>"); } GridView1.DataBind(); // 更新畫面 } } }

OdsReaderWriter.cs

https://www.codeproject.com/Articles/38425/How-to-Read-and-Write-ODF-ODS-Files-OpenDocument-2

using System; using System.Data; using System.Globalization; using System.IO; using System.Reflection; using System.Xml; // Ionic.Zip 已經淘汰，請改用 DotNetZip // 不管 NuGet 安裝 DotNetZip 或 Ionic.Zip，Using 都是 Ionic.Zip using Ionic.Zip; namespace OdsReadWrite { internal sealed class OdsReaderWriter { // Namespaces. We need this to initialize XmlNamespaceManager so that we can search XmlDocument. private static string[,] namespaces = new string[,] { {"table", "urn:oasis:names:tc:opendocument:xmlns:table:1.0"}, {"office", "urn:oasis:names:tc:opendocument:xmlns:office:1.0"}, {"style", "urn:oasis:names:tc:opendocument:xmlns:style:1.0"}, {"text", "urn:oasis:names:tc:opendocument:xmlns:text:1.0"}, {"draw", "urn:oasis:names:tc:opendocument:xmlns:drawing:1.0"}, {"fo", "urn:oasis:names:tc:opendocument:xmlns:xsl-fo-compatible:1.0"}, {"dc", "http://purl.org/dc/elements/1.1/"}, {"meta", "urn:oasis:names:tc:opendocument:xmlns:meta:1.0"}, {"number", "urn:oasis:names:tc:opendocument:xmlns:datastyle:1.0"}, {"presentation", "urn:oasis:names:tc:opendocument:xmlns:presentation:1.0"}, {"svg", "urn:oasis:names:tc:opendocument:xmlns:svg-compatible:1.0"}, {"chart", "urn:oasis:names:tc:opendocument:xmlns:chart:1.0"}, {"dr3d", "urn:oasis:names:tc:opendocument:xmlns:dr3d:1.0"}, {"math", "http://www.w3.org/1998/Math/MathML"}, {"form", "urn:oasis:names:tc:opendocument:xmlns:form:1.0"}, {"script", "urn:oasis:names:tc:opendocument:xmlns:script:1.0"}, {"ooo", "http://openoffice.org/2004/office"}, {"ooow", "http://openoffice.org/2004/writer"}, {"oooc", "http://openoffice.org/2004/calc"}, {"dom", "http://www.w3.org/2001/xml-events"}, {"xforms", "http://www.w3.org/2002/xforms"}, {"xsd", "http://www.w3.org/2001/XMLSchema"}, {"xsi", "http://www.w3.org/2001/XMLSchema-instance"}, {"rpt", "http://openoffice.org/2005/report"}, {"of", "urn:oasis:names:tc:opendocument:xmlns:of:1.2"}, {"rdfa", "http://docs.oasis-open.org/opendocument/meta/rdfa#"}, {"config", "urn:oasis:names:tc:opendocument:xmlns:config:1.0"} }; // Read zip stream (.ods file is zip file). private ZipFile GetZipFile(Stream stream) { return ZipFile.Read(stream); } // Read zip file (.ods file is zip file). private ZipFile GetZipFile(string inputFilePath) { return ZipFile.Read(inputFilePath); } private XmlDocument GetContentXmlFile(ZipFile zipFile) { // Get file(in zip archive) that contains data ("content.xml"). ZipEntry contentZipEntry = zipFile["content.xml"]; // Extract that file to MemoryStream. Stream contentStream = new MemoryStream(); contentZipEntry.Extract(contentStream); contentStream.Seek(0, SeekOrigin.Begin); // Create XmlDocument from MemoryStream (MemoryStream contains content.xml). XmlDocument contentXml = new XmlDocument(); contentXml.Load(contentStream); return contentXml; } private XmlNamespaceManager InitializeXmlNamespaceManager(XmlDocument xmlDocument) { XmlNamespaceManager nmsManager = new XmlNamespaceManager(xmlDocument.NameTable); for (int i = 0; i < namespaces.GetLength(0); i++) nmsManager.AddNamespace(namespaces[i, 0], namespaces[i, 1]); return nmsManager; } /// <summary> /// Read .ods file and store it in DataSet. /// </summary> /// <param name="inputFilePath">Path to the .ods file.</param> /// <returns>DataSet that represents .ods file.</returns> public DataSet ReadOdsFile(string inputFilePath) { ZipFile odsZipFile = this.GetZipFile(inputFilePath); // Get content.xml file XmlDocument contentXml = this.GetContentXmlFile(odsZipFile); // Initialize XmlNamespaceManager XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml); DataSet odsFile = new DataSet(Path.GetFileName(inputFilePath)); foreach (XmlNode tableNode in this.GetTableNodes(contentXml, nmsManager)) odsFile.Tables.Add(this.GetSheet(tableNode, nmsManager)); return odsFile; } // In ODF sheet is stored in table:table node private XmlNodeList GetTableNodes(XmlDocument contentXmlDocument, XmlNamespaceManager nmsManager) { return contentXmlDocument.SelectNodes("/office:document-content/office:body/office:spreadsheet/table:table", nmsManager); } private DataTable GetSheet(XmlNode tableNode, XmlNamespaceManager nmsManager) { DataTable sheet = new DataTable(tableNode.Attributes["table:name"].Value); XmlNodeList rowNodes = tableNode.SelectNodes("table:table-row", nmsManager); int rowIndex = 0; foreach (XmlNode rowNode in rowNodes) this.GetRow(rowNode, sheet, nmsManager, ref rowIndex); return sheet; } private void GetRow(XmlNode rowNode, DataTable sheet, XmlNamespaceManager nmsManager, ref int rowIndex) { XmlAttribute rowsRepeated = rowNode.Attributes["table:number-rows-repeated"]; if (rowsRepeated == null || Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture) == 1) { while (sheet.Rows.Count < rowIndex) sheet.Rows.Add(sheet.NewRow()); DataRow row = sheet.NewRow(); XmlNodeList cellNodes = rowNode.SelectNodes("table:table-cell", nmsManager); int cellIndex = 0; foreach (XmlNode cellNode in cellNodes) this.GetCell(cellNode, row, nmsManager, ref cellIndex); sheet.Rows.Add(row); rowIndex++; } else { rowIndex += Convert.ToInt32(rowsRepeated.Value, CultureInfo.InvariantCulture); } // sheet must have at least one cell if (sheet.Rows.Count == 0) { sheet.Rows.Add(sheet.NewRow()); sheet.Columns.Add(); } } private void GetCell(XmlNode cellNode, DataRow row, XmlNamespaceManager nmsManager, ref int cellIndex) { XmlAttribute cellRepeated = cellNode.Attributes["table:number-columns-repeated"]; if (cellRepeated == null) { DataTable sheet = row.Table; while (sheet.Columns.Count <= cellIndex) sheet.Columns.Add(); row[cellIndex] = this.ReadCellValue(cellNode); cellIndex++; } else { cellIndex += Convert.ToInt32(cellRepeated.Value, CultureInfo.InvariantCulture); } } private string ReadCellValue(XmlNode cell) { XmlAttribute cellVal = cell.Attributes["office:value"]; if (cellVal == null) return String.IsNullOrEmpty(cell.InnerText) ? null : cell.InnerText; else return cellVal.Value; } /// <summary> /// Writes DataSet as .ods file. /// </summary> /// <param name="odsFile">DataSet that represent .ods file.</param> /// <param name="outputFilePath">The name of the file to save to.</param> public void WriteOdsFile(DataSet odsFile, string outputFilePath) { ZipFile templateFile = this.GetZipFile(Assembly.GetExecutingAssembly().GetManifestResourceStream("OdsReadWrite.template.ods")); //ZipFile templateFile = this.GetZipFile(File.OpenRead(@"C:\CODE\ODSExport\ODSExport\bin\template.ods")); //ZipFile templateFile = this.GetZipFile(File.OpenRead(Server.MapPath("/") + "template.ods"); //ZipFile templateFile = this.GetZipFile(File.OpenRead(HttpContext.Current.Server.MapPath("/template.ods"))); XmlDocument contentXml = this.GetContentXmlFile(templateFile); XmlNamespaceManager nmsManager = this.InitializeXmlNamespaceManager(contentXml); XmlNode sheetsRootNode = this.GetSheetsRootNodeAndRemoveChildrens(contentXml, nmsManager); foreach (DataTable sheet in odsFile.Tables) this.SaveSheet(sheet, sheetsRootNode); this.SaveContentXml(templateFile, contentXml); templateFile.Save(outputFilePath); } private XmlNode GetSheetsRootNodeAndRemoveChildrens(XmlDocument contentXml, XmlNamespaceManager nmsManager) { XmlNodeList tableNodes = this.GetTableNodes(contentXml, nmsManager); XmlNode sheetsRootNode = tableNodes.Item(0).ParentNode; // remove sheets from template file foreach (XmlNode tableNode in tableNodes) sheetsRootNode.RemoveChild(tableNode); return sheetsRootNode; } private void SaveSheet(DataTable sheet, XmlNode sheetsRootNode) { XmlDocument ownerDocument = sheetsRootNode.OwnerDocument; XmlNode sheetNode = ownerDocument.CreateElement("table:table", this.GetNamespaceUri("table")); XmlAttribute sheetName = ownerDocument.CreateAttribute("table:name", this.GetNamespaceUri("table")); sheetName.Value = sheet.TableName; sheetNode.Attributes.Append(sheetName); this.SaveColumnDefinition(sheet, sheetNode, ownerDocument); this.SaveRows(sheet, sheetNode, ownerDocument); sheetsRootNode.AppendChild(sheetNode); } private void SaveColumnDefinition(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument) { XmlNode columnDefinition = ownerDocument.CreateElement("table:table-column", this.GetNamespaceUri("table")); XmlAttribute columnsCount = ownerDocument.CreateAttribute("table:number-columns-repeated", this.GetNamespaceUri("table")); columnsCount.Value = sheet.Columns.Count.ToString(CultureInfo.InvariantCulture); columnDefinition.Attributes.Append(columnsCount); sheetNode.AppendChild(columnDefinition); } private void SaveRows(DataTable sheet, XmlNode sheetNode, XmlDocument ownerDocument) { DataRowCollection rows = sheet.Rows; for (int i = 0; i < rows.Count; i++) { XmlNode rowNode = ownerDocument.CreateElement("table:table-row", this.GetNamespaceUri("table")); this.SaveCell(rows[i], rowNode, ownerDocument); sheetNode.AppendChild(rowNode); } } private void SaveCell(DataRow row, XmlNode rowNode, XmlDocument ownerDocument) { object[] cells = row.ItemArray; for (int i = 0; i < cells.Length; i++) { XmlElement cellNode = ownerDocument.CreateElement("table:table-cell", this.GetNamespaceUri("table")); if (row[i] != DBNull.Value) { // We save values as text (string) XmlAttribute valueType = ownerDocument.CreateAttribute("office:value-type", this.GetNamespaceUri("office")); valueType.Value = "string"; cellNode.Attributes.Append(valueType); XmlElement cellValue = ownerDocument.CreateElement("text:p", this.GetNamespaceUri("text")); cellValue.InnerText = row[i].ToString(); cellNode.AppendChild(cellValue); } rowNode.AppendChild(cellNode); } } private void SaveContentXml(ZipFile templateFile, XmlDocument contentXml) { templateFile.RemoveEntry("content.xml"); MemoryStream memStream = new MemoryStream(); contentXml.Save(memStream); memStream.Seek(0, SeekOrigin.Begin); templateFile.AddEntry("content.xml", memStream); } private string GetNamespaceUri(string prefix) { for (int i = 0; i < namespaces.GetLength(0); i++) { if (namespaces[i, 0] == prefix) return namespaces[i, 1]; } throw new InvalidOperationException("Can't find that namespace URI"); } } }

實際使用經驗，.ods元件和.ods檔案相容性，比不上.xlsx元件和.xlsx檔案的相容性。

.ods元件有時候會讀取不到.ods檔案欄位中資料，甚至很多橫列內容相同時，可能只能讀取到一筆。(敝人自己做不出，但某些使用者不知如何做出的，拿來匯入確實有問題)

不管是 OdsReaderWriter +  DotNetZip 或商業付費的 Aspose.Cells 都如此，但是 Aspose.Cells 正常機率高於 OdsReaderWriter +  DotNetZip 方式。

.ods元件讀.ods檔案正常機率有多高，粗估高達 99% 以上，但約萬名使用者，1% 異常就很有得應付處理了。

(完)

[研究][ASP.NET]使用 Aspose.Cells 21.1.0 讀取匯入 .xlsx 或 .ods 到資料庫

[研究][ASP.NET]使用 Aspose.Cells 21.1.0 讀取匯入 .xlsx 或 .ods 到資料庫

2021-02-08

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 Aspose.Cells 21.1.0

packages.conf

<?xml version="1.0" encoding="utf-8"?> <packages> <package id="Aspose.Cells" version="21.1.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=\&quot;Web\&quot; /optionInfer+"/> </compilers> </system.codedom> <appSettings> <add key="AsposelicFolder" value="D:\WWW\Asposelic" /> </appSettings> </configuration>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByAsposeCellsTest.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:FileUpload ID="FileUpload1" runat="server" /><br /> <asp:Button ID="Button_Import_Xlsx_Ods_by_AsposeCells" runat="server" Text="匯入(.xlsx)" OnClick="Button_Import_Xlsx_Ods_by_AsposeCells_Click" /><br /> <br /> <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:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><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.SqlClient; using System.IO; using System.Linq; using System.Web; using System.Web.Configuration; using System.Web.UI; using System.Web.UI.WebControls; namespace ImportXlsxByAsposeCellsTest { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Import_Xlsx_Ods_by_AsposeCells_Click(object sender, EventArgs e) { // https://products.aspose.com/cells/net /* Input/Output Microsoft Excel: XLS, XLSX, XLSB, XLT, XLTX, XLTM, XLSM, XML OpenOffice: ODS Text: CSV, TSV Web: HTML, MHTML Numbers: Apple's iWork office suite Numbers app documents */ // 設定和使用 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) Label_MSG1.Text = ""; Label_ExportImportMSG.Text = ""; int lineNo = 1; string queryString = ""; string errMsg = ""; string url = ""; string msg = ""; string fieldText = ""; string fieldDateTime = ""; string fieldInt = ""; string fieldBit = ""; int rowCount = 0; int columnCount = 0; if (!FileUpload1.HasFile) { Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript' defer>alert('請選擇.xlsx檔案。');</script>"); return; } else { string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\"; if (!System.IO.Directory.Exists(fd)) { System.IO.Directory.CreateDirectory(fd); } string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); // ClosedXML 0.94.2 不支援 .csv // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'. ///if (!((ext == ".xlsx") || (ext == ".ods") || (ext == ".csv"))) //if (!(ext == ".xlsx")) if (!((ext == ".xlsx") || (ext == ".ods"))) { Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + "請上傳副檔名.xlsx檔案！" + "');</script>"); return; } string fileName = fd + FileUpload1.FileName; FileUpload1.SaveAs(fileName); string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName); //string openFilename = "D:\\WWW\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx"; string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; FileUpload1.SaveAs(openFilename); //DataSet odsDataSet = ReadOdsFile(openFilename); //using (FileStream fs = File.Open(@"C:\\Temp\\活頁簿1.xlsx", FileMode.Open)) using (FileStream fs = File.Open(openFilename, FileMode.Open)) { Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fs); //Workbook workbook = null; //if (ext == ".csv") //{ // TxtLoadOptions loadOptions = new TxtLoadOptions(LoadFormat.CSV); // loadOptions.ConvertNumericData = false; // //Workbook workbook = new Workbook(openFilename, loadOptions); // workbook = new Workbook(openFilename, loadOptions); //} //else //{ // //Aspose.Cells.Workbook workbook = new Aspose.Cells.Workbook(fs); // workbook = new Aspose.Cells.Workbook(fs); //} rowCount = workbook.Worksheets[0].Cells.Rows.Count; //columnCount = workbook.Worksheets[0].Cells.Columns.Count; // 只有1 columnCount = workbook.Worksheets[0].Cells.MaxColumn; //columnCount = workbook.Worksheets[0].Cells.MaxDataColumn; // 好像也可 try { //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; "; queryString = @"DELETE FROM [dbo].[Table1]; "; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } int i = 1; while (workbook.Worksheets[0].Cells.Rows[i][0].Value != null) { // 第0列(i=0)是標題，忽略不讀取，故 i=1 開始 // 第1欄位的索引值是 0 // 有些人上傳檔案，下方有幾百列空的，所以某列第一欄若空的，中斷，以下不匯入了，避免浪費時間。 fieldText = workbook.Worksheets[0].Cells.Rows[i][0].ToString().Trim(); if (fieldText != "") { // .xlsx 欄位若為空的，回傳回 null，要先檢查 // 要先清除一次，避免這次沒填，回傳 null，值沒被更新 fieldText = ""; fieldDateTime = ""; fieldInt = ""; fieldBit = ""; // null 若 ToString() 會出錯 if (workbook.Worksheets[0].Cells.Rows[i][0].Value != null) fieldText = workbook.Worksheets[0].Cells.Rows[i][0].Value.ToString().Trim(); if (workbook.Worksheets[0].Cells.Rows[i][1].Value != null) fieldDateTime = workbook.Worksheets[0].Cells.Rows[i][1].Value.ToString().Trim(); DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2); if (workbook.Worksheets[0].Cells.Rows[i][2].Value != null) fieldInt = workbook.Worksheets[0].Cells.Rows[i][2].Value.ToString().Trim(); if (workbook.Worksheets[0].Cells.Rows[i][3].Value != null) fieldBit = workbook.Worksheets[0].Cells.Rows[i][3].Value.ToString().Trim(); // -------------------------------------------------------------------------------- // e.Values 中欄位名稱的大小寫要符合前端 Bind 的欄位名稱 // 檢察欄位長度、必填、關聯要求 // errMsg = CheckRecord(fieldText,fieldDateTime,fieldInt,fieldBit); // if (errMsg != "") // { // Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", // "<script language='javascript' defer>alert('第" + lineNo + // "筆匯入失敗，匯入中斷！" + errMsg + "');</script>"); // return; // } // -------------------------------------------------------------------------------- queryString = @" INSERT INTO [dbo].[Table1] ([FieldText] ,[FieldDateTime] ,[FieldInt] ,[FieldBit]) VALUES (N'" + fieldText + "'" + ",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" + ",'" + fieldInt + "'" + ",'" + fieldBit + "'); "; using (SqlConnection connection = new SqlConnection( WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.Clear(); //command.Parameters.AddWithValue("@Value", value); connection.Open(); command.ExecuteNonQuery(); lineNo++; } }// if i++; } // while loop //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + // Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString(); url = "#"; msg = "#1匯入.xlsx共" + rowCount + "橫列" + columnCount + "行(欄位) (含標題列，若看不到那麼多橫列，，可能填寫了不可見字元)，成功匯入" + (lineNo - 1) + "筆。\\n"; ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "');location.href='" + url + "';</script>"); } catch (Exception ex) { string exceptionMsg = "不明錯誤。"; if (ex != null) { exceptionMsg = ex.Message.ToString(); } Label_MSG1.ForeColor = System.Drawing.Color.Red; Label_MSG1.Text = errMsg + "<br />" + exceptionMsg; errMsg = "#2匯入.xlsx共" + rowCount + "橫列" + columnCount + "行(欄位) (含標題列，若看不到那麼多橫列，，可能填寫了不可見字元)，第" + (lineNo - 1) + "筆 (Excel 第" + lineNo.ToString() + "列)匯入失敗。\\n"; //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>"); Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); // 會因為 Exception 內容關係，JavaScript alert 對話盒視窗可能不會跳出顯示 Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + exceptionMsg + "');</script>"); } } //using (FileStream fs = File.Open(openFilename, FileMode.Open)) } //if (FileUpload1.HasFile) GridView1.DataBind(); // 更新畫面 } // if (FileUpload1.HasFile) } }

(完)

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫

2021-02-04
2021-03-16更新
2022-06-13補不寫暫存檔案的方法

環境：Visual Studio 2019 + ASP.NET + WebForm + C# + NuGet 安裝 ClosedXML 0.95.4

packages.conf

<?xml version="1.0" encoding="utf-8"?> <packages>   <package id="ClosedXML" version="0.95.4" targetFramework="net472" />   <package id="DocumentFormat.OpenXml" version="2.7.2" targetFramework="net472" />   <package id="ExcelNumberFormat" version="1.0.10" targetFramework="net472" />   <package id="Microsoft.CodeDom.Providers.DotNetCompilerPlatform" version="2.0.1" targetFramework="net472" />   <package id="Microsoft.CSharp" version="4.7.0" targetFramework="net472" />   <package id="System.IO.FileSystem.Primitives" version="4.0.1" targetFramework="net472" />   <package id="System.IO.Packaging" version="4.0.0" targetFramework="net472" /> </packages>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ImportXlsxByClosedXMLTest.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:FileUpload ID="FileUpload1" runat="server" /><br /> <asp:Button ID="Button_Import_Xlsx_by_ClosedXML" runat="server" Text="匯入(.xlsx)" OnClick="Button_Import_Xlsx_by_ClosedXML_Click" /><br /> <br /> <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:Label ID="Label_ExportImportMSG" runat="server"></asp:Label><br /> <asp:Label ID="Label_MSG1" runat="server"></asp:Label> </div> </form> </body> </html>

Default.aspx.cs (寫暫存檔)

using ClosedXML.Excel; using System; using System.Collections.Generic; using System.Configuration; 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 ImportXlsxByClosedXMLTest { public partial class Default : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Import_Xlsx_by_ClosedXML_Click(object sender, EventArgs e) { // ClosedXML 官方網站 (有範例) // https://github.com/ClosedXML/ClosedXML/wiki Label_MSG1.Text = ""; Label_ExportImportMSG.Text = ""; int lineNo = 0; int lineCount = 0; int successCount = 0; string queryString = ""; string errMsg = ""; string fieldText = ""; string fieldDateTime = ""; string fieldInt = ""; string fieldBit = ""; int rowCount = 0; int columnCount = 0; if (!FileUpload1.HasFile) { Label_ExportImportMSG.Text = "<font color=red>請選擇.xlsx檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('請選擇.xlsx檔案。');</script>"); return; } else { string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\"; if (!System.IO.Directory.Exists(fd)) { System.IO.Directory.CreateDirectory(fd); } string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); // ClosedXML 0.94.2 不支援 .csv // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'. //if (!((ext == ".xlsx") || (ext == ".csv"))) if (!(ext == ".xlsx")) { Label_ExportImportMSG.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>"; //Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>"; ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true); return; } string fileName = fd + FileUpload1.FileName; FileUpload1.SaveAs(fileName); string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName); //string openFilename = "D:\\Production\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx"; string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; FileUpload1.SaveAs(openFilename); //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; "; queryString = @"DELETE FROM [dbo].[Table1]; "; using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } using (XLWorkbook workbook = new XLWorkbook(openFilename)) { // 讀取第一個 Sheet IXLWorksheet worksheet = workbook.Worksheet(1); // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息 worksheet.AutoFilter.IsEnabled = false; // 定義資料起始/結束 Cell var firstCell = worksheet.FirstCellUsed(); var lastCell = worksheet.LastCellUsed(); // 使用資料起始/結束 Cell，來定義出一個資料範圍 var data = worksheet.Range(firstCell.Address, lastCell.Address); // 將資料範圍轉型 var table = data.AsTable(); //讀取資料 //string Excel = ""; //Excel = table.Cell(6, 1).Value.ToString(); //寫入資料 //table.Cell(2, 1).Value = "test"; rowCount = table.RowCount(); columnCount = table.ColumnCount(); // i=1，第1列標題，不讀取；第2列開始是資料 for (int i = 2; i <= rowCount; i++) { // 填入值, Cell 的 Column number must be between 1 and 16384 fieldText = table.Cell(i, 1).Value.ToString(); // 必填，無財產編號請寫「無」 if (fieldText == "") { errMsg = "#1匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入" + successCount + "筆，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)第一欄位未填，匯入中斷！若資料已全部匯入，可不理會。"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } else { fieldDateTime = table.Cell(i, 2).Value.ToString(); DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2); fieldInt = table.Cell(i, 3).Value.ToString(); fieldBit = table.Cell(i, 4).Value.ToString(); if (errMsg != "") { errMsg = "#2匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + errMsg; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } queryString = @" INSERT INTO [dbo].[Table1] ([FieldText] ,[FieldDateTime] ,[FieldInt] ,[FieldBit]) VALUES (N'" + fieldText + "'" + ",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" + ",'" + fieldInt + "'" + ",'" + fieldBit + "'); "; using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.Clear(); //command.Parameters.AddWithValue("@value", value); connection.Open(); try { command.ExecuteNonQuery(); successCount++; } catch (Exception ex) { string exceptionMsg = "不明錯誤。"; if (ex != null) { exceptionMsg = ex.Message.ToString(); } // Exception 用 JavaScript 顯示，可能會因為內容關係，導致 alert 對話盒視窗無法顯示。 //errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + exceptionMsg; errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n"; //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>"); Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } } // using (SqlConnection connection } // if (fieldText=="") } // for } // using //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString(); string url = "#"; string msg = "#4匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入。\\n"; ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "按【確定】轉往列表畫面。');location.href='" + url + "';</script>"); } //if (FileUpload1.HasFile) GridView1.DataBind(); } } }

********************************************************************************

Default.aspx.cs (不寫暫存檔)

using ClosedXML.Excel; using System; using System.Data.SqlClient; using System.IO; using System.Web.Configuration; using System.Web.UI; namespace WebApplication1 { public partial class XlsxImport : System.Web.UI.Page { protected void Page_Load(object sender, EventArgs e) { } protected void Button_Import_Xlsx_by_ClosedXML_Click(object sender, EventArgs e) { // ClosedXML 官方網站 (有範例) // https://github.com/ClosedXML/ClosedXML/wiki Label_MSG1.Text = ""; int lineNo = 0; int lineCount = 0; int successCount = 0; string queryString = ""; string errMsg = ""; string fieldText = ""; string fieldDateTime = ""; string fieldInt = ""; string fieldBit = ""; int rowCount = 0; int columnCount = 0; if (!FileUpload1.HasFile) { Label_MSG1.Text = "<font color=red>請選擇.xlsx檔案</font>"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('請選擇.xlsx檔案。');</script>"); return; } else { //string fd = (string)ConfigurationManager.AppSettings["TempFolder"] + "\\"; //if (!System.IO.Directory.Exists(fd)) //{ // System.IO.Directory.CreateDirectory(fd); //} string ext = System.IO.Path.GetExtension(FileUpload1.FileName).ToLower(); // ClosedXML 0.94.2 不支援 .csv // Extension 'csv' is not supported. Supported extensions are '.xlsx', '.xslm', '.xltx' and '.xltm'. //if (!((ext == ".xlsx") || (ext == ".csv"))) if (!(ext == ".xlsx")) { Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>"; //Label_MSG1.Text = "<font color=red>請上傳副檔名.xlsx檔案</font>"; ScriptManager.RegisterStartupScript(this, GetType(), "Popup1", "erroralert('" + "請上傳副檔名.xlsx檔案！" + "');", true); return; } //string fileName = fd + FileUpload1.FileName; //FileUpload1.SaveAs(fileName); string mainFileName = Path.GetFileNameWithoutExtension(FileUpload1.FileName); //string openFilename = "D:\\Production\\TempImageFiles\\" + mainFileName + "-匯入結果.xlsx"; //string openFilename = fd + mainFileName + "-匯入結果" + DateTime.Now.ToString("yyyy-MM-dd-HH-mm-ss-fff") + ".xlsx"; //FileUpload1.SaveAs(openFilename); string filePath = FileUpload1.PostedFile.FileName; string filename = Path.GetFileName(filePath); Stream fs = FileUpload1.PostedFile.InputStream; BinaryReader br = new BinaryReader(fs); Byte[] bytes = br.ReadBytes((Int32)fs.Length); MemoryStream destination = new MemoryStream(bytes); //queryString = @"DELETE FROM [dbo].[Table1] WHERE id=@id; "; queryString = @"DELETE FROM [dbo].[Table1]; "; using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); connection.Open(); command.ExecuteNonQuery(); } //using (XLWorkbook workbook = new XLWorkbook(openFilename)) using (XLWorkbook workbook = new XLWorkbook(destination)) { // 讀取第一個 Sheet IXLWorksheet worksheet = workbook.Worksheet(1); // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息 worksheet.AutoFilter.IsEnabled = false; // 定義資料起始/結束 Cell var firstCell = worksheet.FirstCellUsed(); var lastCell = worksheet.LastCellUsed(); // 使用資料起始/結束 Cell，來定義出一個資料範圍 var data = worksheet.Range(firstCell.Address, lastCell.Address); // 將資料範圍轉型 var table = data.AsTable(); //讀取資料 //string Excel = ""; //Excel = table.Cell(6, 1).Value.ToString(); //寫入資料 //table.Cell(2, 1).Value = "test"; rowCount = table.RowCount(); columnCount = table.ColumnCount(); // i=1，第1列標題，不讀取；第2列開始是資料 for (int i = 2; i <= rowCount; i++) { // 填入值, Cell 的 Column number must be between 1 and 16384 fieldText = table.Cell(i, 1).Value.ToString(); // 必填，無財產編號請寫「無」 if (fieldText == "") { errMsg = "#1匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入" + successCount + "筆，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)第一欄位未填，匯入中斷！若資料已全部匯入，可不理會。"; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } else { fieldDateTime = table.Cell(i, 2).Value.ToString(); DateTime.TryParse(fieldDateTime, out DateTime fieldDateTime2); fieldInt = table.Cell(i, 3).Value.ToString(); fieldBit = table.Cell(i, 4).Value.ToString(); if (errMsg != "") { errMsg = "#2匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + errMsg; Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } queryString = @" INSERT INTO [dbo].[Table1] ([FieldText] ,[FieldDateTime] ,[FieldInt] ,[FieldBit]) VALUES (N'" + fieldText + "'" + ",'" + fieldDateTime2.ToString("yyyy/MM/dd HH:mm:ss.fff") + "'" + ",'" + fieldInt + "'" + ",'" + fieldBit + "'); "; using (SqlConnection connection = new SqlConnection(WebConfigurationManager.ConnectionStrings["TestDBConnectionString"].ConnectionString)) { SqlCommand command = new SqlCommand(queryString, connection); //command.Parameters.Clear(); //command.Parameters.AddWithValue("@value", value); connection.Open(); try { command.ExecuteNonQuery(); successCount++; } catch (Exception ex) { string exceptionMsg = "不明錯誤。"; if (ex != null) { exceptionMsg = ex.Message.ToString(); } // Exception 用 JavaScript 顯示，可能會因為內容關係，導致 alert 對話盒視窗無法顯示。 //errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i-1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n" + exceptionMsg; errMsg = "#3匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，第" + (i - 1) + "筆 (Excel 第" + i.ToString() + "列)匯入失敗。\\n"; //ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + errMsg + "');</script>"); Page.ClientScript.RegisterStartupScript(Page.GetType(), "message", "<script language='javascript'>alert('" + errMsg + "');</script>"); return; } } // using (SqlConnection connection } // if (fieldText=="") } // for } // using //string url = "/List.aspx?Ref1=" + Request.QueryString["Ref1"].ToString() + "&Ref2=" + Request.QueryString["Ref2"].ToString() + "&Ref3=" + Request.QueryString["Ref3"].ToString(); string url = "#"; string msg = "#4匯入.xlsx共" + rowCount + "列" + columnCount + "行 (含標題列，若看不到，可能填寫了不可見字元)，成功匯入。\\n"; ClientScript.RegisterStartupScript(GetType(), "message", "<script>alert('" + msg + "按【確定】轉往列表畫面。');location.href='" + url + "';</script>"); } //if (FileUpload1.HasFile) GridView1.DataBind(); } } }

(完)

相關

[研究][ASP.NET]ClosedXML元件常用屬性或方法http://shaurong.blogspot.com/2022/04/aspnetclosedxml.html

[研究][ASP.NET]使用 ClosedXML 0.95.4 讀取匯入 .xlsx 到資料庫(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx.html

[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)
http://shaurong.blogspot.com/2021/02/aspnet-closedxml-0954-xlsx_17.html