python 操作excel--openpyxl

准备工作

材料： xlsx文件
环境要求：适配 windows、linux
需求：对指定表格内容进行增、改，并保留原xlsx文件的样式和公式

场景分析

• 由于操作文件是xlsx, 所以xlrd、xlwt、xlutils这三个包已经不再适合我们的需求
• 由于xlwings 不支持liunx环境，排除
• 最终在从常用的选择，我选择了 openpyxl

读取原文件， 另存为

使用 load_workbook()读取excel
load_workbook 的几种模式

1. read_only=False
   只读模式，不可对内容进行编辑
2. keep_vba=KEEP_VBA
   default KEEP_VBA=False，保留 vba 内容
3. data_only=False
   控制带有公式的单元格是否具有公式（默认）或 Excel 上次读取工作表时存储的值
4. keep_links=True
   是否应保留指向外部工作簿的链接

看这几个模式，只能从keep_vba和data_only 中选择了

data_only=True

import uuid
import openpyxl


# ./static/excel_dome/测试style.xlsx
dome_path = os.path.join(os.path.curdir, "static", "excel_dome", "测试style.xlsx")
# 打开已存在的Excel文件
wb = openpyxl.load_workbook(dome_path, data_only=True)

file_name = "{}.xls".format(uuid.uuid4().hex)
# 保存路径 ./static
path_dir = os.path.join(os.path.curdir, "static")
wb.save(os.path.join(path_dir, file_name))
wb.close()

看结果：

另存的新文件中，只保留了样式，公式所在的单元格都只保留了第一次打开excel,公式的结果值

keep_vba=True

import uuid
import openpyxl


# ./static/excel_dome/测试style.xlsx
dome_path = os.path.join(os.path.curdir, "static", "excel_dome", "测试style.xlsx")
# 打开已存在的Excel文件
wb = openpyxl.load_workbook(dome_path, keep_vba=True)

file_name = "{}.xls".format(uuid.uuid4().hex)
# 保存路径 ./static
path_dir = os.path.join(os.path.curdir, "static")
wb.save(os.path.join(path_dir, file_name))
wb.close()

看结果：

另存的新文件中，保留了样式，和公式, 其他sheet中的公式也保留了下来

进行增、改操作

import uuid
import openpyxl


data = [[123, 123, 1], [321, 321, 2]]
nation = "叙利亚"

def update_excel(data, nation):
    dome_path = os.path.join(os.path.curdir, "static", "excel_dome", "报关style.xlsx")
    # 打开已存在的Excel文件
    wb = openpyxl.load_workbook(dome_path, data_only=True)
    # 获取sheet对象
    sheet = wb["报关清单（面单） "]

    # 读取Excel信息
    c = sheet['C9']
    row_index = c.row

    cols = ['B', 'C', 'D', 'E', 'F', 'G', 'H', 'I', 'J', 'K', 'L', 'M', 'N', 'O', 'P', 'Q', 'R', 'S', 'T', 'U', 'V',
            'W']

    # 目的国
    sheet['N3'].value = nation

    for m in range(len(data)):
        row = row_index + m
        for item in cols:
            if row > 9:
                sheet['%s' % item + str(row)].value = sheet['%s9' % item].value
        sheet['C%s' % row].value = str(data[m][0])
        sheet['E%s' % row].value = str(data[m][1])

        # M 列是求和公式，所以此处写入的值一定要是int类型，否则需要打开excel手动转换类型
        sheet['M%s' % row].value = int(data[m][2])

    file_name = "{}.xls".format(uuid.uuid4().hex)
    path_dir = os.path.join(os.path.curdir, "static")
    wb.save(os.path.join(path_dir, file_name))
    wb.close()
    return "{}{}".format(get_config("static_file_url"), file_name)

总结

整体操作下来还是比较舒服的
如何要操作的excel是xlsx文件，还是推荐openpyxl
openpyxl文档
https://openpyxl.readthedocs.io/en/stable/