2021-08-06Excel导出前后端详解

`

在这里插入代码片`

导出
//(js部分)
//导出

function importData(){
    //var evaluationid = Utils.getUrlParam("id");
    // layer.open({
    //     type: 2,
    //     title: '指标项导入',
    //     maxmin: false,
    //     area: ['700px','400px'],
    //     skin: 'layer-ext-moon',
    //     content: ['./import.html?evaluationid=' + evaluationid, 'yes']  //跳到新增页面
    // })
    // var selectNodesObj = document.getElementById('getid');
    // var selIndex = selectNodesObj.selectedIndex;     //获取当前选择的选项的index值
    // var selValue = selectNodesObj.options[selIndex].value; //获取当前选择项的值
    var id = $("#getid").val();
    var year =  $("#getid").find("option:selected").text();
    var date = new Date();
    var Y = date.getFullYear() + '-';
    var M = (date.getMonth() + 1 < 10 ? '0' + (date.getMonth() + 1) : date.getMonth() + 1) + '-';
    var D = (date.getDate() < 10 ? '0' + date.getDate() : date.getDate()) + ' ';
    var h = (date.getHours() < 10 ? '0' + date.getHours() : date.getHours()) + ':';
    var m = (date.getMinutes() < 10 ? '0' + date.getMinutes() : date.getMinutes()) + ':';
    var s = (date.getSeconds() < 10 ? '0' + date.getSeconds() : date.getSeconds());
    var url = config.rstpfm_url() + '/dc-evaluationitem/exportALLPreviewList?evaluationid='+ id+'&year=' + year;
    var currentdate = Y + M + D;
    var filename = "指标预览-" + currentdate + ".xls";
    DownLoad(url,filename);
}
function DownLoad(url, name) {
    if (!!window.ActiveXObject || "ActiveXObject" in window) {
        //ie
        var oPow = window.open(url, "", "width = 1, height = 1, top = 5000, left = 5000 ");
        var isOpen = true; //判断window.open是否被禁用
        try {
            if (oPow == null) {
                isOpen = false
            }
        } catch (err) {
            isOpen = false
        }
        if (isOpen) {
            //没禁用window.open采用window.open下载
            while (oPow.document.readyState !== "complete") {
                if (oPow.document.readyState === "complete") break;
            }
            oPow.document.execCommand("SaveAs", true, name);
            oPow.close();
        } else {
            //禁用了window.open采用iframe下载
            var oIrame = document.createElement('iframe');
            oIrame.style.width = "0px";
            oIrame.style.height = "0px";
            oIrame.style.opacity = 1;
            document.body.appendChild(oIrame)
            oIrame.src = url;
            var IfDoc = oIrame.contentDocument || oIrame.document;
            oIrame.onreadystatechange = function () { // IE下的节点都有onreadystatechange这个事件
                if (oIrame.readyState == "complete") {
                    // oIrame.execCommand("SaveAs", true, name)
                    document.body.removeChild(oIrame)
                }
            };
        }
    } else {
        if (typeof url == 'object' && url instanceof Blob) {
            url = URL.createObjectURL(url); // 创建blob地址
        }
        var aLink = document.createElement('a');
        aLink.href = url;
        aLink.download = name || ''; // HTML5新增的属性，指定保存文件名，可以不要后缀，注意，file:///模式下不会生效
        var event;
        if (window.MouseEvent) {
            event = new MouseEvent('click');
        } else {
            if (document.createEvent) {
                event = document.createEvent('MouseEvents');
                event.initMouseEvent('click', true, false, window, 0, 0, 0, 0, 0, false, false, false, false, 0, null);
            }
        }
        aLink.dispatchEvent(event);
    }
}

后端基于Java SpringBoot框架

    //controller层
    @ApiOperation(value = "考评汇总excel导出", notes = "考评汇总excel导出")
    @GetMapping("/exportALLPreviewList")
    public void exportALLPreviewList(@RequestParam("evaluationid") String evaluationid,@RequestParam("year") String year, HttpServletResponse response) throws IOException {
    //获取数据源
        List<ItemModelImportExcel3> selfMarkPreviewList = dcEvaluationitemService.getALLPreviewList(evaluationid);
//       Double zipingTol = selfMarkPreviewList.stream().collect(Collectors.summingDouble(ItemModelImportExcel::getZpSum));
//        DecimalFormat df = new DecimalFormat("######0.00");
//        ExportParams exportParams = new ExportParams("自评上报预览---自评分合计" + df.format(zipingTol), null, "sheet1");
        ExportParams exportParams = new ExportParams(year+"年度人社系统绩效考评" , null, "sheet1");
        exportParams.setStyle(ExcelStyleUtil.class);
        Workbook sheets = ExcelExportUtil.exportExcel(exportParams, ItemModelImportExcel3.class, selfMarkPreviewList);
        //设置表格宽度
        sheets.getSheetAt(0).setColumnWidth(0,8000);
        sheets.getSheetAt(0).setColumnWidth(1,8000);
        sheets.getSheetAt(0).setColumnWidth(2,8000);
        sheets.getSheetAt(0).setColumnWidth(3,8000);
        sheets.getSheetAt(0).setColumnWidth(4,5000);
        sheets.getSheetAt(0).setColumnWidth(5,8000);
        sheets.getSheetAt(0).setColumnWidth(6,8000);
        sheets.getSheetAt(0).setColumnWidth(7,5000);
        //设置excel的文件名称
//        String excelName = "自评预览" + zipingTol;filename=\""+ new String(("XXX信息一览表" + ".xls").getBytes("gb2312"),  "iso8859-1") + "\"");
        //String excelName = year+"年度人社系统绩效考评";
        //文件名称中文标准写法，避免无法显示
        String excelName = new String((year + "年度人社系统绩效考评" + ".xls").getBytes("gb2312"),  "iso8859-1");
        //当前日期用于导出文件名称
        SimpleDateFormat sdf = new SimpleDateFormat("yyyyMMdd");
        String dateStr = excelName + "-" + sdf.format(new Date()) + currentTimeMillis();
        OutputStream output = response.getOutputStream();
        try {
            response.setHeader("Content-Disposition", "attachment; filename=" + dateStr + ".xls");
            response.setContentType("application/msexcel");
            sheets.write(output);
            output.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (output != null) {
                output.close();
            }
        }
    }

//设置需要导出的字段实体类
package com.yxkj.rstpfm.dto;

import cn.afterturn.easypoi.excel.annotation.Excel;
import lombok.Data;

@Data
public class ItemModelImportExcel3 {

    @Excel(name = "一级指标", orderNum = "0")
    private String level1;
    @Excel(name = "二级指标", orderNum = "1")
    private String level2;
    @Excel(name = "三级指标", orderNum = "2")
    private String level3;
    @Excel(name = "四级指标", orderNum = "3")
    private String level4;
    @Excel(name = "分值（分）", orderNum = "4")
    private Integer socre;
    @Excel(name = "考评责任部门", orderNum = "5")
    private String obuname;
    @Excel(name = "考评人", orderNum = "6")
    private String personnames;
    @Excel(name = "责任部门评分（分）", orderNum = "7")
    private String obtainscore;
}

本文只作为本人的笔记用，如有疑惑欢迎大家私信共同学习！