How to Create/Read EXCEL files in PHP
dateAugust 29 2013 | comments 0 comments
In the next few lines I’ll try to explain you how to read and how create excel files using PHP and PHPExcel library.

PHP is the most popular server side scripting language designed for web development. PHP stands for Power Hypertext Processor, PHP: Hypertext Preprocessor, Pre Hypertext Processor, but it originally stood for Personal Home Page. At this moment it used by more than 250 million websites and more than 3 million webservers. From 1995, when it was created by Rasmus Lerford, until now PHP language has known an ascending popularity amongst web developers. But if you are curious to see how popular PHP is in fact you can see the Netcraft Survey and you can find more details about PHP on their official website.


PHPExcel is one of the PHPOffice suite pack and it was created to allow web developers to write/read different spreadsheet file formats, like BIFF, XLSX, CSV, ODS, Gnumeric, HTML, PDF. The resulting product of this library isn’t so different from the MSExcel but there is still a difference in the calculation module, in the formula parsing etc. So if you’re writing a WRONG FORMULA in MSExcel you will get a warning, but in PHPExcel the formula is crucial: wrong formula => FATAL ERROR! As I said earlier, don’t expect to get a correct result of index function cause the contributors used a LARP parser to get into formula and to calculate all the values instead of getting their parameters and checking where the element should be...
IE: =INDEX(Sheet1!A1:A30,2) will calculate all the values for that range -> which is wrong in this case, SUM function isn’t perfect also…but what is, after all? If you want to sum ‘1’ with ‘-‘ you will get a N/A error… Once again a correct formula means everything!

Now let’s see how to read a file in PHPExcel.
Our input file:

Our code to read the xlsx file:

The results look like:


Now I’m going to show how you can create a xlsx file using this server side scripting library. So we're going to create an excel file with 3 sheets, first called ‘WriteXLSX’, second called ‘t2’ and the last one called ‘t3’. In WriteXLSX we are going to complete all cells from 1 to 10 for rows and columns, in the second we put some numbers for the A1:B4 range and in the last sheet we should see the formula results for the t2 range as sum and product.


The result looks like this:



This library is very useful if you want to use it as background matrix for calculations with formulas stored in the database. I personally tested it with 10 sheets and more than 2000 cells in each sheet. I really hope you enjoyed and wish you good luck with it!

Useful links:
Official php website
Official PHPExcel website
PHPExcel on GitHub


dateAugust 29 2013 | comments 0 comments

How to Create/Read EXCEL files in PHP
Comments
Blog post currently doesn't have any comments.
Leave comment



 Security code