Calculating in OpenOffice.Org Text Documents

OpenOffice.org's (OOo) word processor,
called Writer, has an embedded calculating facility similar to a
spreadsheet. This article shows how to use it by building an
invoice template with account aging and automatic totals.BackgroundOpenOffice.org is an open-source office suite. It can replace
other office suites and has excellent file interchange filters. It
runs on several operating systems, including Linux, Solaris,
Macintosh OS X and Windows.As an independent contractor, I have to bill customers in
order to eat. As I'd rather be hacking Linux, I have incentive to
make invoicing as simple and easy as possible. Sometimes customers
are late paying, and I have to age their account, that is, show
which parts of the invoice are 30, 60 and 90 or more days past
due.I have that function set up as an OpenOffice.org template,
ready to use for each invoice. Figure 1 shows what a freshly
created invoice looks like. It is loaded from a user template. As
you can see from the illustration, the aging information is in a
table, and the aged amounts are added up in the Total cell. In
addition, all the items in the invoice, including the past due
total, should be summed for the Total Due line of the main invoice
table.
Figure 1. A freshly created invoice in
OpenOffice.org, ready to edit.
In this article, I recreate the core of the invoice, the two
tables and show how to use spreadsheet-like functions to automate
the calculations. This article is based on OpenOffice.org version
1.1. Versions 1.0.x have the same facilities, but the user
interface is different and somewhat more awkward.Creating a TemplateThe first thing to do is create a brand new document. From
the File pull-down menu, choose New --> Text Document. Then save
it (File --> Save As), as shown in Figure 2. Save it as a Text
Document Template, and be sure to save it in the directory
~/OpenOffice.org1.1.0/user/template/, where user templates are
found. If you have OpenOffice.org set to provide the extension
automatically, it should provide one of .stw.
Figure 2. Saving an OpenOffice.org Writer document
as a template. Note the directory to save in.
Now verify you can use the template once you've done
something useful with it. Start a new document with File --> New
--> Templates and Documents. Click on Default, and you should
see test (no extension) in the list of available templates.Filling in the TemplateI leave creating the letterhead as an exercise for the
reader. You need to make sure it lists your return address, the
invoice's date (hint: use date fields) and the addressee (hint: see
Data Source Overview in the OpenOffice.org help). These should all
be in appropriate styles so you can change them easily and the
changes will apply across the document.The Main Invoice TableBelow the letterhead, address and salutation, insert the main
invoice table, the upper of the two. It is a two column table, with
a description on the left and the amount on the right. (Insert
--> Table.) Provide neither a header nor a border. We provide at
least five lines: Previous Balance, Current Work, Paid, Past Due
and Total Due. You may have multiple Current Work lines, depending
on the client's accounting requirements. If so, you can insert
additional rows any time you need them.Notice that the amount for Past Due is given a single
underline; this indicates a sum. The amount for Total Due is given
a double underline. This is accountant shorthand indicating this is
the amount to pay.For testing, set up dummy data in the first three amount
cells. Then apply a suitable number style to all of the amount
columns. Select all the cells in the column, then Format -->
Number Format. In the pop-up window, select a suitable currency
format. Your results should look something like Figure 3.
Figure 3. The main table, with no aging information
yet.
If you don't like the name of the table after you've created
it, you can change it. In fact, you can rename any object in your
document. Open the navigator (F5 or Edit --> Navigator). Select
the object to rename, and right click on it. In the pop-up menu,
got to Table --> Rename.Calculating The InvoiceNow for the fun stuff; let's calculate the total due. For
now, we ignore the aging, but we'll return to it after we've set up
the aging table. To start the process, put the cursor into the
Total Due cell, and press the F2 button. In the formula bar that
magically appeared, enter =sum. Click on the top
amount cell, then hold down the shift button and click on the Past
Due amount cell. You should see a formula like so:
=sum<B1:B4>. Notice the spreadsheet style
addressing. In effect, each OpenOffice.org Writer document is a
spreadsheet, and each table within a Writer document is a sheet
within that spreadsheet. You can click in the formula bar and edit
to your heart's content. Press return, and you should see the
correct sum magically appear in the Total Due amount cell.
Cool!The Aging TableBelow the main table, insert the phrase Account Aging. Below
that, insert a new table named Aging that contains four columns and
two rows. Allow headers and a border, and enter the column headers.
Again, apply number formatting to the amount cells. Your invoice
template should now look like Figure 4.
Figure 4. The aging table, ready for applying a
calculation.
Now we calculate the sum of the past due amounts in the Total
amount cell of the aging table. Click in that cell, and press the
F2 key again. Click on the first cell in the amount row, then, with
the shift key pressed, click on the 90+ Days amount cell. You
should see the formula grow before your eyes. Press return to make
the new formula take effect. The total is, of course, the correct
amount.Inter-Tabular CalculationsWe've calculated the sum of the past due amounts, but how do
we get that value to the main table's line for the past due amount?
Easy: click on the Past Due amount cell in the main table, press
F2, then click on the total in the Aging table. Notice that the
formula refers to the cell in a different table by prepending the
table name to the cell name, for example, Aging.D2. Again, it's
like different sheets within a spreadsheet. After you press return,
notice two things. First, the correct amount is carried from the
aging table to the main table, and the total due is updated to
reflect new data in the main table. Again, cool! Your results
should look like Figure 5.
Figure 5. The main table, with no aging information
calculated.
Finishing TouchesNow that you have the guts of the invoice working, add the
boilerplate you need. Apply and modify existing styles or create
and modify new ones to get the look you want for your invoice. This
is a professional document, so make it look good.Of course, you need more information for an invoice, such as
the name and address of the client, plus the quantity, pricing and
extension of the items sold. These are left as an exercise for the
reader.Resourcesgetopenoffice.org:
getopenoffice.org/main.htmlOOExtras:
ooextras.sourceforge.net/OOo Documentation Project:
www.ooodocs.orgOOo Links:
www.kaaredyret.dk/openoffice_links_uk.htmlOpenOffice.org:
OpenOffice.org/Taming OpenOffice.org Writer:
www.taming-openoffice-org.com/The StarOffice 6.0 User's Guide. Similar to OpenOffice.org.
(PDF):
docs-pdf.sun.com/816-7367/816-7367.pdfCharles Curley's desktop computer has been a Microsoft Free
Zone since 1999.