[SOLVED] Write to Excel

[ober]

What do you guys use?  My servers are linux boxes and I'm curious how you guys handle Excel file creation.  Pear?  That stupid class on phpclasses.org?  Other?

[448191]

I haven't tried it, but I was planning on trying out this lib at the next opportunity:

 

http://www.codeplex.com/PHPExcel

 

If you try it, let us know if you like it.

[neylitalo]

When I have to, I use the PEAR library. I really don't like it, though, because it can only generate Excel 97 files.

[bilis_money]

thanks for  the link 448191

[neylitalo]

If that PHPExcel lib works, I'll be thrilled. I don't plan on using it, though, as I try to stay away from Excel whenever possible.

[corbin]

Does it definitely have to be an Excel file?  Can't just be a CSV file?  Guessing you need to modify an already existing Excel file....

[448191]

CSV files have some issues spoiled clients aren't ready to deal with. They expect to simply choose "open with Excel" and it'll work.

 

- Formatting options (seperator, boundraries)

  You have to know what options are default on the clients system. Otherwise they have to do "text to columns" or import.

 

- Character encoding

  Again, you need to know what the default is on the clients system. Otherwise they have to do import and choose the right encoding.

  If your data is in UTF8, they will definitely have to do this, or you have to convert the source data.

 

In all, using CSV is quite a bit more trouble than one would think. Generating Excel files is less hassle.

 

Not to mention Excel sheets will allow a plenitude of extra options such as multiple sheets and data input validation (which would be extremely useful for imports).

 

You know what, I think I'll give it a quick go later today.

[448191]

Ok, I had little play with the test scripts, and in all honesty, it looks pretty damn good. It is however very expensive on memory and processor time. Also it has quite serious requirements (I don't quite see why the Zip extension is required, but there's probably a good reason for it).

 

Bellow script creates a very basic file. It has a peak memory usage of 7.75 MB and took 0.451s to complete.

 

$objPHPExcel = new PHPExcel();

// Set properties
$objPHPExcel->getProperties()->setCreator("Maarten Balliauw");
$objPHPExcel->getProperties()->setLastModifiedBy("Maarten Balliauw");
$objPHPExcel->getProperties()->setTitle("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setSubject("Office 2007 XLSX Test Document");
$objPHPExcel->getProperties()->setDescription("Test document for Office 2007 XLSX, generated using PHP classes.");
$objPHPExcel->getProperties()->setKeywords("office 2007 openxml php");
$objPHPExcel->getProperties()->setCategory("Test result file");


$objPHPExcel->setActiveSheetIndex(0);
$objPHPExcel->getActiveSheet()->setCellValue('A1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('B2', 'world!');
$objPHPExcel->getActiveSheet()->setCellValue('C1', 'Hello');
$objPHPExcel->getActiveSheet()->setCellValue('D2', 'world!');

// Rename sheet
$objPHPExcel->getActiveSheet()->setTitle('Simple');


// Set active sheet index to the first sheet, so Excel opens this as the first sheet
$objPHPExcel->setActiveSheetIndex(0);


// Save Excel 2007 file
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('C:\Documents and Settings\John\Bureaublad\1.6.2\test.xlsx');

 

Writing an Excel5 file replacing the createWriter statement with:

 

$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');

 

Uses 8.25 MB and takes 0.4828.

 

File attached.

 

The memory usage stays about the same doing more complicated stuff, but processing time quickly skyrockets (and this is on a 'very fast' system).

 

Doing more complicated things apparently requires A LOT of coding. You have to apply stuff manually on every cell. Example:

 

$objPHPExcel->getActiveSheet()->getStyle('E11')->getBorders()->getRight()->setBorderStyle(PHPExcel_Style_Border::BORDER_THIN);

 

Interface could be better. :/ Probably if I ever need to do something like this I would create an interface to apply styles on ranges.

 

I would post the code, but it's absolutely huge. It's included in the distribution anyway.

 

Creating this file took just over 8 seconds.. 

 

NOTE: I had rename the files from .xlsx to .txt to be able to upload them to the forums.

 

 

[attachment deleted by admin]

[ober]

Thanks for the code John... I'll give that a whirl.  Much appreciated. 

[corbin]

Nice!  And good reasons not to use CSV.... ;p

[448191]

Thanks for the code John... I'll give that a whirl.  Much appreciated. 

 

You're welcome, but I didn't code that Just part of my mini review.

 

It is included in the distro.

[ober]

Yeah... I noticed they hand a bunch of code sitting on the site after I visited it.  Now I just think you're a cheater

[ober]

I just wanted to follow up on this thread.  I just started using this to generate some reports for a client and it is fantastic.  I agree that the styling interface could be simplified, but I'll take what I can get.  Very powerful and easy to setup and use.