使用openpyxl写入Excel时重复数据丢失问题求助

我正在使用openpyxl库向Excel文件写入数据，测试代码如下：

import openpyxl

def writeToExcel(diskTag,size,route,outputName):
    wb = openpyxl.Workbook()    
    _fileName=outputName+".xlsx"
    wb.save(_fileName)       
    wb = openpyxl.load_workbook(_fileName, data_only=True)
    #pageName0="GIF"
    s1 = wb.active      
    #s1 = wb[pageName0]            
    #print(size)
    for i in size:
        s1.cell(size.index(i)+1,1).value = i  
        print(i)
    for i in route:
        s1.cell(route.index(i)+1,2).value = diskTag+i
        print(i)
    #s1.cell(9,1).value = size[8]
    #s1.cell(10,1).value = size[9]
    wb.save(_fileName)
    
diskTag="-"
outputName="test"
size = [1,2,3,4,5,6,7,8,9,9,9,4,1,2]
route = ["a","b","c","d","e","e","d","f","g","r","h","h","f","g","h"]
writeToExcel(diskTag,size,route,outputName)

执行代码后生成的Excel文件中重复数据丢失，比如size列表里的后两个9无法写入对应行，route里的重复元素也出现类似覆盖问题。

错误原因 #

问题出在list.index(i)的使用逻辑上：

• list.index(i)只会返回列表中第一个匹配元素的索引，当列表存在重复元素时，后续的重复元素会复用第一个元素的索引，导致之前写入的单元格被覆盖，最终每个重复元素仅能保留最后一次写入的结果，看起来像是前面的重复数据丢失了。

比如size = [1,2,3,4,5,6,7,8,9,9,9]，遍历到第二个9时，size.index(9)仍返回8（第一个9的索引），会把第9行的单元格重新赋值为9；第三个9同理，最终第9行是9，但第10、11行没有数据。

修复方案 #

改用enumerate()函数同时获取元素的索引和值，确保每个元素对应正确的行号，同时优化不必要的文件IO操作：

import openpyxl

def writeToExcel(diskTag,size,route,outputName):
    wb = openpyxl.Workbook()    
    _fileName=outputName+".xlsx"
    s1 = wb.active      
    # 遍历size，用enumerate获取索引（从0开始），行号=索引+1
    for idx, val in enumerate(size):
        s1.cell(row=idx+1, column=1).value = val  
        print(val)
    # 遍历route，同理使用enumerate
    for idx, val in enumerate(route):
        s1.cell(row=idx+1, column=2).value = diskTag + val
        print(val)
    wb.save(_fileName)
    
diskTag="-"
outputName="test"
size = [1,2,3,4,5,6,7,8,9,9,9,4,1,2]
route = ["a","b","c","d","e","e","d","f","g","r","h","h","f","g","h"]
writeToExcel(diskTag,size,route,outputName)

内容的提问来源于stack exchange，提问作者Q oo