XLS Generator Class

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.

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.

• Font - Font name (Arial, Times New Roman, Courier New etc.). Fonts are not included into the final XLS file, and to be displayed correctly, the specified font should be installed in user's system. I advise not to use any of the exotic fonts and stick to the default ones.
• Font_charset - defines the character set for the font
  • "0" = ANSI Latin
  • "2" = Symbol
  • "77" = Apple Roman
  • "128" = ANSI Japanese Shift-JIS
  • "129" = ANSI Korean (Hangul)
  • "130" = ANSI Korean (Johab)
  • "134" = ANSI Chinese Simplified GBK
  • "136" = ANSI Chinese Traditional BIG5
  • "161" = ANSI Greek
  • "162" = ANSI Turkish
  • "163" = ANSI Vietnamese
  • "177" = ANSI Hebrew
  • "178" = ANSI Arabic
  • "186" = ANSI Baltic
  • "204" = ANSI Cyrillic
  • "222" = ANSI Thai
  • "238" = ANSI Latin II (Central European)
  • "255" = OEM Latin I
• Size - font size in pt
• Bold - "1" = bold; "0" = regular
• Italic - "1" = italic; "0" = not italic
• Color - should be equal to one of the predefined color names ("red", "navy" etc). The full list of available color names can be found in the 'available_colors.html' file
• Underline - "1" = underlined; "0" = not underlined
• Strikeout - "1" = stroked out; "0" = not stroked out
• Outline - "1" = outlined; "0" = not outlined
• Script - "0" = none; "1" = superscript; "2" = subscript
• Font_style - to set all the font styles within one parameter:
  • "b" - boldl;
  • "i" - italic;
  • "u" - underlined
  • "r" - strikeout
  • "o" - outline
    The values can be combined: "biu" = bold + italic + underline

<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>.

<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>

<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.

<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.

<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>

<Worksheet name="some name">

    <Password>secret</Password>

. . .

</Worksheet>

<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.

<Worksheet name="some name">

. . .

    <Table>

        <Row>

            <Cell> . . . </Cell>

        <Row>

        <Cell> . . . </Cell>

    </Table>

</Worksheet>

• Index="" - row index - if omitted - the data will be placed to the next row after the previous </Row> (ending tag)
  • "25" - exact row number
  • "+1" - plus number of rows. If the previous row was 10 - then this row will be 12
  • "-2" - minus number of rows. If the previous row number was 10 - then this row will be 9
• Height="20" - row height in millimeters
• Style="style_name" - the name of the style to be used for the whole row. The appropriate style should be predefined in the <Styles> section.

• Index="" - cell index, if omitted - the next cell in the row will be used to place data.
  • "B3" - exact index of the table cell - in this case all the previous row/cell information will be ignored and the data will be put into cell B3
  • "+1" - plus number of columns. If the previous cell has column #2 (B) - this cell will be placed in the column #4 (D)
  • "-1" - minus number of columns. If the previous cell has column #3 (C) - this cell will be placed in the column #2 (B)
• Width="30" - column width in millimeters, this parameter defines the width for the whole column - so, it is useless to redefine it several times
• Style="style_name" - the name of the style to be used for the given cell. The appropriate style should be predefined in the <Styles> section.

<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>

Rules:

• Formulas in cells shuld always start from "=" symbol
• When writing a formula you should always frame operand symbols (like '+', '-', '*', '/' etc into white space:
  wrong: <cell>=3+2</cell>
  right: <cell>= 3 + 2</cell>
• Currently, negative values in formulas are not supported, to override this you can use the following workaround:
  wrong: =SUM(-5,10)
  right: =SUM(0 - 5,10)

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.

<?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>

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.