Step: excel

badge-status badge-metamodel

Step producing Excel spreadsheets from JSON file with instructions.

Input

JSON file containing instructions how to construct the desired Excel spreadsheet as described further in this section.

Properties

It allows to set both basic and custom properties of the workbook / spreadsheet.

{
  "properties": {
    "document": {
      "title": "My example workbook",
      "subject": "",
      "author": "Albert Einstein",
      "manager": "",
      "company": "ACME",
      "category": "",
      "keywords": "test,example,foo,bar",
      "created": "2018-01-01",
      "comments": ""
    },
    "custom": [
      {
        "projectUuid": "...",
      }
    ]
  }
}

Options

It allows to specify various workbook options.

{
  "options": {
    "strings_to_numbers": true,
    "strings_to_urls": true,
    "use_future_functions": true,
    "max_url_length": 255,
    "nan_inf_to_errors": true,
    "default_date_format": null,
    "remove_timezone": true,
    "use_zip64": false,
    "date_1904": false,
    "calc_mode": "auto",
    "read_only_recommended": false,
    "active_sheet": 0,
    "vba_name": "foo",
    "size": {
      "width": 0,
      "height": 0
    },
    "tab_ratio": 50
  }
}

Notes:

  • active_sheet is an index of sheet to be active when document is opened.

  • size sets the default window size.

  • tab_ratio sets ratio between the worksheet tabs and the horizontal slider.

Definitions

It allows to define a name to be then used as a variable (see define_name).

{
  "definitions": {
    "Exchange_rate": "=0.96"
  }
}

Formats

It specifies formats in the spreadsheets that can be then used for cells in sheets. Possible options can be found in the documentation.

{
  "formats": {
    "myBoldFormat": {
      "bold": true
    }
  }
}

The example above creates a format named myBoldFormat that has bold text.

Charts

It specifies charts that can be then inserted inside sheets or used as chartsheets.

The options are documented here. Basically, each chart must have a unique name and then can have some options, series, and axis (e.g. x axis).

Finally, there are some basic and advanced settings:

{
  "name": "myChartA",
  "combine": "myChartB",
  "options": {
    "type": "bar",
    "subtype": "percent_stacked"
  },
  "series": [
    {
      "name": "=Sheet1!$B$1",
      "categories": "=Sheet1!$A$2:$A$7",
      "values": "=Sheet1!$B$2:$B$7"
    }
  ],
  "axis": {
    "x": {"name": "Test number"},
    "y": {"name": "Sample length (mm)"}
  }
}

sheets

It is the main part specifying a list of sheets in the workbook, where each sheet has name (optional), type (optional, work or chart), options and then based on the type it has either chart (for chartsheet) or data (for worksheet). Some of the options are common for both chartsheet and datasheet. The order of sheets in the list corresponds to the order in the Excel spreadsheet.

Chartsheet

A chartsheet simply refers to a chart (by its name) that should be placed in this chartsheet.

The possible options are:

{
  "name": "Nice chart",
  "type": "chart",
  "chart": "myChartA",
  "options": {
    "tab_color": "red"
  }
}

Worksheet

Traditional worksheet with many options and data placed into cells. There are more options when compared to chartsheets.

The possible options are:

Inserting data

In JSON as part of worksheet’s attribute data, you can in the list specify data to be inserted to cells in four ways (type):

  • cell writes cell according to the possibly specifiedsubtype (see below)

  • row writes row using provided data as a list

  • column writes column using provided data as a list

  • grid writes rows using provided data as a list of lists (list of rows)

For data, there are the following subtypes possible (for type set to cell):

  • (unspecified) tries to directly all write with provided arguments, type should be then decided based on provided values and attributes

  • string writes string from value

  • number writes number from numeric value

  • datetime writes datetime; it tries to parse date/datetime value from string as JSON does not have a format for datetime, standard ISO formats are recommended (e.g. 2022-12-24 or 2022-12-24T12:00:00Z)

  • formula writes formula with formula in value and optional result value

  • blank writes blank value (no attributes except format)

  • boolean writes boolean value with boolean value (i.e. true or false)

  • url writes URL value with url, value, and tip attributes

  • rich_string writes rich string that allows formatting; for using format in the string_parts use prefix !fmt:: before name of the desired format

All options above may specify format (refer to defined format via its name).

{
  "type": "cell",
  "subtype": "string",
  "cell": "A1",
  "value": "X"
}

{
  "type": "column",
  "subtype": "string",
  "cell": "A3",
  "data": [
    "ID",
    "Name",
    "Created at",
    "Author"
  ],
  "format": "myBoldFormat"
}

{
  "type": "grid",
  "row": 5,
  "col": 5,
  "data": [
    ["A", "B", "C"],
    ["D", "E", "F"]
  ]
}

Inserting other elements

Aside from data in cell, there is also possibility to insert other elements to the worksheet (type vales):

  • button with options such as macro or caption

  • textbox with text and options such as styling or offset in pixels

  • comment with comment text and options such as color or author

  • chart with chart (name) and options

  • image with filename, b64bytes, and options

All of these are used with corresponding type and are placed to desired cell (or col/row indices).

{
  "type": "button",
  "cell": "B5",
  "options": {
    "caption": "Press Me"
  }
}

vba_projects

List of VBA projects (with macros) to be embedded in the spreadsheet.

{
  "vba_projects": [
    {
      "project": "./vbaProject.bin",
      "is_stream": false
    }
  ]
}

Output

Desired Excel spreadsheet based on instructions from input JSON, it can be one the following formats (whether it uses macros or not):

  • application/vnd.openxmlformats-officedocument.spreadsheetml.sheet (extension .xlsx)

  • application/vnd.ms-excel.sheet.macroEnabled.12 (extension .xlsm)

Options

No options, everything comes from the input JSON file

Notes

  • XlxsWriter library is used to construct Excel spreadsheet.

  • Most likely this step will follow jinja step that constructs the JSON file.

Example

{
  "name": "excel",
  "options": {}
}