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

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

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.