add chartobject to excel via Python

Question

so I have been trying to add a chart object to an Excel file using IronPython and I keep getting an error whenever I call ws.ChartObjects. For some reason it tells me that its a DispCallable and that it has no Add property.

clr.AddReferenceByName('Microsoft.Office.Interop.Excel, Version=11.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c')
from Microsoft.Office.Interop import Excel

System.Threading.Thread.CurrentThread.CurrentCulture = System.Globalization.CultureInfo("en-US")
from System.Runtime.InteropServices import Marshal


def SetUp(xlApp):
    # supress updates and warning pop ups
    xlApp.Visible = False
    xlApp.DisplayAlerts = False
    xlApp.ScreenUpdating = False
    return xlApp

def ExitExcel(filePath, xlApp, wb, ws):
    # clean up before exiting excel, if any COM object remains
    # unreleased then excel crashes on open following time
    def CleanUp(_list):
        if isinstance(_list, list):
            for i in _list:
                Marshal.ReleaseComObject(i)
        else:
            Marshal.ReleaseComObject(_list)
        return None

    wb.SaveAs(str(filePath))
    xlApp.ActiveWorkbook.Close(False)
    xlApp.ScreenUpdating = True
    CleanUp([ws,wb,xlApp])
    return None

def GetWidthHeight(origin, extent, ws):
    left = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]).Left
    top = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0]).Top
    width = ws.Range[origin, extent].Width
    height = ws.Range[origin, extent].Height
    return [left, top, width, height]

if runMe:
    message = None
    try:
        xlApp = SetUp(Excel.ApplicationClass())
        errorReport = None
        xlApp.Workbooks.open(str(filePath))
        wb = xlApp.ActiveWorkbook
        ws = xlApp.Sheets(sheetName)
        # i have no clue why ws.ChartObjects.Count throws an error all the time
        origin = ws.Cells(bb.xlRange(cellRange)[1], bb.xlRange(cellRange)[0])
        extent = ws.Cells(bb.xlRange(cellRange)[3], bb.xlRange(cellRange)[2])
        left = GetWidthHeight(origin, extent, ws)[0]
        top = GetWidthHeight(origin, extent, ws)[1]
        width = GetWidthHeight(origin, extent, ws)[2]
        height = GetWidthHeight(origin, extent, ws)[3]
        xlChartObject = ws.ChartObjects.Add(int(left), int(top), int(width), int(height))
        Marshal.ReleaseComObject(extent)
        Marshal.ReleaseComObject(origin)
        ExitExcel(filePath, xlApp, wb, ws)
    except:
        # if error accurs anywhere in the process catch it
        import traceback
        errorReport = traceback.format_exc()

My problem is with calling ws.ChartObjects.Add() which throws an exception 'DispCallable' object has no attribute 'Add'. How do i go around this? What is wrong?

Is this related? http://stackoverflow.com/questions/14621224/cant-get-excel-chartobjects-count. Does it need to be `ChartObjects().Add()`? If nothing else, it might resolve the issue mentioned in that comment. :) — Byron Wall, Jul 10 '15 at 19:53
@ByronWall this was it! Thanks! ChartObjects().Add() freaking differences between VBA and Python. — konrad, Jul 10 '15 at 20:03

score 1 · Accepted Answer · edited May 23 '17 at 12:14

1

Based on a similar issue indicating that ChartObjects is a function you should use

ChartObjects().Add(...)

edited May 23 '17 at 12:14

Community

1
1

answered Jul 10 '15 at 20:05

Byron Wall

3,970
2
13
29

score 0 · Answer 2 · edited May 23 '17 at 12:06

0

As per official documentation, the arguments should be double. If this is not the issue, you can split

xlChartObject = ws.ChartObjects.Add(...

into

xlChartObjects = ws.ChartObjects
xlChartObject = xlChartObjects.Add(...

to start debugging. It is a good idea to:

Check available methods (for the class of xlChartObjects, e.g.) with How do I get list of methods in a Python class?, or Finding what methods an object has.
Check type with What's the canonical way to check for type in python?.

You will likely learn how to fix these lines.

PS: In the code you posted sheetName and bb are not defined, although you probably define them earlier.

edited May 23 '17 at 12:06

Community

1
1

answered Jul 10 '15 at 19:51

sancho.s ReinstateMonicaCellio

14,708
20
93
185

yes, sheet name is an input string so that's not an issue. bb is a name of a module that i defined and that was never a problem before. argument types should not be an issue either but i can cast them to test. Thanks for the tip. – konrad Jul 10 '15 at 19:52

add chartobject to excel via Python

2 Answers2