Location: PHPKode > scripts > TinyButStrong plug-in for Excel > tinybutstrong-plug-in-for-excel/tbs_plugin_excel.htm
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<title>Plug-in Help</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
table {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
}
body {
	font-family: Verdana, Arial, Helvetica, sans-serif;
	font-size: 12px;
}
.norm {
	margin-left: 30px;
	padding: 5px;
	border: 1px solid #999999;
	margin-top: 12px;
	margin-bottom: 12px;
}
.decal {
	margin-left: 20px;
}
.title-1 {
	font-size: 16px;
}
.title-1b {
	text-decoration: underline;
}
.title-1c {
	font-weight: bold;
}
.title-2 {
	font-size: 16px;
	text-decoration: underline;
}
.title-3 {
	font-size: 16px;
	text-decoration: underline;
	background-color: #6699CC;
	margin-top: 20px;
	margin-bottom: 20px;
}
.txt-small {
	font-size: 10px;
}
.txt-code {
	font-family: "Courier New", Courier, mono;
	font-size: 12px;
}
.opt-name {
	color: #990000;
}
a.opt-name {
	color: #990000;
	text-decoration:none;
}
a.opt-name:hover {
	color: #990000;
	text-decoration:underline;
}
.opt-value {
	color: #000099;
}
.opt-html {
	color: #006600;
}
.opt-type {
	font-style: italic;
	color: #0033CC;
}
.border-0 {
	border: 1px solid #999999;
}
.border-1 {
	border: 1px solid #FF3399;
}
.border-2 {
	border: 1px solid #336699;
}
.border-3 {
	border: 1.5px solid #CC6600;
}
.versioning {
	background-color: #EAEAEA;
}
.excelborder {
	background-color: #DDDDDD;
}
-->
</style>
</head>
<body bgcolor="#FFFFFF">
<div align="center"><span class="title-1 title-1c">Excel plug-in for TinyButStrong</span> <br>
  <span class="txt-small">by Skrol29, 2008-04-06 </span><br>
version 1.0.3 <span class="txt-small">see changelog <a href="#changelog">here</a></span> </div>
<br>
With the Excel plug-in, you can use the <a href="http://www.tinybutstrong.com">TinyButStrong</a> Template Engine to drive the merge of Excel Worksheets based on a template that  you can build visually under Microsoft Excel.<br>
This manual assumes that you know merging with TinyButStrong (TBS).<br>
<br>
Since Microsoft Excel version 2002 and higher, you can open and save   Excel documents in the &quot;XML Spreadsheet&quot; format. Those files are Excel documents saved with an XML source instead of a binary source. The default extension is <span class="opt-name">&quot;.xml&quot;</span>, but you can rename the file with  an <span class="opt-name">&quot;.xls&quot;</span> 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 <a href="http://office.microsoft.com/en-gb/assistance/HA010346391033.aspx">here</a>).<br>
<br>
 OpenOffice.org Calc can open those files since version 2.0, but  only if the   extension is <span class="opt-name">&quot;.xml&quot;</span>.<br>
<br>
  This Excel-XML format is a convenient way to do dynamic Excel files by the template technique.<br>
  <br>
  <span class="title-1c"> PHP side:  </span> <br>
  <br>
  Example:<br>
<div class="decal txt-code">include(<span class="opt-value">'tbs_class.php'</span>);<br>
  <span class="opt-name">$TBS</span> = new clsTinyButStrong; <span class="opt-html">// The TBS engine</span> <br>
  <br>
  include(<span class="opt-value">'tbs_plugin_excel.php'</span>); <br>
  <span class="opt-name">$TBS</span>-&gt;PlugIn(<span class="opt-value">TBS_INSTALL</span>,<span class="opt-value">TBS_EXCEL</span>); <span class="opt-html">// Install the Excel plug-in </span><br>
  <br>
  <span class="opt-name">$TBS</span>-&gt;LoadTemplate(<span class="opt-value">'my_template.xml'</span>); <span class="opt-html">// Open the Excel-XML template </span><br>
  <span class="opt-name">$TBS</span>-&gt;MergeBlock(<span class="opt-value">'bz'</span>,<span class="opt-name">$my_data</span>); <span class="opt-html">// Merge the block </span><br>
  <span class="opt-name">$TBS</span>-&gt;Show() <span class="opt-html">// Force the result to bas donwload  as an Excel file. </span></div>
<br>
<span class="title-1b">Requirements:</span> 
<div class="decal">include the file <span class="tbsname">'tbs_plugin_excel.php'</span>. 
  This can be after the TBS object variable is created.<br>
This plug-in requires TBS version 3.0.5 or higher. </div>
<br>
  <br>
<span class="title-1b">Installation mode:</span> Manual. The plug-in should be installed before to call the LoadTemplate() method.<br>
<div class="decal">
Example:<br>
<br>
<div class="decal txt-code"><span class="opt-name">$TBS</span>-&gt;PlugIn(<span class="opt-value">TBS_INSTALL</span>,<span class="opt-value">TBS_EXCEL</span>);</div>
</div>
<br>
<span class="title-1b">Command syntax:</span> <br>
<br>
<div class="decal"> &bull; Change the name of the file for download:<br>

  <div class="decal txt-code"><span class="opt-name">$TBS</span>-&gt;PlugIn(<span class="opt-value">TBS_EXCEL</span>,<span class="opt-value">TBS_EXCEL_FILENAME</span>,<span class="opt-value">'file.xls'</span>); </div>
  <br>
  &bull; Enables the file to be displayed within the browser (Internet Explorer only):<br>
  <br>
  <div class="decal txt-code"> <span class="opt-name">$TBS</span>-&gt;PlugIn(<span class="opt-value">TBS_EXCEL</span>,<span class="opt-value">TBS_EXCEL_INLINE</span>);</div>
</div>
<br>
Other information: <br>
<div class="decal">- When the method Show() is called, then the merged template is output as a file for download. You can cancel the download by using the TBS property Render.<br>
- By default, the file to download has the same name as the template but with an &quot;.xls' extension. You can change the default file name using a command described above. </div>
<br>
<br>
<span class="title-1c">Template side: </span><br>
<br>
<span class="title-1b"> Create a new template:</span><br>
<br>
<div class="decal">You can choose one of those two ways: <br>
  &bull; Create a new document with Excel. Save it with choosing &quot;XML Spreadsheet (*.xml)&quot; for the File type in the dialog box.<br>
  <div class="decal">I suggest you to change the extension of the file for &quot;.xls&quot; 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. What ever, Excel will makes no problem to open your file. </div>
  or: <br>
  &bull; Use the <span class="opt-html"><span class="opt-name">empty.xls</span></span> file which is provided with this plug-in distribution.
  <br>
  <br>
Your template can have several sheets, and you can use all Excel formats for displaying you data. </div>
<br>
<span class="title-1b">Merging data items in cells:</span><br>
  <br>
  <div class="decal">Microsoft Excel assigns a type for each value placed into a cell (it can be String, Numeric, BateTime, 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 <span class="opt-name">ope</span>. This can be useful for calculating total, or using Excel formats for example. <br>
    <br>
    <table border="0" cellspacing="0" cellpadding="3">
      <tr>
        <td class="title-1b">Parameter</td>
        <td width="5" class="title-1b">&nbsp;</td>
        <td class="title-1b">Value forced to </td>
      </tr>
      <tr>
        <td><span class="opt-name">ope</span>=<span class="opt-value">xlNum</span></td>
        <td>&nbsp;</td>
        <td>Numeric</td>
      </tr>
      <tr>
        <td><span class="opt-name">ope</span>=<span class="opt-value">xlDT</span> </td>
        <td>&nbsp;</td>
        <td>DateTime </td>
      </tr>
    </table>
    <br>
    Examples:<br>
    <div class="decal">
      <table border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td class="opt-html">Current date:</td>
          <td>[var..now;<span class="opt-name">ope</span>=<span class="opt-value">xlDT</span>]</td>
        </tr>
        <tr>
          <td class="opt-html">Total amount: </td>
          <td>[var.amount;<span class="opt-name">ope</span>=<span class="opt-value">xlNum</span>]</td>
        </tr>
      </table>
    </div>
    <br>
  Remark: If the merged value is not corresponding to its type then it will produce an error when the document is opened.<br>
  The String type can display any value.</div>
<br>
  <br>
  <span class="title-1b">Merging blocks:</span><br>
  <br>
  <div class="decal">    Within an Excel-XML document, &lt;Table&gt; &lt;Row&gt; and &lt;Cell&gt; elements are use to dispose data into the Worksheets. It is quite similar to the HTML &lt;table&gt;&lt;tr&gt; and &lt;td&gt; elements. But a  &lt;Table&gt; is really tuchy to multiply (see section Debuggin below). <br>
    <br>
    Thus, the more commun TBS blocks you'll define will be <span class="opt-name">block</span>=<span class="opt-value">Row</span> when you want to exand rows, or <span class="opt-name">block</span>=<span class="opt-value">Cell</span> when you whant to expand columns. <br>
    Examples:<br>
      <div class="decal">
      <table border="1" cellspacing="0" cellpadding="3">
        <tr>
          <td class="title-1c">Title</td>
          <td class="title-1c">Price</td>
        </tr>
        <tr>
          <td>[b1.title;<span class="opt-name">block</span>=<span class="opt-value">Row</span>]</td>
          <td>[b1.price;ope=xlNum]</td>
        </tr>
        </table>
    </div>
</div>
<br>
<span class="title-1b">Managing formulas:</span><br>
<br>
<div class="decal"> 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.<br>
  <br>
&bull; If the formula is placed on the rigth or bottom of the merged block:<br>
<div class="decal">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 save relatively to the cell with the formula. <br>
	<br>
	Example:<br>
	<table border="1" cellspacing="0" cellpadding="3">

    <tr>
    	<td width="20" class="excelborder"><div align="center"></div></td>
    	<td class="excelborder"><div align="center">A</div></td>
    	<td class="excelborder"><div align="center">B</div></td>
    	</tr>
    <tr>
    	<td class="excelborder"><div align="center">1</div></td>
    	<td>[b1.title;<span class="opt-name">block</span>=<span class="opt-value">Row</span>]</td>
    	<td>[b1.price;<span class="opt-name">ope</span>=<span class="opt-value">xlNum</span>]</td>
    </tr>
    <tr>
    	<td class="excelborder"><div align="center">2</div></td>
      <td>&nbsp;</td>
      <td class="opt-html">=SUM(B$1:B1)</td>
    </tr>
  </table>
</div>
<br>
&bull; If the formula is placed on the left or top of the merged block: <br>
<div class="decal">The tip above won't fit in this case because both relative ans absolute reference will refer to the first cell of the block. So this plug-in give another dedicated trick for this.<br>
	<br>
	TBS parameter <span class="opt-name">ope</span>=<span class="opt-value">xlPushref</span> 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 <span class="opt-html">+N(&quot;&quot;)</span> function, this enables the Excel formula to stay valid. If your formula deals with strings, you can use <span class="opt-html">&amp;N(&quot;&quot;)</span>.<br>
	<br>
Example:<br>
	<table border="1" cellspacing="0" cellpadding="3">
		<tr>
			<td width="20" class="excelborder"><div align="center"></div></td>
			<td class="excelborder"><div align="center">A</div></td>
			<td class="excelborder"><div align="center">B</div></td>
		</tr>
		<tr>
			<td class="excelborder"><div align="center">1</div></td>
			<td>&nbsp;</td>
			<td class="opt-html">=SUM(B2:B2)+N(&quot;[b1.#;ope=xlPushRef]&quot;)</td>
		</tr>
		<tr>
			<td class="excelborder"><div align="center">2</div></td>
			<td>[b1.title;<span class="opt-name">block</span>=<span class="opt-value">Row</span>]</td>
			<td>[b1.price;<span class="opt-name">ope</span>=<span class="opt-value">xlNum</span>]</td>
		</tr>
	</table>
</div>
</div>
<br>
  <br> 
  <span class="title-1b">Debuging the result of the merge: </span><br>
  <div class="decal">TBS error messages will cause the result to not be a downloadable file because  error messages are prompted before header information are sent.<br>
    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.<br>
    <br> 
    Here is some interesting information about the Excel-XML syntax: <br>
    <br>
    -       One document contains one or several <span class="opt-html">&lt;Worksheets&gt;</span> elements.<br>
    - One <span class="opt-html">&lt;Worsheet&gt;</span>  contains one or several <span class="opt-html">&lt;Table&gt;</span> elements.<br>
    - One <span class="opt-html">&lt;Table&gt;</span> contains one or more <span class="opt-html">&lt;Row&gt;</span> elements.<br>
    - One <span class="opt-html">&lt;Row&gt;</span> contains one or more <span class="opt-html">&lt;Cell&gt;</span> elements. <br>
    <br>
<span class="title-1b">Diagram:</span><br>
&lt;Worksheet ... &gt; <br>
&nbsp;&nbsp;&lt;Table ... &gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&lt;Row ...&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&lt;Cell ...&gt; &lt;/Cell&gt; &lt;Cell ...&gt; &lt;/Cell&gt; &lt;Cell ...&gt; &lt;/Cell&gt;<br>
&nbsp;&nbsp;&nbsp;&nbsp;&lt;/Row ...&gt;<br>
&nbsp;&nbsp;&lt;/Table ... &gt;<br>
&lt;/Worksheet ... &gt;
  <br>
  <br>
&bull; Microsoft  Excel decides how to split your data in &lt;Tables&gt; , &lt;Cell&gt; and &lt;Row&gt;. Or you can decide it by yourself by manually changing the source of the template. <br>
&bull; Take care that your items may be separated in several &lt;Table&gt; elements in a Worksheets, this  happens mostly when data items are separated by numerous empty cells.<br>
&bull; &lt;Tables&gt; elements should not be repeated by merging TBS block because their first &lt;Row&gt; and &lt;Cell&gt; elements may contain absolute indexes for positioning. <br>
&bull; They are some other XML elements but not really interesting for merging TBS blocks. </div>
	<br>
	<span class="title-1c"><a name="changelog"></a>Changelog:</span><br>
	<br>
	<table border="0" cellspacing="2" cellpadding="4">
		<tr>
			<td valign="top"><span class="versioning">Version 1.0.3:</span>&nbsp;</td>
			<td valign="top">Monor fixes in the HTML header which force the file to download. </td>
		</tr>
		<tr>
			<td width="90" valign="top"><span class="versioning">Version 1.0.2:</span>&nbsp;</td>
			<td valign="top">Bug fixed about special sections (like <span class="opt-name">headergrp</span>, <span class="opt-name">when</span>, <span class="opt-name">nodata</span>, ...  ) that could  be bad placed after a MergeBlock().</td>
		</tr>
	</table>
<br>
</body>
</html>
Return current item: TinyButStrong plug-in for Excel