csv,exl自动提取表头两列英文字段按英文名称排序显示

今天做的是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());
}