XLS Generator Class


http://paggard.com/projects/xls.generator/

Additional materials


[^ up]

Introduction


XLS Generator is a PHP script, that makes it possible to create binary XLS files (used by MS Excel) using PHP only. Using this script you will be able to create spreadsheets on UNIX-based systems, or on Windows systems without any dependant on the software installed - the only thing that is required for the script to work - is PHP 4.x (or later) installed.

The script is using user-friendly input format, which is actually XML template. This template can be generated dynamically (like dynamic php web-pages) and then passed to the XLS Generator (or stored for future use) - and XSL Generator will convert this XML template into binary XLS file, which could be opened by any version of MS Excel (from Excel 95). This means that you will be able to convert data from database tables, user input or from any other sources into Excel spreadsheets, creating price-lists, reports, product listings etc.

XLS Generator has many features which will help you to make your generated spreadsheets look individual: font styles, border and color settings, background, foreground and border colors and styles, text orientation and in-cell alignment settings.

Besides, you will be able to tune the printed look of the generated spreadsheet: paper size, page orientation, printed margins, header and footer which can include page numbers and other kind of technical information.

XLS Generator also provides many other features including the possibility to lock certain spreadsheets with a password.


[^ up]

Usage Example

It is very simple to use the class, so, the main part you should concentrate your efforts - is to create good XML template to pass to the class. Creation of the template is pretty much alike to the process of cretion dynamic HTML pages using PHP - but in this case you do not echoing the results to the user's browser, but pass the ready template to XLS Generator class. The documentation how to create an XML template is given below in this document. When the template is created you can either save it on disk for future use or pass directly to the XLS Generator to get the .xls file.

This is very simple example of creation of an .xls file:



<?
// UNCLUDING THE THE MAIN CLASS FILE
require_once("xls_generator/cl_xls_generator.php");

// CREATING XML TEMPLATE
$xml_template "<?xml version=\"1.0\"?>
<Workbook>
<Styles>
    <style name=\"heading\" bold=\"1\" valign=\"middle\" align=\"center\" />
</Styles>
<Worksheet name=\"Simple Demo\">
    <Table>
        <Row height=\"25\">
            <cell width=\"40\" style=\"heading\" >Hello</cell>
            <cell width=\"50\" style=\"heading\" >World!</cell>
        </Row>
        <Row height=\"25\">
            <cell width=\"40\" style=\"heading\" >Current date is - </cell>
            <cell width=\"50\" num_format=\" d mmmm yyy hh:mm:ss\" >"
.Unix2Excel(time())."</cell>
        </Row>
    </Table>
</Worksheet>
</Workbook>
"
;

// TEMP DIRECTORY PATH - !!! YOU SHOULD HAVE THE WRITING PERMISSIONS TO IT
$temp_dir "";

// RESULT FILE NAME
$file_name "xml_test.xls";

// MAIN CLASS CALL
$xls = new xls($xml_template,$file_name,"xls_config.inc",$temp_dir);


// PASSING THE GENERATED FILE TO THE USER
header("Content-Type: application/X-MS-Excel; name=\"$file_name\"");
header("Content-Disposition: attachment; filename=\"$file_name\"");

$fh=fopen($file_name"rb");
fpassthru($fh);
unlink($file_name);

?>

NOTE!
$temp_dir variable is rather important - you can leave it empty and in this case the file will be created in the system default temp directory - this is not very good, as on Windows systems the temp file may not be deleted, and also - it is always better to be able to track the used space. So, I advise to create some special directory, ensure that your web server (PHP) has permissions to write into it - and assign the full path to this directory to the $temp_dir variable.


[^ up]

Rules

Only mind that XML has certain rules:
  1. All templates must have <?xml version="1.0"?> at the very beginning.
  2. All the document data should be enclosed into <Workbook> … </Workbook> element
  3. All element tags must be closed like this: <Workbook> … </Workbook>
    or like this <Workbook />
  4. Attributes must always be quoted:
    <worksheet name="First Sheet"> … </worksheet>
    <orientation landscape="1" />
  5. All elements must be properly nested:
    wrong: <row><cell> </row></cell>
    right: <row><cell> … </cell></row>
  6. Document can contain comments: <!-- some comment -->
  7. All colors can be only named:
    wrong: #FF0000
    right: "red"
    all the available color names are listed in the "
    Color names list" section of this doc.
  8. To insert special symbols like '<', '>' etc. - you can use their HTML codes : '&lt;', '&gt;' etc.
  9. NOTE! As the Generator uses xls format - BIFF5 - it supports only strings with not more than 255 symbols.
    This means that if the data for the cell is more than 255 symbols - it will be truncated.


[^ up]

Format parameters

Format parameters are responsible for the final look of your SpreadSheet. They can be formed into predefined styles within <Styles> section of the Workbook or can be used for within <Cell> elements to define the format of this very cell. Note! If format parameters will be included into <Cell> element - they will discard any of the predefined styles assigned for this cell.


[^ up]

Font settings


[^ up]

Cell settings


[^ up]

Styles


<Workbook>

    <Styles>

    . . .

    </Styles>

. . .

</Workbook>

Section <Styles> … </Styles> is a part of <Workbook> element and serves for defining style constructions to be used in the worksheets. This section may contain <Style /> records which should always have name="" attribute to define the style name, besides, this record can contain the number of format parameters to describe the style. The created styled can then be used to define styles for Worksheets elements: <Table>, <Row> and <Cell>.

Example:


<Styles>

    <style name="heading" font="Arial" bold="1" size="12"

color="royalblue" bottom="double" border_color="royalblue"

valign="bottom" />

</Styles>

. . .

. . .

    <Row>

        <cell style="heading">Main Heading Text</cell>

    </Row>


[^ up]

WorkSheet


<Worksheet name="some name">

. . .

</worksheet>

This element is a part of <Workbook> and creates new worksheet in the given workbook with the specified name. Within the workbook you can define whatever number of worksheets that you need.


[^ up]

Print settings


<Worksheet name="some name">

    <Print>

    . . .

    </Print>

</worksheet>

Print settings are responsible for the printed look of the given worksheet. They can be defined for each sheet separately in the <Print> element of the each sheet. Or can be set for all the sheets within the Workbook - this can be done by modifying the configuration file.

Example:


<Print>

    <papersize index="0" />

    <orientation landscape="1" />

    <margin top="0.50" right="0.50" bottom="0.50" left="0.50" />

    <Header margin="0.1">Upper Header [Page &P of &N]</Header>

    <Footer>lower footer</Footer>

</Print>


[^ up]

Protection

Any worksheet can be protected from editing by a password. If password is defined in the configuration file - then all the worksheets in the book for which the password was not specified will be protected by this password. Password can not be more than 9 symbols.

Example:


<Worksheet name="some name">

    <Password>secret</Password>

. . .

</Worksheet>


[^ up]

Table


<Worksheet name="some name">

. . .

    <Table>

    . . .

    </Table>

</Worksheet>

This element contains the main worksheet table data. Custom style can be assigned to this element and this style will be inherited by all the children elements to which no other styles was assigned.


[^ up]

Row and Cell elements


<Worksheet name="some name">

. . .

    <Table>

        <Row>

            <Cell> . . . </Cell>

        <Row>

        <Cell> . . . </Cell>

    </Table>

</Worksheet>


[^ up]

Row

Table row definition. May have the following attributes:


[^ up]

Cell

Table cell definition. May or may not be the part of <Row> element. If not part of any <Row> element - the next row will be used to place cell data. If <Cell> elements are following each other - the column number is incremented automatically for each cell. This element can have the following attributes: Also, <Cell> element may contain any numbers of Format parameters to define the format of the cell without predefining style in the <Styles> section. But this new format will discard any of the predefined styles assigned to this cell. <Cell> element may contain a hyperlink or a mailto: link. To put one of this links into a cell you should always have either http:// or ftp:// or mailto: in the beginning of the cell text - then the text will be treated as an URL.

Example:


<Worksheet name="some name">

. . .

<Table>

<Row height="25">

    <Cell>First cell</Cell>

    <Cell width="40" style="heading">Second cell</Cell>

    <Cell index="+1">First cell</Cell>



<Row>

<Cell index="C4" border="double" font="Arial">direct index</Cell>

</Table>

</Worksheet>


[^ up]

Formulas

XLS Generator supports adding formulas and Excel functions to table cells. To identify cell data as a formulas, it should be always started from "=" symbol: <cell>= 3 + 7</cell>.

Rules:

Below is the list of currently supported functions. Please, refer to the MS Excel help documentation concerning the description for functions and usage examples. Only pay attention that to be used in XLS Generator the format of the functions should comply the abovementioned rules.

ABSCONCATENATEDSUMHYPGEOMDISTLOOKUPPEARSONSEARCHTIME
ACOSCONFIDENCEDVARIFLOWERPERCENTILESECONDTIMEVALUE
ACOSHCORRELDVARPINDEXMATCHPERCENTRANKSIGNTINV
ADDRESSCOSERROR.TYPEINDIRECTMAXPERMUTSINTODAY
ANDCOSHEVENINFOMDETERMPISINHTRANSPOSE
AREASCOUNTEXACTINTMEDIANPMTSKEWTREND
ASINCOUNTAEXPINTERCEPTMIDPOISSONSLNTRIM
ASINHCOUNTBLANKEXPONDISTIPMTMINPOWERSLOPETRIMMEAN
ATANCOUNTIFFACTIRRMINUTEPPMTSMALLTRUE
ATAN2COVARFALSEISBLANKMINVERSEPROBSQRTTRUNC
ATANHCRITBINOMFDISTISERRMIRRPRODUCTSTANDARDIZETTEST
AVEDEVDATEFINDISERRORMMULTPROPERSTDEVTYPE
AVERAGEDATEVALUEFINVISLOGICALMODPVSTDEVPUPPER
BETADISTDAVERAGEFISHERISNAMODEQUARTILESTEYXVALUE
BETAINVDAYFISHERINVISNONTEXTMONTHRADIANSSUBSTITUTEVAR
BINOMDISTDAYS360FIXEDISNUMBERNRANDSUBTOTALVARP
CALLDBFLOORISREFNARANKSUMVDB
CEILINGDCOUNTFORECASTISTEXTNEGBINOMDISTRATESUMIFVLOOKUP
CELLDCOUNTAFREQUENCYKURTNORMDISTREGISTER.IDSUMPRODUCTWEEKDAY
CHARDDBFTESTLARGENORMINVREPLACESUMSQWEIBULL
CHIDISTDEGREESFVLEFTNORMSDISTREPTSUMX2MY2YEAR
CHIINVDEVSQGAMMADISTLENNORMSINVRIGHTSUMX2PY2ZTEST
CHITESTDGETGAMMAINVLINESTNOTROMANSUMXMY2 
CHOOSEDMAXGAMMALNLNNOWROUNDSYD 
CLEANDMINGEOMEANLOGNPERROUNDDOWNT 
CODEDOLLARGROWTHLOG10NPVROUNDUPTAN 
COLUMNDPRODUCTHARMEANLOGESTODDROWTANH 
COLUMNSDSTDEVHLOOKUPLOGINVOFFSETROWSTDIST 
COMBINDSTDEVPHOURLOGNORMDISTORRSQTEXT 


[^ up]

Example of the simple XML template


<?xml version="1.0"?>

<Workbook>

<Styles>

    <style name="heading" font="Arial" bold="1" size="12"

    color="royalblue" bottom="double" border_color="royalblue"

    valign="bottom" />

    <style name="date" num_format=" d mmmm yyy hh:mm:ss" />

</Styles>

<Worksheet name="Main Sheet">

        <Print>

            <papersize index="0" />

            <orientation landscape="1" />

            <margin top="0.50" right="0.50" bottom="0.50" left="0.50" />

            <Header margin="0.1">Upper Header [Page &P of &N]</Header>

            <Footer>lower footer</Footer>

        </Print>

        <Password>secret</Password>

    <Table>

        <Row height="25">

            <cell style="heading">XLS Generator</cell>

            <cell style="heading"> PHP 2 EXCEL TOOL</cell>

        </Row>

        <Row>

            <cell width="35">Introduction</cell>

            <cell width="60">XLS Generator is a PHP script, and so on...</cell>

        </Row>

        <row>

            <cell>Date (in different formats) When this file was generated</cell>

            <cell style="date">&NOW</cell>

        </row>

        <row>

            <cell>Mailto links</cell>

            <cell color="dodgerblue" underline="1">mailto:paggard@paggard.com</cell>

        </row>

        <row>

            <cell>This page is protected by password:</cell>

            <cell border="dashed" align="center">"secret"</cell>

        </row>

        <Row index="+1">

            <cell style="heading">Formulas</cell>

            <cell style="heading"> </cell>

        </Row>

        <Row>

            <cell width="35">Basic Operations [3+8*4] = </cell>

            <cell width="60">= 3 + 7 * 4</cell>

        </Row>

        <Row>

            <cell>Cell references [B8 + 5] = </cell>

            <cell>=  B8 + 5</cell>

        </Row>

        <Row>

            <cell>Arythmetic functions [PI()] = </cell>

            <cell>= PI()</cell>

        </Row>

        <Row>

            <cell>And other 351 functions [ROUND(SUM(B8:B10),2)] = </cell>

            <cell>=ROUND(SUM(B8:B10),2)</cell>

        </Row>



    </Table>

</Worksheet>

<Worksheet name="Second Sheet">

    <Table>

        <Row height="30">

            <cell>1</cell>

            <cell>Here is another sheet here</cell>

            <cell>!</cell>

        </Row>

    </Table>

</Worksheet>

</Workbook>


[^ up]

Available color names

IndexNameHEXColor  
8black000000 
9whiteFFFFFF 
10redFF0000 
11blue0000FF 
12yellowFFFF00 
13maroon800000 
14purple800080 
15aqua00FFFF 
16green008000 
17navy000080 
18olive808000 
19teal008080 
20gray808080 
21silverC0C0C0 
22linenFAF0E6 
23lightyellowFFFFE0 
24wheatF5DEB3 
25tanD2B48C 
26goldFFD700 
27tomatoFF6347 
28crimsonDC143C 
29saddlebrown8B4513 
30chocolateD2691E 
31coralFF7F50 
32olivedrab6B8E23 
33darkgreen006400 
34greenyellowADFF2F 
35yellowgreen9ACD32 
    
IndexNameHEXColor  
36limegreen32CD32 
37lightgreen90EE90 
38aquamarine7FFFD4 
39seagreen2E8B57 
40mediumturquoise48D1CC 
41cadetblue5F9EA0 
42lightcyanE0FFFF 
43darkcyan008B8B 
44lightblueADD8E6 
45lightskyblue87CEFA 
46royalblue4169E1 
47dodgerblue1E90FF 
48indigo4B0082 
49midnightblue191970 
50orchidDA70D6 
51mediumpurple9370DB 
52darkmagenta8B008B 
53thistleD8BFD8 
54mediumvioletredC71585 
55hotpinkFF69B4 
56pinkFFC0CB 
57lightsteelblueB0C4DE 
58steelblue4682B4 
59blueviolet8A2BE2 
60whitesmokeF5F5F5 
61lightgreyD3D3D3 
62darkgrayA9A9A9 
63dimgray696969 

[^ up]

The built-in number formats

IndexTypeFormat string
0GeneralGeneral
1Decimal0
2Decimal0.00
3Decimal#,##0
4Decimal#,##0.00
5Currency"$"#,##0_);("$"#,##0)
6Currency"$"#,##0_);[Red]("$"#,##0)
7Currency"$"#,##0.00_);("$"#,##0.00)
8Currency"$"#,##0.00_);[Red]("$"#,##0.00)
9Percent0%
10Percent0.00%
11Scientific0.00E+00
12Fraction# ?/?
13Fraction# ??/??
14DateM/D/YY
15DateD-MMM-YY
16DateD-MMM
17DateMMM-YY
     
IndexTypeFormat string
18Timeh:mm AM/PM
19Timeh:mm:ss AM/PM
20Timeh:mm
21Timeh:mm:ss
22Date/TimeM/D/YY h:mm
37Account._(#,##0_);(#,##0)
38Account._(#,##0_);[Red](#,##0)
39Account._(#,##0.00_);(#,##0.00)
40Account._(#,##0.00_);[Red](#,##0.00)
41Currency_("$"* #,##0_);_("$"* (#,##0);_("$"* "-"_);_(@_)
42Currency_(* #,##0_);_(* (#,##0);_(* "-"_);_(@_)
43Currency_("$"* #,##0.00_);_("$"* (#,##0.00);_("$"* "-"??_);_(@_)
44Currency_(* #,##0.00_);_(* (#,##0.00);_(* "-"??_);_(@_)
45Timemm:ss
46Time[h]:mm:ss
47Timemm:ss.0
48Scientific##0.0E+0
49Text@

[^ up]

Paper Size Indexes Table

IndexPaper typePaper size
0Undefined 
1Letter8 1/2" x 11"
2Letter small8 1/2" x 11"
3Tabloid11" x 17"
4Ledger17" x 11"
5Legal8 1/2" x 14"
6tatement5 1/2" x 8 1/2"
7Executive7 1/4" x 10 1/2"
8A3297mm x 420mm
9A4210mm x 297mm
10A4 small210mm x 297mm
11A5148mm x 210mm
12B4250mm x 354mm
13B5182mm x 257mm
14Folio8 1/2" x 13"
15Quarto215mm x 275mm
16 10" x 14"
17 11" x 17"
18Note8 1/2" x 11"
19#9 Envelope3 7/8" x 8 7/8"
20#10 Envelope4 1/8" x 9 1/2"
     
IndexPaper typePaper size
21#11 Envelope4 1/2" x 10 3/8"
22#12 Envelope4 3/4" x 11"
23#14 Envelope5" x 11 1/2"
24C Sheet17" x 22"
25D Sheet22" x 34"
26E Sheet34" x 44"
27DL Envelope110mm x 220mm
28C5 Envelope162mm x 229mm
29C3 Envelope324mm x 458mm
30C4 Envelope229mm x 324mm
31C6 Envelope114mm x 162mm
32C65 Envelope114mm x 229mm
33B4 Envelope250mm x 353mm
34B5 Envelope176mm x 250mm
35B6 Envelope125mm x 176mm
36Italy Envelope110mm x 230mm
37Monarch Envelope3 7/8" x 7 1/2"
386 3/4 Envelope3 5/8" x 6 1/2"
39US Standard Fanfold14 7/8" x 11"
40German Std. Fanfold8 1/2" x 12"
41German Legal Fanfold8 1/2" x 13"

[^ up]

Header and Footer special commands

CommandDescription
&LStart of the left section
&CStart of the centred section
&RStart of the right section
&PCurrent page number
&NPage count
&DCurrent date
&TCurrent time
&ASheet name (BIFF5-BIFF8)
&FFile name without path
&ZFile path without file name (BIFF8X)
&GPicture (BIFF8X)
&BBold on/off (BIFF2-BIFF4)
&IItalic on/off (BIFF2-BIFF4)
&UUnderlining on/off
&EDouble underlining on/off (BIFF5-BIFF8)
&SStrikeout on/off
&XSuperscript on/off (BIFF5-BIFF8)
&YSubscript on/off (BIFF5-BIFF8)
MAY NOT WORK:
&"<fontname>"Set new font <fontname>
&"<fontname>,<fontstyle>" Set new font with specified style<font style>.
The style <fontstyle> is in most cases one of “Regular”, “Bold”,
“Italic”, or “Bold Italic”. But this setting is dependent
on the used font, it may differ (localised style names,
or “Standard”, “Oblique”, ...).
&<fontheight> Set font height in points (<fontheight> is a decimal value).
If this command is followed by a plain number to be printed in the header,
it will be separated from the font height with a space character.

[^ up]

Author & Copyright

License Agreement:

If you buy XLS Generator you can use it as you like it and anywhere you will need it. You can do what ever you want with it except selling the script itself. If you're going to include my script into some commercial project for selling, you should include the script price into the budget and every of your customer should together with your software buy the license for my script. It is also possible to buy a distribution license for the script. If you have other plans - we can discuss them.

Vadim Kiparenko
paggard@paggard.com
If you will have any question of suggestions - feel free to contact me directly - I'll try to respond as soon as possible.

© 2004. All Rights Reserved. This class is a commercial software, if you are interested in purchasing it - contact me.