Mike Borozdin's Blog

A blog about programming, web and IT in general

Follow Me

Search

Disclaimer

The opinions expressed herein are my own personal opinions and do not represent my employer's view in anyway.

© 2014 Mike Borozdin

PHPExcel: Manipulate Excel Spreadsheets with PHP on Linux

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.

Kick it! Shout it

Tags:
Posted on Monday, June 30, 2008
Comments (0)
blog comments powered by Disqus