背景:
项目需求在原excel文件,根据要求给某一个格子插入图片
实现:
使用pd.ExcelWriter("test_new.xlsx", engine='openpyxl'), openpyxl引擎,基本上原格式与数据不丢失,
但是图片数据会丢失。
源码:
import openpyxl
import pandas as pd
import xlsxwriter
'''
向已经有的xls, 已有的sheet,插入新的数据, RUN OK!!!
But:
ISSUE: 原xls的图片数据会丢失!!!
'''
sheet_name = "SA FACT"
def modify_excel(path):
book = openpyxl.load_workbook(path) # test.xlsx 已存在并有数据
df_empty = pd.DataFrame({'q': ["pppppppppppppp", 2], 'w': [3, 4]})
with pd.ExcelWriter("test_new.xlsx", engine='openpyxl') as writer: #type: pd.io.excel._openpyxl._OpenpyxlWriter
writer.book = book
print(type(writer))
writer.sheets = {sheet.title: sheet for sheet in book.worksheets}
fact_sheet=writer.sheets[sheet_name] #type:openpyxl.worksheet.worksheet.Worksheet
print(type(fact_sheet))
df_empty.to_excel(writer, sheet_name="SA FACT", startrow=5, startcol=11, index=False) # 重写sheet
if __name__ == '__main__':
modify_excel("test.xlsx")