Editing Microsoft Excel

== Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits ==
不同試算表方案的工作表或儲存格的大小限制
<table border="1">
 <tr>
   <th>Title</th>
   <th>File extension</th>
   <th>Data rows size</th>
   <th>Length of cell</th>
   <th>Available solutions</th>
 </tr>
 <tr>
   <td>Microsoft Excel Open XML Spreadsheet</td>
   <td>XLSX</td>
   <td>Maximum of worksheet size is 1,048,576 rows by 16,384 columns.<ref>[https://support.office.com/en-us/article/Excel-specifications-and-limits-1672b34d-7043-467e-8e27-269d656771c3?ui=en-US&rs=en-US&ad=US&fromAR=1 Excel specifications and limits]</ref><ref>[http://office.microsoft.com/zh-tw/excel-help/HP010073849.aspx Excel 的規格及限制] 工作表大小</ref><ref>[http://office.microsoft.com/en-us/excel-help/excel-specifications-and-limits-HA103980614.aspx Excel specifications and limits - Excel]</ref> </td>
   <td>(1) Length of formula contents: 8,192 characters <br />(2) Total number of characters that a cell can contain: 32,767 characters<ref>[https://errerrors.blogspot.com/2016/02/navicat.html Err: 解決 Navicat 「存放裝置空間不足，無法完成此操作」問題訊息]</ref></td>
   <td>Microsoft Excel 2007, EXCEL 2013 and [https://fileinfo.com/extension/xlsx more]</td>
 </tr>
 <tr>
   <td>Microsoft Excel Spreadsheet</td>
   <td>XLS</td>
   <td>Maximum of worksheet size is 65,536 rows by 256 columns. <br />{{exclaim}} Because first row is occupied by column name, actual data size is 65,535 rows. <ref>[http://superuser.com/questions/366468/what-is-the-maximum-allowed-rows-in-a-microsoft-excel-xls-or-xlsx What is the maximum allowed rows in a Microsoft Excel .xls or .xlsx - Super User]</ref><ref>[https://support.office.com/en-us/article/Worksheet-compatibility-issues-f9c80c5b-5afc-40da-a841-b888746abd40 Worksheet compatibility issues - Excel].</ref></td>
   <td></td>
   <td>Microsoft Excel 2003 and [https://fileinfo.com/extension/xls more]</td>
 </tr>
 <tr>
   <td>OpenDocument Spreadsheet</td>
   <td>ODS</td>
   <td>Maximum of worksheet size is 1,048,576 (2^20) rows by 1024 columns for LibreOffice spreadsheet & OpenOffice.org Calc 3 <ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref><ref>[https://simple.wikipedia.org/wiki/OpenOffice_Calc OpenOffice Calc - Simple English Wikipedia, the free encyclopedia]</ref>. </td>
   <td>Total number of characters that a cell can contain: 32,767 characters for LibreOffice spreadsheet<ref>[https://wiki.documentfoundation.org/Faq/Calc/022 Frequently asked questions - Calc - The Document Foundation Wiki]</ref>.</td>
   <td>[http://www.libreoffice.org/ LibreOffice]([http://portableapps.com/apps/office/libreoffice_portable/ LibreOffice Portable])  spreadsheet 3.3.3+ & Apache [https://www.openoffice.org/ OpenOffice.org] Calc 3+ and [https://fileinfo.com/extension/ods more]</td>
 </tr>
 <tr>
   <td>[https://www.google.com/sheets/about/ Google spreadsheet]</td>
   <td></td>
   <td>"Up to 2 million cells for spreadsheets that are created in or converted to Google Sheets" <br />2,000,000 cells = 7,812 rows x 256 columns per sheet.<ref>[https://support.google.com/drive/answer/37603?hl=zh-Hant Google 文件、試算表和簡報的檔案大小限制 - 雲端硬碟說明] [https://support.google.com/drive/answer/37603?hl=en Files you can store in Google Drive - Google Drive Help]</ref></td>
   <td></td>
   <td></td>
 </tr>
 <tr>
   <td>[https://www.zoho.com/docs/sheet.html Zoho Sheet]
   <td></td>
   <td>"Up to 1 million cells for a single spreadsheet with a maximum number of 65536 rows and 256 columns per sheet."<ref>[https://www.zoho.com/docs/faq/online-spreadsheet.html Zoho Docs - FAQ on Online Spreadsheet]</ref></td>
   <td></td>
   <td></td>
 </tr>
</table>


* Office 365: [http://office.microsoft.com/zh-tw/excel-help/HA102837464.aspx 資料模型的規格與限制 - Excel - Office.com] 適用: Excel 2013, Power BI for Office 365 Preview, Power Pivot in Excel 2013


== Export MySQL query to Excel file ==
[[Export MySQL query to Excel file]]

== Export CSV from EXCEL ==
Excel 檔案，匯出 UTF-8 編碼的 CSV 檔案
* 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」，但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。
* [https://errerrors.blogspot.com/2018/12/how-to-fix-scientific-notation-on-excel-or-csv.html 解決 Excel 轉 CSV 檔案的數字變為科學符號的問題]

<table border="1" class="wikitable sortable">
<tr>
<th>軟體</th>
<th>操作方式</th>
<th>輸出 CSV 檔案格式</th>
</tr>
<tr>
<td>[https://zh-tw.libreoffice.org/ LibreOffice] Calc ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable]) 版本 6.2.4.2</td>
<td>檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：Unicode (UTF-8)</td>
<td>{{Gd}} UTF-8 編碼 不帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>[https://zh-tw.libreoffice.org/ LibreOffice] Calc ([http://portableapps.com/apps/office/libreoffice_portable LibreOffice Portable]) 版本 6.2.4.2</td>
<td>檔案 -> 另存新檔 -> 存檔類型：文字 CSV (.csv) -> 字元集：正體中文 (Big5)</td>
<td>Big5 編碼</td>
</tr>
<td>Google 雲端硬碟</td>
<td>檔案 -> 下載 -> 逗號分隔值檔案 (.csv，目前工作表)</td>
<td>{{Gd}} UTF-8 編碼 不帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>Microsoft Excel on {{Mac}} 版本 16.33</td>
<td>檔案 -> 另存新檔 -> 檔案格式：CSV UTF-8 逗號分隔(.csv)</td>
<td>UTF-8 編碼，帶[[Byte order mark |簽名(BOM)]]</td>
</tr>
<tr>
<td>Microsoft Excel on {{Mac}} 版本 16.32</td>
<td>檔案 -> 另存新檔 -> 檔案格式：逗號分隔值 (.csv)</td>
<td>Big5 編碼</td>
</tr>
<tr>
<td>Microsoft Office 2016 on {{Win}}</td>
<td>檔案 -> 另存新檔 -> (選擇存檔位置) -> 存檔類型：CSV 逗號分隔 (.csv)</td>
<td>Big5 編碼</td>
</tr>
<tr>
</table>

== Import CSV to EXCEL ==
* [http://office.microsoft.com/zh-tw/excel-help/HP010099725.aspx 匯入或匯出文字檔案 - Excel] 支援 Unicode 編碼 {{Win}} 圖形教學: [https://www.managertoday.com.tw/articles/view/55615 Excel打開是亂碼？教你換一個「開啟方式」，瞬間找回所有資料｜經理人]
* 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM，這樣才能點選兩下 CSV，Excel 開啟時沒有問題。 (Excel 2007) {{Win}}
* [http://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}} 
* [https://errerrors.blogspot.com/2019/01/fix-garbled-text-after-import-csv-on-mac-excel.html 解決 Mac 系統匯入中文 CSV 檔案遇到亂碼問題 (Excel on Mac 版本 16.20, Office 365)] {{Mac}}
* [http://errerrors.blogspot.com/2014/05/mac-office-excel-csv-microsoft-excel.html Mac Office Excel 匯入中文 CSV 檔案 (Microsoft Excel for Mac 2011)] {{Mac}}
* [https://errerrors.blogspot.com/2018/10/import-csv-file-on-mac-libreoffice.html 解決 MAC 電腦匯入中文 CSV 檔案遇到亂碼問題 (LibreOffice v. 6.1.2.1)] {{Mac}}
* 雖然 CSV 欄位已經用雙引號區隔，但是如果欄位裡面也有包含雙引號，匯入到 Excel，會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ，改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc]

=== 讓 CSV 可以方便讓 Excel 直接開啟 ===
CSV 檔案 (測試的 Excel 版本: Excel 2016 on {{Win}}、LibreOffice Calc  v. 5.4.3.2 on {{Win}}、Excel for {{Mac}} 2011 v. 14.7.7)
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、'''有''' BOM<ref>[https://stackoverflow.com/questions/3127436/adding-bom-to-utf-8-files utf 8 - Adding BOM to UTF-8 files - Stack Overflow]</ref> → 
** {{Gd}} 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，而且不同欄位不會擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 Excel for (測試版本：{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)

* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM → 
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「逗號」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49)

* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM → 
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，不會看到中文變成亂碼，但是不同欄位擠到同一個欄位 (儲存格) (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，需要調整字元集「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7)

* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、沒有 [https://zh.wikipedia.org/wiki/%E4%BD%8D%E5%85%83%E7%B5%84%E9%A0%86%E5%BA%8F%E8%A8%98%E8%99%9F BOM] (byte-order mark) → 
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：Excel 2016 on {{Win}})
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案，會看到「文字匯入」對話視窗，字元集正確偵測是「Unicode (UTF-8)」，分隔符號則已經勾選「定位鍵」，即可順利匯入資料
** 使用 Excel on {{Mac}} 直接開啟，會看到中文變成亂碼，需要改用匯入外部資料方式 (測試版本：{{Mac}} 2011 v. 14.7.7)

相關文章
* [https://errerrors.blogspot.com/2021/06/how-to-fix-garbled-text-in-csv-file-opened-by-excel.html 不要把 CSV 轉成 Big5 編碼，怎樣正確解決 Excel 開 CSV 檔案遇到亂碼]

== Import XLS to MySQL ==
* [https://www.toadworld.com/m/freeware/1469 Toad for MySQL Freeware v7] 32 bit 匯入 xls 檔案時，軟體視窗凍結沒有反應
* [http://www.excel2mysql.net/index.html Excel2MySQL]  v 2.6.3 匯入後發現有掉資料
* ''$'' [http://www.navicat.com/cht/products/navicat-for-mysql Navicat for MySQL | MySQL 資料庫管理和開發工具]

== Batch convert TXT/CSV to EXCEL ==
原始檔案: 欄位值用雙引號框起來(enclosure)、不同欄位資料用定位鍵 {{kbd | key = Tab}} 間隔、檔案編碼 UTF8 沒有 BOM
<pre>
"column_1" \t "column_2" \t "column_3"
</pre>

批次轉檔的方案比較
# {{Gd}} ''$'' [http://www.dbf2002.com/csv-converter/ Advanced CSV Converter] v 5.55 ok 
#* 轉檔時可以選擇 UTF-8 編碼，輸出的中文 Excel 不會亂碼。
#* 但是不能辨識欄位值前後的雙引號 符號，所以原始檔案需要先去除欄位值前後的雙引號 符號。
#* 試用版只能匯出 50 筆資料。
# [http://www.softinterface.com/Convert-XLS/Convert-XLS.htm ConvertXLS] v. 8.54 中文亂碼 {{exclaim}}
# [https://bytescout.com/products/enduser/spreadsheettools/spreadsheettools.html Bytescout Spreadsheet Tools ] v. 1.10.0.21 中文亂碼 {{exclaim}}
# [https://drive.google.com/ Google 雲端硬碟] 上傳 CSV 檔前，要勾選「轉換上傳檔案 -- 將已上傳的檔案轉換成 Google 文件編輯器格式」。上傳後，再下載會變成 Excel 格式。 {{access | date = 2016-12-30}}

== Copy & Paste ==
=== from spreadsheet application to another spreadsheet application ===
{{#widget:Iframe
|url=https://docs.google.com/spreadsheet/pub?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&single=true&gid=0&range=A1%3AH8&output=html
|width=100%
|height=AUTO
|border=1
}}
[https://docs.google.com/spreadsheet/ccc?key=0AuFlnVY9TNrsdDJodlRYRG1zWUY4dFd4SktiZzVyYlE&usp=sharing 線上編輯上面表格]

* Microsoft Excel --> Google Spreadsheet: 
** columns & rows: Copy & Paste is ok 
* Plain text --> Google Spreadsheet: ok 
** columns: {{kbd | key = Tab}} separated columns --> Google Spreadsheet: ok 
** rows: {{kbd | key = Enter}} separated rows --> Google Spreadsheet: ok 
* Plain text --> Microsoft Excel: ok 
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok 
** rows: {{kbd | key = Enter}} separated rows --> Microsoft Excel: ok 
* Microsoft Excel --> Table of Google Document: 
** columns & rows: Copy & Paste is ok 
* Plain text --> Table of Google Document: fail {{exclaim}} Suggest you copy to Microsoft Excel first and then copy paste to Google Document from Microsoft Excel.
** columns: {{kbd | key = Tab}} separated columns --> Table of Google Document: fail 
** rows: {{kbd | key = Enter}} separated rows --> Table of Google Document: fail 
* Plain text --> Microsoft Excel: ok 
** columns: {{kbd | key = Tab}} separated columns --> Microsoft Excel: ok 
** rows: {{kbd | key = Enter}} separated rows --> Microsoft Excel: ok
* [http://www.youtube.com/watch?v=Q43U-7r5AMk&desktop_uri=%2Fwatch%3Fv%3DQ43U-7r5AMk&nomobile=1 How-to Create and Copy a Table in Google Mail (Gmail) from Excel - YouTube]


further reading
* [http://www.playpcesor.com/2016/03/note-to-table-google-sheet.html 純文字筆記也能一鍵轉表格！ Google 試算表小技巧 - 電腦玩物]

=== from spreadsheet application to editor application ===
* Copy the table from Gmail/HTML ...
** Paste to MS Excel: the border line of table is missing {{exclaim}}
** Paste to MS Word: the border line of table is exists after posted
* Copy the table from MS Excel ...
** Paste to Gmail: the border line of table is missing {{exclaim}}
** Paste to MS Word: the border line of table is exists after posted
* Copy the table from MS Word ...
** Paste to Gmail: the border line of table is exists after posted
** Paste to MS Word: the border line of table is exists after posted

== Excel online viewer ==
[https://www.google.com/drive/ Google drive] {{access | date=2020-03-10}}
* file limit: "Up to 5 million cells or 18,278 columns (column ZZZ) for spreadsheets that are created in or converted to Google Sheets."<ref>[https://support.google.com/drive/answer/37603?hl=en Files you can store in Google Drive - Google Drive Help]</ref>

[https://onedrive.live.com/about/zh-tw/ Microsoft OneDrive] 
* file limit: 5MB<ref>[https://support.office.com/zh-TW/client/results?Shownav=true&lcid=1028&ns=XLWAEndUser&version=15&omkt=zh-TW&ver=15&apps=WDWAENDUSER%2CXLWAENDUSER%2CPPWAENDUSER%2CONWAENDUSER&HelpID=BIGWORKBOOK 支援 - Office.com]</ref>

[https://www.zoho.com/sheet/ Online Spreadsheet Maker | Create Spreadsheets for free - Zoho Sheet]
* file limit: "Max number of cells with data = 1 million/workbook"<ref>[https://help.zoho.com/portal/en/community/topic/limits-of-sheets Limits of Sheets]</ref>

== Google spreadsheet ==
利用[[Google spreadsheet]]的函式，分析及統計可複選的問卷題目結果。

== Excel 「樞紐分析表(Pivot Tables)」==
區塊「Σ 值」的「計數 - 欄位名稱」(該欄位的項目個數)，除非是沒有填入任何資料的「空值」，否則都會被計算加 1。包含 
* 邏輯值 {{kbd | key =TRUE}}、{{kbd | key =FALSE}}、字串、數值、錯誤代碼 {{kbd | key =<nowiki>#NAME?</nowiki>}} 等資料，會被列入計算加 1
* {{kbd | key =<nowiki>IF</nowiki>}} 回傳的空值，也會被列入計算加 1 {{exclaim}} version: Excel 2013

== Checking of data type ==
儲存格資料類型檢查與類型轉換

數字
* 預期: 可以「從最小到最大排序」、「從最大到最小排序」
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式，改成數值格式的年份: 
** 法1: [https://support.office.com/zh-tw/article/%E5%B0%87%E5%84%B2%E5%AD%98%E6%88%90%E6%96%87%E5%AD%97%E7%9A%84%E6%95%B8%E5%80%BC%E8%BD%89%E6%8F%9B%E6%88%90%E6%95%B8%E5%80%BC%E6%A0%BC%E5%BC%8F-40105f2a-fe79-4477-a171-c5bad0f0a885 將儲存成文字的數值轉換成數值格式 - Office 支援] {{access | date = 2018-11-05}} 

時間
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份: 
** 法1: [https://errerrors.blogspot.com/2016/12/excel-value-text.html?q=excel Excel 如何轉換文字格式的日期時間格式 (使用 VALUE, TEXT 函數) | Err]

時間: 年份
* 預期: 可以「從最舊到最新排序」、「從最新到最舊排序」。
* 異常: 如果是「從 A 到 Z 排序」、「從 Z 到 A 排序」，需要將通用格式的年份，改成時間格式的年份: 
** 法1: 將 2010 改成 2010/01/01 ，再利用 YEAR 函數擷取出年份
** 法2: 將 2010 改成 2010/01/01 ，再利用 [http://www.tableau.com/ Tableau]''$'' 轉換成年份 {{exclaim}} walk around approach!

== Troubleshooting of Excel errors ==
[[Troubleshooting of common Excel errors in Mandarin]]

== 不同試算表方案的工作表或儲存格的大小限制 ==
[[Export MySQL query to Excel file#Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits | Microsoft Excel (XLS or XLSX) & OpenDocument Spreadsheet (ODS) file limits]]

== Merge data from different files ==
Merge multiple CSV / Text files by using Windows command (命令提示字元)<ref>[http://windows.microsoft.com/zh-tw/windows/command-prompt-faq#1TC=windows-8 命令提示字元：常見問題集 - Windows 說明]</ref>
* {{kbd | key = copy *.csv bundle.csv}} for different CSV files 
* {{kbd | key = copy *.txt bundle.txt}} for different Text files 

Merge Excel worksheets (copy data from multiple worksheets into one workbook)
* ''$'' [https://www.ablebits.com/consolidate-worksheets-excel/index.php Merge multiple Excel worksheets into 1 - Consolidate Worksheets Wizard add-in]
* ''$'' [https://www.extendoffice.com/product/kutools-for-excel.html Kutools - Combines More Than 300 Advanced Functions and Tools for Microsoft Excel]
* [https://www.ptt.cc/bbs/EZsoft/M.1632673165.A.31E.html ExcelMerger 幫你合併 Excel 檔 - 看板 EZsoft - 批踢踢實業坊]

== PHP libraby ==
* [https://github.com/PHPOffice/PhpSpreadsheet PHPOffice/PhpSpreadsheet: A pure PHP library for reading and writing spreadsheet files]
* [https://github.com/rap2hpoutre/fast-excel rap2hpoutre/fast-excel: 🦉 Fast Excel import/export for Laravel]
* [https://github.com/Maatwebsite/Laravel-Excel Maatwebsite/Laravel-Excel: 🚀 Supercharged Excel exports and imports in Laravel]

== Further reading ==

* [https://tools.ietf.org/html/rfc4180 RFC 4180 - Common Format and MIME Type for Comma-Separated Values (CSV) Files]

== References ==
<references/>

{{Template:Data factory flow}}

[[Category: Software]]
[[Category: Spreadsheet]] 
[[Category: Excel]]  
[[Category: MySQL]]
[[Category: Data Science]]