Acces to db made easy

- Home
- Sqlkit
  - Sqlkit
  - Widgets
    - Mask view
    - Table view
  - Browsing data
    - Constraints
    - Filters
    - Totals
  - Editing data
    - Completion
    - Validation
    - Relationships
  - Printing
  - Advanced configuration
    - Fields
    - Views
- Sqledit GUI
- Support & Tutorials
  - Sqledit - the application
  - Sqlkit - features' tour
  - Installing instructions
- Layout
- Download
  - Download
  - Changelog
  - Backward incompatibilities

- Prev: Field Widgets
- Next: Sqlkit & Sqledit

# Printing

Printing in sqlkit uses the `oootemplate` module that is distributed along with sqlkit but could be used in a totally independent way and does not depend in any way on other parts of sqlkit.

## OpenOffice.org template

This module makes it possible to use an oasis `.odt` document as a template to produce documents via the use of an openoffice server (i.e. an istance of openoffice that listens for connections by clients).

It uses the uno module that comes with openoffice. In the following image you can see how the template looks before rendering and how the `pdf` produced looks. The red circle stresses the marker that makes the template produce many lines for each input line: one for each object in context.

This rendering can be obtained by code very similar to this example

### The template

An openoffice writer document (`.odt` extension) can be used as template. No particular templating skills are required by the template designer, just ability to create an openoffice document and a list of variable’s names that must be used. Clearly this list must be provided by the programmer to the designer.

#### scenario

oootemplate will substitute variables values where needed.

    Dear Mr. $user.name $user.last_name,

    Today $date we  received an order for $n books:

    +-----------------------------+----------------+----------+----------+
    |Title                        |Author          |N. pieces |$currency |
    +-----------------------------+----------------+----------+----------+
    | ++$title                    |$author         |$qty      |$price    |
    +-----------------------------+----------------+----------+----------+

This is an example of a template rendered as possible with ascii art.

There are 2 different substitutions that can be done:

> 1. simple substitutions ($user, $date, $currency, above - yellow in the image)
> 2. multiline substitutions (the book entries above, starting with ++ - green in the image)

the first refers to substitution of a single value that is already present in the document, the second refers to the insertion of several rows according to a list of fields that are probably in a table.

The variables defined in the table should be repeted in loop for each book provided in the `context`, that implies an increment of the number of rows of the table. The expected output resembles what follows:

    Dear Mr. Alessandro Dentella,

    today June, 2 2008, we received an order of 2 books:

    +-----------------------------+----------------+----------+----------+
    |Title                        |Author          |Copies    |Euro      |
    +-----------------------------+----------------+----------+----------+
    | Q                           |Luther Blisset  |1         |10        |
    +-----------------------------+----------------+----------+----------+
    | Il sistema periodico        |Primo Levi      |2         |8         |
    +-----------------------------+----------------+----------+----------+

As for any template system a special syntax is needed to allow people to create loops. We are constrained to what can be done by a simple user of openoffice (no programming at all) so we choose to use a MULTI_LINE_MARKER in the default form of a ‘++’ (red circle in the image) .

To make things more complicated it’s clear that the person that created the template (the .odt document), may have used a table just for formatting reason so that substitution of type a. can be in an openoffice table or not.

#### implementation

Substitution of type `a.` above are done using search and replace functionality of openoffice API, while substitution of type `b.` are implemented as a loop on table’s rows and cell in 2 different ways: one that preserves the style of each word of the cells and one that doesn’t. The former uses getTrasferable() method on each cell and is slower.

You can switch from one to the other setting Template’s attrribute `Template.preserve_styles`

The pattern used to detect what is a table can be customized. While the default is ‘$’ as in the shell or in python template system (and perl and php...) since you may have the ‘$’ symbol in your document you may want to customize it. See Template’s method `set_pattern`.

#### context

The context is the object that contains the mapping variable/value to be used. It needs an argument -a dict- that holds the values.

oootemplate allows you to have more that one tables in your document. That means you can implement easily things as:

    Dear Mr. $user.name $user.last_name,

    you can order these books at 20% discount

    +-----------------------------+----------------+----------+----------+
    |Title                        |Author          |N. pieces |$currency |
    +-----------------------------+----------------+----------+----------+
    | ++$title                    |$author         |$qty      |$price    |
    +-----------------------------+----------------+----------+----------+

    or these books at 50% discount

    +-----------------------------+-----------------+----------+----------+
    |Title                        |Author           |N. pieces |$currency |
    +-----------------------------+-----------------+----------+----------+
    | ++$title                    |$author          |$qty      |$price    |
    +-----------------------------+----------+------+----------+----------+
    |Title                        |Author    | Extra|N. pieces |$currency |
    +-----------------------------+----------+------+----------+----------+
    | ++$title                    |$author   | $x   |$qty      |$price    |
    +-----------------------------+----------+------+----------+----------+

In this example we have 3 lines that start with ++, that means that will be conseidered prototipes for new lines. For each of these 3 rows there need to be a list of objects (books in the example) in the context.

Since Openoffice-org tables have names, we wil use that name as a key in the context for the value. That’s enought for the first table (20% discount) not for the second where we have 2 lists in a table. To cope with this case, we can put as value a dict with entry an integer that indicates the position starting from 1 (see example below)

##### name mapping

Occasionally the name of the variable will be too long to fit in the space that you want to allocate. You can translate a shorter name to the real name in the dict context.translate (see example in the demo), blue circle in the image.

This way you can hide the complexity of some variable name. Note that you can translate both `$rs.manager.address` in `$addr` or `$director.last_name` in `$d.last_name`.

#### output

All output accepted by openoffice filters, you’re probably using `.odt` or `.pdf`

#### example

A tipical sample code is:

    import ooootemplate as oo

    tmpl = oo.Template('/tmp/mytemplate.odt', server='127.0.0.1', port=2002)
    context = oo.Context({
        'user' : user,
        'date' : date(2008, 6, 2),
        'currency' : 'Euro',
        'Table1' : (book1, book2, ...)  # lazy assignement (simple tuple)
        'Table2' : (
             (book21, book22, ...),     # correct assignement (list of tuples)
             (book31, book32, ...),
             )
        })

    tmpl.render(context)
    tmpl.save_as('/tmp/new_document.pdf')

### API

#### Context

class `sqlkit.misc.oootemplate.``Context`(content, lazy=True)

  A context used to render a template. It contains both the data and the way to substitute variable with the data.

  `__init__`(content, lazy=True)

    Parameters: | content – a dict with keys the variable names lazy – boolean. If True (default) invokes _implement_lazy_tables to allow the list of objects for a table rendered to be directly set as value of the table_name entry (rather than a list of lists). See example `Table1` is lazy `Table2` is not. The goal is to prevent common errors rather than promoting lazy writing. When you only have a single list of objects you may easily forget that you may have more than one. The assumption is that you don’t normally have lists as values of context (other that for tables). While probably true, should you need lists as contetxt values, you can just set lazy=False.

#### Template

class `sqlkit.misc.oootemplate.``Template`(filename, server='127.0.0.1', port=8100, headless=False, oo_context=None, preserve_styles=False)

  The class template that connects to a server (or starts a local one), read a document, parses the document to find tables

  `__init__`(filename, server='127.0.0.1', port=8100, headless=False, oo_context=None, preserve_styles=False)

    only the `filename` is needed if the server is local. If we already have a template on the same server we can reuse the `oo_context`

    Params filename:
    | the template filename in the server’s filesystem
    Parameters: | server – a server name port – the port to connect to headless – if False, disables the headless mode. oo_context – the openoffice context (not to be confused with `oootemplate.Context`). The oo_context` plays the role of the connection to the server preserve_styles – use `TemplateWithStyles` to enforce preservation of styles in each word of the cell when duplicating rows

  `render`(context)

    substitute all the variables with values from context

    Parameter: | context – the Contex instance to be used

  `save_as`(filename, local=None)

    save the template using save_as capability of openoffice.

    Parameter: | filename – filename in the server‘s filesystem. The extension is used to detect the file type as in regular openoffice use.

  `set_pattern`(pattern, oo_pattern)

    Set the pattern to detect what is a variable to be substituted

    Parameters: | pattern – the pattern with syntax suitable for module `re` module. It must define at least 2 groups: `var_name` and `match` pointing respectively to the name of the variable and the whole match. Default is `(?P<match>\$(?P<var_name>[^ ]+))` oo_pattern – the pattern with syntax suitable for openoffice regexp search. It can only use the openoffice syntax. Default is `$[^ ]+`

  `VARIABLE_PATTERN`
    the pattern for python variable detection. it’s a regular expression read `set_pattern` for details
  `VARIABLE_PATTERN_OO`
    the pattern for openoffice variable detection. read `set_pattern` for details
  `MULTI_LINE_MARKER`
    the pattern used to tel when a multiline line starts. Defaults to ++
  `document`
    the openoffice document from the server
  `oo_context`
    the connection with the server. This can be reused between templates
  `search`
    the openoffice SearchDescriptor
  `preserve_styles`
    Preserve style of each word in the cell when cloning rows. This will slow down the process so it’s disabled by default

#### Table

class `sqlkit.misc.oootemplate.``Table`(oo_table, template)

  table object detail on the API exposed by uno: http://api.openoffice.org/docs/common/ref/com/sun/star/table/module-ix.html

  `__init__`(oo_table, template)

    Parameters: | oo_table – the openoffice table object template – the `oootemplate.template` object in which this table is

class `sqlkit.misc.oootemplate.``TableWithStyles`(oo_table, template)

  A Table that clones rows preserving style info even if several different styles are used within the same cell. This process is slower so it’s not active by default: set Template’s arg `preserve_styles` to True to enable it (see ex. N. 5).

  `__init__`(oo_table, template)

    Parameters: | oo_table – the openoffice table object template – the `oootemplate.template` object in which this table is