用python对excel文件进行一般的处理，十几行代码就能做很多事了。
xlrd和xlwt这两个库可以操作excel，其中xlrd是读excel，xlwt是写excel的库，但是相比而言我还是更喜欢大一统的openpyxl。
openpyxl可以很方便的读写excel文件，包括单元格样式，合并等，这里只是简单写个demo入一下门。话不多说，直接上代码。
待读取的excel文件内容如下：
image.png
# -*- coding: UTF-8 -*-
import os
import openpyxl
def read_excel(filepath, attrOpt=None, rownumOfHeader=1):
"""
读取excel数据
:param filepath: excel文件路径
:param attrOpt: 属性字典，key为属性名称，value为列序号（从1开始）；对于每行数据会根据其序号赋值给对应的属性名，封装成一个字典类型；不传或传空则是一个有序数组
:param rownumOfHeader: 表头占用的行数，数据读取会跳过表头，默认表头占1行
:return:
"""
wb = openpyxl.load_workbook(filepath)
ws = wb.active
dataArr = []
rownumStart = rownumOfHeader
for r in range(rownumStart, ws.max_row):
if attrOpt is None or len(attrOpt) == 0:
rowData = []
for c in range(ws.max_column):
rowData.append(ws.cell(r + 1, c + 1).value)
dataArr.append(rowData)
else:
rowData = {}
for attrName, index in attrOpt.items():
rowData[attrName] = ws.cell(r + 1, index).value
dataArr.append(rowData)
return dataArr
def write_excel(filepath, dataArr):
"""
写excel
:param filepath: 文件路径
:param dataArr: 数据集
:return:
"""
wb = openpyxl.Workbook()
ws = wb.worksheets[0]
for i in range(0, len(dataArr)):
rowData = dataArr[i]
if rowData is not None and len(rowData) > 0:
for j in range(0, len(rowData)):
ws.cell(i + 1, j + 1).value = rowData[j]
wb.save(filepath)
def append_rows(filepath, dataArr):
"""
追加数据行，若文件不存在则创建并写入
:param filepath: 文件路径
:param dataArr: 数据集
:return:
"""
if not os.path.exists(filepath):
write_excel(filepath, dataArr)
return
wb = openpyxl.load_workbook(filepath)
ws = wb.worksheets[0]
maxRow = ws.max_row
for i in range(0, len(dataArr)):
rowData = dataArr[i]
if rowData is not None and len(rowData) > 0:
for j in range(0, len(rowData)):
ws.cell(maxRow + i + 1, j + 1).value = rowData[j]
wb.save(filepath)
if __name__ == '__main__':
attrs = {'id': 1, 'username': 2, 'email': 3, 'mobile': 4, 'registerTime': 5}
dataArr = read_excel('E:/test/demo.xlsx', attrs)
print(dataArr)
datas = [['123', '张三三', 'zhangsansan@qq.com'], ['124', '李思思', 'lisisi@qq.com']]
write_excel('E:/test/writedemo.xlsx', datas)
上述代码执行之后，读取后的数据如下：
[{'id': 111, 'username': '张三', 'email': 'zhangshan@abc.com', 'mobile': 13111121113, 'registerTime': datetime.datetime(2019, 3, 15, 12, 15, 12, 4)}, {'id': 112, 'username': '李四', 'email': 'lisi@abc.com', 'mobile': 15912535642, 'registerTime': datetime.datetime(2019, 3, 16, 12, 15, 12, 4)}, {'id': 113, 'username': '王五', 'email': 'wangwu@abc.com', 'mobile': 15841415252, 'registerTime': datetime.datetime(2019, 3, 17, 12, 15, 12, 4)}, {'id': 114, 'username': '赵六', 'email': 'zhaoliu@abc.com', 'mobile': 17712453698, 'registerTime': datetime.datetime(2019, 3, 18, 12, 15, 12, 4)}]
