ans2dev.general.open_xl module – Read, Create and update Excel files.

Note

This module is part of the ans2dev.general collection (version 0.2.0).

It is not included in ansible-core. To check whether it is installed, run ansible-galaxy collection list.

To install it, use: ansible-galaxy collection install ans2dev.general. You need further requirements to be able to use this module, see Requirements for details.

To use it in a playbook, specify: ans2dev.general.open_xl.

New in ans2dev.general 0.1.0

Synopsis

  • This module reads from or writes to Excel (.xlsx) files using the openpyxl Python library.

  • Create new Excel file is avaible from ans2dev.general 0.2.0.

  • It supports reading the entire workbook or a single worksheet, optionally limited to a given cell range.

  • For updates, you can overwrite cells, append new rows, or insert rows. You can also apply custom cell styles.

  • The original Excel file is not overwritten unless you set dest to the same path as src.

  • This module supports only .xlsx or .xlsm files.

Requirements

The below requirements are needed on the host that executes this module.

  • openpyxl

Parameters

Parameter

Comments

cell_style

dictionary

A dictionary specifying optional style attributes for updated cells. Possible keys include

fontColor Hex RGB code for the font color.

bgColor Hex RGB code for the cell background color.

bold Boolean to set bold font.

italic Boolean to set italic font.

underline Boolean to set underline; if true, uses single underline.

Default: {}

dest

string

Destination file path for updated Excel content.

If omitted, defaults to appending _updated.xlsx to the src filename.

index_by_name

boolean

For read operations, if true, uses the first row as dictionary keys. Otherwise, keys are in the format col_<n>.

Choices:

  • false

  • true ← (default)

op

string / required

The operation to perform on the Excel file.

r Read-only. Returns the content from the specified sheet or all sheets.

w Write. Overwrites specified cells with new values.

a Append. Creates one new row at the end of the sheet, writing each item in updates_matrix to that row.

i Insert. Inserts a new row above the row specified in the first item of updates_matrix and writes the updates.

n New. Create a new Excel file without src file, avaible from ans2dev.general 0.2.0.

Choices:

  • "r"

  • "w"

  • "a"

  • "i"

  • "n"

read_range

dictionary

Dictionary specifying the cell range to read.

Can include start_row, end_row, start_col, and end_col.

If omitted or partially specified, defaults to the entire used range.

Default: {}

sheet_name

string

Name of the worksheet to operate on.

For op=r, if omitted, all sheets are read.

For op=w, op=a, op=i and op=n, this parameter is required.

src

string

Path to the source Excel file.

updates_matrix

list / elements=dictionary

A list of dictionaries describing the cells to update.Each dictionary can include

cell_row The row to update (ignored in append mode).

cell_col The column to update.

cell_value The value to write.

Default: []

Notes

Note

  • This module requires the openpyxl Python library to be installed.

  • Only .xlsx or .xlsm files are supported.

Examples

- name: Read Excel workbook
  ans2dev.general.open_xl:
    src: "/tmp/sample.xlsx"
    op: "r"
    index_by_name: true
  register: result
- debug:
    var: result

- name: Overwrite specific cells
  ans2dev.general.open_xl:
    src: "/tmp/sample.xlsx"
    dest: "/tmp/sample_updated.xlsx"
    op: "w"
    sheet_name: "Sheet1"
    updates_matrix:
      - cell_row: 2
        cell_col: 1
        cell_value: "New Value in row2 col1"
      - cell_row: 3
        cell_col: 2
        cell_value: "Another Value"
    cell_style:
      fontColor: "FF0000"
      bgColor: "FFFF00"
      bold: true

- name: Append new row
  ans2dev.general.open_xl:
    src: "/tmp/sample.xlsx"
    dest: "/tmp/sample_updated.xlsx"
    op: "a"
    sheet_name: "Sheet1"
    updates_matrix:
      - cell_col: 1
        cell_value: "Hostname"
      - cell_col: 2
        cell_value: "MyHost"
    cell_style:
      bgColor: "DDEBF7"
      bold: true

- name: Insert a new row above row 5
  ans2dev.general.open_xl:
    src: "/tmp/sample.xlsx"
    dest: "/tmp/sample_updated.xlsx"
    op: "i"
    sheet_name: "Sheet1"
    updates_matrix:
      - cell_row: 5
        cell_col: 1
        cell_value: "Inserted"
      - cell_row: 5
        cell_col: 2
        cell_value: "Row"
    cell_style:
      italic: true

# Create excel file from ans2dev.general 0.2.0
- name: Create a new Excel file and write data
  ans2dev.general.open_xl:
    dest: "/tmp/new_file.xlsx"
    op: "n"
    sheet_name: "Data"
    updates_matrix:
      - cell_row: 1
        cell_col: 1
        cell_value: "Header"

Return Values

Common return values are documented here, the following are the fields unique to this module:

Key

Description

changed

boolean

Indicates whether the Excel file was modified. For read operations, this is still returned as true, even though no changes were made to the file.

Returned: always

Sample: true

result

dictionary

For read operations, returns a dictionary keyed by sheet name. Each key maps to a list of dictionaries, where each dictionary represents a row with cell values. The keys for each row are determined by the header row (if index_by_name is true) or default to “col_<n>”. For write, append, and insert operations, returns an empty dictionary upon a successful update.

Returned: always

Sample: {"Sheet1": [{"Age": 30, "Name": "Alice"}, {"Age": 25, "Name": "Bob"}]}

Authors

  • Marco Noce (@NomakCooper)