Import and export data using PHPExcel

There’s a newer version of this article topic available (2014). Please read on at Performant handling of Excel files in PHP.
A few weeks ago I had to read and write Excel files of the format BIFF8 (Excel 97), because the customer did not accept the workaround of exporting data to CSV. PEAR’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. It is released under the GNU Lesser General Public License which gives you the freedom to use it in commercial applications.
Output and input formats are not limited to Excel files. This article gives you a short overview about what you can do with it and demonstrates the basic usage.

Input formats

  • Excel 2007 (SpreadsheetML)
  • BIFF5 (Excel 5.0 / Excel 95)
  • BIFF8 (Excel 97 and later)
  • PHPExcel Serialized Spreadsheet
  • Symbolic Link (SYLK by Microsoft)
  • CSV (Comma Separated Values)

 

Output formats

  • Excel 2007 (SpreadsheetML)
  • BIFF8 (Excel 97 and later)
  • PHPExcel Serialized Spreadsheet
  • PDF
  • HTML
  • CSV (Comma Separated Values)

Installing PHPExcel

Make sure your server is running PHP-Version 5.2.0 or higher and has the following extensions installed: php_zip, php_xml and php_gd2.
You can grab your copy of PHPExcel from the project’s homepage. I preferred the PEAR-package which was comfortable to install. Since I already use PEAR, the necessary folder is in the include_path. But even if you are not using PEAR, the installation is painless – just add the home directory of PHPExcel to the include_path in your php.ini file and restart your web server.

Creating a spreadsheet

After unpacking the downloaded package you will find a subfolder Tests which contains a bunch of demo files. When you want to delve deeper into the use, take a look at the demonstration files to get familiar with the handling of PHPExcel’s methods.

Now you are ready to build your first Excel file. After instantiating the object some meta data is set like this:

As you can see, you can use the fluent interface (method chaining) nearly anywhere in the class.
Method names are self-explanatory.
Now you have a PHPExcel-object with meta data but without any content except a worksheet which is called „Minimalistic demo“. At the creation of the object, the first worksheet is built automatically. Of course you can add further worksheets if needed. For now the first worksheet is set as the active one. This will cause Excel or Open Office to show this worksheet after the file is loaded. Proceed inserting the famous „hello world“ data into it.

Cells are referenced by their Excel coordinates, e.g. „C3“. This isn’t very convenient when iterating through greater blocks. Fortunately there is another method that can handle numeric X- and Y-coordinates:

Note that rows are 1-based whereas columns are 0-based, so „A1“ becomes (0,1). The last step is to write the Excel file to the harddisk. This is done by creating a writer object using the PHPExcel_IOFactory. The second parameter determines the output format.

There is a bunch of methods to style, format, merge and validate cells. Take a look at the documentation if you want to decorate your output.

Reading a spreadsheet

PHPExcel is also capable to read Excel files and extract all data. In a real world scenario this could be the import of a product database. This can be useful if you e.g. want to offer your customer a direct upload of an Excel file instead of an exported CSV file. Nevertheless, since PHPExcel can open both file types you could offer both possibilities with very little additional effort.

Reading is just as easy as writing. Once again the IOFactory is used for accessing the file:

The use of a worksheet iterator lets you walk through files of unknown structure. The following snippet shows how you could do this.

By combining the reader with the writer it is possible to convert files from one format to another with just 3 lines of code:

Another interesting approach is to re-use business logic that is saved in an Excel file. Instead of re-coding it on the web site you could use the extracted logic delivered by PHPExcel. The project leader – Maarten Balliauw – spent some thoughts on this in his blog.

It is also possible (and even faster than creating from scratch) to load an excel file and afterwards add data to it. Just like any template you could save a base file containing the corporate layout of a company, load it and continue building a spreadsheet from there. Whenever the layout changes, you just need to switch the base template.

Side notes

PHPExcel can be a memory hog. About 1k is needed per cell. Depending on the size of your spreadsheet you could run into problems.
The developers already took care of this by offering a caching mechanism through a CachedObjectStorageFactory. The manual states:

Cell caching provides a mechanism that allows PHPExcel to maintain the cell objects in a smaller size of memory, on disk, or in APC, memcache or Wincache, rather than in PHP memory. This allows you to reduce the memory usage for large workbooks, although at a cost of speed to access cell data.

Future

PHPExcel is not able to create charts – yet. It is on the roadmap already, a lot of work has been done already. This will probably be in one of the next releases.

Conclusion

PHPExcel comes up with a bunch of useful methods that really fits the needs of a developer. The documentation is well structured and the many test files enables you to quickly find out how the class can be used. Additionally the files are documented using PHPDocBlocks; so IDEs like ZendStudio or NetBeans will show tooltips.

In summary: PHPExcel definitely is worth a look because it can handle many formats.

Für neue Blogupdates anmelden:


21 Gedanken zu “Import and export data using PHPExcel

  1. Hi,
    Nice introduction. I have been using PHPExcel for some years now. Mostly I create & mail Excel sheets from mysql from a Linux environment.
    PHPExcel is a very fine piece of software, and the developers are very helpful.

    • Thanks for reading.
      Watch the example code of the worksheet iterator. Instead of outputting the data, you could use it to build a sql-string.

  2. I have a problem with PHPExcel.

    I have an Excel Template file that I loaded. However, when I tried to write the output Excel file, it gives me an error regarding getBlipType() function. By the way, the Excel template file has images on the first column.

    Please help.
    Thank you in advance.

      • Thanks for the reply Sir Daniel! =D

        By the way, here is the error:

        Notice: Undefined offset: -1 in E:\wamp\www\zf\library\PhpExcel\Classes\PHPExcel\Reader\Excel5.php on line 896

        Fatal error: Call to a member function getBlipType() on a non-object in E:\wamp\www\zf\library\PhpExcel\Classes\PHPExcel\Reader\Excel5.php on line 897

        The excel template file seemed like a class record, wherein the first column had the id number of the students, the second column contained the the id pictures (in bmp or jpg), then the subjects. When I deleted the column for pictures, the template was loaded properly but if I put it back again, I was receiving this error. =C

  3. hi,
    I am not able read .xlsx file with the above code.. working fine with .csv file. But i require only .xsl/.xlsx file to import into data base.. Please help. And also let me know how to do it in php yii.

    Thanks in advance.

  4. i have explored a lot but couldn’t find the solution hope here i can get help.
    My query is if i have a html table with many and then how can i export that table only.

    thanks in advanced

  5. Does php support double circular references? I know its no problem with just a single circular reference, but with a double I seem to be having problems getting the same result as excel.

    Thanks

  6. Is their any way in excel .. When i put value in cell A1 hello then in excel it gives value of cell hello not just hello in bold.. Can you please provide a script html convert as text in excel when it download. Its very difficult for me.

  7. I’m looking to create a script to upload CSV files or xls and insert into the database. I’ve managed to make it until now, but I w

    ID CODE CAP1 CAP2 CAP3
    1 AA 50 60 70
    2 BB 80 90 100
    3 AA 120 130
    What I would like to do is to insert it in a mysql table tbl_dimensions like this:

    RECID DRAFT_IMPORT CODE ID HEADER VALUE
    1 XXXXX AA 1 CAP1 50
    2 XXXXX AA 1 CAP2 60
    3 XXXXX AA 1 CAP3 70
    4 XXXXX BB 2 CAP1 80
    5 XXXXX BB 2 CAP2 90
    6 XXXXX BB 2 CAP3 100
    7 XXXXX AA 3 CAP1 NULL/0
    8 XXXXX AA 3 CAP2 120
    9 XXXXX AA 3 CAP3 130
    Basically instead of creating a wide table in SQL in want to create a list in a table but I need to keep the item Code, AA, BB, etc. It’s possible to have the same code multiple time so I want to use the row nr from CSV/XLS to coordinate the values from CAP1, CAP2, CAP3. Cap1,2 3, can be anything and changes every day so I cant define a table wide enough for each option that will occure.

    Thank you very much

  8. Hi all
    I have code similar this but i upload mi code in apache mounted in centos 7
    well
    my problem is that when read a file xlsx it doesn’t work and when read a file xls there isn’t problem.

    I think that there is a problem with some config of my server.

    Could you please help me?

  9. Thanks for the informative article. This is one of the best resources I have found in quite some time. Nicely written and great info. I really cannot thank you enough for sharing.

Schreibe einen Kommentar

Deine E-Mail-Adresse wird nicht veröffentlicht. Erforderliche Felder sind mit * markiert.