Have you ever faced a situation when you need to manipulate Excel spreadsheets with PHP on the server that is running Linux? If you had a Windows Server you could use PHP COM extensions. However they are unavailable on Linux.

Hopefully, there is a solution. It is called Open XML. It’s a new format of Microsoft Office documents introduced in Microsoft Office 2007. Basically, an Open XML file is a ZIP archive that contains XML files that represent the document mark-up. You can view it yourself or read some documentation, my article isn’t about Open XML, but about the PHP library for working with Excel 2007 files. It also supports Excel 97 format by incor porating a PEAR library.

The library is called PHPExcel. It allows you to read/write Excel spreadsheets, save them in many formats including PDF and HTML. It supports formulas,styles and etc.

It requires:

• PHP 5.2+
• GD extension
• XML extension
• ZIP extension

I have these two simple examples that show you how to create an Excel spreadsheet and save it in several formats and how to read a spreadsheet and display it on the HTML page.

Writing

<?php

//Here we set the include path and load the librarires
set_include_path(get_include_path() . PATH_SEPARATOR . '../PhpExcel2007/Classes/');
require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

$excel = new PHPExcel();
$excel->setActiveSheetIndex(0); //we are selecting a worksheet
$excel->getActiveSheet()->setTitle('Products'); //renaming it

//here we fill in the header row
$excel->getActiveSheet()->setCellValue('A1', 'Title');
$excel->getActiveSheet()->setCellValue('B1', 'Price');
$excel->getActiveSheet()->setCellValue('C1', 'Quanity');
$excel->getActiveSheet()->setCellValue('D1', 'Total price');

//here we put some values
$excel->getActiveSheet()->setCellValue('A2', 'Fictional TV set');
$excel->getActiveSheet()->setCellValue('B2', 300);
$excel->getActiveSheet()->setCellValue('C2', 1500);
$excel->getActiveSheet()->setCellValue('D2', '=B2*C2'); //this is how we put formulas, just like using Excel

$excel->getActiveSheet()->setCellValue('A3', 'Fictional mobile phone');
$excel->getActiveSheet()->setCellValue('B3', 200);
$excel->getActiveSheet()->setCellValue('C3', 5000);
$excel->getActiveSheet()->setCellValue('D3', '=B3*C3');

$excel->getActiveSheet()->setCellValue('A4', 'Fictional laptop');
$excel->getActiveSheet()->setCellValue('B4', 1000);
$excel->getActiveSheet()->setCellValue('C4', 2000);
$excel->getActiveSheet()->setCellValue('D4', '=B4*C4');

//some summarizing formulas
$excel->getActiveSheet()->setCellValue('C5', '=SUM(C2:C4)');
$excel->getActiveSheet()->setCellValue('D5', '=SUM(D2:D4)');

//Now we save the created document in the Exce 2007 format
$excelWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel2007');
$excelWriter->save('Products.xlsx');

//in PDF
$pdfWriter = PHPExcel_IOFactory::createWriter($excel, 'PDF');
$pdfWriter->save('Products.pdf');

//in HTML
$htmlWriter = PHPExcel_IOFactory::createWriter($excel, 'HTML');
$htmlWriter->save('Products.html');

//and in the old binary format
$excelBinaryWriter = PHPExcel_IOFactory::createWriter($excel, 'Excel5');
$excelBinaryWriter->save('Products.xls');

?>

Reading:

<?php

//Here we set the include path and load the librarires
set_include_path(get_include_path() . PATH_SEPARATOR . '../PhpExcel2007/Classes/');
require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');

$excelReader = PHPExcel_IOFactory::createReader('Excel2007'); //we instantiate a reader object
$excel = $excelReader->load('Products.xlsx'); //and load the document

print('<table border="1">');
for ($i = 2; $i < 5; $i++) {
    print('<tr>');

    print('<td>');
    print($excel->getActiveSheet()->getCell('A' . $i)->getValue()); //this is how we get a simple value
    print('</td>');

    print('<td>');
    print($excel->getActiveSheet()->getCell('B' . $i)->getValue());
    print('</td>');

    print('<td>');
    print($excel->getActiveSheet()->getCell('C' . $i)->getValue());
    print('</td>');

    print('<td>');
    print($excel->getActiveSheet()->getCell('D' . $i)->getCalculatedValue()); //this is how we get a calculated value
    print('</td>');

    print('</tr>');
}

print('<tr><td>&nbsp;</td><td>&nbsp;</td>');
print('<td>' . $excel->getActiveSheet()->getCell('C5')->getCalculatedValue() . '</td>');
print('<td>' . $excel->getActiveSheet()->getCell('D5')->getCalculatedValue() . '</td></tr>');
print('</table>');
?>

Conclusion

PHPExcel is a very poweful library which is easy and well documentated. Of course you can use it not only on Linux, but on any operating system, including Windows.