Create an OpenOffice SpreadSheet

From Dabo Wiki
Jump to: navigation, search

Reporting from Dabo is normally done by using reportWriter.py. It works very well for document type reports, but often our clients require a spreadsheet. Since my clients are using OpenOffice I needed a way to create spreadsheet from Dabo. Turns out that creating an OpenOffice spreadsheet from Dabo is very easy.

Turns out a fellow Pythonian Joseph Colton has written a module to create both OpenOffice spreadsheets and documents. It does not interface with OpenOffice like a COM object in the MS windows worldut creates a file that OpenOffice can read. It can be download from http://ooolib.sourceforge.net

I have created a Dabo self contained example. For you MS windows users you will have to change the path and the OpenOffice executable.

#!/usr/bin/python
import os
import ooolib
#http://ooolib.sourceforge.net
import dabo
import dabo
dabo.ui.loadUI("wx")


# app:
app = dabo.dApp(MainFormClass=None)
app.setup()


# db:
con = dabo.db.dConnection(DbType='sqlite', Database=':memory:')
cur = con.getDaboCursor()
cur.execute("create table customers (id INTEGER PRIMARY KEY, name CHAR, valid INT)")
cur.execute("insert into customers (name, valid) values ('Paul', 0)")
cur.execute("insert into customers (name, valid) values ('John', 1)")
cur.execute("insert into customers (name, valid) values ('Larry', 2)")
cur.execute("insert into customers (name, valid) values ('Ed', 3)")
cur.execute("insert into customers (name, valid) values ('Viola', 4)")
cur.execute("insert into customers (name, valid) values ('Bill', 5)")
cur.execute("insert into customers (name, valid) values ('Pascale', 6)")
cur.execute("insert into customers (name, valid) values ('Donna', 7)")
cur.flush()


# biz:
biz = dabo.biz.dBizobj(con)
biz.DataSource = "customers"
biz.KeyField = "id"
biz.UserSQL = "select id, name, valid from customers"
biz.DataStructure = (
        # (field_alias, field_type, pk, table_name, field_name, field_scale)
        ("id", "I", True, "customers", "id"),
        ("name", "C", False, "customers", "name"),
        ("valid", "B", False, "customers", "valid"))

biz.requery()
#the UI
frm = dabo.ui.dForm()


def btnCreate(evt):
    """ Set values.  The values are set in column, row order, but the values are
    not in the traditional "A5" style format.  Instead we require two integers.
    set_cell_value(col, row, datatype, value)"""
    doc = ooolib.Calc()
    ds=biz.getDataSet()
    row_num=1  ## start with 1 = A1
    for row in ds:
        col_num=1
        for cols in row.keys():
            if type(row[cols]) is bool:
                columntype='bool'
            elif type(row[cols]) is int:
                columntype='float'
            elif type(row[cols]) is unicode:
                columntype='string'
            # OK let's write out the data to spreadsheet
            doc.set_cell_value(col_num, row_num, columntype, row[cols])
            col_num=col_num+1
            row_num=row_num+1

    # Save the document to the file you want to create.
    # You need to change the path to match your environment
    doc.save("/home/johnf/dabo-example01.ods")
    #below will allow me to open the spreadsheet using OpenOffice (linux)
    # I believe windows users need 'scalc.exe'
    os.popen2('/usr/bin/oocalc /home/johnf/dabo-example01.ods')

theButton = dabo.ui.dButton(frm,Caption="Push Me",OnHit=btnCreate)
frm.Sizer.append1x(theButton)

#run it
frm.show()
app.start()