每次运行后，我都会得到一个带有测试结果的新csv文件，并且我能够将所有excel文件合并到一个excel文件中，每次运行都作为工作表名称。
为此，我使用xlwt
将不同的excel文件添加到统一excel文件中的其他人参考代码：
book = xlwt.Workbook()
for file in os.listdir(path):
if file.endswith('csv'):
sheet = book.add_sheet(file[:-4])
with open(path + file) as filname:
reader = csv.reader(filname)
i = 0
for row in reader:
for j, each in enumerate(row):
sheet.write(i, j, each)
i += 1
book.save("consolidate_result.xls")
现在我有了一个场景，在这个场景中，我必须在Excel的新摘要表中提供不同测试运行的摘要。
下面是我的示例Excel文件，其中包含多个具有以下数据格式的工作表，第一列作为测试名称，第二列作为测试状态，第三列作为该测试的时间值：
名称为的第1页运行1
Test Name Test Status Time Value
Test 1 PASS 00:06:43
Test 2 Fail 00:06:24
Test 3 PASS 00:06:10
Test 4 PASS 00:05:25
Test 5 Fail 00:05:07
Test 6 PASS 00:02:45
带有名称运行2的工作表2
Test Name Test Status Time Value
Test 1 PASS 00:05:43
Test 2 Fail 00:04:24
Test 3 PASS 00:05:10
Test 4 PASS 00:06:25
Test 5 PASS 00:03:07
Test 6 PASS 00:04:45
第3页，名称运行3
Test Name Test Status Time Value
Test 1 PASS 00:06:40
Test 2 PASS 00:06:52
Test 3 PASS 00:05:50
Test 4 PASS 00:05:35
Test 5 PASS 00:06:17
Test 6 PASS 00:03:55
我想要实现的是得到一个新的工作表，上面有一些名字，比如状态或合并结果，在现有的excel文件中使用这种格式
Test Name Test-Status Run 1 Run 2 Run 3
Test 1 Pass 00:06:43 00:05:38 00:06:43
Test 2 Fail 00:06:24 00:05:56 00:06:24
Test 3 Pass 00:06:10 00:06:43 00:06:10
Test 4 Pass 00:05:25 00:05:32 00:05:25
Test 5 Fail 00:05:07 00:05:22 00:05:07
Test 6 Pass 00:02:45 00:07:26 00:02:45
我试图通过使用pd读取excel文件将结果添加到List中。ExcelFile（filename），然后遍历工作表并将数据添加到结果列表中
df = pd.read_excel(fname, None)
result=[]
for x in range(len(df.keys())):
dfx=pd.read_excel(xls, xls.sheet_names[x])
result.append(dfx)
当我使用writer=pd时，是否有人可以帮助我将结果合并到新的表格中。ExcelWriter（fname，engine='openpyxl'）和df。到excel（编写器，工作表\u name='Summary'）它将覆盖excel并添加一个名为Summary的空白工作表。提前谢谢
我建议使用sheet\u name=None参数创建所有sheets的Ordered Dictionary of DataFrames
path = "file.xlsx"
df = pd.read_excel(path, sheet_name=None)
print (df)
OrderedDict([('Run 1', Test Name Test Status Time Value
0 Test 1 PASS 00:06:43
1 Test 2 Fail 00:06:24
2 Test 3 PASS 00:06:10
3 Test 4 PASS 00:05:25
4 Test 5 Fail 00:05:07
5 Test 6 PASS 00:02:45), ('Run 2', Test Name Test Status Time Value
0 Test 1 PASS 00:05:43
1 Test 2 Fail 00:04:24
2 Test 3 PASS 00:05:10
3 Test 4 PASS 00:06:25
4 Test 5 PASS 00:03:07
5 Test 6 PASS 00:04:45), ('Run 3', Test Name Test Status Time Value
0 Test 1 PASS 00:06:40
1 Test 2 PASS 00:06:52
2 Test 3 PASS 00:05:50
3 Test 4 PASS 00:05:35
4 Test 5 PASS 00:06:17
5 Test 6 PASS 00:03:55)])
然后循环和concat并按列对齐Test Name和Test Status，因此有必要设置索引。还为不匹配的值添加了NaNs：
d = {k:v.set_index(['Test Name','Test Status'])['Time Value'] for k, v in df.items()}
result= pd.concat(d, axis=1).reset_index()
print (result)
Test Name Test Status Run 1 Run 2 Run 3
0 Test 1 PASS 00:06:43 00:05:43 00:06:40
1 Test 2 Fail 00:06:24 00:04:24 NaN
2 Test 2 PASS NaN NaN 00:06:52
3 Test 3 PASS 00:06:10 00:05:10 00:05:50
4 Test 4 PASS 00:05:25 00:06:25 00:05:35
5 Test 5 Fail 00:05:07 NaN NaN
6 Test 5 PASS NaN 00:03:07 00:06:17
7 Test 6 PASS 00:02:45 00:04:45 00:03:55
最后一次附加到新图纸中的现有文件：
#https://stackoverflow.com/a/42375263
from openpyxl import load_workbook
book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book
result.to_excel(writer, sheet_name = 'Status', index=False)
writer.save()
writer.close()
