如何用Openpyxl在Excel指定区域批量插入JSON来源数据？

问题背景 #

需要将从JSON文件提取的多列数据（如Product_ID、Code_Subgroup等）批量插入到Excel的指定区域（例如A10:G35），每类数据对应Excel的一列。原代码仅支持单列插入，遍历嵌套数据时频繁出现Cannot convert [...] to excel类型错误或索引越界问题。

问题根源 #

原代码存在以下问题：

• 未初始化索引变量j，导致运行报错
• 直接遍历多列单元格范围时，循环逻辑不匹配嵌套数据结构
• 未处理数据长度与目标区域行数不一致的情况，引发索引越界

正确实现代码 #

假设data_to_insert是嵌套列表结构，每个子列表对应一个参数的列数据（例如[[Product_ID数据列表], [Code_Subgroup数据列表], ...]），实现代码如下：

import openpyxl
import _read_json as rj

# 定义参数列表，顺序对应Excel中要填充的列顺序（A到G列）
parameters = ["Product_ID", "Code_Subgroup", "Stock", "Serial", "Description", "AC", "DC"]
# 从JSON提取对应参数的列数据，返回嵌套列表
data_to_insert = rj.get_value_parameters(parameters)

def add_data_to_sheet(target_range, sheet, data):
    # 拆分目标区域的起始和结束单元格
    start_cell, end_cell = target_range.split(':')
    # 转换列字母为列索引（A=1，B=2...）
    start_col = openpyxl.utils.column_index_from_string(start_cell[0])
    # 提取起始行和结束行的数值
    start_row = int(start_cell[1:])
    end_row = int(end_cell[1:])
    # 计算目标区域的总行数
    total_rows = end_row - start_row + 1

    # 遍历每一组列数据，对应Excel的一列
    for col_offset, column_data in enumerate(data):
        current_col = start_col + col_offset
        # 逐行填充数据
        for row_offset in range(total_rows):
            current_row = start_row + row_offset
            # 避免数据长度不足导致的索引越界
            if row_offset < len(column_data):
                sheet.cell(row=current_row, column=current_col).value = column_data[row_offset]
            else:
                # 数据不足时填充空值
                sheet.cell(row=current_row, column=current_col).value = None

# 示例调用
if __name__ == "__main__":
    # 加载Excel文件
    workbook = openpyxl.load_workbook("your_spreadsheet.xlsx")
    # 获取目标工作表（这里用活动表，也可指定表名：workbook["Sheet1"]）
    worksheet = workbook.active
    # 指定目标范围：A10到G35（对应7个参数）
    add_data_to_sheet("A10:G35", worksheet, data_to_insert)
    # 保存修改后的文件
    workbook.save("updated_spreadsheet.xlsx")

关键说明 #

• 区域解析：通过拆分目标范围字符串，精准获取起始/结束行列，避免直接操作单元格范围的循环混乱
• 数据匹配：用enumerate遍历嵌套数据，确保每一组列数据对应Excel的一列，顺序与parameters列表一致
• 越界防护：判断数据长度与目标行数，不足时填充空值，彻底解决索引越界问题
• 精准定位：使用cell(row, column)方法直接定位单元格，比遍历sheet[rango]更灵活可控

内容的提问来源于stack exchange，提问作者Roger Mireles