我可以将值写入现有的excel工作表,但无法使用openpyxl将pandas上的数据透视表导出到excel工作表。
以下是我的代码和我的能力:import pandas as pd
import openpyxl as op
import numpy as np
from openpyxl import Workbook, worksheet, load_workbook
wb = op.load_workbook("Table1.xlsx")
#ws = wb.active # selects active excel sheet
print(wb.sheetnames) # Shows all available sheet names
ws = wb['Sheet1'] # Select sheet name "Sheet1"
ws['B2'] = 40 # Input on cell B2
ws['B3'] = 18
ws['B4'] = 20
ws['B5'] = 20
ws['B6'] = 20
ws['C2'] = 8 # Input on cell C2
ws['C3'] = 30
ws['C4'] = 4
ws['C5'] = 10
ws['C6'] = 9
ws['D2'] = 89 # Input on cell D2
ws['D3'] = 300
ws['D4'] = 76
ws['D5'] = 20
ws['D6'] = 4
ws1 = wb['agua'] # Select sheet name "agua"
ws1['B2'] = 4 # Input on cell B2
ws1['B3'] = 60
ws1['B4'] = 0
ws1['C2'] = 90
ws1['C3'] = 23
ws1['C4'] = 20
wb.save("test.xlsx") # Saves to new excell worksheet to avoid mistakes
但是我有一个透视表输出,我需要将透视表的每一列填充到现有的excel文件表中,以便自动填充。如下所示:
^{pr2}$
完整的数据集在链接中,我不能在这里共享(发布),因为stackoverflow.com网站限制字符数为30000
我想把这些列值写在下面:received sent to_send
site
2 32.0 27.0 5.0
3 20.0 17.0 3.0
4 33.0 31.0 2.0
5 40.0 31.0 9.0
All 125.0 106.0 19.0
已包含标题和索引的现有excel工作簿,如下所示:received sent to_send
site
2
3
4
5
All
我有更多的功能为excel工作表,但我只是想了解如何编码,以达到预期的结果。在