CODEWITHSUNDEEP
pythonexcel
konrad
konrad

Reputation: 3706

add chartobject to excel via Python

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?

Upvotes: 0

Views: 591

Answers (2)

Byron Wall
Byron Wall

Reputation: 4010

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

ChartObjects().Add(...)

Upvotes: 1

sancho.s ReinstateMonicaCellio
sancho.s ReinstateMonicaCellio

Reputation: 15551

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:

  1. 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.
  2. 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.

Upvotes: 0

Related Questions