导出数据框到新的excel工作表，也写入特定的值到特定的工作表

Question

我遇到了一个问题，我认为这与需要:

1. 将数据框导出到新的Excel工作表(在导出时创建)
2. 将特定值写入同一工作簿中的现有工作表
3. 在循环中执行上述两个

我可以让1和3单独工作，我也可以让2和3单独工作，但是当我试图同时做这三件事时，它不起作用。我认为使用xlsxwriter引擎与表冲突的pandas to_excel存在一些问题。写入(行，列，值)到同一个工作簿

例如，这可以单独工作(注意我有"writer"导出数据框到新表的内容(注释掉了):

import pandas as pd
import xlsxwriter
loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])

workbook = xlsxwriter.Workbook('C:\Test\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')
writer = pd.ExcelWriter('C:\Test\Test.xlsx', engine = 'xlsxwriter')
for sheets in loopList:

#df.to_excel(writer, sheet_name = sheets, index=False)
totalsSheet.write(counter, counter, sheets + str(counter)) 
counter+=1

#writer.save()
#writer.close()
workbook.close()

上面的代码使test.xlsx工作簿具有一个总计工作表，其中的"A1"， "B2"等以递增的行/列显示。

同样，当我注释掉工作簿的东西和联合国注释熊猫导出数据框架到新的工作表，这也工作:

import pandas as pd
import xlsxwriter
loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])

workbook = xlsxwriter.Workbook('C:\Test\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')
writer = pd.ExcelWriter('C:\Test\Test.xlsx', engine = 'xlsxwriter')
for sheets in loopList:

df.to_excel(writer, sheet_name = sheets, index=False)
#totalsSheet.write(counter, counter, sheets + str(counter)) 
counter+=1

writer.save()
writer.close()
#workbook.close()

上面给了我一个新的测试工作簿，有5个工作表(a, B, C等)，每个工作表导出相同的数据框架。

然而，我似乎不能两者兼顾;根据我写到Excel的行顺序，它仍然只做一个或另一个(我没有得到错误，我只是得到一个结果，不是我想做的两件事)。

是否有一种方法可以在同一个循环中完成这两件事?

我正在使用python 3.x.x。谢谢你的帮助。

Answer 1

您能不能只在单独的循环中运行它，每个循环打开和关闭文件，以确保每个进程都可以使用它?类似…

import pandas as pd
import xlsxwriter
loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
counter = 1

# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])
with pd.ExcelWriter('C:\Test\Test.xlsx', engine = 'xlsxwriter') as writer: 
for sheets in loopList: 
df.to_excel(writer, sheet_name = sheets, index=False)
workbook = xlsxwriter.Workbook('C:\Test\Test.xlsx')
totalsSheet = workbook.add_worksheet('Totals')
for sheets in loopList: 
totalsSheet.write(counter, counter, sheets + str(counter)) 
counter+=1

workbook.close()

查看更多内容，正如文档所说的xlsxwriter:

无法读取或修改现有的Excel XLSX文件。

所以你之前尝试的是导致覆盖发生。但是，如果您进一步查看文档，就会发现关键是从pd创建工作簿对象。ExcelWriter对象。这意味着两个库可以同时写入文件。

我安装了xlsxwriter，下面的代码为我工作:

import pandas as pd
import xlsxwriter
# data to write
loopList = ["A","B","C","D","E"]
data = [['tom', 10], ['nick', 15], ['juli', 14]] 
# Create the pandas DataFrame 
df = pd.DataFrame(data, columns = ['Name', 'Age'])
# create the writer object
writer = pd.ExcelWriter('Test.xlsx', engine='xlsxwriter')
# create the workbook object from the current writer object
# this means that pandas and xlsxwriter can both write to it
workbook = writer.book 
totalsSheet = workbook.add_worksheet('Totals')
# set the counter
counter = 1
# lopo through and use xlsx writer to write specific cells
for sheets in loopList: 
totalsSheet.write(counter, counter, sheets + str(counter)) 
counter+=1

# loop through generating new sheets and writing dfs to the file
for sheets in loopList: 
df.to_excel(writer, sheet_name = sheets, index=False)
# save the written data
writer.save()