วิธีเขียน Excel ไฟล์ใหญ่ด้วย OpenSpout โดยไม่ให้ memory หมด

OpenSpout เป็นตัวต่อจาก box/spout ที่เลิกพัฒนาไปแล้ว ต่างจาก PhpSpreadsheet ตรงที่ไม่สร้างทั้งไฟล์ไว้ใน memory ก่อน แต่เขียนแต่ละแถวออกไปเลย FAQ ของโปรเจกต์บอกว่าใช้ memory ไม่ถึง 3 MB ไม่ว่าไฟล์จะใหญ่แค่ไหน (เทียบตัวเลขกับ package อื่นได้ในวิธีเลือก package export Excel) ตัวอย่างในโพสต์นี้ใช้ OpenSpout 5 ตั้งแต่ 5.4 ขึ้นไปต้องใช้ PHP 8.4 ถ้ายังอยู่บน PHP เก่ากว่านั้น 4.x ก็ใช้ได้ API คล้ายกัน ต่างกันที่ Style ของ 4.x ยังตั้งค่าผ่าน setter

ทุกอย่างต้องรู้ก่อนเขียนแถวแรก#

PhpSpreadsheet ปล่อยให้เราเขียนข้อมูลก่อน แล้วค่อยย้อนไปทำหัวตารางสี ใส่ยอดรวม หรือ merge ทีหลังได้ OpenSpout ทำแบบนั้นไม่ได้ เพราะแถวที่เขียนไปแล้วออกจาก memory ไปแล้ว ลำดับงานจึงเป็น

1. เตรียม style ทุกแบบที่จะใช้ และตั้ง merge กับความกว้างคอลัมน์ไว้
2. เปิด writer ไปที่ไฟล์หรือไปที่เบราว์เซอร์
3. วนเขียนทีละแถว โดยเลือก style ให้แต่ละ cell ตอนเขียน
4. ปิด writer

ข้อมูลที่ต้องคำนวณจากทั้งชุด เช่นยอดรวมท้ายตาราง ต้องคำนวณใน PHP ระหว่างวน แล้วเขียนเป็นแถวสุดท้าย

เขียนไฟล์แรก#

use OpenSpout\Common\Entity\Cell\DateTimeCell;
use OpenSpout\Common\Entity\Cell\NumericCell;
use OpenSpout\Common\Entity\Cell\StringCell;
use OpenSpout\Common\Entity\Row;
use OpenSpout\Common\Entity\Style\Style;
use OpenSpout\Writer\AutoFilter;
use OpenSpout\Writer\XLSX\Entity\SheetView;
use OpenSpout\Writer\XLSX\Options;
use OpenSpout\Writer\XLSX\Writer;

$header = new Style(fontBold: true, backgroundColor: 'D9EAD3');
$money = new Style(format: '#,##0.00');
$date = new Style(format: 'd mmm yyyy');

$options = new Options();
$options->mergeCells(0, 1, 3, 1);           // A1:D1 คอลัมน์เริ่มที่ 0 แถวเริ่มที่ 1

$writer = new Writer($options);
$writer->openToFile(storage_path('app/orders.xlsx'));

$sheet = $writer->getCurrentSheet();
$sheet->setName('ยอดขาย');
$sheet->setColumnWidth(14, 1);              // คอลัมน์ A (เริ่มที่ 1)
$sheet->setColumnWidth(28, 2);
$sheet->setColumnWidth(16, 3, 4);
$sheet->setSheetView(new SheetView(freezeRow: 3));

$writer->addRow(Row::fromValues(['รายงานยอดขาย ตุลาคม 2569']));
$writer->addRow(Row::fromValuesWithStyle(['เลขที่', 'ลูกค้า', 'วันที่', 'ยอดเงิน'], $header));

$count = 0;
foreach (Order::query()->with('customer')->lazy() as $order) {
    $writer->addRow(new Row([
        new StringCell($order->number),
        new StringCell($order->customer->name),
        new DateTimeCell($order->ordered_at, $date),
        new NumericCell((float) $order->total, $money),
    ]));
    $count++;
}

$sheet->setAutoFilter(new AutoFilter(0, 2, 3, 2 + $count));

$writer->addNewSheetAndMakeItCurrent()->setName('สรุป');
$writer->addRow(Row::fromValues(['จำนวนออร์เดอร์', $count]));

$writer->close();

ไฟล์ที่ได้มีหัวรายงานกินช่อง A1:D1 หัวตารางมีสีพื้น แถวหัวค้างไว้ตอนเลื่อน วันที่กับยอดเงินเป็นชนิดข้อมูลจริงที่ Excel เรียงและรวมได้ และมีชีทสรุปแยกอีกชีท

จุดที่สับสนบ่อยคือการนับคอลัมน์ mergeCells() กับ AutoFilter นับคอลัมน์ A เป็น 0 ส่วน setColumnWidth() นับ A เป็น 1 แถวนับจาก 1 เสมอ

ยอดเงินที่ cast เป็น decimal ใน Eloquent จะได้ค่าเป็น string จึงต้องแปลงเป็น float ก่อน เพราะ NumericCell รับแค่ int กับ float

lazy() ดึงข้อมูลเป็นก้อนละ 1,000 แถว แล้วส่งให้ทีละตัว memory ฝั่ง query จึงคงที่เหมือนฝั่ง writer ถ้าใช้ ->get() ทั้งตาราง memory ก็ยังพุ่งอยู่ดี ต่อให้ writer เขียนทีละแถวก็ตาม

เลือกชนิด cell เอง อย่าปล่อยให้เดา#

Row::fromValues() สะดวก แต่ข้างในเรียก Cell::fromValue() ซึ่งเดาชนิดจากค่า int หรือ float เป็นตัวเลข DateTimeInterface เป็นวันที่ และ ข้อความที่ขึ้นต้นด้วย = เป็นสูตร ถ้าลูกค้าตั้งชื่อร้านว่า =HYPERLINK(...) ไฟล์ที่ export ออกไปจะมีสูตรนั้นจริง ๆ (เรื่อง formula injection)

ข้อมูลที่มาจากผู้ใช้จึงควรสร้างเป็น StringCell เอง ส่วนตัวเลขที่ต้องการให้ Excel รวมได้ใช้ NumericCell ซึ่งรับเฉพาะ int กับ float ข้อดีอีกข้อคือเลขบัตรประชาชน เบอร์โทร หรือเลขเอกสารที่ส่งเป็น string จะเป็นข้อความเสมอ เลข 0 ข้างหน้าไม่หาย และไม่กลายเป็น 1.23E+12 แบบที่เจอใน PhpSpreadsheet (อ่านต่อเรื่องนี้)

ส่งไฟล์ตรงไปที่เบราว์เซอร์#

ไม่ต้องเขียนลงดิสก์ก่อน ให้ writer เปิดที่ php://output ใน streamDownload() ของ Laravel ข้อมูลจะไหลไปถึงผู้ใช้ระหว่างที่ยังเขียนอยู่

return response()->streamDownload(function (): void {
    $writer = new Writer();
    $writer->openToFile('php://output');
    $writer->addRow(Row::fromValues(['เลขที่', 'ลูกค้า', 'ยอดเงิน']));

    foreach (Order::query()->with('customer')->lazy() as $order) {
        $writer->addRow(new Row([
            new StringCell($order->number),
            new StringCell($order->customer->name),
            new NumericCell((float) $order->total),
        ]));
    }

    $writer->close();
}, 'orders.xlsx', [
    'Content-Type' => 'application/vnd.openxmlformats-officedocument.spreadsheetml.sheet',
]);

ระวัง timeout ของ nginx หรือ proxy ที่อยู่หน้าแอป ถ้าไฟล์ใช้เวลานานเกินค่าที่ตั้งไว้ ผู้ใช้จะได้ไฟล์ครึ่งเดียว ไฟล์ที่ใช้เวลาเป็นนาทีควรย้ายไปทำใน queue แล้วส่งลิงก์ให้ผู้ใช้แทน (วิธีส่งไฟล์ให้ผู้ใช้แบบต่าง ๆ)

spatie/simple-excel สำหรับไฟล์ที่ไม่ต้องจัดหน้าตามาก#

spatie/simple-excel ใช้ OpenSpout อยู่ข้างใน รองรับทั้ง OpenSpout 4 และ 5 เขียนสั้นกว่ามากสำหรับไฟล์ที่เป็นตารางเรียบ ๆ

use Spatie\SimpleExcel\SimpleExcelWriter;

$writer = SimpleExcelWriter::streamDownload('customers.xlsx');

foreach (Customer::query()->lazy() as $i => $customer) {
    $writer->addRow([
        'รหัส' => $customer->code,
        'ชื่อ' => $customer->name,
    ]);

    if ($i % 1000 === 0) {
        flush();
    }
}

$writer->toBrowser();

key ของแถวแรกกลายเป็นหัวตารางให้เอง และฝั่งอ่านคืนเป็น LazyCollection ใช้ import ไฟล์ใหญ่ได้ด้วย ถ้าต้องการ style ซับซ้อนกว่านี้ simple-excel ให้เข้าถึง writer ของ OpenSpout ได้ แต่ถึงตอนนั้นเขียน OpenSpout ตรง ๆ มักอ่านง่ายกว่า

สิ่งที่ต้องยอมแลก#

อยากได้	OpenSpout	ทางที่ใช้แทน
ปรับความกว้างคอลัมน์ตามข้อมูล	ไม่มี	กำหนดความกว้างเองตามหัวคอลัมน์
conditional formatting	ไม่มี	เตรียม style ไว้หลายแบบ แล้วเลือกตอนเขียนแต่ละแถว
กราฟ	ไม่มี	เพิ่ม XML ของกราฟเข้าไปในไฟล์ zip หลังเขียนเสร็จ
สูตร	มี (FormulaCell)
รูปภาพ	มี (ImageCell)
แถวเกิน 1,048,576	ขึ้นชีทใหม่ให้เอง

conditional formatting ที่ทำแทนได้คือการเตรียม style ไว้ก่อน เช่น $normal, $late (ตัวแดง) และ $warning (พื้นเหลือง) แล้วตอนวนแต่ละแถวก็เช็กเงื่อนไขใน PHP ว่าแถวนี้ควรใช้ style ไหน ผลบนจอเหมือนกัน ต่างกันแค่ว่าถ้าผู้ใช้แก้ค่าในไฟล์ สีจะไม่เปลี่ยนตาม

รายละเอียดของ OpenSpout ที่ทำให้ไฟล์ไม่ตรงใจ#

• ความกว้างคอลัมน์ไปทั้งไฟล์ setColumnWidth() บน Options ใช้กับทุกชีท ถ้าแต่ละชีทมีคอลัมน์ไม่เหมือนกัน ให้ตั้งบน sheet แต่ละตัวแบบในตัวอย่าง
• แถวว่างหายไป OpenSpout ไม่เขียนแถวที่ไม่มีค่าเลย แถวคั่นที่ตั้งใจเว้นไว้ต้องมีอย่างน้อย ' ' หนึ่งช่อง
• ช่องว่างหน้าข้อความหาย การเขียนแบบ inline string ตัดช่องว่างข้างหน้าออก ถ้าต้องย่อหน้าชื่อรายการย่อย ให้ใช้ non-breaking space
• เปิดใน Numbers หรือบน iPhone แล้วเห็นช่องว่าง OpenSpout เขียนข้อความเป็น inline string โดยค่าเริ่มต้น ซึ่ง Apple Numbers แสดงไม่ได้ ถ้าผู้ใช้เปิดไฟล์บนเครื่อง Apple ให้ตั้ง new Options(SHOULD_USE_INLINE_STRINGS: false)
• style ต้องเขียนสองที่ ระบบที่ย้ายบางรายงานจาก Laravel Excel มาเขียนด้วย OpenSpout จะมี style ของรายงานเดียวกันอยู่สองที่ อย่างในระบบรายงานจาก SAP ที่เราทำให้ลูกค้า เราเก็บค่าที่ใช้ร่วมกัน เช่นสีและรูปแบบตัวเลข ไว้เป็นค่าคงที่ของชีท แล้วให้ writer ของ OpenSpout อ่านจากที่เดียวกัน หน้าตาสองทางจึงไม่เพี้ยนจากกัน