We’ve seen a way to create tables in html using PythonCreate A Beautiful Html Table with Python.
This time we are going to make an html table, out of an excel file, using pandas, with some style.
This is the excel file (P.S.: do not need to be in color to have the result in html in color)
Here is the simple code to do it
import pandas as pd import os html = """<style> @import "https://fonts.googleapis.com/css?family=Montserrat:300,400,700"; .rwd-table { margin: 1em 0; min-width: 300px; } .rwd-table tr { border-top: 1px solid #ddd; border-bottom: 1px solid #ddd; } .rwd-table th { display: none; } .rwd-table td { display: block; } .rwd-table td:first-child { padding-top: .5em; } .rwd-table td:last-child { padding-bottom: .5em; } .rwd-table td:before { content: attr(data-th) ": "; font-weight: bold; width: 6.5em; display: inline-block; } @media (min-width: 480px) { .rwd-table td:before { display: none; } } .rwd-table th, .rwd-table td { text-align: left; } @media (min-width: 480px) { .rwd-table th, .rwd-table td { display: table-cell; padding: .25em .5em; } .rwd-table th:first-child, .rwd-table td:first-child { padding-left: 0; } .rwd-table th:last-child, .rwd-table td:last-child { padding-right: 0; } } h1 { font-weight: normal; letter-spacing: -1px; color: #34495E; } .rwd-table { background: #34495E; color: #fff; border-radius: .4em; overflow: hidden; } .rwd-table tr { border-color: #46637f; } .rwd-table th, .rwd-table td { margin: .5em 1em; } @media (min-width: 480px) { .rwd-table th, .rwd-table td { padding: 1em !important; } } .rwd-table th, .rwd-table td:before { color: #dd5; } </style> <script> window.console = window.console || function(t) {}; </script> <script> if (document.location.search.match(/type=embed/gi)) { window.parent.postMessage("resize", "*"); } </script>""" df = pd.read_excel("data.xlsx") df.to_html("data.html") with open("data.html") as file: file = file.read() file = file.replace("<table ", "<table class='rwd-table'") with open("data.html", "w") as file_to_write: file_to_write.write(html + file) os.startfile("data.html")
The result:
Conto | Descrizione | Dare | Avere | |
---|---|---|---|---|
0 | Banca x c/c | Versamento | 500 | 0 |
1 | Denaro in cassa | prelievo depositato in banca | 0 | 500 |
2 | Merci c/acquisto | ricevuta ft. n. 3 | 100 | 0 |
3 | IVA a Credito | iva su ft. nr. 3 | 22 | 0 |
4 | Debiti v/fornitori | ft. 3 | 0 | 122 |
The video explanation of the code
New version with filedialog tkinter to open excel file
In this version user will be asked to open an excel file to transform into html
import pandas as pd import os from tkinter import Tk from tkinter.filedialog import askopenfilename Tk().withdraw() filename = askopenfilename() table_class = "rwd-table" html = """<style> @import "https://fonts.googleapis.com/css?family=Montserrat:300,400,700"; .rwd-table { margin: 1em 0; min-width: 300px; } .rwd-table tr {border-top: 1px solid #ddd; border-bottom: 1px solid #ddd; } .rwd-table th {display: none; } .rwd-table td {display: block; } .rwd-table td:first-child {padding-top: .5em; } .rwd-table td:last-child {padding-bottom: .5em; } .rwd-table td:before {content: attr(data-th) ": "; font-weight: bold; width: 6.5em; display: inline-block; } @media (min-width: 480px) {.rwd-table td:before {display: none; } } .rwd-table th, .rwd-table td {text-align: left; } @media (min-width: 480px) {.rwd-table th, .rwd-table td {display: table-cell; padding: .25em .5em; } .rwd-table th:first-child, .rwd-table td:first-child {padding-left: 0; } .rwd-table th:last-child, .rwd-table td:last-child {padding-right: 0; } } h1 {font-weight: normal; letter-spacing: -1px; color: #34495E; } .rwd-table {background: #34495E; color: #fff; border-radius: .4em; overflow: hidden; } .rwd-table tr {border-color: #46637f; } .rwd-table th, .rwd-table td {margin: .5em 1em; } @media (min-width: 480px) {.rwd-table th, .rwd-table td {padding: 1em !important; } } .rwd-table th, .rwd-table td:before {color: #dd5; } </style> <script> window.console = window.console || function(t) {}; </script> <script> if (document.location.search.match(/type=embed/gi)) { window.parent.postMessage("resize", "*"); } </script>""" df = pd.read_excel(filename) df.to_html("data.html") with open("data.html") as file: file = file.read() file = file.replace("<table ", "<table class=\"" + table_class + "\" ") with open("data.html", "w") as file_to_write: file_to_write.write(html + file) os.startfile("data.html")
What if the data are in a different sheet than the first?
If you want to get the data from another sheet, in case you got multiple sheets, you can add this lines in the code above:
xls = pd.ExcelFile(filename) df = pd.read_excel(xls, "Foglio2")
where “Foglio2” is the name of the sheet in which the data are.