Python操作Excel文件指南 --- Openpyxl
Top
目录
python操作excel有很多库,读写能力如下
包 | xls读 | xlsx读 | xls写 | xlsx写 | 备注 |
---|---|---|---|---|---|
xlrd | ✅ | ✅ | |||
xlwt | ✅ | ||||
openpyxl | ✅ | ✅ | |||
XlsxWriter | ✅ | ||||
xlutils | ✅ | ✅ | ✅ | 需xlrd/xlwt配合 | |
pandas | ✅ | ✅ | ✅ | ✅ | 需xlrd/xlwt/openpyxl/xlsxwriter配合 |
库 ---- openpyxl -- Note
# 对象及其含义
Workbook: Excel全体
Worksheet: 单个sheet
Cell: 单个单元格
# 格式化打印 单元格数据
pprint.pprint(bk["A2:C4"])
pprint.pprint(list(g_all), width=40)
# 注意:用户正在查看的表定义为激活的工作表(active sheet)。每个工作表都有行和列; 行号从 1 开始,列号从 'A' 开始
一个工作表由单元格(cell)组成,cell只存储两种数据类型,数字和字符串(除了纯数字,其它均为字符串类型)
当单元格中的值为None的时候,对应cell中不需要输入任何值,空读取出来就是None
# pip install openpyxl
# 想要在文件中插入图片文件,需要安装pillow,安装文件:PIL-fork-1.1.7.win-amd64-py2.7.exe
· font(字体类):字号、字体颜色、下划线等
· fill(填充类):颜色等
· border(边框类):设置单元格边框
· alignment(位置类):对齐方式
· number_format(格式类):数据格式
· protection(保护类):写保护
导入 已有的 xlsx 类型 Excel 表格文件
from openpyxl import load_workbook
file_path = os.path.join(os.path.dirname(os.path.abspath(__file__)), "文件名称.xlsx")
book = load_workbook(file_path)
查看 Excel 文件对象
book=openpyxl.Workbook()
book.sheetnames # 返回所有WorkSheets的名字列表,以list形式存储
book.worksheets # 返回所有 WorkSheet 对象的列表,以list形式存储
# 获取 Sheet 页对象
food=book["food"] # 通过 Sheet 页名获取 Sheet 页对象
# 创建 Sheet 页
food=book.create_sheet("food") # 默认表示最后一个 Sheet 页
food=book.create_sheet("food", 0) # 表示插入到第一个 Sheet 页
# 删除 Sheet 页
# 方式一
book.remove(sheet)
# 方式二
del book[sheet]
# 方式三
book.remove_sheet(book['sheet1 Copy']) # 这里的参数是一个 Sheet 页对象
print(wb.worksheets)
# [<Worksheet "sheet1">, <Worksheet "sheet2">, <Worksheet "sheet_new">]
# 复制 Sheet 页
sheet_copy = book.copy_worksheet(book['sheet1']) # 这里的参数是一个 Sheet 页对象
# 保存数据,即表示生成表格文件
book.save('文件名称.xlsx') # 注意: 存在则覆盖
book.guess_types = True # 针对百分比该参数如果等于 False 结果会打印百分数, True 打印小数
查看 Sheet 表对象
sheet=openpyxl.Workbook().active # 默认会创建一个名为 Sheet 的 Sheet 页,并返回当前默人选中的WorkSheet
sheet.rows # 表示每一行的数据,值为一个生成器,每一行又由一个 tuple 包裹
sheet.columns # 表示每一列的数据,值为一个生成器,每一行又由一个 tuple 包裹
sheet.values # 表示所有单元格的值, 值为一个生成器,每一元素表示一行,且是一个 tuple;
sheet.title # Sheet 页标题
sheet.active_cell # 表示光标所在单元格
sheet.selected_cell # 表示选中单元格
sheet.row_dimensions[2].height = 40 # 第2行行高
sheet.column_dimensions['C'].width = 30 # C列列宽
sheet.max_row # 获得最大行
sheet.max_column # 获得最大列
sheet.sheet_properties.tabColor = "1072BA" # 设置 sheet 标签按钮颜色
# 访问单元格
# 方法一
>>> c = ws['A4']
# 方法二:row 行;column 列
>>> d = ws.cell(row=4, column=2, value=10)
>>> bk.cell(column=99, row=2).value
# 方法三:只要访问就创建
>>> for i in range(1,101):
... for j in range(1,101):
... ws.cell(row=i, column=j)
#
sheet.iter_rows(min_row=2, max_row=4, min_col=1, max_col=3) # 注意行号从 1 开始
sheet.iter_cols(min_row=2, max_row=4, min_col=1, max_col=3)
sheet.delete_rows
sheet.delete_cols #
sheet['A2:C4'] # 获取单元格序列
# 设置sheet标签的颜色
sheet.sheet_properties.tabColor = '0d6efd'
ws[‘A1’]:根据坐标获取单个单元格对象
ws.cell(row, column, value=None):根据行列获取单个单元格对象
ws[1]:获取第一行所有单元格对象,ws[“1”]也可
ws[“A”]:获取第A列所有单元格对象
ws[“A”:“B”]:获取A到B列所有单元格对象,ws[“A:B”]也可
ws[1:2]:获取1到2行所有单元格对象,ws[“1:2”]也可
ws[“A1”:“B2”]:获取A1到B2范围所有单元格对象,ws[“A1:B2”]也可。
# 插入和删除行和列--- 插入和删除行、列均使用数字指定
ws.insert_rows(row_index,amount=1):在第row_index行上方插入amount列,默认插入1列
ws.insert_cols(col_index,amount=1):在第col_index列左侧插入amount列,默认插入1列
ws.delete_rows(row_index,amount=1):从row_index行开始向下删除amount行,默认删除1行
ws.delete_cols(col_index,amount=1):从col_index列开始向右删除amount行,默认删除1列
查看单元格对象
print(bk["A1"].row , bk["A1"].column, bk["A1"].value )
bk["A1"].row # 行号
bk["A1"].column # 列号
bk["A1"].value # 单元格值
bk["A1"].font # 单元格字体对象 , 可赋值一个 Font 对象 openpyxl.styles.fonts.Font object
bk["A1"].alignment # 单元格对齐方式 , 可赋值一个 openpyxl.styles.alignment.Alignment object
bk["A1"].border # 单元格的边框, 可赋值一个 openpyxl.styles.borders.Border object
bk["A1"].encoding # 单元格字符编码
bk["A1"].fill #
# 创建单元格对象
cell.column_letter # 字母列标
cell.coordinate : # 坐标,例如’A1’
cell.data_type : # 数据类型, ’s‘ = string字符串,‘n’ = number数值,会根据单元格值自动判断
cell.number_format :单元格格式,默认”General“常规,详见excel自定义数据类型
# 单元格样式
cell.font :获取或设置单元格Font对象 (字体名称,字体大小,是否加粗,字体颜色等)
cell.border : 获取或设置单元格边框
cell.alignment : 获取或设置单元格水平/垂直对齐方式
cell.fill:获取或设置单元格填充颜色
# 列宽与行高
ws.row_dimensions[行号]:获取行对象(非行数据,包括行的相关属性、行高等)
ws.column_dimensions[字母列标]:获取列对象(非行数据,包括行的相关属性、列宽等)
get_column_letter(index):根据列的索引返回字母
column_index_from_string(string):根据字母返回列的索引
row.height:获取或设置行高
column.width:获取或设置列宽
根据数字得到字母,根据字母得到数字
from openpyxl.utils import get_column_letter, column_index_from_string
# 根据列的数字返回字母
print(get_column_letter(2)) # B
# 根据字母返回列的数字
print(column_index_from_string('D')) # 4
遍历单元格,查看每一行,每一列
# 因为按行,所以返回A1, B1, C1这样的顺序
for row in sheet.rows:
for cell in row:
print(cell.value)
# A1, A2, A3这样的顺序
for column in sheet.columns:
for cell in column:
print(cell.value)
#
def get_value_list(t_2d):
return([[cell.value for cell in row] for row in t_2d])
def get_list_2d(sheet, start_row, end_row, start_col, end_col):
return get_value_list(sheet.iter_rows(min_row=start_row,
max_row=end_row,
min_col=start_col,
max_col=end_col))
l_2d = get_value_list(sheet['A2:C4'])
pprint.pprint(l_2d, width=40)
# [['one', 11.0, 12.0],
# ['two', 21.0, 22.0],
# ['three', 31.0, 3
l_2d = get_list_2d(sheet, 2, 4, 1, 3)
pprint.pprint(l_2d, width=40)
# [['one', 11.0, 12.0],
# ['two', 21.0, 22.0],
# ['three', 31.0, 32.0]]2.0]]
# 获取所有的行对象(常用于按行读excel)
from openpyxl import load_workbook
wb = load_workbook('1.xlsx')
ws = wb.active
for row in ws.iter_rows():
print(tuple(map(lambda x: x.value, row)))
# 获取所有的列对象(常用于按列读excel)
from openpyxl import load_workbook
wb = load_workbook('1.xlsx')
ws = wb.active
for row in ws.iter_cols():
print(tuple(map(lambda x: x.value, row)))
多单元格同时赋值
# 追加行数据, 表示直接追加一行数据
sheet.append(range(1,101))
# 赋值 Python 类型数据,Python 类型会被自动转换
ws['A3'] = datetime.datetime.now().strftime("%Y-%m-%d")
单元格合并
所谓合并单元格,即以合并区域的左上角的那个单元格为基准,覆盖其他单元格使之称为一个大的单元格。
合并后只可以往左上角写入数据,也就是区间中:左边的坐标
如果这些要合并的单元格都有数据,只会保留左上角的数据,其他则丢弃。换句话说若合并前不是在左上角写入数据,合并后单元格中不会有数据
# 合并单元格, 往左上角写入数据即可 sheet.merge_cells('B1:G1') # 合并一行中的几个单元格 sheet.merge_cells('A1:C3') # 合并一个矩形区域中的单元格
单元格拆分
相反,拆分单元格后将这个大单元格的值返回到原来的左上角位置
sheet.unmerge_cells('A1:C3')
单元格字体设置
from openpyxl.styles import Font, colors, Alignment
# 下面的代码指定了等线24号,加粗斜体,字体颜色红色。直接使用cell的font属性,将Font对象赋值给它。
bold_itatic_24_font = Font(name='等线', size=24, italic=True, color=colors.RED, bold=True)
# 参数解析
name : 字体名, 可以用字体名字的字符串
strike # 删除线, True, False
color: # 文字颜色
size: # 字体大小
bold # 加粗 , True/False
italic # 倾斜, True/False
underline # 下划, True/False
sheet['A1'].font = bold_itatic_24_font
a1.font = Font(color=colors.RED, italic=True)
ft = Font(color=colors.RED) # color="FFBB00",颜色编码也可以设定颜色
# 设置字体和大小
ft1 = Font(name=u'宋体', size=10)
ft2 = copy(ft1) #复制字体对象
ft2.name = "Tahoma"
# 设置字体样式 字体大小为30, bold 加粗
font = Font(u'微软雅黑', size=30, bold=True, color="0000FF")
sheet.cell(1, 2, "纯蓝").font = font
单元格对齐方式设置
from openpyxl.styles import Font, colors, Alignment
也是直接使用cell的属性aligment,这里指定垂直居中和水平居中。除了center,还可以使用right、left等等参数。
# 设置B1中的数据垂直居中和水平居中
sheet['B1'].alignment = Alignment(horizontal='center', c='center')
# horizontal: center, left, right
# horizontal: center , top, bottom
单元格边框设置
border = Border(left=Side(border_style='thin', color='000000'),
right=Side(border_style='mediumDashed', color='000000'),
top=Side(border_style='double', color='000000'),
bottom=Side(border_style='dashed', color='000000'))
sheet['B2'].border = border
wb.save("./data/边框.xlsx")
# 可用的样式
style = NoneSet(values=('dashDot','dashDotDot', 'dashed','dotted',
'double','hair', 'medium', 'mediumDashDot', 'mediumDashDotDot',
'mediumDashed', 'slantDashDot', 'thick', 'thin'))
单元格前景色和背景色设置
# 设置填充颜色-- 背景颜色
fille = PatternFill('solid', fgColor="FF00FF")
whiteFill = PatternFill(fgColor='FFFFFF', bgColor='FFFFFF', fill_type='solid')
sheet.cell(1, 1, "洋红色").fill = fille
单元格插入图片
# 需要先安装Pilow
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook('e:\\sample.xlsx')
ws1=wb.active
img = Image('e:\\1.png')
ws1.add_image(img, 'A1')
wb.save("e:\\sample.xlsx")
单元格隐藏
from openpyxl import load_workbook
from openpyxl.drawing.image import Image
wb = load_workbook('e:\\sample.xlsx')
ws1=wb.active
ws1.column_dimensions.group('A', 'D', hidden=True) #隐藏a到d列范围内的列
ws1.column_dimensions.group('A','D', hidden=True)
ws1.row_dimensions.group(1,10, hidden=True)
#ws1.row_dimensions 无group方法
# Save the file
wb.save("e:\\sample.xlsx")
设置宽 ,高
wb = load_workbook('e:\\sample.xlsx')
ws1=wb.active
ws1.column_dimensions['D'].width = 20
ws1.row_dimensions['D'].height = 20