Calculating in OpenOffice.Org Text Documents

 in
Creating spreadsheet-like invoices in Writer.

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.

______________________

Comments

Comment viewing options

Select your preferred way to display the comments and click "Save settings" to activate your changes.

Re: Calculating in OpenOffice.Org Text Documents

Anonymous's picture

Well, funstuff, thanks.

However, as you treat this as an invoice - which it is not - you will not seriously understand, where the problems lay when using OOo in an professional environment. And this is a problem, of corse.
Martin

Re: Calculating in OpenOffice.Org Text Documents

Anonymous's picture

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.

I would not recommend giving this advice. It is preferred to be saving templates through File - Templates... - Save... dialog. This way they are sure to be saved correctly.
Otherwise I like the article.

ursacol02

Re: Calculating in OpenOffice.Org Text Documents

Anonymous's picture

Is there a doc explaining how to link this to a database or how to use pricelists ? Maybe also customer names and articles in stock. Thanx
Saxa

Database access

Anonymous's picture

Linking a system like this into a database would be pretty impressive. Use data sources to get SUM()s of owing amounts over various periods, etc, then do more calculation in the invoice it's self.

Wow. Thanks for demo-ing this.

White Paper
Linux Management with Red Hat Satellite: Measuring Business Impact and ROI

Linux has become a key foundation for supporting today's rapidly growing IT environments. Linux is being used to deploy business applications and databases, trading on its reputation as a low-cost operating environment. For many IT organizations, Linux is a mainstay for deploying Web servers and has evolved from handling basic file, print, and utility workloads to running mission-critical applications and databases, physically, virtually, and in the cloud. As Linux grows in importance in terms of value to the business, managing Linux environments to high standards of service quality — availability, security, and performance — becomes an essential requirement for business success.

Learn More

Sponsored by Red Hat

White Paper
Private PaaS for the Agile Enterprise

If you already use virtualized infrastructure, you are well on your way to leveraging the power of the cloud. Virtualization offers the promise of limitless resources, but how do you manage that scalability when your DevOps team doesn’t scale? In today’s hypercompetitive markets, fast results can make a difference between leading the pack vs. obsolescence. Organizations need more benefits from cloud computing than just raw resources. They need agility, flexibility, convenience, ROI, and control.

Stackato private Platform-as-a-Service technology from ActiveState extends your private cloud infrastructure by creating a private PaaS to provide on-demand availability, flexibility, control, and ultimately, faster time-to-market for your enterprise.

Learn More

Sponsored by ActiveState