Create OpenXML and OpenDocument reports with OpenTBS

It’s a common requirement in web applications to be able to export data to the end users. The most common format is CSV but in more advanced applications you need a complex system that’s able to create reports with charts, tables and in a specific disposition for the users.

In this article I present TinyButStrong, and OpenTBS, that allow you to create and edit OpenXML and OpenDocument reports in PHP.

1. The issues with PHP reports and what is TinyButStrong
2. Create the report template (odt, docx, ods, xlsx)
3. Process the template into a final document (PHP)
4. Additional information on TinyButStrong and OpenTBS

1. The issues with PHP reports and what is TinyButStrong

Producing reports in PHP has always been the Achilles’ heel. While there are specific classes in Java or C# that can generate OpenXML and OpenDocument documents, that’s not the case in PHP since it’s a scripting language.

Most PHP applications tend to use the simplest solution and produce CSV or PDF files. However, if you want to be able to process OpenXML or OpenDocument files, to enable the end users to edit the report templates, you won’t be able to use PDF since it has a closed format. And, although the arrival of PHPExcel and, more recently, PHPWord have improved this situation, these tools consume too many resources and don’t provide enough flexibility (in my opinion).

To overcome this, I’m writing about TinyButStrong (also known as TBS), a template system based on HTML and XML and the plugin OpenTBS that provides the ability to create and edit OpenXML (docx, xlsx) and OpenDocument (odt, ods) documents.

OpenXML and OpenDocument files are basically a zip file that contains XML code and while there’s a ECMA standard, there are some major differences between Microsoft OpenXML and OpenDocument.

So, how can TinyButStrong and OpenTBS help you? Well, these classes are able to manipulate OpenXML and OpenDocument files and support the following file extensions: odt, ods, odg, odf, odm, odp, ott, ots, otg, otp, docx, xlsx, xlsm, pptx.

2. Create the report template (odt, docx, ods, xlsx)

For demonstration purposes, I’ve created the report template below with Microsoft Word 2013.

The report template contains the indication of which variables should be replaced whenever the document is processed which, in this example are, record_id, created_at, fullname and birth_date. Note the block. before the variable names. That’s because TBS uses blocks as a way of grouping data.

Imagine you want to create a report with a movie information, you could have a block for generic information (movie name, release date, rating, director, …), another with the cast information (name, nationality, age, …) and another with the review’s information (date, description, ratting, author).

3. Process the template into a final document (PHP)

It’s simple enough to process the document in PHP. Once you understand the block’s purpose, you simply need to provide an array with the information of each block and merge that data with the document.

// Include TinyButStrong and OpenTBS classes
include_once('tbs_class.php');
include_once('tbs_plugin_opentbs.php');

// Create a new TBS instance and load OpenTBS plugin
$tbs = new clsTinyButStrong;
$tbs->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);

// Useful for debugging purposes. Displays the errors
$tbs->NoErr = false;

// Set the template report path. In this case we're using docx but we could be using odt, xlsx or ods
$templatePath = 'report-template.docx';

// Load the template to TBS
$tbs->LoadTemplate($templatePath, OPENTBS_ALREADY_UTF8);

// Fill a mock array with the data we want in the final document. In a real application
// this would come from a database or any other storage system or user input.
$blockInfo = [
    'record_id'  => '123',
    'created_at' => date('d-m-Y'),
    'fullname'   => 'Geekalicious',
    'birth_date' => '2015-01-01',
];

// Important: The MergeBlock function receives an array of blocks
// so you will need to pass an array of arrays
$dataToMerge = [
    $blockInfo
];

// Merge is where the system replaces the variables for the given values.
// We need to specify the block's name (in our example, "block").
// @see https://www.tinybutstrong.com/manual.php#php_mergeblock
$tbs->MergeBlock('b', $dataToMerge);

// After the merge we simply save the document in a given location or output it to the user.
$processedPath = 'processed-document.docx';
$tbs->Show(OPENTBS_FILE, $processedPath);

Each code block is commented so you’ll have a pretty good idea of how the system works.

4. Additional information on TinyButStrong and OpenTBS

I began to use these classes as of 2011 and, since then, I haven’t found a better way to create OpenXML / OpenDocument reports in PHP. Even this year (2015) I re-searched for a better solution but it’s still nonexistent. That said, you should be aware of the primary issues with this system:

• The manual is hard to read and comprehend.
• You’ll have a bit of a hard time to master the template’s language and to learn all the potentialities.
• The code itself should be revised (and should support Composer).

Nevertheless, TBS’ flexibility is such that I can safely say that, in this moment, it’s the best way to generate reports in PHP.

Here’s the link to the online demo and a screen shot of a real letter template that’s in production:

In the next article I’ll show you how to convert the document into PDF. If you have any issues just drop me a line (see About us).