Work less work better with Python
Have you ever been in a hurry to complete a document that is made of a lot of data to be reported for different items with different data? So, you have data and you need to report them in a readable way. Maybe you have the scheme of the report. How to make the work automatically or… programmatically with Python without having to get crazy reading documentation about interfacing modules to get the excel data that requires time to be checked and a lot of try and errors learning? In this post you will find a possible way that saved me a lot of time. Let’s go and check what I did.
How to get data from excel and report them in html or in word in a nice readeable mode without wasting time.
I made a very fast way to get data from excel using very simple strategies:
- copy the data in excel
- past them into an editor
- embed the data into a multiline string
- split it into rows
- split every rows to create a list of lists with the data
- use a string template of the report with the placeholders with the different data to fill
- use a for loop to add the different data in the template
I wanted this output for a number of students
Only the names and the numbers where different. This data where in an Excel file and I didn’t wanted to do too much work. So I decided to do what follows.
Let’s copy the data
I copied this data in an excel file
I pasted them in an editor and included the data in a multiline string called data.
Whit splitlines() I created a list of rows like this:
[‘ARTENA MARIAPIA\t2\t2\t3\t3\t3\t3\t3\t19’, ‘BARTINA LUCIA\t2\t1\t3\t3\t3\t3\t3\t18’, ‘CORENA RAFFAELLA\t3\t3\t3\t2\t2\t2\t2\t17’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’, ‘ZARA SOFIA\t2\t2\t3\t3\t3\t3\t4\t20’]
As you can see the \t divides all the data so I used the following loop to separate them in to a list of data for every row, in a list of list like this:
[[‘ARTENA MARIAPIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘3’, ’19’], [‘BARTINA LUCIA’, ‘2’, ‘1’, ‘3’, ‘3’, ‘3’, ‘3’, ‘3’, ’18’], [‘CORENA RAFFAELLA’, ‘3’, ‘3’, ‘3’, ‘2’, ‘2’, ‘2’, ‘2’, ’17’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’], [‘ZARA SOFIA’, ‘2’, ‘2’, ‘3’, ‘3’, ‘3’, ‘3’, ‘4’, ’20’]]
In this way the game was easy. I created this string template with the relative placeholders:
start = """
<b>{}</b><br>
Il Consiglio di classe, a conclusione del curricolo di studi, visto il giudizio analitico ed i voti complessivi riportati dall’alunn_ nelle singole discipline, considerato che la stessa non ha riportato insufficienze in alcuna disciplina, riporta il seguente credito per l’anno scolastico 2018/2019:
<table>
<td>AREA DI BASE PUNTI <td>{}<tr>
<td>AREA INDIRIZZO PUNTI <td>{}<tr>
<td>STAGE PUNTI <td>{}<tr>
<td>Anno scolastico 2014/2015 <td><tr>
<td>ASSE LINGUAGGI PUNTI <td>{}<tr>
<td>ASSE STORICO SOCIALE PUNTI <td>{}<tr>
<td>ASSE MATEMATICO PUNTI <td>{}<tr>
<td>ASSE SCENTIFICO TECNOLOGICO PUNTI <td>{}<tr>
<td><b>TOTALE CREDITI PUNTI </b><td><b style="color:blue">{}</b> <tr>
<td>Pertanto dichiara di ammettere l’alunna all’esame di qualifica.
</table>
<br>
"""
It’s all the same apart from the data. Finally I added this code to add the values in the placeholders and save all in an html file. Done.
html = ""
for pupils in data2:
html += start.format(*pupils)
#createfile("data2.html", html)
with open("data2.html", "w", encoding="utf-8") as file:
file.write(html)
os.startfile("data2.html")
Live coding video of the Excel to Html code in Python
I added this video (5/8/2019) to explain better the process I followed.
The whole code to put data from Excel to Word in a easy way and no modules:
import os
data = """
ARTENA MARIAPIA 2 2 3 3 3 3 3 19
BARTINA LUCIA 2 1 3 3 3 3 3 18
CORENA RAFFAELLA 3 3 3 2 2 2 2 17
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
ZARA SOFIA 2 2 3 3 3 3 4 20
""".splitlines()[1:]
print(data)
data2 = []
for eachstring in data:
data2.append(eachstring.split("\t"))
print(data2)
start = """
<b>{}</b><br>
Il Consiglio di classe, a conclusione del curricolo di studi, visto il giudizio analitico ed i voti complessivi riportati dall’alunn_ nelle singole discipline, considerato che la stessa non ha riportato insufficienze in alcuna disciplina, riporta il seguente credito per l’anno scolastico 2018/2019:
<table>
<td>AREA DI BASE PUNTI <td>{}<tr>
<td>AREA INDIRIZZO PUNTI <td>{}<tr>
<td>STAGE PUNTI <td>{}<tr>
<td>Anno scolastico 2014/2015 <td><tr>
<td>ASSE LINGUAGGI PUNTI <td>{}<tr>
<td>ASSE STORICO SOCIALE PUNTI <td>{}<tr>
<td>ASSE MATEMATICO PUNTI <td>{}<tr>
<td>ASSE SCIENTIFICO TECNOLOGICO PUNTI <td>{}<tr>
<td><b>TOTALE CREDITI PUNTI </b><td><b style="color:blue">{}</b> <tr>
<td>Pertanto dichiara di ammettere l’alunna all’esame di qualifica.
</table>
<br>
"""
html = ""
for pupils in data2:
html += start.format(*pupils)
#createfile("data2.html", html)
with open("data2.html", "w", encoding="utf-8") as file:
file.write(html)
os.startfile("data2.html")
This is an image of the input
This was the first of the students… followed by the other… So everytime I need to create this report, I just copy and paste the data.
Make it with pandas
There is also this way to do it in few lines with pandas:
import pandas as pd
dati = pd.read_excel("data.xlsx")
df = dati
df.to_html("my_table.html")
From this data in Excel
The result is this:
| date | name | age | hour | |
|---|---|---|---|---|
| 0 | 2020-01-01 | Mike Ross | 20 | 8 |
| 1 | 2020-01-01 | John Green | 21 | 4 |
Similar topics
