使用python在Excel中创建数据透视表会导致pywintypes.com_error

问题描述

我对在此处找到的以下代码进行了修改，以创建数据透视表在我现有的Excel工作表中:

I adapted the following code found here to create a pivot table in my 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()

这行代码， wb.ActiveSheet.PivotTables(tname).AddDataField(wb.ActiveSheet.PivotTables(tname).PivotFields(sumvalue),sumvalue,win32c.xlSum)

返回以下错误:

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?

推荐答案

从 PivotTable.AddDataField方法(Excel)，在expression .AddDataField(Field, Caption, Function)中仅需要Field，其他两个参数是可选的.我删除了它们，代码工作正常！

Found from PivotTable.AddDataField method (Excel) 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!

这篇关于使用python在Excel中创建数据透视表会导致pywintypes.com_error的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！