使用python在Excel中创建数据透视表 [英] Creating pivot table in Excel using 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()

这行代码
wb.ActiveSheet.PivotTables（tname）.AddDataField（wb .ActiveSheet.PivotTables（tname）.PivotFields（sumvalue），sumvalue，win32c.xlSum）

返回以下错误：

pywintypes.com_error： u'xlmain11.chm'，0，-2146827284），无）。

当我删除该行时，任何数据字段。有没有什么我在做错？

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

推荐答案

从这里在表达式.AddDataField（字段，标题，功能） 只有字段是必需的，其他两个参数是可选的。我删除他们，代码工作正常！

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!

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