implicate_order wrote: > Greetings, > Here's an Excel class I use. I'm afraid I can't recall where I found the basic class. I have a vague recollection it is due to Mark Hammond, author of the win32com package. Might have been in win32com demos. (Whoever the original author is anyway, many thanks). I added a few methods, including XY plotting (you can probably tell by the change in coding style to that of a newb). Not very generic but you may find it useful, as the hardest part I found was discovering what the Excel specific methods etc where. The MSDN developer site for Excel is a big help. http://msdn.microsoft.com/developercenters/
import win32com.client from win32com.client import Dispatch, constants class ExcelWorkbook: """ An Excel workbook object""" def __init__(self, filename=None): # Use these commands in Python code to auto generate .py support for excel from win32com.client import gencache gencache.EnsureModule('{00020813-0000-0000-C000-000000000046}', 0, 1, 4) # start excel self.xlApp = Dispatch('Excel.Application') if filename and os.path.exists(filename): self.xlBook = self.xlApp.Workbooks.Open(filename) else: self.xlBook = self.xlApp.Workbooks.Add() self.filename = filename def save(self, newfilename=None): if newfilename: self.filename = newfilename self.xlBook.SaveAs(newfilename) else: self.xlBook.Save() def close(self): self.xlBook.Close(SaveChanges=0) del self.xlApp def show(self): self.xlApp.Visible = 1 def hide(self): self.xlApp.Visible = 0 def newSheet(self, sheet): try: # fails if sheet already exists self.xlBook.Sheets(sheet).Name == sheet except: self.xlSheet = self.xlBook.Worksheets.Add() self.xlSheet.Name = sheet def deleteSheet(self, sheet): try: # ignore if sheet doesn't exist self.xlBook.Sheets(sheet).Delete() except: pass def selectSheet(self, sheet): self.xlBook.Worksheets(sheet).Select() def getCell(self, sheet, row, col): "Get value of one cell" sht = self.xlBook.Worksheets(sheet) return sht.Cells(row, col).Value def setCell(self, sheet, row, col, value): "set value of one cell" sht = self.xlBook.Worksheets(sheet) sht.Cells(row, col).Value = value def getRange(self, sheet, row1, col1, row2, col2): "return a 2d array (i.e. tuple of tuples)" sht = self.xlBook.Worksheets(sheet) return sht.Range(sht.Cells(row1, col1), sht.Cells(row2, col2)).Value def setRange(self, sheet, topRow, leftCol, data): """insert a 2d array starting at given location. Works out the size needed for itself""" bottomRow = topRow + len(data) - 1 rightCol = leftCol + len(data[0]) - 1 sht = self.xlBook.Worksheets(sheet) sht.Range( sht.Cells(topRow, leftCol), sht.Cells(bottomRow, rightCol) ).Value = data def getContiguousRange(self, sheet, row, col): """Tracks down and across from top left cell until it encounters blank cells; returns the non-blank range. Looks at first row and column; blanks at bottom or right are OK and return None witin the array""" sht = self.xlBook.Worksheets(sheet) # find the bottom row bottom = row while sht.Cells(bottom + 1, col).Value not in [None, '']: bottom = bottom + 1 # right column right = col while sht.Cells(row, right + 1).Value not in [None, '']: right = right + 1 return sht.Range(sht.Cells(row, col), sht.Cells(bottom, right)).Value def fixStringsAndDates(self, aMatrix): # converts all unicode strings and times newmatrix = [] for row in aMatrix: newrow = [] for cell in row: if type(cell) is UnicodeType: newrow.append(str(cell)) elif type(cell) is TimeType: newrow.append(int(cell)) else: newrow.append(cell) newmatrix.append(tuple(newrow)) return newmatrix def convertRCToA1(self, R1C1): """ fromReferenceStyle = constants.xlR1C1, toReferenceStyle = constants.xlA1, toabsolute = constants.xlRelative) """ return self.xlApp.ConvertFormula(R1C1, constants.xlR1C1, constants.xlA1, constants.xlRelative) def insertFormulaInRange(self, sheet, row, col, len, formula): self.selectSheet(sheet) sht = self.xlBook.Worksheets(sheet) sht.Cells(row, col).FormulaR1C1 = formula fill_range = sht.Range(sht.Cells(row, col), sht.Cells(row+len-1, col)) start = self.convertRCToA1("R"+str(row)+"C"+str(col)) sht.Range(start).AutoFill(Destination=fill_range) def newChartInSheet(self, sheet, num = 1, left = 10, width = 600, top = 50, height = 450, type = 'xy'): if type == 'xy': chart_type = constants.xlXYScatter try: self.selectSheet(sheet) except: # sheet doesn't exist so create it self.newSheet(sheet) try : self.xlBook.Sheets(sheet).ChartObjects(num).Activate # already exists except: self.xlChart = self.xlBook.Sheets(sheet).ChartObjects().Add( Left = left, Width = width, Top = top, Height = height) self.xlChart.Chart.ChartType = chart_type def addXYChartSeries(self, sheet, topRow, bottomRow, xCol, yCol, series_name="", chart_sheet="", chart_num = 1, color = 1, style = 'line', title = "", xlabel = "", ylabel = "", errorbars = {}): if not chart_sheet: chart_sheet = sheet # series properties sht = self.xlBook.Worksheets(sheet) se = self.xlChart.Chart.SeriesCollection().NewSeries() se.Values = sht.Range(sht.Cells(topRow, yCol), sht.Cells(bottomRow, yCol)) se.XValues = sht.Range(sht.Cells(topRow, xCol), sht.Cells(bottomRow, xCol)) if series_name: se.Name = series_name if style == 'line': # line style se.MarkerStyle = constants.xlNone se.Border.ColorIndex = color se.Border.Weight = constants.xlHairline se.Border.LineStyle = constants.xlContinuous se.Border.Weight = constants.xlMedium if style == 'point': # point style #se.MarkerBackgroundColorIndex = constants.xlNone #se.MarkerForegroundColorIndex = color se.MarkerBackgroundColorIndex = color se.MarkerForegroundColorIndex = 1 # black #se.MarkerStyle = constants.xlMarkerStyleCircle se.MarkerStyle = constants.xlMarkerStyleSquare se.MarkerSize = 5 # Chart properties cht = self.xlBook.Sheets(chart_sheet).ChartObjects(chart_num).Chart # Chart Title if title: cht.HasTitle = True cht.ChartTitle.Caption = title cht.ChartTitle.Font.Name = 'Arial' cht.ChartTitle.Font.Size = 10 cht.ChartTitle.Font.Bold = False # X axis labels if xlabel: cht.Axes(constants.xlCategory).HasTitle = True cht.Axes(constants.xlCategory).AxisTitle.Caption = xlabel cht.Axes(constants.xlCategory).AxisTitle.Font.Name = 'Arial' cht.Axes(constants.xlCategory).AxisTitle.Font.Size = 10 cht.Axes(constants.xlCategory).AxisTitle.Font.Bold = False cht.Axes(constants.xlCategory).MinimumScale = 0 cht.Axes(constants.xlCategory).MaximumScaleIsAuto = True # Y axis labels if ylabel: cht.Axes(constants.xlValue).HasTitle = True cht.Axes(constants.xlValue).AxisTitle.Caption = ylabel cht.Axes(constants.xlValue).AxisTitle.Font.Name = 'Arial' cht.Axes(constants.xlValue).AxisTitle.Font.Size = 10 cht.Axes(constants.xlValue).AxisTitle.Font.Bold = False cht.Axes(constants.xlValue).MinimumScale = 0 cht.Axes(constants.xlValue).MaximumScaleIsAuto = True if errorbars: amount = "".join(["=", chart_sheet, "!", "R", str(errorbars['amount'][0]), "C", str(errorbars['amount'][2]), ":", "R", str(errorbars['amount'][1]), "C", str(errorbars['amount'][2])]) se.ErrorBar(Direction = constants.xlY, Include = constants.xlErrorBarIncludeBoth, Type = constants.xlErrorBarTypeCustom, Amount = amount, MinusValues = amount) se.ErrorBars.EndStyle = constants.xlNoCap se.ErrorBars.Border.LineStyle = constants.xlContinuous se.ErrorBars.Border.ColorIndex = color se.ErrorBars.Border.Weight = constants.xlHairline -- http://mail.python.org/mailman/listinfo/python-list