Export a simple Excel table with PHP (with formatting!)

I was looking for a way to export a table of data with some formatting (different font weights, different borders, some combined cells) from a PHP server back-end. There are some solutions which are either commercial or require some more package installed in your PHP environment which I didn’t want to do for just one table.

There is always the way to export a CSV file and with some tricks you can even force MS Excel to import it as UTF instead of ancient limited codepages (the trick seems to be to create your file in UTF16 with a valid UTF16 BOM instead of UTF8). Also you can force Excel to tread numbers as numbers instead of strings. But that’s it. No further formatting can be done.

Here the new XLSX file format comes into the game. You can create a XLSX file on your server with PHP and serve it to your users. For this I started with the data which I already saved locally as CSV files. I also created a XLSX file on my computer and (because it is just a zipped directory of different files) extract its content with 7-Zip. Now you find different files here. Some are not important to the content I want to deliver and so I just copied them into my PHP and they will be written without any change.

The files which are important for what I want to achieve are

  • /xl/styles.xml (here you define the styles used for the cells)
  • /xl/sharedStrings.xml (here you can just put all strings your cells contains)
  • /xl/worksheets/sheet1.xml (that’s the resulting table)

The styles define fonts, fills, borders and put them together in cellXfs. You can then point to one of this style in your table cells:
<c r="A1" s="1" t="s">
s= is the property which points to the index of the style
t= is the property which defines the type of call (with s for string and n for number)

A quite good placed to see what you can use is right here in the docs from MS.

At the end you get a nice looking Excel sheet with borders and different fonts and combined cells. All I wanted to have…

' . "\n" .
'' . "\n" .
'');
fclose($file);

$file = fopen($temp . '/_rels/.rels', 'w');
fwrite($file,
'' . "\n" .
'' . "\n" .
'');
fclose($file);

$file = fopen($temp . '/docProps/app.xml', 'w');
fwrite($file,
'' . "\n" .
'9LibreOffice/6.3.6.2$Windows_X86_64 LibreOffice_project/2196df99b074d8a661f4036fca8fa0cbfa33a497');
fclose($file);

$file = fopen($temp . '/docProps/core.xml', 'w');
fwrite($file,
'' . "\n" .
'de-DE2020-06-05T17:09:11Z1');
fclose($file);

$file = fopen($temp . '/xl/_rels/workbook.xml.rels', 'w');
fwrite($file,
'' . "\n" .
'' . "\n" .
'');
fclose($file);

$file = fopen($temp . '/xl/workbook.xml', 'w');
fwrite($file,
'' . "\n" .
'');
fclose($file);

$file = fopen($temp . '/xl/styles.xml', 'w');
fwrite($file,
'' . "\n" .
'');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fwrite($file, '');
fclose($file);

$idx = 0;
$strings = array();
$lines = explode("\n", file_get_contents('ResultTable.csv'));
for ($i=0; $i 0) {
			$strings[$field] = $idx;
			$idx++;
		}
	}
}

$file = fopen($temp . '/xl/sharedStrings.xml', 'w');
fwrite($file,
'' . "\n" .
'' . "\n");
foreach ($strings as $key => $value) {
	fwrite($file, '' . $key . '' . "\n");
}
fwrite($file, '');
fclose($file);

if (count($lines) > 0) {
	$line = $lines[0];
	$fields = explode("\t", $line);
	$file = fopen($temp . '/xl/worksheets/sheet1.xml', 'w');
	fwrite($file,
	'' . "\n" .
	'' . "\n");

	$line = $lines[0];
	$fields = explode("\t", $line);
	for ($i=0; $i' . "\n");

	for ($i=0; $i 1) {
			$row = ($i+1);
			fwrite($file, '' . "\n");
		}
	}

	fwrite($file, '&C&"Times New Roman,Regular"&12&A&C&"Times New Roman,Regular"&12Page &P' . "\n");
}
fwrite($file, '');
fclose($file);


$attachment_location = "resulttable.xlsx";
if (file_exists($attachment_location)) {
	unlink($attachment_location);
}
exec("cd " . $temp . " && zip -r ../" . $attachment_location . " .");
exec("rm -rf " . $temp);

if (file_exists($attachment_location)) {
	header($_SERVER["SERVER_PROTOCOL"] . " 200 OK");
	header("Cache-Control: public"); // needed for internet explorer
	header("Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet");
	header("Content-Transfer-Encoding: Binary");
	header("Content-Length:" . filesize($attachment_location));
	header("Content-Disposition: attachment; filename=" . $attachment_location);
	readfile($attachment_location);
	die();        
} else {
	die("Error: File not found.");
} 

?>

Juni 6, 2020 · admin · No Comments
Posted in: Allgemein

Leave a Reply

You must be logged in to post a comment.