Python generare automaticamente un report in excel

Qualunque sia il nostro lavoro, prima o poi dovremo occuparci di attività ripetitive come l’aggiornamento di un report giornaliero in Excel.

Devi solo usare il modulo Python openpyxl per dire a Excel cosa vogliamo fare tramite Python.

In questo esempio utilizzeremo un file Excel con dati di vendita, al dettaglio, provenienti da un’azienda della grande distribuzione.

Se volete, potete scaricarlo dal link qui sotto:

vendite-gdo

Quindi, useremo la libreria  Pandas per leggere il file Excel, creare una tabella pivot ed esportarla in Excel.

Utilizzeremo,inoltre, la libreria Openpyxl per scrivere formule Excel, creare grafici e formattare il foglio di calcolo tramite Python.

Infine, creeremo una funzione Python per automatizzare questo processo.

Le librerie necessarie:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string

Leggiamo il file di Excel:

Prima di leggere il file Excel, assicurati che il file si trovi nella stessa posizione in cui si trova il tuo script Python. Quindi, leggi il file Excel con pd.read_excel() come nel codice seguente:

excel_file = pd.read_excel('vendite-gdo.xlsx')

excel_file[['Genere', 'Categoria prodotto', 'Totale']]

l file ha molte colonne, ma utilizzeremo solo le colonne Genere, Categoria prodotto e Totale per il report che andremo a creare. Per mostrarti come sono, li ho selezionati usando le doppie parentesi. Se eseguiamo il seguente codice:

import pandas as pd
import openpyxl
from openpyxl import load_workbook
from openpyxl.styles import Font
from openpyxl.chart import BarChart, Reference
import string
excel_file = pd.read_excel('vendite-gdo.xlsx')
excel_file[['Genere', 'Categoria prodotto', 'Totale']]

vedrl semo il dataframe che abbiamo creato simile ad un foglio di calcolo Excel:

Creare la tabella pivot

Possiamo ora ottenere una tabella pivot dal dataframe “excel_file” precedentemente creato.

Abbiamo solo bisogno di usare il metodo .pivot_table().

Supponiamo di voler creare una tabella pivot che mostri il  totale speso da uomini e donne nelle diverse categorie prodotto. Per fare ciò, aggiungiamo il codice seguente:

report_table = excel_file.pivot_table(index='Genere',
columns='Categoria prodotto',
values='Totale',
aggfunc='sum').round(0)

report_table

Otterremo:

Esportazione della tabella pivot in un file Excel.

Per esportare la tabella pivot precedente creata utilizziamo il metodo .to_excel(). All’interno delle parentesi, dobbiamo scrivere il nome del file Excel di output. In questo caso lo chiameremo  ‘ report_2021.xlsx’.
Possiamo anche specificare il nome del foglio che vogliamo creare e in quale cella dovrebbe posizionarsi la tabella pivot.

report_table.to_excel('report_2021.xlsx',
sheet_name='Report', startrow=4)

Il file ‘report_2021.xlsx’ verraà creato nella stessa cartella in cui risiede il codice.

Creiamo il report con Openpyxl
Ogni volta che vogliamo accedere a una cartella di lavoro dobbiamo utilizzare il metodo  load_workbook importato da openpyxl e poi lo salveremo con il metodo .save(). Nel codice seguente, caricheremo e salveremo la cartella di lavoro ogni volta che verrà modificata.

Apriamo il file che abbiamo esportato e verifichiamone il contenuto:

Come possiamo osservare,la riga di partenza è 5 e l’ultima riga è 7. Inoltre, la colonna di partenza è A (1) e l’ultima colonna  è G (7). Questi riferimenti saranno estremamente utili per le operazioni seguenti.

Aggiunta di un grafico tramite Python

Per creare un grafico Excel dalla tabella pivot che abbiamo creato, dobbiamo utilizzare il modulo Barchart che abbiamo importato in precedenza. Per identificare la posizione dei dati e dei valori di categoria, utilizziamo il modulo Reference di openpyxl .

# aggiunta dei dati e delle categorie
barchart.add_data(data, titles_from_data=True)
barchart.set_categories(categories)
#posizione del grafico
sheet.add_chart(barchart, "B12")
barchart.title = 'Vendite per categoria prodotto'
barchart.style = 5 # scegliamo il tipo di grafico
wb.save('report_2021.xlsx')

Otterremo questo risultato:

Il codice nel dettaglio:
barchart = BarChart() inizializza una variabile di grafico a barre dalla classe Barchart, i dati e le categorie sono variabili che indicano dove si trovano tali informazioni. Stiamo usando i riferimenti di colonna e riga che abbiamo definito sopra per poter automatizzare le operazioni. Inoltre, tieni presente che stimo includendo le intestazioni nei dati ma non nelle categorie
Usiamo add_data e set_categories per aggiungere i dati necessari al grafico a barre. All’interno di add_data aggiungiamo   titles_from_data=True perché abbiamo incluso le intestazioni per i dati
Usiamo sheet.add_chart per specificare cosa vogliamo aggiungere al foglio “Report” e in quale cella vogliamo aggiungerlo.
Possiamo modificare il titolo predefinito e lo stile del grafico usando barchart.title e barchart.style.
Salviamo tutte le modifiche con wb.save().

Aggiungere formule ad un foglio Excel tramite Python.

Puoi scrivere delle formulein un foglio di Excel tramite Python nello stesso modo in cui scriveremmo direttamente in Excel. Ad esempio, supponiamo di voler sommare i dati nelle celle B6 e B7 e mostrarli nella cella B8 con il simbolo di valuta.

sheet['B8'] = '=SUM(B6:B7)'
sheet['B8'].style = 'Currency [0]'
wb.save('report_2021.xlsx')

Possiamo ripetere la stessa operazione sulle altre colonne o utilizzare un ciclo for per automatizzarlo.

Ma prima, dobbiamo ottenere l’alfabeto per averlo un riferimento per i nomi che hanno le colonne in Excel (A, B, C, …). Per farlo, usiamo la libreria di stringhe e scriviamo il seguente codice.

import string
alphabet = list(string.ascii_uppercase)
excel_alphabet = alphabet[0:max_column]
print(excel_alphabet)

Se lo stampiamo, otteniamo una lista da A a G:

[‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘F’, ‘G’]

Dapprima abbiamo creato una lista alfabetica dalla A alla Z, ma poi ne abbiamo estratto una “slice” [0:max_column]  con le prime 7 lettere dell’alfabeto (A-G). Successivamente, possiamo creare un ciclo attraverso attraverso il quale applicare la formula della somma alle colonne che ci interessano:

#inseriamo la funzione somma nelle colonne B-G
for i in excel_alphabet:
if i!='A':
sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row})'
sheet[f'{i}{max_row+1}'].style = 'Currency'
# adding total label
sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Totale'
wb.save('report_2021.xlsx')
Ottenendo:

Il codice in dettaglio:

for i in excel_alphabet scorre tutte le colonne attive, escludendo la colonna A con if i!='A' perché la colonna A non contiene dati numerici
sheet[f'{i}{max_row+1}'] = f'=SUM({i}{min_row+1}:{i}{max_row}'

equivale a scrivere sheet['B8'] = '= SUM(B6:B7)' ma ora lo facciamo per le colonne dalla A alla G.
sheet[f'{i}{max_row+1}'].style = 'Currency' fornisce lo stile della valuta alle celle al di sotto dell’ultima riga.
Aggiungiamo l’etichetta “Totale” alla colonna A sotto la riga massima con

sheet[f'{excel_alphabet[0]}{max_row+1}'] = 'Totale'

[elementor-template id=”12586″]

[no_toc]

(1428)

PubblicitàPubblicità