Python操作Excel生成HTML table
问题描述:项目上,客户需要将html页面上的表格里面的内容进行更新,更新信息来源于一张Excel表,客户手工编辑的话,比较麻烦,就寻求技术上的帮助。
环境工具:Win7(32bit) Python3.2 Excel
解决过程:1. 使用pip安装python操作excel的模块,如果是3.0版本以上,请安装 xlrd3和xlwt3;如果是3.0版本以下,请安装 xlrd和xlwt。
2. python脚本如下:
import os
import xlrd3
def open_excel(file= 'HKKH.xls'):
try:
data = xlrd3.open_workbook(file)
return data
except Exception as e:
print(e)
def main():
#tables = excel_table_byindex()
#for row in tables:
# print (row)
fw=open('C:\\Users\\HK\\Desktop\\Python\\HK.txt','a')
data = open_excel("C:\\Users\\HK\\Desktop\\Python\\HKKH.xls")
table = data.sheets()[0]
nrows = table.nrows
ncols = table.ncols
for rownum in range(2,nrows):
row = table.row_values(rownum)
exceline=''
if row:
for colnum in range(ncols):
wordhk=row[colnum]
if type(wordhk)==type(1.0):
wordhk=int(wordhk)
wordhk=str(wordhk)
if colnum==1:
if row[7].strip()=='':
exceline=exceline+'<tr bordercolor="#cccccc" clas=""><td width="195"><p class="myaa" align="left"><a class="myaa" href="#" target="_blank">'+wordhk+'</a></p></td>'
else:
exceline=exceline+'<tr bordercolor="#cccccc" clas=""><td width="195"><p class="myaa" align="left"><a class="myaa" href="'+row[7]+'" target="_blank">'+wordhk+'</a></p></td>'
if colnum==2:
exceline=exceline+'<td width="203"><p class="myaa" align="left">'+wordhk+'</p></td>'
if colnum==3:
exceline=exceline+'<td width="57"><p class="myaa" align="center">'+wordhk+'</p></td>'
if colnum==4:
exceline=exceline+'<td width="48"><p class="myaa" align="center">'+wordhk+'</p></td>'
if colnum==5:
exceline=exceline+'<td width="74"><p class="myaa" align="center">'+wordhk+'</p></td>'
if colnum==6:
exceline=exceline+'<td width="77"><p class="myaa" align="center">'+wordhk+'</p></td></tr>\n'
fw.write(exceline)
fw.close()
fr=open('C:\\Users\\HK\\Desktop\\Python\\HK.txt')
for line in fr.readlines():
print(line)
fr.close()
if __name__=="__main__":
main()
3. 得到的结果如下图: