groundwork-spreadsheets 0.1.1

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 cells against it

  • Date

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

  • Floating point numbers (+minimum/maximum check)

  • Integer numbers (+minimum/maximum check)

  • String (+RegEx pattern check)

• Get a dictionary as output with the following form row number -> header name -> cell value

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