openpyxl 2.4.7版本,版本号不同使用方法略有差异。
安装模块
pip install openpyxl
创建文档
wb = openpyxl.Workbook()
打开文档
import openpyxl
wb = openpyxl.load_workbook(path)
打开工作表
sheet = wb.get_active_sheet() # 获取活动表,即打开时显示的表
wb.get_active_sheet() # 获得包含所有表名的列表
wb.title() # 当前表名
sheet = wb.get_sheet_by_name('<sheet_name>') # 根据表名打开
sheet = wb.create_sheet(<pos>) # 创建新表,参数为表的位置,默认在最后插入
获得单元格
- 根据索引
c = sheet['A1']
- sheet的方法
c = sheet.cell['A1']
- 根据行号、列号
c = sheet.cell(row={}, column={})
获得多个单元格
- 使用切片
for row in sheet['A1':'D2']:
# 外层for遍历每一行
for cellObj in row:
# 内层for遍历改行每一列
print(cellObj.value)
- 获得所有行
sheet.rows的方法获得的是一个生成器,需要通过遍历获得所有行。
列表中每一项为一个元组,元组每一项改行的一个单元格。
rows = []
for row in sheet.rows:
rows.append(row)
- 获得所有列
columns = []
for column in sheet.columns:
columns.append(column)
修改数据
- 通过获取的单元格
c.value = 1
- 直接根据索引修改
sheet['A1'] =1
- 给当前表格新增一行数据
# 获取表格所有行
rows = []
for row in sheet.rows:
rows.append(row)
new_line = len(rows) + 1
cell = sheet.cell(row=new_line, column=1)
cell.value = 9
保存修改
wb.save(path) # 名称重复会覆盖
Excel和数据库的交互
def excel2database(path):
"""
读取excel表格文件,写入数据库。
需要保证excel文件表头和数据库中列名称一致。
"""
db = db_handle()
# 打开一个现有表格文件
wb = openpyxl.load_workbook(path)
sheet = wb.get_active_sheet()
# 获取表格所有行
rows = []
for row in sheet.rows:
rows.append(row)
# 获取表头
header_row = rows[0]
header = [h.value for h in header_row]
# 从第二行开始读取表格数据,添加到已建立好的数据库中
for row in rows[1:]:
value = [v.value for v in row]
with db as cur:
# 转换从excel表格中提取出的表头名的格式,以便于插入SQL语句 ['a','b','c'] --> a,b,c
columns_name = str(header)[1:-1].replace("'", "")
sql = 'insert into {}({}) values{}'.format(table, columns_name, tuple(value))
cur.execute(sql)
wb.save(path)
def database2excel(path):
"""
从数据库中读出数据写入excel表格。
表格首行根据数据库列名称生成表头。
"""
db = db_handle()
# 创建一个新的表格实例
wb = openpyxl.Workbook()
sheet = wb.active
# 在首行建立表头
cur = db.cursor()
sql = "select column_name from information_schema.columns where table_name = '{}'".format(table)
cur.execute(sql)
columns = cur.fetchall()
for column in range(len(columns) - 1):
sheet.cell(row=1, column=column+1).value = columns[column + 1][0]
# sheet['A1'] = 'name'
# sheet['B1'] = 'time'
# sheet['C1'] = 'subject'
# sheet['D1'] = 'count'
# 从第二行开始添加数据
with db as cur:
sql = 'select * from {}'.format(table)
cur.execute(sql)
teachers = cur.fetchall()
row = 2
for teacher in teachers:
data_row = [data for data in teacher[1:]]
for column in range(len(columns) - 1):
cell = sheet.cell(row=row, column=column+1)
cell.value = data_row[column]
row += 1
# 保存
wb.save(path)
网友评论