14,954
edits
m (Text replacement - "https://news.google.com/news/rss/search/section/q/" to "https://news.google.com/_/rss/search?q=") |
No edit summary |
||
| (30 intermediate revisions by the same user not shown) | |||
| Line 1: | Line 1: | ||
== 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 == | ||
| Line 4: | Line 54: | ||
== Export CSV from EXCEL == | == Export CSV from EXCEL == | ||
Excel 檔案,匯出 UTF-8 編碼的 CSV 檔案 | |||
* 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」,但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。 | * 雖然 Excel 本身也有提供匯出 「Unicode 文字 (*.txt)」,但是因為無法設定使用雙引號框住欄位值。會造成後續匯入檔案容易出問題。 | ||
* Excel | * [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 == | == 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打開是亂碼?教你換一個「開啟方式」,瞬間找回所有資料|經理人] | * [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}} | * 欄位內容分隔符號 " (雙引號); 欄位分隔符號 , 不可夾雜空白。Unicode 檔案需要檔案有 BOM,這樣才能點選兩下 CSV,Excel 開啟時沒有問題。 (Excel 2007) {{Win}} | ||
* [http://errerrors.blogspot. | * [http://errerrors.blogspot.com/2010/10/csv-microsoft-excel-2007-openofficeorg.html 解決CSV檔亂碼 (Microsoft Excel 2007 或 OpenOffice.org 3.2)] {{Win}} | ||
* [http://errerrors.blogspot. | * [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,會導致欄位錯誤的問題。解法1: 原始資料的雙引號改成單引號、解法2: 不用 Excel ,改用 [http://www.libreoffice.org/discover/calc/ LibreOffice Calc] | ||
=== 讓 CSV 可以方便讓 Excel 直接開啟 === | === 讓 CSV 可以方便讓 Excel 直接開啟 === | ||
CSV 檔案 (測試的 Excel 版本: 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> → | * 欄位間隔符號 {{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}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,而且不同欄位不會擠到同一個欄位 (儲存格) | ** {{Gd}} 使用 Excel on {{Win}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,而且不同欄位不會擠到同一個欄位 (儲存格) (測試版本:Excel 2016 on {{Win}}) | ||
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料 | ** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料 | ||
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 | ** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 Excel for (測試版本:{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49) | ||
* 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM → | * 欄位間隔符號 {{kbd | key = ,}} 、UTF-8 編碼、沒有 BOM → | ||
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 | ** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Excel 2016 on {{Win}}) | ||
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料 | ** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「逗號」,即可順利匯入資料 | ||
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 | ** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:{{Mac}} 2011 v. 14.7.7、Mac 版 Microsoft Excel 365 版本 16.49) | ||
* 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM → | * 欄位間隔符號 {{kbd | key = TAB}} 、UTF-8 編碼、'''有''' BOM → | ||
** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,但是不同欄位擠到同一個欄位 (儲存格) | ** 使用 Excel on {{Win}} 點選兩下檔案直接開啟,不會看到中文變成亂碼,但是不同欄位擠到同一個欄位 (儲存格) (測試版本:Excel 2016 on {{Win}}) | ||
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料 | ** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,需要調整字元集「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料 | ||
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 | ** 使用 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) → | * 欄位間隔符號 {{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 on {{Win}} 點選兩下檔案直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 (測試版本:Excel 2016 on {{Win}}) | ||
** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料 | ** 使用 LibreOffice Calc on {{Win}} 直接開啟檔案,會看到「文字匯入」對話視窗,字元集正確偵測是「Unicode (UTF-8)」,分隔符號則已經勾選「定位鍵」,即可順利匯入資料 | ||
** 使用 Excel on {{Mac}} 直接開啟,會看到中文變成亂碼,需要改用匯入外部資料方式 | ** 使用 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 == | == Import XLS to MySQL == | ||
| Line 99: | Line 192: | ||
** Paste to Gmail: the border line of table is exists after posted | ** 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 | ** 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 == | ||
利用[[Google spreadsheet]]的函式,分析及統計可複選的問卷題目結果。 | 利用[[Google spreadsheet]]的函式,分析及統計可複選的問卷題目結果。 | ||
== Excel 「樞紐分析表(Pivot Tables)」== | == Excel 「樞紐分析表(Pivot Tables)」== | ||
| Line 130: | Line 231: | ||
== Troubleshooting of Excel errors == | == Troubleshooting of Excel errors == | ||
[[Troubleshooting of common Excel errors in Mandarin]] | |||
== 不同試算表方案的工作表或儲存格的大小限制 == | == 不同試算表方案的工作表或儲存格的大小限制 == | ||
| Line 151: | Line 243: | ||
Merge Excel worksheets (copy data from multiple worksheets into one workbook) | 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.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/> | <references/> | ||
{{Template: | {{Template:Data factory flow}} | ||
[[Category:Software]] | [[Category: Software]] | ||
[[Category: | [[Category: Spreadsheet]] | ||
[[Category:Data Science]] | [[Category: Excel]] | ||
[[Category: MySQL]] | |||
[[Category: Data Science]] | |||