Create Reports with Scribus (Part 1)

Why?

Taking content from a web application and transferring it onto paper can be a difficult problem. At least if the user wants to modify things before they go to the printer, especially on a Linux desktop that wouldn't run Adobe's Creative Suite natively, even if you wanted to spend the money on it. To show the solutions I came up with I want to look at two specific use-cases:

1. Filling out a standard template, such as a coversheet.
2. Printing barcode labels on common sets of labels such as 5160.

In this first part I'm assuming that direct access to an SQL database is possible, this might be the case on a local intranet. The next post will focus on options within a common CMS. The situation now in principle looks as follows for both use-cases:

Data in MySQL → ??? → Standardized document ready for print.

Scribus includes a scripting plugin that makes it possible to automate tasks with Python. While an upcoming version promises much improved scripting support, this tutorial will deal with the possibilities in the current 1.3.5.x releases.

Common functionality

Before we start to mess around with the internals of the document we want, we first have to introduce a preamble for the database connection and similar things. Make sure that you have the relevant Python modules installed. For instance, python-mysqldb (as it is known under Debian/Ubuntu) is required for this example. A general outline might look as follows:

import scribus
import MySQLdb scribus.openDoc("yourdocument.sla")
db = MySQLdb.connect(host="dbhost" , user="youruser", passwd="yourpw", db="yourdb") 
cursor = db.cursor()

While those blocks above should just work, it's a lot nicer to get some feedback where the script failed, should one condition not be met. Feel free to use this preamble which tries each statement and pops up useful exceptions. Put it all in a def block and you can reuse it for all your Scribus scripts.

After that, you would only need to run your SQL query against the cursor. This will retrieve the result set you will use a little later on to customize your document:

cursor.execute (cmd)

A few other useful ones:

cursor.fetchone() // fetch one row, easy to iterate through
cursor.rowcount() // number of rows in result set
cursor.close() //self-explanatory like the next one
db.close()

Coversheet

If you have a limited number of fields and want to quickly automate an exisiting Scribus document all you need is the names of the objects (i.e. text frames) that you want to show. This one-liner does all the work for you:

scribus.setText("your text","object name")

If you don't know an object name, just select it in Scribus and under Properties it's the first field under X,Y,Z. In most cases though you will have to provide your script with some sort of input to use on the database, here valueDialog is what you need. So an example where you need to input a string (instead of an integer, where the str isn't needed), you would use:

choice=str(scribus.valueDialog('Popup Title','Please enter the item for which you want a coversheet'))

Warning: You need to make sure you properly escape your string/integer/etc for execute(), think %s, otherwise you will meet Little Bobby Tables sooner or later.

Barcode labels

When we look at something like a list of barcodes that we want to print on label sheets, normally we have no clue about the exact number of labels; it's definitely not 1, such as in the example above. Logically, just creating a complete Scribus document and filling in the text for placeholders won't work, we have to create the fields dynamically.

Before we can create the fields though, we first have to figure out the pages, let's assume there are 3 columns of labels with 10 rows each. The following will shows us how many pages we are actually working on. It gives us the number of pages, makes it one if it's less than 30 and adds another if a fraction of 30 is involved otherwise.

num=cursor.rowcount / 30
numr=cursor.rowcount % 30
if num == 0:
    num=1
elif numr != 0:
    num=num+1

The following code will start our iteration and add a consecutive page to the end of the document, each time it loops until we are on the final page. Notice that we not only have to create a page but also make it the active one by using gotoPage. The variables xnull and ynull are starting points on the page from which we can calculate the labels.

i=0
for f in range(1,num+1):
    if f > 1:
	scribus.newPage(-1,"Master")
    scribus.gotoPage(f)
    xbase=xnull
    ybase=ynull

They way our loops are set up the script will run through each row for three fields and then go to the next row, resetting the x value. While it makes sense to use a variable such as w to move the starting point of the label as necessary, using variables for the createText is often not worth the effort, especially with half a dozen fields, offset by a slight margin.

      for j in range(10):
	xbase=xnull
	for k in range(3):
	    row=cursor.fetchone()
	    if row:
		title=scribus.createText(xbase+1.34, ybase+.4, 1.15 , .45)
		i=i+1
		xbase=xbase+w
	ybase=ybase+h

Since we did not rely on already present frames, we will often need to set particular options for these fields as well, below is an example for our title frame. To actually produce barcodes on these labels, at least for trivial ones like Code39, we can just set a matching font here.

scribus.setFont("DejaVu Sans Mono", title)
scribus.setTextAlignment(1, title)
scribus.setFontSize(8, title)

Scribus can be rather slow when creating a few hundred frames (thousands work but you can get a coffee while it does) so the progress meter is a nice addition, simply add scribus.progressTotal(cursor.rowcount) before your main loop begins and invoke scribus.progressSet(i) each time you finished a label. I also used scribus.setRedraw(False) to improve performance but I'm not sure it still makes much of a difference.

In the next part we will take a look at what you can still use Scribus for if you rely on a common CMS and cannot just access MySQL to get data and thus also cannot use Scripter.