PEAR’s ‘Excel Writer’ is a nifty module to create Excel documents form PHP. Its a port of the perl
Spreadsheet::WriteExcel module. It supports cell formating, formulas, multiple worksheets, page size setting, notes and more.
Below is given a small example code to show how easy it is to create a Excel document. The created document can be saved to the server or sent to the browser directly.
require_once 'Spreadsheet/Excel/Writer.php';
// Creating a workbook
$workbook = new Spreadsheet_Excel_Writer();
/* Sending HTTP headers, this will popup a file save/open
dialog in the browser when this file is run
*/
$workbook->send('excelTest.xls');
// Create 2 worksheets
$worksheet1 =& $workbook->addWorksheet('worksheet 1');
$worksheet2 =& $workbook->addWorksheet('worksheet 2');
// Set header formating for Sheet 1
$header =& $workbook->addFormat();
$header->setBold(); // Make it bold
$header->setColor('black'); // Make foreground color black
$header->setFgColor("green"); // Set background color to green
$header->setHAlign('center'); // Align text to center
// Write some data on Sheet 1
$worksheet1->write(0, 0, 'Name', $header);
$worksheet1->write(0, 1, 'Age', $header);
$worksheet1->write(1, 0, 'Sameer Borate');
$worksheet1->write(1, 1, 30);
// Set header formating for Sheet 2
$header =& $workbook->addFormat();
$header->setBold(); // Make it bold
$header->setFgColor("red"); // Set background color to red
$header->setHAlign('center'); // Align text to center
// Write some data on Sheet 2
$worksheet2->write(0, 0, 'Name', $header);
$worksheet2->write(0, 1, 'Age', $header);
$worksheet2->write(1, 0, 'Tom Peters');
$worksheet2->write(1, 1, 30);
// Send the file to the browser
$workbook->close();
?>
I understood how to fill an excel file witha data. But how can I add makro VBA code to the excel file, so that the user has a set of working makros delivered with the data. Thanks in advance, Steven.
No you cannot put macros using PEAR::Spreadsheet_Excel_Writer.
OK, it doesn’t work with PEAR’s ‘Excel Writer’ … are there any other ways to create excel files with VBA code inside from a web server?
Not that I know of. You can try one of these although they are for .NET languages. The first one can also be used with PHP as said on their web page.
1. http://www.aspose.com/categories/file-format-components/aspose.cells-for-.net-and-java/default.aspx
2. http://www.carlosag.net/Tools/ExcelXmlWriter/
3. http://www.codeplex.com/ExcelPackage
… I’ll have a try. Thanks!
a nice post! it’s so helpful..
now,how if i want to export from php but i’ve data from database and use adodb library.
i’ve tried like this : $worksheet1->write_string(5,0,$res->fields[NAME]);
But it’s also not worked…
thanks
require_once ‘Spreadsheet/Excel/Writer.php’;
is this file in the PHP library?
Its in the PEAR ‘Excel Writer’ library
yup, but you MUST install OLE too, which is a dependency of SEW, otherwise nothing will work.
I have used this code in my project for excel file generation.but after adding writer.php .it is asking for pear.php .can you tell me from where i can get this file.
Im having the same issue, its asking for pear.php. i copied the pear.php file from the bin directory, that does not seem to work.
Just copying the file does not work. You need to set the include path correctly.
http://php.net/manual/en/function.set-include-path.php
Hi, I would like to insert in a cell a list, maybe as autofilter or another method used. I have found examples in pearl but not in php.
Do you have an idea how to work with lists in a cell?