Open MS Excel Spreadsheet with Python

[Allison Bailey]

Hi Folks,

I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....

I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.

I'm not really sure how to get started with this process.
I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
using Microsoft Excel 10.0 Object Library, then
import win32com.client
xl = win32com.client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")

Then, I get errors when I try the following:
sh = wb.worksheets(1)


I think I'm missing something fairly fundamental, but I've googled all
over the place and can't seem to find anything very introductory about
opening and using data from MS Excel using Python. Any suggestions,
including places to get more information are welcome.

Also, do I need to run the makepy utility every time I run my script?
If so, how would I do it from within my Python program, rather than with
the GUI in the IDE?

Thanks for your help,

Allison


~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Allison Bailey
TerraLogic GIS, Inc.
(e-mail address removed)
425-673-4495
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[John Hunter]

Probably are. Do you use http://groups.google.com to search the
archives of this mailing list? If you go there, and search for

excel group:*python*

you'll likely find everything you need to know.

In a nutshell, using the win32 extensions you can access excel
directly. On other platforms, you'll probably need to go with an
xl->csv converter, and then either parse the csv yourself or use the
excellent csv module (http://object-craft.com.au/projects/csv); note
that a csv module will be standard with the python library as of
python 2.3. If you decide to go this route, let me know: I do a lot
of XL->CSV work in python and have a few utility functions that may
come in handy.

JDH

 

[Thomas =?ISO-8859-15?Q?G=FCttler?=]

Hi Folks,

I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....

I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.

Hi!

You can save the spreadsheet in XML format. This can be parsed and
modified. This has the advantage that it works on all plattforms.

I wrote a small receipe in the python cookbook some months ago.

thomas

 

[Koczian]

First things first:

Python programming on Win32 : [help for Windows programmers] / Mark
Hammond and Andy Robinson. - 1. ed.
Beijing [u.a.] : O'Reilly, 2000. - XVII, 652 S. : Ill.
ISBN 1-56592-621-8

Lots of material about MS Excel <-> Python. Uses Python 1.5.2, but most
of the code works with later versions.

And of course, use your Excel VBA help to get the object and method
names right.

Most probably a simple case sensitivity problem:

sh = wb.Worksheets(1)

sh = wb.ActiveSheet might be better, depending on context.

No, just once. I don't know anything about deploying, though.

Your code worked on my system. However, I have found this interface to be
pretty tricky. It often happens that while you are debugging the program you
generate an instance of Excel which then makes later operations bomb
mysteriously. On XP, hit Ctrl-Alt-Del tp bring up the task manager and then
look at the Processes tab. If you have an instance of Excel running you
should see it there. Use End Process to get rid of it.

Very true (but as I read the original post, the exceptions start at
first try).

You can avoid these problems by making sure that any COM references you make
get released properly if an exception occurs. Your script would look like
this:

import win32com.client
xl = win32com.client.Dispatch("Excel.Application")
try:
wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")
try:
sh = wb.worksheets(1)
try:
print sh.Cells(2,3)
except:
sh = None
raise
sh = None
except:
wb.Close()
wb = None
raise
wb.Close()
wb = None
except:
xl = None
raise
xl = None

There are more elegant ways of doing this using classes. However, with this
code I think you will never see one of those mysterious Excel instances in
your task manager.

I think try / finally would be better. Explicitly deleting all three:
sh, wb and xl is the key to not having Excel zombies, if there is an
exception or not.

That's a small bug in the easyExcel class of the Hammond book: it only
deletes xl, not the variable representing the Workbook. So you still get
your zombie.

HTH,
Koczian

--
Dr. Sibylle Koczian
Universitaetsbibliothek, Abt. Naturwiss.
D-86135 Augsburg

Tel.: (0821) 598-2400, Fax : (0821) 598-2410
e-mail : (e-mail address removed)-Augsburg.DE

 

[logistix at cathoderaymission.net]

Then, I get errors when I try the following:
sh = wb.worksheets(1)

After running makepy, your methods become case sensitive. They are
case insensitive before makepy. You probably need 'wb.Worksheets(1)'

 

[William Peterson]

Hi Folks,

I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....

I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.

I'm not really sure how to get started with this process.
I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
using Microsoft Excel 10.0 Object Library, then
import win32com.client
xl = win32com.client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")

At this point, I don't believe you're doing Python any more, but
Visual Basic for Applications

Then, I get errors when I try the following:
sh = wb.worksheets(1)

Is worksheets the proper term or should it be Sheets? Probably depends
on the version of Excel/VBA

Im looking at "Python Programming on Win32", an Oreilly book. If you
really must work with Excel or Word you should get it.

Bill

 

[Peter Abel]

Hi Folks,

I'm a brand new Python programmer, so please point me in the right
direction if this is not the best forum for this question....

I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.

I'm not really sure how to get started with this process.
I ran the COM Makepy utility from my PythonWin (IDE from ActiveSTate),
using Microsoft Excel 10.0 Object Library, then
import win32com.client
xl = win32com.client.Dispatch("Excel.Application")
wb = xl.Workbooks.Open ("c:\\data\\myspreadsheet.xls")

Then, I get errors when I try the following:
sh = wb.worksheets(1)

Try

sh=wb.Sheets[1] OR
sh=xl.Workbooks[0].Sheets[1]

You need to have only 1 Workbook open or the one you
want, must be the first one

xl.Workbooks[0].Sheets[1]==wb.Sheets[1]

1

Regards
Peter



I think I'm missing something fairly fundamental, but I've googled all
over the place and can't seem to find anything very introductory about
opening and using data from MS Excel using Python. Any suggestions,
including places to get more information are welcome.

Also, do I need to run the makepy utility every time I run my script?
If so, how would I do it from within my Python program, rather than with
the GUI in the IDE?

Thanks for your help,

Allison


~~~~~~~~~~~~~~~~~~~~~~~~~~~~
Allison Bailey
TerraLogic GIS, Inc.
(e-mail address removed)
425-673-4495
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

 

[srijit]

Hi,
I am working on Python-Excel interface based on the excellent library
win32com.
I was planning to put it up as a Python recipe in Activestate.
Anyway before that I shall personally send you the code after some
cleanup. It will be good to get an early feedback before I upload it
in Activestate.

Regards,
Srijit

 

[A.Bailey]

Hi Folks,

I would like to open an existing MS Excel spreadsheet and extract
information from specific worksheets and cells.

Then, I get errors when I try the following:
sh = wb.worksheets(1)

Thanks to everyone for suggestions, hints, and code, particularly the
ideas about where to find more information. I know that MS Excel is
not every Open Source-er's idea of a good time, but my plan is to get
my data out of there as fast as I can and do the fun stuff in Python!

It turns out, as some people pointed out, that after running the
makepy utility, the methods become case-sensitive, so it should have
read:
sh = wb.Worksheets(1)
and everything would have gone smooth as silk!

And yes, I would love to preview Excel/Python code if you would like
to pass it along to me.

Allison

 

[Antti Kaihola]

There's an excellent utility called xlhtml, which converts XLS
spreadsheets to HTML files, available at
http://chicago.sourceforge.net/xlhtml/

I made a simple Python wrapper, which uses xlhtml and parses the
converted spreadsheet into a Python Numeric array. As I didn't need to
do any calculations with the data, you only get cell contents as string
objects at this time.

Note that using this method you only need freely available tools, no
excel or windows. Also, it isn't necessary to open and save the
spreadsheet as a CSV or XML file, which is nice if you need to batch
process lots of spreadsheets.

Take a look at akaihola.iki.fi/comp/python and drop me a note if you
make improvements to the code.


Antti

 

[srijit]

Hello Members,
I would like to share the Python-Excel Interface code.
This is based on sample code from Mark Hammond and Andy Robinson's
"Python Programming on Win32"
I use Python 2.3 and Win32all-157 on a Win 98 machine.
I look forward to feedback on how to improve this code. If I get good
response I may think of putting up this code as a Python recipe in
Activestate.
Once again hats off to Mark Hammond for his excellent Win32all
library.

Regards,
Srijit

---------------------------------------------------------------------------

import win32com.client.dynamic

class UseExcel(object):
"""Python Excel Interface. It provides methods for accessing the
basic functionality of MS Excel 97/2000 from Python.

This interface uses dynamic dispatch objects. All necessary constants
are embedded in the code. There is no need to run makepy.py.
"""
__slots__ = ("xlapp", "xlbook")
def __init__(self, fileName=None):
"""e.g. xlFile =
useExcel("e:\\python23\myfiles\\testExcel1.xls")
"""
self.xlapp =
win32com.client.dynamic.Dispatch("Excel.Application")
if fileName:
self.xlbook = self.xlapp.Workbooks.Open(fileName)
else:
self.xlbook = self.xlapp.Workbooks.Add()

def save(self, newfileName=None):
if newfilename:
self.xlbook.SaveAs(newfileName)
else:
self.xlbook.Save()

def close(self):
self.xlbook.Close(SaveChanges=False)
del self.xlapp

def show(self):
self.xlapp.Visible = True

def hide(self):
self.xlapp.Visible = False

def getcell(self, sheet, cellAddress):
"""Get value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
return sht.Range(cellAddress).Value
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
return sht.Cells(row, col).Value

def setcell(self, sheet, value, cellAddress,
fontStyle=("Regular",), fontName="Arial",
fontSize=12, fontColor=1):
"""Set value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
value - The cell value. it can be a number, string etc.
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
fontStyle - tuple. Combination of Regular, Bold, Italic, Underline
e.g. ("Regular", "Bold", "Italic")
fontColor - ColorIndex. Refer ColorIndex property in Microsoft
Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).Value = value
sht.Range(cellAddress).Font.Size = fontSize
sht.Range(cellAddress).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Range(cellAddress).Font.Name = fontName
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).Value = value
sht.Cells(row, col).Font.FontSize = fontSize
sht.Cells(row, col).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Cells(row, col).Font.Name = fontName

def getrange(self, sheet, rangeAddress):
"""Returns a tuple of tuples from a range of cells. Each tuple
corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
return sht.Range(rangeAddress).Value
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
return sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).Value

def setrange(self, sheet, topRow, leftCol, data):
"""Sets range of cells with values from data. data is a tuple
of tuples.
Each tuple corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
topRow - row number (integer data type)
leftCol - column number (integer data type)
"""
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
return (bottomRow, rightCol)

def setcellalign(self, sheet, cellAddress, alignment):
"""Aligns the contents of the cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
alignment - "Left", "Right" or "center"
"""
if (alignment.lower() == "left"):
alignmentValue = 2
elif ((alignment.lower() == "center") or (alignment.lower() ==
"centre")):
alignmentValue = 3
elif (alignment.lower() == "right"):
alignmentValue = 4
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).HorizontalAlignment =
alignmentValue
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).HorizontalAlignment = alignmentValue

def addnewworksheetbefore(self, oldSheet, newSheetName):
"""Adds a new excel sheet before the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet before which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(sht).Name = newSheetName

def addnewworksheetafter(self, oldSheet, newSheetName):
"""Adds a new excel sheet after the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet after which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(None,sht).Name = newSheetName

def insertchart(self, sheet, left, top, width, height):
"""Creates a new embedded chart. Returns a ChartObject object.
Refer Add Method(ChartObjects Collection) in Microsoft Excel Visual
Basic Reference.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
left, top - The initial coordinates of the new object (in
points), relative to the upper-left corner of cell A1 on a worksheet
or to the upper-left corner of a chart.
width, height - The initial size of the new object, in points.
point - A unit of measurement equal to 1/72
inch.
"""
sht = self.xlbook.Worksheets(sheet)
return sht.ChartObjects().Add(left, top, width, height)

def plotdata(self, sheet, dataRanges, chartObject, chartType,
plotBy=None,
categoryLabels=1, seriesLabels=0,
hasLegend=None, title=None,
categoryTitle=None, valueTitle=None,
extraTitle=None):
"""Plots data using ChartWizard. For details refer ChartWizard
method in Microsoft Excel Visual Basic Reference.
Before using PlotData method InsertChart method should be used.

Description of parameters:
sheet - name of the excel worksheet. This name should be same
as that in InsertChart method
dataRanges - tuple of tuples ((topRow, leftCol, bottomRow,
rightCol),). Range of data in excel worksheet to be plotted.
chartObject - Embedded chart object returned by InsertChart method.
chartType - Refer plotType variable for available options.
For remaining parameters refer ChartWizard method in Microsoft Excel
Visual Basic Reference.
"""
sht = self.xlbook.Worksheets(sheet)
if (len(dataRanges) == 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
elif (len(dataRanges) > 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
for count in range(len(dataRanges[1:])):
topRow, leftCol, bottomRow, rightCol =
dataRanges[count+1]
tempSource = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
source = self.xlapp.Union(source, tempSource)
plotType = {
"Area" : 1,
"Bar" : 2,
"Column" : 3,
"Line" : 4,
"Pie" : 5,
"Radar" : -4151,
"Scatter" : -4169,
"Combination" : -4111,
"3DArea" : -4098,
"3DBar" : -4099,
"3DColumn" : -4100,
"3DPie" : -4101,
"3DSurface" : -4103,
"Doughnut" : -4120,
"Radar" : -4151,
"Bubble" : 15,
"Surface" : 83,
"Cone" : 3,
"3DAreaStacked" : 78,
"3DColumnStacked" : 55
}
gallery = plotType[chartType]
format = None
chartObject.Chart.ChartWizard(source, gallery, format, plotBy,
categoryLabels, seriesLabels, hasLegend, title, categoryTitle,
valueTitle, extraTitle)

def copyrange(self,source, destination):
"""Copy range of data from source range in a sheet to
destination range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
source - tuple (sheet, rangeAddress)
sheet - name of the excel sheet
rangeAddress - "cell1Address:cell2Address"
destination - tuple (sheet, destinationCellAddress)
destinationCellAddress - string "ColumnRow"
"""
sourceSht = self.xlbook.Worksheets(source[0])
destinationSht = self.xlbook.Worksheets(destination[0])
sourceSht.Range(source[1]).Copy(destinationSht.Range(destination[1]))

def copychart(self, sourceChartObject, destination,delete="N"):
"""Copy chart from source range in a sheet to destination
range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
sourceChartObject - Chart object returned by InsertChart method.
destination - tuple (sheet, destinationCellAddress)
sheet - name of the excel
worksheet.
destinationCellAddress - string "ColumnRow"
if sheet is omitted and only
destinationCellAddress is available as string data then same sheet is
assumed.
delete - "Y" or "N". If "Y" the source chart object is
deleted after copy.
So if "Y" copy chart is equivalent to move
chart.
"""
if (isinstance(destination,tuple)):
sourceChartObject.Copy()
sht = self.xlbook.Worksheets(destination[0])
sht.Paste(sht.Range(destination[1]))
else:
sourceChartObject.Chart.ChartArea.Copy()
destination.Chart.Paste()
if (delete.upper() =="Y"):
sourceChartObject.Delete()

def hidecolumn(self, sheet, col):
"""Hide a column.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
col - column number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Columns(col).Hidden = True

def hiderow(self, sheet, row):
""" Hide a row.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
row - row number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Rows(row).Hidden = True

def excelfunction(self, sheet, range, function):
"""Access Microsoft Excel worksheet functions. Refer
WorksheetFunction Object in Microsoft Excel Visual Basic Reference

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
range - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
For list of functions refer List of Worksheet Functions
Available to Visual Basic in Microsoft Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if isinstance(range,str):
xlRange = "(sht.Range(" + "'" + range + "'" + "))"
elif isinstance(range,tuple):
topRow = range[0]
leftColumn = range[1]
bottomRow = range[2]
rightColumn = range[3]
xlRange = "(sht.Range(sht.Cells(topRow, leftColumn),
sht.Cells(bottomRow, rightColumn)))"
xlFunction = "self.xlapp.WorksheetFunction." + function +
xlRange
return eval(xlFunction, globals(), locals())

def clearrange(self, sheet, rangeAddress, contents="Y",
format="Y"):
"""Clear the contents of a range of cells.
Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
contents - "Y" or "N". If "Y" clears the formulas from the
range
format - "Y" or "N". If "Y" clears the formatting of the
object
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
if (format.upper() == "Y"):
sht.Range(rangeAddress).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(rangeAddress).ClearContents()
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
if (format.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearContents()

def addcomment(self, sheet, cellAddress, comment=""):
"""Add or delete comment to a cell. If parameter comment is
None, delete the comments

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
comment - String data. Comment to be added. If None, delete
comments
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
if (comment != None):
sht.Range(cellAddress).AddComment(comment)
else:
sht.Range(cellAddress).ClearComments()
elif (isinstance(cellAddress,tuple)):
row1 = cellAddress[0]
col1 = cellAddress[1]
if (comment != None):
sht.Cells(row1, col1).AddComment(comment)
else:
sht.Cells(row1, col1).ClearComments()

def excelapp():
xlFile = UseExcel("e:\\python23\myfiles\\StudentTabulation.xls")
xlFile.show()
xlFile.setcell(sheet="Sheet1", value="Class X Annual
Examination",fontName="Arial",
cellAddress="D1",fontColor=1,
fontStyle=("Bold",), fontSize=16)
xlFile.setcell(sheet="Sheet1", value="Subject : History",
fontName="Arial",
cellAddress="D3",fontColor=1)
data = (
("Sl. No." ,"Name of Students", "Roll No.",
"Marks(out of 100)"),
(1 ,"John" ,1020, 52),
(2 ,"Nikhil" ,1021, 75),
(3 ,"Stefen" ,1025, 85),
(4 ,"Thomas" ,1026, 54),
(5 ,"Ali" ,1027, 87),
(6 ,"Sanjay" ,1028, 0)
)
(bottomRow, rightCol) = xlFile.setrange("Sheet1", 5,2,data)
xlFile.addcomment("sheet1", "C11", "Absent")
chrt1 = xlFile.insertchart("sheet2", 100, 100, 400, 200)
xlFile.plotdata(sheet="sheet1",dataRanges=((6,3,bottomRow,5),),
chartObject=chrt1,
title="Annual Examination : History", plotBy=2,
categoryLabels=1,
seriesLabels=0, chartType="Bar")
#~ xlFile.clearrange("sheet1",(3,2,3,5),"y")
#~ xlFile.addcomment("sheet1", "B4", "Test Comment")
#~ chrt1 = xlFile.insertchart("sheet1", 100, 100, 400, 250)
#~ xlFile.plotdata(sheet="sheet1",dataRange=(4,2,bottomRow,
rightCol), chartObject=chrt1,
#~ title="Test Chart", chartType="Column")
#~ xlFile.copyrange(("sheet1","C3:E3"), ("sheet2", "C3"))
#~ chrt2 = xlFile.insertchart("sheet2", 100, 100, 400, 250)
#~ xlFile.movechart(chrt1,chrt2)
#~ xlFile.copychart(chrt1,("sheet3","D22"), "y")
#~ xlFile.hiderow("sheet1",7)
#~ print xlFile.excelfunction("sheet1", (3,2,3,5), "Min")
#~ print xlFile.getrange("sheet1","A2","C3")
#~ xlFile.setcellfont("sheet1","Regular", "A1")
#~ cellVal1 = xlFile.getcell("sheet1","A1")
#~ xlFile.setcell("sheet1", cellVal1,1,3)
#~ xlFile.setcellfont("sheet1","bold","C1")
#~ xlFile.setcellfont("sheet1","italic",1,3)
#~ xlFile.setcellfont("sheet1","underline",1,3)
#~ xlFile.setcellalign("sheet1","left",1,3)
#~ print xlFile.getrange("sheet1", "D5", "F6")
#~ xlFile.setrange("sheet1", 10,10,
((45,67),(67,"342"),(88,66.8),(32,77),(3,3)))
#~ xlFile.addnewworksheetafter("sheet1", "Srijit1")

if (__name__ == "__main__"):
excelapp()


---------------------------------------------------------------------------

 

[Ajith Prasad]

Would it be possible to give a step-by-step guide on how to use the
script, ie, what should one do in an interactive Python session to use
one or two of the functions in the script?

Hello Members,
I would like to share the Python-Excel Interface code.
This is based on sample code from Mark Hammond and Andy Robinson's
"Python Programming on Win32"
I use Python 2.3 and Win32all-157 on a Win 98 machine.
I look forward to feedback on how to improve this code. If I get good
response I may think of putting up this code as a Python recipe in
Activestate.
Once again hats off to Mark Hammond for his excellent Win32all
library.

Regards,
Srijit

---------------------------------------------------------------------------

import win32com.client.dynamic

class UseExcel(object):
"""Python Excel Interface. It provides methods for accessing the
basic functionality of MS Excel 97/2000 from Python.

This interface uses dynamic dispatch objects. All necessary constants
are embedded in the code. There is no need to run makepy.py.
"""
__slots__ = ("xlapp", "xlbook")
def __init__(self, fileName=None):
"""e.g. xlFile =
useExcel("e:\\python23\myfiles\\testExcel1.xls")
"""
self.xlapp =
win32com.client.dynamic.Dispatch("Excel.Application")
if fileName:
self.xlbook = self.xlapp.Workbooks.Open(fileName)
else:
self.xlbook = self.xlapp.Workbooks.Add()

def save(self, newfileName=None):
if newfilename:
self.xlbook.SaveAs(newfileName)
else:
self.xlbook.Save()

def close(self):
self.xlbook.Close(SaveChanges=False)
del self.xlapp

def show(self):
self.xlapp.Visible = True

def hide(self):
self.xlapp.Visible = False

def getcell(self, sheet, cellAddress):
"""Get value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
return sht.Range(cellAddress).Value
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
return sht.Cells(row, col).Value

def setcell(self, sheet, value, cellAddress,
fontStyle=("Regular",), fontName="Arial",
fontSize=12, fontColor=1):
"""Set value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
value - The cell value. it can be a number, string etc.
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
fontStyle - tuple. Combination of Regular, Bold, Italic, Underline
e.g. ("Regular", "Bold", "Italic")
fontColor - ColorIndex. Refer ColorIndex property in Microsoft
Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).Value = value
sht.Range(cellAddress).Font.Size = fontSize
sht.Range(cellAddress).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Range(cellAddress).Font.Name = fontName
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).Value = value
sht.Cells(row, col).Font.FontSize = fontSize
sht.Cells(row, col).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Cells(row, col).Font.Name = fontName

def getrange(self, sheet, rangeAddress):
"""Returns a tuple of tuples from a range of cells. Each tuple
corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
return sht.Range(rangeAddress).Value
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
return sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).Value

def setrange(self, sheet, topRow, leftCol, data):
"""Sets range of cells with values from data. data is a tuple
of tuples.
Each tuple corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
topRow - row number (integer data type)
leftCol - column number (integer data type)
"""
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
return (bottomRow, rightCol)

def setcellalign(self, sheet, cellAddress, alignment):
"""Aligns the contents of the cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
alignment - "Left", "Right" or "center"
"""
if (alignment.lower() == "left"):
alignmentValue = 2
elif ((alignment.lower() == "center") or (alignment.lower() ==
"centre")):
alignmentValue = 3
elif (alignment.lower() == "right"):
alignmentValue = 4
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).HorizontalAlignment =
alignmentValue
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).HorizontalAlignment = alignmentValue

def addnewworksheetbefore(self, oldSheet, newSheetName):
"""Adds a new excel sheet before the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet before which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(sht).Name = newSheetName

def addnewworksheetafter(self, oldSheet, newSheetName):
"""Adds a new excel sheet after the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet after which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(None,sht).Name = newSheetName

def insertchart(self, sheet, left, top, width, height):
"""Creates a new embedded chart. Returns a ChartObject object.
Refer Add Method(ChartObjects Collection) in Microsoft Excel Visual
Basic Reference.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
left, top - The initial coordinates of the new object (in
points), relative to the upper-left corner of cell A1 on a worksheet
or to the upper-left corner of a chart.
width, height - The initial size of the new object, in points.
point - A unit of measurement equal to 1/72
inch.
"""
sht = self.xlbook.Worksheets(sheet)
return sht.ChartObjects().Add(left, top, width, height)

def plotdata(self, sheet, dataRanges, chartObject, chartType,
plotBy=None,
categoryLabels=1, seriesLabels=0,
hasLegend=None, title=None,
categoryTitle=None, valueTitle=None,
extraTitle=None):
"""Plots data using ChartWizard. For details refer ChartWizard
method in Microsoft Excel Visual Basic Reference.
Before using PlotData method InsertChart method should be used.

Description of parameters:
sheet - name of the excel worksheet. This name should be same
as that in InsertChart method
dataRanges - tuple of tuples ((topRow, leftCol, bottomRow,
rightCol),). Range of data in excel worksheet to be plotted.
chartObject - Embedded chart object returned by InsertChart method.
chartType - Refer plotType variable for available options.
For remaining parameters refer ChartWizard method in Microsoft Excel
Visual Basic Reference.
"""
sht = self.xlbook.Worksheets(sheet)
if (len(dataRanges) == 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
elif (len(dataRanges) > 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
for count in range(len(dataRanges[1:])):
topRow, leftCol, bottomRow, rightCol =
dataRanges[count+1]
tempSource = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
source = self.xlapp.Union(source, tempSource)
plotType = {
"Area" : 1,
"Bar" : 2,
"Column" : 3,
"Line" : 4,
"Pie" : 5,
"Radar" : -4151,
"Scatter" : -4169,
"Combination" : -4111,
"3DArea" : -4098,
"3DBar" : -4099,
"3DColumn" : -4100,
"3DPie" : -4101,
"3DSurface" : -4103,
"Doughnut" : -4120,
"Radar" : -4151,
"Bubble" : 15,
"Surface" : 83,
"Cone" : 3,
"3DAreaStacked" : 78,
"3DColumnStacked" : 55
}
gallery = plotType[chartType]
format = None
chartObject.Chart.ChartWizard(source, gallery, format, plotBy,
categoryLabels, seriesLabels, hasLegend, title, categoryTitle,
valueTitle, extraTitle)

def copyrange(self,source, destination):
"""Copy range of data from source range in a sheet to
destination range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
source - tuple (sheet, rangeAddress)
sheet - name of the excel sheet
rangeAddress - "cell1Address:cell2Address"
destination - tuple (sheet, destinationCellAddress)
destinationCellAddress - string "ColumnRow"
"""
sourceSht = self.xlbook.Worksheets(source[0])
destinationSht = self.xlbook.Worksheets(destination[0])
sourceSht.Range(source[1]).Copy(destinationSht.Range(destination[1]))

def copychart(self, sourceChartObject, destination,delete="N"):
"""Copy chart from source range in a sheet to destination
range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
sourceChartObject - Chart object returned by InsertChart method.
destination - tuple (sheet, destinationCellAddress)
sheet - name of the excel
worksheet.
destinationCellAddress - string "ColumnRow"
if sheet is omitted and only
destinationCellAddress is available as string data then same sheet is
assumed.
delete - "Y" or "N". If "Y" the source chart object is
deleted after copy.
So if "Y" copy chart is equivalent to move
chart.
"""
if (isinstance(destination,tuple)):
sourceChartObject.Copy()
sht = self.xlbook.Worksheets(destination[0])
sht.Paste(sht.Range(destination[1]))
else:
sourceChartObject.Chart.ChartArea.Copy()
destination.Chart.Paste()
if (delete.upper() =="Y"):
sourceChartObject.Delete()

def hidecolumn(self, sheet, col):
"""Hide a column.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
col - column number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Columns(col).Hidden = True

def hiderow(self, sheet, row):
""" Hide a row.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
row - row number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Rows(row).Hidden = True

def excelfunction(self, sheet, range, function):
"""Access Microsoft Excel worksheet functions. Refer
WorksheetFunction Object in Microsoft Excel Visual Basic Reference

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
range - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
For list of functions refer List of Worksheet Functions
Available to Visual Basic in Microsoft Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if isinstance(range,str):
xlRange = "(sht.Range(" + "'" + range + "'" + "))"
elif isinstance(range,tuple):
topRow = range[0]
leftColumn = range[1]
bottomRow = range[2]
rightColumn = range[3]
xlRange = "(sht.Range(sht.Cells(topRow, leftColumn),
sht.Cells(bottomRow, rightColumn)))"
xlFunction = "self.xlapp.WorksheetFunction." + function +
xlRange
return eval(xlFunction, globals(), locals())

def clearrange(self, sheet, rangeAddress, contents="Y",
format="Y"):
"""Clear the contents of a range of cells.
Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
contents - "Y" or "N". If "Y" clears the formulas from the
range
format - "Y" or "N". If "Y" clears the formatting of the
object
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
if (format.upper() == "Y"):
sht.Range(rangeAddress).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(rangeAddress).ClearContents()
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
if (format.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearContents()

def addcomment(self, sheet, cellAddress, comment=""):
"""Add or delete comment to a cell. If parameter comment is
None, delete the comments

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
comment - String data. Comment to be added. If None, delete
comments
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
if (comment != None):
sht.Range(cellAddress).AddComment(comment)
else:
sht.Range(cellAddress).ClearComments()
elif (isinstance(cellAddress,tuple)):
row1 = cellAddress[0]
col1 = cellAddress[1]
if (comment != None):
sht.Cells(row1, col1).AddComment(comment)
else:
sht.Cells(row1, col1).ClearComments()

def excelapp():
xlFile = UseExcel("e:\\python23\myfiles\\StudentTabulation.xls")
xlFile.show()
xlFile.setcell(sheet="Sheet1", value="Class X Annual
Examination",fontName="Arial",
cellAddress="D1",fontColor=1,
fontStyle=("Bold",), fontSize=16)
xlFile.setcell(sheet="Sheet1", value="Subject : History",
fontName="Arial",
cellAddress="D3",fontColor=1)
data = (
("Sl. No." ,"Name of Students", "Roll No.",
"Marks(out of 100)"),
(1 ,"John" ,1020, 52),
(2 ,"Nikhil" ,1021, 75),
(3 ,"Stefen" ,1025, 85),
(4 ,"Thomas" ,1026, 54),
(5 ,"Ali" ,1027, 87),
(6 ,"Sanjay" ,1028, 0)
)
(bottomRow, rightCol) = xlFile.setrange("Sheet1", 5,2,data)
xlFile.addcomment("sheet1", "C11", "Absent")
chrt1 = xlFile.insertchart("sheet2", 100, 100, 400, 200)
xlFile.plotdata(sheet="sheet1",dataRanges=((6,3,bottomRow,5),),
chartObject=chrt1,
title="Annual Examination : History", plotBy=2,
categoryLabels=1,
seriesLabels=0, chartType="Bar")
#~ xlFile.clearrange("sheet1",(3,2,3,5),"y")
#~ xlFile.addcomment("sheet1", "B4", "Test Comment")
#~ chrt1 = xlFile.insertchart("sheet1", 100, 100, 400, 250)
#~ xlFile.plotdata(sheet="sheet1",dataRange=(4,2,bottomRow,
rightCol), chartObject=chrt1,
#~ title="Test Chart", chartType="Column")
#~ xlFile.copyrange(("sheet1","C3:E3"), ("sheet2", "C3"))
#~ chrt2 = xlFile.insertchart("sheet2", 100, 100, 400, 250)
#~ xlFile.movechart(chrt1,chrt2)
#~ xlFile.copychart(chrt1,("sheet3","D22"), "y")
#~ xlFile.hiderow("sheet1",7)
#~ print xlFile.excelfunction("sheet1", (3,2,3,5), "Min")
#~ print xlFile.getrange("sheet1","A2","C3")
#~ xlFile.setcellfont("sheet1","Regular", "A1")
#~ cellVal1 = xlFile.getcell("sheet1","A1")
#~ xlFile.setcell("sheet1", cellVal1,1,3)
#~ xlFile.setcellfont("sheet1","bold","C1")
#~ xlFile.setcellfont("sheet1","italic",1,3)
#~ xlFile.setcellfont("sheet1","underline",1,3)
#~ xlFile.setcellalign("sheet1","left",1,3)
#~ print xlFile.getrange("sheet1", "D5", "F6")
#~ xlFile.setrange("sheet1", 10,10,
((45,67),(67,"342"),(88,66.8),(32,77),(3,3)))
#~ xlFile.addnewworksheetafter("sheet1", "Srijit1")

if (__name__ == "__main__"):
excelapp()


---------------------------------------------------------------------------

Hi,
I am working on Python-Excel interface based on the excellent library
win32com.
I was planning to put it up as a Python recipe in Activestate.
Anyway before that I shall personally send you the code after some
cleanup. It will be good to get an early feedback before I upload it
in Activestate.

Regards,
Srijit

 

[Ajith Prasad]

Would appreciate any advice on how to get started with the script.
Created an empty spreadsheet "test.xls" on drive F. Then at the
PythonWin prompt tried and obtained:Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "UseExcel.py", line 16, in __init__
File "<COMObject <unknown>>", line 2, in Open
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel',
"'f:\test.xls' could not be found. Check the spelling of the file
name, and verify that the file location is correct.\n\nIf you are
trying to open the file from your list of most recently used files on
the File menu, make sure that the file has not been renamed, moved, or
deleted.", 'C:\\Program Files\\Microsoft
Office\\Office10\\1033\\xlmain10.chm', 0, -2146827284), None)

Thanks in advance.

Hello Members,
I would like to share the Python-Excel Interface code.
This is based on sample code from Mark Hammond and Andy Robinson's
"Python Programming on Win32"
I use Python 2.3 and Win32all-157 on a Win 98 machine.
I look forward to feedback on how to improve this code. If I get good
response I may think of putting up this code as a Python recipe in
Activestate.
Once again hats off to Mark Hammond for his excellent Win32all
library.

Regards,
Srijit

---------------------------------------------------------------------------

import win32com.client.dynamic

class UseExcel(object):
"""Python Excel Interface. It provides methods for accessing the
basic functionality of MS Excel 97/2000 from Python.

This interface uses dynamic dispatch objects. All necessary constants
are embedded in the code. There is no need to run makepy.py.
"""
__slots__ = ("xlapp", "xlbook")
def __init__(self, fileName=None):
"""e.g. xlFile =
useExcel("e:\\python23\myfiles\\testExcel1.xls")
"""
self.xlapp =
win32com.client.dynamic.Dispatch("Excel.Application")
if fileName:
self.xlbook = self.xlapp.Workbooks.Open(fileName)
else:
self.xlbook = self.xlapp.Workbooks.Add()

def save(self, newfileName=None):
if newfilename:
self.xlbook.SaveAs(newfileName)
else:
self.xlbook.Save()

def close(self):
self.xlbook.Close(SaveChanges=False)
del self.xlapp

def show(self):
self.xlapp.Visible = True

def hide(self):
self.xlapp.Visible = False

def getcell(self, sheet, cellAddress):
"""Get value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
return sht.Range(cellAddress).Value
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
return sht.Cells(row, col).Value

def setcell(self, sheet, value, cellAddress,
fontStyle=("Regular",), fontName="Arial",
fontSize=12, fontColor=1):
"""Set value of one cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
value - The cell value. it can be a number, string etc.
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
fontStyle - tuple. Combination of Regular, Bold, Italic, Underline
e.g. ("Regular", "Bold", "Italic")
fontColor - ColorIndex. Refer ColorIndex property in Microsoft
Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).Value = value
sht.Range(cellAddress).Font.Size = fontSize
sht.Range(cellAddress).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Range(cellAddress).Font.Name = fontName
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).Value = value
sht.Cells(row, col).Font.FontSize = fontSize
sht.Cells(row, col).Font.ColorIndex = fontColor
for i, item in enumerate(fontStyle):
if (item.lower() == "bold"):
sht.Range(cellAddress).Font.Bold = True
elif (item.lower() == "italic"):
sht.Range(cellAddress).Font.Italic = True
elif (item.lower() == "underline"):
sht.Range(cellAddress).Font.Underline = True
elif (item.lower() == "regular"):
sht.Range(cellAddress).Font.FontStyle = "Regular"
sht.Cells(row, col).Font.Name = fontName

def getrange(self, sheet, rangeAddress):
"""Returns a tuple of tuples from a range of cells. Each tuple
corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
return sht.Range(rangeAddress).Value
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
return sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).Value

def setrange(self, sheet, topRow, leftCol, data):
"""Sets range of cells with values from data. data is a tuple
of tuples.
Each tuple corresponds to a row in excel sheet.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
topRow - row number (integer data type)
leftCol - column number (integer data type)
"""
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
return (bottomRow, rightCol)

def setcellalign(self, sheet, cellAddress, alignment):
"""Aligns the contents of the cell.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
alignment - "Left", "Right" or "center"
"""
if (alignment.lower() == "left"):
alignmentValue = 2
elif ((alignment.lower() == "center") or (alignment.lower() ==
"centre")):
alignmentValue = 3
elif (alignment.lower() == "right"):
alignmentValue = 4
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
sht.Range(cellAddress).HorizontalAlignment =
alignmentValue
elif (isinstance(cellAddress,tuple)):
row = cellAddress[0]
col = cellAddress[1]
sht.Cells(row, col).HorizontalAlignment = alignmentValue

def addnewworksheetbefore(self, oldSheet, newSheetName):
"""Adds a new excel sheet before the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet before which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(sht).Name = newSheetName

def addnewworksheetafter(self, oldSheet, newSheetName):
"""Adds a new excel sheet after the given excel sheet.

Description of parameters (self explanatory parameters are not
described):
oldSheet - Name of the sheet after which a new sheet should
be inserted
newSheetName - Name of the new sheet
"""
sht = self.xlbook.Worksheets(oldSheet)
self.xlbook.Worksheets.Add(None,sht).Name = newSheetName

def insertchart(self, sheet, left, top, width, height):
"""Creates a new embedded chart. Returns a ChartObject object.
Refer Add Method(ChartObjects Collection) in Microsoft Excel Visual
Basic Reference.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
left, top - The initial coordinates of the new object (in
points), relative to the upper-left corner of cell A1 on a worksheet
or to the upper-left corner of a chart.
width, height - The initial size of the new object, in points.
point - A unit of measurement equal to 1/72
inch.
"""
sht = self.xlbook.Worksheets(sheet)
return sht.ChartObjects().Add(left, top, width, height)

def plotdata(self, sheet, dataRanges, chartObject, chartType,
plotBy=None,
categoryLabels=1, seriesLabels=0,
hasLegend=None, title=None,
categoryTitle=None, valueTitle=None,
extraTitle=None):
"""Plots data using ChartWizard. For details refer ChartWizard
method in Microsoft Excel Visual Basic Reference.
Before using PlotData method InsertChart method should be used.

Description of parameters:
sheet - name of the excel worksheet. This name should be same
as that in InsertChart method
dataRanges - tuple of tuples ((topRow, leftCol, bottomRow,
rightCol),). Range of data in excel worksheet to be plotted.
chartObject - Embedded chart object returned by InsertChart method.
chartType - Refer plotType variable for available options.
For remaining parameters refer ChartWizard method in Microsoft Excel
Visual Basic Reference.
"""
sht = self.xlbook.Worksheets(sheet)
if (len(dataRanges) == 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
elif (len(dataRanges) > 1):
topRow, leftCol, bottomRow, rightCol = dataRanges[0]
source = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
for count in range(len(dataRanges[1:])):
topRow, leftCol, bottomRow, rightCol =
dataRanges[count+1]
tempSource = sht.Range(sht.Cells(topRow, leftCol),
sht.Cells(bottomRow, rightCol))
source = self.xlapp.Union(source, tempSource)
plotType = {
"Area" : 1,
"Bar" : 2,
"Column" : 3,
"Line" : 4,
"Pie" : 5,
"Radar" : -4151,
"Scatter" : -4169,
"Combination" : -4111,
"3DArea" : -4098,
"3DBar" : -4099,
"3DColumn" : -4100,
"3DPie" : -4101,
"3DSurface" : -4103,
"Doughnut" : -4120,
"Radar" : -4151,
"Bubble" : 15,
"Surface" : 83,
"Cone" : 3,
"3DAreaStacked" : 78,
"3DColumnStacked" : 55
}
gallery = plotType[chartType]
format = None
chartObject.Chart.ChartWizard(source, gallery, format, plotBy,
categoryLabels, seriesLabels, hasLegend, title, categoryTitle,
valueTitle, extraTitle)

def copyrange(self,source, destination):
"""Copy range of data from source range in a sheet to
destination range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
source - tuple (sheet, rangeAddress)
sheet - name of the excel sheet
rangeAddress - "cell1Address:cell2Address"
destination - tuple (sheet, destinationCellAddress)
destinationCellAddress - string "ColumnRow"
"""
sourceSht = self.xlbook.Worksheets(source[0])
destinationSht = self.xlbook.Worksheets(destination[0])
sourceSht.Range(source[1]).Copy(destinationSht.Range(destination[1]))

def copychart(self, sourceChartObject, destination,delete="N"):
"""Copy chart from source range in a sheet to destination
range in same sheet or different sheet

Description of parameters (self explanatory parameters are not
described):
sourceChartObject - Chart object returned by InsertChart method.
destination - tuple (sheet, destinationCellAddress)
sheet - name of the excel
worksheet.
destinationCellAddress - string "ColumnRow"
if sheet is omitted and only
destinationCellAddress is available as string data then same sheet is
assumed.
delete - "Y" or "N". If "Y" the source chart object is
deleted after copy.
So if "Y" copy chart is equivalent to move
chart.
"""
if (isinstance(destination,tuple)):
sourceChartObject.Copy()
sht = self.xlbook.Worksheets(destination[0])
sht.Paste(sht.Range(destination[1]))
else:
sourceChartObject.Chart.ChartArea.Copy()
destination.Chart.Paste()
if (delete.upper() =="Y"):
sourceChartObject.Delete()

def hidecolumn(self, sheet, col):
"""Hide a column.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
col - column number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Columns(col).Hidden = True

def hiderow(self, sheet, row):
""" Hide a row.

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet.
row - row number (integer data)
"""
sht = self.xlbook.Worksheets(sheet)
sht.Rows(row).Hidden = True

def excelfunction(self, sheet, range, function):
"""Access Microsoft Excel worksheet functions. Refer
WorksheetFunction Object in Microsoft Excel Visual Basic Reference

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
range - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
For list of functions refer List of Worksheet Functions
Available to Visual Basic in Microsoft Excel Visual Basic Reference
"""
sht = self.xlbook.Worksheets(sheet)
if isinstance(range,str):
xlRange = "(sht.Range(" + "'" + range + "'" + "))"
elif isinstance(range,tuple):
topRow = range[0]
leftColumn = range[1]
bottomRow = range[2]
rightColumn = range[3]
xlRange = "(sht.Range(sht.Cells(topRow, leftColumn),
sht.Cells(bottomRow, rightColumn)))"
xlFunction = "self.xlapp.WorksheetFunction." + function +
xlRange
return eval(xlFunction, globals(), locals())

def clearrange(self, sheet, rangeAddress, contents="Y",
format="Y"):
"""Clear the contents of a range of cells.
Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
rangeAddress - tuple of integers (row1,col1,row2,col2) or
"cell1Address:cell2Address"
row1,col1 refers to first cell
row2,col2 refers to second cell
e.g. (1,2,5,7) or "B1:G5"
contents - "Y" or "N". If "Y" clears the formulas from the
range
format - "Y" or "N". If "Y" clears the formatting of the
object
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(rangeAddress,str)):
if (format.upper() == "Y"):
sht.Range(rangeAddress).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(rangeAddress).ClearContents()
elif (isinstance(rangeAddress,tuple)):
row1 = rangeAddress[0]
col1 = rangeAddress[1]
row2 = rangeAddress[2]
col2 = rangeAddress[3]
if (format.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearFormats()
if (contents.upper() == "Y"):
sht.Range(sht.Cells(row1, col1), sht.Cells(row2,
col2)).ClearContents()

def addcomment(self, sheet, cellAddress, comment=""):
"""Add or delete comment to a cell. If parameter comment is
None, delete the comments

Description of parameters (self explanatory parameters are not
described):
sheet - name of the excel worksheet
cellAddress - tuple of integers (row, cloumn) or string "ColumnRow"
e.g. (3,4) or "D3"
comment - String data. Comment to be added. If None, delete
comments
"""
sht = self.xlbook.Worksheets(sheet)
if (isinstance(cellAddress,str)):
if (comment != None):
sht.Range(cellAddress).AddComment(comment)
else:
sht.Range(cellAddress).ClearComments()
elif (isinstance(cellAddress,tuple)):
row1 = cellAddress[0]
col1 = cellAddress[1]
if (comment != None):
sht.Cells(row1, col1).AddComment(comment)
else:
sht.Cells(row1, col1).ClearComments()

def excelapp():
xlFile = UseExcel("e:\\python23\myfiles\\StudentTabulation.xls")
xlFile.show()
xlFile.setcell(sheet="Sheet1", value="Class X Annual
Examination",fontName="Arial",
cellAddress="D1",fontColor=1,
fontStyle=("Bold",), fontSize=16)
xlFile.setcell(sheet="Sheet1", value="Subject : History",
fontName="Arial",
cellAddress="D3",fontColor=1)
data = (
("Sl. No." ,"Name of Students", "Roll No.",
"Marks(out of 100)"),
(1 ,"John" ,1020, 52),
(2 ,"Nikhil" ,1021, 75),
(3 ,"Stefen" ,1025, 85),
(4 ,"Thomas" ,1026, 54),
(5 ,"Ali" ,1027, 87),
(6 ,"Sanjay" ,1028, 0)
)
(bottomRow, rightCol) = xlFile.setrange("Sheet1", 5,2,data)
xlFile.addcomment("sheet1", "C11", "Absent")
chrt1 = xlFile.insertchart("sheet2", 100, 100, 400, 200)
xlFile.plotdata(sheet="sheet1",dataRanges=((6,3,bottomRow,5),),
chartObject=chrt1,
title="Annual Examination : History", plotBy=2,
categoryLabels=1,
seriesLabels=0, chartType="Bar")
#~ xlFile.clearrange("sheet1",(3,2,3,5),"y")
#~ xlFile.addcomment("sheet1", "B4", "Test Comment")
#~ chrt1 = xlFile.insertchart("sheet1", 100, 100, 400, 250)
#~ xlFile.plotdata(sheet="sheet1",dataRange=(4,2,bottomRow,
rightCol), chartObject=chrt1,
#~ title="Test Chart", chartType="Column")
#~ xlFile.copyrange(("sheet1","C3:E3"), ("sheet2", "C3"))
#~ chrt2 = xlFile.insertchart("sheet2", 100, 100, 400, 250)
#~ xlFile.movechart(chrt1,chrt2)
#~ xlFile.copychart(chrt1,("sheet3","D22"), "y")
#~ xlFile.hiderow("sheet1",7)
#~ print xlFile.excelfunction("sheet1", (3,2,3,5), "Min")
#~ print xlFile.getrange("sheet1","A2","C3")
#~ xlFile.setcellfont("sheet1","Regular", "A1")
#~ cellVal1 = xlFile.getcell("sheet1","A1")
#~ xlFile.setcell("sheet1", cellVal1,1,3)
#~ xlFile.setcellfont("sheet1","bold","C1")
#~ xlFile.setcellfont("sheet1","italic",1,3)
#~ xlFile.setcellfont("sheet1","underline",1,3)
#~ xlFile.setcellalign("sheet1","left",1,3)
#~ print xlFile.getrange("sheet1", "D5", "F6")
#~ xlFile.setrange("sheet1", 10,10,
((45,67),(67,"342"),(88,66.8),(32,77),(3,3)))
#~ xlFile.addnewworksheetafter("sheet1", "Srijit1")

if (__name__ == "__main__"):
excelapp()


---------------------------------------------------------------------------

Hi,
I am working on Python-Excel interface based on the excellent library
win32com.
I was planning to put it up as a Python recipe in Activestate.
Anyway before that I shall personally send you the code after some
cleanup. It will be good to get an early feedback before I upload it
in Activestate.

Regards,
Srijit

 

[Bob Gailer]

Would appreciate any advice on how to get started with the script.
Created an empty spreadsheet "test.xls" on drive F. Then at the
PythonWin prompt tried and obtained:

What precedes this code> Where does UseExcel() come from?

Try "f:\\test.xls" (\t is the tab character and must be escaped)

Traceback (most recent call last):
File "<interactive input>", line 1, in ?
File "UseExcel.py", line 16, in __init__
File "<COMObject <unknown>>", line 2, in Open
com_error: (-2147352567, 'Exception occurred.', (0, 'Microsoft Excel',
"'f:\test.xls' could not be found. Check the spelling of the file
name, and verify that the file location is correct.\n\nIf you are
trying to open the file from your list of most recently used files on
the File menu, make sure that the file has not been renamed, moved, or
deleted.", 'C:\\Program Files\\Microsoft
Office\\Office10\\1033\\xlmain10.chm', 0, -2146827284), None)

[snip]

Bob Gailer
(e-mail address removed)
303 442 2625