AttributeError: xlColumnClustered

Question

My Python code to generate charts from pywin32 in Excel is:

def add_chart1(location, sheet, range1, title, left, right, height, width, charttype):
    pythoncom.CoInitialize()
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    xl.Visible = True
    ss = xl.Workbooks.Open("demo.xlsx")
    worksheet = ss.Worksheets.Item(sheet)
    workchart = worksheet.ChartObjects().Add(left, right, height, width).Activate()
    xl.ActiveChart.ChartType = charttype
    xl.ActiveChart.Axes(c.xlValue).HasMajorGridlines = False
    xl.ActiveChart.Axes(c.xlCategory).HasTitle = True 
    xl.ActiveChart.SetSourceData(Source=worksheet.Range(range1))
    #workchart.ChartType = 57 # 3DPie
    xl.ActiveChart.HasTitle = True
    workchart_title = xl.ActiveChart.ChartTitle
    workchart_title.Text = title
    
    xl.ActiveChart.HasLegend = True
    xl.ActiveChart.Legend.LegendEntries(1)
    xl.ActiveChart.Legend.Position = -4107 # Right Position
    
    ss.SaveAs("demo.xlsx")
    ss.Close(False); workbook=None
    xl.Application.Quit()

I'm executing this in a flask application. The function call is:

import win32com.client as win32
from win32com.client import constants as c
import pythoncom
from flask import *

app = Flask(__name__)

@app.route('/', methods = ['POST'])
def index():
    add_chart1('demo.xlsx', 'Chart',range1, 'Visualization', 350, 32, 600, 300, c.xlColumnClustered)

The output is:

File "C:\Anaconda3\Lib\site-packages\win32com\client\__init__.py", line 178, in __getattr__
raise AttributeError(a)
AttributeError: xlColumnClustered

As seen in other questions of SO, I cleared the cache in temp\1\gen_py directory but still the error persists.

I tried every solution of this question .

But none of them worked.

Answer 1

Here's a revised version of your code:

import win32com.client as win32
import pythoncom
from flask import Flask

app = Flask(__name__)

def add_chart1(location, sheet, range1, title, left, right, height, width, charttype):
    pythoncom.CoInitialize()
    xl = win32.gencache.EnsureDispatch('Excel.Application')
    xl.Visible = True
    ss = xl.Workbooks.Open(location)
    worksheet = ss.Worksheets.Item(sheet)
    workchart = worksheet.ChartObjects().Add(left, right, height, width).Activate()
    xl.ActiveChart.ChartType = charttype
    xl.ActiveChart.Axes(2).HasMajorGridlines = False  # c.xlValue = 2
    xl.ActiveChart.Axes(1).HasTitle = True  # c.xlCategory = 1
    xl.ActiveChart.SetSourceData(Source=worksheet.Range(range1))
    xl.ActiveChart.HasTitle = True
    workchart_title = xl.ActiveChart.ChartTitle
    workchart_title.Text = title
    
    xl.ActiveChart.HasLegend = True
    xl.ActiveChart.Legend.LegendEntries(1)
    xl.ActiveChart.Legend.Position = -4107  # Right Position
    
    ss.SaveAs("demo.xlsx")
    ss.Close(False)
    xl.Application.Quit()

@app.route('/', methods=['POST'])
def index():
    range1 = "A1:B5"  # Example range, replace with actual range
    add_chart1('demo.xlsx', 'Sheet1', range1, 'Visualization', 350, 32, 600, 300, 51)  # 51 is xlColumnClustered
    return "Chart added successfully"

if __name__ == '__main__':
    app.run(debug=True)

Explanation

1. Replace c.xlColumnClustered with 51: The 51 is the actual value for the xlColumnClustered constant. This avoids the need for win32com.client.constants.
2. Add a Sample Range: Ensure you pass a valid Excel range (e.g., "A1:B5") to the function.
3. Correct Worksheet Reference: Ensure the worksheet name in the ss.Worksheets.Item(sheet) call matches the actual sheet name in your Excel file.
4. Ensure Flask Route Works: The route should handle the POST request and call the add_chart1 function.