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.Wor kbooks.Add()
# store default worksheet object so we can delete it later
defaultWorkshee t = workbook.Worksh eets(1)
# build new chart (on seperate page in workbook)
chart = workbook.Charts .Add()
print "chart", chart
chart.ChartType = constants.xlXYS catter
chart.Name = "Plot"
# create data worksheet
worksheet = workbook.Worksh eets.Add()
worksheet.Name = "Plot data"
# install data
xColumn = addDataColumn(w orksheet, 0, x)
yColumn = addDataColumn(w orksheet, 1, y)
# create series for chart
series = chart.SeriesCol lection().NewSe ries()
series.XValues = xColumn
series.Values = yColumn
series.Name = "Data"
series.MarkerSi ze = 3
# setup axises
xAxis = chart.Axes()[0]
yAxis = chart.Axes()[1]
xAxis.HasMajorG ridlines = True
yAxis.HasMajorG ridlines = True
if xAxisLog:
xAxis.ScaleType = constants.xlLog arithmic
if yAxisLog:
yAxis.ScaleType = constants.xlLog arithmic
# remove default worksheet
defaultWorkshee t.Delete()
# make stuff visible now.
chart.Activate( )
application.Vis ible = True
def genExcelName(ro w, col):
"""Translat e (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(w orksheet, columnIdx, data):
range = worksheet.Range ("%s:%s" % (
genExcelName(0, columnIdx),
genExcelName(le n(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.xlXYS catter
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.
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.Wor kbooks.Add()
# store default worksheet object so we can delete it later
defaultWorkshee t = workbook.Worksh eets(1)
# build new chart (on seperate page in workbook)
chart = workbook.Charts .Add()
print "chart", chart
chart.ChartType = constants.xlXYS catter
chart.Name = "Plot"
# create data worksheet
worksheet = workbook.Worksh eets.Add()
worksheet.Name = "Plot data"
# install data
xColumn = addDataColumn(w orksheet, 0, x)
yColumn = addDataColumn(w orksheet, 1, y)
# create series for chart
series = chart.SeriesCol lection().NewSe ries()
series.XValues = xColumn
series.Values = yColumn
series.Name = "Data"
series.MarkerSi ze = 3
# setup axises
xAxis = chart.Axes()[0]
yAxis = chart.Axes()[1]
xAxis.HasMajorG ridlines = True
yAxis.HasMajorG ridlines = True
if xAxisLog:
xAxis.ScaleType = constants.xlLog arithmic
if yAxisLog:
yAxis.ScaleType = constants.xlLog arithmic
# remove default worksheet
defaultWorkshee t.Delete()
# make stuff visible now.
chart.Activate( )
application.Vis ible = True
def genExcelName(ro w, col):
"""Translat e (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(w orksheet, columnIdx, data):
range = worksheet.Range ("%s:%s" % (
genExcelName(0, columnIdx),
genExcelName(le n(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.xlXYS catter
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.
Comment