How to get data from excel and report them in html or in word in a nice readable mode without wasting time.
I have already posted this code sometime ago, but I wanted to talk a little more about this one because I think it’s useful and I also made a new video with the live coding of the script to have a better knowledge of the process to get the final output from the initial input. So… let’s go.
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")
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 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>
"""
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 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.
Similar topics
