OpenTBS - create OpenOffice and Ms Office documents with PHP

1. Introduction
2. Installing
3. Understanding principles
4. Synopsis and code examples
   • 4.1. Preparation of TinyButStrong Template Engine with the OpenTBS plug-in
   • 4.2. Method LoadTemplate()
   • 4.3. Method Show()
   • 4.4. Block Alias
   • 4.5. Charts - Change or read data of series in a chart
   • 4.6. Pictures - Change pictures in the document
   • 4.7. Tables - Delete or merge columns in tables
   • 4.8. Tables - Merge cells vertically
   • 4.9. Workbook - Merging data in spreadsheet cells
   • 4.10. Workbook - Read and writre ranges in a workbook
   • 4.11. Merge document fields
   • 4.12. Other merging commands
     • For all types of documents
     • For documents (ODT and DOCX)
     • For workbooks (ODS and XLSX)
     • For presentations (ODP and PPTX)
       
   • 4.13. Manage files in the archive
   • 4.14. Special options
5. Demo
6. Debugging your template
7. What to do if Zlib extension is not enabled with PHP?
8. License

1. Introduction

OpenTBS is a plug-in for the TinyButStrong Template Engine.

TinyButStrong is a PHP Template Engine which has special template syntax and allows you to design templates in their natural editing tools. But it normally works only for Text files, including XML and HTML.

With TinyButStrong and its plug-in OpenTBS, you can use the template engine to merge OpenOffice documents and Ms Office documents with lot of facilities. All OpenDocument Format (ODF) and Office Open XML (OOXML) can be merged with OpenTBS, and also XPS files (XPS is a PDF competitor provided by Microsoft). In fact, all zip archives containing Xml/Html/Text files can be merged with OpenTBS.

Since version 1.10.0, OpenTBS can also read data in workbooks (xlsx and ods files).

What is special to OpenTBS:

• Design your templates directly with OpenOffice or MS Office.
• No exe file needed to merge documents.
• No temporary files needed to merge documents.
• Output directly as an http download, a new file on the disk, or as a string (for file attachment for example).
• Works with PHP 8, PHP 7 and PHP 5.
• No PHP extension is required (If the Zlib extension is enabled it becomes easier to use templates, see more detail below)

You should know Template Engines and more specifically TinyButStrong to use OpenTBS.

2. Installing

Requirements:

• TinyButStrong version 3.12.0 or higher.
• PHP 8, PHP 7 or PHP 5.
• It is better to have the Zlib extension enabled on your PHP installation. If it's not, here is what to do.

Installation:

Just put the file "tbs_plugin_opentbs.php" with your PHP scripts.

3. Understanding principles

It is important to figure out that documents made with LibreOffice, OpenOffice and Ms Office (since version 2007) are technically zip archives containing XML files, even if the extension of the document is not ".zip". Those zip archives can contain other file types like pictures or sounds, but the document structure and the text contents are saved as XML files. The XML Synopsis summarizes the key entities of XML sub-files contained in LibreOffice, OpenOffice and Ms Office documents.

TinyButStrong can merge XML files, but cannot read zip archives by itself. The plug-in OpenTBS extends the TinyButStrong methods LoadTemplate() and Show() to make them working with zip archives. But you do not have to bother with it because OpenTBS is managing archives in a way that is quite invisible for you.

When the OpenTBS plugin is installed, the LoadTemplate() method becomes able to first load a zip archive (a LibreOffice, OpenOffice or Ms Office document), and then to load the contents of any XML or Text files stored in the archive. Then you can load an merge the contents of XML or Text files with all features of the TinyButStrong template engine. TBS can merge fields and blocks only for the current loaded sub-file, but OpenTBS make you very easy to manage sub-files and gives lot of facilities for special contents such as pictures, charts, ... . At the end, the Show() method does render the entire zip archive including modified stored files. The render can be done as an HTTP download, or a new file on the server's disk, or as a PHP string containg the binary of the result archive.

Since OpenTBS version 1.3, you can also add and delete files in the archive.

OpenTBS has automatic extension recognition. When you load a document which has one of the following extensions { odt, odg, ods, odf, odp, odm, docx, xlsx, pptx }, then the main XML file of the archive are automatically loaded, and some special character conversion are preset. For example, for all OpenDocument files, the stored file "content.xml" is automatically loaded.

Since version 1.6.0, if the extension is not recognized then OpenTBS also try to find the document by the sub-file presence. And if all fails, then you can force the document type using a special command.

4. Synopsis and code examples

4.1. Preparation of TinyButStrong Template Engine with the OpenTBS plug-in

include_once('tbs_class.php');
include_once('tbs_plugin_opentbs.php');

$TBS = new clsTinyButStrong;
$TBS->Plugin(TBS_INSTALL, OPENTBS_PLUGIN);

$TBS = new clsTinyButStrong;
$TBS->Plugin(TBS_INSTALL, 'clsOpenTBS');

4.2. Method LoadTemplate()

• Load an archive with the automatic extension recognition (explained above):

• Load an archive without the automatic extension recognition:

• Load an archive with a PHP file handle:

$handle = tmpfile();
fwrite($handle, $binary_contents);
$TBS->LoadTemplate($handle);

• Load an archive and one file stored in this archive:

• Load an archive and several files stored in this archive:

• Unload the current template, so the template file is no longer locked:

• Load a stored file from the current archive:

• Load an archive with special data conversion:

4.3. Method Show()

Render options for OpenTBS:

• Render the merged archive as an HTTP download: ($file_name is optional)

• Render the merged archive as an HTTP output with your customized HTTP headers:

• Render the merged archive as a new file saved on the server's disk:

• Render the merged archive as a PHP string:

4.4. Block alias

Block Alias are supported since OpenTBS version 1.8.0, they can work only with TBS 3.8.0 or higher.

The Block Alias are wrappers for defining TBS blocks on usual Libre Office and Ms Office entities. This helps to not referring directly to XML entities and to harmonize block definitions between types of documents. For example: you can use block=tbs:row instead of block=table:table-row.

Some Block Alias, such as tbs:page and tbs:section, are also defining an entity that you cannot have with normal XML entities.

Block alias available according to the type of document :

Notes (*)

• tbs:page in a DOCX: works with the two ways for defining a page break: (1) insert a section break doing a page break, or (2) use the paragraph property named page break before.
• tbs:page in a DOCX: the TBS tag using the alias tbs:page must not be placed in the last paragraph of the page if it uses a section break doing a page break. This is a knowed bug. Instead, use the paragraph property named page break before, or move the TBS tag in another paragraph.
• tbs:section in a DOCX: this alias points out avery kind of sections, including thoses defined with a section break doing a page break

4.5. Change or read data of series in a chart

• Change series in a chart:

Delete a category in a chart:

• Read series in a chart:

// Example :
array (
  'Series 1' => array(
      0 => array (0 => 'Category A', 1 => 'Category B', 2 => 'Category C'),
      1 => array (0 => '2', 1 => '2.1', 2 => false,),
  ),
  'Series 2' => array(
      0 => array (0 => 'Category A', 1 => 'Category B', 2 => 'Category C',),
      1 => array (0 => '7', 1 => '7.1', 2 => '6.9',),
  ),
  ...
); 

// Example :
array (
  'file_idx' => 17,
  'file_name' => 'Object 1/content.xml',
  'parent_idx' => 19,
  'parent_name' => 'content.xml',
  'series' => array (
    0 => array (
      'name' => 'Series 1',
      'cat' => array (0 => 'Category A', 1 => 'Category B', 2 => 'Category C',),
      'val' => array (0 => '2', 1 => '2.1', 2 => false,),
    ),
    1 => array (
      'name' => 'Series 2',
      'cat' => array (0 => 'Category A', 1 => 'Category B', 2 => 'Category C',),
      'val' => array (0 => '7', 1 => '7.1', 2 =>'6.9',),
    ),
  ...
); 

4.6. Change pictures in the document

• Change an internal picture with a new one:

4.7. Delete or merge columns in a table

4.7.1 Delete columns in a table

Parameter "ope=delcol" enables you to delete specified columns in a table in an Ms Word document (docx) or any LibreOffice document.

Limitations of parameter "ope=delcol" :

• It will produce a wrong result if there is any cell with a merged style before of within the columns to delete using OpenTBS.
• If used with other "ope" options, then it must be the last one. This is because it avoid the usual merging process. Example : ope=docfield,delcol.

You have to put a TBS field anywhere inside the table with the following parameters:

If a column number to delete is empty (''), or zero (0), or negative, or over the actual number of colmun int the table, then the value is ignored whitout raising error.

Parameter "ope=delcol" is supported since OpenTBS version 1.8.0.
Parameter "colset" is supported since OpenTBS version 1.10.0.
Parameter "colnum" is optional since OpenTBS version 1.10.0.

4.7.2 Merge dynamic columns in a table

Parameter "parallel=tbs:table" enables you to create dynamic columns in a table of an Ms Word Document or any LibreOffice document.

Limitations:

• Parameter "parallel=tbs:table" doesn't work with Ms Excel and Ms Powerpoint documents.
• Parameter "parallel=tbs:table" with OpenTBS will produce a wrong result if there is any cell with a merged style before of within the columns to merge with data using OpenTBS.

See the TBS manual and the OpenTBS demo for more details about how to use parameter "parallel".

Parameter "parallel=tbs:table" is supported since OpenTBS version 1.8.2.

4.8. Merge cells vertically

Limitations:

• Parameter "ope=mergecell" doesn't work with LibreOffice.
• Parameter "ope=mergecell" will produce a wrong merge if there is any cell with a merged style before of within the columns to merge with data using OpenTBS.

Parameter "ope=mergecell" can work only for a block merged on the rows of the table. Note that the TBS field with "ope=mergecell" does not display any value, it only produces the merge.

Parameter "ope=mergecell" is supported since OpenTBS version 1.8.0.

4.9. Merging data in spreadsheet cells

In speaksheets (Ms Excel or LibreOffice Calc), cells values may be formated but also typed. For example, a cell value may be typed as String, Numerical, Boolean or Date.

Unfortunately, as soon as you enter a TBS tag in a cell, the cell it is typed as String by the software. This may not be corresponding to the final value you wanted after the merging.

For those situations, TBS offers a parameter like "ope=cellType". The cell type will be changed during the merging and the merged value will be implicitly converted by TBS to fit to the expected type.

Example:

• [onload.x;ope=tbs:num] => will turn the cell as Numerical after $x is merged.

Parameters for merging data in spreadsheet cells:

The keywoks in the table above are supported since OpenTBS version 1.8.1.

In previous version you should use keywords: odsNum, odsBool, odsDate, odsTime, odsCurr, odsPercent, xlsxNum, xlsxBool and xlsxDate.

4.10. Read and write ranges in a worksheet

• Read a range in a worksheet

• Write a range in a worksheet (Will be available in a next version)

4.11. Merge document fields

(Supported since OpenTBS version 1.10.0)

Both DOCX and ODT files supports document fields that can be inserted anywhere in the document that is for example Mail Merge Fields.

In Ms Word, document fields are available using: ribbon Insert / Text / Quick Parts / Field…
In LibreOffice, document fields are available using: menu Insert / Field / Other fields…

For now, OpenTBS can replace only document fields whose type is Conditional.
For a DOCX, that is IF field.
For an ODT, that is Fonction / Conditional text.

When you’ve positioned your Conditional document field in your contents :

• in the placeholder for the condition, enter « 0 = 1 » for a DOCX (the spaces are mandatory), or « false » for an ODT.
• use a TBS field with the parameter ope=docfield in the placeholder for THEN,
• enter any text in the placeholder for ELSE. This text will be displayed until the TBS fields is merged.

When the TBS fields will be merged, the full document field will be deleted and replaced with the merged value.

Limitations of parameter "ope=docfield" :

• It's taken into account only if a value is merged. I.e. it’s a TBS field's parameter, not a bock's parameter.
• Other TBS fields in the document field will be deleted.

Example with DOCX:

IF 1 = 0 "[onshow.x_num;ope=docfield;frm=0,000.00]" "XX.XX"

Example with ODT:

Conditional text false [onshow.x_num;ope=docfield;frm=0,000.00] XX.XX

See the demo DOC and ODT for more details.

4.12. Other merging commands

The following commands are supported since OpenTBS version 1.7.0 :

4.13. Manage files in the archive

• Get all files in the archive:

• Get all files in the archive:

• Get all files in the archive:

// Example:
$user_func = array($my_object, 'translate');
$TBS->Plugin(OPENTBS_WALK_OPENED_FILES, $user_func);

• Check if a file does exists in the archive:

• Add any new file in the archive:

// OpenTBS >= 1.6.0
$TBS->Plugin(OPENTBS_ADDFILE, $Name, $Data, $DataType=OPENTBS_STRING, $Compress=true);

// Deprecated since OpenTBS 1.6.0
$TBS->Plugin(OPENTBS_PLUGIN, OPENTBS_ADDFILE, $Name, $Data, $DataType=OPENTBS_STRING, $Compress=true);  

• Replace an existing file in the archive:

• Delete an existing file in the archive:

// OpenTBS >= 1.6.0
$TBS->Plugin(OPENTBS_DELETEFILE, $Name);

// Deprecated since OpenTBS 1.6.0
$TBS->Plugin(OPENTBS_PLUGIN, OPENTBS_DELETEFILE, $Name);

• Reset all modifications in the archive:

// OpenTBS >= 1.6.0
$TBS->Plugin(OPENTBS_RESET);

// Deprecated since OpenTBS 1.6.0
$TBS->Plugin(OPENTBS_PLUGIN, OPENTBS_RESET);

4.14. Special options

Dealing with apostrophes:

•Make an optimized version of a template:

• Forcing the document type recognition:

• Retrieving the name of the current document:

5. Demo

The OpenTBS package includes a full set of runnable templates. Some templates can contain useful complementary information for designing.

You can test the demo at the web site: OpenTBS demo

6. Debugging your template

Since OpenTBS version 1.6.0, there are several commands for debugging. Please note that those commands do not exit the process.

There is also deprecated debug options:

Otherwise, here are some indications that may help for the issues you can met with merging:

a) The merged document is producing error messages when opened with its application (OpenOffice or Ms Office)

The most likely causes are:

• You've chosen the OPENTBS_DOWNLOAD render option but a php error message or any other unexpected content has been output before by PHP.

Activate the debug mode using the command OPENTBS_DEBUG_XML_SHOW, it helps to check PHP error message and other unexpected content.

• The merging has produced an invalid document or an invalid XML content in an XML file of the document.

Activate the debug mode using it helps to check the XML contents of merged files.

See section (b) below for more information in the XML structure of the files.

b) Some TBS tags are not merged

First be sure that the TBS tag is a in a part of the document which is loaded and selected. Since a sub-file is loaded, automatic block ([onload] and [onshow]) will be merged. And you need to have the sub-file selected in order to use MergeBlock() and MergeField() on it. See commands like OPENTBS_SELECT_*. For example, in Ms Word , header and footers are loaded, and the main document is loaded and selected, but your need to select back a header part in order to use MergeBlock() on it.

Then, it may happens that a TBS tag seems uniformly formatted, while in fact the inner XML is split because of small difference in format, small different spelling information, or other... In order to avoid inner split, you can select the TBS tags, then cut it, and then paste it back without formatting.  It is “Paste special / Unformatted text” in LibreOffice, or “Paste / Keep Text Only” is Ms Office.

For MS Word, you can also use “OpenTBS plug-in for Microsoft Word”, which is given with the standard OpenTBS package. This plug-in gives a tag cleaner tool.

c) The merged document is well opened by its application (OpenOffice or Ms Office) but the content is not designed as expected

First, you can have a look the demo templates, they contain examples and advices for each type of document.

And to go further: even if you can edit your template using directly OpenOffice or Ms Office, you will probably need to understand the XML tags and attributes to complete your merge. The file xml_synopsis.txt is a small synopsis of the XML structure you can found in the inner source of those documents. Have a look to it if you feel lost.

d) Go deeper in the debugging

You can view the inner source of a document using a zip software like 7-Zip. It allows you to open an archive even if the extension is not ".zip".

• Open the merged document with 7-Zip (or your other zip software),
• extract the main XML file (or another file that you've merged),
• then open the XML file in an Text Editor software.
• those XML files are usually saved with no line breaks, which make them hard to be read. Some Text Editors can reformat them. You can also use the debug mode to see the formatted XML.
• check the structure of the XMK, try some fix and arrangements, deleted suspicious parts, ...
• put the modified XML file back to the archive, and test if it's correctly opened with its application (OpenOffice, Ms Office)

7. What to do if Zlib extension is not enabled with PHP?

OpenTBS uses Zlib functions in order to automatically uncompress and recompress files stored in the zip archive. If Zlib is not enabled, then you have to use your own uncompress/compress tool, or to prepare the template to have files uncompressed in the zip archive.

Example to uncompress the "content.xml" file in an ODT document using 7-Zip:

1. open the ODT file with 7-Zip,
2. extract the "content.xml" file from the ODT file in the same folder than the ODT file,
3. close 7-Zip,
4. open 7-Zip, and change current directory to be the same as the ODT file
5. select the "content.xml" file and click on button [Add], or menu [File][7-Zip][Add to archive...],
6. a new window named "Add to archive" is opened,
   • replace the archive name with the ODT file name,
   • set the Compression level to "None",
7. click on [Ok].

If you re-open the ODT file with 7-Zip, you can notice that the size and the uncompressed size are the same.
If the file should be placed in a sub-folder of the archive, then open the archive and rename the file in order to move it in a folder. For example rename "manifest.xml" to "META-INF\manifest.xml" will move it into META-INF. But moving the file will no delete the one which has the same name in the target folder. You have to go and delete the old one.

8. License

OpenTBS is under LGPL (Lesser General Public License)