If you need help with your excel table conversion, i provide such a service here 😀
Sometimes a database is really not necessary. Why not try a simple excel sheet
Coded this in an hour; sips on coffee though 9:06:34 PM
I generated the excel workbook using a website called Mockaroo. Today’s task is to convert a basic excel sheet table into an HTML table. I broke the task into sub tasks which were then later translated into functions.
PseudoCode
- Convert Excel Table into one dimensional List containing dictionaries
- Convert List into HTML document
- Save HTML string into a file with “.html” extension
- Done
The Excel Sheet looked like this:
The code generated an HTML table (using the style.css – see below) that looked like this:
This is the code
#Create a function to process excel data
#create another function to create html page
#https://github.com/Knio/dominate/blob/master/README.md
import logging, dominate, openpyxl, sys, os
from dominate.tags import *
#setup logging Debug
logging.basicConfig(level=logging.DEBUG, format="%(asctime)s - %(levelname)s - %(message)s")
#create excel sheet processor
def excel_sheet_processor(workbookfilepath: str):
'''
Converts Excel Sheet data into an array with dictionaries
arguments:
workbookfilepath: filepath to workbook
returns:
list
'''
#open workbook
wb = openpyxl.load_workbook(workbookfilepath)
#select active sheet
ws = wb.active
#define list
workbook_list = []
#define keys as a list
my_keys = []
for col in range(0, ws.max_column):
my_keys.append( ws.cell(row=1, column=col + 1).value )
#define loop to convert rows to dictionaries
for row in range( 2, ws.max_row ):
#create dictionary
dictionary = {}
for pos in range(0, len(my_keys)):
dictionary[my_keys[pos]] = ws.cell(row=row, column=pos+1).value
workbook_list.append(dictionary)
return workbook_list
def list_diction_to_html(workbook_list: list):
'''
Creates HTML Pages with table containing "list" data
arguments:
workbook_list; a list of dictionaries
returns:
html file path; str
'''
doc = dominate.document(title="Excel Spread Sheet") #sets html title tag
with doc.head:
link(rel="stylesheet", href="style.css")
with doc:
with div(id="excel_table").add(table()):
with thead():
#add header
dictionary = workbook_list[0]
for key in dictionary.keys():
table_header = td()
table_header.add(p(key))
for dictionary in workbook_list:
#loop through row; create table row
table_row = tr(cls="excel_table_row")
#loop through each key in dictionary
for key in dictionary:
with table_row.add(td()):
p(dictionary[key])
return str(doc) #turns the document into a string
def save_dom_to_html(dom):
'''
Saves DOM string into newly generated HTML file
arguments:
dom- str
returns:
filepath; str
'''
filepath = os.path.abspath("excel.html")
htmfile = open(filepath, "w")
htmfile.write(dom)
htmfile.close()
return filepath
if __name__ == "__main__":
filepath = os.path.abspath("MOCK_DATA.xlsx")
list_work = excel_sheet_processor(filepath)
if list_work:
dom = list_diction_to_html(list_work)
save_dom_to_html(dom)
logging.info("HTML file saved")
- Getting the HTML file name from the command line
- Create different tables based on different sheets in a workbook
- Create different HTML files based on different workbook (in a given directory)
- Convert HTML Table into Excel Sheet (well basically webscraping; using the python module: BeautifulSoup)
If you have any questions, write them in the comment section.
See you soon!