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.

Background

OpenOffice.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 Template

The 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 Template

I 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 Table

Below 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 Invoice

Now 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 Table

Below 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 Calculations

We'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 Touches

Now 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.

Resources

getopenoffice.org: getopenoffice.org/main.html

OOExtras: ooextras.sourceforge.net/

OOo Documentation Project: www.ooodocs.org

OOo Links: www.kaaredyret.dk/openoffice_links_uk.html

OpenOffice.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.pdf

Charles Curley's desktop computer has been a Microsoft Free Zone since 1999.