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')
        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 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
Revision:	649
Committed:	Thu Dec 12 20:42:20 2019 UTC (6 years, 3 months ago) by nino.borges
Content type:	text/x-python
File size:	3274 byte(s)
Log Message:	small changes
#	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			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	nino.borges	649
39	nino.borges	568	def setCell(self, sheet, row, col, value):
40			"set value of one cell"
41			sht = self.xlBook.Worksheets(sheet)
42			sht.Cells(row,col).Value = value
43
44			def getRange(self,sheet,row1,col1,row2,col2):
45			"return a 2d array (i.e. tuple of tuples)"
46			sht = self.xlBook.Worksheets(sheet)
47			return sht.Range(sht.Cells(row1,col1), sht.Cells(row2,col2)).Value
48
49	nino.borges	649	def setRange(self, sheet, leftCol, topRow, data, sheetRenameValue = None):
50	nino.borges	568	"""insert a 2d array starting at given location.
51	nino.borges	649	works out the size needed for itself. optionally you can rename the sheet."""
52			bottomRow = topRow + len(data)-1
53	nino.borges	568	rightCol = leftCol + len(data[0]) -1
54			sht = self.xlBook.Worksheets(sheet)
55			sht.Range(
56			sht.Cells(topRow, leftCol),
57			sht.Cells(bottomRow, rightCol)).Value = data
58	nino.borges	649	if sheetRenameValue:
59			sht.Name = sheetRenameValue
60	nino.borges	568
61			def getContiguousRange(self, sheet, row, col):
62			"""Tracks down and across from top left cell until it
63			encounters blank cells; returns the non-blank range.
64			Loost at first row and column; blanks at bottom or right
65			are OK and return None within the array."""
66			sht = self.xlBook.Worksheets(sheet)
67
68			# find the bottom row
69			bottom = row
70			while sht.Cells(bottom + 1, col).Value not in [None, '']:
71			bottom = bottom +1
72
73			# right column
74			right = col
75			while sht.Cells(row, right + 1).Value not in [None,""]:
76			right = right +1
77
78			return sht.Range(sht.Cells(row,col), sht.Cells(bottom, right)).Value
79
80			def fixStringsAndDates(self,aMatrix):
81			# converts all unicode strings and times
82			newMatrix = []
83			for row in aMatrix:
84			newRow = []
85			for cell in row:
86			if type(cell) is UnicodeType:
87			newRow.apppend(str(cell))
88			elif type(cell) is TimeType:
89			newRow.append(init(cel))
90			else:
91			newRow.append(cell)
92			newMatrix.append(tuple(newRow))
93			return newMatrix