| 1 |
nino.borges |
568 |
"""
|
| 2 |
|
|
ExcelLib
|
| 3 |
|
|
|
| 4 |
|
|
This will be an excel Library, making it easier to control Excel
|
| 5 |
|
|
|
| 6 |
|
|
"""
|
| 7 |
|
|
|
| 8 |
|
|
from win32com.client import Dispatch
|
| 9 |
|
|
|
| 10 |
|
|
class ExcelConnection:
|
| 11 |
|
|
"""A class that makes it easier to control Excel. Remember to save is your problem, as is error handling.
|
| 12 |
|
|
Operates on one workbook at a time."""
|
| 13 |
|
|
|
| 14 |
|
|
def __init__(self,fileName = None):
|
| 15 |
|
|
self.xlApp = Dispatch('Excel.Application')
|
| 16 |
|
|
if fileName:
|
| 17 |
|
|
self.fileName = fileName
|
| 18 |
|
|
self.xlBook = self.xlApp.Workbooks.Open(fileName)
|
| 19 |
|
|
else:
|
| 20 |
|
|
self.xlBook = self.xlApp.Workbooks.Add()
|
| 21 |
|
|
self.fileName = ''
|
| 22 |
|
|
|
| 23 |
|
|
def save(self, newFileName = None):
|
| 24 |
|
|
if newFileName:
|
| 25 |
|
|
self.fileName = newFileName
|
| 26 |
|
|
self.xlBook.SaveAs(newFileName)
|
| 27 |
|
|
else:
|
| 28 |
|
|
self.xlBook.Save()
|
| 29 |
|
|
|
| 30 |
|
|
def close(self):
|
| 31 |
|
|
self.xlBook.Close(SaveChanges=0)
|
| 32 |
|
|
del self.xlApp
|
| 33 |
|
|
|
| 34 |
|
|
def getCell(self, sheet, row, col):
|
| 35 |
|
|
"Get Value of one Cell"
|
| 36 |
|
|
sht = self.xlBook.Worksheets(sheet)
|
| 37 |
|
|
return sht.Cells(row,col).Value
|
| 38 |
|
|
def setCell(self, sheet, row, col, value):
|
| 39 |
|
|
"set value of one cell"
|
| 40 |
|
|
sht = self.xlBook.Worksheets(sheet)
|
| 41 |
|
|
sht.Cells(row,col).Value = value
|
| 42 |
|
|
|
| 43 |
|
|
def getRange(self,sheet,row1,col1,row2,col2):
|
| 44 |
|
|
"return a 2d array (i.e. tuple of tuples)"
|
| 45 |
|
|
sht = self.xlBook.Worksheets(sheet)
|
| 46 |
|
|
return sht.Range(sht.Cells(row1,col1), sht.Cells(row2,col2)).Value
|
| 47 |
|
|
|
| 48 |
|
|
def setRange(self, sheet, leftCol, topRow, data):
|
| 49 |
|
|
"""insert a 2d array starting at given location.
|
| 50 |
|
|
works out the size needed for itself"""
|
| 51 |
|
|
bottomRow = topRod + len(data)-1
|
| 52 |
|
|
rightCol = leftCol + len(data[0]) -1
|
| 53 |
|
|
sht = self.xlBook.Worksheets(sheet)
|
| 54 |
|
|
sht.Range(
|
| 55 |
|
|
sht.Cells(topRow, leftCol),
|
| 56 |
|
|
sht.Cells(bottomRow, rightCol)).Value = data
|
| 57 |
|
|
|
| 58 |
|
|
def getContiguousRange(self, sheet, row, col):
|
| 59 |
|
|
"""Tracks down and across from top left cell until it
|
| 60 |
|
|
encounters blank cells; returns the non-blank range.
|
| 61 |
|
|
Loost at first row and column; blanks at bottom or right
|
| 62 |
|
|
are OK and return None within the array."""
|
| 63 |
|
|
sht = self.xlBook.Worksheets(sheet)
|
| 64 |
|
|
|
| 65 |
|
|
# find the bottom row
|
| 66 |
|
|
bottom = row
|
| 67 |
|
|
while sht.Cells(bottom + 1, col).Value not in [None, '']:
|
| 68 |
|
|
bottom = bottom +1
|
| 69 |
|
|
|
| 70 |
|
|
# right column
|
| 71 |
|
|
right = col
|
| 72 |
|
|
while sht.Cells(row, right + 1).Value not in [None,""]:
|
| 73 |
|
|
right = right +1
|
| 74 |
|
|
|
| 75 |
|
|
return sht.Range(sht.Cells(row,col), sht.Cells(bottom, right)).Value
|
| 76 |
|
|
|
| 77 |
|
|
def fixStringsAndDates(self,aMatrix):
|
| 78 |
|
|
# converts all unicode strings and times
|
| 79 |
|
|
newMatrix = []
|
| 80 |
|
|
for row in aMatrix:
|
| 81 |
|
|
newRow = []
|
| 82 |
|
|
for cell in row:
|
| 83 |
|
|
if type(cell) is UnicodeType:
|
| 84 |
|
|
newRow.apppend(str(cell))
|
| 85 |
|
|
elif type(cell) is TimeType:
|
| 86 |
|
|
newRow.append(init(cel))
|
| 87 |
|
|
else:
|
| 88 |
|
|
newRow.append(cell)
|
| 89 |
|
|
newMatrix.append(tuple(newRow))
|
| 90 |
|
|
return newMatrix |
