Creating Charts in Excel with pyExcelerator.ExcelMagic

[implicate_order]

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("Exce l.Application")
>>wb = xl.Workbooks.op en("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

[mensanator@aol.com]

Re: Creating Charts in Excel with pyExcelerator.E xcelMagic


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("Exce l.Application")
>wb = xl.Workbooks.op en("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

[Chris]

Re: Creating Charts in Excel with pyExcelerator.E xcelMagic

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.Ensure Module('{000208 13-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.Work books.Open(file name)
else:
self.xlBook = self.xlApp.Work books.Add()
self.filename = filename

def save(self, newfilename=Non e):
if newfilename:
self.filename = newfilename
self.xlBook.Sav eAs(newfilename )
else:
self.xlBook.Sav e()

def close(self):
self.xlBook.Clo se(SaveChanges= 0)
del self.xlApp

def show(self):
self.xlApp.Visi ble = 1

def hide(self):
self.xlApp.Visi ble = 0
def newSheet(self, sheet):
try: # fails if sheet already exists
self.xlBook.She ets(sheet).Name == sheet
except:
self.xlSheet = self.xlBook.Wor ksheets.Add()
self.xlSheet.Na me = sheet

def deleteSheet(sel f, sheet):
try: # ignore if sheet doesn't exist
self.xlBook.She ets(sheet).Dele te()
except:
pass

def selectSheet(sel f, sheet):
self.xlBook.Wor ksheets(sheet). Select()

def getCell(self, sheet, row, col):
"Get value of one cell"
sht = self.xlBook.Wor ksheets(sheet)
return sht.Cells(row, col).Value

def setCell(self, sheet, row, col, value):
"set value of one cell"
sht = self.xlBook.Wor ksheets(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.Wor ksheets(sheet)
return sht.Range(sht.C ells(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.Wor ksheets(sheet)
sht.Range(
sht.Cells(topRo w, leftCol),
sht.Cells(botto mRow, rightCol)
).Value = data

def getContiguousRa nge(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.Wor ksheets(sheet)

# find the bottom row
bottom = row
while sht.Cells(botto m + 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.C ells(row, col), sht.Cells(botto m,
right)).Value

def fixStringsAndDa tes(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(s tr(cell))
elif type(cell) is TimeType:
newrow.append(i nt(cell))
else:
newrow.append(c ell)
newmatrix.appen d(tuple(newrow) )
return newmatrix

def convertRCToA1(s elf, R1C1):
"""
fromReferenceSt yle = constants.xlR1C 1,
toReferenceStyl e = constants.xlA1,
toabsolute = constants.xlRel ative)
"""
return self.xlApp.Conv ertFormula(R1C1 , constants.xlR1C 1,
constants.xlA1,
constants.xlRel ative)

def insertFormulaIn Range(self, sheet, row, col, len, formula):
self.selectShee t(sheet)
sht = self.xlBook.Wor ksheets(sheet)
sht.Cells(row, col).FormulaR1C 1 = formula
fill_range = sht.Range(sht.C ells(row, col),
sht.Cells(row+l en-1, col))
start = self.convertRCT oA1("R"+str(row )+"C"+str(col ))
sht.Range(start ).AutoFill(Dest ination=fill_ra nge)

def newChartInSheet (self, sheet, num = 1, left = 10, width = 600,
top = 50, height = 450, type = 'xy'):
if type == 'xy':
chart_type = constants.xlXYS catter
try:
self.selectShee t(sheet)
except: # sheet doesn't exist so create it
self.newSheet(s heet)
try :
self.xlBook.She ets(sheet).Char tObjects(num).A ctivate #
already exists
except:
self.xlChart = self.xlBook.She ets(sheet).Char tObjects().Add(
Left = left, Width = width, Top = top,
Height = height)
self.xlChart.Ch art.ChartType = chart_type

def addXYChartSerie s(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.Wor ksheets(sheet)
se = self.xlChart.Ch art.SeriesColle ction().NewSeri es()
se.Values = sht.Range(sht.C ells(topRow, yCol),
sht.Cells(botto mRow, yCol))
se.XValues = sht.Range(sht.C ells(topRow, xCol),
sht.Cells(botto mRow, xCol))
if series_name:
se.Name = series_name
if style == 'line':
# line style
se.MarkerStyle = constants.xlNon e
se.Border.Color Index = color
se.Border.Weigh t = constants.xlHai rline
se.Border.LineS tyle = constants.xlCon tinuous
se.Border.Weigh t = constants.xlMed ium
if style == 'point':
# point style
#se.MarkerBackg roundColorIndex = constants.xlNon e
#se.MarkerForeg roundColorIndex = color
se.MarkerBackgr oundColorIndex = color
se.MarkerForegr oundColorIndex = 1 # black
#se.MarkerStyle = constants.xlMar kerStyleCircle
se.MarkerStyle = constants.xlMar kerStyleSquare
se.MarkerSize = 5
# Chart properties
cht = self.xlBook.She ets(chart_sheet ).ChartObjects( chart_num).Char t
# 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(consta nts.xlCategory) .HasTitle = True
cht.Axes(consta nts.xlCategory) .AxisTitle.Capt ion = xlabel
cht.Axes(consta nts.xlCategory) .AxisTitle.Font .Name = 'Arial'
cht.Axes(consta nts.xlCategory) .AxisTitle.Font .Size = 10
cht.Axes(consta nts.xlCategory) .AxisTitle.Font .Bold = False
cht.Axes(consta nts.xlCategory) .MinimumScale = 0
cht.Axes(consta nts.xlCategory) .MaximumScaleIs Auto = True
# Y axis labels
if ylabel:
cht.Axes(consta nts.xlValue).Ha sTitle = True
cht.Axes(consta nts.xlValue).Ax isTitle.Caption = ylabel
cht.Axes(consta nts.xlValue).Ax isTitle.Font.Na me = 'Arial'
cht.Axes(consta nts.xlValue).Ax isTitle.Font.Si ze = 10
cht.Axes(consta nts.xlValue).Ax isTitle.Font.Bo ld = False
cht.Axes(consta nts.xlValue).Mi nimumScale = 0
cht.Axes(consta nts.xlValue).Ma ximumScaleIsAut o = 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(Dir ection = constants.xlY,
Include = constants.xlErr orBarIncludeBot h,
Type = constants.xlErr orBarTypeCustom ,
Amount = amount, MinusValues = amount)
se.ErrorBars.En dStyle = constants.xlNoC ap
se.ErrorBars.Bo rder.LineStyle = constants.xlCon tinuous
se.ErrorBars.Bo rder.ColorIndex = color
se.ErrorBars.Bo rder.Weight = constants.xlHai rline

[implicate_order]

Re: Creating Charts in Excel with pyExcelerator.E xcelMagic

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("Exce l.Application")
wb = xl.Workbooks.Op en(outfile01)

prodws = wb.Worksheets(1 )
wc_prod = wb.Charts.Add()
wc_prod.ChartWi zard(Source=pro dws.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.