使用openpyxl 操作excel(xlsx)
由于 xlrd 和xlwt 停止更新并阉割了自己的能力来推荐使用这个库，涉及到xlsx文件的处理使用这个库。
这个库不支持xls操作
安装
pip install openpyxl
# 如果涉及图片处理
pip install pillow功能介绍
openpyxl 可以对xlsx文件进行读取和写入操作，字体单元格对齐格式等样式操作
读写操作
介绍
openpyxl 通过 Workbook sheet cell 对象分别控制 表格文件 sheet 页 和单元格所以我们可以同时读取和写入
demo 写入&读取
#!/usr/bin/env python
#-*- coding: utf-8 -*-
#PROJECT_NAME: /home/ocrproject/reno_test/json2xlsx
#CREATE_TIME: 2022-08-09
#E_MAIL: renoyuan@foxmail.com
#AUTHOR: reno
from openpyxl import Workbook # Workbook 对象
from openpyxl import load_workbook # 读取xlsx 等于载入一个Workbook
wb = Workbook() # 创建表格对象
shhet = wb.active # 激活工作区 == 返回一个默认sheet页
# 其他创建sheet 页方法
shhet1 = wb.create_sheet("Mysheet1")
shhet2 = wb.create_sheet("Mysheet2", 0)
shhet3 = wb.create_sheet("Mysheet3", -1)
# 更改sheet名
shhet1.title = "Mysheet1"
# 取sheet
# 通过名字取sheet 页
sheet_ranges = wb['Mysheet1']
# 通过索引取sheet 页
sheet = wb[wb.sheetnames[0]]
# 单元格内插值
shhet1.cell(row=1,column=1, value="1") # 坐标索引插值 从1 开始
shhet1['A1'].value = "1" # excel 表示方法
print(shhet1['A1'].value)
# 结构化数据插入 demo
demo_dict = {
"a":[1,1,1,1],
"b":[2,2,2,2],
"c":[3,3,3,3],
}
demo_list = [list([k,*value]) for k,value in demo_dict.items() ]
# 确认row 和col 长度
max_col = len(demo_dict)
max_row = len(demo_dict["a"]) +1
# 写入
for row in range(max_row):
for col in range(max_col):
shhet1.cell(row=row+1,column=col+1, value=demo_list[col][row]) # 坐标索引插值 从1 开始
wb.save('demo.xlsx') # 保存
# 读取xlsx
wb_r = load_workbook(filename = 'demo.xlsx')
sheet = wb[wb.sheetnames[0]]
sheet = wb['Mysheet1']
# 确认row 和col 长度
print(sheet.max_row)
print(sheet.max_column)
columns = sheet.columns
rows = sheet.max_row
result = sheet.iter_rows(min_row=1, max_row=sheet.max_row, min_col=1, max_col=sheet.max_column, values_only=True) # 按索引 读取
# print(list(sheet)) # sheet对象
# print(list(result)) # sheet对象读取 这个方法是仅读取的不会误操作原始文件
# 结构化数据
result = list(result)
print(result)
print(result[1])
print(result[1:])
result_d = {k:[row[index] for row in result[1:]] for index,k in enumerate(result[0]) }
print(result_d)
from openpyxl.utils import get_column_letter样式
合并单元格
需要合并的左上方和右下方单元格坐标
ws.merge_cells(range_string=‘A1:B3’)
ws.merge_cells(start_row=5, start_column=4, end_row=8, end_column=8)
单元格取消合并使用unmerge_cells
ws.unmerge_cells(range_string=‘A1:B3’)
ws.unmerge_cells(start_row=5, start_column=4, end_row=8, end_column=8)
elif block.get("type")=="table_with_line":
talbe_info = block.get("table_cells")
if len(talbe_info)<1:
continue
# 序列化 结构 1 求极值 row col 2 按照行排序 3 过滤 非必要值
new_talbe_info,max_row,max_col =_serialize_table(talbe_info)
print("写入信息")
for cell_info in new_talbe_info:
print(cell_info)
content = cell_info[4]
cell = sheet.cell(row=pageRow+cell_info[0], column=1+cell_info[1])
cell.value = content
sheet.merge_cells(start_row=pageRow+cell_info[0], start_column=1+cell_info[1], end_row=pageRow+cell_info[2], end_column=1+cell_info[3])
max_row+=1
max_col+=1
pageRow += max_row字体/对齐/边框/颜色/填充
样式可以应用于以下几个方面：
- font 设置字体大小、颜色、下划线等。
- 填充以设置图案或颜色渐变
- 边框在单元格上设置边框
- 单元格对齐
- 保护
>>> from openpyxl.styles import PatternFill, Border, Side, Alignment, Protection, Font
>>> font = Font(name='Calibri',
... size=11,
... bold=False,
... italic=False,
... vertAlign=None,
... underline='none',
... strike=False,
... color='FF000000')
>>> fill = PatternFill(fill_type=None,
... start_color='FFFFFFFF',
... end_color='FF000000')
>>> border = Border(left=Side(border_style=None,
... color='FF000000'),
... right=Side(border_style=None,
... color='FF000000'),
... top=Side(border_style=None,
... color='FF000000'),
... bottom=Side(border_style=None,
... color='FF000000'),
... diagonal=Side(border_style=None,
... color='FF000000'),
... diagonal_direction=0,
... outline=Side(border_style=None,
... color='FF000000'),
... vertical=Side(border_style=None,
... color='FF000000'),
... horizontal=Side(border_style=None,
... color='FF000000')
... )
>>> alignment=Alignment(horizontal='general',
... vertical='bottom',
... text_rotation=0,
... wrap_text=False,
... shrink_to_fit=False,
... indent=0)
>>> number_format = 'General'
>>> protection = Protection(locked=True,
... hidden=False)单元格样式和命名样式
样式有两种类型：单元格样式和命名样式，也称为样式模板。
单元格样式
单元格样式在对象之间共享，一旦指定，就无法更改。这可以阻止不需要的副作用，例如在仅更改一个单元格时更改许多单元格的样式。
>>> from openpyxl.styles import colors
>>> from openpyxl.styles import Font, Color
>>> from openpyxl import Workbook
>>> wb = Workbook()
>>> ws = wb.active
>>>
>>> a1 = ws['A1']
>>> d4 = ws['D4']
>>> ft = Font(color="FF0000")
>>> a1.font = ft
>>> d4.font = ft
>>>
>>> a1.font.italic = True # is not allowed # doctest: +SKIP
>>>
>>> # If you want to change the color of a Font, you need to reassign it::
>>>
>>> a1.font = Font(color="FF0000", italic=True) # the change only affects A1复制样式
样式也可以复制
>>> from openpyxl.styles import Font
>>> from copy import copy
>>>
>>> ft1 = Font(name='Arial', size=14)
>>> ft2 = copy(ft1)
>>> ft2.name = "Tahoma"
>>> ft1.name
'Arial'
>>> ft2.name
'Tahoma'
>>> ft2.size # copied from the
14.0其他
插入图片
from openpyxl.drawing.image import Image
wb = Workbook
ws = actuve
img = Image("aa.png")
# 设置图片宽高
size = (90,90)
img.width,img.height = size
ws.add_image(img,"A1")
wb.save("test.xlsx")
本文章为转载内容，我们尊重原作者对文章享有的著作权。如有内容错误或侵权问题，欢迎原作者联系我们进行内容更正或删除文章。
