Managing Microsoft Excel Sheets from within Red Hat Ansible

February 18, 2020

Tweet This:
Share on LinkedIn:

By Mohamed Abouahmed, Kovarus Professional Services Engineer

While working on some Red Hat Ansible automation playbooks, I needed to open Microsoft Excel files from my Ansible playbook to search for content, add content, and update some parts of the sheet. Unfortunately, there is no Ansible-native way I know of that can search and edit Excel files. I therefore had to write my own Ansible modules using Python to be able to manage Excel files.

The Use Case

There are many use cases one can think of, but mine is simple. We have an Excel sheet with a list of 1000+ network devices. The requirement is to collect certain IP and routing information from these 1000+ devices, verify the location and admin contacts (from the SNMP configuration), and save the collected information back into the same Excel sheet.

Objective

The objective of this article is not to explain how the network device information is collected, but rather to focus on how to access the Excel sheet, read data from it, and write into it. I am therefore referencing the GitHub sources for two Python modules I put together, open_excel and search_excel, and will go over a couple of examples on how to use these two modules.

Installing the Code

You need to have openpyxl installed before you use open_excel. To install openpyxl:

sudo pip install openpyxl

For open_excel, you can either install open_excel using pip:

sudo pip install open_excel

Or, you can do that manually by downloading the module .py files from the GitHub open_excel project directory and place the files under your Ansible modules directory. Use export $ANSIBLE_LIBRARY to check the configured library directory or look for the library variable in your /etc/ansible/ansible.cfg file. You can also just download the two .py files under your project directory “<project directory>/library”.

Using the Modules — Read Excel Sheet Content

The open_excel GitHub has two examples and a sample Excel file as a demo. The modules’ code has detailed usage documentation. I am including the documentation at the end of this article for your reference.

module_examples/
     read_excel_example.yml
     update_excel_example.yml
     device_list.xlsx

Below, are another two basic Ansible examples to read and update Excel files. The Excel file has a list of device names, devices starting with “R” are routers, and devices starting with “S” are switches.

Use open_excel with read-only “r” operational mode (op:) to get the Loopback0 IP address shown in the Devices sheet. The cell reference is specified in the read_range dict option.

  - name: Read Excel File
    hosts: localhost
    connection: local
    gather_facts: no
 
    tasks:
      - name: Show Device Loopback0 IP Address
        register: result
        open_excel:
          src: "device_list.xlsx"
          sheet_name: "Devices"
          op: "r"
          read_range: "{{ {'start_row': 4, 'start_col': 2, 'end_row': 4, 'end_col': 2} }}"
 
      - debug: var=result

Result:

ok: [localhost] => {
    "result": {
        "changed": true,
        "failed": false,
        "sheet_index_0": [
            {
                "device_loopback0_ip": "10.10.5.50"
            }
         ]
    }
}

Using the Modules — Update the Excel Sheet

Use open_excel with write “w” operational mode (op:) to update R550 device_location and device_serial_num in the Devices sheet. The value of the cells to be updated are specified in the “update_matrix” list of dict option.

      - name: Update the R550
        open_excel:
          src: "device_list.xlsx"
          dest: "device_list_updated.xlsx"
          sheet_name: "Devices"
          op: "w"
          updates_matrix: "{{ [{'cell_row': 4, 'cell_col': 3, 'cell_value': 'Sacramento Data Center'}, {'cell_row': 4, 'cell_col': 4, 'cell_value': 'SN55566677'}] }}"

Result:

Using the Modules — Search Excel Sheet

Use search_excel to search the Excel document. The “i” in search_options makes the search case-insensitive. The search_range options are optional, if omitted the module will look in the entire sheet. If no sheet_name is specified, the module will look in the entire workbook.

For a partial-match, case-insensitive search, just use “i” in the search_options. So, to look for all routers in the sheet, use “r” as your search_device search token, this will list all devices starting with “r” or “R.”

  - name: Search Excel File
    hosts: localhost
    connection: local
    gather_facts: no
 
    vars:
        search_range:
          start_col: 1 # Search only in the 1st column of the sheet
          end_col: 1
          start_row: 2 # Search from row #2 till the end of the sheet (no end_row is specified)
 
       search_device: "s120"
 
    tasks:
      - name: Looking-up Device IP
        search_excel:
          src: "device_list.xlsx"
          search_token: "{{ search_device }}"
          search_range: "{{ search_range }}"
          search_options: "iw"                  # "i" to ignore case, "w" for whole words
          sheet_name: "Devices"
        register: device_list
 
      - debug: var=device_list.list      # Show the excel sheet cell reference(s) where your search device was located

Result:

ok: [localhost] => {
    "device_list.list": [
        {
            "col_no": 1,
            "row_no": 6,
            "sheet_name": "Devices"
        }
    ]
}

Module Documentation — open_excel

Returns:

Returns a list of dict with the following elements:

  • sheet_index_<n>: Reference to the index of the sheet found in the Excel workbook, where <n> refers to the sheet index number (starting from 0)
    • <column header> or col_<n>: If the option “index_by_name” is True, the dictionary key will be the header of the column in the excel sheet. If “index_by_name” is False, the sheet column key will be “col_<n>” where <n> is the column number.

Options:

  • src: The name of the Excel spreadsheet
    • Required: true
    • Type: str
  • dest: Only relevant when Excel file is opened for update. The source filename will not be overwritten, instead, the sheet after the update will be saved as this specified destination filename
    • Type: str
    • Optional
    • Default: if missing, the generated file will be “<source filename>_updated.xlsx”. If the file is opened with “r” mode (read), the “dest” value will be ignored
  • op: The operational mode of the file.
    • Valid “op” values:
      • r: to open the file for “read only”
      • w: to write the updates_matrix values in the specified calls. Any old cell value will be overwritten. If cell_row is 0, the module will assume an “a” operational mode
      • a: to append the sheet with a new row at the end of the sheet. The row_no value specified in updates_matrix will be ignored
      • i: insert the updates_matrix values in the row above the row number specified in the row_no parameter. Make sure the row_no values specified in updates_matrix are all the same for valid insert operation. Different row_no values may have unintended results
    • The original file will not be overwritten, instead, another file with a name specified in the parameter “dest” will be created.
    • Required: true
    • Type: str
  • index_by_name: If true, returns the result values dictionary with keys specified in the excel sheet column headers. When False, keys will carry “col_<n>” format, where <n> is the column number.
    This option is useful when headers carry titles with invalid dictionary format (have special characters, spaces, etc.)
    • Type: bool
    • Optional
    • Default: true
  • read_range: The start and end cell references to specify the range to read from the sheet(s).
    • Type: dict
    • Optional
    • Default: if missing, the module will read the entire excel sheet (or the entire workbook if the sheet name not specified)
    • Elements:
      • start_row: Along with “start_col” specifies the cell reference from which the module will start reading the sheet. If omitted, the read process will start from the first row in the worksheet
      • start_col: Along with “start_row” specifies the cell reference from which the module will start reading the sheet. If omitted, the read process will start from the first column in the worksheet
      • end_row: Along with “end_col” specifies the cell reference at which the module ends reading the sheet content. If omitted, the read process will end at final row in the worksheet
      • end_col: Along with “end_row” specifies the cell reference at which the module ends reading the sheet content. If omitted, the read process will end at final column in the worksheet.
  • updates_matrix: A list of dictionary that contains the following:

cell_row: Along with “cell_col” specifies the cell reference at which “cell_value” will be written
cell_col: Along with “cell_row” specifies the cell reference at which “cell_value” will be written
cell_value: The value to write to the cell reference (cell_row, cell_col).

  • cell_style: The foreground and background color of the cell(s) being updated. If omitted, the cell will be updated with the current existing colors, no change will happen.
    • Type: dict
    • Optional
    • Default: If omitted, no style will be applied
    • Elements:
      • fontColor: A string representing the RGB value of the font color of the cell(s) being updated.
      • bgColor: A string representing the RGB value of the background color of the cell(s) being updated.
      • bold: A boolean value to set the font strike as bold or normal
      • italic: A boolean value to set the font italics True or False
      • underline: A boolean value to set the font underline True or False
  • sheet_name: The sheet name to read or updated. This parameter can only be omitted when the Excel sheet is opened as “r” (read-only)
    • Type: str
    • Optional
    • Default: sheet_name can only be omitted when the Excel sheet is opened as “r” (read-only). When omitted, the entire worksheet is opened and returned

Module Documentation — search_excel

Returns:

A list of dictionaries containing cell references where the specified search_token was found. Will return an empty list if search_token was not found. The dict inside the return list has the following elements:

  • sheet_name: The name of the sheet where the specified search_token was found.
  • row_no: Along with “col_no” specifies the cell reference where the specified search_token was found.
  • col_no: Along with “row_no” specifies the cell reference where the specified search_token was found.

Options:

  • src: The name of the Excel spreadsheet
    • Required: true
    • Type: str
  • search_token: The search token to look for inside the excel sheet
    • Required: true
    • Type: str
  • search_range: The start and end cell references to specify the range at which the search-token will be looked at.
    • Type: dict
    • Optional
    • Default: if missing, the module will look in the entire excel sheet (or the entire workbook if the sheet name not specified)
    • Elements:
      • start_row: Along with “start_col” specifies the cell reference from which the search starts. If omitted, the search will start from the first row in the worksheet
      • start_col: Along with “start_row” specifies the cell reference from which the search starts. If omitted, the search will start from the first column in the worksheet
      • end_row: Along with “end_col” specifies the cell reference at which the search ends. If omitted, the search will end at final row in the worksheet
      • end_col: Along with “end_row” specifies the cell reference at which the search ends. If omitted, the search will end at final column in the worksheet
  • search_options: A string carrying all search options The options are as follows:
    • i: ignore case
    • w: whole words
    • x: exact match of the cell content

Notes:

      • If both “ix” options are specified, the exact non-case sensitive cell search will be processed
      • If both “wx” options are specified, the “w” option will be ignored, and module will search for exact cell content
      • Any other character specified in the search_options will be ignored.
      • Type: str
    • Optional
    • Default: if omitted, a case-sensitive search for any occurrence of the “search_token” will be processed
  • sheet_name: The sheet name to be searched
    • Type: str
    • Optional
    • Default: if omitted, all of the workbook sheets are searched

So that wraps up this article on Excel from within Ansible. I hope this article helps.


Looking to learn more about modernizing and automating IT? We created the Kovarus Proven Solutions Center (KPSC) to let you see what’s possible and learn how we can help you succeed. To learn more about the KPSC go to the KPSC page.

Also, follow Kovarus on LinkedIn for technology updates from our experts along with updates on Kovarus news and events.