471,624 Members | 1,832 Online
Bytes | Software Development & Data Engineering Community
Post +

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 471,624 software developers and data experts.

Creating Charts in Excel with pyExcelerator.ExcelMagic

Greetings,

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

Aug 15 '06 #1
3 13335

implicate_order wrote:
Greetings,

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".
Try this one:

<http://mathieu.fenniak.net/plotting-in-excel-through-pythoncom/>
>
Any help would be greatly appreciated.

TIA
Aug 15 '06 #2
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).Activa te #
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
Aug 16 '06 #3
Gentlemen,

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.

Aug 18 '06 #4

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by Anand | last post: by
6 posts views Thread by Duncan Smith | last post: by
3 posts views Thread by Hamilton, William | last post: by
20 posts views Thread by Marin Brkic | last post: by

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.