翻譯成 Translate to... English | 繁體中文 | 简体中文

解決從資料庫輸出 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!

留言

如果要直接聯絡我,請點選左側上方漢堡選單,填寫其中的聯絡我表單~

facebook page