写在前面
- 在实际的工作场景中,我们通常需要在原表的基础更新存储或修改数据,而不是直接存储一个新表。而检索出来的大多是存储新表。
- 在可以存储的情况下,还是会遇以下两种情况:1,想在不修改原表样式的基础上进行数据修改;2. 想对一个表的不同sheet修改并存储。
- 首先,一开始的时候自己是通过pandas进行行列存储,前面的博客中有介绍,虽然很便捷,但是会存在一个问题,存储是覆盖是写入,会丢失原表的格式,虽然网上说通过设置engine=“openpyxl”,可以解决覆盖问题,但是试了过以后发现不好用,适用于写入新表数据,而不是做修改。 其次是,使用openpy修改存储,虽然好用,直接操作原表,前不改变原表样式,但是没有pandas灵活,一般是单个cell操作。
- 以下是自己基于上面两个库,分别封装的函数,比较简陋,但是可以方便新手直接调用。 封装pandas的函数应用场景是:单sheet,新增存储列(函数本身新增单列),套一个for就可以新增存储多列了。 封装openpyxl的函数应用场景是:不修改原表样式,指定位置根据索引出列数据。(openpyxl是操作单cell的,没有官方的新增列的)
1. pandas 存储列数据
1.1 函数
# pandas进行写入数据 写入列 不支持多sheet写入 会覆盖原表
# 通常用于更新单列数据
import pandas as pd
def store_col_pandas(file_name, col_name, col_data):
"""
:param file_name: 需储的文件路径
:param col_name: 存储数据的列名
:param col_data: 存储的数据
"""
update_data = pd.read_excel(file_name)
update_data[col_name] = col_data
pd.DataFrame(update_data).to_excel(file_name, index=False, header=True)
1.2 存储示例
store_col_pandas(dict_config["register_history"],
col_name=dict_config["date_last_now"][1], col_data=register_now)
2. openpyxl 存储列数据
2.1 函数
# openpyxl写入数据 写入列 可以多sheet写入 但是是逐空写入 不会覆盖原表
# 通常是指定列写入所以需要传入列
import openpyxl
def store_col_openpyxl(file_name, sheet_name, col_index, col_range, data):
"""
:param file_name: 需储的文件路径
:param sheet_name: 操作的exel的sheet名
:param col_index: exel中对应的列索引值 (输入int, 不要输入字母)
:param col_range: 列的范围 (例如 range(2, 6))
:param data: 存储的值
"""
wb = openpyxl.load_workbook(file_name)
ws = wb[sheet_name]
for index, row in enumerate(col_range):
ws.cell(row=row, column=col_index).value = data[index]
wb.save(file_name)
wb.close()
2.2 存储示例
store_col_openpyxl(file_name=dict_config["result_file"],
sheet_name=dict_config["sheet_name"][3],
col_index=14,
col_range=range(2, 7),
data=refund_now_pp[2])
2.3 更新sheet
- 一个自己遇到的场景:需要更新一个表里的多个sheet,sheet名不变,但是内容是覆盖是写入,也就删除原有sheet,新建同名sheet写入。
- 如果用上面的方法也是可以的,但是不建议用,上面方法更适合单列,局部写入类型。
2.3.1 函数
def save_sheet_detail(wb, sheet_name, df:pd.dataframe):
"""
:param wb: 加载的表 (由于是一表操作多sheet, 直接传入加载好的表)
:param sheet_name: 需要操作的sheet
:param df: 需要存储的数据(数据类型:pd.dataframe)
(注:pd是 import pandas as pd)
"""
del wb[sheet_name]
sheet_detail = wb.create_sheet(sheet_name)
from openpyxl.utils.dataframe import dataframe_to_rows
for r in dataframe_to_rows(df, index=False, header=True):
sheet_detail.append(r)
2.3.2 示例
dict_config = config_name.required_config1
wb = openpyxl.load_workbook(dict_config["result_file"])
register_detail = register_read_detail(dict_config)
save_sheet_detail(wb, dict_config["sheet_name"][1], register_detail)
4. 可能出现的报错
- AttributeError: ‘MergedCell’ object attribute ‘value’ is read-only
问题: 当你操作的Exel中出现合并过的单元格时, 插入值或修改值可能出现以上报错。
或者没有出现报错, 但是值填充不到对应的位置。
解决方案: 这是因为出现合并单元格式,会导致对应的索引就发生改变了。
如果不会判断当前索引的画,就调整索引位置观察, 或者先指定索引
观察读取到的值,再来做调整。
最后
- 这里需要注意的是,以上函数为了实现通用性, 都是调用一次打开一次文件存储并关闭,而这个过程是相对会比较耗时,几万数据操作也还好,如果需要优化的话,建议修整函数,读取一次文件,完成所有修改存储操作后,再关闭文件。