Looking for more information on how to do PHP the right way? Check out PHP: The Right Way

BitExpert Blog:
Think About It: PHP/PostgreSQL Bulk Performance (Part 3)
Jul 24, 2015 @ 10:46:06

On the bitExpert blog they've continued their "Think About It" series of posts looking at optimizations that can be made to different technologies in their stack to increase performance. In this third part of the series they focus in on the changes made to help speed things up with the PostgreSQL database backend.

This article is the last of a three-part series and describes how we optimized the persistence process of bulk data in our code in combination with PostgreSQL. Make sure you covered the first article about how we tweaked PHPExcel to run faster while reading Excel and CSV files and the second article about how we optimized our data processing and reached performance improvements tweaking our code.

They work from the example code provided at the end of part two and update the "update" handling to optimize it a bit. By default it executes an update query for each record so, instead, they modified it to perform a bulk update with an "update from values" format. They could then migrate to a "save all" handler with the complete set of records to save.

tagged: performance postgresql bulk series part3 tutorial phpexcel excel csv

Link: https://blog.bitexpert.de/blog/think-about-it-php-postgresql-bulk-performance-part-3/

BitExpert Blog:
Think About It: Loop Iteration Per
Jul 15, 2015 @ 09:30:16

On the BitExpert.com blog Florian Horn continues his "Think About It" series (part 2) looking at performance enhancements that can be made when using the PHPExcel library and in their overall data processing. In this article they build on part one and share a few more handy performance tweaks.

This article is the second of a three-part series and describes how we optimized our data processing and reached performance improvements tweaking our code. Make sure you covered the first article about how we tweaked PHPExcel to run faster while reading Excel and CSV files.

He shows how they replaced some repeated looping and generating entities with an index-cached set. This set uses the ID of the element as the index and makes it faster and easier to reference the value. This dropped their overall loop handling of the imported data by half.

tagged: phpexcel performance update tweak part2 series indexcached set

Link: https://blog.bitexpert.de/blog/think-about-it-loop-iteration-performance-part-2/

BitExpert Blog:
Think About It: PHPExcel Performance Tweaks (Part 1)
Jul 07, 2015 @ 10:34:21

Florian Horn has posted the first part of a series of performance tweaks for using PHPExcel to work with Excel spreadsheets and CSV data.

A few weeks back I covered a small article about a CSV-Tool optimized for memory usage and additionally tweaking performance. Our performance optimization sprint contained the improvement of read file data, processing and persist it. While the file data is relatively small referred to the file size, the amount of data sets can vary between 5.000 and more then 40.000 entities on an average, but may be a lot more in some cases.

This article is the first of a three-part series and describes how we tweaked PHPExcel to run faster while reading Excel and CSV files.

In this first part of the series he goes through three different tips to improve some of the basic performance:

  • Cache Cell Index in Memory
  • Iterators and GC Optimization
  • Use Custom Read Filters

You can find out more about the PHPExcel library on the project's main page.

tagged: phpexcel performance tweak series part1 cache iterators filter

Link: https://blog.bitexpert.de/blog/think-about-it-phpexcel-performance-tweaks-part-1/

BitExpert Blog:
Processing CSV files in a memory efficient way
Apr 23, 2015 @ 10:50:59

In their latest post Florian Horn shares some of his experience in using the PHPExcel tool to parse CSV files and the performance issues he ran into. Fortunately, he found a solution...in the form of another library.

A little while ago I had to dive deeper into the performance optimized usage of PHPExcel. Our users are uploading files like Excel or CSV with a lot data to process. Initially we used the PHPEXcel instance without any tuning of the default configuration which lead to heavy memory issues on relativly small files. So I had to avoid reading all file content at ones to the buffer (like file_get_contents does).

In my research mainly optimizing the usage of PHPExcel I came across a tiny library I am grown really fond of. It is called Goodby/CSV. Both tools have a very well grounded documentation to read in and understand the basics and the usage.

He describes some of the main differences between the two tools and includes some basic benchmark results comparing memory consumption and overall speed.

tagged: phpexcel csv file goodbycsv process performance memory benchmark

Link: https://blog.bitexpert.de/blog/processing-csv-files-in-a-memory-efficient-way/

SitePoint PHP Blog:
Generate Excel Files and Charts with PHPExcel
Sep 11, 2014 @ 09:48:55

SitePoint's PHP blog has a new post that walks you through the creation of Excel files with the help of the PHPExcel tool, a part of the overall PHPOffice package.

After my article “How To Make Microsoft Word Documents with PHP” (using Interop capability under Windows), there were quite a few comments urging a pure PHP implementation, i.e., only using a universal PHP library to manipulate Office files. In this article, we will see how to use a PHPExcel library to provide an “Export to Excel” function in a web app so that the user can export the data into an Excel 2007/2013 file for further analysis.

His example spreadsheet shows game information for an NBA team (the LA Lakers) including some charting and analytic data in the exported output. He sets up a simple Silex application and both a base and export endpoints. He also shows you how to create the PHPExcel instance, configure it with properties like creator, title and a set of keywords. With this object in place he moves into setting data for each of the rows/cells, populating it from a database table. He also includes examples of creating more than one sheet of results, inserting formulas and creating the charts mentioned earlier.

tagged: phpexcel tutorial excel microsoft chart formular export

Link: http://www.sitepoint.com/generate-excel-files-charts-phpexcel/

How to Generate a Complete Excel Spreadsheet From MySQL
Nov 23, 2011 @ 17:52:54

On NetTuts there's a new tutorial showing how to take data from a MySQL database and translate it into a usable Excel file with the help of some simple PHP.

A CSV (Comma Separated Value) file is usually sufficient for exporting MySQL data as an Excel Spreadsheet. These CSV files are data only though. A real Excel spreadsheet has formatting, formulas and perhaps even graphics - the difference between a simple method of data transfer and a professional report. This tutorial shows how to use open source PHP components to create "real" Excel spreadsheets from MySQL SELECT statements.

With the help of the PHPExcel tool, making Excel-formatted files is a simple process. They show how to label columns, pull data out with a "quick and dirty" SQL statement, formatting the results to something a bit more clean and iterate through the pages of data to push them into the spreadsheet (including formulas). You can download the full source to get everything in one shot.

tagged: excel spreadsheet tutorial phpexcel mysql database


Ilia Alshanetsky's Blog:
PHP Excel Extension
Aug 02, 2010 @ 13:12:02

In a new post to his blog today Ilia Alshanetsky talks about a PHP Excel extension to help with some output generation problems he was seeing (based on the LibXL libraries).

As we are doing more & more Excel output generation, this became a bigger and bigger problem. At first we've tried solving the problem via a newer PHP based Excel library, PHPExcel. Unfortunately, it is massive beast, that is not only slower than the old Spreadsheet Excel Writer/Reader, but memory hog too. [...] So, I turned to Google and found LibXL, which is a small, C++ (with C, C++ interfaces) library that promised really fast Excel reading & writing.

His extension gives you an object-oriented interface to the library that can generate Excel files with a lot less resources than some of the other PHP Excel extensions out there. He includes a sample benchmarking script he used to generate these stats.

tagged: excel extension libxl phpexcel github


Import and export data using PHPExcel
Jul 15, 2010 @ 10:21:12

On the ThinkPHP blog today there's a new post looking at their experience with PHPExcel to open, modify, save, etc Microsoft Excel files directly from PHP.

EAR's Spreadsheet_Excel_Writer combined with the project Spreadsheet_Excel_Reader on SourceForge was a good helper in the past - but only for BIFF5. BIFF8 support in spreadsheet excel writer has been a problem for a long time, and according to the authors, is still somewhat kludgy in the current version. So I needed an alternative. After a short research I stumbled upon PHPExcel which supports reading and writing of many formats in one API.

He lists some of these input and output formats and includes a simple example of how to use the tool. He creates a basic Excel file with a few different attributes (title, body, keywords) and show how to read in a document to extract the document's content.

tagged: import phpexcel export tutorial excel microsoft


Maarten Balliauw's Blog:
Saving a PHPExcel spreadsheet to Google Documents
Feb 03, 2009 @ 12:58:56

Maarten Balliauw has written up a guide to saving the output of your PHPExcel applications out to the Documents service that Google offers.

As you may know, PHPExcel is built using an extensible model, supporting different input and output formats. The PHPExcel core class library features a spreadsheet engine, which is supported by IReader and IWriter instances used for reading and writing a spreadsheet to/from a file. Currently, PHPExcel supports writers for Excel2007, Excel5 (Excel 97+), CSV, HTML and PDF. Wouldnt it be nice if we could use PHPExcel to store a spreadsheet on Google Documents? Let’s combine some technologies.

The two technologies in question are a standard install of the PHPExcel library and the Zend Framework (with its Zend_Gdata component). Using the component, a save() method is created and hooked into the IWriter interface of PHPExcel. When this save method is called, the Zend_Gdata component connects to the Documents service and uploads the resulting information for the account you supply.

tagged: tutorial save phpexcel extensible document google spreadsheet zendframework zendgdata


Maarten Balliauw's Blog:
Reuse Excel business logic with PHPExcel
May 06, 2008 @ 07:51:38

Maarten Balliauw has made a new blog post today about a method he's using to help reuse some of the business logic that Excel spreadsheets can have in a PHP script with help from PHPExcel.

In many companies, business logic resides in Excel. This business logic is sometimes created by business analysts and sometimes by business users who want to automate parts of their everyday job using Excel. [...] Did you know you can use PHPExcel to take advantage of the Excel-based business logic without having to worry about duplicate business logic?

He creates a quick example of a script that can take in an Excal file and pull it into a PHPExcel object, ready for manipulation. He fills in values for the already defined fields (like "carColor" or "leatherSeats") and uses the getCalculatedValue method to perform the action on the cell. The output is dropped into a variable that can be echoed out or used later on in the PHP script.

tagged: phpexcel excel business logic reuse tutorial example