PHP Classes

Read and Write Microsoft Excel Files in PHP Part 2: Writing XLS files

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 (7)   Trackbacks (0)  

Author:

Viewers: 512

Last month viewers: 46

Categories: PHP Tutorials

The first part of this article talked about reading an XLS file and downloading it using PHP a XLS stream handler.

Read this article to learn how to do the opposite, writing an Excel file using Ignatius Teo's XLS stream handler class to write spreadsheet rows as arrays to files.




Loaded Article

Contents

Introduction

Creating a Data Array

Open the XLS file

Conclusion


Introduction

In the first part of the article I talked about reading an XLS file and downloading it. In this part of the article it is shown in more detail how to write to an XLS file on the server side.

Creating a Data Array

To add data to an XLS file we will need an Array. XLS files contain actual representations of tables, so they also can be represented as a two dimensional arrays with rows and columns. For that the data we will pass in must be also a two dimensional array.

$assoc = array(
 array(
  "Sales Person" => "Sam Jackson",
  "Q1" => "$3255",
  "Q2" => "$3167",
  "Q3" => 3245,
  "Q4" => 3943
 ),
 array(
  "Sales Person" => "Jim Brown",
  "Q1" => "$2580",
  "Q2" => "$2677",
  "Q3" => 3225,
  "Q4" => 3410
 ),
 array(
  "Sales Person" => "John Hancock",
  "Q1" => "$9367",
  "Q2" => "$9875",
  "Q3" => 9544,
  "Q4" => 10255
 ),
);

As you can see, our array is constructed from rows. Every row has column values, in our case "Sales Person", "Q1", "Q2", "Q3" and "Q4".

Open the XLS file

Our data array can also be constructed from database query results or reading a list of files for instance. After creating the data array we need to open our XLS for reading and writing.

require_once "excel.php";

$export_file = "xlsfile://path-to-the-file/example.xls";
$fp = fopen($export_file, "wb");
if (!is_resource($fp))
{
 die("Cannot open $export_file");
}

First, as in the previous article, we need to download and instal the Ignatius Teo's MS-Excel Stream Handler class. Now create a file named "example2.php" and paste the code above in it.

We include the class which sets up the xlsfile:// stream handler for us. We define the target file then open it with fopen with permissions for writing in binary mode.

fwrite($fp, serialize($assoc));
fclose($fp);

Finally we pass a serialized array with fwrite and close the file. With that we have written our data to our XLS file. Now we can use the code presented in the first part of the article to serve the file for download.

Conclusion

Ignatius Teo XLS stream handler class made reading and writing Microsoft Excel stream handler class a very easy task.

In the first part of the article we learned how read and serve for download Excel files from PHP using the XLS stream handler. In this part of the article also we learned how to write data to that file. Next we could create a script to upload an empty XLS file, then fill it with data and download it again.

If you liked this article 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:

4. Excel to PHP - Ian Onvlee (2015-10-28 21:23)
Excel to PHP... - 1 reply
Read the whole comment and replies

3. it seems it does not work......problem - OSWALDO OLEA (2015-10-28 21:23)
it does not open the xls file creted in open office... - 1 reply
Read the whole comment and replies

2. have a look - lack (2015-10-28 21:22)
good... - 1 reply
Read the whole comment and replies

1. PHPExcel - dGo (2015-10-26 18:35)
I can only recommend PHPExcel... - 0 replies
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 (7)   Trackbacks (0)