mardi 27 mai 2014

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!


1 commentaire:

  1. Tableau online Training, ONLINE TRAINING – IT SUPPORT – CORPORATE TRAINING http://www.21cssindia.com/courses/tableau-online-training-226.html The 21st Century Software Solutions of India offers one of the Largest conglomerations of Software Training, IT Support, Corporate Training institute in India - +919000444287 - +917386622889 - Visakhapatnam,Hyderabad Tableau online Training, Tableau Training, Tableau, Tableau online Training| Tableau Training| Tableau| "Courses at 21st Century Software Solutions
    Talend Online Training -Hyperion Online Training - IBM Unica Online Training - Siteminder Online Training - SharePoint Online Training - Informatica Online Training - SalesForce Online Training - Many more… | Call Us +917386622889 - +919000444287 - contact@21cssindia.com
    Visit: http://www.21cssindia.com/courses.html"

    RépondreSupprimer