API¶

Location¶

api.weak_decimal.wDecimal

Goal¶

1. Manage numbers with perfect accuracy for floating numbers, because we are on a financial plateform where accuracy does matter;
2. Support weak type operations between numbers and strings (like Excel does). Concretely to concatenate a string to a number;
3. Permit divisions with zero (like Excel does). Concretely a number divided by zero or a zero divided by another number will allways return zero without raising exceptions;

Scope¶

Internally, Autodiag allways use weak decimal to compute datas.

This means getted cell datas numbers from database are transformed to weak decimal and formulas allways return weak decimal for numbers.

wDecimal inherit from python object decimal.Decimal then overrides some methods to implement some Excel behaviors.

Not all operators implemented in decimal.Decimal are really implemented into wDecimal, only +,``-,``*,``/,``**. Others may work, but they will return a decimal.Decimal type and not a wDecimal type, that may break chaining operations if used.

Demonstration¶

>>> 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

Process¶

There are some steps to publish new Python workbook matrix:

1. Inspect the source (a FODS file) to check that the parser can fully read it and there is no basic problems;
2. Then we process to extract and generate the Python matrix;
3. Finally we apply a modifier scheme to fix some extract flaws, like non data cells that contain some text string (that are not eligible to be data cells) or some formulas badly typed that cannot work with our parser;

Tools¶

There are multiple tools to proceed to extraction:

1. Extractor to read and parse Excel workbook;
2. Inspector that inherits from Extractor and check extracted content to validate some basic details;
3. Publisher that inherits from Extractor and use extracted content to build Python matrix;
4. Modifier use a modifier scheme to apply some changes to Python matrix to fix some flaws from Extractor parser;

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.

To do calculation from cell formulas, the API implements Excel sheet behaviors:

• Excel use weak typing, the API supports it with Weak Decimal;
• API have to support Excel functions. But only used operators and functions in Autodiag plateforms workbooks are implemented;

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'),
            ....
        },
        ....
    }

Matrix 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 from API (WorkbookBaseMatrix) is initialized with a Python workbook matrix to be able to find cells and perform calculations or editing cells values.

It implements Excel functions used in formulas converted to Python. Concretely the Python code for a formula uses manager instance methods to apply Excel functions like self.Sum(...) to perform an Excel sum.

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'),
        },