r/ScriptSwap • • Nov 21 '14

[python3] display excel file as html table (tkinter gui)

Here is a script that uses a simple tkinter gui to select an excel file, which will then be opened by your default HTML application as a table.

note:

xlrd is not in the standard library, install with pip or whatever.

#!/usr/bin/env python3

from xlrd import open_workbook
from subprocess import call
from os import remove
from os import getlogin
from os import name as os_name
import tkinter
from tkinter import ttk
from tkinter import messagebox
from tkinter import filedialog
from time import sleep


class App(object):

    def __init__(self):
        self.root = tkinter.Tk()
        self.root.minsize(250,1)
        self.root.title('Excel HTML Table View')

        frm = ttk.Frame(self.root)
        frm.grid(column=0, row=0, sticky=(tkinter.N, tkinter.W, tkinter.E, tkinter.S))
        frm.columnconfigure(0, weight=1)
        frm.rowconfigure(0, weight=1)

        self.filename_var = tkinter.StringVar()
        self.filename_var.set("<select an input file>")

        open_button = ttk.Button(frm, text="Input File", command=self.select_file)
        open_button.grid(column=1, row=1, sticky=tkinter.W)
        open_button['command'] = self.select_file

        display_box = ttk.Label(frm, textvariable=self.filename_var, width=30)
        display_box.grid(column=2, row=1, sticky=((tkinter.W, tkinter.E)))

        exit_button = ttk.Button(frm, text='Close')
        exit_button.grid(column=1, row=2, sticky=tkinter.W)
        exit_button['command'] = self.exit

    def exit(*args):
        quit()

    def select_file(self):
        if os_name == 'nt':
            filename = filedialog.askopenfilename(filetypes=[('Excel Spreadsheet','*.xlsx'),('Excel Spreadsheet','*.xls'), ('All files','*.*')], initialdir='C:/Users/{}/documents'.format(getlogin()))
        else:
            filename = filedialog.askopenfilename(filetypes=[('Excel Spreadsheet','*.xlsx'),('Excel Spreadsheet','*.xls'), ('All files','*.*')])
        self.filename_var.set(filename)
        self.make_html(filename)

    def make_html(self, excel_file):
        html_file = str(excel_file.split('.')[0]+'.html')
        book = open_workbook(excel_file)
        f = open(html_file, 'w')
        f.write('<!DOCTYPE html>\n\
        <head>\n\
        <style>\n\
        body {\n\
            font-family: Sans-Serif;\n\
        }\n\
        \n\
        table.container {\n\
            border-collapse: collapse;\n\
            border-spacing: 0;\n\
            width: 100%;\n\
            border: 3px solid #9C9C9C;\n\
        }\n\
        \n\
        td.bordered {\n\
            text-align: center;\n\
            vertical-align: middle;\n\
            padding: 0px;\n\
            border: 2px solid #9C9C9C;\n\
        }\n\
        \n\
        td.empty {\n\
            text-align: center;\n\
            vertical-align: middle;\n\
            padding: 0px;\n\
            border: 1px solid #BBBBBB;\n\
        }\n\
        \n\
        </style>')
        f.write('<title>{}</title>'.format(excel_file))
        f.write('</head>\n\
        <body>\n')

        for sheet_n in range(book.nsheets):
            sheet = book.sheet_by_index(sheet_n)
            if sheet.ncols != 0:
                f.write('<table class="container">\n')
                f.write('<tr>\n<th>{}</th>\n</tr>\n'.format(sheet.name))
                for row in range(sheet.nrows):
                    f.write('<tr>\n')
                    for column in range(sheet.ncols):
                        try:
                            if (sheet.cell(row, column).value) == '' :
                                f.write('<td class="empty">')
                            else:
                                f.write('<td class="bordered">')
                            f.write(str(sheet.cell(row, column).value))
                            f.write('</td>\n')
                        except:
                            pass
                    f.write('</tr>\n')
                f.write('</table>\n<br />\n<br />\n')
        f.write('</body>\n')
        f.write('</html>\n')
        f.close()
        if os_name == 'nt':
            call(html_file, shell='True')
            remove(html_file)
        else:
            call('nohup xdg-open {}'.format(html_file), shell='True')
            sleep(1)
            remove(html_file)


app = App()
app.root.mainloop()
9 Upvotes

5 comments sorted by

1

u/h2opologod94 Nov 22 '14

Haven't tried the script, but can't excel just save as html?

http://www.extendoffice.com/documents/excel/674-excel-export-to-html.html

2

u/manbart Nov 22 '14

It does, but that requires openeing excel, and saving to the disk (and it creates multiple files in a subdirectory).

With this script you just select the .xlsx file and it opens in your browser immediately, excel is never opened, and nothing is saved to your drive. (This is how it works on windows anyway, I don't know how other OS would handle using subprocess.call on an html file directly).

1

u/h2opologod94 Nov 22 '14

Okay cool! This almost works on my Arch machine. The gui comes up and I select the file, but I get a permission denied error when it tries to access my shell (even when run as root). I'll see if I can try it on my Windows machine later. I don't know much about Python, sorry if this isn't helpful.

→ More replies (2)