基于openpyxl封装的模块,用于Excel表格的读写,最新版本
"""
基于openpyxl的excel读写模块
提供更简单的读写方式
"""
from openpyxl import load_workbook, Workbook
class WorkExcel:
def __init__(self, filePath, dataOnly=True):
"""
加载excel,如果没有将创建,默认选择第一个工作表
:param filePath: str: 文件地址
"""
self.filePath = filePath
try:
# 加载excel
self.excel = load_workbook(self.filePath, data_only=dataOnly)
except FileNotFoundError:
# 创建excel
self.excel = Workbook()
# 创建sheet
self.createSheet('Sheet1')
# sheet设置为第一个工作表
self.sheet = self.excel.active
def save(self):
"""
保存并退出
:return:
"""
self.excel.save(self.filePath)
def close(self):
"""
直接退出
:return:
"""
self.excel.close()
def createSheet(self, sheetName):
"""
创建工作表
:param sheetName: str: 工作表名称
:return:
"""
self.excel.create_sheet(sheetName)
def getSheetTitle(self):
"""
获取当前工作表名称
:return: str: 工作表名称
"""
return self.sheet.title
def getSheetTitles(self):
"""
获取excel所有工作表名称
:return: list: [工作表名称]
"""
return self.excel.sheetnames
def delSheet(self):
"""
删除当前工作表
:return:
"""
self.excel.remove(self.sheet)
def selectSheet(self, sheetName):
"""
选择工作表, 如果没有将创建
:param sheetName: str: 工作表名
:return:
"""
if sheetName in self.getSheetTitles():
# 选择工作表
self.sheet = self.excel[sheetName]
else:
# 创建工作表
self.createSheet(sheetName)
# 选择工作表
self.sheet = self.excel[sheetName]
def setCell(self, r, c, var):
"""
修改指定行, 列的单元格内容
:param r: int: 行数
:param c: int: 列数
:param var: str: 修改内容
:return:
"""
self.sheet.cell(row=r, column=c, value=var)
def getCell(self, r, c):
"""
获取指定行, 列的单元格内容
:param r: int: 行数
:param c: int: 列数
:return: str: 单元格内容
"""
return self.sheet.cell(row=r, column=c).value
def getRow(self, r):
"""
获取指定行所有数据
:param r: int: 行数
:return: tuple: [数据]
"""
data = []
for cell in self.sheet.iter_rows(min_row=r, max_row=r, values_only=True):
for var in cell:
if var is not None:
data.append(var)
return data
def getColumn(self, c):
"""
获取指定列所有数据
:param c: int: 列数
:return: tuple: [数据]
"""
data = []
for cell in self.sheet.iter_cols(min_col=c, max_col=c, values_only=True):
for var in cell:
if var is not None:
data.append(var)
return data
def getRows(self):
"""
以行分组获取所有数据
:return: list: [[数据]]
"""
data = []
for rows in self.sheet.rows:
tempList = []
for cell in rows:
if cell.value is not None:
tempList.append(cell.value)
data.append(tempList)
return data
def getColumns(self):
"""
以列分组获取所有数据
:return: list: [[数据]]
"""
data = []
for cols in self.sheet.columns:
tempList = []
for cell in cols:
if cell.value is not None:
tempList.append(cell.value)
data.append(tempList)
return data