Layui练习(读取mysql数据库表并导出为word)

Layui的练习(读取mysql数据库表并导出为word)

本Demo主要是为了练习layui的使用(Layui+SSH)。

整体流程

页面首先将数据库的所有表名加载到下拉框中，由我们手动选择数据库表之后点击查询，然后将数据库表查询出来，利用分页查询，防止表中数据量过大造成浏览器卡死的情况，加载完成之后，依据是否需要进行导出，点击导出之后利用POI将数据库表导出到word文档中。

页面部分

html部分

<hr align=center width=100% color=black size="1" style="height: 3px;color: black">
<form class="layui-form" action="">
<div class="layui-inline">
    <label class="layui-form-label">选择数据表</label>
    <div class="layui-input-inline">
        <select id="select" name="modules" lay-filter="tablename" lay-verify="">
        </select>
    </div>
</div>
    <button type="button" id="query" class="layui-btn layui-btn-normal" style="width: 70px;height: 35px;margin-left: 10px" onclick="xxx()">查询</button>
    <button type="button" class="layui-btn layui-btn-normal" style="width: 70px;height: 35px;margin-left: 10px" onclick="word()">导出</button>
<table id="tableinfo" lay-filter="test"></table>

js部分

预加载部分，声明Layui模块化，同时从后台获取数据库中的所有数据表

$(function () {
        layui.use(['form','table'],function () {
            form = layui.form;
            var table = layui.table;
        });
            $.ajax({
                type: 'post',
                url: '/tabledata/gettablename.do',
                async: false,
                dataType: "json",
                cache: false,
                success: function (data) {
                    var htmls = '<option value = "">选择数据表</option>';
                    //动态给Layui下拉框赋值
                    $.each(data,function(index,item){
                        $('#select').append(new Option(item));// 下拉菜单里添加元素
                    });
                }
            });
    });

返回的json数据是一个json数组，数组内是表名，返回后用data接收

后台代码

 //下拉框表名
@Action("gettablename")
public void gettablename(){
    List<String> tablename = tableservice.tablename();
    this.printJson(JsonUtil.ObjectToJson(tablename));
}

Service层

    @Override
    public List<String> tablename() {
        List<String> tableName = mysqlUtil.getTableName();
        return tableName;
    }

获取数据库的所有表名

//获取数据库连接
public static Connection getConnection() throws Exception{
    Class.forName(driver);
    connection = DriverManager.getConnection(url, username, password);
    return connection;
}

    //获取数据库下的所有的表名
    public static List<String> getTableName() {
        List<String> tableNames = new ArrayList<>();
        ResultSet resultSet = null;
        try {
            connection = getConnection();//获取数据库连接
            DatabaseMetaData metaData = getmetaData();
            //参数输入为null代表不启用该种约束
            resultSet = metaData.getTables(null, null, null, new String[]{"TABLE"});
            while(resultSet.next()){
                tableNames.add(resultSet.getString(3));
            }
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            try {
                resultSet.close();
                closeConnection(connection);
            } catch (SQLException e) {
                e.printStackTrace();
            }
        }
        return tableNames;
    }

获取下拉框中选择的表名

  function gettablename() {
        var tablename = $("#select option:selected").text();
        return tablename;
    }

select是下拉框标签的id

获取Layui表头的数据格式

接受从后台返回的动态表头，用来构成Layui数据表格部分的col表头部分

 //接受后台传回的动态表头
    function col() {
        var tablename = gettablename();//获取下拉框中的表名
        var first = {};
        var res = [];
        $.ajax({
            type: 'post',
            async: false,//注意是同步
            url: '/tabledata/col.do?tablename='+tablename,
            success: function (data) {
                first = {type: 'checkbox'};//开启数据表格的第一列的复选框
                res.push(first);
                for (var i = 0; i<=data.length;i++){
                    for (var key in data[i]){
                        var tempinfo = {field: key,title: data[i][key]};
                        res.push(tempinfo);
                    }
                }
            }
        });
        return res;
    }

json数据格式，以某张表为例，都是表的列名{field：title}

[{"id":"id"},
 {"pid":"pid"},
 {"line_name":"line_name"},
 {"start_lng":"start_lng"},
 {"start_lag":"start_lag"},
 {"end_lng":"end_lng"},
 {"end_lag":"end_lag"},
 {"alarm_point":"alarm_point"}]

后台代码

//查询表格的表头列名
@Action("col")
public void tablecol() throws Exception {
    String tablename = request.getParameter("tablename");
    List<Map<String,String>> tablecol = tableservice.tablecol(tablename);
    this.printJson(JsonUtil.ObjectToJson(tablecol));
}

Service层

@Override
public List<Map<String,String>> tablecol(String tablename) throws Exception {
    List<Map<String,String>> list = tabledao.gettableColumns(tablename);
    List<Map<String,String>> colname = new ArrayList<>();
    for (int i = 0 ; i < list.size(); i++){
        HashMap<String,String> fieldtitlemap = new HashMap<>();
        fieldtitlemap.put(list.get(i).get("columnname"),list.get(i).get("columnname"));
        colname.add(fieldtitlemap);
    }
    return colname;
}

dao层

//获取一张表的列名
@Override
public List<Map<String,String>> gettableColumns(String tablename) throws Exception {
    String sql = "select COLUMN_NAME as columnname FROM information_schema.columns where table_name="+"'"+tablename+"'";
    List<Map<String,String>> list = baseDao.queryListJdbc(sql, null);//执行sql
    return list;
}

点击查询后渲染动态表格

使用layui数据表格和分页结合时：

1.接受前台传输的page和limit数据(当前页、每页数据)

2.sql查询时使用接受的page和limit进行分页查询

3.将要查询表的行总数填入表格格式的count字段(可见Layui官网的数据表格部分的参数说明)

 //查询渲染展示表格
    function xxx() {
        var tablename = gettablename();//获取下拉框中的表名
        cols = col();//获取格式部分的数据
        $.ajax({
            type: 'post',
            dataType: 'json',
            async: false,//注意是同步
            //page和limit应该是默认传输的，可能我的写法有问题，直接获取获取不到，所以我自定义了
            url: '/tabledata/selectdata.do?tablename='+tablename+"&page="+1+"&limit="+10,
            success: function (data) {
                var temp = {
                 "code":data['code'],//0
                 "msg":"",           //随便写
                 "count":data['count'],//表格中数据的总数count(1)
                 "data":data['data']//表格数据利用sql进行分页查询
             };
                layui.use(['table','layer'], function(){
                    var table = layui.table;
                    var tableinfo = table.render({
                        elem: '#tableinfo',
                        id: 'test',
                        url: '/tabledata/selectdata.do?tablename='+tablename,
                        limit: 10,
                        parseData: function (temp){//重新整理数据格式
                            return{
                                "code": temp['code'],
                                "msg": temp['msg'],
                                "count":temp['count'],
                                "data":temp['data']
                            }
                        },
                        //让表格单元格的宽度自适应
                        done: function (res, curr, count) {
                            $("table").css("width", "100%");
                        },
                        data: temp,
                        cols: [
                            cols//是一个全局变量var cols= col()
                        ],
                        //开启分页
                        page: true,
                    });
                });
            }
        });
    }

后台代码

//查询表格数据和具体信息
@Action("selectdata")
public void selectAll() throws Exception {
    String tablename = request.getParameter("tablename");
    String page = request.getParameter("page");
    String limit = request.getParameter("limit");
    Map<Object, Object> resultmap = tableservice.selectAll(tablename,page,limit);
    this.printJson(JsonUtil.ObjectToJson(resultmap));
}

Service层

//页面渲染表格时需要的数据
@Override
public Map<Object, Object> selectAll(String tablename,String page,String limit) throws Exception {
    Map<Object, Object> map = new LinkedHashMap<>();
    map.put("code", 0);
    map.put("msg", "");
    List<Map<String,String>> list1 = tabledao.gettableRow(tablename);//表的行数
    map.put("count", list1.get(0).get("row"));
    List list = tabledao.selectAllinfo(tablename,page,limit);//表的所有数据
    map.put("data", list);
    List list2 = tabledao.gettableColumns(tablename);//表的列名集合
    map.put("column", list2);
    List list3 = tabledao.gettableColumnsnum(tablename);//表的列数
    map.put("columnnum", list3);
    return map;
}

获取表的行数

//获取一张表的行数
@Override
public List gettableRow(String tablename) throws Exception {
    String sql = "select count(1) as row from "+ tablename;
    List list = baseDao.queryListJdbc(sql, null);
    return list;
}

获取表的所有数据(sql进行分页查询，page为当前页，limit为每页的显示数量)

 @Override
public List<Map<String, String>> selectAllinfo(String tablename,String page,String limit) throws Exception {
    String sql = "select * from "+tablename + " limit "+ (Integer.parseInt(page) -                         1)*Integer.parseInt(limit) + "," + limit;
    List list = baseDao.queryListJdbc(sql, null);
    return list;
}

获取一张表的所有列名

//获取一张表的列名
@Override
public List<Map<String,String>> gettableColumns(String tablename) throws Exception {
    String sql = "select COLUMN_NAME as columnname FROM information_schema.columns where                   table_name="+"'"+tablename+"'";
    List<Map<String,String>> list = baseDao.queryListJdbc(sql, null);
    return list;
}

获取一张表的列数

//获取一张表的列数
@Override
public List<Map<String,String>> gettableColumnsnum(String tablename) throws Exception {
    String sql ="select count(COLUMN_NAME) as columnname FROM information_schema.columns                  where table_name="+"'"+tablename+"'";
    List<Map<String,String>> list = baseDao.queryListJdbc(sql,null);
    return list;
}

导出为word

 function word() {
        var tablename = gettablename();//获取下拉框中的表名
        res = '';
        var rownum = '';
        var columnname = '';
        var columnnum = '';
        $.ajax({
            type: 'post',
            url: '/tabledata/selectdata.do?tablename='+tablename,
            async:false,
            success: function (data) {
                res=data['data'];//表格数据内容
                rownum=data['count'];//表格行数
                columnname=data['column'];//表格的列名集合
                columnnum=data['columnnum'][0]['columnname'];//列名数量
                debugger;
                $.ajax({
                    type: 'post',
                    url: '/word/toword.do',
                    async:false,
                    data: {
                        "row": rownum,//行数
                        "column": columnnum,//列数
                        "tablename":tablename,//表名
                        "res": JSON.stringify(res),//表格数据
                        "columnname": JSON.stringify(columnname)//列名集合
                    },
                    success:function (data) {
                        layui.use('layer',function () {//是否成功的窗口提示
                            var layer = layui.layer;
                            layer.open({
                                title: '数据导出',
                                content: data,
                            });
                        })
                    }
                });
            }
        });
    }

后台代码

@Action("toword")
public void tabletoword() throws Exception {
    String row = request.getParameter("row");//行数
    String column = request.getParameter("column");//列数
    String tablename = request.getParameter("tablename");//表名
    String columnname = request.getParameter("columnname");//列名

    Type type = new TypeToken<String>() {}.getType();
    String table = new Gson().fromJson(tablename,type);

    Type type1 = new TypeToken<List<Map<String,String>>>() {}.getType();
    List<Map<String,String>> columnnames = new Gson().fromJson(columnname,type1);

    List<Map<String,String>> datainfo = serviceddata.selectAllinfo(tablename);//表信息
    try {
        tabletoword.writeword(Integer.parseInt(row), Integer.parseInt(column), table,datainfo,columnnames);
        this.printJson(JsonUtil.ObjectToJson("导出成功"));
    }catch (Exception e){
        this.printJson(JsonUtil.ObjectToJson("导出失败"));
    }
}

Service层

//导出时的数据
@Override
public List<Map<String, String>> selectAllinfo(String tablename) throws Exception {
    List<Map<String, String>> list = tabledao.wordAllinfo(tablename);
    return list;
}

Dao直接获取所有数据

//获取一张表的所有数据(导出表格使用)
@Override
public List wordAllinfo(String tablename) throws Exception {
    String sql = "select * from "+tablename;
    List list = baseDao.queryListJdbc(sql, null);
    return list;
}

利用POI绘制表格并导出到word文档

/*
    * row：表格行数
    * column：表格列数
    * tablename：表名
    * data:表格所有数据
    * columnnames：表格列名集合
    *
    * */
public static void writeword(int row, int column, String tablename, List<Map<String,String>> data,List<Map<String,String>> columnnames) throws IOException {
    String docname = tablename+".docx";
    //新建一个文档
    XWPFDocument doc = new XWPFDocument();
    FileOutputStream OutputStream = new FileOutputStream(new File("文件路径"+docname));
    //创建一个表格
    XWPFTable table = doc.createTable(row+1,column);
    //获取表格的第一行
    XWPFTableRow row1 = table.getRow(0);
    for (int i = 0 ; i < row+1 ; i++){//按行遍历
        //填充首行列名
        if (i == 0) {
            for (int j = 0; j < column; j++) {//按列遍历(表头)
                row1.getCell(j).setText(columnnames.get(j).get("columnname"));
            }
        } else {
            XWPFTableRow row2 = table.getRow(i);
            for (int m = 0 ; m <column ; m++){//按列遍历(数据)
                row2.getCell(m).setText(String.valueOf(data.get(i-1).get(columnnames.get(m).get("columnname"))));
            }
        }
    }
    doc.write(OutputStream);
    OutputStream.close();
}