There is one other question from Sep 2017 that addresses this same problem but does not have an answer: create a pivotchart with python win32com
I have tried several approaches to get this working so I want to explain these and hopefully get some insight from someone on how to get this working. This does not appear to be a well worn path so I do not have high hopes.
Environment details:
- Windows 10
- Office 2013
- Anaconda 3.6
I use
win32com.client.gencache.EnsureDispatch('Excel.Application')
but I can also use
win32com.client.DispatchEx('Excel.Application')
or
win32com.client.dynamic.Dispatch('Excel.Application')
Each one returns this CLSID win32com.gen_py.00020813-0000-0000-C000-000000000046x0x1x8
Each one also has the same error.
I also ran this command as per documentation here:
C:\Users\home_dir>python AppData\Local\Continuum\anaconda3\pkgs\pywin32-223-py36hfa6e2cd_1\Lib\site-packages\win32com\client\makepy.py -i "Microsoft Excel 15.0 Object Library"
Output generated from makepy.py:
Microsoft Excel 15.0 Object Library {00020813-0000-0000-C000-000000000046}, lcid=0, major=1, minor=8
>>> # Use these commands in Python code to auto generate .py support
>>> from win32com.client import gencache
>>> gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 8)
This version of gencache did not work successfully:
Excel = win32com.client.gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 8)
Source Data There are 100 rows and 18 columns in a Pandas Dataframe that I write to Excel using ExcelWriter
ew = pd.ExcelWriter('c:\devworkspace\SQL-Pandas-Excel\SampleData.xlsx')
sample_data_df.to_excel(ew, sheet_name='Source Data')
ew.save()
ew.close()
Excel = win32com.client.gencache.EnsureDispatch('Excel.Application')
win32c = win32com.client.constants
wb = Excel.Workbooks.Open('c:\devworkspace\SQL-Pandas-Excel\SampleData.xlsx')
src_sheet = wb.Worksheets('Source Data')
rng_row = 100
rng_col = 18
rng_beg = src_sheet.Cells(1,2)
rng_end = src_sheet.Cells(rng_row,rng_col)
pvt_src_rng = src_sheet.Range(rng_beg, rng_end)
pvt_src_rng.Select()
pvt_src = "%s!R1C2:R%dC%d"%(src_sheet.Name,rng_row+1,rng_col+1) #add 1 for header and df index
Pivot Cache I use PivotCaches().Create() as opposed to .Add() so I can specify Version=win32c.xlPivotTableVersion15 which is the correct version for office 2013. Otherwise it appeared to default to version 11.
pc = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=pvt_src, Version=win32c.xlPivotTableVersion15)
This change moved the dial but did not solve my problem - I am still getting an error and the chart is not getting created:
- The formatting on the pivot table is enhanced when I applied this change.
- The root error code changed from -2147024809 which is "The parameter is incorrect" To root error code -2146827284
Which it cannot translate to human readable message:
print(win32api.FormatMessage(error.excepinfo[5]))
error: (317, 'FormatMessageW', 'The system cannot find message text for message number 0x%1 in the message file for %2.')
Searching on this error code 2146827284 the discussions appear to be related to the excel object being busy. But Excel.Visible is set to 0 - also the default - so it is running in headless mode.
Adding Sheets, Creating the Pivot Table, Adding Fields is successful These are all wrapped in try-except in the actual code - removed for brevity.
pvt_sheet = wb.Sheets.Add(After=src_sheet)
pvt_sheet.Name = 'Pivot Sheet'
pvt_rng_beg = pvt_sheet.Cells(2,2)
pvt_rng_end = pvt_sheet.Cells(2,2)
pvt_dest_rng = pvt_sheet.Range(pvt_rng_beg, pvt_rng_end)
pt = pc.CreatePivotTable(TableDestination=pvt_dest_rng,TableName='PivotTable1')
pt.AddFields(RowFields="claimant_type_desc" , ColumnFields="claim_cause_desc" )
pt.AddDataField(Field=pt.PivotFields("total_direct_payment"), Caption="Total Incurred")
I can add a sheet or a chart as the "ChartDestination" but neither option alters the outcome. I can validate that the object is getting added successfully.
#chrt_sheet = wb.Charts.Add(After=pvt_sheet)
chrt_sheet = wb.Sheets.Add(After=pvt_sheet)
chrt_sheet.Name = 'Pivot Chart'
ChartDestination argument is the only required argument the other arguments are optional: XlChartType, Left, Top, Width, Height
Docs here:
Based on examples I pass the name of the sheet or chart object as a string 'Pivot Sheet'. That should work.
pch = pc.CreatePivotChart(ChartDestination='Pivot Chart')
Because the parameter is defined as a Variant then I explicitly assign the string to a Variant object. I tried a range of different variant types but that did not yield a different outcome.
chrt_sheet_name_variant = win32com.client.VARIANT(pythoncom.VT_BYREF | pythoncom.VT_BSTR, chrt_sheet.Name)
print(chrt_sheet_name_variant.value)
print(chrt_sheet_name_variant.varianttype)
print(chrt_sheet_name_variant.__class__)
print(type(chrt_sheet_name_variant))
pch = pc.CreatePivotChart(ChartDestination=chrt_sheet_name_variant)
#Output:
#Pivot Chart
#16392
#<class 'win32com.client.VARIANT'>
#<class 'win32com.client.VARIANT'>
This is the error that is generated:
File "C:\Users\xxxxxx\AppData\Local\Temp\gen_py\3.6\00020813-0000-0000-C000-000000000046x0x1x8\PivotCache.py", line 36, in CreatePivotChart
, XlChartType, Left, Top, Width, Height
com_error: (-2147352567, 'Exception occurred.', (0, None, None, None, 0, -2146827284), None)
