Excel plug-in for TinyButStrong

With the Excel plug-in, you can use the TinyButStrong Template Engine to drive the merge of Excel Worksheets based on a template that you can build visually under Microsoft Excel.
This manual assumes that you know merging with TinyButStrong (TBS).

Since Microsoft Excel version 2002 and higher, you can open and save Excel documents in the "XML Spreadsheet" format. Those files are Excel documents saved with an XML source instead of a binary source. The default extension is ".xml", but you can rename the file with an ".xls" extension and they will open like any Excel document. Such Excel documents saved in XML enable nearly all worksheets features (makeup, formats, formulas) but Graphics and Visual Basic Macros are not supported (all differences are documented here).

OpenOffice.org Calc can open those files since version 2.0, but only if the extension is ".xml".

This Excel-XML format is a convenient way to do dynamic Excel files by the template technique.

1 PHP side

include('tbs_class.php');
$TBS = new clsTinyButStrong; // The TBS engine

include('tbs_plugin_excel.php');
$TBS->PlugIn(TBS_INSTALL,TBS_EXCEL); // Install the Excel plug-in

$TBS->LoadTemplate('my_template.xml'); // Open the Excel-XML template
$TBS->MergeBlock('bz',$my_data); // Merge the block
$TBS->Show() // Force the result to bas donwload as an Excel file.

1.1 Requirements

Include the file 'tbs_plugin_excel.php'. This can be after the TBS object variable is created.
This plug-in version 1.0.x requires TBS version between 3.0.5 and 3.1.1.
This plug-in version 1.1.x requires TBS version 3.6.2 or higher.

1.2 Installation

You have to install the plugin in manual mode. The plug-in should be installed before to call the LoadTemplate() method.

Example:

$TBS->PlugIn(TBS_INSTALL,TBS_EXCEL);

1.3 Commands

The Excel plug-in enables you to retrieve the final result of the merge in several ways. It is done with the method $TBS->Show(). By default this method will produce an Http download with a default file name. Here are the available options:

Output the result as an Http download:

$TBS->Show(TBS_EXCEL_DOWNLOAD, $FileName);

Output the result as a file on the local server:

$TBS->Show(TBS_EXCEL_FILE, $FilePath);

Retrieve the result as a PHP string file:

$TBS->Show(TBS_EXCEL_STRING);
$xml_result = $TBS->Source;

Deprecated since version 1.1.0:
- Change the name of the file for download: $TBS->PlugIn(TBS_EXCEL,TBS_EXCEL_FILENAME,'file.xls');
- Enables the file to be displayed within the browser (old Internet Explorer only): $TBS->PlugIn(TBS_EXCEL,TBS_EXCEL_INLINE);

2 Template side

2.1 Create a new template

You can choose one of those two ways:
• Create a new document with Excel. Save it with choosing "XML Spreadsheet (*.xml)" for the File type in the dialog box.

I suggest you to change the extension of the file for ".xls" just in order to have your template opened with Excel normally from Windows Explorer. Otherwise, Windows Explorer may ask you to choose a program to open the XLS file when you double-click on it. Whatever, Excel will makes no problem to open your file.

or:
• Use the empty.xls file which is provided with this plug-in distribution.

Your template can have several sheets, and you can use all Excel formats for displaying you data.

2.2 Merging data items in cells

Microsoft Excel assigns a type for each value placed into a cell (it can be String, Numeric, DateTime, Boolean or Error). But when you put a TBS field into a cell, then the type of the cell's value is automatically set to String. If the value to be merged by the TBS field is not a string, you can force the type of the cell's value using parameter ope. This can be useful for calculating total, or using Excel formats for example.

Parameter		Value forced to
ope=xlNum		Numeric
ope=xlDT		DateTime

Examples:

Current date:	[var..now;ope=xlDT]
Total amount:	[var.amount;ope=xlNum]

Remark: If the merged value is not corresponding to its type then it will produce an error when the document is opened.
The String type can display any value.

2.3 Merging blocks

Within an Excel-XML document, <Table> <Row> and <Cell> elements are used to dispose data into the Worksheets. It is quite similar to the HTML <table><tr> and <td> elements. But a <Table> is really touchy to multiply (see section Debuggin below).

Thus, the more common TBS blocks you'll define will be block=Row when you want to expand rows, or block=Cell when you want to expand columns.

Examples:

Title	Price
[b1.title;block=Row]	[b1.price;ope=xlNum]

2.4 Managing formulas

After a block is merged, Excel formulas (like SUM) over this block may not be what you wanted because cells or rows have been expanded by the block.

• If the formula is placed on the right or bottom of the merged block:

Use an Excel formula with Absolute Reference to refer to the first cell of the block, and Relative Reference to refer to the last cell of the block. This way, the formula will stay valid after the block is expanded. This is working because Relative References are saved relatively to the cell with the formula.

Example:

	A	B
1	[b1.title;block=Row]	[b1.price;ope=xlNum]
2		=SUM(B$1:B1)

• If the formula is placed on the left or top of the merged block:

The tip above won't fit in this case because both relative and absolute references will refer to the first cell of the block. So this plug-in gives another dedicated trick for this.

TBS parameter ope=xlPushref placed in an Excel formula, makes the previous Relative Reference to be pushed as much as the value of the TBS field's value. Only the first previous Relative Reference will be pushed. The TBS field with parameter must be placed inside a +N("") function, this enables the Excel formula to stay valid. If your formula deals with strings, you can use &N("").

Example:

	A	B
1		=SUM(B2:B2)+N("[b1.#;ope=xlPushRef]")
2	[b1.title;block=Row]	[b1.price;ope=xlNum]

3 Result of the merge

3.1 Debugging the result of the merge

TBS error messages will cause the result to not be a downloadable file because error messages are prompted before header informations are sent.
Nevertheless, your result may be an invalid Excel-XML document. XML syntax is less tolerating than HTML. If your document is invalid then Excel will prompt an unhelpful error message when opened. Then you will probably need to look at the source of the merged document to understand what's wrong.

Here is some interesting information about the Excel-XML syntax:

- One document contains one or several <Worksheets> elements.
- One <Worksheet> contains one or several <Table> elements.
- One <Table> contains one or more <Row> elements.
- One <Row> contains one or more <Cell> elements.

Diagram:
<Worksheet ... >
  <Table ... >
    <Row ...>
      <Cell ...> </Cell> <Cell ...> </Cell> <Cell ...> </Cell>
    </Row ...>
  </Table ... >
</Worksheet ... >

• Microsoft Excel decides how to split your data in <Tables> , <Cell> and <Row>. Or you can decide it by yourself by manually changing the source of the template.
• Take care that your items may be separated in several <Table> elements in a Worksheets, this happens mostly when data items are separated by numerous empty cells.
• Take care that <Row> elements which are positioned after a merged block (or a conditional block) have no absolute index for positioning (attribute ss:Index), otherwise Excel will consider the file as badly formatted.
• <Tables> elements should not be repeated by merging TBS block because their first <Row> and <Cell> elements may contain absolute indexes for positioning (attribute ss:Index).
•They are some other XML elements but not really interesting for merging TBS blocks.

3.2 XML or XLS extension?

Microsoft Excel offers to save XML Spreadsheets as a file with an ".xml" extension (for example: result.xls). Nevertheless, other type of XML files may have the same extension. That's why if you double-click on an XML Spreadsheet file to open it directly, it may or may not open Excel depending to your configuration.

OpenOffice and LibreOffice can open XML Spreadsheet files only if the file has the XML extension.

In order to be sure that the user will be able to open your XML Spreadsheet, you may prefer to save the file with the XLS extension (for example: result.xls). With this extension, the file will be opened by Excel whatever the version of Windows of the version of Excel. Nevertheless, if you open such a file with Excel 2007 and higher, you will have a message box: "the file you are trying to open 'result.xls' is in a different format than specified by the file [...]". If you choose Yes, then the workbook will be opened normally.

Version 1.1.0:	- Works with TBS version 3.6.2 or higher and with PHP 5. - Possibility to save the result as a file. - Possibility to retrieve the result in a PHP string. - Avoid the output if a TBS error occurs. - Fixed bug: Warning: Parameter 4 to clsTbsExcel::BeforeMergeBlock() expected to be a reference, value given [...] - Fixed bug: Strict Standards: call_user_func() expects parameter 1 to be a valid callback, non-static method clsTbsExcel::f_XmlConv() should not be called statically in [...]
Version 1.0.3:	Minor fixes in the HTML header which force the file to download.
Version 1.0.2:	Bug fixed about special sections (like headergrp, when, nodata, ... ) that could be bad placed after a MergeBlock().