Excel报表导出

歲✧西窗人

已于 2022-08-03 17:16:02 修改

阅读量421

点赞数

分类专栏： spring 文章标签：前端 html java spring boot

于 2022-07-11 16:26:47 首次发布

本文链接：https://blog.csdn.net/qq_64891979/article/details/125678538

版权

spring 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

废话少说，直接上代码

前端结构

<!DOCTYPE HTML>
<html lang="zh" xmlns:th="http://www.thymeleaf.org" xmlns:shiro="http://www.pollix.at/thymeleaf/shiro">
<meta charset="utf-8">
<head th:include="include :: header"></head>
<link th:href="@{/ajax/libs/jquery-layout/jquery.layout-latest.css}" rel="stylesheet"/>
<link th:href="@{/safety/aq/aqjy_common.css}"  rel="stylesheet" />
<body class="gray-bg" id="bbList">
<div class="ui-layout-west">
    <div class="main-content">
        <div class="box box-main">
            <div class="box-header">
                <div class="box-title">
                    <i class="fa icon-grid"></i> 报表清单
                </div>
            </div>
            <div class="ui-layout-content">
                <label class="radio-box box-width fa-clear">
                    <input type="radio" autocomplete="off" name="reportName" value="sgb1" th:checked="${reportName=='sgb1'}"/>各单位月度安全情况一览表
                </label>
                <br />
                <label class="radio-box box-width fa-clear">
                    <input type="radio" autocomplete="off" name="reportName" value="sgb2" th:checked="${reportName=='sgb2'}"/>安全生产事故月度汇总表
                </label>
                <br />
                <label class="radio-box box-width fa-clear">
                    <input type="radio" autocomplete="off" name="reportName" value="Sg_Sgmxb" th:checked="${reportName=='Sg_Sgmxb'}"/>事故明细表
                </label>
                <br />
                <label class="radio-box box-width fa-clear">
                    <input type="radio" autocomplete="off" name="reportName" value="sgb4" th:checked="${reportName=='sgb4'}"/>各单位年度安全情况一览表
                </label>
            </div>
        </div>
    </div>
</div>

<div class="container-div ui-layout-center">
    <div class="row" >
        <!--查询区域-->
        <div th:replace="../templates/safety/jm/sg/sgb1 :: searchPart"></div>
        <div th:replace="../templates/safety/jm/sg/sgb2 :: searchPart"></div>
        <div th:replace="../templates/safety/jm/sg/Sg_Sgmxb :: searchPart"></div>
        <div th:replace="../templates/safety/jm/sg/sgb4 :: searchPart"></div>
        <!--样张显示区域-->
        <div class="col-sm-12 select-table table-striped">
            <img alt="image" class="m-t-xs img-responsive" th:src="@{/img/baobiao.jpg}">
        </div>
    </div>
</div>

<div th:include="include :: footer"></div>
<script th:src="@{/ajax/libs/jquery-layout/jquery.layout-latest.js}"></script>
<script th:inline="javascript">
    var prefix = ctx + "safety/jm/bbList";
    var paramYear = (new Date()).getFullYear();
    var paramMonth = (new Date()).getMonth()+1;

    $(function () {
        var panehHidden = false;
        if ($(this).width() < 769) {
            panehHidden = true;
        }
        $('body').layout({ initClosed: panehHidden, west__size: 252 });

        /*报表类型变更*/
        $("input[name='reportName']").on('ifChecked', function (event) {
            var value = $(event.target).val();
            window.location = ctx + "jm/bbList?reportName=" + value+"&path=sg";
        });

        // 获取查询年度和查询月度的初始值
        // var stime = $("#stime").val();
        // if($.common.isNotEmpty(stime)){
        //     $("input[name='pYear']").val(stime.substring(0,4));
        //     $("input[name='pMonth']").val(stime.substring(5,7));
        // }
    });


    function searchReport(formId,url) {
        var params =  $("#"+formId).serialize(params);
        window.open(url+'&'+params, '_blank');
        //$.modal.openFull("样张报表",url);
        //window.location.href=url;
    }

    function formReset(formId) {
        $.form.reset(formId);
        $("input[name='pYear']").val(paramYear);
        $("input[name='pMonth']").val(paramMonth);
    }

    /*机构树*/
    function selectOrgTree(orgCodeId, orgNameId){
        var treeId = $("#"+orgCodeId).val();
        var orgCode = $.common.isEmpty(treeId) ? "100" : treeId;
        var url = ctx + "system/org/selectOrgTree/"+orgCode;
        var options = {
            title: '选择机构',
            width: "380",
            url: url,
            callBack: function(index, layero){
                var body = layer.getChildFrame('body', index);
                $("#"+orgCodeId).val(body.find('#treeId').val());
                $("#"+orgNameId).val(body.find('#treeName').val());
                layer.close(index);
            }
        };
        $.modal.openOptions(options);
    }

    $('.pYear').each(function (index) {
        var _this = this;
        layui.use('laydate', function () {
            layui.laydate.render({
                elem: _this //指定元素
                , theme: 'molv'
                , type: 'year'
                , value: new Date()
                , ready: function (date) {
                    $(".layui-laydate").off('click').on('click', '.layui-laydate-list li', function () {
                        $(".layui-laydate").remove();
                    });
                }
                , change: function (value, dates, edate) {
                    $('.pYear').val(value);
                }
            });
        });
    });

    $('.pMonth').each(function (index) {
        var _this = this;
        layui.use('laydate', function () {
            layui.laydate.render({
                elem: _this //指定元素
                , theme: 'molv'
                , type: 'month'
                , format: 'MM'
                , value: new Date()
                , ready: function (date) {
                    $(".layui-laydate").off('click').on('click', '.layui-laydate-list li', function () {
                        $(".layui-laydate").remove();
                    });
                }
                , change: function (value, dates, edate) {
                    $('.pMonth').val(value);
                }
            });
        });
    });
</script>
</body>
</html>

后台方面结构

实体类（doamin）：

/*
消防重点部位统计表
 */
@Data
@Getter
@Setter
@EqualsAndHashCode
@HeadRowHeight(30)
public class Xf_Xfzdtj {

    /** 单位名称 */
    @ColumnWidth(25)
    @ExcelProperty(value = {"消防重点部位统计表","","单位名称"},index = 0)
    private String org_name;

    /** A级 */
    @ColumnWidth(15)
    //@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 10)
    @ExcelProperty(value = {"消防重点部位统计表","","A级"},index = 1)
    private double levelA;
    /** B级 */
    @ColumnWidth(15)
    //@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 51)
    @ExcelProperty(value = {"消防重点部位统计表","","B级"},index = 2)
    private double levelB ;

    /** C级 */
    @ColumnWidth(15)
    //@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 13)
    @ExcelProperty(value = {"消防重点部位统计表","","C级"},index = 3)
    private double levelC;

    /** D级 */
    @ColumnWidth(15)
    //@HeadStyle(fillPatternType = FillPatternTypeEnum.SOLID_FOREGROUND, fillForegroundColor = 35)
    @ExcelProperty(value = {"消防重点部位统计表","","D级"},index = 4)
    private double levelD ;

    /** 合计 */
    @ColumnWidth(15)
    @ExcelProperty(value = {"消防重点部位统计表","","合计"},index = 5)
    private double sum1 ;
}

这是Excel表导出的实体类，该项目是封装好的，所以一些依赖不太一样，内容就是这样写的

mapper：



public interface XfHazardMapper {
    
    List<Xf_Xfzdtj> Xf_Xfzdtj(XfHazardReport report);
}

sql里面放SQL语句，存储过程，映射调用

业务层：

/**
     * 文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
     * 导出消防重点部位统计表
     */
    @GetMapping("/Xf_Xfzdtj")
    @ResponseBody
    public void Xf_Xfzdtj(String orgCode, String beginTime,String endTime, HttpServletResponse response) throws IOException {
        try {
            response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8");
            // 这里 需要指定写用哪个class去写，然后写到第一个sheet，名字为模板 然后文件流会自动关闭
            String fileName = URLEncoder.encode("消防重点部位统计表.xlsx", "UTF-8");
            //流文件
            response.setHeader("Content-Disposition", "attachment;filename=" + fileName);
            XfHazardReport report=new XfHazardReport();
            report.setOrgCode(orgCode);
            report.setStime(beginTime);
            report.setEtime(endTime);
            List<Xf_Xfzdtj> list= xfHazardMapper.Xf_Xfzdtj(report);
//这里是做了个遍历将有环保的数据全部删除
            for (int j = 0; j < list.size(); j++) {
                String org_name = list.get(j).getOrg_name();
                if (org_name.contains("环保")) {
                    list.remove(j);
                    j--;
                }
            }
            EasyExcel.write(response.getOutputStream(),Xf_Xfzdtj.class)
                     .registerWriteHandler(new DetectionRowWriteHandler(1,"" ,"时间："+beginTime+"至"+endTime))
                     .sheet("消防重点部位统计表").doWrite(list);
        } catch (Exception e) {
            //文件下载并且失败的时候返回json（默认失败了会返回一个有部分数据的Excel）
            // 重置response
            response.reset();
            response.setContentType("application/json");
            response.setCharacterEncoding("utf-8");
            Map<String, String> map = MapUtils.newHashMap();
            map.put("status", "failure");
            map.put("message", "下载文件失败" + e.getMessage());
            response.getWriter().println(JSON.toJSONString(map));
        }
    }

实现效果就是：

有待优化的：列如时间与C级颜色一致问题，搜了搜我还没解决，有大佬了可以指点小弟一下

总结：做导出的话，表的实体类写好，字段与数据库查询字段一致，逻辑就是用官网的自己再加以修改，符合逻辑即可。

官方文档
官方API

歲✧西窗人

关注

0
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
Excel报表导出

废话少说，直接上代码前端结构后台方面结构实体类（doamin）：这是Excel表导出的实体类，该项目是封装好的，所以一些依赖不太一样，内容就是这样写的mapper：sql里面放SQL语句，存储过程，映射调用业务层：实现效果就是：有待优化的：列如时间与C级颜色一致问题，搜了搜我还没解决，有大佬了可以指点小弟一下总结：做导出的话，表的实体类写好，字段与数据库查询字段一致，逻辑就是用官网的自己再加以修改，符合逻辑即可。......
复制链接

扫一扫

专栏目录