Creation of Excel-tables by means of PHP

In this article following questions will be discussed:

• Introduction into PEAR::Spreadsheet_Excel_Writer
• Go on working: introduction into API
• Adding cells’ formatting
• Adding of function Excel: C1+D1=2!

You are supposed to have elementary knowledge of Excel but nothing too serious. You even don’t have to possess a copy as far as all the examples shown work perfectly with OpenOffice Calc as well.

Introduction into PEAR::Spreadsheet_Excel_Writer

Excel is used by everyone dealing with finances and money. In other words Accountant department which hasn’t paid your amount in time uses it. Make life of accountants easier and they’ll reply you in a similar way.

Wouldn’t it be better if you could offer your customers ability for access to the downloaded data presented as Excel pages? The good news is that you can do it by means of PEAR::Spreadsheet_Excel_Writer.

You’ll say it’s impossible. “Excel uses file format Microsoft. It’s impossible to do that!”

Yes, it’s possible. Spreadsheet_Excel_Writer generates ‘real things’ with Excel functions, formatting and all the rest. No, here we’re not speaking about files separated with commas or using COM-extensions (or any other extensions). It is written by means of simple PHP and will work under Unix server as well as it works on Windows-servers. In a few words, PEAR::Spreadsheet_Excel_Writer along with optional abilities of PEAR::OLE enables ‘understanding’ of Microsoft Excel format.

Now armed with advanced knowledge of PEAR-parcel which you should have installed we start loading libraries. Open your command string and enter following:

$ pear install OLE 
$ pear install Spreadsheet_Excel_Writer


That’s all. We are ready!

It’s important to notice: for all the examples in this article I’ve used PEAR::OLE version and PEAR::Spreadsheet_Excel_Writer version 0.7. I warn you that something may be changed in the next versions.

We’ll go proceed with our way. Let’s create a simple data list.

File name: example_1.php

<?php 
// Introduction of PEAR::Spreadsheet_Excel_Writer 
require_once "Spreadsheet/Excel/Writer.php"; 

// Creation of spreadsheet 
$xls =& new Spreadsheet_Excel_Writer(); 

// Sending HTTP-headings for informing a reporter about type of the input
//data 
$xls->send("test.xls"); 

// Adding spreadsheet to a file, returning object for data adding 
$sheet =& $xls->addWorksheet('Binary Count'); 

// We write some numbers 
for ( $i=0;$i<11;$i++ ) { 
 // Usage of function PHP decbin() for conversion of a whole number into
//binary data 
 $sheet->write($i,0,decbin($i)); 
} 
// The end of a sheet, sending to a viewer
$xls->close(); 
?> 


Open script within your viewer (he is supposed to ‘be familiar’ with Excel or OpenOffice Calc) and it will display an Excel-sheet with numbers from 0 to 10 in the binary form.

Saving files

In this case a sheet will be created dynamically – nothing is saved on server. If you want to create a file instead of this, you may throw away the part of creation of the sheet that hasn’t changed by simple sending to constructor name of a file and path to it evading the necessity of sending HTTP-headings:

File name: example_2.php

<?php 
// Was a sheet created? 
if ( !file_exists('sheets/binary.xls') ) { 

    // Introduction of PEAR::Spreadsheet_Excel_Writer 
    require_once "Spreadsheet/Excel/Writer.php"; 
     
    // Creation of a spreadsheet, sending file name for creation
    $xls =& new Spreadsheet_Excel_Writer('sheets/binary.xls'); 
     
    //Adding a sheet to file, returning object for data adding
    $sheet =& $xls->addWorksheet('Binary Count'); 
     
    // We write some numbers
    for ( $i=0;$i<11;$i++ ) { 
    
// Usage of PHP-function decbin() for conversion of a whole number into 
//binary data
 $sheet->write($i,0,decbin($i)); 
    } 
     
    // The end of a sheet, sending to a viewer
    $xls->close(); 
} 
?> 


If you use system of the Unix-family, don’t forget about changing the access to folder in which you save data sheets in order to make PHP help you to add data into them.

API review

Well, we’ve examined the basic principles. To get from PEAR::Spreadsheet_Excel_Writer as much as you can, you should know more about API. API-documentation which is available on the PEAR-site is out-of-date at the moment (the site has been enlarged since the version of documentation was created). Thanks to the authors who have added a lot of documentation directly into the code you can create API-documentation of your own if you download phpDocumentor and direct it to the folder which contains the entire source codes Spreadsheet_Excel_Writer.

The basic class from which you are always to start your work is Spreadsheet_Excel_Writer which represents an access item to all the rest of the classes within a library. It provides two important factory methods (which are defined within the parent class Spreadsheet_Excel_Writer_Workbook:)

• addWorksheet() returns occurrence Spreadsheet_Excel_Writer_Worksheet. The most of the work is done in the occurrence of this class offering you ability to entry data into the cells on one sheet.
• addFormat() returns occurrence Spreadsheet_Excel_Writer_Format which is used for adding of cells visual formatting.

The library also contains three other classes which you should avoid although you are unlikely to need their usage.

• Spreadsheet_Excel_Writer_Validator enables adding validating rules for cells. There is no documentation for this class at the moment. It is something like an experimental code and thus we are not going to discuss it here. Mostly it offers ability for validation of data which have been put into a cell by a final user. The more complicated rules of validation may be set up by means of class extension. Class Spreadsheet_Excel_Writer_Workbook presents method addValidator() for creation of validation case at the time when Spreadsheet_Excel_Writer_Worksheet enables validation rules to be set up into the cells by means of method setValidation()
• Spreadsheet_Excel_Writer_Parser which is Parser for Excel data sheets helps you to check if a function is correct Excel-syntax.
• And finally Spreadsheet_Excel_Writer_BIFFwriter is used for creation of Binary Files Format for saving Excel-files. If you are interested in Excel-breaking, it will be interesting for you to study its actions and if not, you shouldn’t worry about it as far as the library hides this class completely.

Confusion of the zero indexes

One of the notice methods is Spreadsheet_Excel_Writer_Worksheet::write() which we saw in the foregoing example and you are going to use it many times for adding data into cells. This method is a bit confusing in comparison to the same method in Excel.

The first argument of function write() is the number of string. The number of the first string in PEAR::Spreadsheet_Excel_Writer- table is 0instead of 1 as it is usual in Excel.

The second argument is the number of a column. Now columns in Excel are identified with letters not with numbers and so you have to get accustomed to interpretation between the two. Letter ‘F’ is the 6th in the alphabet so the second argument… 5 (of course!) is the very left column 0 (zero) in PEAR::Spreadsheet_Excel_Writer and so you’ll have to subtract to get the number of a column.

The third argument of the function write() are the data which have to be inserted into cells; there is also the fourth element which is compulsory and used for visual formatting of cells.

There are many other methods in class Spreadsheet_Excel_Writer_Worksheet such as ‘freezing’ and ‘melting’ of parts of a sheet used for formatting the entire sheet for printing and so on. I’ll tell about them in the following example but most of them you are to examine on your own.

Adding of cells formatting

And what about nicer sheets? We can achieve this by means of PEAR::Spreadsheet_Excel_Writer using function addFormat() for conversion of an object into Spreadsheet_Excel_Writer_Format. We apply formatting to this object using methods which it provides and then transmit it with method write() to the function Spreadsheet_Excel_Writer_Worksheet to add formatting to the function which we added.

For the sake of example of the ‘Real World’ let’s imagine that I want to give my clients of the Internet-shop phpPetstore.com possibility to download the check for the things they bought in the form of Workbook containing one Worksheet.

I begin my sheet with the ordinary stuff.

<?php 
require_once "Spreadsheet/Excel/Writer.php"; 

// creation of a workbook 
$xls =& new Spreadsheet_Excel_Writer(); 

// creation of a Worksheet 
$cart =& $xls->addWorksheet('phpPetstore');


Next we’ll add a heading to the sheet joining some cells for its placement. Here we’ll get the thirst idea of how formatting is done:


 // some text as a heading of a sheet 
$titleText = 'phpPetstore: Receipt from ' . date('dS M Y'); 
// Creation of an object for formatting 
$titleFormat =& $xls->addFormat(); 
// Setting a font - Helvetica works with OpenOffice as well as calc 
$titleFormat->setFontFamily('Helvetica'); 
// Setting text in bold 
$titleFormat->setBold(); 
// Setting text size 
$titleFormat->setSize('13'); 
// Setting text color 
$titleFormat->setColor('navy'); 
// Setting bottom limit width into "thick" 
$titleFormat->setBottom(2); 
// Setting bottom limit color 
$titleFormat->setBottomColor('navy'); 
// Setting alignment into a special value 
$titleFormat->setAlign('merge'); 
// Adding heading into the upper left cell of a sheet  
// by sending it a heading string and the object of formatting as well 
$cart->write(0,0,$titleText,$titleFormat); 
// Adding of three empty cells for joining  
$cart->write(0,1,'',$titleFormat); 
$cart->write(0,2,'',$titleFormat); 
$cart->write(0,3,'',$titleFormat); 
// High of a string 
$cart->setRow(0,30); 
// Setting of columns’ width for the first four columns 
$cart->setColumn(0,3,15);  



You are to notice that I’ve obtained a formatting object by calling addFormat() by means of the object $xls which represents the current sheet. Then I applied to the object some specific formatting (methods which are called setBold() don’t need explanations – for the more detailed information look up API documentation).

When formatting is ended I call function write() for the object $cart for adding to the cell transmitting the object as the fourth argument.

The only non-standard action which I’ve done here is joining of four cells. Calling setAlign('merge') for the object of formatting (usually you use ‘left’, ‘right’ or ‘center’ for this) I ordered Spreadsheet_Excel_Writer to join the entire cells to which this formatting concerns. That’s why I’ve created three empty cells and applied formatting to them.

Usage of setRow() enables me to change high of a string making it more than setting of Excel-string high on default. This method possesses many optional formatting arguments enabling you, for instance, to apply the object of formatting to the current string. Similar to setColumn() I can set up column’s width and apply to it further formatting. The difference is that setRow() is applied to one string only at the time when setColumn() is applied to many columns.

Now I need data for adding to the sheet. In order to evade complication of example (by adding data base) I’m going to use an indexed massive of associative massives which is likely to be a result of SQL-selection.

 $items = array ( 
 array( 'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1), 
 array( 'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2), 
 array( 'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10), 
);  


“Columns within database” are keys of the massive - 'description', 'price' and 'quantity'; the second thing we should do is to add headings of columns with optional heading ‘Total’ which we are going to use soon:

  // Setting of some formatting 
$colHeadingFormat =& $xls->addFormat(); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setFontFamily('Helvetica'); 
$colHeadingFormat->setBold(); 
$colHeadingFormat->setSize('10'); 
$colHeadingFormat->setAlign('center'); 

// Data massive heading for columns 
$colNames = array('Item','Price($)','Quantity','Total');

// Adding all headings at one call 
// we leave the string empty to have a more pleasant view  
$cart->writeRow(2,0,$colNames,$colHeadingFormat);  


You’ve already seen formatting. But you haven’t seen method writeRow(). This method performs the same as write() but it enables you to add data massive from the left to the right beginning from the different number of a string or column. This method enables considerable reducing of a program code.

Also I’d like to make columns’ headings be always visible when we scroll a page. In Excel it’s can be done by means of ‘freezing’ by selecting a block of cells which will be visible when a user scrolls a sheet enabling him to see columns’ headings (in this case) which explain what these data present. The same is possible in PEAR::Spreadsheet_Excel_Writer:

  // A group of cells fro freezing 
// 1st argument is position of vertical joining 
// 2nd argument is position for horizontal joining (0 = no horizontal joining) 
// 3rd argument is the upper visible row under the vertical joining  
// 4th argument is the left visible column after the horizontal joining 
$freeze = array(3,0,4,0); 

// Freeze these cells! 
$cart->freezePanes($freeze);  


Notice that ‘freezing’ has been applied directly by the object $cart not by means of a formatting object as far as it has been applied to some cells. On the other hand formatting has been applied to the single cells.

And finally I come through the objects in ‘their own bag’ adding data to the sheet:

  // Pseudo data 
$items = array ( 
 array( 'description'=>'Parrot'  ,'price'=>34.0,  'quantity'=>1), 
 array( 'description'=>'Snake'  ,'price'=>16.5,  'quantity'=>2), 
 array( 'description'=>'Mouse'  ,'price'=>1.25,  'quantity'=>10), 
); 

// Use this for tracing of the current row number 
$currentRow = 4; 

// Pass though the data adding them to the list  
foreach ( $items as $item ) { 
  
 // Write each item to the sheet 
 $cart->writeRow($currentRow,0,$item); 
 $currentRow++; 
}  



Generally speaking, that’s all. If you are new to object oriented programming in PHP, it may seem to you a bit scaring at first sight but as you’ve already noticed that all the methods are named clearly and you can realize their purposes by having a look of them. The idea of attraction of one object by the other one may be new to you but when you think about this it you seem to be creating a Worksheet-object by calling method addWorksheetSheet() and to be adding formatting objects to a cell when you write() into Worksheet.

Adding of Excel-function.

Now you are able to create having pleasant view large-formatted tables but as any Excel-professional will say the simple data displaying is far from being useful. Life becomes more interesting when you use Excel-functions for calculation of data and their conversion into something more interesting.

It’s clear that my check should be more rational so I need add some calculations based on the data which I’ve already inserted into the table. I want to display "total item cost" for each row – the data contain unit price and number of the items purchased as well:

   "total item cost" = "unit price" * "number of items purchased"  


Converting into Excel-terms to get the sum of the 5th row you need to write a formula like following:

    [Cell D5] =PRODUCT(B5:C5)  


To achieve this with PEAR::Spreadsheet_Excel_Writer I need only to correct code provides passing through the data:

     // Use this for tracing the current row number 
$currentRow = 4; 

// Excess data adding them to the sheet 
foreach ( $items as $item ) { 
   // Adding each object to the sheet 
$cart->writeRow($currentRow,0,$item); 
  
 // Remember that Excel starts rows counting of rows from #1! 
 $excelRow = $currentRow + 1; 
  
 // Creation of a PHP-string containing formula  
 $formula = '=PRODUCT(B' . $excelRow .':C' . $excelRow .')'; 
  
 // Writing formula to the row 
 $cart->writeFormula($currentRow,3,$formula); 
  
 $currentRow++; 
}  
  


Writing formula isn’t difficult – we only use method writeFormula(). But the main thing is, as I’ve mentioned before, that Excel starts counting rows from 1 at the time when PEAR::Spreadsheet_Excel_Writer starts doing this from zero. This means I have to keep it in the mind when creating a function otherwise I will refer to false cells. That’s why I’ve created variable $excelRow which represents $currentRow + 1. You may have thought that this is design flaw on behalf of the authors but remember that like in many other programming languages in PHP indexed massives begin from the zero index.

Now mine displays amounts for each row. But what about summing up all amounts in order to let a customer know about the amount which is going to be on cheque of their credit card? For this it’s enough to sum up all amounts and display the result in some cell.

Using Excel-terms, I need to use function SUM() for summing up all the amounts which are displayed in the column D.

    [Grand Total Cell] =SUM(D5:D7)  


For placing this into a sheet after passage through the data is over I add following:

 // The 1st string as Excel understands it, - $currentRow has been 4 in the beginning
$startingExcelRow = 5; 

// The last string is like in Excel 
// (which is the same as currentRow after the passage is over) 
$finalExcelRow = $currentRow; 

// Excel-format for getting sum of all the meanings 
$gTFormula = '=SUM(D'.$startingExcelRow.':D'.$finalExcelRow.')'; 

// Some additional formatting for sum total cells  
$gTFormat =& $xls->addFormat(); 
$gTFormat->setFontFamily('Helvetica'); 
$gTFormat->setBold(); 
$gTFormat->setTop(1); // Top border 
$gTFormat->setBottom(1); // Bottom border 

// Adding some text and formatting 
$cart->write($currentRow,2,'Grand Total:',$gTFormat); 

// Adding of the total sum formula with format 
$cart->writeFormula($currentRow,3,$gTFormula,$gTFormat);  

And finally I’m over with creation of my check sending sheet directly to the viewer.

 // Send the Spreadsheet to the browser 
$xls->send("phpPetstore.xls"); 
$xls->close(); 



That’s all. The sheet is ready for downloading.