Generating Spreadsheets with PHP and PEAR

[ArticleBot]

This is an article discussion thread for discussing the SitePoint article, "Generating Spreadsheets with PHP and PEAR"

[riu]

Too bad it cannot open an existing spreadsheet as a template first. Building a nice spreadsheet from pure code sounds rather nasty - I'd much rather just make a spreadsheet in Excel, upload it, and then get PHP to just change the relevant numbers.

[lmpmbernardo]

Just for the record there is a PHP-ExcelReader in sourceforge. I never tested it though.

[Kevin Kraeer]

This is absolutely excellent.

My only question is, can you append new entries to the same XLS file?

[chedlee]

Excellent tutorial, I am now generating spreadsheets very easily. One small problem is inserting images in openoffice calc, it works perfectly in excel but not in calc.

[Roj2002]

Great tutorial, but has anyone an idea how to add linefeeds to a cell? \n or \n\r is not working...

[Valentijn]

Hi

Question about excel and php.
In code i see flying around usin pear with php etc.
I see that have to make a new object by creating a new worksheet of document based on: PEAR::Spreadsheet_Excel_Writer

But I prever making a template and adding data to it, overriding information when writing from pear/php/excelwriter.
But there is no such thing of using excel templates within the pear/php framework I guess. So I have to make a hard coded design template in stead of the easy way layouting a visual rich excel sheet for reports etc.

Hope you have an answer !

Valentijn
info@multimediaal.nl

[Sam]

Very Nice tutorial. But in example_2.php it is said that the Spreadsheet would be generated dynamically, but I saw that the file is getting stored on the server.
Does this mean there is no such feature that the file can get generated dynamically.

[bxny]

For the linefeed question above, someone provided me with this and it works:

$multipleLineDataFormat = &$workbook->addFormat( array(
'Border'=> 1, 'Align' => 'left' ) );
$multipleLineDataFormat->setTextWrap();

$userString = 'line 1' . " \n ";
$userString .= 'line 2' . " \n ";
$userString .= 'Much longer line 3';

$worksheet->write( 0, 2, $userString, $multipleLineDataFormat );

[jak007]

Very Nice tutorial. I just have only one question, how can I open an existing workbook/sheet using Spreadsheet_Excel_Writer?

[laurik]

Very Nice tutorial. I just have only one question, how can I open an existing workbook/sheet using Spreadsheet_Excel_Writer?

Try http://sourceforge.net/projects/phpexcelreader/

[markc]

be careful about the first two examples!
example 1: my browsers (ie /ff) couldnt open it as an excel file - it showed a mess
example 2: cut and pasting the text misses the crucial href link (use the download above instead)

more importantly the writeRow function is not in the current release of Spreadsheet_Excel_Writer

[Jethro]

Hi there,

I find your article very useful. However I have this problem:

I used these two commands to install the needed packages:

$ pear install OLE
$ pear install Spreadsheet_Excel_Writer

This is what I do in my code to include the neccesary file:

require_once('Spreadsheet/Excel/Writer.php');

After that I get the following error message:

Warning: main(Spreadsheet/Excel/Writer.php) [function.main]: failed to open stream: No such file or directory in /usr/home/jesdesign/jdhd/Projecten/Bos Consultancy/webnet/intern/wwwroot/_exceltest.php on line 6

Fatal error: main() [function.require]: Failed opening required 'Spreadsheet/Excel/Writer.php' (include_path='.:/usr/home/jesdesign/jdhd/Projecten/Bos Consultancy/webnet/intern/wwwroot/includes') in /usr/home/jesdesign/jdhd/Projecten/Bos Consultancy/webnet/intern/wwwroot/_exceltest.php on line 6

When I searh my FreeBSD server for the file Writer.php this is what I get:

su-2.05b# locate Writer.php
/usr/local/lib/php/Spreadsheet/Excel/Writer.php

Apparently the PEAR package is installed, does anybody know what I am doing wrong?

Best regards,
Jethro

[Cezar708]

Hello all,

I've a problem with writting formula.
Example:
[Cell Sheet_1.A1] <- =Sheet_2.A1
PHP code:
<?
// ...other statements...
$Sheet_1->writeFormula(0,0,"=Sheet_2.A1");
// ...other statements...
?>
finally it writes string "=Sheet" instead of "=Sheet_2.A1".

Do you know why?

[Doug]

How do I output data from a MySQL query into an excel file? I get problems that only the last row shows up with several blank rows before it.

[Rodney]

I get good results from this, exporting to Open Office Calc, thank you. However, I would like to set the Open Office Calc Format/Cells/Alignment tick box "Wrap text automatically". How, please?

[Jan]

This is a very nice article for learning, tanx.
How we can modify/append the data/contents in the already existing excel sheet?
Any help will be appricated.
Tanx in advance

[pixeline]

Hi and thank you for these PEAR tutorials. They were very helpful at getting me started with pear.

However, i don't pass beyond your first example code. It is outputting a lot of garbage. Looks like a header() instruction is messing up but i'm not sure.

can you explain me what is wrong?

[pixeline]

@jethro: ok this is 2 years later, but it may help someone:

check your php include_path and open_basedir directives. They must include /usr/bin/php in order for php to include your pear modules at run time.