Make HTML TABLES with PANDAS with css Style

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.

Utilities

Published by pythonprogramming

Started with basic on the spectrum, loved javascript in the 90ies and python in the 2000, now I am back with python, still making some javascript stuff when needed.