浮雲雅築: 2月 2021

2021年2月23日星期二

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)

2021-02-23

參考

https://www.w3schools.com/bootstrap/bootstrap_ref_js_tab.asp

<!DOCTYPE html>
<html lang="en">
<head>
  <title>Bootstrap Example</title>
  <meta charset="utf-8">
  <meta name="viewport" content="width=device-width, initial-scale=1">
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/css/bootstrap.min.css">
  <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>
  <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.1/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
  <h2>Dynamic Tabs</h2>
  <ul class="nav nav-tabs">
    <li class="active"><a data-toggle="tab" href="#home">Home</a></li>
    <li><a data-toggle="tab" href="#menu1">Menu 1</a></li>
    <li><a data-toggle="tab" href="#menu2">Menu 2</a></li>
    <li><a data-toggle="tab" href="#menu3">Menu 3</a></li>
  </ul>

  <div class="tab-content">
    <div id="home" class="tab-pane fade in active">
      <h3>HOME</h3>
      <p>內容</p>
    </div>
    <div id="menu1" class="tab-pane fade">
      <h3>Menu 1</h3>
      <p>內容1</p>
    </div>
    <div id="menu2" class="tab-pane fade">
      <h3>Menu 2</h3>
      <p>內容2</p>
    </div>
    <div id="menu3" class="tab-pane fade">
      <h3>Menu 3</h3>
      <p>內容3</p>
    </div>
  </div>
</div>

</body>
</html>

(完)

相關

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)
https://shaurong.blogspot.com/2021/02/tab-html-jquery-351-bootstrap-341.html

[研究] 頁籤(tab) (HTML + CSS3)
https://shaurong.blogspot.com/2021/02/tab-html-css3.html

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)
https://shaurong.blogspot.com/2021/02/tab-html-css-javascript-jquery-jquery-ui.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascriptjquery.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascript.html

Online HTML Code Generator | CSS Code Generator | JavaScript
https://www.html-code-generator.com/

JQuery Tabs Generator
https://www.html-code-generator.com/jquery/tabs-generator

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

Top Tabs Generator
https://www.pagecolumn.com/tool/top_tabs_generator.htm

Review techniques of making tabs with CSS
https://www.pagecolumn.com/webparts/making_tabs_with_CSS.htm

Tab Menu - Online Tab Menu builder
http://www.menucool.com/horizontal/tab-menu

「CSS3:target選擇器」免程式!純CSS就可實現Tab頁籤式的互動切換 | 梅問題．教學網https://www.minwt.com/webdesign-dev/css/16987.html

bootstrap頁籤
https://codepen.io/fenture/pen/pWEMdR

使用Jquery特效做出Tab頁籤切換效果
https://www.webdesigns.com.tw/jquery_tab.asp

[研究] 頁籤(tab) (HTML + CSS3)

[研究] 頁籤(tab) (HTML + CSS3)

2021-02-23

參考

CSS :target Selector
https://www.w3schools.com/cssref/sel_target.asp

<<!DOCTYPE html>

<html>
<head>
  <meta charset="utf-8">
<style>
.tab div {
  display: none;
}

.tab div:target {
  display: block;
}
</style>
</head>
<body>

<div class="tab">

<a href="#link1">Link 1</a>   
<a href="#link2">Link 2</a>
<a href="#link3">Link 3</a>

<div id="link1">
  <h3>Content to Link 1</h3>
  <p>Hello World!</p>
</div>

<div id="link2">
  <h3>Content to Link 2</h3>
  <h4>Great success!</h4>
</div>

<div id="link3">
  <h3>Content to Link 3</h3>
  <p>Yeah!</p>
</div>

</div>

</body>
</html>

網址若不含錨文字 HTML anchor，則不會顯示任何頁籤內容

file:///C:/Users/Administrator/Desktop/4.htm

網址若包含錨文字 HTML anchor，則會顯示

file:///C:/Users/Administrator/Desktop/4.htm#link1

(完)

相關

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)
https://shaurong.blogspot.com/2021/02/tab-html-jquery-351-bootstrap-341.html

[研究] 頁籤(tab) (HTML + CSS3)
https://shaurong.blogspot.com/2021/02/tab-html-css3.html

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)
https://shaurong.blogspot.com/2021/02/tab-html-css-javascript-jquery-jquery-ui.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascriptjquery.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascript.html

Online HTML Code Generator | CSS Code Generator | JavaScript
https://www.html-code-generator.com/

JQuery Tabs Generator
https://www.html-code-generator.com/jquery/tabs-generator

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

Top Tabs Generator
https://www.pagecolumn.com/tool/top_tabs_generator.htm

Review techniques of making tabs with CSS
https://www.pagecolumn.com/webparts/making_tabs_with_CSS.htm

Tab Menu - Online Tab Menu builder
http://www.menucool.com/horizontal/tab-menu

「CSS3:target選擇器」免程式!純CSS就可實現Tab頁籤式的互動切換 | 梅問題．教學網https://www.minwt.com/webdesign-dev/css/16987.html

bootstrap頁籤
https://codepen.io/fenture/pen/pWEMdR

使用Jquery特效做出Tab頁籤切換效果
https://www.webdesigns.com.tw/jquery_tab.asp

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery 3.5.1 + jQuery UI 1.12.1)

2021-02-23

參考

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

<!doctype html>
<html lang="en">
<head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>jQuery UI Tabs - Default functionality</title>
    <link rel="stylesheet" href="http://code.jquery.com/ui/1.12.1/themes/base/jquery-ui.css">
    <!-- <link rel="stylesheet" href="https://jqueryui.com/resources/demos/style.css"> -->
    <style>
        body {
            font-family: Arial, Helvetica, sans-serif;
        }

        table {
            font-size: 1em;
        }

        .ui-draggable, .ui-droppable {
            background-position: top;
        }
    </style>
    <!-- <script src="https://code.jquery.com/jquery-1.12.4.js"></script> -->
    <script src="https://code.jquery.com/jquery-3.5.1.js"></script>
    <script src="https://code.jquery.com/ui/1.12.1/jquery-ui.js"></script>
    <script>
        $(function () {
            $("#tabs").tabs();
        });
    </script>
</head>
<body>

    <div id="tabs">
        <ul>
            <li><a href="#tabs-1">頁籤1</a></li>
            <li><a href="#tabs-2">頁籤2</a></li>
            <li><a href="#tabs-3">頁籤3</a></li>
        </ul>
        <div id="tabs-1">
            <p>內容1</p>
        </div>
        <div id="tabs-2">
            <p>內容2</p>
        </div>
        <div id="tabs-3">
            <p>內容3</p>
        </div>
    </div>

</body>
</html>

(下圖) IE 11 上會出現「Internet Explorer 已限制這個網頁執行指令碼或 ActiveX 控制項。」

(下圖) IE 11 按下「允許被封鎖的內容」按鈕後。

(完)

相關

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)
https://shaurong.blogspot.com/2021/02/tab-html-jquery-351-bootstrap-341.html

[研究] 頁籤(tab) (HTML + CSS3)
https://shaurong.blogspot.com/2021/02/tab-html-css3.html

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)
https://shaurong.blogspot.com/2021/02/tab-html-css-javascript-jquery-jquery-ui.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascriptjquery.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascript.html

Online HTML Code Generator | CSS Code Generator | JavaScript
https://www.html-code-generator.com/

JQuery Tabs Generator
https://www.html-code-generator.com/jquery/tabs-generator

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

Top Tabs Generator
https://www.pagecolumn.com/tool/top_tabs_generator.htm

Review techniques of making tabs with CSS
https://www.pagecolumn.com/webparts/making_tabs_with_CSS.htm

Tab Menu - Online Tab Menu builder
http://www.menucool.com/horizontal/tab-menu

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)

2021-02-23

範例

<!DOCTYPE html>
<html>
<head>

<meta charset="utf-8">
<script src="https://code.jquery.com/jquery-3.5.1.min.js"></script>

<style>

/* 頁籤的內容框寬度 */
div#hcg_tabs {
    width: 100%;
}
/* 頁籤的標籤基本設定 */
div#tabs-nav {
    position: relative;
    display: flex;
    justify-content: flex-start;
}
/* 頁籤的標籤間隔 */
div#tabs-nav a:nth-child(even) {
    margin: 0 3px;
}

/* 頁籤的標籤設定 */
a.tabs-menu {
    display: inline-block;
    background-color: #1179ac;
    font-size: 12px;
    font-family: Arial,Helvetica,sans-serif;
    color: #fff;
    padding: 5px 10px;
    font-weight: bold;
    text-decoration: none;
    border: solid 2px #1179ac;
    border-bottom: 0;
    border-radius: 3px 3px 0 0;
}

/* 被選的頁籤白色顯示 */
a.tabs-menu.tabs-menu-active {
    background-color: #fff;
    border: solid 2px #1179ac;
    color: #6b6b6b;
    border-bottom: 0;
}

/* 頁籤的內容框 */
.tabs-content {
    border: solid 2px #1179ac;
    margin-top: -2px;
    background-color: #fff;
    overflow: hidden;
    line-height: 1.5;
}

/* 避免一開始全部顯示 */
.tabs-panel {
    display: none;
    min-height: 150px;
    overflow: auto;
    padding: 10px;
    height: 200px;
    font-size: 14px;
}
</style>
</head>
<body>
<div id="hcg_tabs">

 <div id="tabs-nav">
    <a href="#" data-target="tab_1" class="tabs-menu tabs-menu-active">Tab 1</a>
    <a href="#" data-target="tab_2" class="tabs-menu">Tab 2</a>
    <a href="#" data-target="tab_3" class="tabs-menu">Tab 3</a>
 </div>

 <div class="tabs-content">
   <div id="tab_1" class="tabs-panel" style="display:block">
    內容1
   </div>

   <div id="tab_2" class="tabs-panel">
    內容2
   </div>

   <div id="tab_3" class="tabs-panel">
    內容3
   </div>

 </div>

</div>

<script>
$(function() {
    $("#tabs-nav a").click(function(event) {
        event.preventDefault();
        $("#tabs-nav a").removeClass("tabs-menu-active");
        $(this).addClass("tabs-menu-active");
        $(".tabs-panel").hide();
        var tab_id = $(this).data("target");
        $("#"+tab_id).show();
    });
});
</script>

</body>
</html>

(完)

相關

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)
https://shaurong.blogspot.com/2021/02/tab-html-jquery-351-bootstrap-341.html

[研究] 頁籤(tab) (HTML + CSS3)
https://shaurong.blogspot.com/2021/02/tab-html-css3.html

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)
https://shaurong.blogspot.com/2021/02/tab-html-css-javascript-jquery-jquery-ui.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascriptjquery.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascript.html

Online HTML Code Generator | CSS Code Generator | JavaScript
https://www.html-code-generator.com/

JQuery Tabs Generator
https://www.html-code-generator.com/jquery/tabs-generator

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

Top Tabs Generator
https://www.pagecolumn.com/tool/top_tabs_generator.htm

Review techniques of making tabs with CSS
https://www.pagecolumn.com/webparts/making_tabs_with_CSS.htm

Tab Menu - Online Tab Menu builder
http://www.menucool.com/horizontal/tab-menu

[研究] 頁籤(tab) (HTML+CSS+JavaScript)

[研究] 頁籤(tab) (HTML+CSS+JavaScript)

2021-02-23

<!DOCTYPE html>
<html>
<head>
<meta charset="utf-8">
<style>

/* 頁籤的內容框寬度 */
div#hcg_tabs {
    width: 100%;
}
/* 頁籤的標籤基本設定 */
div#tabs-nav {
    position: relative;
    display: flex;
    justify-content: flex-start;
}
/* 頁籤的標籤間隔 */
div#tabs-nav a:nth-child(even) {
    margin: 0 3px;
}

/* 頁籤的標籤設定 */
a.tabs-menu {
    display: inline-block;
    background-color: #1179ac;
    font-size: 12px;
    font-family: Arial,Helvetica,sans-serif;
    color: #fff;
    padding: 5px 10px;
    font-weight: bold;
    text-decoration: none;
    border: solid 2px #1179ac;
    border-bottom: 0;
    border-radius: 3px 3px 0 0;
}

/* 被選的頁籤白色顯示 */
a.tabs-menu.tabs-menu-active {
    background-color: #fff;
    border: solid 2px #1179ac;
    color: #6b6b6b;
    border-bottom: 0;
}

/* 頁籤的內容框 */
.tabs-content {
    border: solid 2px #1179ac;
    margin-top: -2px;
    background-color: #fff;
    overflow: hidden;
    line-height: 1.5;
}

/* 避免一開始全部顯示 */
.tabs-panel {
    display: none;
    min-height: 150px;
    overflow: auto;
    padding: 10px;
    height: 200px;
    font-size: 14px;
}
</style>
</head>
<body>
<div id="hcg_tabs">

 <div id="tabs-nav">
    <a href="#" data-target="tab_1" class="tabs-menu tabs-menu-active">Tab 1</a>
    <a href="#" data-target="tab_2" class="tabs-menu">Tab 2</a>
    <a href="#" data-target="tab_3" class="tabs-menu">Tab 3</a>
 </div>

 <div class="tabs-content">
   <div id="tab_1" class="tabs-panel" style="display:block">
    內容1
   </div>

   <div id="tab_2" class="tabs-panel">
    內容2
   </div>

   <div id="tab_3" class="tabs-panel">
    內容3
   </div>

 </div>

</div>

<script>
(function () {
    var tabs_menu = document.getElementsByClassName("tabs-menu");
    for (var k = 0; k < tabs_menu.length; k++) {
        tabs_menu[k].onclick = js_tabs;
    }
    function js_tabs() {
        var tab_id = this.getAttribute("data-target");
        var tabs_panel = document.getElementsByClassName("tabs-panel");

        for (var i = 0; i < tabs_panel.length; i++) {
            tabs_panel[i].style.display = "none";
        }
        for (var j = 0; j < tabs_menu.length; j++) {
            tabs_menu[j].className = tabs_menu[j].className.replace(" tabs-menu-active", "");
        }
        this.className += " tabs-menu-active";
        document.getElementById(tab_id).style.display = "block";
        return false;
    }
})();
</script>

</body>
</html>

(完)

相關

[研究] 頁籤(tab) (HTML + jQuery 3.5.1 + Bootstrap 3.4.1)
https://shaurong.blogspot.com/2021/02/tab-html-jquery-351-bootstrap-341.html

[研究] 頁籤(tab) (HTML + CSS3)
https://shaurong.blogspot.com/2021/02/tab-html-css3.html

[研究] 頁籤(tab) ( HTML + CSS + JavaScript + jQuery + jQuery UI)
https://shaurong.blogspot.com/2021/02/tab-html-css-javascript-jquery-jquery-ui.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript+jQuery)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascriptjquery.html

[研究] 頁籤(tab) (HTML+CSS+JavaScript)
https://shaurong.blogspot.com/2021/02/tab-htmlcssjavascript.html

Online HTML Code Generator | CSS Code Generator | JavaScript
https://www.html-code-generator.com/

JQuery Tabs Generator
https://www.html-code-generator.com/jquery/tabs-generator

Tabs | jQuery UI
https://jqueryui.com/tabs/

Tabs Widget | jQuery UI 1.8 Documentation
https://api.jqueryui.com/1.8/tabs/

Top Tabs Generator
https://www.pagecolumn.com/tool/top_tabs_generator.htm

Review techniques of making tabs with CSS
https://www.pagecolumn.com/webparts/making_tabs_with_CSS.htm

Tab Menu - Online Tab Menu builder
http://www.menucool.com/horizontal/tab-menu

「CSS3:target選擇器」免程式!純CSS就可實現Tab頁籤式的互動切換 | 梅問題．教學網https://www.minwt.com/webdesign-dev/css/16987.html

bootstrap頁籤
https://codepen.io/fenture/pen/pWEMdR

使用Jquery特效做出Tab頁籤切換效果
https://www.webdesigns.com.tw/jquery_tab.asp

2021年2月18日星期四

[研究][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

[研究] Wifi 無線網路出現 RTL8186-default

[研究] Wifi 無線網路出現 RTL8186-default

2021-02-17

現在部分無線路由器，有主人和客人網路之分。所以可能出現2個 WiFi SSID，一個不需密碼就可連線。可以進入設定畫面設定密碼或關閉。

(完)

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

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

2021-02-18

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

packages.conf

<?xml version="1.0" encoding="utf-8"?>
<packages>
  <package id="DocumentFormat.OpenXml" version="2.12.2" 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="TempFolder" value="D:\WWW\Temp" />
	</appSettings>
</configuration>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportXlsxByOpenXMLSDKTest.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_Xlsx_By_OpenXML" runat="server" 
                OnClick="Button_Export_Xlsx_By_OpenXML_Click" Text="匯出(.xlsx)" /><br />
            <asp:Label ID="Label_MSG1" runat="server"></asp:Label>
        </div>
    </form>
</body>
</html>

Default.aspx.cs

using DocumentFormat.OpenXml;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data.SqlClient;
using System.IO;
using System.Linq;
using System.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ExportXlsxByOpenXMLSDKTest
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Button_Export_Xlsx_By_OpenXML_Click(object sender, EventArgs e)
        {
            /*
            Open XML SDK 2.5 for Microsoft Office
            https://www.microsoft.com/en-us/download/details.aspx?id=30425

            微軟官方下載只提供到 2.5 版，NuGet 才能下載到更新版。
            另外 OpenXML 在不同版本時，API function method 用法不完全相同。

            Welcome to the Open XML SDK 2.5 for Office
            2017/11/01
            https://docs.microsoft.com/zh-tw/office/open-xml/open-xml-sdk

            DocumentFormat.OpenXml 2.7.2
            https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.7.2

            DocumentFormat.OpenXml 2.8.1
            https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.8.1

            DocumentFormat.OpenXml 2.12.2
            https://docs.microsoft.com/zh-tw/dotnet/api/documentformat.openxml.spreadsheet?view=openxml-2.12.2

            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 = "匯出檔案名稱";
            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 + ".xlsx";

            // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            string dirFileName = fd + mainFileName + "-" +
                dateTimeString + ".xlsx";
            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 = "";

                    SpreadsheetDocument spreadsheetDocument = SpreadsheetDocument.Create(myMemoryStream, SpreadsheetDocumentType.Workbook, true);
                    WorkbookPart workbookpart = spreadsheetDocument.AddWorkbookPart();
                    workbookpart.Workbook = new Workbook();
                    // Add a WorksheetPart to the WorkbookPart.
                    WorksheetPart worksheetPart = workbookpart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook.
                    Sheets sheets = spreadsheetDocument.WorkbookPart.Workbook.AppendChild(new Sheets());
                    // Append a new worksheet and associate it with the workbook.
                    Sheet sheet = new Sheet() { Id = spreadsheetDocument.WorkbookPart.GetIdOfPart(worksheetPart), SheetId = 1, Name = "工作表1" };
                    sheets.Append(sheet);
                    // Get the sheetData cell table.
                    SheetData sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();
                    // Add a column row to the cell table.
                    Row colrow;
                    colrow = new Row() { RowIndex = 1 };
                    sheetData.Append(colrow);

                    // 定義欄位名稱列
                    string[] columnNameArray = { "id", "FieldText", "FieldDateTime", "FieldInt", "FieldBit" };
                    // Column number must be between 1 and 16384
                    for (int i = 0; i < columnNameArray.Count(); i++)
                    {
                        Cell CReceiptID = new Cell();
                        CReceiptID.CellValue = new CellValue(columnNameArray[i]);
                        CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                        colrow.InsertAt(CReceiptID, i);
                        //string debugValue = sheetData.Cell(i,j).GetString();
                    }

                    // 讀取資料，資料寫入「工作表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();

                        // Add rows to the cell table
                        UInt32Value DataIndex = new UInt32Value();
                        DataIndex.Value = 2;     // 1 是標題列
                        //int rowIndex = 2;   // 1 是標題列
                        while (reader.Read())
                        {
                            Row row;
                            row = new Row() { RowIndex = DataIndex.Value };
                            sheetData.Append(row);

                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                //reader[i]的 i 要從 0 開始
                                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("\\", "");

                                Cell CReceiptID = new Cell();
                                CReceiptID.CellValue = new CellValue(dataText);
                                CReceiptID.DataType = new EnumValue<CellValues>(CellValues.String);
                                row.InsertAt(CReceiptID, i);
                            }
                            DataIndex.Value++;
                        }

                        Response.Clear();
                        Response.Buffer = true;
                        Response.Charset = "";
                        //Response.ContentEncoding = Encoding.GetEncoding(950);   //950就是所謂的BIG5
                        //Response.AddHeader("Content-Disposition", "attachment;filename=\"" +
                        //    HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + "-" +
                        //    DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
                        Response.AddHeader("Content-Disposition", "attachment;filename=\"" + outFileName);
                        //'Excel 2003 : "application/vnd.ms-excel"
                        //'Excel 2007 : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

                        //Extension       MIME Type
                        //.xlsx               application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
                        //.xltx                application/vnd.openxmlformats-officedocument.spreadsheetml.template
                        //.potx               application/vnd.openxmlformats-officedocument.presentationml.template
                        //.ppsx              application/vnd.openxmlformats-officedocument.presentationml.slideshow
                        //.pptx               application/vnd.openxmlformats-officedocument.presentationml.presentation
                        //.sldx               application/vnd.openxmlformats-officedocument.presentationml.slide
                        //.docx              application/vnd.openxmlformats-officedocument.wordprocessingml.document
                        //.dotx               application/vnd.openxmlformats-officedocument.wordprocessingml.template
                        //.xlam              application/vnd.ms-excel.addin.macroEnabled.12
                        //.xlsb               application/vnd.ms-excel.sheet.binary.macroEnabled.12

                        //Response.ContentType = "application/ms-excel";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                        //Response.TransmitFile(@"C:\Program Files (x86)\IIS Express\Export.xlsx");
                        //dirFileName = dirFileName.Replace('\\', '/');   //轉換 Windows, Linux 不同路徑斜線
                        //dirFileName = dirFileName.Replace("/", "\\");  // 轉換斜線 / 成 \  ，因 \ 是特殊符號，要以 \\ 表示

                        // 方法1: 直接輸出
                        worksheetPart.Worksheet.Save();
                        workbookpart.Workbook.Save();
                        spreadsheetDocument.SaveAs(dirFileName);    // 方法2: 寫入檔案再輸出
                        spreadsheetDocument.Close();

                        myMemoryStream.WriteTo(Response.OutputStream);
                        Response.Flush();

                        // // 方法2: 寫入檔案再輸出
                        //Response.TransmitFile(dirFileName);   // 把存檔輸出

                        // 3.輸出結束
                        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>");
            }
        }
        // ----------
    }
}

不過根據以往經驗，元件直接產生的 .xlsx，和用 Excel 建立的 .xlsx，實際的檔案骨架 schema 好像沒有 100% 相同，前者用某些元件或版本匯入可能正常或會出錯，但前者用 Microsoft Excel 軟體直接開啟正常。

或可考慮先用 Excel 2016 或 Excel 2019 做好一個沒填寫內容的活頁簿 (Workbook) 和工作表1 (Worksheet)，複製後 (避免多人同開開起讀寫)，再拿來寫入、關閉、匯出。

(完)

2021年2月17日星期三

[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)

[研究][ASP.NET]使用 ClosedXML 0.95.4 匯出、寫入 .xlsx(暫存、不暫存檔)

2021-02-17
2021-03-16 更新
2022-03-23增加格線、自適應欄寬、水平向上對整
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>

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>

Default.aspx

<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Default.aspx.cs" Inherits="ExportXlsxByClosedTest.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_Xlsx_By_ClosedXML" runat="server" 
                OnClick="Button_Export_Xlsx_By_ClosedXML_Click" Text="匯出(.xlsx)" /><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.Text;
using System.Web;
using System.Web.Configuration;
using System.Web.UI;
using System.Web.UI.WebControls;

namespace ExportXlsxByClosedTest
{
    public partial class Default : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }
        protected void Button_Export_Xlsx_By_ClosedXML_Click(object sender, EventArgs e)
        {
            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 + ".xlsx";

            // XLWorkbook.SaveAs() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            // Response.TransmitFile() 輸出檔案名稱不要用 HttpUtility.UrlEncode 編碼
            string dirFileName = fd + mainFileName + "-" +
                dateTimeString + ".xlsx";
            try
            {
                Response.Clear();
                Response.Buffer = true;
                Response.Charset = "";

                #region Export 

                // [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 = "";

                    var wb = new XLWorkbook();
                    var ws = wb.Worksheets.Add("工作表1");

                   // 否則會出現類似 The range 工作表1!A1:U57 overlaps with the worksheet's autofilter. 錯誤訊息
                    ws.AutoFilter.IsEnabled = false;

                    // 定義欄位名稱列
                    string[] columnNameArray = { "id", "FieldText", "FieldDateTime", "FieldInt", "FieldBit" };
                    // Column number must be between 1 and 16384
                    for (int i = 0; i < columnNameArray.Count(); i++)
                    {
                        ws.Cell(1, i+1).Value = 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 = 1;   // 1 是標題列
                        while (reader.Read())
                        {
                            rowIndex++;
                            for (int i = 0; i < reader.FieldCount; i++)
                            {
                                //reader[i]的 i 要從 0 開始
                                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("\\", "");

                                // Column number must be between 1 and 16384
                                ws.Cell(rowIndex, i+1).Value = dataText;
                            }
                        }

                        //設定區域範圍的  儲存格框線
                        //ws.Ranges("A1:F5").Style.Border.TopBorder = XLBorderStyleValues.Thin;
                        string endCell = "S" + rowIndex.ToString();
                        ws.Ranges("A1:" + endCell).Style.Border.TopBorder = XLBorderStyleValues.Thin;
                        ws.Ranges("A1:" + endCell).Style.Border.LeftBorder = XLBorderStyleValues.Thin;
                        ws.Ranges("A1:" + endCell).Style.Border.RightBorder = XLBorderStyleValues.Thin;
                        ws.Ranges("A1:" + endCell).Style.Border.BottomBorder = XLBorderStyleValues.Thin;

                        ws.Columns().AdjustToContents();// 自適應欄寬，這要在資料寫完後，檔案儲存關閉前再做

                        ws.Ranges("A1:" + endCell).Style.Alignment.Vertical = XLAlignmentVerticalValues.Top; // 水平向上對齊

                        Response.Clear();
                        Response.Buffer = true;
                        Response.ContentEncoding = Encoding.GetEncoding(950);   //950就是所謂的BIG5
                                                                                //Response.AddHeader("Content-Disposition", "attachment;filename=\"" +
                                                                                //    HttpUtility.UrlEncode(fileName, System.Text.Encoding.UTF8) + "-" +
                                                                                //    DateTime.Now.ToString("yyyyMMddHHmmss") + ".xlsx");
                        Response.AddHeader("Content-Disposition", "attachment;filename=\"" + outFileName);
                        //'Excel 2003 : "application/vnd.ms-excel"
                        //'Excel 2007 : "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet"

                        //Extension       MIME Type
                        //.xlsx               application/vnd.openxmlformats-officedocument.spreadsheetml.sheet
                        //.xltx                application/vnd.openxmlformats-officedocument.spreadsheetml.template
                        //.potx               application/vnd.openxmlformats-officedocument.presentationml.template
                        //.ppsx              application/vnd.openxmlformats-officedocument.presentationml.slideshow
                        //.pptx               application/vnd.openxmlformats-officedocument.presentationml.presentation
                        //.sldx               application/vnd.openxmlformats-officedocument.presentationml.slide
                        //.docx              application/vnd.openxmlformats-officedocument.wordprocessingml.document
                        //.dotx               application/vnd.openxmlformats-officedocument.wordprocessingml.template
                        //.xlam              application/vnd.ms-excel.addin.macroEnabled.12
                        //.xlsb               application/vnd.ms-excel.sheet.binary.macroEnabled.12

                        //Response.ContentType = "application/ms-excel";
                        Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

                        //Response.TransmitFile(@"C:\Program Files (x86)\IIS Express\Export.xlsx");
                        //dirFileName = dirFileName.Replace('\\', '/');   //轉換 Windows, Linux 不同路徑斜線
                        //dirFileName = dirFileName.Replace("/", "\\");  // 轉換斜線 / 成 \  ，因 \ 是特殊符號，要以 \\ 表示

                        // 方法1: 直接輸出
                        wb.SaveAs(myMemoryStream);
                        Response.BinaryWrite(myMemoryStream.ToArray());
                        // myMemoryStream.WriteTo(Response.OutputStream); //works too
                        Response.Flush();
                        Response.Close();
                        
                        // 方法2: 若需要寫入檔案再輸出
                        wb.SaveAs(dirFileName);                 // 存檔，Debug 或其他用途
                        ////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>");
            }
        }
        // ----------
    }
}

(完)

相關

[研究][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