2

I have a work process that I am automating. I believe this would be a very familiar scenario to many others. The task is somewhat like this:

  1. Download zip files from mail (csv files)
  2. extract values from the csv files
  3. paste these values into the corresponding sheet in a master excel file (whilst retaining the formatting) - using pandas and python to do the sheet mapping and extracting/pasting of data via pd DataFrames
  4. save the master file after repeating step 3-4 for all csv files. And fanout as email.

STEP 3 is the problem in particular. At this step I have already fully compiled the data I want and injected it into the respective sheet (as df). I am only left to write this df into the master excel sheet. However, I am still unable to find a method that does this without overriding my excel formatting. It would be really helpful to have a paste as values solution rather than to spend time and lengthening the code by doing an additional formatting step after pasting. Hope to get some help from you experts out there!

Currently exploring with pandas, numpy, openpyxl to solve the solution.

# INJECT ORDERED DATA FROM TEMP DF INTO TRANSACTION METRICS DF
transaction_metrics_df[INPUT_DATETIME] = temp_df["data"]
with pd.ExcelWriter(TRANSACTION_METRICS, engine="openpyxl") as writer:
   transaction_metrics_df.to_excel(writer, sheet_name=update_sheet, 
index=False)

I understand as of now my code will override the whole master sheet with just one sheet, not because I don't know how to save multiple sheets. Its just that at the moment I am getting one sheet right first. Anyways, getting to the problem with the output, the final xlsx file is saved with correct data, however, all excel formatting that used to exist is now lost e.g. numbers to 3dp, coloured fills, borders... aesthetic problem basically

Fraser Hay
  • 53
  • 4
JustinChoy
  • 21
  • 3
  • correction "to find a solution". – JustinChoy Jun 20 '19 at 04:06
  • I think you are looking something like https://stackoverflow.com/questions/38074678/append-existing-excel-sheet-with-new-dataframe-using-python-pandas – Mohamed Thasin ah Jun 20 '19 at 05:18
  • @MohamedThasinah sadly not really the same issue. I have already experimented with append method, and appending is not the issue I have. But since appending was mentioned, yes I have tried it, but it also overrides the pre existing formatting in the excel sheet. – JustinChoy Jun 20 '19 at 06:14
  • I would recommend this answer: https://stackoverflow.com/questions/71527992/pandas-dataframe-to-specific-sheet-in-a-excel-file-without-losing-formatting/73116312#73116312 – Fraser Hay Jul 26 '22 at 14:52

0 Answers0