Creating Charts in Excel with pyExcelerator.ExcelMagic
implicate_order
I'm new to python and am in the process of writing a script to parse
some CSV data, spread it across multiple Excel worksheets and then
generate charts. I searched the internet to find some place where I
could look up a HOWTO doc/recipe to do that using either pyExcelerator
or win32com.client.
Could someone point me in the right direction?
I'm at the stage where the spreadsheet and associated data worksheets
are ready. The chart is created (with win32com.client). I need to know
how I can use win32com.client to actually generate some data based on
the contents of a particular work sheet.
>>> from win32com.client import *
>>> xl = win32com.client.Dispatch("Excel.Application")
>>> wb = xl.Workbooks.open("C:\scripts\dummytest.xls")
>>> xl.Visible = 1
>>> ws = wb.Worksheets(1)
>>> ws.Range('$A1:$D1').Value = ['NAME', 'PLACE', 'RANK', 'PRICE']
>>> ws.Range('$A2:$D2').Value = ['Foo', 'Fooland', 1, 100]
>>> ws.Range('$A3:$D3').Value = ['Bar', 'Barland', 2, 75]
>>> ws.Range('$A4:$D4').Value = ['Stuff', 'Stuffland', 3, 50]
>>> wb.Save()
>>> wb.Charts.Add()
>>> wc1 = wb.Charts(1)
At this point, I'm lost -- I couldn't find any lucid docs to indicate
what can be done to populate the chart from the worksheet "ws".
Any help would be greatly appreciated.
TIA
mensa...@aol.com
Chris
> Greetings,
>
Here's an Excel class I use. I'm afraid I can't recall where I found the
basic class. I have a vague recollection it is due to Mark Hammond,
author of the win32com package. Might have been in win32com demos.
(Whoever the original author is anyway, many thanks). I added a few
methods, including XY plotting (you can probably tell by the change in
coding style to that of a newb). Not very generic but you may find it
useful, as the hardest part I found was discovering what the Excel
specific methods etc where. The MSDN developer site for Excel is a big
help. http://msdn.microsoft.com/developercenters/
import win32com.client
from win32com.client import Dispatch, constants
class ExcelWorkbook:
""" An Excel workbook object"""
def __init__(self, filename=None):
# Use these commands in Python code to auto generate .py
support for excel
from win32com.client import gencache
gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}',
0, 1, 4)
# start excel
self.xlApp = Dispatch('Excel.Application')
if filename and os.path.exists(filename):
self.xlBook = self.xlApp.Workbooks.Open(filename)
else:
self.xlBook = self.xlApp.Workbooks.Add()
self.filename = filename
def save(self, newfilename=None):
if newfilename:
self.filename = newfilename
self.xlBook.SaveAs(newfilename)
else:
self.xlBook.Save()
def close(self):
self.xlBook.Close(SaveChanges=0)
del self.xlApp
def show(self):
self.xlApp.Visible = 1
def hide(self):
self.xlApp.Visible = 0
def newSheet(self, sheet):
try: # fails if sheet already exists
self.xlBook.Sheets(sheet).Name == sheet
except:
self.xlSheet = self.xlBook.Worksheets.Add()
self.xlSheet.Name = sheet
def deleteSheet(self, sheet):
try: # ignore if sheet doesn't exist
self.xlBook.Sheets(sheet).Delete()
except:
pass
def selectSheet(self, sheet):
self.xlBook.Worksheets(sheet).Select()
def getCell(self, sheet, row, col):
"Get value of one cell"
sht = self.xlBook.Worksheets(sheet)
return sht.Cells(row, col).Value
def setCell(self, sheet, row, col, value):
"set value of one cell"
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).Value = value
def getRange(self, sheet, row1, col1, row2, col2):
"return a 2d array (i.e. tuple of tuples)"
sht = self.xlBook.Worksheets(sheet)
return sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).Value
def setRange(self, sheet, topRow, leftCol, data):
"""insert a 2d array starting at given location.
Works out the size needed for itself"""
bottomRow = topRow + len(data) - 1
rightCol = leftCol + len(data[0]) - 1
sht = self.xlBook.Worksheets(sheet)
sht.Range(
sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol)
).Value = data
def getContiguousRange(self, sheet, row, col):
"""Tracks down and across from top left cell until it
encounters blank cells; returns the non-blank range.
Looks at first row and column; blanks at bottom or right
are OK and return None witin the array"""
sht = self.xlBook.Worksheets(sheet)
# find the bottom row
bottom = row
while sht.Cells(bottom + 1, col).Value not in [None, '']:
bottom = bottom + 1
# right column
right = col
while sht.Cells(row, right + 1).Value not in [None, '']:
right = right + 1
return sht.Range(sht.Cells(row, col), sht.Cells(bottom,
right)).Value
def fixStringsAndDates(self, aMatrix):
# converts all unicode strings and times
newmatrix = []
for row in aMatrix:
newrow = []
for cell in row:
if type(cell) is UnicodeType:
newrow.append(str(cell))
elif type(cell) is TimeType:
newrow.append(int(cell))
else:
newrow.append(cell)
newmatrix.append(tuple(newrow))
return newmatrix
def convertRCToA1(self, R1C1):
"""
fromReferenceStyle = constants.xlR1C1,
toReferenceStyle = constants.xlA1,
toabsolute = constants.xlRelative)
"""
return self.xlApp.ConvertFormula(R1C1, constants.xlR1C1,
constants.xlA1,
constants.xlRelative)
def insertFormulaInRange(self, sheet, row, col, len, formula):
self.selectSheet(sheet)
sht = self.xlBook.Worksheets(sheet)
sht.Cells(row, col).FormulaR1C1 = formula
fill_range = sht.Range(sht.Cells(row, col),
sht.Cells(row+len-1, col))
start = self.convertRCToA1("R"+str(row)+"C"+str(col))
sht.Range(start).AutoFill(Destination=fill_range)
def newChartInSheet(self, sheet, num = 1, left = 10, width = 600,
top = 50, height = 450, type = 'xy'):
if type == 'xy':
chart_type = constants.xlXYScatter
try:
self.selectSheet(sheet)
except: # sheet doesn't exist so create it
self.newSheet(sheet)
try :
self.xlBook.Sheets(sheet).ChartObjects(num).Activate #
already exists
except:
self.xlChart = self.xlBook.Sheets(sheet).ChartObjects().Add(
Left = left, Width = width, Top = top,
Height = height)
self.xlChart.Chart.ChartType = chart_type
def addXYChartSeries(self, sheet, topRow, bottomRow, xCol, yCol,
series_name="", chart_sheet="", chart_num = 1,
color = 1, style = 'line',
title = "", xlabel = "", ylabel = "", errorbars
= {}):
if not chart_sheet:
chart_sheet = sheet
# series properties
sht = self.xlBook.Worksheets(sheet)
se = self.xlChart.Chart.SeriesCollection().NewSeries()
se.Values = sht.Range(sht.Cells(topRow, yCol),
sht.Cells(bottomRow, yCol))
se.XValues = sht.Range(sht.Cells(topRow, xCol),
sht.Cells(bottomRow, xCol))
if series_name:
se.Name = series_name
if style == 'line':
# line style
se.MarkerStyle = constants.xlNone
se.Border.ColorIndex = color
se.Border.Weight = constants.xlHairline
se.Border.LineStyle = constants.xlContinuous
se.Border.Weight = constants.xlMedium
if style == 'point':
# point style
#se.MarkerBackgroundColorIndex = constants.xlNone
#se.MarkerForegroundColorIndex = color
se.MarkerBackgroundColorIndex = color
se.MarkerForegroundColorIndex = 1 # black
#se.MarkerStyle = constants.xlMarkerStyleCircle
se.MarkerStyle = constants.xlMarkerStyleSquare
se.MarkerSize = 5
# Chart properties
cht = self.xlBook.Sheets(chart_sheet).ChartObjects(chart_num).Chart
# Chart Title
if title:
cht.HasTitle = True
cht.ChartTitle.Caption = title
cht.ChartTitle.Font.Name = 'Arial'
cht.ChartTitle.Font.Size = 10
cht.ChartTitle.Font.Bold = False
# X axis labels
if xlabel:
cht.Axes(constants.xlCategory).HasTitle = True
cht.Axes(constants.xlCategory).AxisTitle.Caption = xlabel
cht.Axes(constants.xlCategory).AxisTitle.Font.Name = 'Arial'
cht.Axes(constants.xlCategory).AxisTitle.Font.Size = 10
cht.Axes(constants.xlCategory).AxisTitle.Font.Bold = False
cht.Axes(constants.xlCategory).MinimumScale = 0
cht.Axes(constants.xlCategory).MaximumScaleIsAuto = True
# Y axis labels
if ylabel:
cht.Axes(constants.xlValue).HasTitle = True
cht.Axes(constants.xlValue).AxisTitle.Caption = ylabel
cht.Axes(constants.xlValue).AxisTitle.Font.Name = 'Arial'
cht.Axes(constants.xlValue).AxisTitle.Font.Size = 10
cht.Axes(constants.xlValue).AxisTitle.Font.Bold = False
cht.Axes(constants.xlValue).MinimumScale = 0
cht.Axes(constants.xlValue).MaximumScaleIsAuto = True
if errorbars:
amount = "".join(["=", chart_sheet, "!",
"R",
str(errorbars['amount'][0]),
"C",
str(errorbars['amount'][2]),
":",
"R",
str(errorbars['amount'][1]),
"C",
str(errorbars['amount'][2])])
se.ErrorBar(Direction = constants.xlY,
Include = constants.xlErrorBarIncludeBoth,
Type = constants.xlErrorBarTypeCustom,
Amount = amount, MinusValues = amount)
se.ErrorBars.EndStyle = constants.xlNoCap
se.ErrorBars.Border.LineStyle = constants.xlContinuous
se.ErrorBars.Border.ColorIndex = color
se.ErrorBars.Border.Weight = constants.xlHairline
implicate_order
Thanks for your responses. I also found some additional threads on this
newsgroup that gave me insight into how to use the MS Excel com objects
(or whatever they are called)...
So I used this:
xl = win32com.client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open(outfile01)
prodws = wb.Worksheets(1)
wc_prod = wb.Charts.Add()
wc_prod.ChartWizard(Source=prodws.Range("b1", "g30"), Gallery=11,
Format=5, CategoryLabels=3, SeriesLabels=3, PlotBy=None, Title="Prod" )
Does a pretty decent job of creating charts (we can change the chart
type by changing the Gallery and Format values)
So I use pyExcelerator to generate the workbook with various worksheets
and then use win32com.client to generate the charts.