Pandas keep existing format of Excel

[AlphaInc]

Hello everybody,

I've written a little Python Script which loads an Excel file and picks random entries basend on a few criteria. Those entries are then written to a python list. The status of those entries needs to be changed afterwards in my excel. This works as planned.
But when the data is written to Excel my custom formatting (Alignment of all rows, text- and row colours are all set back to white, etc.) is gone. So this brings me to my question, how can i save to Excel with pandas while keeping my original formating?

This is my code so far:

#!/usr/bin/env Python3

# Imports
import pandas as pd
import random
import sys

def main():
    # Load Information From Excel
    df = pd.read_excel('config/Mappe1.xlsx')

    try:
        # Create List
        random_names = []

        # Get Random Task Entries
        for i in [{"description": "Value Below 800", "start_number": 0, "end_number": 800, "amount": 2}, {"description": "Value Above 800", "start_number": 801, "end_number": 10000, "amount": 1}]:
            # Criteria Are Checked
            entries = df.query(f"`Category` == 'Tasks' and `Status` == 'Open' and {i['start_number']} <= `Value` <= {i['end_number']}")[['Name', 'Value']].values.tolist()

            # Select Random Names and Append to List
            selected_entries = random.sample(entries, i['amount'])
            random_names.extend(selected_entries)

        # Change Information in DataFrame
        for name, follower in random_names:
            df.loc[df['Name'] == name, 'Status'] = 'Done'

        # Save Information to Excel
        df.to_excel('config/Mappe1.xlsx', index=False)

    except Exception as e:
        print(e)

# Start Main Function
main()

# End
sys.exit()

[deanhystad]

Pandas knows nothing about excel formatting, but you can add formatting when you write a dataframe to an excel file. This is done by making a custom xlsx writer. XlsxWriter is a package for making custom "writers" that can be passed to to_excel().

If you know what cells changed, you can use a package like Openpyxl to open the existing excel file and modify the data of the cells, leaving the formatting alone. Looking at your example I don't see where you are getting any benefit from using Pandas. Maybe you could open the spreadheet using openpyxl, make all the changes using regular python code, and write the modified spreadsheet.

I'm a bit confused about this:

    except Exception as e:
        print(e)

This extra code only results in getting less information about errors that occur.

# End
sys.exit()

This just results in your program raising an exception and ending. Your program will end anyway because there are no instructions left to execute.

[plonkarchivist]

When using pandas to write data back to an Excel file, you can lose the formatting because the default behavior is to only write the data, not the formatting information. To preserve the formatting, you can use the openpyxl library in conjunction with pandas. This library allows you to load an existing workbook, modify its content, and save it, preserving the original formatting. doodle baseball

#!/usr/bin/env Python3

# Imports
import pandas as pd
import openpyxl
import random
import sys

def main():
    # Load Information From Excel
    df = pd.read_excel('config/Mappe1.xlsx', engine='openpyxl')

    try:
        # Create List
        random_names = []

        # Get Random Task Entries
        for i in [{"description": "Value Below 800", "start_number": 0, "end_number": 800, "amount": 2}, {"description": "Value Above 800", "start_number": 801, "end_number": 10000, "amount": 1}]:
            # Criteria Are Checked
            entries = df.query(f"`Category` == 'Tasks' and `Status` == 'Open' and {i['start_number']} <= `Value` <= {i['end_number']}")[['Name', 'Value']].values.tolist()

            # Select Random Names and Append to List
            selected_entries = random.sample(entries, i['amount'])
            random_names.extend(selected_entries)

        # Change Information in DataFrame
        for name, follower in random_names:
            df.loc[df['Name'] == name, 'Status'] = 'Done'

        # Save Information to Excel with openpyxl
        with pd.ExcelWriter('config/Mappe1.xlsx', engine='openpyxl') as writer:
            writer.book = openpyxl.load_workbook('config/Mappe1.xlsx')
            df.to_excel(writer, sheet_name='Sheet1', index=False)

    except Exception as e:
        print(e)

# Start Main Function
main()

# End
sys.exit()