Excel 2007 Charts with PyWin32

[Ally]

Hi all,

I’m looking to plot charts in Excel from python. After some Googling
I’ve found the following code:

def plot(x, y, xAxisLog=False, yAxisLog=False):
# acquire application object, which may start application
application = Dispatch("Excel.Application")

# create new file ('Workbook' in Excel-vocabulary)
workbook = application.Workbooks.Add()

# store default worksheet object so we can delete it later
defaultWorksheet = workbook.Worksheets(1)

# build new chart (on seperate page in workbook)
chart = workbook.Charts.Add()

print "chart", chart
chart.ChartType = constants.xlXYScatter
chart.Name = "Plot"

# create data worksheet
worksheet = workbook.Worksheets.Add()
worksheet.Name = "Plot data"

# install data
xColumn = addDataColumn(worksheet, 0, x)
yColumn = addDataColumn(worksheet, 1, y)

# create series for chart
series = chart.SeriesCollection().NewSeries()
series.XValues = xColumn
series.Values = yColumn
series.Name = "Data"
series.MarkerSize = 3

# setup axises
xAxis = chart.Axes()[0]
yAxis = chart.Axes()[1]
xAxis.HasMajorGridlines = True
yAxis.HasMajorGridlines = True
if xAxisLog:
xAxis.ScaleType = constants.xlLogarithmic
if yAxisLog:
yAxis.ScaleType = constants.xlLogarithmic

# remove default worksheet
defaultWorksheet.Delete()

# make stuff visible now.
chart.Activate()
application.Visible = True

def genExcelName(row, col):
"""Translate (0,0) into "A1"."""
if col < 26:
colName = chr(col + ord('A'))
else:
colName = chr((col / 26)-1 + ord('A')) + \
chr((col % 26) + ord('A'))
return "%s%s" % (colName, row + 1)

def addDataColumn(worksheet, columnIdx, data):
range = worksheet.Range("%s:%s" % (
genExcelName(0, columnIdx),
genExcelName(len(data) - 1, columnIdx),
))
for idx, cell in enumerate(range):
cell.Value = data[idx]
return range

# A simple example:
plot( (1,2,3,4,5), (6,7,8,9,10) )

I’m continually getting errors with:

chart.ChartType = constants.xlXYScatter

with

AttributeError: xlXYScatter

If I try other chart types, such as xl3DPieExploded, I still receive
the same error. Has anyone got any suggestions as to why it can’t seem
to find any chart types? I’m running WinXP, Python 2.5, latest PyWin32
and Excel 2007.

I don’t know who the original author of the above code is, it
defiantly wasn’t me, so major thanks to whoever it was.

 

[Ally]

Solved. See http://bytes.com/forum/thread482449.html

application = Dispatch("Excel.Application")

should be

application =
win32com.client.gencache.EnsureDispatch('Excel.Application')