Creating Charts in Excel with pyExcelerator.ExcelMagic
Chris
cfriedl at bigpond.net.au
Wed Aug 16 05:09:07 EDT 2006
implicate_order wrote:
> 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
More information about the Python-list
mailing list