groundwork-spreadsheets 0.2.0

Patterns for reading writing spreadsheet documents

Groundwork patterns to read and write spreadsheet documents. Excel 2010 (xlsx, xlsm) is supported at the moment. The full documentation is available at https://groundwork-spreadsheets.readthedocs.io/

For more information regarding groundwork, see here.

ExcelValidationPattern

• Uses the library openpyxl

• Can read Excel 2010 files (xlsx, xlsm)

• Configure your sheet using a json file

• Auto detect columns by names

• Layout can be

  • column based: headers are in a single row and data is below

  • row based: headers are in a single column and data is right of the headers

• Define column types and verify cell values against them

  • Date

  • Enums (e.g. only the values ‘yes’ and ‘no’ are allowed)

  • Floating point numbers with optional min/max check

  • Integer numbers with optional min/max check

  • String with optional regular expression pattern check

• Exclude data row/columns based on filter criteria

• Output is a dictionary of the following form row or column number -> header name -> cell value

• Extensive logging of problems

Here is how an example json config file looks like:

{
    "sheet_config": "last",
    "orientation": "column_based",
    "headers_index_config": {
        "row_index": {
            "first": 1,
            "last": "automatic"
        },
        "column_index": {
            "first": "automatic",
            "last": "severalEmptyCells:3"
        }
    },
    "data_index_config": {
        "row_index": {
            "first": 2,
            "last": "automatic"
        },
        "column_index": {
            "first": "automatic",
            "last": "automatic"
        }
    },
    "data_type_config": [
        {
            "header": "hex number",
            "fail_on_type_error": true,
            "fail_on_empty_cell": false,
            "fail_on_header_not_found": true,
            "type": {
                "base": "string",
                "pattern": "^0x[A-F0-9]{6}$"
            }
        },
        {
            "header": "int number",
            "type": {
                "base": "integer",
                "minimum": 2
            }
        }
    ]
}