pandas-Excel多表保存操作

Pandas处理Excel多张表只更新操作的sheet

1.正常pandas读取Excel表格内容，根据自己的需求处理得到操作后的data

import pandas as pd
from pandas import DataFrame
import openpyxl

# 读取Excel中sheet表格的数据---DataFrame格式
data = pd.read_excel(excel_filename, sheetname=sheet)

2.操作表格 将两列内容转换为字典形式

dictionary= data.set_index("col1").to_dict()["col2"]

3.写入data到Excel，同时不覆盖其他原有sheet

def write_excel_all(excel_filename, data,sheet):
    book = openpyxl.load_workbook(excel_filename)
    writer = pd.ExcelWriter(excel_filename, engine='openpyxl')
    writer.book = book
    writer.sheets = dict((ws.title, ws) for ws in book.worksheets)
    a = ['是'] * 227
    data['col3'] = a
    data.to_excel(writer, sheet_name=sheet, index=False)
    writer.save()

参考网址
pands-excel两列字段变字典格式：https://www.jianshu.com/p/15e3a5893c4d
pandas写入sheet不覆盖原有sheet：https://zhuanlan.zhihu.com/p/85918022

4.pandas导出excel，日期只保留年月日

data['日期'] = pd.to_datetime(data['日期'], format='%Y-%m-%d %H:%M:%S')
data['日期'] = data['日期'].dt.strftime('%Y/%m/%d')
data['日期'] = data['日期'].astype(str)

参考网址
http://www.voidcn.com/article/p-rjxyimtc-btm.html
https://blog.csdn.net/weixin_44434140/article/details/89602934
https://bbs.csdn.net/topics/392576444
https://blog.csdn.net/weixin_38546295/article/details/83537558