LibreOffice: Creating invoices using Writer

Submitted by dag on Tue, 2011/04/05 - 10:54

From the very start I have been using OpenOffice (now LibreOffice) for creating my own invoices. The first thing you have to decide is whether to create your invoice with Calc or with Writer. I decided to use Writer, as it is harder to create a proper layout with a spreadsheet document (especially when using window envelopes) that comes out well on different printers, but that's up to one's preference.

However for the calculation of VAT and totals I use an embedded spreadsheet object. Everything else (eg. logo, company info, customer info, invoice number, invoice date, conditions) is part of the layout of the invoice in Writer.

There are two items on the invoice in Writer that I have automated:

  • Invoice number
  • Due date

The invoices are named invoice-YYYY-MM-#NR-customer.odt and stored in a single directory so it is easy to see what the next invoice number should be. For new invoices I always start from an existing invoice and modify the customer info if needed.

The invoice number on the invoice is automatically extracted from the filename in the format YYYY/MM/#NR. This helps to avoid an old invoice number appears on a new invoice.

The due date is automatically set to be the invoice date plus 30 days so that you only have to add/update the invoice date. I implemented this recently and it was not that easy to do with BASIC macro's and UNO.

So how was this implemented ? The easiest solution I found is to work with custom user fields (Insert > Fields > Variables > User field) as they can easily be named and modified from UNO. I created InvoiceNumber (string), InvoiceDate (Date format) and DueDate (Date format).

Then use the following BASIC macro and attach the various 'event-handlers' of your document to the below functions. (Tools > Customize > Events)

REM For testing purposes
Sub Main
    updateInvoiceNumber()
    updateDueDate()
End Sub

REM Event handler when opening a document
Sub _Open
    updateInvoiceNumber()
End Sub

REM Event handler when a document is being saved
Sub _Save
    updateDueDate()
End Sub

REM Event handler when a document is modified
Sub _Modified
    updateDueDate()
End Sub

REM Update a user field (DueDate) with the invoice date + 30 days
Sub updateDueDate
    DueDate = getDueDate()
    DueDateField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.DueDate" )
    If (DueDateField.Value <> DueDate) Then
        DueDateField.Value = DueDate
        thisComponent.TextFields.refresh()
    EndIf
End Sub

REM Retrieve the user field (InvoiceDate) from the document and make DueDate = InvoiceDate + 30
Function getDueDate
    If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
        GlobalScope.BasicLibraries.LoadLibrary("Tools")
    End If
    InvoiceDateField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.InvoiceDate" )
    getDueDate = InvoiceDateField.Value + 30
End Function

REM Update a user field (InvoiceNumber) with a slice of the document name
Sub updateInvoiceNumber
    InvoiceNumber = getInvoiceNumber()
    InvoiceNumberField = thisComponent.getTextFieldMasters().getByName( "com.sun.star.text.fieldmaster.User.InvoiceNumber" )
    If (InvoiceNumberField.Content <> InvoiceNumber) Then
        InvoiceNumberField.Content = InvoiceNumber
        thisComponent.TextFields.refresh()
    End If
End Sub

REM Extract the invoice number from the document name (slice 9-20)
Function getInvoiceNumber
    If (Not GlobalScope.BasicLibraries.isLibraryLoaded("Tools")) Then
        GlobalScope.BasicLibraries.LoadLibrary("Tools")
    End If
    getInvoiceNumber = FileNameoutofPath(thisComponent.getURL(), "/")
    getInvoiceNumber = Mid(getInvoiceNumber, 9, 11)
    getInvoiceNumber = ReplaceStr(getInvoiceNumber, "-", "/")
End Function

REM Return a string with str1 replaced with str2
Function ReplaceStr(myString As String, str1 As String, str2 As String)
    ReplaceStr = join(split(myString, str1), str2)
End Function

How do you make your invoices ? Any feedback regarding my BASIC programming ? :-) I am interested to know.

Redmine plugin

I use my own invoicing thing http://www.redmine.org/plugins/haltr

same combination but from different vendor

I am using the same combination (text document for header, ship to, dates etc. and embedded spreadsheet object for invoice items, price calculation, VAT etc.) but from different vendor.

Your code examples are truncated :-(

Afternoon,

a good article, most enlightening. I too have to produce invoices and never thought to use Open/Libre Office Writer to do it, I use Calc instead and agree about the printing.

I notice in your code above, that some of the longer lines are somewhat truncated. For example, in updateDueDate, the third line is shortened to:

thisComponent.getTextFieldMasters().getByName( "c

However, if I highlight the line and copy it, I get this instead:

thisComponent.getTextFieldMasters().getByName( "com

In Firefox, I can see the source in full if I view the page source. Unfortunately the code is butchered by lots of "<" for all the '<'. Can't win them all I suppose! :-)

Cheers,
Norm.

Thanks

Hi there

Just wanted to thank you for posting this - I spent a lot of time searching the docs, for something similar.

Regarding you Basic skills - perhaps you should try "Option Explicit" - it does gives some hints about some minor stuff that could be changed.

Kind Regards

Mads