python做数据透视表,使用python在Excel中创建数据透视表

I wrote the following code to create a pivot table in an existing excel sheet:

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)

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).

When I remove that line, the pivot table is generated without any data fields. Is there something I'm doing wrong?

解决方案

Found from here that in expression .AddDataField(Field, Caption, Function) only Field is required and the other two parameters are optional. I removed them and the code works fine!