Categories
Laravel PHP

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.

Leave a Reply

Your email address will not be published. Required fields are marked *

This site uses Akismet to reduce spam. Learn how your comment data is processed.