Generating Excel workbooks

generate.py

from openpyxl import Workbook
from openpyxl.workbook.defined_name import DefinedName
from openpyxl.worksheet.datavalidation import DataValidation
from openpyxl.utils import quote_sheetname, absolute_coordinate

import json
from json import JSONEncoder

wb = Workbook()
ws = wb.active
# Remove the default/active worksheet. We'll make more.
wb.remove(ws)

jobj = json.loads(open("sheet_spec.json").read())

def add_yorn_validation(ws):
    yorn_dv = DataValidation(type="list", formula1='"Y,N"', allow_blank=True)
    yorn_dv.error = "Entries must be Y or N in this column"
    yorn_dv.errorTitle = "Must by Y or N"
    yorn_dv.showDropDown = False
    ws.add_data_validation(yorn_dv)
    return yorn_dv


for ndx, sheet in enumerate(jobj['sheets']):
    # Create and name the worksheet
    ws = wb.create_sheet(title=sheet['name'], index=ndx)
    
    # Create all the single cells
    for o in sheet['single_cells']:
        range = f"{absolute_coordinate(o['range_cell'])}"
        cell_reference = f"{quote_sheetname(ws.title)}!{range}"
        new_range = DefinedName(name=o["range_name"], attr_text=cell_reference)
        wb.defined_names.add(new_range)
        ws[o["title_cell"]] = o["title"]

    # Create all the open ranges (which go from a cell down the entire column)
    for r in sheet['open_ranges']:
        abs_start = f"{absolute_coordinate(r['range_start'])}"
        abs_start_col = r['range_start'][0]
        range = f"{abs_start}:${abs_start_col}$1048576"
        cell_reference = f"{quote_sheetname(ws.title)}!{range}"
        new_range = DefinedName(name=r["range_name"], attr_text=cell_reference)
        wb.defined_names.add(new_range)
        ws[r["title_cell"]] = r["title"]

        # Is this a Y/N range?
        if r["type"] == "yorn_range":
            yorn_dv = add_yorn_validation(ws)
            # Use the range, because the ws object is already passed in; it knows
            # what sheet to apply the validation to.
            yorn_dv.add(range)



wb.save(jobj["filename"])

spec.json

{
   "filename": "example_workbook.xlsx",
   "sheets": [
      {
         "name": "TheFirstSheet",
         "open_ranges": [
            {
               "range_name": "some_values",
               "range_start": "A5",
               "title": "Some values",
               "title_cell": "A4",
               "type": "open_range"
            },
            {
               "range_name": "yorns",
               "range_start": "B5",
               "title": "Some YorNs",
               "title_cell": "B4",
               "type": "yorn_range"
            }
         ],
         "single_cells": [
            {
               "range_cell": "B1",
               "range_name": "auditee_uei",
               "title": "Auditee UEI",
               "title_cell": "A1",
               "type": "single_cell"
            },
            {
               "range_cell": "B2",
               "range_name": "total_expenditures",
               "title": "Total Expenditures",
               "title_cell": "A2",
               "type": "single_cell"
            }
         ]
      }
   ]
}

spec.jsonnet

local SingleCell = {
    type: "single_cell",
    title: "Example Cell",
    range_name: "example_cell",
    title_cell: "A1",
    range_cell: "B1"
};

local single_cells = [
        SingleCell + {
            title: "Auditee UEI",
            range_name: "auditee_uei",
            title_cell: "A1",
            range_cell: "B1"
        },
        SingleCell + {
            title: "Total Expenditures",
            range_name: "total_expenditures",
            title_cell: "A2",
            range_cell: "B2"
        },
];

local OpenRange = {
    type: "open_range",
    title: "Example open range",
    range_name: "Example open range",
    title_cell: "A1",
    range_start: "B1" 
};

local YorNRange = OpenRange + {
        type: "yorn_range",
        title: "Example YorN range",
        range_name: "Example YorN range"
};


local open_ranges = [
    OpenRange + {
        title: "Some values",
        range_name: "some_values",
        title_cell: "A4",
        range_start: "A5"
    },
    YorNRange + {
        title: "Some YorNs",
        range_name: "yorns",
        title_cell: "B4",
        range_start: "B5"
    },
];


local sheets = [
    {
        name: "TheFirstSheet",
        single_cells: single_cells,
        open_ranges: open_ranges
    },
];

local workbook = {
    filename: "example_workbook.xlsx",
    sheets: sheets,
};

{} + workbook