List index out of range (openpyxl) - writing to a document

Question

I am encountering an issue with a Python script that writes to an Excel file. The script works flawlessly on the first run, but any subsequent runs result in an error in the terminal.

Here is a brief overview of the script's functionality:

1. It establishes an SSH connection to a remote server.
2. It executes a bash script located on the server. This bash script navigates to the backup-location directory and searches for backups created in the last 48 hours. If no backup is found, it outputs an error message.
3. The output from the bash script is sent back to the Python script, which writes this output to an Excel document using the openpyxl library.

The problem arises when attempting to run the script more than once. After the initial successful execution, subsequent attempts result in an error.

Python script:

import openpyxl
import os
import subprocess

# location of the excel document
location = '/nfs/commonshare/SCRIPT-DEVELOPING'
filename = 'test-check.xlsx'
filepath = os.path.join(location, filename)

# BACKUP AUTOMATIC CHECKING

# SSH connection information
commands = [
    "sshpass -p 'password1' ssh -o PreferredAuthentications=password -o PubkeyAuthentication=no -o StrictHostKeyChecking=no username1@host1 '/home/location1/test1.sh' 2>/dev/null",
    "sshpass -p 'password2' ssh -o PreferredAuthentications=password -o PubkeyAuthentication=no -o StrictHostKeyChecking=no username2@host2 '/home/location2/test2.sh' 2>/dev/null",
    "sshpass -p 'password3' ssh -o PreferredAuthentications=password -o PubkeyAuthentication=no -o StrictHostKeyChecking=no username3@host3 '/home/location3/test3.sh' 2>/dev/null"
]

# Load the existing Excel document
wb = openpyxl.load_workbook(filepath, read_only=False)
sheet = wb.active

for row in sheet.iter_rows():
    for cell in row:
        cell.value = None

# Starting row for D column
row = 12

# Execute each command one by one
for command in commands:
    process = subprocess.Popen(command, shell=True, stdout=subprocess.PIPE, stderr=subprocess.PIPE)
    stdout, stderr = process.communicate()
    output = stdout.decode().splitlines()

    # Write the output to the Excel document
    for line in output:
        if line == "END":
            row += 0  # Move to next line when 'END' is encountered
        else:
            cell_ref = f'D{row}'
            sheet[cell_ref] = line  # Write data into cell
            row += 1  # Move to next line for next backup detail

    # Print any errors
    if stderr:
        print(stderr.decode())

# Save changes to Excel document
wb.save(filepath)

Here is an error i am getting:

15 is out of range
Traceback (most recent call last):
  File "checklist-script.py", line 24, in <module>
    wb = openpyxl.load_workbook(filepath, read_only=False)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 348, in load_workbook
    reader.read()
  File "/usr/local/lib/python3.6/site-packages/openpyxl/reader/excel.py", line 301, in read
    apply_stylesheet(self.archive, self.wb)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/stylesheet.py", line 198, in apply_stylesheet
    stylesheet = Stylesheet.from_tree(node)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/stylesheet.py", line 103, in from_tree
    return super(Stylesheet, cls).from_tree(node)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/descriptors/serialisable.py", line 103, in from_tree
    return cls(**attrib)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/stylesheet.py", line 94, in __init__
    self.named_styles = self._merge_named_styles()
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/stylesheet.py", line 114, in _merge_named_styles
    self._expand_named_style(style)
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/stylesheet.py", line 124, in _expand_named_style
    xf = self.cellStyleXfs[named_style.xfId]
  File "/usr/local/lib/python3.6/site-packages/openpyxl/styles/cell_style.py", line 189, in __getitem__
    return self.xf[idx]
IndexError: list index out of range

When providing an empty Excel document to this location:
/nfs/commonshare/SCRIPT-DEVELOPING

It works. However, as soon as the document is written over once, the terminal returns the error mentioned above.

Answer 1

I haven't found a way to fix this, but I got around it by using one file as a starting file,

starting-file.xlsx

which the script opens, and another file as the destination file.

destination-file.xlsx

The script saves everything from the starting file, along with all the changes that were made, to the destination file, leaving the starting file intact.

starting-file.xlsx (script opens this file) -->
script makes changes (writes to the file) --> 
instead of saving this file it saves file and changes under different name (destination-file.xlsx) -->
The original file stays intact