有没有更有效的方法将列表传递到excel文件(使用Openpyxl)

Question

在下面的代码中，我将列表传递给与列表长度一样多的excel列。我能用更有效的方法吗？理想情况下，我希望传递列表，并用结果填充整行，然后移动到下一行以写入下一个结果。

#lets say the list has 15 indexes, i just didn't write them 
resultList = []
def save_results():
wb = openpyxl.Workbook()
os.chdir(r'C:UsersResults')
sheet = wb['Sheet']
sheet['A1'] = 'TEST1'
sheet['B1'] = 'TEST2'
sheet['C1'] = 'TEST3'
sheet['D1'] = 'TEST4'
sheet['F1'] = 'TEST5'
sheet['E1'] = 'TEST6'
sheet['G1'] = 'TEST7'
sheet['H1'] = 'TEST8'
sheet['I1'] = 'TEST9'
sheet['J1'] = 'TEST10'
sheet['K1'] = 'TEST11'
sheet['L1'] = 'TEST12'
sheet['M1'] = 'TEST13'
sheet['N1'] = 'TEST14'
sheet['O1'] = resultList[0] 
sheet['P1'] = resultList[1] 
sheet['A2'] = resultList[2] 
sheet['B2'] = resultList[3] 
sheet['C2'] = resultList[4] 
sheet['D2'] = resultList[5] 
sheet['E2'] = resultList[6] 
sheet['F2'] = resultList[7] 
sheet['G2'] = resultList[8] 
sheet['H2'] = resultList[9] 
sheet['I2'] = resultList[10] 
sheet['J2'] = resultList[11] 
sheet['K2'] = resultList[12] 
sheet['L2'] = resultList[13] 
wb.save(datetime_output_results +'.xlsx')

Answer 1

您可以附加这样的列表(如下面的代码所示(，也可以制作testList并以与resultList:相同的方式附加它

def save_results():
os.chdir(r'C:UsersResults')
wb = openpyxl.Workbook()
sheet = wb['Sheet']
sheet['A1'] = 'TEST1'
sheet['B1'] = 'TEST2'
sheet['C1'] = 'TEST3'
sheet['D1'] = 'TEST4'
sheet['F1'] = 'TEST5'
sheet['E1'] = 'TEST6'
sheet['G1'] = 'TEST7'
sheet['H1'] = 'TEST8'
sheet['I1'] = 'TEST9'
sheet['J1'] = 'TEST10'
sheet['K1'] = 'TEST11'
sheet['L1'] = 'TEST12'
sheet['M1'] = 'TEST13'
sheet['N1'] = 'TEST14'
ws = wb.active
ws.append(resultList)
wb.save('10P_Results.xlsx')

Answer 2

由于您的序列不遵循字母和数字的简单递增规则，因此可以执行以下操作：

title_sequence = [x + "1" for x in ['A', 'B', 'C', 'D', 'F', 'E', 'G', 'H', 'I', 'K',
'L', 'M', 'N']]
list_sequence = ['O1', 'P1']
list_sequence.extend(x + "2" for x in ['A', 'B', 'C', 'D', 'E', 'F', 'G', 'H', 'I',
'J', 'K', 'L'] 
for idx, c in enumerate(title_sequence):
sheet[c] = "TEST{}".format(idx+1)
for idx, c in enumerate(list_sequence):
sheet[c] = resultList[idx]