使用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")