目录
在Python中,对于Excel表的操作可以用xlrd
和xlwt
来做,一个用来读,一个用来写。
但后来,都使用openpyxl模块了...
关于excel表格,大家都不陌生了哈,这里它们在Python操作中的名词解释:
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files — openpyxl 3.0.9 documentation
openpyxl是Python的第三方库,专门用于读/写excel文件的,通常支持xlsx/xlsm/xltx/xltm
结尾的excel文件。
pip install openpyxl # pip install lxml # 创建大文件时会用到 # pip install pillow # 在excel中处理图片,会用到
假如我有这样的一个excel表格:
正在上传…重新上传取消
基础操作
from openpyxl import load_workbook wb = load_workbook('./xxx.xlsx') # 获取所有sheet # print(wb.sheetnames) # ['mysqld', 'mysqld_safe', 'client', 'Sheet'] # 获取已经存在的sheet # 基于sheet名称获取sheet对象 # sheet = wb['mysqld'] # print(sheet) # <Worksheet "mysqld"> # 基于索引获取sheet对象 sheet = wb.worksheets[0] # 获取指定单元格的对象,有以下两种方式获取 # cell = sheet.cell(1, 1) # 法1 # cell = sheet['A1'] # 法2 # 可以通过单元格对象来获取其内容、样式、字体等 # print(cell.value) # 获取单元格的内容 key # print(cell.alignment) # 对齐方式 https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.alignment.html?highlight=alignment # print(cell.font) # 字体 https://openpyxl.readthedocs.io/en/stable/api/openpyxl.styles.fonts.html?highlight=font#openpyxl.styles.fonts.Font # print(cell.style) # 字体样式 # 获取指定行 # print(sheet[1]) # (<Cell 'mysqld'.A1>, <Cell 'mysqld'.B1>) # print(list(map(lambda x: x.value, sheet[1]))) # ['key', 'value'] # 获取所有行 # print(sheet.rows) # 获取所有行,返回一个生成器 <generator object Worksheet._cells_by_row at 0x000001FBB9936DD0> # for row in sheet.rows: # # 获取每一行的内容 # print(list(map(lambda x: x.value, row))) # # 获取每行指定列的内容 # print(row[0].value, row[1].value) # 获取每行第一列、第二列内容 # 获取所有列 # print(sheet.columns) # for col in sheet.columns: # # 获取每一列的内容 # print(list(map(lambda x: x.value, col))) # # 以列的形式,获取指定行的内容 # print(col[0].value) # 获取第一行的数据 # 获取所有行,用的也比较多 # for row in sheet.iter_rows(): # print(row) # 也可以指定从哪行哪列循环到哪行哪列 # for row in sheet.iter_rows(min_row=3, min_col=3, max_row=5, max_col=5): # print(row) # print(row[0].row) # 返回当前行的行号
读取合并单元格
如果遇到合并的单元格的话,就需要注意了:
如有这样的一个表格:
正在上传…重新上传取消
from openpyxl import load_workbook wb = load_workbook('./xxx.xlsx') sheet = wb.worksheets[-1] # 先来看水平合并的单元格的特点 # 下面两个输出结果,非常正常,因为它是最左侧的单元格 # print(sheet['B1']) # <Cell 'Sheet'.B1> # print(sheet['B1'].value) # 出版书籍 # 被合并的单元格,它是merge cell,且值为None # print(sheet['C1'], sheet['C1'].value) # <MergedCell 'Sheet'.C1> None # print(sheet['D1'], sheet['D1'].value) # <MergedCell 'Sheet'.D1> None # 再来看垂直合并的单元格 # 先来看最上面的单元格,也是非常正常的 # print(sheet['A2']) # <Cell 'Sheet'.A2> # print(sheet['A2'].value) # 机械工业出版社 # 被合并的单元格,它是merge cell,且值为None # print(sheet['A3'], sheet['A3'].value) # <MergedCell 'Sheet'.A3> None # 在循环中也能看到这个现象 for row in sheet.rows: print(row) """ (<Cell 'Sheet'.A1>, <Cell 'Sheet'.B1>, <MergedCell 'Sheet'.C1>, <MergedCell 'Sheet'.D1>, <Cell 'Sheet'.E1>) (<Cell 'Sheet'.A2>, <Cell 'Sheet'.B2>, <Cell 'Sheet'.C2>, <Cell 'Sheet'.D2>, <Cell 'Sheet'.E2>) (<MergedCell 'Sheet'.A3>, <Cell 'Sheet'.B3>, <Cell 'Sheet'.C3>, <Cell 'Sheet'.D3>, <Cell 'Sheet'.E3>) (<Cell 'Sheet'.A4>, <Cell 'Sheet'.B4>, <Cell 'Sheet'.C4>, <Cell 'Sheet'.D4>, <Cell 'Sheet'.E4>) """
注意:在Windows平台,只要牵扯到保存修改的操作,都必须保证要操作的excel表格是关闭的,否则操作失败。提示权限问题: PermissionError: [Errno 13] Permission denied: 'xxx.xlsx'
写excel表格,就分为两种,文件存在,然后改写;文件不存在,打开文件写。
原excel表格写内容
from openpyxl import load_workbook file_path = './xxx.xlsx' wb = load_workbook(file_path) sheet = wb.worksheets[0] # 拿到单元格对象 cell = sheet['A1'] # 有了单元格对象,就可以一顿操作了,改内容、字体、边框..... cell.value = 'new_key' # 最后要保存修改 wb.save(file_path)
新建excel表格,写内容
from openpyxl.workbook import Workbook file_path = './x1.xlsx' # Workbook会自动创建一个excel文件,然后默认也会创建一个sheet,sheet名叫Sheet,注意,是Sheet不是sheet,这俩不是一回事 wb = Workbook() # 拿到sheet sheet = wb['Sheet'] # 拿到单元格对象 cell = sheet['A1'] # 有了单元格对象,就可以一顿操作了,改内容、字体、边框..... cell.value = 'new_key' # 最后要保存修改 wb.save(file_path)
在来研究下关于sheet和cell的更多细节操作。
sheet的操作
from openpyxl.workbook import Workbook file_path = './x1.xlsx' # Workbook会自动创建一个excel文件,然后默认也会创建一个sheet,sheet名叫Sheet,注意,是Sheet不是sheet,这俩不是一回事 wb = Workbook() sheet = wb['Sheet'] # 修改sheet名 sheet.title = "new_sheet" # 设置sheet颜色 sheet.sheet_properties.tabColor = "ffcc66" # rgb颜色:http://tools.jb51.net/static/colorpicker/ # 创建新的sheet # 0表示索引,相当于新创建的sheet插入到原有(多个)sheet中的那个位置,0就是新创建的sheet放在最左边 s2_sheet = wb.create_sheet('s2', 0) # 当你打开excel时,指定默认打开的sheet,默认是打开最左侧的sheet wb.active = 1 # 拷贝sheet s3_sheet = wb.copy_worksheet(wb['s2']) # sheet s2也必须存在 s3_sheet.title = 's3' # 删除sheet del wb['new_sheet'] # 不存在则报错 # 最后都要记得保存 wb.save(file_path)
cell操作
from openpyxl.workbook import Workbook from openpyxl import load_workbook from openpyxl.styles import Alignment, Border, Side, Font, PatternFill, GradientFill file_path = './x1.xlsx' # Workbook会自动创建一个excel文件,然后默认也会创建一个sheet,sheet名叫Sheet,注意,是Sheet不是sheet,这俩不是一回事 wb = load_workbook(file_path) sheet = wb['Sheet'] # 修改单元格的值 # 法1 # cell = sheet['A1'] # cell.value = '单元格A1' # 法2 # sheet['A1'] = '单元格A1' # for row in sheet['A1': 'B3']: # # 每一行的row,相当于有两个值,是元组类型 ('A1', 'B1') # row[0].value, row[1].value = 'x', 'y' """ 上面的for循环结果,表格中的效果如下 A B 1 x y 2 x y 3 x y """ # 对齐方式 # cell = sheet['A1'] # horizontal,水平方向对齐方式:"general", "left", "center", "right", "fill", "justify", "centerContinuous", "distributed" # vertical,垂直方向对齐方式:"top", "center", "bottom", "justify", "distributed" # text_rotation,旋转角度。 # wrap_text,是否自动换行。 # cell.alignment = Alignment(horizontal='center', vertical='distributed', text_rotation=45, wrap_text=True) # 边框 # side的style有如下:dashDot','dashDotDot', 'dashed','dotted','double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot','mediumDashed', 'slantDashDot', 'thick', 'thin' # cell = sheet['A1'] # cell.border = Border( # top=Side(style="thin", color="FFB6C1"), # bottom=Side(style="dashed", color="FFB6C1"), # left=Side(style="dashed", color="FFB6C1"), # right=Side(style="dashed", color="9932CC"), # diagonal=Side(style="thin", color="483D8B"), # 对角线 # diagonalUp=True, # 左下 ~ 右上 # diagonalDown=True # 左上 ~ 右下 # ) # 字体 # cell = sheet['A1'] # cell.font = Font(name="微软雅黑", size=45, color="ff0000", underline="single") # 背景色 # cell = sheet['A1'] # cell.fill = PatternFill("solid", fgColor="99ccff") # 渐变背景色 # cell = sheet['A1'] # cell.fill = GradientFill("linear", stop=("FFFFFF", "99ccff", "000000")) # stop:从左到右依次进行渐变 # 宽高设置,在sheet中,序号从1开始 # sheet.row_dimensions[1].height = 50 # sheet.column_dimensions["E"].width = 100 # 合并单元格 # sheet.merge_cells("B2:D8") # sheet.merge_cells(start_row=15, start_column=3, end_row=18, end_column=8) # 写入公式 # sheet["D2"] = "=B2*C2" # sheet["D3"] = "=SUM(B3,C3)" # 删除 # idx:删除行或者列的起始位置 # amount:总共删amount行/列,默认是1行/列 # sheet.delete_rows(idx=1, amount=2) # 从第一行开始,总共删除2行 # sheet.delete_cols(idx=1, amount=2) # 从第一列开始,总共删除2列 # 插入 # sheet.insert_rows(idx=1, amount=2) # 插入两行 # sheet.insert_cols(idx=1, amount=2) # 插入两列 # 移动 # 将指定范围内的单元格进行移动 # row 正值表示向下移动;负值表示向上移动 # col 正值表示向有移动;负值表示向左移动 # translate:True表示公式自动调整,默认为False # sheet.move_range("B1:D3", rows=3, cols=3, translate=True) # 将 B1到B3范围内的单元格整体向下移动3行向右移动3列 # 打印区域 # sheet.print_area = "A1:D8" # 打印时,每个页面都固定表头 # sheet.print_title_cols = 'A:D' # sheet.print_title_rows = '1:2' # 最后都要记得保存 wb.save(file_path)
pip install xlrd==1.2.0
PS:之前还没发现,截至到我编辑这段说明的时候,xlrd这个模块最新已经到了2.0.1版本,但最新版本跟我下面的示例中的代码不兼容,所以,大家如果不愿意研究最新的模块操作,就按照上面的下载指定版本,保证下面的示例能跑通。
sheet_by_index(0)
。sheet_by_name('自动化')
。import xlrd # 首先拿到book对象 book = xlrd.open_workbook('./a1.xlsx') # sheet_by_index = book.sheet_by_index(0) sheet_by_name = book.sheet_by_name('自动化')
rows = sheet_by_name.nrows cols = sheet_by_name.ncols
for row in range(rows): # 使用 row方法读取 # print(sheet_by_name.row(row)) # 也可以使用row_values读取 print(sheet_by_name.row_values(row))
for col in range(cols): # 下面两种方法都可以 # print(sheet_by_name.col(col)) print(sheet_by_name.col_values(col))
print(sheet_by_name.cell(0, 0)) print(sheet_by_name.cell_value(0, 0))
l = [] title = sheet_by_name.row_values(0) # print(title) for row in range(1, rows): l.append(dict(zip(title, sheet_by_name.row_values(row)))) print(l)