Jak automatycznie tworzyć pliki xlsx w Pythonie
xlsxwriter pozwala na automatyczne tworzenie nowych plików Excel w Pythonie, utrzymując jednocześnie wiele z funkcjonalności dostępnych w Excelu. Z ważniejszych można wymienić:
- Wprowadzanie tekstu i liczb
- Formatowanie wartości
- Hiperlinki
- Wykresy
- Filtry
- Formatowanie warunkowe
- Komentarze
- Wstawianie obrazków jak jpg czy png
Innymi słowy xlsxwriter wspiera bardzo wiele z oryginalnych możliwości Excela. Jednak trzeba podkreślić, że są to nowe pliki i moduł nie pozwala na edycję istniejących plików.
Wprowadzanie oraz formatowanie danych
Do przykładu ponownie posilimy się wartościami notowań KGHM z witryny stockwatch.
W pierwszym kroku zaimportujemy moduł xlsxwriter i kolejno zadeklarujemy obiekty workbook oraz worksheet. W naszym przypadku skoroszytem będą notowania, natomiast arkuszem – KGHM.
import xlsxwriter
notowania = xlsxwriter.Workbook("notowania.xlsx")
KGHM = notowania.add_worksheet("KGHM") Możemy jeszcze sprawdzić czy wybrane obiekty istnieją, poprzez print oraz sprawdzenie czy wywołanie KGHM jest takie samo jako wywołanie arkusza z obiektu notowania.
print(notowania)
print(KGHM)
print(notowania.sheetnames['KGHM']==KGHM)Powinniśmy otrzymać analogiczny rezultat, oczywiście z innym adresem w pamięci. Daje nam to pewność, że obiekty istnieją i są sobie równe.
<xlsxwriter.workbook.Workbook object at 0x000001F291DA9FD0>
<xlsxwriter.worksheet.Worksheet object at 0x000001F291DA9D00>
TrueAby plik zapisał się na dysku należy zastosować metodę close(). Inaczej nic nie zostanie fizycznie zapisane na dysku. Dlatego też przyjmijmy, że poniższy kod znajduje się na końcu każdego skryptu jaki wywołujemy.
notowania.close()W tym momencie mamy już pusty plik xlsx.
Kolejno dodamy dane o notowaniach w formie list.
notowania_kwartaly = ['Q3 2021', 'Q4 2021', 'Q1 2022', 'Q2 2022', 'Q3 2022']
notowania_wartosci = [153.9300, 174.6000, 123.8800, 113.8100, 114.3500]Inną przydatną metodą jest add_format do zapisywanie formatów. W ten sposób zdefiniujemy formaty dla wartości, kwartałów, nagłówków oraz format pola zmiana, które będzie liczone formułą.
format_wartosci = notowania.add_format({'bg_color':'#fff2c9'})
format_kwartaly = notowania.add_format({'bg_color':'#d7ffc9'})
format_naglowki = notowania.add_format({'bold':True})
format_zmiana = notowania.add_format({'num_format':'0.00%'})Do zapisania nagłówków wykorzystamy write_row, które pozwala na zapisywanie całych wierszy.
KGHM.write_row('A1',['Kwartały','Notowania','Zmiana'],format_naglowki)Same notowania i kwartały zapiszemy przez write_column, którym zapiszemy całe kolumny.
KGHM.write_column('A2',notowania_kwartaly,format_kwartaly)
KGHM.write_column('B2',notowania_wartosci,format_wartosci)W tym momencie jeszcze potrzebujemy poszerzyć nieco kolumnę B, ponieważ nagłówek nie mieści się w standardowej szerokości. Wartość 11 jest zupełnie subiektywna i pozwala na wyświetlenia pełnego nagłówka w kolumnie.
KGHM.set_column('B:B',11)Na tym etapie nasz plik z jednym arkuszem wygląda następująco.
Warto też wspomnieć, że wartości możemy również dodawać w pętli komórka po komórce, zamiast kolumnowo. Pierwsza metoda to dodawanie w odnosząc się do nazwy komórki.
for i in range (len(notowania_kwartaly)):
KGHM.write('A' + str(i+2) ,notowania_kwartaly[i])
KGHM.write('B' + str(i+2) ,notowania_wartosci[i])Druga to odwoływanie się bezpośrednio do numeru wiersza i numeru kolumny.
for i in range (len(notowania_kwartaly)):
KGHM.write(i+1, 0, notowania_kwartaly[i],format_kwartaly)
KGHM.write(i+1, 1, notowania_wartosci[i],format_wartosci)Kolumnę zmiana wypełnimy przez write_formula i zastosujemy formatowanie format_zmiana
for i in range (1,len(notowania_kwartaly)):
KGHM.write_formula(i+1, 2, '=(B'+ str(i+2)+ '-B' +str(i+1) + ')/B' + str(i+1),format_zmiana)Formatowanie warunkowe
Możemy również dodać formatowanie warunkowe, które będzie oznaczać zmiany większe niż 10%. Wykorzystamy tutaj zielony kolor z format_kwartaly.
KGHM.conditional_format(
"C2:C6",{
"type":"cell",
"criteria":">=",
"value":0.1,
"format":format_kwartaly
}
)Dane w notowaniach powinny wyglądać następująco.
Inną z funkcjonalności jest również poprawność danych. W naszym przypadku dodamy prostą listę wyboru, wraz z odpowiednimi nagłówkami, a także z funkcją wyszukaj.pionowo do pobierania zmian po wybranym kwartale. Warto zwrócić uwagę na anglojęzyczny styl – argumenty oddzielane przecinkami oraz nazwy formuł w języku angielskim
KGHM.write('E1','Kwartały',format_naglowki)
KGHM.write('F1','Notowania',format_naglowki)
KGHM.data_validation('E2', {'validate': 'list',
'source': notowania_kwartaly,
})
KGHM.write_formula('F2','=IFERROR(VLOOKUP(E2,$A$2:$B$6,2,0),"")')Formuła w naszym pliku będzie wyglądać jak niżej
Dodawanie wykresów w xlsxwriter
Ostatnią z funkcjonalności jakie zobaczymy jest możliwość dodawania wykresów. W tym przypadku wykorzystamy wykres kolumnowy dla zmian po kategoriach.
Dla czytelności wykres dodamy w nowej zakładce – Wykresy. Będzie to wykres kolumnowy – wykres_KGHM
wykresy = notowania.add_worksheet("Wykresy")
wykres_KGHM = notowania.add_chart({'type': 'column'})
wykresy.insert_chart('A1', wykres_KGHM)Następnie dodamy serię danych. Dla kategorii wykorzystamy kwartały i jako wartości weźmiemy listę zmian. Do tego dodamy jeszcze nazwę wykresu.
wykres_KGHM.add_series({
'categories':'=KGHM!$A$3:$A$6',
'values': '=KGHM!$C$3:$C$6',
'name':'Zmiany notowań KGHM po kwartałach'
})Nasz wykres powinien wyglądać tak jak poniżej.
To con się rzuca w oczy to zbędna legenda – mamy tylko jedną serię danych, więc tytuł jest wystarczająco opisowy. Możemy ją usunąć przez zmianę widoczności metodą set_legend.
wykres_KGHM.set_legend({'none': True})Wykres wygląda zdecydowanie lepiej.
Możemy również dodać drugą serię danych – poziomy notowań wraz z dodatkową skalą na tym samym wykresie. W tym przypadku zmienimy typ wykresu na liniowy.
wykres_KGHM = notowania.add_chart({'type': 'line'})Będziemy również potrzebować legendy. Najlepiej po prawej stronie, przy czym legenda jest dodawana domyślnie ale kod poniżej pozwala na pozycjonowanie inne niż standardowe.
wykres_KGHM.set_legend({'position': 'right'})Ze względu na występowanie wielu serii danych warto uprościć nazwę w notowaniach.
wykres_KGHM.add_series({
'categories':'=KGHM!$A$3:$A$6',
'values': '=KGHM!$C$3:$C$6',
'name':'Zmiany notowań'
})Dodając drugą serię danych warto również pamiętać o ustawieniu y2_axis, które definiuje czy ta seria danych ma się pojawiać w dodatkowej skali.
wykres_KGHM.add_series({
'categories':'=KGHM!$A$3:$A$6',
'values': '=KGHM!$B$3:$B$6',
'name':'Notowania',
'y2_axis': 1
})Przyda się również opis wartości przedstawianych na poszczególnych skalach.
wykres_KGHM.set_title({'name': 'Notowania oraz zmiany po kwartałach'})
wykres_KGHM.set_x_axis({'name': 'Kwartały' })
wykres_KGHM.set_y_axis({'name': 'Zmiany'})
wykres_KGHM.set_y2_axis({'name': 'Notowania'})Wykres będzie wyglądał następująco.
Dla poprawienia czytelności zmienimy rozmiar wykresu. Można tego dokonać na etapie wstawiania wykresu.
wykresy.insert_chart('A1', wykres_KGHM,{'x_scale': 2, 'y_scale': 2})Dzięki temu wykres jest inaczej skalowany i lepiej się prezentuje.
Pełny kod i konkluzja
import xlsxwriter
# Generowanie pliku xlsx
notowania = xlsxwriter.Workbook("notowania.xlsx")
# Generowanie zakładki
KGHM = notowania.add_worksheet("KGHM")
# Sprawdzenie obiektów
# print(notowania)
# print(KGHM)
# print(notowania.sheetnames['KGHM']==KGHM)
# Listy danych - notowania po kwartałach
notowania_kwartaly = ['Q3 2021', 'Q4 2021', 'Q1 2022', 'Q2 2022', 'Q3 2022']
notowania_wartosci = [153.9300, 174.6000, 123.8800, 113.8100, 114.3500]
# Definiowanie formatów
format_wartosci = notowania.add_format({'bg_color':'#fff2c9'})
format_kwartaly = notowania.add_format({'bg_color':'#d7ffc9'})
format_naglowki = notowania.add_format({'bold':True})
format_zmiana = notowania.add_format({'num_format':'0.00%'})
# Wstawienie nagłówków
KGHM.write_row('A1',['Kwartały','Notowania','Zmiana'],format_naglowki)
# Dodawanie wpisów jako kolumn
KGHM.write_column('A2',notowania_kwartaly,format_kwartaly)
KGHM.write_column('B2',notowania_wartosci,format_wartosci)
# Poszerzenie kolumny B
KGHM.set_column('B:B',11)
# Formuły do kolumny Zmiana
for i in range (1,len(notowania_kwartaly)):
KGHM.write_formula(i+1, 2, '=(B'+ str(i+2)+ '-B' +str(i+1) + ')/B' + str(i+1),format_zmiana)
# Formatowanie warunkowe
KGHM.conditional_format(
"C2:C6",{
"type":"cell",
"criteria":">=",
"value":0.1,
"format":format_kwartaly
}
)
# Poprawność danych
KGHM.write('E1','Kwartały',format_naglowki)
KGHM.write('F1','Notowania',format_naglowki)
KGHM.data_validation('E2', {'validate': 'list',
'source': notowania_kwartaly,
})
KGHM.write_formula('F2','=IFERROR(VLOOKUP(E2,$A$2:$B$6,2,0),"")')
# Wykres zmian po kwartałach
wykresy = notowania.add_worksheet("Wykresy")
wykres_KGHM = notowania.add_chart({'type': 'line'})
# Zmiana rozmiaru wykresu
wykresy.insert_chart('A1', wykres_KGHM,{'x_scale': 2, 'y_scale': 2})
wykres_KGHM.add_series({
'categories':'=KGHM!$A$3:$A$6',
'values': '=KGHM!$C$3:$C$6',
'name':'Zmiany notowań'
})
# wykres_KGHM.set_legend({'none': True})
wykres_KGHM.set_legend({'position': 'right'})
wykres_KGHM.add_series({
'categories':'=KGHM!$A$3:$A$6',
'values': '=KGHM!$B$3:$B$6',
'name':'Notowania',
'y2_axis': 1
})
# Tytuł wykresu oraz nazwy poszczególnych osi
wykres_KGHM.set_title({'name': 'Notowania oraz zmiany po kwartałach'})
wykres_KGHM.set_x_axis({'name': 'Kwartały' })
wykres_KGHM.set_y_axis({'name': 'Zmiany'})
wykres_KGHM.set_y2_axis({'name': 'Notowania'})
# Fizyczne zapisywanie do pliku.
notowania.close()Pomimo przedstawionych wyżej funkcjonalności, są one tylko fragmentem możliwości jakie oferuje xlsxwriter i sam moduł pozwala na bardzo wiele. Ponadto generowane pliki są niezwykle bliskie oryginałom tworzonym bezpośrednio w Excelu. Jest to o tyle przydatne, że możemy w pełni automatycznie tworzyć pliki o predefiniowanych parametrach jak wartości, formaty, wykresy czy wiele innych niezależnie czy posiadamy pakiet Office ale też przy swobodzie wyboru systemu operacyjnego.
Po więcej szczegółów odsyłam do oficjalnej dokumentacji.