PHP code to write excel spreadsheet with multiple workbooks

Hi all,
I have a linux based web app which prints an html screen of results.
My users really want Excel spreadsheets with the same results. There
is a PEAR application which does this, but from the PEAR description it
seems to be pretty buggy (65 open bugs, average days open 616 days) and
dated.

My spreadsheets will be somewhat simple. They will have multiple
workbooks and could be as large as 10000 rows spread out over 100
workbooks (Bigger than the code can handle, according to one of the open
bug reports). No colors, no formulas, no hyperlinks.

I've checked the archives and found an example which shows me how to
write the results of an SQL (MySQL, I am using postresql, but the
example will still work) into a very simple Excel spreadsheet with just
one table of results. Is there a way to get a php program to generate a
spreadsheet with multiple workbooks?

Thanks

Mary

On Fri, Mar 28, 2008 at 1:12 PM, Mary Anderson
<maryfran@demog.berkeley.edu> wrote:
> Hi all,
> I have a linux based web app which prints an html screen of results.
> My users really want Excel spreadsheets with the same results. There
> is a PEAR application which does this, but from the PEAR description it
> seems to be pretty buggy (65 open bugs, average days open 616 days) and
> dated.
>
> My spreadsheets will be somewhat simple. They will have multiple
> workbooks and could be as large as 10000 rows spread out over 100
> workbooks (Bigger than the code can handle, according to one of the open
> bug reports). No colors, no formulas, no hyperlinks.
>
> I've checked the archives and found an example which shows me how to
> write the results of an SQL (MySQL, I am using postresql, but the
> example will still work) into a very simple Excel spreadsheet with just
> one table of results. Is there a way to get a php program to generate a
> spreadsheet with multiple workbooks?
>

There is an XML format that Microsoft has documented that will do
multi-workbook spreadsheets in Excel. (You can get an idea by saving
an existing spreadsheet as XML and looking at the source.) I haven't
used any libraries like PEAR to build them, but it isn't too difficult
to write with the basic PHP XML libraries like XMLWriter or DOM.

Andrew

Mary Anderson wrote:
> I have a linux based web app which prints an html screen of results.
> My users really want Excel spreadsheets with the same results. There
> is a PEAR application which does this, but from the PEAR description it
> seems to be pretty buggy (65 open bugs, average days open 616 days) and
> dated.
As long as your spreadsheets are simple without too much formatting etc., I
don't think you should worry too much about these bugs. I have used the
package in a variety of contexts without problems.

> My spreadsheets will be somewhat simple. They will have multiple
> workbooks and could be as large as 10000 rows spread out over 100
> workbooks
I assume you mean multiple worksheets, not workbooks (AFAIK there can be
only one workbook per file)? I don't have any experience with a file that big.


You may also have a look at the PHPExcel package:
http://www.phpexcel.net/


Christian

Spreadsheet_Excel_Writer will do this. There's a tutorial here:

http://www.sitepoint.com/article/pea...t_excel_writer

Regards,

John Peters

On Mar 28, 1:12 pm, maryf...@demog.berkeley.edu (Mary Anderson) wrote:
> Hi all,
> I have a linux based web app which prints an html screen of results.
> My users really want Excel spreadsheets with the same results. There
> is a PEAR application which does this, but from the PEAR description it
> seems to be pretty buggy (65 open bugs, average days open 616 days) and
> dated.
>
> My spreadsheets will be somewhat simple. They will have multiple
> workbooks and could be as large as 10000 rows spread out over 100
> workbooks (Bigger than the code can handle, according to one of the open
> bug reports). No colors, no formulas, no hyperlinks.
>
> I've checked the archives and found an example which shows me how to
> write the results of an SQL (MySQL, I am using postresql, but the
> example will still work) into a very simple Excel spreadsheet with just
> one table of results. Is there a way to get a php program to generate a
> spreadsheet with multiple workbooks?
>
> Thanks
>
> Mary