SilverStripe GridField button for excel exports

Similar to the default GridFieldExportButton but exports Excel (xlsx) files instead of CSV.

Compat

2.x	1.x	0.1.x
Silverstripe 5, php >= 8.1	Silverstripe 4, php >= 7.1	Silverstripe 3, php >= 7

Howto

Styling the exported xlsx file

You can hook in and style the ready-to-export Excel file with a callback function.

$excelExportButton = new GridFieldExcelExportButton('buttons-before-left', $exportFieldMapping);
        $excelExportButton->setAfterExportCallback([ExcelStylingHelper::class, 'styleExcelExport']);

The callback passes a subclass of PhpOffice\PhpSpreadsheet\Writer\BaseWriter (e.g. Xlsx writer object), there you can get the spreadsheet and the worksheet and manipulate it. See PHPSpreadsheet docs fo more details.

Setting the default font

class ExcelStylingHelper
{
    public static function styleExcelExport(BaseWriter $writer): void
    {
        $sheet = $writer->getSpreadsheet();
        $sheet->getDefaultStyle()->getFont()->setName('Comic Sans MS');
        $sheet->getDefaultStyle()->getFont()->setSize(12);
    }
}

Draw borders around the exported data

and a thicker line between header and data

        /** @var Worksheet $worksheet */
        $worksheet = $sheet->getActiveSheet();
        $borders = [
            'borders' => [
                'allBorders' => [
                    'borderStyle' => Border::BORDER_MEDIUM,
                    'color' => ['argb' => 'FF000000']
                ]
            ]
        ];
        $bottomRight = $worksheet->getHighestColumn() . $worksheet->getHighestRow();
        $worksheet->getStyle('A1:' . $bottomRight)->applyFromArray($borders);
        $worksheet->getStyle('A1:' . $worksheet->getHighestColumn() . '1')->applyFromArray(
            [
                'borders' => [
                    'bottom' => [
                        'borderStyle' => Border::BORDER_THICK,
                        'color' => ['argb' => 'FF000000']
                    ]
                ]
            ]
        );

Style a specific column

Sometimes, e.g. telephone numbers are displayed as scientific number. In this case you need to format the content as text.

In this example we check the header row for a specific column named "Tel" and format each cell in this column explicitely as text.

        $headerRow = $worksheet->getRowIterator(1, 1)->current();

        foreach ($headerRow->getCellIterator() as $headerCell) {
            //check if header cell is "Tel"
            if ($headerCell->getValue() === 'Tel') {
                //format column as text
                $column = $headerCell->getColumn();
                $worksheet->getStyle($column . ':' . $column)
                    ->getNumberFormat()
                    ->setFormatCode(NumberFormat::FORMAT_TEXT);
                $column = $worksheet->getColumnIterator($column, $column)->current();
                foreach ($column->getCellIterator() as $valueCell) {
                    $valueCell->setValueExplicit(
                        $valueCell->getValue(),
                        DataType::TYPE_STRING
                    );
                }
            }

        }

Maintainer

• Daniel Kliemsch [email protected]
• Julian Scheuchenzuber [email protected]