Export from PHP to Excel

tags:

excel
php

views:

answers:

Export from PHP to Excel

I need to export data from php to Excel, and be able to format various items in the excel spreadsheet.

So far, the best library that I have found is PHPExcel (http://phpexcel.codeplex.com/). However, it seems very heavy, and somewhat slow. Granted, it is very powerful.

Is there anything a bit more lightweight and faster, that allows me to export to excel and be able to apply simple formatting (bold, alignment, borders)?

+2 A:

I just got done with this yesterday. Using PHPExcel, I had no problems reading in a "master" document with formatting, writing 20-100 rows of content, and saving off the file (I save it "to screen" for immediate download. While some people on the forums complained about speed and overhead, I'm pushing a lot of data its way and it doesn't have any problem at all doing what it advertises.

Note that somewhere I read to do styling in series as opposed to in loops when possible. For example, style a1:a50 as opposed to style->a1, style->a2 in a loop. Apparently, the two different scenarios have very different memory implications.

The only gotcha I found was a few quirks between outputting and reading Excel 2003 files. If you're working entirely in XLSX files, it should function exactly as documented.

bpeterson76 2010-10-24 03:41:18

We always recommend applying styles to ranges rather than looping through individual cells... it's faster and uses less memory, and the outputted Excel file is smaller because of the shared styles. Another useful style trick is applyFromArray(), defining a set of style features (bold, colour, background colour, borders, etc) in an array and then applying them to cells in a single call rather than setting each element individually.

Mark Baker 2010-10-24 09:33:36

Yep, I definately try format ranged instead of cells, but to be honest, there really isnt that much formatting in this sheet at all. At the moment, I'm exporting about 1000 rows and 6 columns, with some range formatting on 3 rows and 3 columns. It takes about 6 seconds (of which about 1 second is query time) to run on my localhost, which is usually a decent approximation of the server's speed.

JonoB 2010-10-24 18:08:09

@JonoB - For writing your data to the PHPExcel object, have you looked at the PHPExcel_Worksheet's fromArray() method rather than setting each cell's value individually. This can be used with a Value Binder to force correct datatyping of cell values to speed up the process of building the workbook in memory.

Mark Baker 2010-10-25 07:58:18

Having worked in an enterprise shop for a major international bank, I can tell you that 6 seconds or processing is nothing compared to the convoluted online systems in place for Credit Cards. We would have been happy with 36 seconds.....

bpeterson76 2010-10-25 14:01:48

ansaurus

tags:

views:

answers:

Export from PHP to Excel

related questions