Creating Excel documents in PHP

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

// 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

13 thoughts to “Creating Excel documents in PHP”

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

  2. 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?

  3. 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…

  4. yup, but you MUST install OLE too, which is a dependency of SEW, otherwise nothing will work.

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

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

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

Leave a Reply

Your email address will not be published.