Creation of tables Excel on PHP

About what there will be a conversation in this clause{article}:

* Acquaintance with PEAR:: Spreadsheet_Excel_Writer

* We continue job: acquaintance with API

* Dobavlenie formatting of cells

* Addition of function Excel: C1+D1=2!

It is meant, that you have basic knowledge Excel, but anything too serious. It is even not necessary for you to have a copy, all resulted examples work as well and with OpenOffice Calc.

Acquaintance with PEAR:: Spreadsheet_Excel_Writer

Excel - an ohm those who works with the finance and money use. Differently Buxgalterskij the department which has not paid your sum in time, uses her . Make a life buxgaleterov more easy and they will answer you same.

Unless it would not be better, if you could enable your clients of access to the loaded data as sheets Excel? Xoroshie news will be, that you can make it with help PEAR:: Spreadsheet_Excel_Writer.

" It is impossible! " You will say. " Excel uses file format Microsoft. It to make it is impossible! ".

Yes, yes it is possible. Spreadsheet_Excel_Writer generates " real things ", with functions Excel, formatting and all the rest. No, here we do not speak about fajlax divided{shared} by points, or ispol`zujuhhix COM expansion (or ljubie other expansions). He is written by means of simple PHP, and will work under Unix the server as well as well as on Windows servers If to be brief PEAR:: Spreadsheet_Excel_Writer, together with additional opportunities PEAR:: OLE "understands" format Microsoft Excel.

Let's remove{take off} hats before Xavier Noguer which has made surprising job for introduction of it in PHP, with help Mika Tuupola for Spreadsheet_Excel_Writer.

Now, without the further noise, and the armed full knowledge of management of package PEAR which at you should be established, we shall begin loading libraries. Open your command line and enter in him the following:

$ pear install OLE

$ pear install Spreadsheet_Excel_Writer

That's all. We are ready!

The important remark: For examples in this clause{article} I used PEAR:: OLE version 0.5 and PEAR:: Spreadsheet_Excel_Writer version 0.7. I warn, what - that can will change in buduhhix versijax.

We continue our way. Let's create a simple sheet of the data.

Name of a file: example_1.php

<? php

// Introduction PEAR:: Spreadsheet_Excel_Writer

require_once "Spreadsheet/Excel/Writer.php";

// Creation of a case

$xls = and new Spreadsheet_Excel_Writer ();

// Sending HTTP of headings for the message to an observer about type vxodimyx // dannyx 

$xls->send("test.xls ");

// Addition of a sheet to a file, returning of object for addition dannyx

$sheet = and $xls-> addWorksheet (' Binary Count ');

// We write some figures 

for ($i=0; $i <11; $i ++) {

// Use of function PHP decbin () for transformation of an integer in // the binary data

$sheet-> write ($i, 0, decbin ($i));

}

// The end of a sheet, sending to an observer

$xls-> close ();

?>

Open a script in your observer, (it is meant, that he "is familiar" with Excel or OpenOffice Calc) and he will display sheet Excel with numbers{rooms} from 0 up to 10 in a binary kind.

Preservation of files

In this case the sheet is created dynamically - nothing soxranjaetsja on the server. If you xotite instead of it to create a file, you can reject a part for creation of a sheet which has not changed, for this purpose simply sending the designer a name of a file and a way to it , and it avoiding neobxodimosti sendings HTTP of headings:

Name of a file: example_2.php

<? php

// Whether the sheet was created?

if (! file_exists('sheets/binary.xls ')) {

    // Introduction PEAR:: Spreadsheet_Excel_Writer

    require_once "Spreadsheet/Excel/Writer.php";

    // Creation of a case, sending of a name of a file for creation

    $xls = and new Spreadsheet_Excel_Writer('sheets/binary.xls ');

    // Addition of a sheet to a file, returning of object for addition dannyx

    $sheet = and $xls-> addWorksheet (' Binary Count ');

    // We write some figures

    for ($i=0; $i <11; $i ++) {

    // Use of function PHP decbin () for transformation of an integer in // the binary data

      $sheet-> write ($i, 0, decbin ($i));

}

    // The end of a sheet, sending to an observer

    $xls-> close ();

}

?>

If you use system of family Unix do not forget to change sanctions to a folder in which you xranite sheets of the data that PHP could add in nix the data.

Review API

Xorosho, we have understood with the basic concepts. To receive a maximum from PEAR:: Spreadsheet_Excel_Writer, you need to know little bit more about API. Documentation API which is accessible on site PEAR now out-of-date (she began much more, with tex por the version of the documentation) has been created. Due to authors who have added a lot of documentation directly in a code, you can create own documentation API if you load phpDocumentor and will specify it  a folder which contains all isxodnye codes Spreadsheet_Excel_Writer.

The basic class with which you will always begin job - Spreadsheet_Excel_Writer, is item{point} of access to all other classes in library. He gives two vazhnyx zavodskix a method (which are determined in genitive class Spreadsheet_Excel_Writer_Workbook:)

* addWorksheet () - returns case Spreadsheet_Excel_Writer_Worksheet. The most part of job is carried out about a case of this class, enabling you to enter the data in cells of one sheet.

* addFormat () - returns case Spreadsheet_Excel_Writer_Format which is used for addition of visual formatting cells.

The library also contains three drugix a class, kotoryx you should oposat`sja, whether xotja you vrjat when nibud` will need ix to use.

* Spreadsheet_Excel_Writer_Validator makes possible addition of verifying rules for cells. Now for this class there is no documentation. It as though is an experimental code, hence I here shall not discuss it . Basically, he gives an opportunity of check dannyx vvedennyx in a cell the end user. More complex  rules of check can be established by means of expansion of a class. Class Spreadsheet_Excel_Writer_Workbook gives a method addValidator () for creation of a case of check, while Spreadsheet_Excel_Writer_Worksheet enables rules of check to be appointed in cells by means of a method setValidation ()

* Spreadsheet_Excel_Writer_Parser which is Parser - an ohm for sheets dannyx Excel, and helps you to check up, whether function by correct syntax Excel is.

* And at last - Spreadsheet_Excel_Writer_BIFFwriter - it is used for creation of Format Binarnyx of Files for xranenija files Excel. If you are interested in breaking Excel it will be interesting to you to study that he does{makes}, if no, to you to anything to worry about it as the library completely hides this class.

Confusion of a zero index.

One of methods of the note - Spreadsheet_Excel_Writer_Worksheet:: write () which we saw in the above-stated example, you will use many times for addition dannyx in cells. This method little bit confusing on stavneniju with the same method in Excel.

The first argument of function write () is number{room} of a line. Number{room} of the first line in tables PEAR:: Spreadsheet_Excel_Writer is 0, instead of 1 as it is accepted in Excel.

The second argument is number{room} of a column. Now, columns in Excel, are identified by letters of the alphabet instead of numbers so you only should get used to translation between two. Letter F is 6-th in the alphabet, so the second argument... 5 (certainly!) - an extreme left column - 0 (zero) in PEAR:: Spreadsheet_Excel_Writer, so you should deduct to receive number{room} of a column.

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

Exist still set of methods in class Spreadsheet_Excel_Writer_Worksheet, such as for "freezing" or "thawing" of parts of a sheet, for formatting a sheet as a whole for a seal, etc. About nix I shall a little tell in the subsequent primerax, but you should investigate the majority.

Addition of formatting of cells.

As about more krasivyx sheets? We can reach{achieve} it with help PEAR:: Spreadsheet_Excel_Writer using function addFormat () for transformation of object in Spreadsheet_Excel_Writer_Format. We use formatting to this object, using methods with which he provides, then is transmitted by his  method write () to function Spreadsheet_Excel_Writer_Worksheet, for addition of formatting to a cell which we have added.

For the sake of an example of " the Real World ", let's present, that I xochu to enable the clients the Internet of shop phpPetstore.com uploadings of the check for kuplennyx them of things as the Book (Workbook) containing one sheet (Worksheet).

I begin the sheet a usual material.

<? php

require_once "Spreadsheet/Excel/Writer.php";

// Creation of the book

$xls = and new Spreadsheet_Excel_Writer ();

// Creation of a sheet

$cart = and $xls-> addWorksheet (' phpPetstore ');

Further we shall add heading to a sheet - merging some cells, for his  accommodation. Here we shall receive the first performances of how formatting is done{made}:

// What nibud` the text in a role of heading of a sheet

$titleText = ' phpPetstore: Receipt from '. date (' dS M Y ');

// Creation of object of formatting

$titleFormat = and $xls-> addFormat ();

// Definition of a font - Helvetica works with OpenOffice calc too...

$titleFormat-> setFontFamily (' Helvetica ');

// Definition of the fat text

$titleFormat-> setBold ();

// Definition of the size of the text

$titleFormat-> setSize (' 13 ');

// Definition of color of the text

$titleFormat-> setColor (' navy ');

// Definitions width of border of the basis in "thick"

$titleFormat-> setBottom (2);

// Definition of color of border of the basis

$titleFormat-> setBottomColor (' navy ');

// Definitions of alignment in special value

$titleFormat-> setAlign (' merge ');

// Addition of heading in verxnuju the left cell of a sheet,

// Sending him a line of heading and also object of formatting 

$cart-> write (0,0, $titleText, $titleFormat);

// Addition trex pustyx cells for draining

$cart-> write (0,1, ", $ titleFormat);

$cart-> write (0,2, ", $ titleFormat);

$cart-> write (0,3, ", $ titleFormat);

// Height of a line

$cart-> setRow (0,30);

// Definition of width of a column for the first 4 columns

$cart-> setColumn (0,3,15);

At first notice, that I have received object of formatting having called addFormat () by means of object $xls which represents the current sheet. Then I have applied to object some specific formatting (methods under the name setBold () speak about myself - for more detailed information look documentation API).

When formatting is finished, I cause function write () for object $cart, for addition to a cell, passing object as the fourth argument.

I here have made a unique non-standard course, this association of four cells. Causing setAlign (' merge ') for object of formatting (usually you use for this purpose ' left ',' right 'or' center '), I have ordered Spreadsheet_Excel_Writer, that he should unit all cells to which to concern it of formatting. That is why I have created three empty cells and have applied to them formatting.

Use setRow () allows me to change height of a line, sdelaja it is more than her , than installation of height of line Excel by default. This method has many additional arguments of formatting which allow you to apply, for example, object of formatting to the current line. It is similar setColumn () I can establish width of a column and apply to it  the further formatting. The difference will be, that setRow () is applied only to one line, when setColumn () is applied to many stolbcam.

Now the data are necessary for addition to a sheet for me. To not complicate an example (adding a database), I shall use an indexed file of associative files which ostensibly grows out selection SQL.

$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),

);

" Stolbcy in a database " are keys of a file - ' description ',' price 'and' quantity ', the second, that we should make, it to add headings stolbcov with additional heading ' Total ' which we shall soon use:

// Definition of some formatting

$colHeadingFormat = and $xls-> addFormat ();

$colHeadingFormat-> setBold ();

$colHeadingFormat-> setFontFamily (' Helvetica ');

$colHeadingFormat-> setBold ();

$colHeadingFormat-> setSize (' 10 ');

$colHeadingFormat-> setAlign (' center ');

// A file with the data heading for stolbcov

$colNames = array (' Item ',' Price ($) ',' Quantity ',' Total ');

// Addition of all heading a uniform call

// We leave a line empty for more pleasant kind

$cart-> writeRow (2,0, $colNames, $colHeadingFormat);

You already saw formatting. You did not see a method writeRow () earlier. This method does{makes} same, as write (), but allows you to add data file with is left to the right, since certain number{room} of a line or a column. This method allows to reduce a code of the program considerably.

Still I want to make so that headings stolbcov were always visible, when we scroll page. In Excel - e to make it it is possible by means of "freezing" - choosing the block of cells which will be visible when the user will scroll a sheet, allowing him to see headings stolbcov (in this case) which explain that predstvalvjut these data. The same is possible in PEAR:: Spreadsheet_Excel_Writer:

// Group of cells for freezing

// 1-st Argument - a position of vertical association 

// 2-nd Argument - a position of horizontal association (0 = there is no horizontal association)

// 3-rd Argument - the top seen line in the bottom of vertical association

// 4-th Argument - the left seen column after goriznotal`nogo associations

$freeze = array (3,0,4,0);

// To freeze these cells!

$cart-> freezePanes ($freeze);

Notice, that "freezing" has been applied directly by object $cart, instead of by means of object of formatting as it has been applied to several cells. On the other hand, formatting has been applied to separate cells.

At last I pass through objects in " the bag ", adding the data to a sheet:

// Pseudo the 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 it for tracking current number{room} of a line

$currentRow = 4;

// Pass through the data, adding them to a sheet

foreach ($items as $item) {

   // Write each item to the sheet

$cart-> writeRow ($currentRow, 0, $item);

$currentRow ++;

}

In princype and all. If you newbie OOP in PHP, at first sight it can seem little bit frightening off, but you could notice already, that all methods are very understandably named also you can understand their values only having looked on them. The idea of an attraction of one object another can be novshevstvom for you but when you think of it it seems, that you create object Worksheet causing a method addWorksheetSheet () and that you add objects of formatting to a cell when you write () (write) in Worksheet.

Addition of function Excel.

Now you are able to create pleasant on a kind krupnoformatnye tables, but as any expert Excel-a will say, simple display of the data is not too useful. The life becomes more interesting when you use functions Excel for calculation of the data and their transformations in what nibud` more interesting.

Now I not expert Excel - a (and it not a management{manual} on Excel), but am clear, that my check should be cleverer so I need to add some calculations based on the data which I have already brought to the table. For every line I want to display " total item cost " (the sum of the bought things) - the data contain the price of a unit of measurements and also quantity{amount} of the bought products:

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

Having translated to terms Excel, for reception of the sum of the fifth line it is necessary to write the formula similar following:

[Cell D5] =PRODUCT (B5:C5)

For achievement of it with PEAR:: Spreadsheet_Excel_Writer, only it is necessary for me podpravit`t` a code which spends pass through the data:

// Use it for tracking current number{room} of a line

$currentRow = 4;

// Pass through the data, adding them to a sheet

foreach ($items as $item) {

   // Addition of each subject to a sheet

$cart-> writeRow ($currentRow, 0, $item);

// Remember, that Excel begins calculation of lines with *1!

$excelRow = $currentRow + 1;

// Creation of line PHP containing formula

$formula = ' =PRODUCT (B '. $excelRow. ':C '. $excelRow. ')';

// Addition of the formula by the line

$cart-> writeFormula ($currentRow, 3, $formula);

$currentRow ++;

}

Addition of the formula not predstvljaet from ourselves work - we only shall use a method writeFormula (). But the most important how I have mentioned earlier, - that Excel reckons lines since unit whereas PEAR:: Spreadsheet_Excel_Writer starts with zero. It means, that at creation of function I should remember it, otherwise I shall refer to wrong cells. That is why I have created a variable $excelRow which of itself represents $currentRow + 1. You can think, what is it design flaw on behalf of the authors, but remember, that in PHP as well as in many programming languages, indexed files begin with a zero index.

Now I displays the sums for every line. But how combining all sums that the visitor knew about size of that sum which will be on the check of their credit card? For this purpose it is enough to combine all sums and result to display in what nibud` to a cell.

Terms Excel, I need to use function SUM (), for addition of all sums which are displayed in stolbce D.

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

For a premise{room} of it in a sheet after pass through the data is ended, I add the following:

// The first line as Excel it understands, - $currentRow was 4 in the beginning

$startingExcelRow = 5;

// Last line as in Excel

// (which same as well as currentRow after the termination{ending} of pass)

$finalExcelRow = $currentRow;

// Format Excel for reception of the sum of all values

$gTFormula = ' =SUM (D '. $ startingExcelRow. ':D '. $ finalExcelRow. ')';

// Some additional formatting for cells of a total sum

$gTFormat = and $xls-> addFormat ();

$gTFormat-> setFontFamily (' Helvetica ');

$gTFormat-> setBold ();

$gTFormat-> setTop (1); // Top border

$gTFormat-> setBottom (1); // Bottom border

// Addition of some text and formatting

$cart-> write ($currentRow, 2, ' Grand Total: ', $ gTFormat);

// Addition of the formula of a total sum with a format

$cart-> writeFormula ($currentRow, 3, $gTFormula, $gTFormat);

At last I finish creation of the check sending a sheet directly in an observer.

// Send the Spreadsheet to the browser

$xls->send("phpPetstore.xls ");

$xls-> close ();

That's all. The sheet is ready for uploading.