Python win32com PivotCache.CreatePivotChart()错误:“发生异常" [英] Python win32com PivotCache.CreatePivotChart() error: 'Exception occurred'

问题描述

2017年9月还有一个问题可以解决同样的问题，但没有答案:

此版本的gencache无法成功运行:

Excel = win32com.client.gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 8)

源数据 我使用ExcelWriter写入Excel的Pandas数据框中有100行18列

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

数据透视缓存 我使用PivotCaches().Create()而不是.Add()，因此我可以指定Version = win32c.xlPivotTableVersion15，它是Office 2013的正确版本.否则，它似乎默认为版本11.

pc = wb.PivotCaches().Create(SourceType=win32c.xlDatabase, SourceData=pvt_src, Version=win32c.xlPivotTableVersion15)

此更改移动了转盘，但未能解决我的问题-我仍然遇到错误，并且未创建图表:

• 应用此功能后，数据透视表的格式得到了增强 改变.
• 根本错误代码从-2147024809更改为 参数不正确到根错误代码-2146827284

它无法翻译为人类可读的消息:

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.')

搜索此错误代码2146827284，讨论似乎与excel对象正忙有关.但是Excel.Visible设置为0(也是默认值)，因此它以无头模式运行.

添加图纸，创建数据透视表，添加字段成功 这些都用实际代码中的try-except包装-为简洁起见，将其删除.

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")

我可以将工作表或图表添加为"ChartDestination"，但两个选项都不能改变结果.我可以验证对象是否已成功添加.

#chrt_sheet = wb.Charts.Add(After=pvt_sheet)
chrt_sheet = wb.Sheets.Add(After=pvt_sheet)
chrt_sheet.Name = 'Pivot Chart'

ChartDestination参数是唯一必需的参数，其他参数是可选的:XlChartType，Left，Top，Width，Height

文档此处:

基于示例，我将工作表或图表对象的名称作为字符串数据透视表"传递.那应该起作用.

pch = pc.CreatePivotChart(ChartDestination='Pivot Chart')

因为参数定义为Variant，所以我将字符串显式分配给Variant对象.我尝试了各种不同的变体类型，但结果却不同.

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'>

这是生成的错误:

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)

解决方案

感谢Boussif找到解决方法.

这是我的工作解决方案:

从数据透视表生成数据透视图

使用我们之前创建的Workbook对象，我们可以将图表插入"到工作簿中，并为图表指定名称-名称出现在选项卡中.

由于数据透视表是数据透视缓存中的一个对象-它是工作簿中的一个对象，因此将图表对象添加为数据透视图，并使用数据透视表作为其数据.

在此处阅读相关文档:

https://docs.microsoft.com /en-us/office/vba/api/excel.workbook.charts

https://docs.microsoft. com/en-us/office/vba/api/excel.chart(object)

chrt_sheet = wb.Charts.Add()
chrt_sheet.Name = 'Pivot Chart'

指定图表类型

图表类型对应于Excel程序中可用的相同图表类型.

从枚举类型列表中选择.

例如，一个3D柱形图的xl3DColumn值编码如下:

win32c.xl3DColumn

使用之前定义的win32c对象ew来引用所有常量值，包括枚举列表.

此处记录:

https://docs.microsoft.com /en-us/office/vba/api/excel.chart.charttype

https://docs.microsoft.com/en -us/office/vba/api/excel.xlcharttype

chrt_sheet.ChartType =  win32c.xl3DColumn

设置图表标题

在将HasTitle属性设置为True之前，工作表对象将没有ChartTitle属性.

chrt_sheet.HasTitle = True
chrt_sheet.ChartTitle.Text = "Chart Title"

设置配色方案

ChartColor值10-26对应于图表工具"功能区的设计"选项卡上的更改颜色"菜单.

chrt_sheet.ChartColor = 13

指定图表布局

布局是可选的

布局与Excel程序中可供选择的布局列表相同.

在这种情况下，它们不作为列举列表提供.因此，您必须提供布局编号.通常为1到10.根据相应的图表类型，布局会有所不同.要找出选择哪种布局，您将需要运行Excel程序并尝试每个布局.如果将鼠标悬停在布局"选项上，它将显示带有布局"名称的工具提示".例如:版式7".您将提供与各自的布局相关联的编号.

您使用ApplyLayout方法选择布局:

ApplyLayout(Layout, Chart Type)

此处记录:

https://docs.microsoft.com /en-us/office/vba/api/excel.chart.applylayout

chrt_sheet.ApplyLayout(7, win32c.xl3DColumn)

指定图表样式

样式是可选的

文档指出1到48是有效值.但是，正确的范围取决于所选的图表类型.

取决于图表类型201到352也有效.

要获取与图表类型"可用的样式选择匹配的数字，请执行以下操作:

1. 在开发人员"标签中-运行宏
2. 从图表设计"标签中-选择所有样式
3. 从开发者"标签-停止运行宏
4. 在开发人员"标签中-编辑宏

这将显示您的案例的正确值

对于3D柱形图类型，范围是286到297

此处的相关文档:

https://docs.microsoft.com /en-us/office/vba/api/excel.chart.chartstyle

xlChartStyle = 294
chrt_sheet.ClearToMatchStyle
chrt_sheet.ChartStyle = xlChartStyle

图表样式实验

如果您好奇并想看看每个人的样子，请运行此代码

提示:删除注释#"

#import time
#from time import sleep
​
#for xlChartStyle in range(286, 297):
#    try:
#        chrt_sheet.ClearToMatchStyle
#        chrt_sheet.ChartStyle = xlChartStyle
#        chrt_sheet.ChartTitle.Text = "Chart Style = "+str(xlChartStyle)
#        sleep(1)
#    except pythoncom.com_error as error:
#        print("Chart Style = %s" % str(xlChartStyle))

格式化轴标签

XlAxisType枚举指定了三个轴尺寸

• X轴= xlCategory
• Y轴= xlValue
• Z轴= xlSeriesAxis(仅适用于3D图表)

在此示例中，我们还将删除Z轴刻度标签

chrt_sheet.Axes(win32c.xlCategory).AxisTitle.Text = "X Axis Title"
chrt_sheet.Axes(win32c.xlSeries).TickLabelPosition = win32c.xlNone
chrt_sheet.Axes(win32c.xlSeries).HasTitle = True
chrt_sheet.Axes(win32c.xlSeries).AxisTitle.Text = "Z Axis Title"
chrt_sheet.Axes(win32c.xlValue).AxisTitle.Text = "Y Axis Title"

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)

解决方案

Thank you Boussif for finding the way on this.

Here is my working solution:

Generate a Pivot Chart from your Pivot Table

Using the Workbook object that we created earlier we can "insert" a Chart to our workbook and assign a name to our chart - name appears in the tab.

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.

Read the relevant documentation here:

https://docs.microsoft.com/en-us/office/vba/api/excel.workbook.charts

https://docs.microsoft.com/en-us/office/vba/api/excel.chart(object)

chrt_sheet = wb.Charts.Add()
chrt_sheet.Name = 'Pivot Chart'

Specify the Chart Type

The chart type corresponds to the same Chart Types that are available in the Excel program.

Choose from a list of enumerated types.

For example a 3D Column Chart has a value of xl3DColumn coded as follows:

win32c.xl3DColumn

Use the win32c object ew defined earlier to reference all constant values including enumerated lists.

Documented here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.charttype

https://docs.microsoft.com/en-us/office/vba/api/excel.xlcharttype

chrt_sheet.ChartType =  win32c.xl3DColumn

Set the Chart Title

The sheet object will not have a ChartTitle property until to set HasTitle Property to True

chrt_sheet.HasTitle = True
chrt_sheet.ChartTitle.Text = "Chart Title"

Set the Color Scheme

ChartColor values 10 - 26 correspond to the Change Colors menu on the DESIGN tab of the CHART TOOLS ribbon.

chrt_sheet.ChartColor = 13

Specify a Chart Layout

Layout is optional

Layouts are the same as the list of Layouts to choose from in the Excel program.

In this case they are not provided as an enumerated list. So you have to provide a number for the layout. It will generally be from 1 to 10. Layouts are different depending on the respective Chart Type. To find out which layout to choose you will need to run the Excel program and try each one. If you hover your mouse over the Layout option it will display a "Tool Tip" with the name of the Layout. For example: 'Layout 7'. You would provide the number associated with your respective layout.

you use the ApplyLayout method to choose the Layout:

ApplyLayout(Layout, Chart Type)

Documented here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.applylayout

chrt_sheet.ApplyLayout(7, win32c.xl3DColumn)

Specify a Chart Style

Style is optional

Documentation states that 1 to 48 are valid values. However the correct range depends on which Chart Type is chosen.

Depending on the Chart Type 201 to 352 is also valid.

To get the numbers numbers that match the style selections that are available for your Chart Type:

1. From the Developer Tab - Run a macro
2. From the Chart Design Tab - Select all the styles
3. From the Devloper Tab - Stop running the macro
4. From the Developer Tab - Edit the macro

This will reveal the correct values for your case

For the 3D Column Chart Type, the range is 286 to 297

Relevant documentation here:

https://docs.microsoft.com/en-us/office/vba/api/excel.chart.chartstyle

xlChartStyle = 294
chrt_sheet.ClearToMatchStyle
chrt_sheet.ChartStyle = xlChartStyle

Chart Style Experiment

If you are curious and want to see what each one looks like then run this code

hint: Remove the comments '#'

#import time
#from time import sleep
​
#for xlChartStyle in range(286, 297):
#    try:
#        chrt_sheet.ClearToMatchStyle
#        chrt_sheet.ChartStyle = xlChartStyle
#        chrt_sheet.ChartTitle.Text = "Chart Style = "+str(xlChartStyle)
#        sleep(1)
#    except pythoncom.com_error as error:
#        print("Chart Style = %s" % str(xlChartStyle))

​ Format Axis Labels

There are three Axis dimensions specified by the XlAxisType Enumeration

• X axis = xlCategory
• Y Axis = xlValue
• Z Axis = xlSeriesAxis (3D Charts only)

In this example we are also removing the Z Axis Tick Labels

chrt_sheet.Axes(win32c.xlCategory).AxisTitle.Text = "X Axis Title"
chrt_sheet.Axes(win32c.xlSeries).TickLabelPosition = win32c.xlNone
chrt_sheet.Axes(win32c.xlSeries).HasTitle = True
chrt_sheet.Axes(win32c.xlSeries).AxisTitle.Text = "Z Axis Title"
chrt_sheet.Axes(win32c.xlValue).AxisTitle.Text = "Y Axis Title"

这篇关于Python win32com PivotCache.CreatePivotChart()错误:“发生异常"的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！