| 1 |
"""
|
| 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 |
#self.xlApp.Visible = True
|
| 17 |
if fileName:
|
| 18 |
self.fileName = fileName
|
| 19 |
self.xlBook = self.xlApp.Workbooks.Open(fileName)
|
| 20 |
else:
|
| 21 |
self.xlBook = self.xlApp.Workbooks.Add()
|
| 22 |
self.fileName = ''
|
| 23 |
|
| 24 |
def save(self, newFileName = None):
|
| 25 |
if newFileName:
|
| 26 |
self.fileName = newFileName
|
| 27 |
self.xlBook.SaveAs(newFileName)
|
| 28 |
else:
|
| 29 |
self.xlBook.Save()
|
| 30 |
|
| 31 |
def close(self):
|
| 32 |
self.xlBook.Close(SaveChanges=0)
|
| 33 |
del self.xlApp
|
| 34 |
|
| 35 |
def getCell(self, sheet, row, col):
|
| 36 |
"Get Value of one Cell"
|
| 37 |
sht = self.xlBook.Worksheets(sheet)
|
| 38 |
return sht.Cells(row,col).Value
|
| 39 |
|
| 40 |
def setCell(self, sheet, row, col, value):
|
| 41 |
"set value of one cell"
|
| 42 |
sht = self.xlBook.Worksheets(sheet)
|
| 43 |
sht.Cells(row,col).Value = value
|
| 44 |
|
| 45 |
def setCellNumberFormat(self, sheet, row, col, format):
|
| 46 |
"""Sets the format for the cell, for date and other number type fields"""
|
| 47 |
sht = self.xlBook.Worksheets(sheet)
|
| 48 |
sht.Cells(row,col).NumberFormat = format
|
| 49 |
|
| 50 |
def setCellProperties(self, sheet, row, col, font = None, fontSize = None, cellColor = None):
|
| 51 |
"""A catch all for changing the properties of a cell. I can keep adding to this to avoid too many methods"""
|
| 52 |
sht = self.xlBook.Worksheets(sheet)
|
| 53 |
#sht.Cells(1,"A").AutoFilter(Field = 1, VisibleDropDown= False)
|
| 54 |
#sht.Range("A1:B1").AutoFilter(Field = 2, VisibleDropDown= False)
|
| 55 |
if font:
|
| 56 |
pass
|
| 57 |
if fontSize:
|
| 58 |
sht.Cells(row,col).Font.Size = fontSize
|
| 59 |
if cellColor:
|
| 60 |
sht.Cells(row,col).Interior.ColorIndex = cellColor
|
| 61 |
|
| 62 |
def getRange(self,sheet,row1,col1,row2,col2):
|
| 63 |
"return a 2d array (i.e. tuple of tuples)"
|
| 64 |
sht = self.xlBook.Worksheets(sheet)
|
| 65 |
return sht.Range(sht.Cells(row1,col1), sht.Cells(row2,col2)).Value
|
| 66 |
|
| 67 |
def setRange(self, sheet, leftCol, topRow, data, sheetRenameValue = None):
|
| 68 |
"""insert a 2d array starting at given location.
|
| 69 |
works out the size needed for itself. optionally you can rename the sheet."""
|
| 70 |
bottomRow = topRow + len(data)-1
|
| 71 |
rightCol = leftCol + len(data[0]) -1
|
| 72 |
sht = self.xlBook.Worksheets(sheet)
|
| 73 |
sht.Range(
|
| 74 |
sht.Cells(topRow, leftCol),
|
| 75 |
sht.Cells(bottomRow, rightCol)).Value = data
|
| 76 |
if sheetRenameValue:
|
| 77 |
sht.Name = sheetRenameValue
|
| 78 |
|
| 79 |
def getContiguousRange(self, sheet, row, col):
|
| 80 |
"""Tracks down and across from top left cell until it
|
| 81 |
encounters blank cells; returns the non-blank range.
|
| 82 |
Loost at first row and column; blanks at bottom or right
|
| 83 |
are OK and return None within the array."""
|
| 84 |
sht = self.xlBook.Worksheets(sheet)
|
| 85 |
|
| 86 |
# find the bottom row
|
| 87 |
bottom = row
|
| 88 |
while sht.Cells(bottom + 1, col).Value not in [None, '']:
|
| 89 |
bottom = bottom +1
|
| 90 |
|
| 91 |
# right column
|
| 92 |
right = col
|
| 93 |
while sht.Cells(row, right + 1).Value not in [None,""]:
|
| 94 |
right = right +1
|
| 95 |
|
| 96 |
return sht.Range(sht.Cells(row,col), sht.Cells(bottom, right)).Value
|
| 97 |
|
| 98 |
def fixStringsAndDates(self,aMatrix):
|
| 99 |
# converts all unicode strings and times
|
| 100 |
newMatrix = []
|
| 101 |
for row in aMatrix:
|
| 102 |
newRow = []
|
| 103 |
for cell in row:
|
| 104 |
if type(cell) is UnicodeType:
|
| 105 |
newRow.apppend(str(cell))
|
| 106 |
elif type(cell) is TimeType:
|
| 107 |
newRow.append(init(cel))
|
| 108 |
else:
|
| 109 |
newRow.append(cell)
|
| 110 |
newMatrix.append(tuple(newRow))
|
| 111 |
return newMatrix
|
| 112 |
|
| 113 |
def deleteColumnByLetter(self, sheet, colLetter):
|
| 114 |
"""Deletes a single entire column"""
|
| 115 |
sht = self.xlBook.Worksheets(sheet)
|
| 116 |
sucessCode = sht.Columns(colLetter).EntireColumn.Delete()
|
| 117 |
|
| 118 |
def deleteColumnsByLetterList(self, sheet, colLetterList):
|
| 119 |
"""Deletes multiple columns or colume ranges wiht a list"""
|
| 120 |
## Example sheet.Range("D:E, H:H, J:K").EntireColumn.Delete
|
| 121 |
## Above gives an error, so I'm resorting to using python to loop
|
| 122 |
pass
|
| 123 |
|
| 124 |
def removeHeaderFilterArrows(self, sheet, row, colStart, colEnd):
|
| 125 |
"""Removes those filter arrows for all cells in a specified row."""
|
| 126 |
sht = self.xlBook.Worksheets(sheet)
|
| 127 |
for c in range(colStart,colEnd+1):
|
| 128 |
sht.Cells(1).AutoFilter(Field = c, VisibleDropDown= False)
|
| 129 |
|
| 130 |
#sht.Range("A1:B1").AutoFilter(Field = 2, VisibleDropDown= False)
|
| 131 |
|
| 132 |
def forceAutoFitRow(self, sheet, colStart, colEnd):
|
| 133 |
"""Takes a colstart and colend and then performs an autofit on those columns."""
|
| 134 |
sht = self.xlBook.Worksheets(sheet)
|
| 135 |
sht.Columns("%s:%s"%(colStart, colEnd)).AutoFit() |
