Création de tableau croisé dynamique dans Excel à l'aide de python - Stack Overflow

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!

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!

Source