create a pivotchart with python win32com causes pywintypes.com_error

Question

is there anyone can give a example code for creating a pivotchart in excel? I have got an example to create a pivottable with pivottable, it works , while when I try to create a pivotchart in the same ways, it will always failed!

import win32com.client
import os

Excel   = win32com.client.gencache.EnsureDispatch('Excel.Application') 
win32c  = win32com.client.constants
wb      = Excel.Workbooks.Add()
Sheet1  = wb.Worksheets.Add()
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("Sheet1")
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)
PivotChart = PivotCache.CreatePivotChart(ChartDestination="Sheet1")
Excel.Visible = 1
cwd = os.getcwd()
wb.SaveAs(os.path.join(cwd,'test.xlsx'))
Excel.Application.Quit()

it will always report the error:

Traceback (most recent call last):
  File "excelpivotchart.py", line 41, in <module>
    PivotChart = PivotCache.CreatePivotChart(ChartDestination="Sheet1")
  File "C:\Python27\lib\site-packages\win32com\gen_py\00020813-0000-0000-C000-000000000046x0x1x8\PivotCache.py", line 36, in CreatePivotChart
    , XlChartType, Left, Top, Width, Height
pywintypes.com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)

can you answer my question here stackoverflow.com/questions/64284460/… — Abdelfattah Boutanes
– Abdelfattah Boutanes, Commented Oct 9, 2020 at 17:20

Trenton McKinney · Accepted Answer · 2020-06-22 07:57:47Z

I managed to make it work

import win32com.client
import os

Excel   = win32com.client.gencache.EnsureDispatch('Excel.Application') 
win32c  = win32com.client.constants
wb      = Excel.Workbooks.Add()
Sheet1  = wb.Worksheets.Add()
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.Add()
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)

#Properties from a recorded macro I made, I didn't take the time to determine which attribute is really important and I don't think I will.

PivotTable.ColumnGrand = True
PivotTable.HasAutoFormat = True
PivotTable.DisplayErrorString = False
PivotTable.DisplayNullString = True
PivotTable.EnableDrilldown = True
PivotTable.ErrorString = ""
PivotTable.MergeLabels = False
PivotTable.NullString = ""
PivotTable.PageFieldOrder = 2
PivotTable.PageFieldWrapCount = 0
PivotTable.PreserveFormatting = True
PivotTable.RowGrand = True
PivotTable.SaveData = True
PivotTable.PrintTitles = False
PivotTable.RepeatItemsOnEachPrintedPage = True
PivotTable.TotalsAnnotation = False
PivotTable.CompactRowIndent = 1
PivotTable.InGridDropZones = False
PivotTable.DisplayFieldCaptions = True
PivotTable.DisplayMemberPropertyTooltips = False
PivotTable.DisplayContextTooltips = True
PivotTable.ShowDrillIndicators = True
PivotTable.PrintDrillIndicators = False
PivotTable.AllowMultipleFilters = False
PivotTable.SortUsingCustomLists = True
PivotTable.FieldListSortAscending = False
PivotTable.ShowValuesRow = False
PivotTable.CalculatedMembersInFilters = False



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'

#Creating the PivotChart

chart=Sheet2.Shapes.AddChart2(201)
#to change XlChartType,cf.https://msdn.microsoft.com/fr-fr/vba/excelvba/articles/shapes-addchart2-method-excel
#For example, you can do chart=Sheet2.Shapes.AddChart2(201,4) for xlLine chart


Excel.Visible = 1

The result:

Here!

@Boussif Thanks for leaving a comment on my question to lead me to your answer. I implemented a slight variation where I add a chart object to the Workbook wb.Charts.Add(). Because the Pivot Table is an object in the Pivot Cache - which is an object in the Workbook, the Chart object is added as a Pivot Chart and uses the Pivot Table as its the data. I added the complete solution to my question: stackoverflow.com/questions/51953990/….
@Threadid You're welcome ! Your new solution is very clean and organized. I Hope it can be a future reference for others people (this subject is so bad-documented...).

Collectives™ on Stack Overflow

create a pivotchart with python win32com causes pywintypes.com_error

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related