CODEWITHSUNDEEP
excelpandasdataframe
Stefano G.
Stefano G.

Reputation: 309

pandas DataFrame.to_excel keeping excel file format

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'

Upvotes: 3

Views: 4666

Answers (3)

Vajeeston
Vajeeston

Reputation: 1

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')

Upvotes: 0

Chadee Fouad
Chadee Fouad

Reputation: 2948

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:

enter image description here

Note that the cell colors are still the same

Hope that helps! :-)

Upvotes: 5

DSteman
DSteman

Reputation: 1658

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

Upvotes: 2

Related Questions