วิธีใส่วันที่ พ.ศ. ในไฟล์ Excel ที่ export จาก Laravel

รายงานที่ส่งให้คนไทยควรมีวันที่เป็น พ.ศ. ทางที่เร็วที่สุดคือเรียก thaidate('j M y') แล้วเขียนผลลงเซลล์ ไฟล์ที่ได้ดูถูกทุกอย่าง จนผู้ใช้กด sort คอลัมน์วันที่แล้วได้ "1 ก.พ." มาก่อน "2 ม.ค." หรือกด filter แล้วไม่มีให้เลือกเป็นเดือน เพราะ Excel เห็นค่าในเซลล์เป็นข้อความ ไม่ใช่วันที่

ใน Excel วันที่กับหน้าตาของวันที่เป็นคนละเรื่องกัน เราเลือกได้ทั้งคู่โดยไม่ต้องเสียอย่างใดอย่างหนึ่ง

Excel เก็บวันที่เป็นตัวเลข#

ค่าในเซลล์วันที่คือจำนวนวันนับจากต้นปี 1900 วันที่ 30 ตุลาคม 2026 เก็บเป็น 46325 ส่วนเวลาเป็นเศษส่วนของวัน เที่ยงวันคือ .5 ตัวเลขชุดนี้คือ serial ที่ Excel ใช้ sort, filter และคำนวณ เช่น ลบวันที่สองช่องเพื่อหาจำนวนวันค้างชำระ

สิ่งที่ผู้ใช้เห็นมาจาก number format ของเซลล์ ซึ่งเป็นแค่คำสั่งว่าจะแสดงตัวเลขนั้นยังไง format เดียวกันจึงแสดงปีเป็น ค.ศ. หรือ พ.ศ. ก็ได้ โดยค่าข้างในไม่เปลี่ยน

ค่าในเซลล์ยังเป็นวันที่ของ ค.ศ. เหมือนในฐานข้อมูล number format เปลี่ยนแค่สิ่งที่ผู้ใช้เห็น

หลักเดียวกับที่ใช้กับฐานข้อมูลและ thaidate() คือเก็บเป็น ค.ศ. แล้วแปลงเป็น พ.ศ. ตอนแสดงผล ใน Excel ตัวที่แปลงตอนแสดงผลคือ number format

เขียนวันที่ลงเซลล์ให้เป็นวันที่จริง#

ส่ง Carbon ให้ Laravel Excel ตรง ๆ ไม่ได้ผลอย่างที่คิด DefaultValueBinder ของ PhpSpreadsheet แปลง object วันที่เป็นข้อความ 'Y-m-d H:i:s' แล้วเขียนเป็นข้อความ ต้องแปลงเป็น serial เองด้วย Date::PHPToExcel() แล้วบอก format ของคอลัมน์ใน columnFormats()

use PhpOffice\PhpSpreadsheet\Shared\Date;

class OrdersExport implements FromQuery, WithMapping, WithColumnFormatting
{
    public function map($order): array
    {
        return [
            $order->number,
            Date::PHPToExcel($order->ordered_at->setTimezone('Asia/Bangkok')),
            $order->total,
        ];
    }

    public function columnFormats(): array
    {
        return ['B' => '[$-107041E]d mmm yyyy'];
    }
}

ถ้าเขียนด้วย OpenSpout ใช้ DateTimeCell กับ style ที่มี format (OpenSpout เขียนไฟล์ใหญ่แบบ streaming มีรายละเอียดของ writer)

use OpenSpout\Common\Entity\Cell;
use OpenSpout\Common\Entity\Cell\DateTimeCell;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Style\Style;

$thaiDate = new Style(format: '[$-107041E]d mmm yyyy');

$writer->addRow(new Row([
    Cell::fromValue($order->number),
    new DateTimeCell($order->ordered_at->setTimezone('Asia/Bangkok'), $thaiDate),
]));

setTimezone('Asia/Bangkok') ในสองตัวอย่างสำคัญกว่าที่เห็น เราลองแปลงเวลาเดียวกันที่เป็น UTC กับที่เป็นเวลาไทยแล้วได้ serial คนละค่า เพราะ PHPToExcel() อ่านวันที่และเวลาตามที่ object แสดง ไม่ได้แปลง timezone ให้ แอปที่เก็บเวลาเป็น UTC แล้วไม่แปลงก่อน คำสั่งซื้อตอนสองทุ่มจะไปโผล่ในไฟล์เป็นบ่ายโมง และคำสั่งซื้อหลังเที่ยงคืนจะกลายเป็นของเมื่อวาน ถ้าแอปตั้ง app.timezone เป็น Asia/Bangkok อยู่แล้ว Carbon ที่ได้จาก model ก็เป็นเวลาไทยแล้ว

Format code ที่ทำให้ Excel แสดง พ.ศ.#

Excel อ่านส่วน [$-…] หน้า format เป็นรหัสภาษาและปฏิทิน รูปแบบของรหัสคือ [$-nnccLLLL] แต่ละส่วนมีหน้าที่ดังนี้

• LLLL คือรหัสภาษา (LCID) ภาษาไทยคือ 041E Excel จึงใช้ชื่อเดือนภาษาไทย
• cc คือปฏิทิน 07 คือปฏิทินพุทธ ปีจึงออกมาเป็น พ.ศ.
• nn คือรูปตัวเลข D คือเลขไทย

Format	ที่คาดว่า Excel จะแสดง
d mmm yyyy	30 Oct 2026
[$-107041E]d mmm yyyy	30 ต.ค. 2569
[$-107041E]d mmmm yyyy	30 ตุลาคม 2569
[$-D07041E]d mmm yyyy	๓๐ ต.ค. ๒๕๖๙
d/m/bbbb	30/10/2569 (bbbb คือปี พ.ศ. สี่หลักใน Excel ภาษาไทย)

bbbb มาจากเอกสาร Excel ภาษาไทยของ Microsoft ที่ให้ d/m/bbbb แสดงเป็น 15/5/2541 แต่โปรแกรมอื่นอาจไม่รู้จัก ส่วนรหัส [$-107041E] LibreOffice อ่านได้ ทั้งเรื่องปฏิทินพุทธและเลขไทย เราจึงใช้ [$-107041E] เป็นหลัก

ต้องระวังสองที่ที่ไม่เข้าใจ format นี้

• Google Sheets เอกสาร number format ไม่มี token สำหรับปี พ.ศ. และบอกว่าไม่สนรหัสภาษาในวงเล็บ ไฟล์เดียวกันที่เปิดใน Google Sheets จึงน่าจะแสดงปีเป็น ค.ศ.
• ตัว PhpSpreadsheet เอง PhpSpreadsheet กับ OpenSpout เขียน format ลงไฟล์ตามที่เราส่งไปทุกตัวอักษร Excel จึงแสดงได้ถูก แต่ตัว format ของ PhpSpreadsheet เอง (getFormattedValue() และ writer CSV, HTML, PDF) ตัดส่วน [$-…] ทิ้งและไม่รู้จัก b เราลองแล้วได้ "30 October bbbb" ถ้าระบบเปิดไฟล์ .xlsx แล้วแปลงเป็น PDF หรือ CSV ต่อด้วย PhpSpreadsheet ให้ใส่ปี พ.ศ. เองในขั้นนั้น

วันที่แบบข้อความยังมีที่ใช้#

ไม่ใช่ทุกวันที่ในรายงานต้องเป็น serial

ตรงไหนในรายงาน	เขียนเป็น
คอลัมน์ที่คนจะ sort, filter หรือคำนวณต่อ	serial + format พ.ศ.
หัวรายงาน เช่น "ข้อมูล ณ วันที่ 30 ตุลาคม 2569"	ข้อความจาก thaidate()
ช่วงเวลาของรายงาน เช่น "ไตรมาส 3/2569"	ข้อความ
รายงานที่ออกเป็น PDF หรือพิมพ์อย่างเดียว	ข้อความจาก thaidate()
ไฟล์ที่ต้องเปิดใน Google Sheets	serial + format ค.ศ. หรือข้อความ พ.ศ. ตามที่ผู้ใช้ต้องการ

ตอน import วันที่ พ.ศ. กลับเข้าระบบ#

ฝั่ง export จบที่ serial กับ format แต่ระบบที่ให้ผู้ใช้ส่งไฟล์ Excel กลับมา import จะเจอวันที่ พ.ศ. อีกหลายแบบ

• ผู้ใช้พิมพ์ 10/07/2569 ลงเซลล์วันที่ Excel รู้ว่าเป็นวันที่ แต่เก็บเป็นปี 2569 ของ ค.ศ. ค่าที่อ่านได้จึงเป็น serial ของอีก 543 ปีข้างหน้า ทางที่เราใช้คือถ้าแปลง serial แล้วได้ปีตั้งแต่ 2400 ขึ้นไป ให้ลบ 543 ปี เพราะไม่มีวันที่จริงในระบบงานไหนที่ไกลขนาดนั้น
• ผู้ใช้พิมพ์เป็นข้อความ เช่น 10/07/2569 ในเซลล์ที่ตั้งเป็นข้อความ ให้อ่านด้วย pattern d/m/Y แล้วลบ 543 ถ้าปีตั้งแต่ 2400 ขึ้นไป แล้วตรวจด้วย checkdate()
• วันที่ 29/2/2567 ปี 2567 ของ ค.ศ. ไม่ใช่ปีอธิกสุรทิน Excel จึงไม่ยอมรับว่าเป็นวันที่ ค่านี้จะมาเป็นข้อความเสมอ ต้องแปลงจากข้อความ (ได้ 29 ก.พ. 2024)
• อ่านไฟล์แบบข้อมูลอย่างเดียว (setReadDataOnly(true)) แล้วแยกไม่ออกว่าเซลล์ไหนเป็นวันที่ เพราะ PhpSpreadsheet ดูว่าเป็นวันที่จาก number format ของเซลล์ ไฟล์ที่มีคอลัมน์วันที่ต้องอ่าน style ด้วย
• Carbon::parse('2025') ไม่ใช่ปี 2025 Carbon อ่านเลขสี่หลักโดด ๆ เป็นเวลา 20:25 ของวันนี้ หัวรายงานรายปีที่สร้างจากเลขปีจึงออกมาเป็นปีปัจจุบัน ให้สร้างวันที่จากปีด้วย Carbon::create($year) แทน

อย่างตอนที่เราย้ายข้อมูลจากโปรแกรมเดิมเข้าระบบสินเชื่อ ไฟล์ของลูกค้ามีทั้งวันที่จริง serial ที่เป็นปี พ.ศ. และข้อความ พ.ศ. ตัวอ่านไฟล์จึงต้องรับได้ทั้งสามแบบ และต้องอ่าน style ของเซลล์ด้วย ถ้าอยากเห็นว่าระบบนั้นทำอะไรบ้าง ลองอ่านต่อที่ระบบสินเชื่อโฉนดที่ดินได้ครับ