Read and Write Excel Data With Php

Author: Ashraf Gheith Viewers: 2,443 Concluding month viewers: 135 Categories: PHP Tutorials

Microsoft Excel is the most popular spreadsheet program in the world. This is why many Spider web applications demand to provide means to consign or import files in Microsoft format.

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

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

Contents

Read and Write Microsoft Excel Spreadsheet Files

Introduction to MS Excel XLS files

PHP back up for Stream Treatment

Read Data from a XLS Stream

Conclusion

Read and Write Microsoft Excel Spreadsheet Files

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

One of the most popular ones is Microsoft Excel. Information technology used the XLS binary file format as a default one until 2007 version. Fifty-fifty though it was eight years agone when Microsoft moved to XML based technology, a lot of people still apply the old version. So this will show y'all how to benefit from PHP's power of reading xls stream.

This article is split in 2 parts. The offset part will talk a little bit virtually the theory and then evidence how to read and download information 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 past Microsoft for employ with Microsoft Excel. XLS stands for eXceL Spreadsheet and has a Binary File Format structure, so information technology is very hard to read and edit like manifestly texts equally XML or text files. XLS files tin exist read past Microsoft Excel, Microsoft Excel Viewer and OpenOffice.

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

PHP support for Stream Handling

As information technology is stated in PHP.internet website: "Streams were introduced with PHP iv.3.0 as a way of generalizing file, network, data compression, and other operations which share a common ready of functions and uses.". That opened a new manner to manipulate with data files.

A stream is referenced as: scheme://target, for instance (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 instance "xlsfile://path-to-file".

Let'due south now take a look how to employ PHP to read and write XLS files.

Read Information from a XLS Stream

Offset you will need to download Ignatius Teo's grade. 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 K YH:i:s") . " GMT");  header ("Enshroud-Command: no-cache, must-revalidate");  header ("Pragma: no-cache");  header ("Content-type: awarding/x-msexcel");  header ("Content-Disposition: zipper; filename=\"" . basename($export_file) . "\"" );  header ("Content-Clarification: PHP/INTERBASE Generated Data" );  readfile($export_file);  go out; ?>            

Let me explain what we accept done. Outset we downloaded the class excel.php and put it in the aforementioned folder where example.php is. We likewise need an xls file. I have named it every bit example.xls. It is a elementary spreadsheet file I have created with OpenOffice.

First we include the course and define the consign file schema and path. Next we send a raw HTTP header, that will assist us download the file. The HTTP header contains expiration date set to the past to prevent caching, a last modification engagement as current time and date and no cache directives. We exercise non 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 this part of the commodity we learned how read and serve for download Excel files from PHP using the XLS stream handler.

In the next part I volition get more than 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 equally Microsoft Excel file.

If yous liked this article and then far or you have questions about the XLS stream handler class, mail a comment.

You demand to exist a registered user or login to post a comment

1,590,265 PHP developers registered to the PHP Classes site.
Be One of U.s.a.!

Login Immediately with your business relationship on:

	Facebook		Gmail
	Hotmail		StackOverflow
	GitHub		Yahoo

Comments:

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

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

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

2. Keen ! - Ariel Rivera (2015-10-25 19:31)
Simple and cracking.... - 1 reply
Read the whole annotate and replies

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

andersonfropeorcee.blogspot.com

Source: https://www.phpclasses.org/blog/post/322-Read-and-Write-Microsoft-Excel-Files-in-PHP-Part-1-Reading-XLS-files-for-Download.html

Share this post