PHP_XLSXWriter Mixed Format Issues

Question

I'm working with PHP_XLSXWriter for one of my projects and running into a problem I don't seem to be able to resolve; mixed formats.

I need to produce an Excel file from my database tables, together with a header row that's all text. The data after the header row is of the format 'integer','number','text'. I already have a perfectly working script that pulls information out of database, then writes it into an XLSX file before sending the file to the user's browser. At the top of this file is the following:

$header = array("string","string","string");
$writer->writeSheetHeader($sheet_name, $header, $col_options = ['widths'=>[20,20,50], 'suppress_row'=>true] );

This all works flawlessly and my file opens in Excel. However, all my data is in the Excel 'text' format; the header row and the data rows. That's obviously because I'm declaring the three columns to be formatted as 'string', but if I change that line to:

$header = array("integer","#0.00","string");
$writer->writeSheetHeader($sheet_name, $header, $col_options = ['widths'=>[20,20,50], 'suppress_row'=>true] );

Then the final Excel alerts the user to a problem in the file as it's being opened and offers to repair the file. That works, and the header is all text and the data all the correct format, but it's messy and unprofessional from the user's perspective.

It's clear that this problem arises because the header row does not contain integers or numbers and I'm trying to force the formatting of the entire columns, but how to get around this? I've spent a long time search the help files and online forums but can't find anything that clearly tells me how to mix my formats in PHP_XLSXWriter.

Has anyone else managed to solve this problem and, if so, how?

Answer 1

This has now been solved, so I thought I'd update the thread to help anyone else who encounters this problem.

To summarise, the issue is that in many vanilla versions of PHP_XLSXWriter (including the one I was using) you can only set the master headers for the column datatype formats (decimal, integer, string, etc.). This works, but you cannot then set additional datatype format commands later.

After further searching I found a new function on this page: https://github.com/mk-j/PHP_XLSXWriter/pull/126/files. In case that link is down or otherwise can't be visited, here's the new function from it:

public function updateFormat($sheet_name, array $header_types) {
    if (empty($sheet_name) || empty($header_types))
        return;
    if(empty($this->sheets[$sheet_name])) {
        $this->writeSheetHeader($sheet_name, $header_types, array('suppress_row'=>true));
        return;
    }
    $sheet = &$this->sheets[$sheet_name];
    $sheet->columns = $this->initializeColumnTypes($header_types);
    $this->current_sheet = $sheet_name;
}

This function gets added to the downloaded xlsxwriter.php file, down around line 210 (between the 'initializeColumnTypes' function and 'writeSheetHeader' function).

Once added, I could then call the following PHP code in my page:

$writer->updateFormat($sheet_name, array('integer','#.00','string'));

This mean the initial datatype formats were set by the master header as usual, but I could selectively format the datatypes on any row I desired with the new function.

Hopefully this will help any PHP developers who find themselves in the same situation I did.