groundwork-spreadsheets 0.1.1

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