今天做的是csv,exl自动提取表头两列英文字段按英文名称排序显示
首先是html界面的设计部分：
通过获取mysql中历史数据的表格来进行相关的操作
效果：
然后将获取到的数据进行显示；
先看一下数据库的数据
然后将数据显示到前端界面
点击字典功能能将mysql中的数据显示出来
点击删除能将字段删除
代码部分：
前端html:<div class="layui-body">
<div class="HisTabDiv">
</br>
<h2 align="center">历史数据表格</h2>
</br>
<table class="layui-hide" id="HisTab" lay-filter="HisEvent"></table>
<script type="text/html" id="barDemo">
<a class="layui-btn layui-btn-primary layui-btn-xs" lay-event="detail">字典</a>
<a class="layui-btn layui-btn-xs" lay-event="out">导出</a>
<a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
</script>
<script type="text/html" id="DictMethod">
<a class="layui-btn layui-btn-danger layui-btn-xs" lay-event="del">删除</a>
</script>
</div>
</div>
<div class="layui-footer">
</div>
</div>
<script src="./layui/layui.js"></script>
<script src="js/jquery.js"></script>
<script>
layui.use('table', function () {
var table = layui.table;
table.render({
elem: '#HisTab'
, url: 'servlet?method=HisTab'
,1000
, cellMinWidth: 200 //全局定义常规单元格的最小宽度，layui 2.2.1 新增
, cols: [[
{field: 'tabname', title: '数据名称' ,align: 'center',event: 'ChangeTabname'}
, {field: 'crtime', 160, title: '创建时间',edit: 'text'}
, {field: 'user', 160, title: '创建者ID', sort: true}
, {fixed: 'right', title:"操作", 178, align:'center', toolbar: '#barDemo'}
]]
});
layui.use('table', function(){
var table = layui.table;
//监听单元格事件
table.on('tool(HisEvent)', function(obj){
var data = obj.data;
if(obj.event === 'detail'){
PopWindows(data.tabname);
} else if(obj.event === 'del'){
layer.confirm('确定删除这个数据集吗？', function(index){
DeleteOneHistory(data.tabid,data.tabname);
obj.del();
layer.close(index);
});
}
else if(obj.event === 'ChangeTabname'){
layer.prompt({
formType: 2
,title: '将表名修改为:'
,value: data.tabname
}, function(value, index){
layer.close(index);
//这里一般是发送修改的Ajax请求
ChangeTabName(data.tabname,value);
//同步更新表格和缓存对应的值
obj.update({
tabname: value
});
});
}
else if(obj.event === 'out'){
OutData(data.tabname);
}
});
table.on('tool(demoEvent)', function(obj){
var data = obj.data;
if(obj.event === 'setbeizhu'){
layer.prompt({
formType: 2
,title: '修改 ID 为 ['+ data.ywname +'] 的用户签名'
,value: data.beizhu
}, function(value, index){
layer.close(index);
//这里一般是发送修改的Ajax请求
//同步更新表格和缓存对应的值
obj.update({
beizhu: value
});
});
}
});
});
});
</script>
<script>
function PopWindows(name){
layer.open({
type: 2,
area: ['1000px', '700px'],
title: name+"字典",
fixed: false, //不固定
maxmin: true,
content: 'PopTable.html?tabname='+name,
success: function (layero, index) {
var body = layer.getChildFrame('body', index);
body.find("[name='tabname']").val(name);
}
});
}
</script>
<script>
function OutData(TName){
var url = "servlet?method=OutData&tabname="+TName;
$.ajax({
type: "get",
url: url,
data:[],
dataType: "json",
success: function(result){
layer.alert('导出完成');
},
error: function(){
alert("error");
}
});
}
</script>
<script>
function ChangeTabName(Tname,NewName){
var url = "servlet?method=ChangeTabName&tabname="+Tname+"&NewName="+NewName;
$.ajax({
type: "get",
url: url,
data:[],
dataType: "json",
success: function(result){
},
error: function(){
alert("error");
}
});
}
</script>
<script>
function DeleteOneHistory(tabid,tname){
var url = "servlet?method=DeleteOneHistory&tabid="+tabid+"&tabname="+tname;
$.ajax({
type: "get",
url: url,
data:[],
dataType: "json",
success: function(result){
},
error: function(){
alert("error");
}
});
}
</script>
<script>
//JS
layui.use(['element', 'layer', 'util'], function(){
var element = layui.element
,layer = layui.layer
,util = layui.util
,$ = layui.$;
//头部事件
util.event('lay-header-event', {
//左侧菜单事件
menuLeft: function(othis){
layer.msg('展开左侧菜单的操作', {icon: 0});
}
,menuRight: function(){
layer.open({
type: 1
,content: '<div style="padding: 15px;">处理右侧面板的操作</div>'
,area: ['260px', '100%']
,offset: 'rt' //右上角
,anim: 5
,shadeClose: true
});
}
});
});
</script>
bean层：
package classes;
public class HisTabBean {
private int id;
private String tabname;
private String crtime;
private int user;
public int getId() {
return id;
}
public void setId(int id) {
this.id = id;
}
public String getTabname() {
return tabname;
}
public void setTabname(String tabname) {
this.tabname = tabname;
}
public String getCrtime() {
return crtime;
}
public void setCrtime(String crtime) {
this.crtime = crtime;
}
public int getUser() {
return user;
}
public void setUser(int user) {
this.user = user;
}
public HisTabBean(int id, String tabname, String crtime, int user) {
this.id = id;
this.tabname = tabname;
this.crtime = crtime;
this.user = user;
}
}
dao层
public List<SqlTableBean> getTableAll(String TableName){
String sql="select * from "+TableName+"";
Connection conn = Dbutil.getConnection();
Statement st=null;
List<SqlTableBean> list=new ArrayList<>();
ResultSet rs=null;
SqlTableBean bean=null;
try {
st=conn.createStatement();
st.executeQuery(sql);
rs=st.executeQuery(sql);
while(rs.next()) {
String ywname=rs.getString("ywname");
String zwname=rs.getString("zwname");
String danwei=rs.getString("danwei");
String zdtype=rs.getString("zdtype");
String beizhu=rs.getString("beizhu");
String ispri=rs.getString("ispri");
String crtime=rs.getString("crtime");
String chtime=rs.getString("chtime");
String user=rs.getString("user");
bean =new SqlTableBean(ywname,zwname,danwei,zdtype,beizhu,ispri,crtime,chtime,user);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
Dbutil.close(st, conn);
}
return list;
}
public void FirInsertToHis(String TableName,String user) throws SQLException {
SimpleDateFormat formatter= new SimpleDateFormat("yyyy/MM/dd HH:mm:ss");
Date date = new Date(System.currentTimeMillis());
String crtime=formatter.format(date);
String sql="insert into hisdata (tabname,crtime,user) values('"+TableName+"','"+crtime+"','"+user+"')";
Connection conn= Dbutil.getConnection();
PreparedStatement pt = conn.prepareStatement(sql);
Statement st=null;
ResultSet rs=null;
try
{
st=conn.createStatement();
st.executeUpdate(sql);
}catch(Exception e)
{
e.printStackTrace();
}
finally
{
Dbutil.close(rs, st, conn);
}
}
public List<HisTabBean> HisTab(){
String sql="select * from hisdata";
Connection conn = Dbutil.getConnection();
Statement st=null;
List<HisTabBean> list=new ArrayList<>();
ResultSet rs=null;
HisTabBean bean=null;
try {
st=conn.createStatement();
st.executeQuery(sql);
rs=st.executeQuery(sql);
while(rs.next()) {
int id=rs.getInt("tabid");
String tabname=rs.getString("tabname");
String crtime=rs.getString("crtime");
int user=rs.getInt("user");
bean =new HisTabBean(id,tabname,crtime,user);
list.add(bean);
}
} catch (SQLException e) {
e.printStackTrace();
}
finally{
Dbutil.close(st, conn);
}
return list;
}
servlet层：
public void HisTab( HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException, SQLException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
List<HisTabBean> list=dao.HisTab();
JSONArray json=new JSONArray();
for(int i=0;i<list.size();i++){
JSONObject ob=new JSONObject();
ob.put("tabid",list.get(i).getId());
ob.put("tabname",list.get(i).getTabname());
ob.put("crtime",list.get(i).getCrtime());
ob.put("user",list.get(i).getUser());
json.add(ob);
}
JSONObject ob=new JSONObject();
ob.put("code", 0);
ob.put("msg", "");
ob.put("count",1);
ob.put("data",json);
PrintWriter out = response.getWriter();
out.write(ob.toString());
}
public void PopTable( HttpServletRequest request,HttpServletResponse response) throws ServletException, IOException, SQLException {
request.setCharacterEncoding("utf-8");
response.setContentType("text/html;charset=UTF-8");
String tabname=request.getParameter("tabname");
List<SqlTableBean> list=dao.getTableAll(tabname);
JSONArray json=new JSONArray();
for(int i=0;i<list.size();i++){
JSONObject ob=new JSONObject();
ob.put("ywname",list.get(i).getYwname());
ob.put("zwname",list.get(i).getZwname());
ob.put("danwei",list.get(i).getDanwei());
ob.put("zdtype",list.get(i).getZdtype());
ob.put("beizhu",list.get(i).getBeizhu());
ob.put("ispri",list.get(i).getIspri());
ob.put("chtime",list.get(i).getChtime());
json.add(ob);
}
JSONObject ob=new JSONObject();
ob.put("code", 0);
ob.put("msg", "");
ob.put("count",1);
ob.put("data",json);
PrintWriter out = response.getWriter();
out.write(ob.toString());
}