Jak zapisać nowy arkusz w istniejącym pliku Excela za pomocą Pand?

Chcę używać plików programu Excel do przechowywania danych opracowanych w Pythonie. Mój problem polega na tym, że nie mogę dodać arkuszy do istniejącego pliku Excela. Tutaj proponuję przykładowy kod do pracy w celu rozwiązania tego problemu

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

Ten kod zapisuje dwie ramki DataFrame w dwóch arkuszach o nazwach odpowiednio „x1” i „x2”. Jeśli utworzę dwie nowe ramki DataFrames i spróbuję użyć tego samego kodu, aby dodać dwa nowe arkusze, „x3” i „x4”, oryginalne dane zostaną utracone.

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.save()
writer.close()

Chcę mieć plik Excela z czterema arkuszami: „x1”, „x2”, „x3”, „x4”. Wiem, że „xlsxwriter” nie jest jedynym „silnikiem”, istnieje „openpyxl”. Widziałem też, że są już inni ludzie, którzy pisali o tym problemie, ale nadal nie rozumiem, jak to zrobić.

Tutaj kod pobrany z tego linku

import pandas
from openpyxl import load_workbook

book = load_workbook('Masterfile.xlsx')
writer = pandas.ExcelWriter('Masterfile.xlsx', engine='openpyxl') 
writer.book = book
writer.sheets = dict((ws.title, ws) for ws in book.worksheets)

data_filtered.to_excel(writer, "Main", cols=['Diff1', 'Diff2'])

writer.save()

Mówią, że to działa, ale trudno zrozumieć, jak. Nie rozumiem, co w tym kontekście oznacza „ws.title”, „ws” i „dict”.

Jaki jest najlepszy sposób na zapisanie „x1” i „x2”, a następnie zamknięcie pliku, ponowne otwarcie go i dodanie „x3” i „x4”?

Dziękuję Ci. Uważam, że kompletny przykład może być dobry dla każdego, kto ma ten sam problem:

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)

writer = pd.ExcelWriter(path, engine = 'xlsxwriter')
df1.to_excel(writer, sheet_name = 'x1')
df2.to_excel(writer, sheet_name = 'x2')
writer.save()
writer.close()

Tutaj generuję plik Excela, z mojego zrozumienia nie ma znaczenia, czy jest on generowany przez silnik „xslxwriter” czy „openpyxl”.

Kiedy chcę pisać bez utraty oryginalnych danych

import pandas as pd
import numpy as np
from openpyxl import load_workbook

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

book = load_workbook(path)
writer = pd.ExcelWriter(path, engine = 'openpyxl')
writer.book = book

x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name = 'x3')
df4.to_excel(writer, sheet_name = 'x4')
writer.save()
writer.close()

ten kod działa!

W udostępnionym przykładzie ładujesz istniejący plik do booki ustawiasz writer.bookwartość book. W wierszu, do writer.sheets = dict((ws.title, ws) for ws in book.worksheets)którego uzyskujesz dostęp do każdego arkusza w skoroszycie, jako ws. Tytuł arkusza jest wtedy wstak utworzony, że tworzysz słownik {sheet_titles: sheet}par kluczy i wartości. Ten słownik jest następnie ustawiany na writer.sheets. Zasadniczo te kroki po prostu ładują istniejące dane z 'Masterfile.xlsx'i wypełniają nimi program zapisujący.

Teraz powiedzmy, że masz już plik z arkuszami x1i x2jako arkusze. Możesz użyć przykładowego kodu, aby załadować plik, a następnie możesz zrobić coś takiego, aby dodać x3i x4.

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"
writer = pd.ExcelWriter(path, engine='openpyxl')
df3.to_excel(writer, 'x3', index=False)
df4.to_excel(writer, 'x4', index=False)
writer.save()

To powinno zrobić to, czego szukasz.

Prosty przykład zapisywania wielu danych jednocześnie. A także, gdy chcesz dołączyć dane do arkusza w zapisanym pliku Excela (zamknięty plik Excela).

Kiedy po raz pierwszy piszesz do programu Excel. (Zapisywanie „df1” i „df2” do „1st_sheet” i „2nd_sheet”)

import pandas as pd 
from openpyxl import load_workbook

df1 = pd.DataFrame([[1],[1]], columns=['a'])
df2 = pd.DataFrame([[2],[2]], columns=['b'])
df3 = pd.DataFrame([[3],[3]], columns=['c'])

excel_dir = "my/excel/dir"

with pd.ExcelWriter(excel_dir, engine='xlsxwriter') as writer:    
    df1.to_excel(writer, '1st_sheet')   
    df2.to_excel(writer, '2nd_sheet')   
    writer.save()    

Po zamknięciu programu Excel, ale chcesz „dołączyć” dane do tego samego pliku programu Excel, ale w innym arkuszu, powiedzmy „df3” do nazwy arkusza „3rd_sheet”.

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

    ## Your dataframe to append. 
    df3.to_excel(writer, '3rd_sheet')  

    writer.save()     

Pamiętaj, że format Excela nie może być xls, możesz użyć formatu xlsx.

Zdecydowanie polecam pracę bezpośrednio z openpyxl, ponieważ obsługuje on teraz ramki Pandas DataFrames .

Pozwala to skoncentrować się na odpowiednim kodzie Excela i Pandy.

Do tworzenia nowego pliku

x1 = np.random.randn(100, 2)
df1 = pd.DataFrame(x1)
with pd.ExcelWriter('sample.xlsx') as writer:  
    df1.to_excel(writer, sheet_name='x1')

Aby dołączyć do pliku, użyj argumentu mode='a'w pd.ExcelWriter.

x2 = np.random.randn(100, 2)
df2 = pd.DataFrame(x2)
with pd.ExcelWriter('sample.xlsx', engine='openpyxl', mode='a') as writer:  
    df2.to_excel(writer, sheet_name='x2')

Domyślnie jest mode ='w'. Zobacz dokumentację .

Może to zrobić bez użycia ExcelWriter, używając narzędzi w openpyxl To może znacznie ułatwić dodawanie czcionek do nowego arkusza przy użyciu openpyxl.styles

import pandas as pd
from openpyxl import load_workbook
from openpyxl.utils.dataframe import dataframe_to_rows

#Location of original excel sheet
fileLocation =r'C:\workspace\data.xlsx'

#Location of new file which can be the same as original file
writeLocation=r'C:\workspace\dataNew.xlsx'

data = {'Name':['Tom','Paul','Jeremy'],'Age':[32,43,34],'Salary':[20000,34000,32000]}

#The dataframe you want to add
df = pd.DataFrame(data)

#Load existing sheet as it is
book = load_workbook(fileLocation)
#create a new sheet
sheet = book.create_sheet("Sheet Name")

#Load dataframe into new sheet
for row in dataframe_to_rows(df, index=False, header=True):
    sheet.append(row)

#Save the modified excel at desired location    
book.save(writeLocation)

Możesz czytać istniejące arkusze swoich zainteresowań, na przykład „x1”, „x2”, do pamięci i „zapisywać” je z powrotem przed dodaniem kolejnych nowych arkuszy (pamiętaj, że arkusze w pliku i arkusze w pamięci to dwa różne rzeczy, jeśli ich nie przeczytasz, zostaną utracone). To podejście wykorzystuje tylko „xlsxwriter”, bez udziału openpyxl.

import pandas as pd
import numpy as np

path = r"C:\Users\fedel\Desktop\excelData\PhD_data.xlsx"

# begin <== read selected sheets and write them back
df1 = pd.read_excel(path, sheet_name='x1', index_col=0) # or sheet_name=0
df2 = pd.read_excel(path, sheet_name='x2', index_col=0) # or sheet_name=1
writer = pd.ExcelWriter(path, engine='xlsxwriter')
df1.to_excel(writer, sheet_name='x1')
df2.to_excel(writer, sheet_name='x2')
# end ==>

# now create more new sheets
x3 = np.random.randn(100, 2)
df3 = pd.DataFrame(x3)

x4 = np.random.randn(100, 2)
df4 = pd.DataFrame(x4)

df3.to_excel(writer, sheet_name='x3')
df4.to_excel(writer, sheet_name='x4')
writer.save()
writer.close()

Jeśli chcesz zachować wszystkie istniejące arkusze, możesz zastąpić powyższy kod między początkiem a końcem:

# read all existing sheets and write them back
writer = pd.ExcelWriter(path, engine='xlsxwriter')
xlsx = pd.ExcelFile(path)
for sheet in xlsx.sheet_names:
    df = xlsx.parse(sheet_name=sheet, index_col=0)
    df.to_excel(writer, sheet_name=sheet)

#This program is to read from excel workbook to fetch only the URL domain names and write to the existing excel workbook in a different sheet..
#Developer - Nilesh K
import pandas as pd
from openpyxl import load_workbook #for writting to the existing workbook

df = pd.read_excel("urlsearch_test.xlsx")

#You can use the below for the relative path.
# r"C:\Users\xyz\Desktop\Python\

l = [] #To make a list in for loop

#begin
#loop starts here for fetching http from a string and iterate thru the entire sheet. You can have your own logic here.
for index, row in df.iterrows():
    try: 
        str = (row['TEXT']) #string to read and iterate
        y = (index)
        str_pos = str.index('http') #fetched the index position for http
        str_pos1 = str.index('/', str.index('/')+2) #fetched the second 3rd position of / starting from http
        str_op = str[str_pos:str_pos1] #Substring the domain name
        l.append(str_op) #append the list with domain names

    #Error handling to skip the error rows and continue.
    except ValueError:
            print('Error!')
print(l)
l = list(dict.fromkeys(l)) #Keep distinct values, you can comment this line to get all the values
df1 = pd.DataFrame(l,columns=['URL']) #Create dataframe using the list
#end

#Write using openpyxl so it can be written to same workbook
book = load_workbook('urlsearch_test.xlsx')
writer = pd.ExcelWriter('urlsearch_test.xlsx',engine = 'openpyxl')
writer.book = book
df1.to_excel(writer,sheet_name = 'Sheet3')
writer.save()
writer.close()

#The below can be used to write to a different workbook without using openpyxl
#df1.to_excel(r"C:\Users\xyz\Desktop\Python\urlsearch1_test.xlsx",index='false',sheet_name='sheet1')

Innym dość prostym sposobem na zrobienie tego jest utworzenie takiej metody:

def _write_frame_to_new_sheet(path_to_file=None, sheet_name='sheet', data_frame=None):
    book = None
    try:
        book = load_workbook(path_to_file)
    except Exception:
        logging.debug('Creating new workbook at %s', path_to_file)
    with pd.ExcelWriter(path_to_file, engine='openpyxl') as writer:
        if book is not None:
            writer.book = book
        data_frame.to_excel(writer, sheet_name, index=False)

Pomysł polega na tym, aby załadować skoroszyt w path_to_file jeśli istnieje, a następnie dołączyć ramkę_danych jako nowy arkusz z nazwą_arkusza . Jeśli skoroszyt nie istnieje, zostanie utworzony. Wydaje się, że ani openpyxl lub xlsxwriter append, tak jak w przykładzie powyżej przez @Stefano, naprawdę masz do obciążenia, a następnie przepisać dołączyć.