Juozas devBlog

In one of my projects, we need to send emails to our suppliers with new orders as Excel spreadsheets. We used to do it by hand, but when you have 10 suppliers and you make 200 products orders per week, after some time you are just tired of creating Excel spreadsheets, mailing them and so on. So, my task was to implement automatic spreadsheets creation with PHP.

One of my goals is to make e-commerce software as intelligent as possible and make it to do  much work as possible – people should focus on other things, computers can do a lot of boring work for you. It sounds logical, but I think that not every shop has good e-commerce system and not just catalog of products.

Creating XLS’ is not that hard at all. At first you need to download Spreadsheet_Excel_Writer (I use this re-pack) and your work is almost done. This projects development has stopped, but class itself is rock-solid and works just fine once you make it to do so. To create simple spreadsheet your code should look like this (it can be simplified a lot by removing all styling attributes):

require ('..path/Writer.php');
 
$filename = 'myFile.xls';
 
$workbook = new Spreadsheet_Excel_Writer();
$workbook->send($filename);
 
// Look at documentation
$workbook->setVersion(8);
// Temporary files directory
$workbook->setTempDir('/tmp');
 
$worksheet =& $workbook->addWorksheet('Items');
$worksheet->setInputEncoding('UTF-8');
$worksheet->setMargins(0.25);
$worksheet->centerHorizontally(1);
 
$worksheet->activate();
 
$format_header =& $workbook->addFormat();
$format_header->setBold();
$format_header->setSize(12);
 
$worksheet->write(0, 0, 'Code',$format_header);
$worksheet->setColumn(0, 0, 30);
$worksheet->write(0, 1, 'Title',$format_header);
$worksheet->setColumn(1, 1, 50);
 
$format_row =& $workbook->addFormat();
$format_row->setSize(12);
 
foreach ($items as $item)
{
	$worksheet->writeString($i, 0, $item->code, $format_row);
	$worksheet->writeString($i, 1, $item->title, $format_row);
}
 
$workbook->close();

I’m not author of this class so don’t complain to me about its bugs – but feel free to contact me if you can’t get something to work. I spent huge amount of time trying to create working XLS’ with PHP and this package was the only one which worked fine UTF-8 and didn’t have random file integrity bugs (Excel files are created by writing binary data).

By saying working above I mean, that some files worked with Microsoft Office, but not with OOfice, some didn’t worked with Google Docs and so on. I tried many different libraries and only this managed to create correctly encoded and working documents. In some cases, library seemed to work, but when I tried bigger tables, it corrupted whole file.

People use XML for Excel files, but in my opinion, if you want files to be really usable by normal files – create XLS’. For example, we are just ordering products, our task is not to teach IT skills and prove that Excel works with other formats. It’s much more easier for everyone just to send normal XLS file, believe me.