pandas DataFrame.to_excel keeping excel file format

Question

there is an option in pandas to keep the format of the file, when I use df.to_excel to save the data on the file?
The only workaround that i found is:

from openpyxl import load_workbook
import pandas as pd

# df_data is a pd.DataFrame

wb = load_workbook(fout_file)
sheet = wb.active
for r, row in enumerate(dataframe_to_rows(df_data, index=False, header=False), 2):
    for c in range(0, df_columns):
        sheet.cell(row=r, column=c + 1).value = row[c]
wb.save(fout_file)

There a better way where i don't must copy cell by cell?

Thanks

stefano G.

@DSteman thanks for the idea, I jus tryed to use StyleForm as you advised me.

def main ():
    ...
    ...
    ...
    # df_new_data = pd.DataFrame(columns=self.df_values.columns)
    df_new_data = StyleFrame.read_excel(self.template_fout, read_style=True)
    ...
    ...
    ...
        cr_dfnewdata = 0
        for j, row_data in data.iterrows():
            original_row = row_data.copy(deep=True)
            # df_new_data = df_new_data.append(original_row)
            cr_dfnewdata += 1
            df_new_data[cr_dfnewdata] = original_row
            ...
            ...
            ...
            compensa_row = row_data.copy(deep=True)
            compensa_row[self.importo_col] = importo * -1
            # compensa_row[self.qta_col] = qta * -1
            compensa_row[self.cod_ribal_col] = f"{cod_ribal}-{j}"
            # df_new_data = df_new_data.append(compensa_row)
            cr_dfnewdata += 1
            df_new_data[cr_dfnewdata] = compensa_row
            ...
            ...
            ...


def save_working_data(self, cod_ribalt: str, df_data):
    fout_working_name = f"{self.working_dir}/working_{cod_ribalt}.xlsx"
    df_data.to_excel(fout_working_name).save()

BUT i got this error:

export_df.index = [row_index.value for row_index in export_df.index] AttributeError: 'int' object has no attribute 'value'

What do you mean exactly with 'keep the format of the file'? — DSteman
– DSteman, Commented Jun 30, 2021 at 16:26
Hi @DSteman, the case is this, i just have a xlsx template where i've alreday setted the right column width, the format of text, the alternate color of the rows, the right format for the header. I wish to presesrve these formats. I wish that pandas saves only the data on that file. — Stefano G.
– Stefano G., Commented Jul 2, 2021 at 7:52
If my answer worked out for you please accept it as an answer by clicking the checkmark, that way this thread will be closed ;) — DSteman
– DSteman, Commented Jul 3, 2021 at 13:23

Chadee Fouad · Accepted Answer · 2022-03-29 23:53:38Z

5

You can do this using df.to_clipboard(index=False)

from win32com.client import Dispatch
import pandas as pd

xlApp = Dispatch("Excel.Application")
xlApp.Visible = 1
xlApp.Workbooks.Open(r'c:\Chadee\test.xlsx')
xlApp.ActiveSheet.Cells(1,1).Select

d = {'col1': [1, 2], 'col2': [3, 4]}
df = pd.DataFrame(data=d)
df.to_clipboard(index=False)

xlApp.ActiveWorkbook.ActiveSheet.PasteSpecial()

Output:

Note that the cell colors are still the same

Hope that helps! :-)

edited Mar 29, 2022 at 23:53

answered Mar 29, 2022 at 23:36

Chadee Fouad

3,0192 gold badges30 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

DSteman · Accepted Answer · 2021-07-02 09:36:49Z

2

Use the styleframe module to perserve most of the styling in your sheet. If you have a styled sheet with columns ['Entry 1', 'Entry 2'] for example, you can enter values like this:

from styleframe import StyleFrame

sf = StyleFrame.read_excel('test.xlsx', read_style=True)

sf.loc[0,'Entry 1'].value = 'Modified 1'
sf.to_excel('test.xlsx').save()

Make sure that the cell you are trying to fill already has a placeholder value like 0. My script returned errors if it attempted to fill an empty cell.

Check out this thread too: Overwriting excel columns while keeping format using pandas

answered Jul 2, 2021 at 9:36

DSteman

1,7145 gold badges15 silver badges36 bronze badges

1 Comment

Stefano G. Over a year ago

Hi @DSteman, my destination xls file is only a template, it doesn't have data in it. The solution on stackoverflow.com/questions/54005600/…, uses a loop as I do with my workaround. So for my necessity I don't need styleframe, is enough the loop that I showned in the question . Thanks for the support and to permit to me to know a new library.

Vajeeston · Accepted Answer · 2024-04-22 16:29:48Z

0

import pandas as pd
from openpyxl import load_workbook
df = pd.read_excel('your_excel_file.xlsx')
workbook = load_workbook('your_excel_file.xlsx')
worksheet = workbook.active

# Get the maximum row and column indices
max_row = worksheet.max_row
max_col = worksheet.max_column

for r_idx, row in enumerate(df.values, start=1):
    for c_idx, value in enumerate(row, start=1):
        worksheet.cell(row=r_idx + max_row, column=c_idx, value=value)

# Save the changes
workbook.save('output.xlsx')

answered Apr 22, 2024 at 16:29

Vajeeston

1

3 Comments

Vajeeston Over a year ago

This will keep the colors and format without any change in your file!

Jeremy Caney Over a year ago

Thank you for contributing to the Stack Overflow community. This may be a correct answer, but it’d be really useful to provide additional explanation of your code so developers can understand your reasoning. This is especially useful for new developers who aren’t as familiar with the syntax or struggling to understand the concepts. Would you kindly edit your answer to include additional details for the benefit of the community?

Chadee Fouad Aug 2 at 1:40

The issues with this is that is will be quite slow if you have a lot of data.

Collectives™ on Stack Overflow

pandas DataFrame.to_excel keeping excel file format

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

1 Comment

3 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related