解決從資料庫輸出 Excel 檔案，遇到非常大數字的科學符號和有效位數的問題

日期：
如果想要在 Excel 檔案呈現長數字，會遇到兩個狀況: (1) 通用格式針對 12 位以上 (含 12 位) 的數字，會以科學記號表示 [1]，例如 123456789012 顯示為 1.23457E+11 。(2) 因為 Excel 本身精確度 (precision) 15 位有效位數的限制，  16 位以上  (含 16 位) 的數字，都會變成 0 [2]。例如 12345678901234567 顯示為 1.23457E+16 。如果點選儲存格，可以看到資料變成 12345678901234600 ，造成跟原始資料有出入的問題。


解決方式

解決方式 I: 使用資料庫工具，輸出數值資料時，使用字串連接函數 CONCAT() [3] 將原始資料最前面加上單引號。MySQL 的範例 SQL 查詢語法如下:
SELECT CONCAT("'", `column`) FROM `my_table`;

解決方式 II: 使用 PHPExcel 套件 [4][5][6]，輸出數值時，PHP 程式設定儲存格是文字格式。設定 A1 儲存格是文字格式，stackoverflow上提到 [7] 無法針對特定範圍設定格式，只能逐一儲存格設定。
$objPHPExcel->setActiveSheetIndex(0)
                         ->setCellValueExplicit('A1', '12345678901234567', PHPExcel_Cell_DataType::TYPE_STRING);
解決方式 III: 同樣使用 PHPExcel 套件，輸出數值時，將資料寫在公式內 [7]。
$objPHPExcel->setActiveSheetIndex(0)
                        ->setCellValue('A1', '="12345678901234567"');


失敗的嘗試 I: 另一種設定儲存格是文字格式的方式 [8] 。實際測試雖然有設定文字格式，但是並沒有解決科學符號和有效位數的問題。
$objPHPExcel->setActiveSheetIndex(0)
                       ->setCellValue('A2', '12345678901234567');

$objPHPExcel->getActiveSheet()
                       ->getStyle('A2')
                       ->getNumberFormat()
                       ->setFormatCode(
                           PHPExcel_Style_NumberFormat::FORMAT_TEXT
                       );
失敗的嘗試 II: 相當於 Excel 內自定儲存格格式的方式，但是只有解決科學符號的問題，並未解決有效位數的問題。如果資料庫資料的數字長度都是固定的，並且小於15位，例如手機號碼10位，則適合採用這種解法。如果資料庫資料的數字長度不是固定的，則還需要額外處理最前面以零補位的資料問題。

PHPExcel 範例程式碼:
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A3', '12345678901234567');
$objPHPExcel->getActiveSheet()->getStyle('A3:Z3')
->getNumberFormat()->setFormatCode('00000000000000000000'); /*20個零*/

參考資料

  1. 以科學記號 (指數) 表示法顯示數字
  2. 浮點算術可能會在 Excel 中造成不正確的計算結果
  3. MySQL :: MySQL 5.0 Reference Manual :: 12.5 String Functions
  4. PHPExcel
  5. Docs For Class PHPExcel_Cell_DataType 
  6. PHP Excel - Correct format for strings / numbers beginning with zero? - Stack Overflow 
  7. php - Phpexcel file: string data type is parsed as numbers after creating the file - Stack Overflow 
  8. PHPExcel - set cell type before writing a value in it - Stack Overflow 
  9. Adding more than 15 digits in Excel - Super User

相關資料



Photo: Thanks, Ryan McGuire!

留言

張貼留言