用Python在Excel中创建数据透视表时出现pywintypes.com_error

Question

我根据这个链接找到的代码，调整了一下，想在我现有的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()

这行代码：

wb.ActiveSheet.PivotTables(tname).AddDataField(wb.ActiveSheet.PivotTables(tname).PivotFields(sumvalue),sumvalue,win32c.xlSum)

出现了以下错误：

pywintypes.com_error: (-2147352567, '发生了异常。', (0, u'微软Excel', u'PivotTable类的PivotFields方法失败', u'xlmain11.chm', 0, -2146827284), None)。

当我把那行代码去掉时，数据透视表生成了，但没有任何数据字段。请问我是不是哪里做错了？

Answer 1

因为这是在网上搜索“从Python生成Excel数据透视表”时，最先出现的结果之一，所以我分享一下我的示例代码。这段代码通过一个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()

Answer 2

从这个链接 PivotTable.AddDataField 方法 (Excel) 了解到，在 expression .AddDataField(Field, Caption, Function) 这个方法中，只有 Field 是必须的，其他两个参数是可选的。我把它们去掉了，代码运行得很好！