Contents

Read and Write Microsoft Excel Spreadsheet Files

Introduction to MS Excel XLS files

PHP support for Stream Handling

Read Data from a XLS Stream

Conclusion

Read and Write Microsoft Excel Spreadsheet Files

Spreadsheets are an interactive computer application programs for organization, analysis and storage of data in tabular form.

One of the most popular ones is Microsoft Excel. It used the XLS binary file format as a default one until 2007 version. Even though it was 8 years ago when Microsoft moved to XML based technology, a lot of people still use the old version. So this will show you how to benefit from PHP's power of reading xls stream.

This article is split in two parts. The first part will talk a little bit about the theory and then show how to read and download data from XLS. The second part will show how to write data to an XLS file on the server.

Introduction to MS Excel XLS files

XLS is a file extension for a spreadsheet file format created by Microsoft for use with Microsoft Excel. XLS stands for eXceL Spreadsheet and has a Binary File Format structure, so it is very hard to read and edit like plain texts as XML or text files. XLS files can be read by Microsoft Excel, Microsoft Excel Viewer and OpenOffice.

Binary Files like XLS file format are stored in a simple stream file. That is why we will use PHP stream capabilities to read and write XLS's.

PHP support for Stream Handling

As it is stated in PHP.net website: "Streams were introduced with PHP 4.3.0 as a way of generalizing file, network, data compression, and other operations which share a common set of functions and uses.". That opened a new way to manipulate with data files.

A stream is referenced as: scheme://target, for example (file://path-to-file, http://path-to-url, https://path-to-url, ftp://path-to-server, ftps://path-to-server). And of course what we will use for this example "xlsfile://path-to-file".

Let's now take a look how to use PHP to read and write XLS files.

Read Data from a XLS Stream

First you will need to download Ignatius Teo's class. Now create a file named example.php and paste this code in it:

<?php
 require_once "excel.php";
 $export_file = "xlsfile://example.xls";
 header ("Expires: Mon, 26 Jul 1997 05:00:00 GMT");
 header ("Last-Modified: " . gmdate("D,d M YH:i:s") . " GMT");
 header ("Cache-Control: no-cache, must-revalidate");
 header ("Pragma: no-cache");
 header ("Content-type: application/x-msexcel");
 header ("Content-Disposition: attachment; filename=\"" . basename($export_file) . "\"" );
 header ("Content-Description: PHP/INTERBASE Generated Data" );
 readfile($export_file);
 exit;
?>

Let me explain what we have done. First we downloaded the class excel.php and put it in the same folder where example.php is. We also need an xls file. I have named it as example.xls. It is a simple spreadsheet file I have created with OpenOffice.

First we include the class and define the export file schema and path. Next we send a raw HTTP header, that will help us download the file. The HTTP header contains expiration date set to the past to prevent caching, a last modification date as current time and date and no cache directives. We do not want browsers to cache our file we are reading.

To guide the browser how to open or download the file we include the Content-type, we provide the file name in Content-Disposition and the Content-Description. And Finally we read the file in buffer with readfile function.

Conclusion

In the next part I will go more detailed on how to write to an XLS file. I will create an array with sample data, and then write it into the stream. The resulting file will be then downloaded as Microsoft Excel file.

If you liked this article so far or you have questions about the XLS stream handler class, post a comment.