Create XLSX Spreadsheets in PHP

Background:
In the early 2000s the first PHP library that emerged for writing to Excel 5.0 was the pear library Spreadsheet_Excel_Writer derived from a Perl's Spreadsheet::WriteExcel. As a pear library, it had extra dependencies, was not lightweight, and only supported the BIFF 5 (Excel 5.0) format. As subsequent versions of Excel continue to be released, support for Excel 5.0 in modern versions continues to decrease.

For Office 2007, Microsoft wrote the OpenXML .xlsx format to compete with OpenOffice's .ods format. One of the advantages of these are if you rename the spreadsheet to a .zip file, you can unzip the format and see how the document was built. Both formats are much more, "open", because if you can write a program to zip xml files, you may be able to produce a valid .xlsx or .ods file.

In the last few years the PHPExcel project has risen to become the de-facto library for reading and writing excel formats. In fact, it includes the Spreadsheet_Excel_Writer internally. I tried it out for one of my projects, and found it worked well for small spreadsheets, but did not scale well for large datasets for speed and memory usage. To write larger amounts of data without speed and memory issues, I came up with a simple lightweight PHP XLSX Spreadsheet Writer library.

PHP_XLSX_Writer:
The goals of PHP XLSX Writer are simple:
* lightweight library (a single php class file)
* low memory usage
* optimize for speed
* support UTF-8 characters
Limitations:
* only supports writing XLSX files
* assumes all inputs are already valid UTF-8

Simple Example:

<?php
include_once("xlsxwriter.class.php");
$data = array(
    array('year','month','amount'),
    array('2004','1','220'),
    array('2004','2','153.5'),
);
$writer = new XLSXWriter();
$writer->writeSheet($data);
$writer->writeToFile('output.xlsx');
?>

Example demonstrating UTF-8 Support

include_once("xlsxwriter.class.php");
$data = array(
    array('name','views'),
    array('Mark Henry Rowswell','43'),
    array('大山','432432'),
    array("\xe5\xa4\xa7\xe5\xb1\xb1",'432432'), //escaped UTF-8 of previous row
);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
$writer = new XLSXWriter();
$writer->setAuthor('Doc Author');
$writer->writeSheet($data);
echo $writer->writeToString();

Cell Formatting

include_once("xlsxwriter.class.php");
$header = array(
  'create_date'=>'date',
  'quantity'=>'string',
  'product_id'=>'string',
  'amount'=>'money',
  'description'=>'string',
);
$data = array(
    array('2014-01-01',1,27,'44.00','twig'),
    array('2014-01-05',1,'=C1','-44.00','refund'),
);
$writer = new XLSXWriter();
$writer->writeSheet($data,'Sheet1', $header);
$writer->writeToFile('example.xlsx');

Multiple Sheets

include_once("xlsxwriter.class.php");
$data1 = array(  
     array('5','3'),
     array('1','6'),
);
$data2 = array(  
     array('2','7','9'),
     array('4','8','0'),
);
header('Content-Type: application/vnd.openxmlformats-officedocument.spreadsheetml.sheet');
header('Content-Disposition: attachment;filename="myfile.xlsx"');
header('Cache-Control: max-age=0');
$writer = new XLSXWriter();
$writer->writeSheet($data1);
$writer->writeSheet($data2);
$writer->writeToStdOut();//like echo $writer->writeToString();

Download xlsxwriter.class.php from github
View xlsxwriter.class.php source on github

code snippets are licensed under Creative Commons CC-By-SA 3.0 (unless otherwise specified)

Ciprian on 2014-10-03 10:46:35

I have to admit, it works great and does the job as intended. And most of all, it's super fast!
Thank you! :)

Rokonz Zaman on 2016-03-31 09:10:14

Its working perfectly. Cool!
Thanks!

zedwood.com

Create XLSX Spreadsheets in PHP