#!/usr/bin/env python
# _*_ coding: utf-8 _*_
# @author : otfsenter
# @time : 2017/4/20 22:50
import win32com
class TimeType(basestring):
def __init__(self):
pass
class easyExcel:
"""A utility to make it easier to get at Excel. Remembering
to save the data is your problem, as is error handling.
Operates on one workbook at a time."""
def __init__(self, filename=None):
self.xlApp = win32com.client.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.Worksheet(sheet)
return sht.Range(sht.Cells(row1, col1), sht.Cells
(row2, col2)).Value
def setRange(self, sheet, leftCol, topRow, 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 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 unicode:
newrow.append(str(cell))
# check whether the type of the cell is time type
elif type(cell) is TimeType: # TimeType
newrow.append(int(cell))
else:
newrow.append(cell)
newmatrix.append(tuple(newrow))
return newmatrix
if __name__ == '__main__':
xls = easyExcel('test.xlsx')
xls.xlBook.
xls.setCell()
