Methods

Methods

Algorithms working with Excel data are going to be implemented as methods of the Context object. This object is loaded into the memory throughout the entire lifetime of Worker. Data written by one method should be accessible to other methods.

 _______    events    _________
|       |   ----->   | Worker  |
| Excel |            |(runtime)|
|_______|   <-----   |_________|
            actions

Method overview

What we need to do	What needs to be stored
Header detection	Position of header, column names on every sheet
Table detection	Position of beginning and end of table on every sheet
Error detection for columns	Column validation features (numpy.array's)
Autofill from another column (in another sheet)	A sequence of previously entered cells with their position
Selecting elements that user highlights	A sequence of previously highlighted elements

Events

Event name	Triggered when	Information contained (passed to Worker)
WorkbookOpen	New Excel file is open	Name of file
WorkbookBeforeClose	Before the Excel file is closed	Name of file
SheetChange	Any cell is changed	A new value of a cell, it's metaparameters (formatting, etc, could serialize `Excel Cell object into a dict) and a cell address
UndoComnsenseChange	User presses Undo after comnsense changed something	ComnsenseActionID to be cancelled
RangeResponse	Range requested by the worker is retrieved	List of lists of values. Sublists are rows, lists are columns, see PyWin32 below.

Actions

Action name	Description	Information contained (passed to Excel)
ComnsenseChange	Change the sheet based on comnsense inference (color cell red, autofill, etc.)	Range address and range values in Python format
RangeRequest(font=False, borders=False, color=False, bold=False, italic=False)	Request values for an Excel range (A1:A10, C3:D4, etc, see Range object	List of lists of cell objects, containing value property with cell values. If font=True, cell.font in a name of font. If border=True, cell.border_left, ... cell.border_down are Boolean indicators of cell borders. If color=True, cell.color is a cell color index. If bold=True or italic=True, cell.bold should have an indicator is the cell font is bald, cell.italic .. italic.

Compliance with PyWin32

For the sake of debugging and consistency, it's best to keep Python excel manipulation API consistent with PyWin32. Examples (motivated by tutorial):

>>> import win32com.client
>>> import win32com.client as win32
>>> excel = win32.gencache.EnsureDispatch('Excel.Application')
>>> excel.Visible = True
>>> ws = wb.Worksheets[1]
>>> ws
<win32com.gen_py.Microsoft Excel 14.0 Object Library._Worksheet instance at 0x43791624>
>>> ws.Range("A5:D5").Value = [2,3,4,66]
>>> ws.Range("H1:H3").Value = [[12],[15],[66]]
>>> ws.Range("A1:B2").Value
((2.0, 3.0), (2.0, 3.0))
>>> ws.Range("A10:D10").Interior.ColorIndex = 5
>>> ws.Range("A10:D10").Value = (5,67,8,2,3)
>>> ws.Range("A5:D5").Font.Name
u'Calibri'
>>> ws.Range("A10:D10").Font.Bold
False
>>> ws.Range("A10:D10").Font.Bold = True

Example JSON

ComnsenseChange example JSON sent to Excel and handled by Router.cs:

{
    "type":0, // means comnsensechange as stated in Action.cs
    "workbook":"f1f2d913-8de3-49b6-8993-f6b026686cda",
    "sheet":"\xd0\xb2\xd0\xb0\xd1\x81\xd0\xb8\xd0\xbb\xd0\xb8\xd0\xb9",
    "cells":[
        [
            {
                "key":"$B$3",
                "value":"33",
                "color":3, // Excel ColorIndex
                "font":"Times New Roman",
                "borders":{
                    "right":[
                        2, // Line weight, Excel-defined enum
                        4 // Line style, Excel-defined enum
                    ],
                    "bottom":[
                        1,
                        -4119
                    ]
                },
                "fontstyle":5 // bit mask for (bold, italic, underline), 5=b101 = (bold, underline)
            }
        ]
    ]
}

Used Excel constants are referred and explained in Cell.cs.

MSDN reference.