python - Read content of Excel with multiple sheets with pandas and append new one sheet - Stack Overflow

admin管理员组

文章数量:1410712

I have a Python script that reads content from JSON file and then write to excel file using pandas with that code and format:

fichero_excel_salida = 'Estad.xlsx'
nombre_pagina = 'Jornada_' + str(num_jornada)
count = 0

    # Generamos los dataframe y Volcamos la info al fichero excel
    with pd.ExcelWriter(fichero_excel_salida, engine="openpyxl") as writer:

        for key,contenido_dict in  dict_dict_excels.items():
            count = count +1
            key = pd.DataFrame(data=contenido_dict)
            if (count % 2) == 0:
                pass
            else:
                key = (key.T)
            print(key)
    
            if count == 1:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=1)
            elif count == 2:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=6, header=True, index=True)
            elif count == 3:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=11)

So the result of the code above, give me this with some format cells:

   A    B    C    D    E    F    G    H    I
1
2       10
3  AA   2
4  BB   3
5
6
7       XX  YY   ZZ
8  AA    5   3   6
9  BB    6   2   6
10
11
12      NUM  MIN  PTS
13 John  3   24    6
14 Dave  4   12    0
15 Ko    5   30    2
16 Dam   10  20    2
17

Now, I need to get new data from next week, and append this new data to new sheet in the same excel.

What is the best form to do it?

1. Reading the existing Excel, create new Excel file and put all data again (oldest and newest) using this code?

   dict_all_xlsx_file = pd.read_excel(fichero_excel_salida, sheet_name=None)
   

   How I can put the data with the same format as was in the previous excel file (with cell formats)?

2. Append the new data of each week in the Excel?

   How I can do it, because using my Python script in Linux with pandas 2.2.3, all time create new Excel document and I lost old sheets.

I have a Python script that reads content from JSON file and then write to excel file using pandas with that code and format:

fichero_excel_salida = 'Estad.xlsx'
nombre_pagina = 'Jornada_' + str(num_jornada)
count = 0

    # Generamos los dataframe y Volcamos la info al fichero excel
    with pd.ExcelWriter(fichero_excel_salida, engine="openpyxl") as writer:

        for key,contenido_dict in  dict_dict_excels.items():
            count = count +1
            key = pd.DataFrame(data=contenido_dict)
            if (count % 2) == 0:
                pass
            else:
                key = (key.T)
            print(key)
    
            if count == 1:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=1)
            elif count == 2:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=6, header=True, index=True)
            elif count == 3:
                key.style.set_properties(**{'text-align': 'center'}).to_excel(writer, sheet_name=nombre_pagina, startcol=0, startrow=11)

So the result of the code above, give me this with some format cells:

   A    B    C    D    E    F    G    H    I
1
2       10
3  AA   2
4  BB   3
5
6
7       XX  YY   ZZ
8  AA    5   3   6
9  BB    6   2   6
10
11
12      NUM  MIN  PTS
13 John  3   24    6
14 Dave  4   12    0
15 Ko    5   30    2
16 Dam   10  20    2
17

Now, I need to get new data from next week, and append this new data to new sheet in the same excel.

What is the best form to do it?

1. Reading the existing Excel, create new Excel file and put all data again (oldest and newest) using this code?

   dict_all_xlsx_file = pd.read_excel(fichero_excel_salida, sheet_name=None)
   

   How I can put the data with the same format as was in the previous excel file (with cell formats)?

2. Append the new data of each week in the Excel?

   How I can do it, because using my Python script in Linux with pandas 2.2.3, all time create new Excel document and I lost old sheets.

• python
• excel
• pandas

Share Improve this question Follow edited Mar 9 at 15:24 jonrsharpe 122k3030 gold badges268268 silver badges476476 bronze badges asked Mar 9 at 15:22 garvarmagarvarma 1311 silver badge44 bronze badges 2

• python - How to save a new sheet in an existing excel file, using Pandas? - Stack Overflow – furas Commented Mar 9 at 15:41
• The suggested link above has a lot of answers so to be sure you're on the right track, you want to use keep using engine="openpyxl" but use append mode mode='a' which means the workbook must already exist which should be the case. The default mode is 'w' which will overwrite your existing workbook so ensure to set this correctly. Then set the new sheet name. You can also set the 'if_sheet_exists' param to handle what happens if the new sheet name already exists in the workbook – moken Commented Mar 10 at 13:02

Add a comment  | 

1 Answer 1

Sorted by: Reset to default 0

It probably would be easiest to append the new data.

Append the new data using:

df.to_excel(writer, sheet_name='New Sheet')

Opening the file in the writer shouldn't override the current information, and specifying the sheet name should avoid the override.

xl = pd.ExcelFile('foo.xls')

xl.sheet_names  # see all sheet names

This will get the current sheet names so you can ensure there is no overlap.

Hope this helps.

本文标签： pythonRead content of Excel with multiple sheets with pandas and append new one sheetStack Overflow