The API side¶

Workbook formats in details¶

Even we are talking about Excel files (XSL file), internally this is not really true. When the project has been started, there was no Python library that can fully acceed to all needed datas from a common Excel file (like formulas and cell style), so it has been decided to use OpenOffice Flat XML format that was an XML format that can be fully parsed with any XML parser.

The method is somewhat tricky because it involved to open the original Excel file with OpenOffice then save it as a Flat XML file. Problem is since OpenOffice 3.2 has changed this format and its successor LibreOffice has inherited from it, so the parser from the API is not up to date with it and that leads to the fact that we can’t actually update Workbook matrix from the sources. The parser need to be updated and it is too much work for now.

Each Workbook sources are actually stored for three formats :

• XLS: Common Excel file, that is the source used to create Flat XML version and used as a template to export Structure’s Workbooks from the web interface. Provided by France Active;
• XLSX: OpenDocument (XML) Excel file, only for some API tools to inspect some datas but not really used. Provided by France Active;
• FODS: Flat OpenOffice XML file, used to inspect and extract a workbook to their Python matrix. Generated from the common Excel files;

Even we mainly use the FODS files and XLS files, we still maintains all versions to have fully synchronized sources.

Matrix¶

A workbook matrix is composed of multiple parts :

• A data matrix which contains all cell values for data cells and formulas cells;
• A formula matrix which contains all formula cells where formulas have been transcripted to their Python equivalent;
• A chart matrix which contains all chart maps required to build them from cell datas;
• A matrix summary which contains some shortcut, labels and coordinates to use some specific cell out of the workbook context;
• A matrix source which contains all formula cells in the Excel syntax, this is mainly for debugging purpose;

Technically a matrix is mainly a simple Class object containing a dict where is stored sheets and their cells.

class DatasMatrix(object):
    sheet_order = [ u'Données générales', u'Bilans passés', .... ]

    def __init__(self):
        self.cells = {
            u'Données générales':{
                'C4': (u'', 'string', 'string'),
                'C5': (0, 'number', 'number'),
                'C6': (u'Association', 'string', 'string'),
                ....
            },
            u'Bilans passés':{
                'B25': (u'', 'longstring', 'string'),
                'C4': (2007, 'number', 'number'),
                'C6': (0, 'number', 'number-grouped'),
                'C8': (0, 'number', 'number-grouped'),
                'C10': (0, 'number', 'number-grouped'),
                ....
            },
            ....
        }

class FormulasMatrix(object):
    sheet_order = [ u'Données générales', u'Bilans passés', .... ]

    formulas = {
        u'Données générales':{
        },
        u'Bilans passés':{
            'C4': (u"self.Range(u'Bilans passés','D4')-wDecimal('1')", 'fo:number', 'number'),
            'C12': (u"self.Sum(self.Range(u'Bilans passés','C6','C10'))", 'fo:number', 'number-grouped'),
            'C18': (u"self.Sum(self.Range(u'Bilans passés','C13','C17'))", 'fo:number', 'number-grouped'),
            'C20': (u"self.Sum(self.Range(u'Bilans passés','C19'),self.Range(u'Bilans passés','C18'),self.Range(u'Bilans passés','C12'))", 'fo:number', 'number-grouped'),
            'D4': (u"self.Range(u'Bilans passés','E4')-wDecimal('1')", 'fo:number', 'number'),
            ....
        },
        ....
    }

Manager¶

Like Django models have managers to perform queries on their database entries, a Workbook matrix have a manager to read and write to its cells.

The base matrix manager (badly named WorkbookBaseMatrix..) is instantiated with a matrix to be able to find cells and perform calculations or editing cells values.

It is the manager that implements Excel functions used in formulas transcripted to Python. In effect, the Python code for a formula use manager instance methods to apply Excel functions like self.Sum(...) to perform an Excel sum. Calculations are done through the wDecimal object, see below.

wDecimal¶

Because we are doing Financial plateform, we need a strong accuracy on calculations and so we need to use the Python Decimal object that can support float number without to do any round effect with any operator, like Python float can do in some case.

But also with Excel weak typing, sometime some formula can try to mix string and numbers in a way that is not supported from Decimal (nor Float and Integer) and so we needed to extend the Decimal object to implement this weak typing with operators.

This is why internally, matrix and manager allways use wDecimal for numbers, an extended Decimal object.

wDecimal support string concatenation from left or the right, allways returns a wDecimal object from any operation and return a simple zero for division by zero without raising an exception.

>>> wDecimal(4)+" alertes"
4 alertes
>>> wDecimal(40)+2
42
>>> wDecimal(40)+wDecimal(2)
42
>>> wDecimal(40)+wDecimal(2)+" alertes"
42 alertes
>>> wDecimal(40)+wDecimal(2)+u" alèrtes"
42 alèrtes
>>> wDecimal(40)+u" alèrtes"+wDecimal(2)
40 alèrtes2
>>> wDecimal(2)-wDecimal(42)+wDecimal(7)
-33
>>> wDecimal(40)*wDecimal(2)
80
>>> wDecimal(40)-wDecimal(2)
38
>>> wDecimal(40)/wDecimal(2)
20
>>> wDecimal(70)/0
0
>>> wDecimal(5)**wDecimal(5)
3125

class FormulasMatrix(object):
    sheet_order = [ u'Données générales', u'Bilans passés' ]

    formulas = {
        u'Données générales':{
        },
        u'Bilans passés':{
            'C4': (u"self.Range(u'Bilans passés','D4')-wDecimal('1')", 'fo:number', 'number'),
            'G4': (u"self.Range(u'Bilans passés','C4')", 'fo:number', 'number'),
        },