473,224 Members | 1,690 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,224 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 13525

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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
5
by: Alan | last post by:
Hi there, Are there Excel charting gurus here?? If so then please read on... Sorry for the cross-post but I'm not familiar with the Excel groups. I've posted to asp.general because if I have...
0
by: mscir | last post by:
Date: Thu, 08 Jul 2004 17:02:27 -0700 Organization: Posted via Supernews, http://www.supernews.com Message-ID: <10ero4l3kp2qa65@corp.supernews.com> Reply-To: mscir@usa.com User-Agent: Mozilla/5.0...
6
by: Anand | last post by:
Hello, Can I get some help on how to read the excel files using python? from win32com.client import Dispatch xlApp = Dispatch("Excel.Application") xlWb = xlApp.Workbooks.Open("Read.xls")...
3
by: tkpmep | last post by:
I downloaded PyExcelerator.zip as I need to write some data into Excel files, and tried unsuccessfully to install it. I unzipped the files into C:/Python24/Lib/site-packages/PyExcelerator, and in a...
4
by: Marco Aschwanden | last post by:
Hi I would like to 1. import an existing Excel-sheet (a template) 2. and add some data to it 3. and save it under a different name afterwards. To me it seems, that pyExcelerator does not...
6
by: Duncan Smith | last post by:
Hello, I am currently implementing (mainly in Python) 'models' that come to me as Excel spreadsheets, with little additional information. I am expected to use these models in a web application. ...
3
by: Hamilton, William | last post by:
I'm in need of a module that will let me create Excel workbooks from within Python. Something like PyExcelerator, but it needs to work with Python 2.3. (A third-party limitation that I have no...
20
by: Marin Brkic | last post by:
Hello all, please, let me apologize in advance. English is not my first language (not even my second one), so excuse any errors with which I'm about to embarass myself in front of the general...
1
isladogs
by: isladogs | last post by:
The next online meeting of the Access Europe User Group will be on Wednesday 6 Dec 2023 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, Mike...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
3
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 3 Jan 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). For other local times, please check World Time Buddy In...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
2
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 7 Feb 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:30 (7.30PM). In this month's session, the creator of the excellent VBE...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....

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.