Open MS Excel Spreadsheet with Python
Ajith Prasad
ofnap at nus.edu.sg
Fri Sep 12 10:16:38 EDT 2003
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:
>>> xlapp=UseExcel("f:\test.xls")
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.
srijit at yahoo.com wrote in message news:<221d8dbe.0308150959.b18167c at posting.google.com>...
> 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