Preserve conditional formatting with pandas.ExcelWriter () and engine = openpyxl

I am trying to create a bunch of excel files from a template file that contains conditional formatting for a range of cells that I will be populating with data.

I am creating a dataframe from an excel file that contains all the airports in a state with a bunch of data columns that describe the airport. Then I want to make a summary file for each airport, which is basically output from the airport row, and put into a new excel file as a column with a few extra columns. All of this is currently working.

However, when I write the DataFrame to a new file created from the template excel file, everything else in the new file is retained (the rest of the filled cells still exist), except the conditional formatting is not shown. When I look at the conditional formatting rules in the new file, this rule exists, but the format part is reset. See the images below:

Template file formatting:

Formatting in a new file (copied from the template and populated from the framework):

So here's the code:

Create a name for the output file, copy the template and save it with a new name:

# ap is the single airport row extracted from the master frame (series here)
ap_name = ap['Airport Name'].replace('/', '-').strip()
ap_id = ap['Airport Identifier']
file_name = '{} ({}).xlsx'.format(ap_name, ap_id)
output_ap_file = path.join(output_folder, file_name)
shutil.copy(input_template_file, output_ap_file)  # copy template and create new file

      

        
        
        
      

    

Note. If I look at the file created in the previous step, the formatting works fine. I can add something that added my trigger cell and row borders.

Clean up the new airport series and make it a data frame with some other columns:

ap = ap.dropna()
ap.name = 'Existing Data'
ap = ap.reset_index().rename(columns={'index': 'Information'})
ap.insert(len(ap.columns), 'Current Information?', np.nan)
ap.insert(len(ap.columns), 'Corrected Information', np.nan)

      

        
        
        
      

    

Identify the author and write the data file to a new file:

writer = pd.ExcelWriter(output_ap_file, engine='openpyxl')
book = load_workbook(output_ap_file)
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

ap.to_excel(writer, index=False, startrow=start_row)

writer.save()

      

        
        
        
      

    

As stated earlier, the new file successfully saved existing cells from the template file, populated with data in the correct range, even kept the conditional formatting rule, but the actual format inside the rule was reset.

To some extent a loss, any help is appreciated.

Thank!