使用python在Excel中创建数据透视表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/22532019/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Creating pivot table in Excel using python
提问by raul
I adapted the following code found hereto create a pivot table in my existing excel sheet:
我修改了此处找到的以下代码以在我现有的 Excel 工作表中创建一个数据透视表：
import win32com.client as win32
win32c = win32.constants
import sys
import itertools
tablecount = itertools.count(1)
def addpivot(wb,sourcedata,title,filters=(),columns=(),
rows=(),sumvalue=(),sortfield=""):
newsheet = wb.Sheets.Add()
newsheet.Cells(1,1).Value = title
newsheet.Cells(1,1).Font.Size = 16
tname = "PivotTable%d"%tablecount.next()
pc = wb.PivotCaches().Add(SourceType=win32c.xlDatabase,
SourceData=sourcedata)
pt = pc.CreatePivotTable(TableDestination="%s!R4C1"%newsheet.Name,
TableName=tname,
DefaultVersion=win32c.xlPivotTableVersion10)
for fieldlist,fieldc in ((filters,win32c.xlPageField),
(columns,win32c.xlColumnField),
(rows,win32c.xlRowField)):
for i,val in enumerate(fieldlist):
wb.ActiveSheet.PivotTables(tname).PivotFields(val).Orientation = fieldc
wb.ActiveSheet.PivotTables(tname).PivotFields(val).Position = i+1
wb.ActiveSheet.PivotTables(tname).AddDataField(wb.ActiveSheet.PivotTables(tname).
PivotFields(sumvalue),sumvalue,win32c.xlSum)
def runexcel():
excel = win32.gencache.EnsureDispatch('Excel.Application')
#excel.Visible = True
try:
wb = excel.Workbooks.Open('18.03.14.xls')
except:
print "Failed to open spreadsheet 18.03.14.xls"
sys.exit(1)
ws = wb.Sheets('defaulters')
xldata = ws.UsedRange.Value
newdata = []
for row in xldata:
if len(row) == 4 and row[-1] is not None:
newdata.append(list(row))
rowcnt = len(newdata)
colcnt = len(newdata[0])
wsnew = wb.Sheets.Add()
wsnew.Range(wsnew.Cells(1,1),wsnew.Cells(rowcnt,colcnt)).Value = newdata
wsnew.Columns.AutoFit()
src = "%s!R1C1:R%dC%d"%(wsnew.Name,rowcnt,colcnt)
addpivot(wb,src,
title="Employees by leads",
filters=("Leads",),
columns=(),
rows=("Name",),
sumvalue="Actual hours",
sortfield=())
if int(float(excel.Version)) >= 12:
wb.SaveAs('new18.03.14.xlsx',win32c.xlOpenXMLWorkbook)
else:
wb.SaveAs('new18.03.14.xls')
excel.Application.Quit()
if __name__ == "__main__":
runexcel()
This line of code,
wb.ActiveSheet.PivotTables(tname).AddDataField(wb.ActiveSheet.PivotTables(tname).PivotFields(sumvalue),sumvalue,win32c.xlSum)
这行代码，
wb.ActiveSheet.PivotTables(tname).AddDataField(wb.ActiveSheet.PivotTables(tname).PivotFields(sumvalue),sumvalue,win32c.xlSum)
returns the following error:
返回以下错误：
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u'PivotFields method of PivotTable class failed', u'xlmain11.chm', 0, -2146827284), None).
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, u'Microsoft Excel', u'PivotFields method of PivotTable class failed', u'xlmain11.chm', 0, -2146827284), None).
When I remove that line, the pivot table is generated without any data fields. Is there something I'm doing wrong?
当我删除该行时，生成的数据透视表没有任何数据字段。有什么我做错了吗？
采纳答案by raul
回答by user3563696
As this is the one of the first Google hits when searching for Excel pivot tables from Python, I post my example code. This code generates a simple pivot table in Excel through a COM server, with some basic filters, columns, rows, and some number formatting applied. I hope this helps someone not to waste half a day on it (like I did...)
由于这是从 Python 搜索 Excel 数据透视表时第一个 Google 搜索结果之一，因此我发布了我的示例代码。此代码通过 COM 服务器在 Excel 中生成一个简单的数据透视表，其中应用了一些基本的筛选器、列、行和一些数字格式。我希望这有助于某人不要在上面浪费半天时间（就像我所做的那样......）
import win32com.client
Excel = win32com.client.gencache.EnsureDispatch('Excel.Application') # Excel = win32com.client.Dispatch('Excel.Application')
win32c = win32com.client.constants
wb = Excel.Workbooks.Add()
Sheet1 = wb.Worksheets("Sheet1")
TestData = [['Country','Name','Gender','Sign','Amount'],
['CH','Max' ,'M','Plus',123.4567],
['CH','Max' ,'M','Minus',-23.4567],
['CH','Max' ,'M','Plus',12.2314],
['CH','Max' ,'M','Minus',-2.2314],
['CH','Sam' ,'M','Plus',453.7685],
['CH','Sam' ,'M','Minus',-53.7685],
['CH','Sara','F','Plus',777.666],
['CH','Sara','F','Minus',-77.666],
['DE','Hans','M','Plus',345.088],
['DE','Hans','M','Minus',-45.088],
['DE','Paul','M','Plus',222.455],
['DE','Paul','M','Minus',-22.455]]
for i, TestDataRow in enumerate(TestData):
for j, TestDataItem in enumerate(TestDataRow):
Sheet1.Cells(i+2,j+4).Value = TestDataItem
cl1 = Sheet1.Cells(2,4)
cl2 = Sheet1.Cells(2+len(TestData)-1,4+len(TestData[0])-1)
PivotSourceRange = Sheet1.Range(cl1,cl2)
PivotSourceRange.Select()
Sheet2 = wb.Worksheets(2)
cl3=Sheet2.Cells(4,1)
PivotTargetRange= Sheet2.Range(cl3,cl3)
PivotTableName = 'ReportPivotTable'
PivotCache = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=PivotSourceRange, Version=win32c.xlPivotTableVersion14)
PivotTable = PivotCache.CreatePivotTable(TableDestination=PivotTargetRange, TableName=PivotTableName, DefaultVersion=win32c.xlPivotTableVersion14)
PivotTable.PivotFields('Name').Orientation = win32c.xlRowField
PivotTable.PivotFields('Name').Position = 1
PivotTable.PivotFields('Gender').Orientation = win32c.xlPageField
PivotTable.PivotFields('Gender').Position = 1
PivotTable.PivotFields('Gender').CurrentPage = 'M'
PivotTable.PivotFields('Country').Orientation = win32c.xlColumnField
PivotTable.PivotFields('Country').Position = 1
PivotTable.PivotFields('Country').Subtotals = [False, False, False, False, False, False, False, False, False, False, False, False]
PivotTable.PivotFields('Sign').Orientation = win32c.xlColumnField
PivotTable.PivotFields('Sign').Position = 2
DataField = PivotTable.AddDataField(PivotTable.PivotFields('Amount'))
DataField.NumberFormat = '#\'##0.00'
Excel.Visible = 1
wb.SaveAs('ranges_and_offsets.xlsx')
Excel.Application.Quit()
