使用openpyxl写入Excel时遇List index out of range问题求助

我开发了一个Python脚本，用来把远程服务器的备份检查结果写入Excel文件，脚本功能如下：

• 建立SSH连接到远程服务器；
• 执行服务器上的bash脚本，该脚本会在备份目录查找近48小时内的备份，没有备份就输出错误信息；
• 通过openpyxl库把bash脚本的输出写入Excel文档。

遇到的问题：脚本首次运行完全正常，但后续任何一次运行都会在终端报错。用空Excel文件时脚本能正常执行，但文件被写入一次后，再次运行就触发错误。

Python脚本代码 #

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)

报错信息 #

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

解决方案 #

这个错误是因为openpyxl在加载已写入过的Excel文件时，遇到了样式引用损坏的问题——原Excel文件可能自带无效的命名样式，或者旧版本openpyxl在保存时破坏了样式结构。以下是两种可行的解决方法：

方法1：重新创建Excel文件（适合不需要保留原有格式的场景） #

如果不需要保留Excel里的原有格式，每次运行脚本时直接创建新工作簿，避免加载损坏的旧文件：

import openpyxl
import os
import subprocess

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

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"
]

# 直接创建新工作簿，而非加载旧文件
wb = openpyxl.Workbook()
sheet = wb.active

row = 12

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

    for line in output:
        if line != "END":
            cell_ref = f'D{row}'
            sheet[cell_ref] = line
            row += 1

    if stderr:
        print(stderr.decode())

wb.save(filepath)

方法2：修复加载逻辑，避免破坏样式 #

如果需要保留原有Excel的格式，修改清空单元格的方式（只清空需要写入的D列），同时升级openpyxl到兼容Python3.6的稳定版本（比如2.6.4，因为openpyxl 3.x不再支持Python3.6）：

import openpyxl
import os
import subprocess

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

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"
]

# 加载文件时指定data_only=True，避免加载样式相关错误
wb = openpyxl.load_workbook(filepath, read_only=False, data_only=True)
sheet = wb.active

# 只清空D列从第12行开始的内容，而非所有单元格
max_row = sheet.max_row
for row_num in range(12, max_row + 1):
    sheet[f'D{row_num}'].value = None

row = 12

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

    for line in output:
        if line != "END":
            cell_ref = f'D{row}'
            sheet[cell_ref] = line
            row += 1

    if stderr:
        print(stderr.decode())

wb.save(filepath)

额外建议 #

1. 执行pip install openpyxl==2.6.4升级到兼容Python3.6的稳定版本，避免旧版本的样式处理bug；
2. 不要在脚本里硬编码密码，改用环境变量或者密钥认证，提升安全性；
3. 用paramiko库替代subprocess调用sshpass，更安全且易维护。

内容的提问来源于stack exchange，提问作者Adrian Tušar