Spreadsheet

Problem

PHPExcel is no longer maintained, and is not PHP8 compatible (https://github.com/PHPOffice/PHPExcel).

Proposal

Propose we move to using PHPSpreadsheet (https://github.com/PHPOffice/PhpSpreadsheet). PHPSpreadsheet is the successor to PHPExcel, so the transition is relatively easy, with just a few function name changes.

Advantages

As well as maintaining PHP8 compatibility, PHPSpreadsheet has the facility to save spreadsheets in other formats, not just Excel, allowing us to save the spreadsheet in Excel format (.xlsx), or Open document format (.ods). As we are an open source project I feel more comfortable offering an open format as well as a proprietary one.

Implementation

We currently only use PHPExcel in two scripts, PcAnalysis.php and PcTabExpensesList.php.

Add the lines

require_once 'vendor/autoload.php'; use PhpOffice\PhpSpreadsheet\Helper\Sample; use PhpOffice\PhpSpreadsheet\IOFactory; use PhpOffice\PhpSpreadsheet\Spreadsheet; use PhpOffice\PhpSpreadsheet\Cell\Coordinate;

The declaration of the object changes from

$objPHPExcel = new PHPExcel();

to

$objPHPExcel = new Spreadsheet();

Setting the horizontal alignment style changes from

$objPHPExcel->getActiveSheet()->getStyle('A:B')->getAlignment()->setHorizontal(PHPExcel_Style_Alignment::HORIZONTAL_LEFT);

to

$objPHPExcel->getActiveSheet()->getStyle('A:B')->getAlignment()->setHorizontal(\PhpOffice\PhpSpreadsheet\Style\Alignment::HORIZONTAL_LEFT);

and creating the writer changes from

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007'); $objWriter->save('php://output');

to

if ($_POST['Format'] == 'xlsx') { $objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Xlsx($objPHPExcel); $objWriter->save('php://output'); } else if ($_POST['Format'] == 'ods') { $objWriter = new \PhpOffice\PhpSpreadsheet\Writer\Ods($objPHPExcel); $objWriter->save('php://output'); }