Using Spreadsheet Excel Writer with examples

Developing software systems in PHP, we have in our hands a powerful tool for presenting information in various formats, the main of which is HTML.
Sometimes the functionality of pure HTML may not be enough to implement the idea of ​​the developer. In such cases, PHP can provide information in almost any other popular formats: as images using the GD library, in PDF, XML formats, and even as a SWF Flash movie generated on demand or simply (and angrily) as a plain text file. (text / plain).
And in some cases it is useful to teach our application to save information in a rather exotic format for the World Wide Web - in Excel spreadsheet format.
This is what we will do.

Introduction

To generate tables, use Spreadsheet_Excel_Writer. I began my acquaintance with this library with the article “Creating Excel Tables with PHP Tools” . This is a handy article for the first acquaintance, but for further use of this library information from it may simply not be enough. Also, the author has not paid due attention to many points: for example, localization settings. Therefore, my first tables contained translized rows. Later, studying the discussions on forums and documentation in English, I gathered useful information and decided to structure it in the form of this article.

Brief Library Description

Translation of the description from the module page:
Spreadsheet_Excel_Writer appeared as a Spreadsheet :: WriteExcel port with Perl for PHP. Allows you to generate Excel spreadsheets without the need to connect COM-objects. Supports formulas, BMP images and all kinds of text and cell formatting.
')
Environment: Operating system with PHP 5 and PEAR . In my archive, the PEAR build is used to work offline.

Dependencies: PEAR and OLE package (Package for reading and writing OLE containers) .

Version and Release Date: 0.9.1 (beta) was released on 2006-09-26 </ h
This tells us that the library, although not completely finished, but almost all the necessary functions are present, so no one is going to write further. On the other hand, the library code was settled and almost did not change, which made it possible to identify many errors (which, however, are not corrected :( ). The latest version is sharpened to work with Excel 8.0 (Office '98). What does that give us? Of the positive, the ability to open the generated spreadsheet file in almost any modern spreadsheet program (MS Office 2003, MS Office XP, MS Office 2007, OpenOffice from first to third version). At the same time, our tables will look almost the same.
In addition, this library differs in a good way in that it does not limit us in terms of localization (you can use unicode, but you need to configure it, for more details, see below).

License: LGPL . Class code can be used in commercial projects as a library ( from the wiki : GNU LGPL allows linking with this library or program program under any license incompatible with the GNU GPL, provided that such a program is not derived from the object distributed under (L) GPL, except by binding .

Using

So let's get started.
You need to download, install, configure PEAR and the necessary packages. Or just use it from the archive.
Next, we proceed to programming.

Attention: If you are going to immediately output data to the user, and not save it to disk, then your script should not output any extraneous data through the standard echo and print output functions, otherwise it will destroy the structure of the binary table file!

Examples

Example 0

This is the simplest example. Let's try to simply output the data from the two-dimensional array. The description is divided into steps. Putting them together, you get the desired spreadsheet.
Step 1. Connect the library:
<?php
// PEAR
$path = realpath(dirname(__FILE__).'/pear/');
//
set_include_path(dirname(__FILE__). PATH_SEPARATOR . $path);
require_once('PEAR.php');

// PEAR::Spreadsheet_Excel_Writer
require_once "Spreadsheet/Excel/Writer.php";
?>

Step 2. Initialize the object
<?php
//
$xls =& new Spreadsheet_Excel_Writer();

// Excel
$xls->setVersion(8);

// HTTP xx x
$xls->send('excel_'.date("H_i_s").'.xls'); // excel_17_16_18.xls
?>

Step 3. Create a sheet with the name " info " in the $ sheet variable
<?php
// , x
$sheet = $xls->addWorksheet('info'); //
?>

Step 4. Set the encoding of the UTF-8 sheet
<?php
$sheet->setInputEncoding('UTF-8');
?>

Step 5. Our data: $ table two-dimensional array
<?php
$table = array(
'Header 1' => array(1, 2, 3),
'Header 2' => array(4, 5, 6),
'Header 3' => array(7, 8, 9)
);
?>

Step 6. Fill the table with data from the $ table array. Use the cycle, initialize the variables $ cell and $ row, where
$ cell - column
$ row - row.
Add data to the sheet. We run the array, write the data on the column using functions:
$ sheet-> write ($ row, $ cell, $ header); - writes data to the cell ($ row; $ cell) $ header
$ sheet-> writeCol ($ row, $ cell, $ row_data); - writes the $ row_data column to the cells, starting with ($ row; $ cell).

<?php
$cell = 0;
foreach ($table AS $header => $row_data){
$row = 0;
$sheet->write($row, $cell, $header);
$row++;
$sheet->writeCol ($row, $cell, $row_data);

$cell++;
}?>

Step 7. Finish the work of the library, send data
<?php
$xls->close();
?>

Complicate the formatting and methods of displaying information, with the help of other examples.

Example 1

In this example, we introduce the concept of format. A format is an object that describes the appearance of a cell or group of cells.
<?php
//
$textFormat =& $xls->addFormat();
//
$textFormat->setSize(10);
//
$textFormat->setColor('black');
$textFormat->setAlign('center');
$textFormat->setBorder(1);
?>
The methods speak for themselves.
This style can be applied when creating a cell:
<?php
$sheet->write($row, $cell, $data, $textFormat); //
?>

Example 2

We solve one of the problems of the library with crutches. How? In this case, we define the width of the column in the loop. We obtain an array with a maximum row length in the column cells and set the column width manually using the $ sheet-> setColumn function ($ cell, $ cell, $ celLenght), which sets the width of $ cell columns to $ cell width $ celLenght (in pixels? ).

Example 3

Output multiplication tables in Excel spreadsheets. Wordplay;)

Example 4

Using Excel formulas on a sheet. It’s pretty pointless when we have the power of PHP, but sometimes it’s useful.

Example 5

Convenient script that displays a table of colors that can be used to create style formats.

Archive with examples

Files in the archive (zip format, size ~ 200kb, UTF-8 text file encoding):
excel.php - a simple sheet (example 0) ;
excel_1.php - sheet with formatting (example 1) ;
excel_2.php - sheet with manual determination of the width of the column (Example 2);
excel_3.php - multiplication table;) (example 3)
excel_4.php - sheet with formulas (example 4) ;
excel_5.php - sheet with flowers (example 5) ;
daddy pear with library.
daddy of examples with examples xls-files (for your reference).

Problems

Localization:
solved by functions
$xls->setVersion(8); // Excel, - ,
$sheet->setInputEncoding('UTF-8'); // -
Now you can write data on “clean” UTF-8, and Excel will understand you;)
Mistake:
Class creators made a mistake. But it occurs only if you write on UTF-8 in Excel version 8 and write a lot (> 200 lines, several sheets). In the archive, a patch was found on the file, found on the bugtracker , which solves the problem.

Conclusion

Let's sum up our work. We got some basic spreadsheet skills with PHP. Studying examples from the archive and on the developer’s website you can improve these skills.
By the end I will write what I had to write at the beginning. So, the pros and cons of using the Spreadsheet_Excel_Writer library in their projects.
Pros:
Platform Independence
+ Ability to enter formulas (from the English version)
+ Normally opens in OpenOffice.org CALC
+ You can add pictures via void Worksheet :: insertBitmap

Minuses:
- Manual determination of the width of the column (if someone finds elegant solutions - I will be sincerely grateful)
- You can not add graphics (especially not necessary)

Also recommended for familiarization:
Article “Creating Excel spreadsheets using PHP” (rus)
Pear website (eng)
list of functions of class Excel Writer (eng)
Excel function list