Generating Excel Reports with PhpSpreadsheet in PHP

The tutorial demonstrates how to generate Excel files programmatically using the PhpOffice\PhpSpreadsheet library in PHP. It begins by outlining the necessary classes and methods for manipulating worksheets, columns, rows, cells, styles, borders, alignment, fonts, and fills.

First, the script instantiates a Spreadsheet object and obtains the active sheet and default style objects:

<code>$spreadsheet = new Spreadsheet();
$sheet = $spreadsheet->getActiveSheet(0);
$defaultStyle = $spreadsheet->getDefaultStyle();</code>

It then shows how to set global alignment and font color:

<code>$defaultStyle->getAlignment()
    ->setHorizontal(Alignment::HORIZONTAL_CENTER)
    ->setVertical(Alignment::VERTICAL_CENTER);
$defaultStyle->getFont()->getColor()->setRGB('333333');</code>

Column widths are adjusted with getColumnDimension :

<code>$sheet->getColumnDimension('A')->setWidth(7);
$sheet->getColumnDimension('B')->setWidth(35);
// ... other columns ...
$sheet->getColumnDimension('F')->setWidth(0); // reserve column
$sheet->getColumnDimension('G')->setWidth(14);</code>

The example proceeds to create a title row, merge cells, set row height, and apply font styling:

<code>$line = 1;
$sheet->mergeCells('A'.$line.':G'.$line);
$sheet->getRowDimension($line)->setRowHeight(40);
$ATitle = $sheet->getCell('A'.$line);
$ATitle->getStyle('A'.$line)->getAlignment()->setHorizontal(Alignment::HORIZONTAL_CENTER);
$ATitle->getStyle('A'.$line)->getFont()->setSize(22)->setBold(true);
$ATitle->setValue('Smallnews - 门店订单');</code>

Subsequent rows demonstrate merging cells for store information, setting borders, and populating data such as store manager, address, order totals, and shipping time, with appropriate alignment and styling.

A sample data array of orders is defined, and a foreach loop iterates over each order to render order details, including dynamic row heights, background fill, and cell borders. Item titles are truncated if they exceed a certain length.

<code>$orders = [
    ['items' => [
        ['goods_title' => '这是个名字很长的商品，真的很长， 不信你看，肯定超过了表格宽度'],
        ['goods_title' => '这是个名字比较短的商品'],
    ]],
    // ... more orders ...
];
foreach ($orders as $order) {
    // set row height, style, fill, etc.
    // merge cells for user info, set values, etc.
    foreach ($order['items'] as $key => $item) {
        $line++;
        $sheet->setCellValue('A'.$line, $key+1);
        $goods_title = mb_strlen($item['goods_title']) > 16 ? mb_substr($item['goods_title'],0,14).'**' : $item['goods_title'];
        $sheet->setCellValue('B'.$line, $goods_title);
        $sheet->setCellValue('C'.$line, '22.22');
        $sheet->setCellValue('D'.$line, '11.11');
        $sheet->setCellValue('E'.$line, 3);
    }
    // add spacing rows, etc.
}
</code>

Finally, the script sends appropriate HTTP headers and writes the spreadsheet to the output as an XLSX file:

<code>ob_end_clean();
header('pragma:public');
header('Content-type:application/vnd.ms-excel;charset=utf-8;name="门店面单".xls');
header('Content-Disposition:attachment;filename=门店面单.xls');
$writer = \PhpOffice\PhpSpreadsheet\IOFactory::createWriter($spreadsheet, 'Xlsx');
$writer->save('php://output');</code>

This comprehensive example can be run directly to produce a formatted Excel report for store orders.