Charles Curley's Weblog

OpenOffice.org/LibreOffice and Labels

What is it with OpenOffice.org (OOo)/LibreOffice (LO) and labels and mail merge? OOo/LO have had mail merge and label creation for years. Both have always been awkward to use. For one thing, I always end up searching the net for a current set of instructions when I want to use either.

Worse, it's always buggy. With different bugs.

Every so often I need to make a set of tickets and ticket stubs. The tickets and stubs require continuous serial numbers printed on each one. The tickets and stubs have to be attached until the seller sells the ticket. The serial numbers must be continuous so that we can account for all tickets, sold and unsold.

I found a set of instructions for creating labels with serial numbers, and they work if you only want to use each serial number only once. However, when I tried using each number twice (ticket and stub), everything worked, except when LO printed to the output file: at each page boundary it skipped a serial number. Arrrggh!

My solution was to abandon the mail merge capabilities of LO entirely. I created a page of tickets and stubs, five up, for use on business card stock. I included everything except the serial number. I adjusted the page style to use 8.5x11 paper, not the weird size OOo came up with. I then exported that to PDF as tickets.raw.pdf.

I then wrote a program in Perl to apply the serial numbers and create an appropriate number of PDF pages. The last step is to use pdftk, the PDF Tool Kit, to stitch the PDFs together into one monster output file.

This is not a solution suitable for the average secretary. Neither is searching the Internet and trying and rejecting five different sets of instructions.

For the purient minded, the program is as follows:

#!/usr/bin/perl -w

# Given the raw tickets in PDF, create a new set of numbered tickets.

use lib "/usr/local/share/perl/5.10.0/";

use strict;
use PDF::API2::Simple;

my $leftCol = 72;
my $centerLine = 306;
my $leftCenter = ($centerLine - $leftCol)/2 + $leftCol;
my $rightCenter = ($centerLine - $leftCol)/2 + $centerLine;

my $startText = 626;

my $number = 1;
my $row = $startText;
for (my $page = 1 ; $page < 201/5 ; $page++) {

  our $pdf = PDF::API2::Simple->open('open_file' => 'tickets.raw.pdf',
                                     'margin_right' => 72, 'margin_left' => 72);

  $pdf->add_font('TimesBold');
  $pdf->add_font('TimesRoman');

  $pdf->file ('pdfs/tickets.numbered.' . (sprintf ("%02d", $page)) . '.pdf');
  print ("Saving as " . $pdf->file . "\n");

  for ($row = $startText ; $row > 0 ; $row -= 72*2) {
    print ("Number is $number. Row is $row.\n");

    $pdf->text(sprintf ("%03d", $number), autoflow => 'off',
               x => $leftCenter,
               y => $row,
               align => 'center',
               fill_color => 'red'
              );

    $pdf->text(sprintf ("%03d", $number++), autoflow => 'off',
               x => $rightCenter,
               y => $row,
               align => 'center',
               fill_color => 'red'
              );

  }
  $pdf->save();
}

The pdftk command line to glue the pages together is:

pdftk pdfs/*.pdf cat output tickets.numbered.pdf