ไฟล์รายชื่อลูกค้าที่ export ไปให้ฝ่ายขายมักกลับมาพร้อมสองคำถาม เลขบัตรประชาชนทำไมเป็น 1.23457E+12 และเบอร์โทรทำไม 0 ข้างหน้าหายไป ถ้าผู้ใช้กดขยายคอลัมน์แล้วเปลี่ยน format เป็นตัวเลข จะเห็นเลขบัตรครบ 13 หลัก ข้อมูลยังไม่เสีย แต่ถ้ารหัสยาวกว่า 15 หลัก เลขท้ายจะกลายเป็น 0 ไปแล้ว และแก้กลับไม่ได้
สองเรื่องนี้มาจากที่เดียวกัน คือเซลล์นั้นเก็บค่าเป็นตัวเลข ทั้งที่ข้อมูลเป็นรหัส
Excel เก็บตัวเลขได้แม่นแค่ 15 หลัก
Excel เก็บตัวเลขได้แม่นยำ 15 หลัก หลักที่ 16 เป็นต้นไปจะเป็น 0 เสมอ และตัวเลขที่ยาวตั้งแต่ 12 หลักขึ้นไปในเซลล์ format General จะแสดงเป็นแบบวิทยาศาสตร์ ส่วน 0 ข้างหน้าตัวเลขไม่มีความหมายทางคณิตศาสตร์ Excel จึงตัดทิ้ง
สามเรื่องนี้ไม่ใช่ปัญหาของยอดเงิน แต่เป็นปัญหาของรหัส ที่หน้าตาเป็นตัวเลขแต่ไม่มีใครเอาไปบวกลบ
| ข้อมูล | ตัวอย่าง | ถ้าเก็บเป็นตัวเลข |
|---|---|---|
| เลขบัตรประชาชน, เลขผู้เสียภาษี | 1234567890123 | แสดงเป็น 1.23457E+12 |
| เบอร์โทร | 0812345678 | เหลือ 812345678 |
| เบอร์โทรแบบสากล | +66812345678 | เหลือ 66812345678 เครื่องหมาย + หายไป |
| รหัสลูกค้า, รหัสสินค้า | 0010001419 | เหลือ 10001419 |
| เลขบัตรเครดิต, เลขอ้างอิงยาว ๆ | 4111111111111111 | หลักสุดท้ายเป็น 0 |
ทางแก้คือเขียนรหัสลงเซลล์เป็นข้อความ (cell type เป็น string) ตั้งแต่ตอนสร้างไฟล์ Excel จะไม่แตะค่านั้นเลย
PhpSpreadsheet แปลงข้อความเป็นตัวเลขให้เอง
ถ้าเขียนด้วย PhpSpreadsheet หรือ Laravel Excel ซึ่งใช้ PhpSpreadsheet อยู่ข้างใน การส่งค่าเป็น string ยังไม่พอ ตัวที่ตัดสินว่าเซลล์จะเป็นข้อความหรือตัวเลขคือ DefaultValueBinder ซึ่งดูจากหน้าตาของค่า
- ข้อความที่เป็นตัวเลขล้วนจะกลายเป็นตัวเลข เลขบัตร
'1234567890123'จึงเป็นตัวเลข 13 หลัก '+66812345678'ก็กลายเป็นตัวเลข และเสียเครื่องหมาย +- ข้อความที่ขึ้นต้นด้วย 0 เช่น
'0812345678'รอดไป ยังเป็นข้อความ - ข้อความที่หน้าตาเป็นเลขยกกำลัง เช่น
'12E5'ก็กลายเป็นตัวเลขเหมือนกัน - ตั้งแต่ PhpSpreadsheet 5.0 จำนวนเต็มที่ยาวเกิน 15 หลักจะเป็นข้อความ แต่เลข 12–15 หลักยังเป็นตัวเลขอยู่
เราลองสามค่าแรกแล้ว ได้ผลตามนี้จริง กฎ "ขึ้นต้นด้วย 0 ไม่โดน" ทำให้ปัญหาหลบตา คอลัมน์รหัสที่มีทั้ง 0061314106 กับ 3400062310 จะได้ข้อความครึ่งหนึ่ง ตัวเลขอีกครึ่ง ผู้ใช้ VLOOKUP ไม่เจอบางแถวโดยไม่รู้สาเหตุ
ทางที่เรามักลองก่อนคือตั้ง format ของคอลัมน์เป็น @ (ข้อความ) ใน AfterSheet หรือ columnFormats() วิธีนี้ไม่พอ เพราะ format เปลี่ยนแค่การแสดงผล ค่าที่ binder เขียนลงเซลล์เป็นตัวเลขไปแล้ว ต้องเขียนเป็นข้อความตั้งแต่ตอน bind
เขียนคอลัมน์รหัสเป็นข้อความด้วย value binder
Laravel Excel ให้ export เปลี่ยน binder เองได้ผ่าน WithCustomValueBinder เราบอกว่าคอลัมน์ไหนเป็นรหัส แล้วเรียก setValueExplicit() กับ DataType::TYPE_STRING ให้คอลัมน์นั้น คอลัมน์อื่นใช้กฎเดิม
use Maatwebsite\Excel\Concerns\FromQuery;
use Maatwebsite\Excel\Concerns\WithColumnFormatting;
use Maatwebsite\Excel\Concerns\WithCustomValueBinder;
use Maatwebsite\Excel\Concerns\WithMapping;
use PhpOffice\PhpSpreadsheet\Cell\Cell;
use PhpOffice\PhpSpreadsheet\Cell\DataType;
use PhpOffice\PhpSpreadsheet\Cell\DefaultValueBinder;
use PhpOffice\PhpSpreadsheet\Style\NumberFormat;
class CustomersExport extends DefaultValueBinder implements FromQuery, WithMapping, WithCustomValueBinder, WithColumnFormatting
{
private const array TEXT_COLUMNS = ['B', 'C']; // เลขบัตร, เบอร์โทร
public function bindValue(Cell $cell, mixed $value): bool
{
if ($value !== null && in_array($cell->getColumn(), self::TEXT_COLUMNS, true)) {
$cell->setValueExplicit((string) $value, DataType::TYPE_STRING);
return true;
}
return parent::bindValue($cell, $value);
}
public function columnFormats(): array
{
return ['B' => NumberFormat::FORMAT_TEXT, 'C' => NumberFormat::FORMAT_TEXT];
}
// query() และ map() ตามปกติ
}
columnFormats() ยังมีประโยชน์ แม้จะไม่ใช่ตัวที่กันปัญหา ถ้าผู้ใช้แก้เซลล์นั้นด้วยมือ Excel จะเก็บค่าที่พิมพ์ใหม่เป็นข้อความด้วย
ถ้าเขียนด้วย OpenSpout ไม่ต้องทำอะไรเพิ่ม OpenSpout ดูจากชนิดของค่าใน PHP ค่าที่เป็น string ได้ StringCell เสมอ ค่าที่เป็น int หรือ float ได้ตัวเลข (OpenSpout เขียนไฟล์ใหญ่แบบ streaming มีรายละเอียดเพิ่ม) ข้อเดียวที่ต้องระวังทั้งสอง package คืออย่า cast รหัสเป็น int ตั้งแต่ใน query หรือ model เพราะ 0 ข้างหน้าจะหายตั้งแต่ตอนนั้น
CSV เก็บชนิดของค่าไม่ได้ ไฟล์ CSV ที่มีเลขบัตรประชาชน พอผู้ใช้ดับเบิลคลิกเปิดใน Excel ก็จะกลายเป็นตัวเลขอยู่ดี Excel รุ่นใหม่มีตัวเลือก Automatic Data Conversions ให้ผู้ใช้ปิดการตัด 0 และการแปลงเลขยาวได้เอง แต่เป็นการตั้งค่าในเครื่องของแต่ละคน ถ้ารหัสต้องอยู่ครบ ให้ส่งเป็น .xlsx
ตัวเลขศูนย์กับ true/false ที่หายไปจากไฟล์
อีกอาการที่มาคู่กันคือเซลล์ที่ควรเป็น 0 ว่างเปล่า Laravel Excel เทียบค่ากับ null แบบ == เป็นค่าเริ่มต้น และใน PHP 0 == null เป็นจริง ยอด 0 บาทจึงกลายเป็นช่องว่าง ใส่ WithStrictNullComparison ให้ export ทุกตัวที่มีตัวเลข แล้ว 0 จะเป็น 0
ค่า true/false ก็โดนแบบเดียวกัน ถ้าเขียนเป็น 1 กับ 0 เลข 0 จะหายด้วยเหตุผลเดิม เขียนเป็น boolean จริงของ PHP แล้ว Excel จะแสดงเป็น TRUE และ FALSE หรือแปลงเป็นข้อความที่ผู้ใช้อ่านเข้าใจ เช่น "ใช่" และ "ไม่ใช่"
ยอดเงินเขียนเป็นตัวเลข แล้วให้ format จัดหน้าตา
รหัสต้องเป็นข้อความ ส่วนยอดเงินกลับกัน ต้องเป็นตัวเลข ฝ่ายบัญชีจะ SUM, ทำ pivot หรือคัดลอกไปใช้ต่อ ถ้าเราเรียก number_format() ใน PHP แล้วเขียนผลลงเซลล์ ค่าที่ได้คือข้อความ "1,250.00" ที่บวกกันไม่ได้
ให้เขียนตัวเลขดิบ (1250.5) แล้วตั้ง number format ของคอลัมน์ format แบ่งได้สี่ส่วนคั่นด้วย ; คือ บวก;ลบ;ศูนย์;ข้อความ
| Format | ค่า | แสดงเป็น |
|---|---|---|
#,##0 |
1250.5 | 1,251 |
#,##0.00 |
1250.5 | 1,250.50 |
#,##0.00;-#,##0.00 |
-1250.5 | -1,250.50 |
#,##0.00;(#,##0.00) |
-1250.5 | (1,250.50) |
#,##0.00;-#,##0.00;"-" |
0 | - |
_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_) |
-1250.5 | (1,250.50) จัดตำแหน่งแบบรูปแบบบัญชี |
#,##0.00 "บาท" |
1250.5 | 1,250.50 บาท |
0.00% |
0.07 | 7.00% |
0.00"%" |
7 | 7.00% |
#,##0.0000 |
35.24718 | 35.2472 |
@ |
0812345678 | 0812345678 (ข้อความ) |
ข้อที่มักพลาดในตารางนี้มีสองข้อ format 0.00% คูณค่าด้วย 100 ก่อนแสดง ถ้าเก็บอัตราภาษีเป็น 7 ต้องใช้ 0.00"%" ที่พิมพ์เครื่องหมาย % เป็นตัวอักษรแทน อีกข้อคือ format ปัดเศษแค่ตอนแสดง ค่าข้างในยังมีทศนิยมครบ ยอดรวมที่ Excel คำนวณจึงอาจต่างจากผลบวกของตัวเลขที่ตาเห็นหนึ่งสตางค์ ถ้ายอดในรายงานต้องตรงกับเอกสารที่ออกไปแล้ว ให้ปัดเศษใน PHP ก่อนเขียน
ตัวเลขติดลบจะใช้เครื่องหมายลบหรือวงเล็บ ให้ถามคนที่ใช้รายงาน ฝ่ายบัญชีมักคุ้นกับวงเล็บและขีดแทนศูนย์ ส่วนรายงานที่ต้องหน้าตาเหมือนรายงานเดิมจากระบบอื่นก็ต้องตามของเดิม
คอลัมน์ที่มักพลาดในรายงานจริง
- เลขที่เอกสารจาก SAP ในระบบรายงานจาก SAP ที่เราทำให้ลูกค้า เลขใบสั่งซื้อของลูกค้าบางรายเป็นตัวเลขล้วน
3400062310บางรายขึ้นต้นด้วย 0 ไฟล์จึงได้คอลัมน์ที่ปนกันระหว่างข้อความกับตัวเลข แก้ด้วย value binder ที่กำหนดคอลัมน์รหัสไว้ในที่เดียว ถ้าอยากเห็นว่าระบบนั้นส่งรายงานอะไรบ้าง ลองอ่านต่อที่ระบบรายงานจาก SAP Business Oneได้ครับ - เลขผู้เสียภาษีในรายงานภาษี ของนิติบุคคลขึ้นต้นด้วย 0 จึงรอดไป แต่ของบุคคลธรรมดาคือเลขบัตรประชาชนที่ขึ้นต้นด้วย 1–8 คอลัมน์เดียวกันจึงได้ทั้งข้อความและ
1.23457E+12ปนกัน ถ้าไม่ได้บังคับเป็นข้อความ - export จาก Blade view (
FromView) ที่ใส่number_format()ไว้ใน view ยอดเงินจะเป็นข้อความทั้งคอลัมน์ และถ้า view เรียกnumber_format()กับทุกค่าที่is_numeric()รหัสที่เป็นตัวเลขก็จะได้ comma และเสีย 0 ข้างหน้าไปด้วย - ยอด 0 หาย ทั้งที่ข้อมูลมีค่า ใส่
WithStrictNullComparison
ถ้ารายงานมีวันที่ด้วย หลักเดียวกันนี้ใช้กับวันที่ คือเขียนเป็นค่าจริงแล้วให้ format จัดหน้าตา ลองอ่านต่อที่วิธีใส่วันที่ พ.ศ. ใน Excelได้ครับ
