python 读取 excel 生成 html 页面优化

科技 07-09 来源：自观自学

效果

请耐心慢慢刷新到页面完整显示出来

https://fjxasdf.github.io/daogou/

哪些优化？

1.显示商品图片

2.没有显示全部商品，只显示热门商品，避免数据、图片加载太慢

3.样式稍微美化了

4.上传到GitHub，不过GitHub很卡，要刷新等好久才能看到

python读取excel生成json代码

import xlrd
from datetime import date,datetime
import json
file = '精选优质商品清单(内含优惠券).xls'
def read_excel():
 wb = xlrd.open_workbook(filename=file)#打开文件
 # print(wb.sheet_names())#获取所有表格名字
 sheet1 = wb.sheet_by_index(0)#通过索引获取表格
 # sheet2 = wb.sheet_by_name('Page1')#通过名字获取表格
 # print(sheet1)
 # print(sheet2)
 # print(sheet1.name) #表 名
 rows = sheet1.nrows #多少行
 # print(sheet1.ncols) #多少列
 # rows = sheet1.row_values(1)#获取行内容
 category0 = sheet1.col_values(4)#获取列内容(类目)
 del category0[0]
 category = sorted(set(category0),key=category0.index) #类目列表->去重
 data = []
 data_hot = []
 # print(rows)
 # print(cols)
 for i,v in enumerate(category):
 category[i] = v.replace("/", "、")#吧"/"替换"、"
 data.append([category[i],[]])
 jsonData = json.dumps(category, ensure_ascii=False)
 with open('./daogou/category.json', 'w',encoding="utf-8") as f:
 f.write(jsonData)#保存分类json
 for i,v in enumerate(data):
 for x in range(rows):
 if v[0] == (sheet1.cell(x,4).value.replace("/", "、")):
 xo = sheet1.row_values(x)
 y = [xo[1],xo[2],xo[4],xo[6],xo[21]] #选择保存商品名称、图片、链接等
 data[i][1].append(y)
 if x>0 and float(sheet1.row_values(x)[9]) >= 1: #选择佣金多的作为热门
 xo = sheet1.row_values(x)
 y = [xo[1],xo[2],xo[4],xo[6],xo[21]]
 data_hot.append(y)
 data_hot = data_hot[:24]
 jsonData_hot = json.dumps(data_hot, ensure_ascii=False)
 with open('./daogou/results_hot.json', 'w',encoding="utf-8") as f:
 f.write(jsonData_hot)#保存热门商品json
 for i,v in enumerate(data):
 jsonData = json.dumps(v[1], ensure_ascii=False)
 with open('./daogou/'+v[0]+'.json', 'w',encoding="utf-8") as f:
 f.write(jsonData)#保存每个分类商品json
 # jsonData1 = json.dumps(data, ensure_ascii=False)
 # with open('results.json', 'w',encoding="utf-8") as f:
 # f.write(jsonData1)
 # print(sheet1.cell(0,0).value)#获取表格里的内容,第一行第一个格内容
 # print(sheet1.cell_value(0,0))#获取表格里的内容,第一行第一个格内容
 #print(sheet1.row(0)[0].value)#获取表格里的内容,第一行第一个格内容
if __name__ == '__main__':
 	read_excel()

创建html页面，并引用json文件

<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<script src="../js/jquery.min.js"></script>
	<link rel="stylesheet" href="../font/Alibaba-PuHuiTi-Regular.css">
		<script>
		var navo = '';
var info = '';
var data_total;//总数据
function color16(){//十六进制颜色随机
	var r = Math.floor(Math.random()*256);
	var g = Math.floor(Math.random()*256);
	var b = Math.floor(Math.random()*256);
	var color = '#'+r.toString(16)+g.toString(16)+b.toString(16);
	return color;
}
function nav_href(title,id){//锚跳转
		if ($('#'+title).length>0) {
		location.href="#"+title;
		}else{
			var div_title = "<div id='"+title+"' style='float:left;'>";
			var div_content = "<div class='pro_img' style='background:#ff5000'><b style='color:#FFF'>"+title+"</b><\/div>";
			$('#load').show();
			$.get('./'+title+'.json', function(data) {
				$.each(data, function(index, val) {
					div_content+="<a style='background:"+color16()+"' target='_blank' href='"+val[4]+"' class='pro_img'><img onload='$(this).show()' src="+val[1]+" style='width:100%;height:100%;display:none;' />"+val[0]+"<span class='money'>￥"+val[3]+"</span><span class='title'>"+val[0].substring(0,25)+"...</span><\/a>";
					if ((index+1)==data.length) {
						var div_footer ="</div><br>";
						infoo=div_title+div_content+div_footer;
						$('#content').append(infoo);
						location.href="#"+title;
						$('#load').hide();
					}
						 });
			},'json');
			
						 
			
		}
		
	
}
function get_data(){
	$.get('./category.json', function(data) {//导航
				$.each(data, function(index, val) {
					 // console.log(val[0]);
					 navo+="<a class='nav' href=javascript:;nav_href(\'"+val+"\',"+index+")>"+val+"</a>　";
				});
	$('#nav').html(navo);
	$.get('./results_hot.json', function(data) {//热门商品
					var div_title = "<div id='热门商品' style='float:left;'>";
					 var div_content = "<div class='pro_img' style='background:#ff5000'><b style='color:#FFF'>热门商品</b></span><\/div>";
		$.each(data, function(index, val) {
					 // console.log(val);
					 
					 	 div_content+="<a style='background:"+color16()+"' target='_blank' href='"+val[4]+"' class='pro_img'><img onload='$(this).show()' src="+val[1]+" style='width:100%;height:100%;display:none;' />"+val[0]+"<span class='money'>￥"+val[3]+"</span><span class='title'>"+val[0].substring(0,25)+"...</span><\/a>"
					 
					 
					 // if (index==3) {return false}
				});
		var div_footer ="</div><br>";
		info+=div_title+div_content+div_footer;
	$('#content').html(info);
	$('#load').hide(0);	
	});
			},'json');
}
	
		
	</script>
	<style>
	#body{
		/*border: 1px solid #eee;*/
		width: 1110px;
		margin:0 auto;
	}
	a.nav{
		text-decoration: none;
		margin-right: 10px;
		padding: 0 5px;
		/*padding-bottom: 10px;*/
		display: inline-block;
		color: #000;
 font-weight: 700;
	}
	#nav{
		display: inline-block;
	}
*,html,body{
	font-family:"Alibaba-PuHuiTi-Regular";
}
		#content{
			margin-top: 10px;
			display: inline-block;
		}
		.money{
	 width: 100%;
 /* padding: 0px 10px; */
 background: #ffffffcc;
 position: absolute;
 left: 0;
 bottom: 50px;
 height: 30px;
 line-height: 30px;
 color: #F40;
 font-weight: 700;
 /* border-radius: 0 8px 0 0; */
 text-align: left;
 font-size: 18px;
		}
				.title{
 width: 100%;
 font-weight: 700;
 /* padding: 0px 10px; */
 background: #ffffffcc;
 position: absolute;
 left: 0;
 bottom: 0;
 height: 50px;
 line-height: normal;
 color: #000;
 /* border-radius: 0 8px 0 0; */
 text-align: left;
 font-size: 15px;
		}
		.pro_img{
			position: relative;
			float: left;
			width: 220px;
			height: 220px;
			line-height: 220px;
			text-align: center;
			border: 1px solid #eee;
			cursor: pointer;
			font-size: 30px;
			/*white-space:normal; */
			overflow:hidden; /*超过部分不显示*/
 　　　　　text-overflow:ellipsis; /*超过部分用点点表示*/
 /*　　　　　white-space:nowrap;/*不换行*/
		}
		.input1{
			 width: 300px;
			 height: 30px;
			 border: 1px solid #888;
				border-radius: 10px 0 0 10px;
				outline-style: none ;
	
		}
		.button1{
			margin-left: -7px;
 width: 50px;
 height: 34px;
 border-radius:0 10px 10px 0 ;
 outline-style: none ;
		}
		#search1{
			/*width: 350px;*/
			/*margin:0 auto;*/
		}
		#load{
			position: fixed;
			width: 100%;
			height: 100%;
			top: 0;
			left: 0;
			background: #000000ad;
			z-index: 999;
			text-align: center;
			vertical-align: middle;
		}
		#load>span{
 display: inline-block;
 vertical-align: middle;
 height: 100%;
		}
		.load{
			display: inline-block;
			vertical-align: middle;
			font-size: 50px;
			color: #FFF;
		}
	</style>
</head>
<body onload="get_data()">
<!-- loading -->
<div id="load">
	<span ></span>
	<div class="load">加载中...</div>
</div>
<div id="body">
<h1>网站仅学习交流！！网站中的商品信息均来自于互联网。</h1>
<!-- 		<div id="search1">
		<input type="text" class="input1">
		<button class="button1">搜索</button><b>　网站仅学习交流！！网站仅学习交流！！网站仅学习交流！！网站仅学习交流！！网站仅学习交流！！</b>
	</div> -->
	<div id="nav"></div>
	<div id="content"></div>
</div>
</body>
</html>