导出Excel报表+分页中的多重分页

最新推荐文章于 2021-12-08 09:13:47 发布

梵墨白

最新推荐文章于 2021-12-08 09:13:47 发布

阅读量252

点赞数

分类专栏： java 前端

本文链接：https://blog.csdn.net/weixin_45501398/article/details/104304131

版权

java 同时被 2 个专栏收录

13 篇文章 0 订阅

订阅专栏

前端

3 篇文章 0 订阅

订阅专栏

完成样图

前段代码实现

<!DOCTYPE html>
<html xmlns:th="http://www.thymeleaf.org">
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<head th:include="include :: header"></head>
<body class="gray-bg">
<div class="layui-form">
    <div class="layui-form-item" style="position: relative; left: 50px;top: 30px;height: 80px">
        <div class="layui-inline">
            <div class="layui-input-inline">
                <input type="text" class="layui-input" id="account" placeholder="场景名称">
            </div>
        </div>

        <div class="layui-inline">
            <div class="layui-input-inline">
                <button class="btn btn-success" οnclick="reLoad()">查询</button>
            </div>
        </div>
        <div class="layui-inline">
            <div class="layui-input-inline">
                <button class="btn btn-success" οnclick="exports()">导出近七天的报表</button>
            </div>
        </div>
    </div>
</div>
<table id="exampleTable" data-mobile-responsive="true">
</table>
<div th:include="include :: footer"></div>
<script>
    //存放 场景id
    var array = new Array();

    var prefix = "/bubble/report"
    $(function () {
        load();
    });
    function load() {
        $('#exampleTable').bootstrapTable(
            {
                method: 'get', // 服务器数据的请求方式 get or post
                url: prefix + "/tochartList", // 服务器数据的加载地址
                //	showRefresh : true,
                //	showToggle : true,
                //	showColumns : true,
                iconSize: 'outline',
                toolbar: '#exampleToolbar',
                striped: true, // 设置为true会有隔行变色效果
                dataType: "json", // 服务器返回的数据类型
                pagination: true, // 设置为true会在底部显示分页条
                // queryParamsType : "limit",
                // //设置为limit则会发送符合RESTFull格式的参数
                singleSelect: false, // 设置为true将禁止多选
                // contentType : "application/x-www-form-urlencoded",
                // //发送到服务器的数据编码类型
                pageSize: 10, // 如果设置了分页，每页数据条数
                pageNumber: 1, // 如果设置了分布，首页页码
                //search : true, // 是否显示搜索框
                showColumns: false, // 是否显示内容下拉框（选择显示的列）
                sidePagination: "server", // 设置在哪里进行分页，可选值为"client" 或者 "server"
                queryParams: function (params) {
                    return {
                        //说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对
                        limit: params.limit,
                        offset: params.offset,
                        names: $.trim($('#account').val()),
                    };
                },
                // //请求服务器数据时，你可以通过重写参数的方式添加一些额外的参数，例如 toolbar 中的参数 如果
                // queryParamsType = 'limit' ,返回参数必须包含
                // limit, offset, search, sort, order 否则, 需要包含:
                // pageSize, pageNumber, searchText, sortName,
                // sortOrder.
                // 返回false将会终止请求
                columns: [
                    {
                        field: 'id',
                        // title: '冒泡场景数据统计',
                        formatter: function (value, row, index) {
                            array.push(row.busi_id);
                            setTimeout(Loading, 800);
                            return '<table id="' + row.busi_id + '" data-mobile-responsive="true"></table>';
                        }
                    }
                ]
            }, setTimeout(Loading, 800));
    };
    /**
     *导出excel
     */
    function exports(nums) {
        layer.confirm('确定导出冒泡数据的信息？', {
            btn: ['确定', '取消']
        }, function (index) {
            //按钮【按钮一】的回调
            location.href = prefix + '/exports/' + nums;
            layer.close(index);
        }, function (index) {
            //按钮【按钮二】的回调
        });
        // layer.confirm("确认导出数据吗?", {
        //     btn: ['确定', '取消']
        //     // 按钮
        // }, function () {
        //
        //
        // }, function () {
        //
        // })
    }
    /**
     * 加载图形的方法
     * @constructor
     */
    function Loading() {
        for (var i = 0; i < array.length; i++) {
            buttonLoading(i)
            Loading2(i);
        }
        array = null;
        array = new Array();
        // 使用刚指定的配置项和数据显示图表。
        // myChart.setOption(option);
        // myChart2.setOption(option);
    }
    function buttonLoading(i) {
        var numa = parseInt(array[i]);
        var button = '<button class="btn btn-success" style="position: relative;left: 1050px;" οnclick="exports(' + numa + ')">导出场景报表</button>';
        $('#' + array[i]).before(button);
    }

    /**
     * 根据表格的id查询
     * @param myChart
     * @param i array的下标
     * @constructor
     */
    function Loading2(i) {
        //生成按钮


        $('#' + array[i]).bootstrapTable(
            {
                method: 'get', // 服务器数据的请求方式 get or post
                url: prefix + "/getBid/" + array[i], // 服务器数据的加载地址
                //	showRefresh : true,
                //	showToggle : true,
                //	showColumns : true,
                iconSize: 'outline',
                toolbar: '#' + array[i],
                striped: true, // 设置为true会有隔行变色效果
                dataType: "json", // 服务器返回的数据类型
                pagination: true, // 设置为true会在底部显示分页条
                // queryParamsType : "limit",
                // //设置为limit则会发送符合RESTFull格式的参数
                singleSelect: false, // 设置为true将禁止多选
                // contentType : "application/x-www-form-urlencoded",
                // //发送到服务器的数据编码类型
                pageSize: 10, // 如果设置了分页，每页数据条数
                pageNumber: 1, // 如果设置了分布，首页页码
                //search : true, // 是否显示搜索框
                showColumns: false, // 是否显示内容下拉框（选择显示的列）
                sidePagination: "server", // 设置在哪里进行分页，可选值为"client" 或者 "server"
                queryParams: function (params) {
                    return {
                        //说明：传入后台的参数包括offset开始索引，limit步长，sort排序列，order：desc或者,以及所有列的键值对
                        limit: params.limit,
                        offset: params.offset,
                        names: $.trim($('#account').val()),
                    };
                },
                // //请求服务器数据时，你可以通过重写参数的方式添加一些额外的参数，例如 toolbar 中的参数 如果
                // queryParamsType = 'limit' ,返回参数必须包含
                // limit, offset, search, sort, order 否则, 需要包含:
                // pageSize, pageNumber, searchText, sortName,
                // sortOrder.
                // 返回false将会终止请求
                columns: [
                    {
                        field: 'busi_name',
                        title: '场景名',
                        width: 180
                    }, {
                        field: 'busi_id',
                        title: '场景id',
                        width: 180
                    }, {
                        field: 'priority',
                        title: '优先级'
                        , width: 180
                    }, {
                        field: 'target_user_number',
                        title: '目标用户数',
                        align: 'center'
                        , width: 180
                    }, {
                        field: 'push_reach_number',
                        title: '用户到达冒泡数据'
                        , width: 180
                    }, {
                        field: 'click_return_number',
                        title: '点击返回键的用户数'
                        , width: 180
                    }, {
                        field: 'click_fix_number',
                        title: '点击确定键的用户数'
                        , width: 180
                    }

                ]
            });
    }


    $("body").keydown(function () {
        if (event.keyCode == "13") {//keyCode=13是回车键
            $('#reLoad').click();
        }
        ;
    });

    function reLoad() {
        $.ajax({
            type: 'GET',
            data: {
                "limit": 10,
                "offset": 0,
                "names": $.trim($('#account').val()),
            },
            url: prefix + '/tochartList',
            success: function (data) {
                $('#exampleTable').bootstrapTable("load", data);
                setTimeout(Loading, 800);
            }
        })
    }
</script>
</body>
</html>

控制层代码


@Controller
@RequestMapping("/bubble/report")
public class BubbleReportController {

    @Autowired
    JdbcTemplate jdbcTemplate;

    @Autowired
    BubbleReportService bubbleReportService;
    /**
     * 冒泡数据详细报表
     *
     * @return
     */
    @GetMapping("/tochart4")
    @RequiresPermissions("market:activityStatistics:chart4")
    public String Tochart4() {
        return "market/activityStatistics/chart4";
    }

    /**
     * 查出所有的数据
     * @return
     */
    @GetMapping("/tochartList")
    @ResponseBody
    public PageUtils tochartList(@RequestParam Map<String, Object> params){
        Object offset = params.get("offset");
        //显示数量
        Object limit = params.get("limit");
        //场景名称
        Object senceName=params.get("names");

        //查询数据sql
        StringBuffer sql=new StringBuffer("select * from  bubble_report   where true ");
        //查询总数sql2
        StringBuffer sql2=new StringBuffer("SELECT count(*) as count FROM `bubble_report` where true");
        if (senceName != null && senceName.toString().length() > 1) {
            sql.append(" and busi_name like '%" + senceName + "%' or  busi_id = '"+senceName+"'");
            sql2.append(" and busi_name like '%" + senceName + "%' or  busi_id = '"+senceName+"'");
        }
        sql.append("GROUP BY  busi_id  limit " + offset + "," + limit);
        //查询列表数据
        List<Map<String, Object>> blackListList = jdbcTemplate.queryForList(sql.toString());

        int total = Convert.toInt(jdbcTemplate.queryForMap(sql2.toString()).get("count").toString());
        PageUtils pageUtils = new PageUtils(blackListList, total);
        return pageUtils;
    }

    /**
     * 根据场景id获取报表数据
     *
     * @return
     */
    @ResponseBody
    @RequestMapping("/getBid/{id}")
    public PageUtils getBid(@PathVariable("id") Integer id, @RequestParam Map<String, Object> params) {
        //起始数
        Object offset = params.get("offset");
        //显示数量
        Object limit = params.get("limit");
        //场景名称
        Object senceName=params.get("names");
        //查询数据sql
        StringBuffer sql =  new StringBuffer("select * from bubble_report where busi_id="+id);
        //查询总数sql
        StringBuffer sql2 = new StringBuffer("select count(1) as count from bubble_report where busi_id="+id);

        if (senceName != null && senceName.toString().length() > 1) {
            sql.append(" and busi_name like '%" + senceName + "%' or  busi_id = '"+senceName+"'");
            sql2.append(" and busi_name like '%" + senceName + "%' or  busi_id = '"+senceName+"'");
        }
        sql.append(" limit " + offset + "," + limit);
        //查询列表数据
        List<Map<String, Object>> blackListList = jdbcTemplate.queryForList(sql.toString());

        int total = Convert.toInt(jdbcTemplate.queryForMap(sql2.toString()).get("count"));
        PageUtils pageUtils = new PageUtils(blackListList, total);
        return  pageUtils;
    }
    @RequestMapping("/exports/{id}")
    @ResponseBody
    public void exports(@PathVariable("id") String id,HttpServletRequest request, HttpServletResponse response) {
        Map<String,Object> map=new HashMap<>();
        if (StringUtils.isNotBlank(id)&&!id.equals("undefined")) {
            map.put("busiid",id);
        }
        List<BubbleReportDO> list = bubbleReportService.list(map);
        try {
            ServletOutputStream out = response.getOutputStream();
            ExcelWriter writer = new ExcelWriter(out, ExcelTypeEnum.XLSX, true);
            String fileName = new String("冒泡数据详细报表"
                    .getBytes("utf-8"), "iso8859-1");
            Sheet sheet1 = new Sheet(1, 0, BubbleReportDO.class);
            sheet1.setSheetName("sheet1");
            //表格样式
            short i=9;
            TableStyle tableStyle=new TableStyle();
            //设置样式
            Font font=new Font();
            font.setBold(true);
            font.setFontHeightInPoints(i);
            tableStyle.setTableHeadFont(font);
            tableStyle.setTableHeadBackGroundColor(IndexedColors.WHITE);
            tableStyle.setTableContentBackGroundColor(IndexedColors.WHITE);
            tableStyle.setTableContentFont(font);
            //设置表格样式
            sheet1.setTableStyle(tableStyle);
            sheet1.setAutoWidth(Boolean.TRUE);
            response.setHeader("Content-disposition", "attachment;filename="+fileName+".xlsx");
            response.setContentType("multipart/form-data");
            response.setCharacterEncoding("utf-8");
            writer.write(list, sheet1);
            writer.finish();
            out.flush();
        } catch (Exception e) {
            e.printStackTrace();
        }
    }
}

模型代码


public class BubbleReportDO extends BaseRowModel implements Serializable {

    private static final long serialVersionUID = 15353L;

    public Integer getBusiid() {
        return busiid;
    }

    public void setBusiid(Integer busiid) {
        this.busiid = busiid;
    }

    public String getBusiname() {
        return businame;
    }

    public void setBusiname(String businame) {
        this.businame = businame;
    }

    public Integer getPriority() {
        return priority;
    }

    public void setPriority(Integer priority) {
        this.priority = priority;
    }

    public Integer getTargetusernumber() {
        return targetusernumber;
    }

    public void setTargetusernumber(Integer targetusernumber) {
        this.targetusernumber = targetusernumber;
    }

    public Integer getPushreachnumber() {
        return pushreachnumber;
    }

    public void setPushreachnumber(Integer pushreachnumber) {
        this.pushreachnumber = pushreachnumber;
    }

    public Integer getClickreturnnumber() {
        return clickreturnnumber;
    }

    public void setClickreturnnumber(Integer clickreturnnumber) {
        this.clickreturnnumber = clickreturnnumber;
    }

    public Integer getClickfixnumber() {
        return clickfixnumber;
    }

    public void setClickfixnumber(Integer clickfixnumber) {
        this.clickfixnumber = clickfixnumber;
    }

    private Integer id;

    @ExcelProperty(value = "场景id", index = 0)
    private Integer busiid;

    @ExcelProperty(value = "场景名称", index = 1)
    private String businame;

    @ExcelProperty(value = "优先级", index = 2)
    private Integer priority;
    @ExcelProperty(value = "目标用户数", index = 3)
    private Integer targetusernumber;
    public static long getSerialVersionUID() {
        return serialVersionUID;
    }
    public Integer getId() {
        return id;
    }
    public void setId(Integer id) {
        this.id = id;
    }
    @ExcelProperty(value = "用户到达冒泡数据", index = 4)
    private Integer pushreachnumber;

    @ExcelProperty(value = "点击返回键的用户数", index = 5)
    private Integer clickreturnnumber;

    @ExcelProperty(value = "点击确定键的用户数", index = 6)
    private Integer clickfixnumber;
}

需要导入的包

	<!--easyExcel-->
		<dependency>
			<groupId>com.alibaba</groupId>
			<artifactId>easyexcel</artifactId>
			<version>1.1.2-beat1</version>
		</dependency>
		<!-- POI -->
		<dependency>
		      <groupId>org.apache.poi</groupId>
		      <artifactId>poi</artifactId>
		      <version>3.15</version>
		</dependency>
		<dependency>
		      <groupId>org.apache.poi</groupId>
		      <artifactId>poi-ooxml</artifactId>
		      <version>3.15</version>
		</dependency>

注意: POI的版本不能过低,否则报错

梵墨白

关注

0
点赞
踩
1

收藏

觉得还不错? 一键收藏
0
评论
导出Excel报表+分页中的多重分页

完成样图前段代码实现<!DOCTYPE html><html xmlns:th="http://www.thymeleaf.org"><meta charset="utf-8"><meta name="viewport" content="width=device-width, initial-scale=1.0"><head t...
复制链接

扫一扫

专栏目录