PHP Classes

Read and Write Microsoft Excel Files in PHP Part 1: Reading XLS files for Download

Recommend this page to a friend!
  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Read and Write Micros...   Post a comment Post a comment   See comments See comments (10)   Trackbacks (0)  

Author:

Viewers: 2,443

Last month viewers: 135

Categories: PHP Tutorials

Microsoft Excel is the most popular spreadsheet program in the world. This is why many Web applications need to provide means to export or import files in Microsoft format.

In general reading and writing Excel files is not a trivial task. However Ignatius Teo had a brilliant idea of using PHP streams to read and write Excel files just and simple and reading and writing arrays of data.

Read this article to learn how to use the MS-Excel Stream Handler class to read and write Excel files from the xls:// stream.




Loaded Article

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.

Spreadsheet example image

Conclusion

In this part of the article we learned how read and serve for download Excel files from PHP using the XLS stream handler.

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.




You need to be a registered user or login to post a comment

1,611,040 PHP developers registered to the PHP Classes site.
Be One of Us!

Login Immediately with your account on:



Comments:

5. php and excel - Ian Onvlee (2015-10-25 19:31)
php and excel part 1... - 1 reply
Read the whole comment and replies

4. Embed image - tim mason (2015-10-25 19:31)
How would i embed an image from a URL... - 1 reply
Read the whole comment and replies

3. Multiple worksheet handling - sathish kannan (2015-10-25 19:31)
Writing HTML tables into Multiple excel worksheet... - 1 reply
Read the whole comment and replies

2. Great ! - Ariel Rivera (2015-10-25 19:31)
Simple and neat.... - 1 reply
Read the whole comment and replies

1. Can we get data into PHP array? - Adwait Pande (2015-10-25 19:30)
Need code to get excel data into PHP array... - 1 reply
Read the whole comment and replies



  Blog PHP Classes blog   RSS 1.0 feed RSS 2.0 feed   Blog Read and Write Micros...   Post a comment Post a comment   See comments See comments (10)   Trackbacks (0)