Location: PHPKode > scripts > PHPExcel wrapper for populating XLS files with user data > printform-xls.en.htm
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<html>
<head><title>printform-xls : PHPExcel wrapper for populating XLS files with user data
</title>
<meta http-equiv="content-type" content="text/html; charset=iso-8859-1">
<style type="text/css">
<!--
body     { font-family:verdana,arial,helvetica;font-size:12px; }
pre { font-family: verdana,arial; font-size:11px; color:#0000FF;
      background-color: #EFEFF0; border: 1px solid #D0D0F0; padding:6px;
}
td   { font-family:arial,helvetica;font-size:11px;}
td.head  { background-color: #FBFBFE; border: 1px solid #ccf; font-weight:bold; padding-left:6px;}
tr.odd   { background-color: #F0F0F8; }
tr.even  { background-color: #E7E7F5; }
h4       { background-color: #E0E0F0; text-align: left; font-size:12px; }
h5       { background-color: #E0E0F0; text-align: left; font-size:11px; padding-left:8px;}
.red     { color:red; font-weight: bold;}
.details { background-color: #fbfbff; border: 1px solid #ccf; margin: 20px 10px; padding:8px; }
-->
</style>
</head>
<body>
<!-- DOC BEGIN -->

<h3>printform-xls : PHPExcel wrapper for populating XLS files with user data</h3>

<p>
This class written for reading "template" XLS file, loading "configuration" from prepared XML file,
filling the file with user data (populating) and sending result XLS body to the client's output stream (or saving to the file).
Worksheets in the output Excel file can be protected by user password.
The great PHPExcel classes are used for reading and writing XLS files, they not included in this distributive and
should be downloaded from author's site <a href="http://www.codeplex.com/PHPExcel" target="_blank">codeplex.com</a>.
</p>

<br />Currently the class has following features:
<ul>
  <li>Auto-converting of all "string" field values if they come in charset other than UTF-8.
  </li>
  <li>User-defined "convertor" function can be defined for any field, to make good-looking value.
  </li>
  <li>"Date" values supported (they encoded separately, and excel cell format is adjusted with user predefined
    date format, like 'dd/mm/yyyy'). Automatic converting from 'string' values is done if possible.
  </li>
  <li>XLS files with multiple worksheets supported.
  </li>
  <li>Worksheets and workbook protecting support (as they supported in PHPExcel).
  </li>
</ul>

<h4>Example : Creating XLS file from template and configuration XML</h4>

<br /><br />First, remember to alwais include PHPExcel modules in your project, otherwise this class won't work.
The main folder containg PHPExcel classes, should be added to your PHP 'Include_path' (on hosting provider side,
this is done by set_include_path() function ).

<pre>
require_once('PHPExcel.php');
require_once('PHPExcel/IOFactory.php');
require_once('PHPExcel/Reader/Excel5.php'); # Or other Excel formats if needed
</pre>

Let's suppose we have a configuration file (it contains all worksheet definitions - with field names, types and positions
in the sheet[s]) like this (cfg-test.xml):
<pre>
&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;printdef&gt;
  &lt;version&gt;1.0&lt;/version&gt;
  &lt;description&gt;sample format for testing&lt;/description&gt;
  &lt;dateformat&gt;dd/mm/yyyy&lt;/dateformat&gt;
  &lt;stringcharset&gt;WINDOWS-1251&lt;/stringcharset&gt;
  &lt;templatefile&gt;config-test.xls&lt;/templatefile&gt;
  &lt;sheets&gt;
   &lt;sheet offset="0"&gt;
    &lt;field name="last_name" col="3" row="5" type="string" convert="AddBraces" /&gt;
    &lt;field name="first_name" col="3" row="7" type="string"/&gt;
    &lt;field name="birthdate" col="3" row="9" type="date"/&gt;
    &lt;field name="address" col="3" row="11" type="string"/&gt;
    &lt;field name="resident" col="3" row="13" type="bool"/&gt;
    &lt;field name="resident" col="5" row="15" type="bool-invert"/&gt;
   &lt;/sheet&gt;
  &lt;/sheets&gt;
&lt;/printdef&gt;
</pre>

Here is a PHP code for populating XLS with data:
<pre>
require_once('printform-xls.php');
require_once('PHPExcel.php'); // We use PHPExcel classes !
require_once('PHPExcel/IOFactory.php');
require_once('PHPExcel/Reader/Excel5.php');

# our data:
$excel = new CPrintFormXls(array(
   'configfile' =&gt; 'cfg-test.xml'
  ,'outname'    =&gt; 'myfile-test.xls'
  ,'resident'   =&gt; 1
));

$data = array(
   'last_name'=&gt;'Smirnoff'
  ,'first_name' =&gt; 'Ivan'
  ,'birthdate'  =&gt; '1970-04-18'
  ,'address'     =&gt;'Russia, Moscow, Usacheva street, 42-90'
  ,'resident' =&gt; 1
);

$excel-&gt;AddData($data); // populate XLS with this data
$ok = $excel-&gt;Render(); // echoes generated XLS to the client browser

if(!$ok) echo $excel-&gt;GetErrorMessage(); // something goes wrong ?
exit;

// user function for converting some field values
function AddBraces($param) {
    return "[ $param ]";
}
</pre>

<h4>CPrintFormXls methods list</h4>

<b>CPrintFormXls($param='' [, $data [, $outname [, $tofile]]])</b> - class constructor.
<br />$param - can be a string, that passes configuration XML file name, or (preferred way)
<br />all parameters can be passed as an assosiative array in the first parameter.
(it may contain any combination of following pairs  'key'=&gt;value).

<br />
<table>
<tr>
  <td class="head">Key</td><td class="head">Description</td>
</tr>
<tr class="odd">
  <td><b>template</b>
  </td>
  <td>The full path/name of the source XLS tremplate file. Tested file format is "Excel5" (i.e. Excel 95- Excel 2003)
  </td>
</tr>

<tr class="even">
  <td><b>outname</b>
  </td>
  <td>Output file name. Client will see this name in the "Save as" dialog. If non-empty "tofile" parameter passed,
    this name is used for saving to the disk.
  </td>
</tr>

<tr class="odd">
  <td><b>tofile</b>
  </td>
  <td>By default genereated XLS file echoed directlry to the client "stream" (so the browser will ask to open or save a file)
    If non-empty value passed in 'tofile' parameter, file will be created on the disk (with name as set in 'outname' parameter),
    without echoing result to the client.
  </td>
</tr>

<tr class="even">
  <td><b>configfile</b>
  </td>
  <td>Full path/name of the configuration XML file, that stores info about all data field names and positions in XLS sheet(s),
  and other parameters (see <a href="#XML_config">file definition</a>).
  If configfile parameter not passed constructor, You will have to call LoadConfig() method explicitly.
  </td>
</tr>

<tr class="odd">
  <td><b>outfmt</b>
  </td>
  <td>Output file format (and type). Any type of XLS files supported by PHPExcel is allowed.
  If you use other than 'Excel5', format, you have to include respective PHPExcel classes in your script.
  <br />BTW, 'pdf' format is allowed, to. As PHPExcel contains classes CPDF for PDF writing, so it can be chosen as output format.
  But this feature was not hardly tested and may create buggy results on "complex" XLS files.
  </td>
</tr>

<tr class="even">
  <td><b>protectsheets</b>
  </td>
  <td>sets protection for all worksheets in the output file. Remember, source XLS template should be unprotected !
  Protecting sheets mode can be changed by calling ProtectSheets() method.
  </td>
</tr>

<tr class="odd">
  <td><b>password</b>
  </td>
  <td>Common password for protecting all worksheets.
  </td>
</tr>

<tr class="even">
  <td><b>protectbook</b>
  </td>
  <td>sets protection on the workbook ('1' or true value turns it ON, zero/false turns OFF)
  Protecting workbook mode can be changed by calling ProtectBook() method.
  </td>
</tr>

<tr class="odd">
  <td><b>bookpassword</b>
  </td>
  <td>Protecting workbook password.
  </td>
</tr>

<tr class="even">
  <td><b>data</b>
  </td>
  <td>Associative array with user data. If not passed, you will have to call AddData(), after <b>CPrintFormXls</b> object created.
  </td>
</tr>

</table>

<br /><br />
<b>LoadConfig($cfgname)</b> loads configuration from prepared XML file. That file must be edited manually
by You as programmer or administrator. Here is it's full sample with all parameters and descriptions.
$cfgname can contain an existing XML filename, or the whole "well formed" XML body (since version 1.01).

<br /><br /><a name="XML_config">Configuration XML</a>
<pre>
&lt;?xml version="1.0" encoding="UTF-8"?&gt;
&lt;printdef&gt;
  &lt;version&gt;1.0&lt;/version&gt; /* format version, for compatibility reasons in the future */
  &lt;description&gt;sample format for testing&lt;/description&gt; /* just for my information */
  &lt;dateformat&gt;dd/mm/yyyy&lt;/dateformat&gt;         /* date cell format in output file, default is 'dd/mm/yyyy' */
  &lt;stringcharset&gt;WINDOWS-1251&lt;/stringcharset&gt;   /* source strings charset, needed to make correct UTF-8 values for output */
  &lt;templatefile&gt;config-test.xls&lt;/templatefile&gt;  /* path+filename of template xls file */
  &lt;protectsheets&gt;1&lt;/protectsheets&gt; /* turn on worksheets protection */
  &lt;protectpassword&gt;mypassword&lt;/protectpassword&gt; /* password for protecting worksheets */
  &lt;sheets&gt; /* Defining all "data" fields in the sheet, to be filled with user data */
   &lt;sheet offset="0"&gt; /* offset is 0-based sheet number in the XLS template */
    /* Attributes: "name" - field name; "col" is column number (0-based); "row" - row number (1-based) */
    &lt;field name="last_name" col="3" row="5" type="string" convert="AddBraces" /&gt;
    &lt;field name="birthday" col="3" row="11" type="date"/&gt;
    /* etc... */
   &lt;/sheet&gt;
  &lt;/sheets&gt;
&lt;/printdef&gt;

</pre>
Every "sheet" tag must have "offset" attribute, 0-based worksheet number in the template XLS file.


<br /><br />"Field" attributes:
<br /><b>name</b> - field name. If passed User data array contains element with such key, it will be used as data for the cell.
<br /><b>col</b> - column number, 0-based.
<br /><b>row</b> - row number, 1-based.
<br /><b>type</b> - optional attribute that defines data type.
<br />Supported field types are "<b>string</b>", "<b>date</b>", "<b>bool</b>" and "<b>bool-invert</b>".

<div class="details">
If "Date" values come as string, they are auto-converted from 'yyyy-mm-dd', 'yy/dd/yyyy' and 'dd.mm.yyyy' formats to XLS-specific integer value
(day number since 01.01.1900). Numeric values remain unconverted (supposed that programmer prepares and passes XLS date number).
<br /><br />"<b>Bool</b>" format : all non-zero values drives to place 'X' char to the excel cell, and space if empty.
<br>"<b>bool-invert</b>" does the opposite (i.e. '1' value creates space, '0' creates 'X') - due to this one passed data value can manage
two cells: one will be filled with 'V', and another one - with ' '. It can be useful for example for YES[ ] NO[ ] cells.
</div>

<br /><b>convert</b> - optional attribute that defines function name that will be used to convert data value before placing to into XLS.

<br /><br /><b>AddData($param, $pval=null)</b> - method adds user data to populate in output file.
$param can be a string with "field name" -  in that case second parameter should pass field value.
Or $param can be an associative array, with field names as keys.

<br />AddData() can be called multiple times before final Render() call.

<br /><br /><b>ProtectSheets($protect, $password='')</b> turns on ($protect=1 or true) or off (0|false) worksheets protection,
with passed password. Protecting sheets mode can be turned by 'protectsheets' parameter in constructor call, or by calling this method.

<br /><br /><b>ProtectBook($protect, $password=null)</b> turns on ($protect=1 or true) or off (0|false) workbook protection.
<br /><span class='red'>Attention</span>: i've noticed, that workbook protecting may not function correctly in current in PHPExcel version (1.7.3c),
so you'll have to test it if going to use.

<br /><br /><b>Render()</b> performs "rendering" the body of final XLS data to the output stream or file.

<br /><br /><b>GetErrorMessage()</b> returns text containing description of last occured error, if any.

<br /><br /><b>GetXlsObject()</b> returns internal PHPExcel object so programmer can manipulate it directly by calling PHPExcel methods,
for example, explicitly setting cell values, changing XLS document properties and so on.

<h4>Using examples</h4>

Example 1. Protecting worksheets in output file
<pre>
$excel = new CPrintFormXls(array(
   'configfile' =&gt; 'cfg-test.xml'
  ,'outname' =&gt; 'myfile-test.xls'
));

$data = array(
   'last_name'=&gt;'Smirnoff'
  ,'first_name' =&gt; 'Ivan'
# ...
);
$excel-&gt;AddData($data);

$excel =&gt;ProtectSheets(true,'mypassword'); // turn on protecting

$ok = $excel-&gt;Render();
</pre>

Example 2. Generating PDF file instead of XLS
<pre>
$excel = new CPrintFormXls(array(
   'configfile' =&gt; 'cfg-test.xml'
  ,'outname' =&gt; 'myfile-test.<b>pdf</b>' // Set correct output file extension
  ,'outfmt' =&gt; 'pdf' // set output format to PDF
));

$data = array(
   'last_name'=&gt;'Smirnoff'
  ,'first_name' =&gt; 'Ivan'
# ...
);
$excel-&gt;AddData($data);
$ok = $excel-&gt;Render();
</pre>

Example 3. Doing some work in Excel object directly
<pre>
$excel = new CPrintFormXls(array(
   'configfile' =&gt; 'cfg-test.xml'
  ,'outname' =&gt; 'myfile-test.xls'
));
# ...

$xlsobj = $excel-&gt;GetXlsObject();
$xlsobj-&gt;getSheet(0)-&gt;setCellValueByColumnAndRow(3, 17, 'This text inserted outside class !');

$excel-&gt;AddData($data);
$ok = $excel-&gt;Render();
</pre>

<br /><br />
<div style="text-align:right">Distributed under <a href="http://www.opensource.org/licenses/bsd-license.php">BSD license</a></div>

<h4>Links</h4>
<ul>
  <li><a href="http://www.codeplex.com/PHPExcel" target="_blank">PHPExcel classes</a>
  </li>
</ul>

<h4>Change log</h4>

<h5>1.01.005 (07/13/2010)</h5>
<ul>
  <li>Feature request: LoadConfig() method can receive the whole XML body as a parameter (instead of filename)
  </li>
</ul>

<h5>1.00.004 (07/07/2010)</h5>
<ul>
  <li>First release published
  </li>
</ul>

<!-- DOC END -->
<br />
<hr>
<div align=center><font size=-2>Copyright &copy; 2010 Alexander Selifonov, <a href="http://www.selifan.ru">www.selifan.ru</a>
</font></div>
</body></html>
Return current item: PHPExcel wrapper for populating XLS files with user data