Use python develop web page which can read and write excel file

The idea comes from school students information collection.

This prototype is setup a web page for user to input information and all the information will be saved to the Excel file. it will read the excel file and get the table head, and show it on the web page, user will fill the information for the table items.

Two python packages are used:

1. webpy is a python package which support python develop web page.

2. xlrd, xlwr, xlutils are python packages which support excel file access by python.

 

Host environment: ubuntu 16.4

1. Excel access: excel_test.py

import xlrd
import xlwt
import xlutils.copy

def open_excel(filename):
	workbook = xlrd.open_workbook(filename)
	print workbook.sheet_names()
	return workbook

def read_excel(workbook):
	
	sheet1_name = workbook.sheet_names()[0]
	sheet1 = workbook.sheet_by_name(sheet1_name)
	
	print "rows is %d" % (sheet1.nrows)
	
	print sheet1.cell(2,0).value
	print sheet1.cell(2,1).value
	print sheet1.cell(2,2).value
	return sheet1

def write_excel(sheet,row_index,name,study,special):
	sheet.write(row_index,0,name)
	sheet.write(row_index,1,study)
	sheet.write(row_index,2,special)

def get_writable_workbook(workbook):
	return xlutils.copy.copy(workbook)

2. web page: web_form_test.py

# -*- coding: utf-8 -*-
#!/usr/bin/env python
import web
from web import form
import excel_test

render = web.template.render('templates/',base='base')

urls =	(
	'/', 'index' ,
	'/admin' , 'admin'
	)
app = web.application(urls, globals())

org_workbook = excel_test.open_excel('special1.xlsx');
org_sheet = excel_test.read_excel(org_workbook)
write_workbook = excel_test.get_writable_workbook(org_workbook)
write_sheet = write_workbook.get_sheet(0)

text_box_index1=org_sheet.cell(2,0).value.encode('utf-8')
text_box_index2=org_sheet.cell(2,1).value.encode('utf-8')
text_box_index3=org_sheet.cell(2,2).value.encode('utf-8')

myform = form.Form(
            form.Textbox(text_box_index1),
            form.Textbox(text_box_index2),
            form.Textbox(text_box_index3),
)

class index:
    def __init__(self):
	self.form = 0

    def GET(self):
        global org_workbook
        global org_sheet

	org_workbook = excel_test.open_excel('special1.xlsx');
        org_sheet = excel_test.read_excel(org_workbook)

        self.form = myform()
        # make sure you create a copy of the form by calling it (line above)
        # Otherwise changes will appear globally
        print(self.form.render())
        return render.formtest(self.form)

    def POST(self):
	data = web.data()
	print "data is"
	print data
        self.form = myform() 
        if not self.form.validates(): 
            print(self.form.render())
            return render.formtest(self.form)
        else:
            global write_workbook
            global write_sheet
            write_workbook = excel_test.get_writable_workbook(org_workbook)
            write_sheet = write_workbook.get_sheet(0)
            excel_test.write_excel(write_sheet,
                                    org_sheet.nrows,
                                    self.form[text_box_index1].value,
                                    self.form[text_box_index2].value,
                                    self.form[text_box_index3].value
                                    )
            write_workbook.save('special1.xlsx')
            return "Great success! name is %s " % (self.form[text_box_index1].value)

class admin:
	admin_form=web.form.Form(
		web.form.Textbox('name', web.form.notnull, description='student names')
	)
	def GET(self):
		org_workbook = excel_test.open_excel('special1.xlsx');
		org_sheet = excel_test.read_excel(org_workbook)
		
		adform = self.admin_form()
		#special_list = org_sheet.col_values(0,2,org_sheet.nrows-1)
		special_list = org_sheet.get_rows()
		'''
		for row in special_list:
			for i in range(0,3):
				print row[i].value
		'''
		return render.admin(special_list,org_sheet.ncols)
		

if __name__=="__main__":
    web.internalerror = web.debugerror
    app.run()

Web Page needs templates:

base.html

$def with (page)

<html>
<head>
    <title>Special list</title>
</head>
<body>

$:page

</body>
</html>

formtest.html

$def with (form)

<div align="center">
<form name="main" method="post"> 
$if not form.valid: <p class="error">Try again:</p>
$:form.render()
<input type="submit" />
</form>
<div>

admin.html

$def with (rows,nclm )

<table border="1" align="center">
$for row in rows:
    <tr>
    $for i in range(0,nclm):
        <td>$row[i].value</td>
    </tr>    
</table> 

files in the folder:

put special1.xlsx, excel_test.py and web_form_test.py in the same folder, and make a folder 'templates' then put all three html files in this folder.

 

Test it:

I implemented Tow pages: One is for user to input information, another is for admin to check the information

the excel file I used is 'special1.xlsx'. The table header is :

Test 1:

open the brower and use 'localhost:8080/' to test the input page and it will looks like this:

after you input information and click 'submit', you should see this:

Now you can test another page -- the admin page:

Now you can see 'mouse' is add to the last row.