Output and input formats are not limited to Excel files. This article gives you a short overview about what you can do with it and demonstrates the basic usage.
Input formats
- Excel 2007 (SpreadsheetML)
- BIFF5 (Excel 5.0 / Excel 95)
- BIFF8 (Excel 97 and later)
- PHPExcel Serialized Spreadsheet
- Symbolic Link (SYLK by Microsoft)
- CSV (Comma Separated Values)
Output formats
- Excel 2007 (SpreadsheetML)
- BIFF8 (Excel 97 and later)
- PHPExcel Serialized Spreadsheet
- HTML
- CSV (Comma Separated Values)
Installing PHPExcel
Make sure your server is running PHP-Version 5.2.0 or higher and has the following extensions installed: php_zip, php_xml and php_gd2.
You can grab your copy of PHPExcel from the project’s homepage. I preferred the PEAR-package which was comfortable to install. Since I already use PEAR, the necessary folder is in the include_path. But even if you are not using PEAR, the installation is painless – just add the home directory of PHPExcel to the include_path in your php.ini file and restart your web server.
Creating a spreadsheet
After unpacking the downloaded package you will find a subfolder Tests which contains a bunch of demo files. When you want to delve deeper into the use, take a look at the demonstration files to get familiar with the handling of PHPExcel’s methods.
Now you are ready to build your first Excel file. After instantiating the object some meta data is set like this:
require_once 'PHPExcel.php';
$objPHPExcel = new PHPExcel();
// Set properties
$objPHPExcel->getProperties()->setCreator("ThinkPHP")
->setLastModifiedBy("Daniel Schlichtholz")
->setTitle("Office 2007 XLSX Test Document")
->setSubject("Office 2007 XLSX Test Document")
->setDescription("Test doc for Office 2007 XLSX, generated by PHPExcel.")
->setKeywords("office 2007 openxml php")
->setCategory("Test result file");
$objPHPExcel->getActiveSheet()->setTitle('Minimalistic demo');
As you can see, you can use the fluent interface (method chaining) nearly anywhere in the class.
Method names are self-explanatory.
Now you have a PHPExcel-object with meta data but without any content except a worksheet which is called „Minimalistic demo“. At the creation of the object, the first worksheet is built automatically. Of course you can add further worksheets if needed. For now the first worksheet is set as the active one. This will cause Excel or Open Office to show this worksheet after the file is loaded. Proceed inserting the famous „hello world“ data into it.
$objPHPExcel->setActiveSheetIndex(0)
->setCellValue('A1', 'Hello')
->setCellValue('B1', 'world!');
Cells are referenced by their Excel coordinates, e.g. „C3“. This isn’t very convenient when iterating through greater blocks. Fortunately there is another method that can handle numeric X- and Y-coordinates:
setCellValueByColumnAndRow($column, $row, $value)
Note that rows are 1-based whereas columns are 0-based, so „A1“ becomes (0,1). The last step is to write the Excel file to the harddisk. This is done by creating a writer object using the PHPExcel_IOFactory. The second parameter determines the output format.
require_once 'PHPExcel/IOFactory.php';
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
// If you want to output e.g. a PDF file, simply do:
//$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'PDF');
$objWriter->save('MyExcel.xslx);
There is a bunch of methods to style, format, merge and validate cells. Take a look at the documentation if you want to decorate your output.
Reading a spreadsheet
PHPExcel is also capable to read Excel files and extract all data. In a real world scenario this could be the import of a product database. This can be useful if you e.g. want to offer your customer a direct upload of an Excel file instead of an exported CSV file. Nevertheless, since PHPExcel can open both file types you could offer both possibilities with very little additional effort.
Reading is just as easy as writing. Once again the IOFactory is used for accessing the file:
require_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");
The use of a worksheet iterator lets you walk through files of unknown structure. The following snippet shows how you could do this.
require_once 'PHPExcel/IOFactory.php';
$objPHPExcel = PHPExcel_IOFactory::load("MyExcel.xlsx");
foreach ($objPHPExcel->getWorksheetIterator() as $worksheet) {
$worksheetTitle = $worksheet->getTitle();
$highestRow = $worksheet->getHighestRow(); // e.g. 10
$highestColumn = $worksheet->getHighestColumn(); // e.g 'F'
$highestColumnIndex = PHPExcel_Cell::columnIndexFromString($highestColumn);
$nrColumns = ord($highestColumn) - 64;
echo "<br>The worksheet ".$worksheetTitle." has ";
echo $nrColumns . ' columns (A-' . $highestColumn . ') ';
echo ' and ' . $highestRow . ' row.';
echo '<br>Data: <table border="1"><tr>';
for ($row = 1; $row <= $highestRow; ++ $row) {
echo '<tr>';
for ($col = 0; $col < $highestColumnIndex; ++ $col) {
$cell = $worksheet->getCellByColumnAndRow($col, $row);
$val = $cell->getValue();
$dataType = PHPExcel_Cell_DataType::dataTypeForValue($val);
echo '<td>' . $val . '<br>(Typ ' . $dataType . ')</td>';
}
echo '</tr>';
}
echo '</table>';
}
By combining the reader with the writer it is possible to convert files from one format to another with just 3 lines of code:
$objPHPExcel = PHPExcel_IOFactory::load("XMLTest.xml");
$objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel2007');
$objWriter->save('covertedXml2Xlsx.xlsx');
Another interesting approach is to re-use business logic that is saved in an Excel file. Instead of re-coding it on the web site you could use the extracted logic delivered by PHPExcel. The project leader – Maarten Balliauw – spent some thoughts on this in his blog.
It is also possible (and even faster than creating from scratch) to load an excel file and afterwards add data to it. Just like any template you could save a base file containing the corporate layout of a company, load it and continue building a spreadsheet from there. Whenever the layout changes, you just need to switch the base template.
Side notes
PHPExcel can be a memory hog. About 1k is needed per cell. Depending on the size of your spreadsheet you could run into problems.
The developers already took care of this by offering a caching mechanism through a CachedObjectStorageFactory. The manual states:
Cell caching provides a mechanism that allows PHPExcel to maintain the cell objects in a smaller size of memory, on disk, or in APC, memcache or Wincache, rather than in PHP memory. This allows you to reduce the memory usage for large workbooks, although at a cost of speed to access cell data.
Future
PHPExcel is not able to create charts – yet. It is on the roadmap already, a lot of work has been done already. This will probably be in one of the next releases.
Conclusion
PHPExcel comes up with a bunch of useful methods that really fits the needs of a developer. The documentation is well structured and the many test files enables you to quickly find out how the class can be used. Additionally the files are documented using PHPDocBlocks; so IDEs like ZendStudio or NetBeans will show tooltips.
In summary: PHPExcel definitely is worth a look because it can handle many formats.
Schreibe einen Kommentar