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.
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.
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.
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.
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.
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.
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.
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.
OOo Documentation Project: www.ooodocs.org
OOo Links: www.kaaredyret.dk/openoffice_links_uk.html
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.
Practical Task Scheduling Deployment
July 20, 2016 12:00 pm CDT
One of the best things about the UNIX environment (aside from being stable and efficient) is the vast array of software tools available to help you do your job. Traditionally, a UNIX tool does only one thing, but does that one thing very well. For example, grep is very easy to use and can search vast amounts of data quickly. The find tool can find a particular file or files based on all kinds of criteria. It's pretty easy to string these tools together to build even more powerful tools, such as a tool that finds all of the .log files in the /home directory and searches each one for a particular entry. This erector-set mentality allows UNIX system administrators to seem to always have the right tool for the job.
Cron traditionally has been considered another such a tool for job scheduling, but is it enough? This webinar considers that very question. The first part builds on a previous Geek Guide, Beyond Cron, and briefly describes how to know when it might be time to consider upgrading your job scheduling infrastructure. The second part presents an actual planning and implementation framework.
Join Linux Journal's Mike Diehl and Pat Cameron of Help Systems.
Free to Linux Journal readers.Register Now!
- SUSE LLC's SUSE Manager
- Google's SwiftShader Released
- Murat Yener and Onur Dundar's Expert Android Studio (Wrox)
- My +1 Sword of Productivity
- Managing Linux Using Puppet
- Non-Linux FOSS: Caffeine!
- SuperTuxKart 0.9.2 Released
- Parsing an RSS News Feed with a Bash Script
- Rogue Wave Software's Zend Server
- Doing for User Space What We Did for Kernel Space
With all the industry talk about the benefits of Linux on Power and all the performance advantages offered by its open architecture, you may be considering a move in that direction. If you are thinking about analytics, big data and cloud computing, you would be right to evaluate Power. The idea of using commodity x86 hardware and replacing it every three years is an outdated cost model. It doesn’t consider the total cost of ownership, and it doesn’t consider the advantage of real processing power, high-availability and multithreading like a demon.
This ebook takes a look at some of the practical applications of the Linux on Power platform and ways you might bring all the performance power of this open architecture to bear for your organization. There are no smoke and mirrors here—just hard, cold, empirical evidence provided by independent sources. I also consider some innovative ways Linux on Power will be used in the future.Get the Guide