level51 / silverstripe-excel-export
GridField button for excel exports
Installs: 1 762
Dependents: 0
Suggesters: 0
Security: 0
Stars: 0
Watchers: 2
Forks: 6
Open Issues: 1
Type:silverstripe-vendormodule
Requires
- php: ^8.1
- phpoffice/phpspreadsheet: ^2.0
- silverstripe/framework: ^5.0
This package is auto-updated.
Last update: 2024-10-04 11:05:18 UTC
README
Similar to the default GridFieldExportButton but exports Excel (xlsx) files instead of CSV.
Compat
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 dk@lvl51.de
- Julian Scheuchenzuber js@lvl51.de