PHP Library to write XLSX files

Excel is used to store large amount of information in the form of a spreadsheet. Which is easy to manage accounts information and other information. Now a day’s we save large amount of information online in database but some times we need to convert that information into excel spreadsheet and provides to user. In this post i will explains you, how to create excel file online using PHP_XLSXWriter library available on github. You can download this from the link : https://github.com/mk-j/PHP_XLSXWriter

This library is designed to be lightweight, and have minimal memory usage.

It is designed to output an Excel compatible spreadsheet in (Office 2007+) xlsx format, with just basic features supported:

• Supports PHP 5.2.1+
• Takes UTF-8 encoded input
• Multiple worksheets
• Supports currency/date/numeric cell formatting and simple formulas
• Supports basic cell styling
• Supports writing huge 100K+ row spreadsheets

Below are a few test cases, writing large xlsx spreadsheet files with PHPExcel and PHP_XLSXWriter for comparison.

Case	PHP_Excel	PHP_XLSXWriter
10 columns, 10,000 rows in 4 sheets (400,000 cells)	File size : 458MB Execution time : 101.55s	File size : 11MB Execution time : 7.56s
10 columns, 10,000 rows in 6 sheets (600,000 cells)	File size : 658MB Execution time : 202.72s	File size : 15MB Execution time : 11.31s
10 columns, 20,000 rows in 4 sheets (800,000 cells)	File size : 912MB Execution time : 301.31s	File size : 22MB Execution time : 14.94s

 

In a few simple tests, PHP_XLSXWriter shows a 98% savings on memory and 95% savings on time when compared to PHP_Excel for writing large files. Though PHP_Excel may have more features, it doesn’t compare with PHP_XLSXWriter when it comes to performance.

Simple PHP CLI example:

$data = array(
    array('year','month','amount'),
    array('2003','1','220'),
    array('2003','2','153.5'),
);

$writer = new XLSXWriter();
$writer->writeSheet($data);
$writer->writeToFile('output.xlsx');

Note : PHP_XLSXWriter is simple to create lightweight excel spreadsheet using PHP library.