Skip to content

Level51/silverstripe-excel-export

Folders and files

NameName
Last commit message
Last commit date

Latest commit

 

History

23 Commits
 
 
 
 
 
 
 
 
 
 
 
 
 
 

Repository files navigation

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

About

SilverStripe GridField button for excel exports

Resources

License

Stars

Watchers

Forks

Packages

No packages published

Contributors 4

  •  
  •  
  •  
  •  

Languages