Will PHPSpreadsheet convert xls to xlsx and preserve formatting?

Question

I am on the fact-finding part of our project specs and have not found a definitive answer re whether PHPSpreadsheet can convert an .xls to .xlsx and preserve formatting, such as table borders.

From this question, I see that there are separate imports for read/write and for file format type. This example demonstrates use of the different modules for read/write file formats:

<?php 

require 'vendor\autoload.php';

use \PhpOffice\PhpSpreadsheet\Reader\Xls;
use \PhpOffice\PhpSpreadsheet\Writer\Xlsx;

$xls_file = "Example.xls";

$reader = new Xls();
$spreadsheet = $reader->load($xls_file);

$loadedSheetNames = $spreadsheet->getSheetNames();

$writer = new Xlsx($spreadsheet);

foreach($loadedSheetNames as $sheetIndex => $loadedSheetName) {
    $writer->setSheetIndex($sheetIndex);
    $writer->save($loadedSheetName.'.xlsx');
}

However, I have not seen if the resultant export preserves formatting, specifically border lines. At the moment, I am unable to write this myself.

score 0 · Answer 1 · answered Dec 17 '18 at 18:28

I haven't tested PhpSpreadsheet (due to Composer requirement), but FWIW PhpExcel (the predecessor to PhpSpreadsheet) does the job quite handily and yes, it does preserve most formatting.

Here is a sample conversion script using PhpExcel:

<?php

    $xls_to_convert = 'test.xls';

    error_reporting(E_ALL);
    ini_set('display_errors', TRUE);
    ini_set('display_startup_errors', TRUE);

    define('EOL',(PHP_SAPI == 'cli') ? PHP_EOL : '<br />');

    date_default_timezone_set('America/Vancouver');

    require_once dirname(__FILE__) . '/PHPExcel/Classes/PHPExcel/IOFactory.php';

    $objPHPExcel = PHPExcel_IOFactory::load(dirname(__FILE__) . '/' . $xls_to_convert);

    $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
    $objWriter->save(str_replace('.xls', '.xlsx', $xls_to_convert));

A cursory comparison of my test xls spreadsheet against the xlsx result shows:

font sizes, font colors, bold/italic, borders, centering, cell background/shading/colors, drop-downs with values, are preserved
Buttons are lost
Shapes (e.g. lines, arrows, textboxes) are lost
Charts are lost

Will PHPSpreadsheet convert xls to xlsx and preserve formatting?

1 Answers1