Generating Spreadsheets with PHP and PEAR

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.

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

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

This is absolutely excellent.

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

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.

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

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

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.

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 );

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

Quote:

---

Originally Posted by jak007

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/

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

Hi there,

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

I used these two commands to install the needed packages:

Code:

---

$ pear install OLE
$ pear install Spreadsheet_Excel_Writer

---

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

Code:

---

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

---

After that I get the following error message:

Quote:

---

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:

Code:

---

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

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?

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.

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?

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

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?

@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.