NinoCode/Tool_Box/ExcelLib.py

"""
ExcelLib

This will be an excel Library, making it easier to control Excel

"""

from win32com.client import Dispatch

class ExcelConnection:
    """A class that makes it easier to control Excel. Remember to save is your problem, as is error handling.
    Operates on one workbook at a time."""

    def __init__(self,fileName = None):
        self.xlApp = Dispatch('Excel.Application')
        #self.xlApp.Visible = True
        if fileName:
            self.fileName = fileName
            self.xlBook = self.xlApp.Workbooks.Open(fileName)
        else:
            self.xlBook = self.xlApp.Workbooks.Add()
            self.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 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 setCellNumberFormat(self, sheet, row, col, format):
        """Sets the format for the cell, for date and other number type fields"""
        sht = self.xlBook.Worksheets(sheet)
        sht.Cells(row,col).NumberFormat = format

    def setCellProperties(self, sheet, row, col, font = None, fontSize = None, cellColor = None):
        """A catch all for changing the properties of a cell.  I can keep adding to this to avoid too many methods"""
        sht = self.xlBook.Worksheets(sheet)
        #sht.Cells(1,"A").AutoFilter(Field = 1, VisibleDropDown= False)
        #sht.Range("A1:B1").AutoFilter(Field = 2, VisibleDropDown= False)
        if font:
            pass
        if fontSize:
            sht.Cells(row,col).Font.Size = fontSize
        if cellColor:
            sht.Cells(row,col).Interior.ColorIndex = cellColor

    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, leftCol, topRow, data, sheetRenameValue = None):
        """insert a 2d array starting at given location.
        works out the size needed for itself. optionally you can rename the sheet."""
        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
        if sheetRenameValue:
            sht.Name = sheetRenameValue

    def getContiguousRange(self, sheet, row, col):
        """Tracks down and across from top left cell until it
        encounters blank cells; returns the non-blank range.
        Loost at first row and column; blanks at bottom or right
        are OK and return None within 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.apppend(str(cell))
                elif type(cell) is TimeType:
                    newRow.append(init(cel))
                else:
                    newRow.append(cell)
            newMatrix.append(tuple(newRow))
        return newMatrix

    def deleteColumnByLetter(self, sheet, colLetter):
        """Deletes a single entire column"""
        sht = self.xlBook.Worksheets(sheet)
        sucessCode = sht.Columns(colLetter).EntireColumn.Delete()

    def deleteColumnsByLetterList(self, sheet, colLetterList):
        """Deletes multiple columns or colume ranges wiht a list"""
        ##  Example sheet.Range("D:E, H:H, J:K").EntireColumn.Delete
        ##  Above gives an error, so I'm resorting to using python to loop
        pass

    def removeHeaderFilterArrows(self, sheet, row, colStart, colEnd):
        """Removes those filter arrows for all cells in a specified row."""
        sht = self.xlBook.Worksheets(sheet)
        for c in range(colStart,colEnd+1):
            sht.Cells(1).AutoFilter(Field = c, VisibleDropDown= False)
            
        #sht.Range("A1:B1").AutoFilter(Field = 2, VisibleDropDown= False)

    def forceAutoFitRow(self, sheet, colStart, colEnd):
        """Takes a colstart and colend and then performs an autofit on those columns."""
        sht = self.xlBook.Worksheets(sheet)
        sht.Columns("%s:%s"%(colStart, colEnd)).AutoFit()
Revision:	707
Committed:	Wed Jul 15 15:19:04 2020 UTC (5 years, 8 months ago) by nino.borges
Content type:	text/x-python
File size:	5325 byte(s)
Log Message:	Added support to set the cell color to the setCellProperties method.
#	User	Rev	Content
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	nino.borges	690	#self.xlApp.Visible = True
17	nino.borges	568	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	nino.borges	649
40	nino.borges	568	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	nino.borges	679	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	nino.borges	707	def setCellProperties(self, sheet, row, col, font = None, fontSize = None, cellColor = None):
51	nino.borges	682	"""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	nino.borges	707	if cellColor:
60			sht.Cells(row,col).Interior.ColorIndex = cellColor
61	nino.borges	682
62	nino.borges	568	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	nino.borges	649	def setRange(self, sheet, leftCol, topRow, data, sheetRenameValue = None):
68	nino.borges	568	"""insert a 2d array starting at given location.
69	nino.borges	649	works out the size needed for itself. optionally you can rename the sheet."""
70			bottomRow = topRow + len(data)-1
71	nino.borges	568	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	nino.borges	649	if sheetRenameValue:
77			sht.Name = sheetRenameValue
78	nino.borges	568
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	nino.borges	679	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	nino.borges	686	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	nino.borges	690
130	nino.borges	686	#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()