2 Techniques
Access the raw Excel Binary File
Use the Excel API
Both techniques have their advantages and disadvantages as you will see during this presentation
Access the raw Excel Binary File – 1
There is a php library written that can access a raw Excel binary file. Benefits include that it is easy and straight forward to use, has plenty of documentation and examples, and only has one include file of approximately 800 lines. No need for special php compile flags etc, and can be used straight from the download.
Access the raw Excel Binary File – 2
sample code
<?php
require_once ‘Excel/reader.php‘;
// ExcelFile($filename, $encoding);
$data = new Spreadsheet_Excel_Reader();
// Set output Encoding.
$data->setOutputEncoding(‘CP1251′);
$data->read(‘filename.xls’);
//display cell in row 3, column 4 (D)
echo $data->sheets[0]['cells'][3][4];
?>
Access the raw Excel Binary File – 3
The $data->sheets[0]['cells'] array has other elements that can be used instead of cells (otherwise it wouldn’t be an array). You can also use numRows or numCols which returns the number or rows or columns respectivily. cellsinfo contains an array itself in which you can retrieve cell information i.e. background color, formating, cell type (number, character etc), column or rowspan etc…
Access the raw Excel Binary File – 4
Important The Excel Reader library can be used on any platform, not just Windows. Some aspects DO NOT work, e.g. Lookup Tables, and there maybe more. You will need to be comfortable with arrays, and understand the alphabet sequence, i.e. D => 4.
Links
php excel reader
php excel writer
There is a link at openoffice.org for the excel file specification, but I forgot to put that link in. Sorry.
Use the Excel API – 1
Microsoft have provided an API to access it’s popular products such as Word, Excel, PowerPoint etc. The language commonly refered to is VBA, and this can be accessed via a COM object. Benefits: The major benefit is that it is provided by the product vendor! Another aspect is that it is fairly intuitive, and the techniques can be ported to other Microsoft products
Use the Excel API – 2
Sample Code
<?php
$excel_app = new COM(“Excel.application”) or Die (“Did not connect”);
$Workbook = $excel_app->Workbooks->Open(‘filename’) or Die(‘Did not open filename’);
$Worksheet = $Workbook->Worksheets(‘sheet1′);
$Worksheet->activate;
$excel_cell = $Worksheet->Range(‘D3′);
$excel_cell->activate;
echo $excel_cell->value;
…
Use the Excel API – 3
Sample Code – closing off
$Workbook->Save();
$Workbook->Saved = true;
$Workbook->Close;
unset($Worksheet);
unset($Workbook);
$excel_app->Workbooks->Close();
$excel_app->Quit();
unset($excel_app);
?>
Use the Excel API – 4
There are plenty of API functions available and the documentation is pretty crappy, but with good examples and notes. Anything that you can do in Excel can also be done via the API. This includes lookup tables, formulas, protected sheets etc…
You will also notice from the example code that you can refer to cell coordinates the same way that you view them in Excel, i.e. D3.
Links
VBA reference – Excel
Conclusion – 1
Two methods of accessing an Excel file have been presented tonight. One method was directly accesssing the raw binary, the other was via the API. Accessing the raw binary is a bit of a hack. Microsoft do not provide the file format, but instead you must rely on a third party to provide the information.
Classes that have been written work for simple cases, however more complex situations can not be resolved. The benefit however is that this method can be used on any platform and does not require Excel to be installed.
Conclusion – 2
Using the Excel API is by far the preferred technique. The API was written by the product vendors, and anything you can do in Excel can be replicated with the API. The Microsoft documentation is pretty crappy, but I’m sure there are good books in the market that can fill the gap. Understanding the object model means that the techniques can be ported to other office applications.
Using VBA does require Excel to be installed on the server, and if my knowledge is correct this also means you must have a Windows Server.
