Open MS Excel Spreadsheet with Python
srijit at yahoo.com
srijit at yahoo.com
Fri Aug 15 13:59:19 EDT 2003
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()
---------------------------------------------------------------------------
srijit at yahoo.com wrote in message news:<221d8dbe.0307160619.2c2943c3 at posting.google.com>...
> 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
>
> "Allison Bailey" <allisonb at terralogicgis.com> wrote in message news:<mailman.1058229111.12248.python-list at python.org>...
> > 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.
> > allisonb at terralogicgis.com
> > 425-673-4495
> > ~~~~~~~~~~~~~~~~~~~~~~~~~~~~
More information about the Python-list
mailing list