java自定义模板导出excel

java自定义模板导出excel

1、前端请求编码

// 自定义按钮导出数据

function exportData() {
    var options = $('#exampleTable').bootstrapTable('getOptions');
    //通过table的#id 得到每页多少条
    var pageSize = options.pageSize;
    //通过table的#id 得到当前第几页
    var pageNumber = options.pageNumber;
    var condition = {
        'name': $('#name').val(),
    };
    var param = {
        size: pageSize, // 如果设置了分页，每页数据条数
        current: pageNumber, // 如果设置了分布，首页页码
        condition: JSON.stringify(condition)
    };
    var ee = encodeParam(param);
    var rr = encodeURI(ee);
    //定义url //自己的访问路径prefix 
    var url = prefix + "/exportExcel?" + rr;
    var xhr = new XMLHttpRequest();
    xhr.open('GET', url, true);        // 也可以使用POST方式，根据接口
    xhr.responseType = "blob";    // 返回类型blob
    // 定义请求完成的处理函数，请求前也可以增加加载框/禁用下载按钮逻辑
    xhr.onload = function () {
        // 请求完成
        if (this.status === 200) {
            // 返回200
            var blob = this.response;
            var reader = new FileReader();
            reader.readAsDataURL(blob);    // 转换为base64，可以直接放入a表情href
            reader.onload = function (e) {
                // 转换完成，创建一个a标签用于下载
                var a = document.createElement('a');
                var nowDate = moment().format('YYYYMMDDHHmmssSSS');
                a.download = '文件' + nowDate + '.xls';
                a.href = e.target.result;
                $("body").append(a);    // 修复firefox中无法触发click
                a.click();
                $(a).remove();
            }
        }
    };
    // 发送ajax请求
    xhr.send()
};
function encodeParam(json) {
    var tmps = [];
    for (var key in json) {
        tmps.push(key + '=' + json[key]);
    }
    return tmps.join('&');
}

2、后端处理代码

 /**
     * 导出excel
     */
    @ResponseBody
    @GetMapping(value = "/exportExcel")
    public void exportExcel(@RequestParam Map<String, Object> _params, HttpServletResponse response) {
        Map<String, Object> params = new HashMap<>();
        _params.forEach((k, v) -> {
            params.put(k.trim(), v);
        });
        List<pojo> list = xxxSevice.list(params );
        if (CollectionUtils.isNotEmpty(list)) {
            //excel标题
            String[] title = {"",""};
            //说明
            String[] explain = {"",""};
            String fileName = "xxx.xlsx";
            //sheet名
            String sheetName = "xxx";
            int total = list.size();
            String[][] content = new String[list.size()][title.length];
            List<pojo> pojoList = JSON.parseArray(list.toString(), pojo.class);
            for (int i = 0; i < total; i++) {
                Pojo pojo= pojoList .get(i);
                //
                content[i][0] = "";
                //
                content[i][1] = "";
                //
               content[i][2] = StringUtils.isBlank(pojoList .getName()) ? "" : pojoList .getName();
        
            }
            //创建模板
            HSSFWorkbook workbook = new HSSFWorkbook();
            HSSFSheet sheet = workbook.createSheet(sheetName);
            //第一行
            HSSFRow row0 = sheet.createRow(0);
            HSSFCell cell_00 = row0.createCell(0);
            cell_00.setCellValue("ds");
            HSSFCell cell_01 = row0.createCell(1);
            cell_01.setCellValue("8.5.0");

            //第二行
            HSSFRow row01 = sheet.createRow(1);
            HSSFCell cell_10 = row01.createCell(0);
            cell_10.setCellValue("表格");
            HSSFCell cell_11 = row01.createCell(1);
            cell_11.setCellValue("题目");
            //合并单元格
            HSSFCell cell_20 = row0.createCell(2);
            cell_20.setCellValue("内容");
            CellRangeAddress region = new CellRangeAddress(0, 1, 2, 13);
            sheet.addMergedRegion(region);

            //声明列对象
            HSSFCell cell = null;
            HSSFRow row02 = sheet.createRow(2);
            //创建标题
            for (int i = 0; i < title.length; i++) {
                cell = row02.createCell(i);
                cell.setCellValue(title[i]);
            }
            HSSFRow row03 = sheet.createRow(3);
            //字段说明
            for (int i = 0; i < explain.length; i++) {
                cell = row03.createCell(i);
                cell.setCellValue(explain[i]);
            }
            //创建内容
            for (int i = 0; i < content.length; i++) {
                row03 = sheet.createRow(i + 4);
                for (int j = 0; j < content[i].length; j++) {
                    //将内容按顺序赋给对应的列对象
                    row03.createCell(j).setCellValue(content[i][j]);
                }
            }
            //创建HSSFWorkbook
            HSSFRow row3 = sheet.getRow(2);
            HSSFFont font = workbook.createFont();
            font.setBold(true);
            HSSFCellStyle style = workbook.createCellStyle();
            style.setFont(font);
            style.setAlignment(HSSFCellStyle.ALIGN_CENTER);
            for (int i = 0, c = title.length; i < c; i++) {
                sheet.autoSizeColumn(i, true);
                row3.getCell(i).setCellStyle(style);
            }
            //响应到客户端
            try {
                this.setResponseHeader(response, fileName);
                OutputStream os = response.getOutputStream();
                workbook.write(os);
                os.flush();
                os.close();
            } catch (Exception e) {
                log.error("excel写出异常" + e.getMessage());
            }
        }
    }


    private void setResponseHeader(HttpServletResponse response, String fileName) {
        try {
            fileName = URLEncoder.encode(fileName, "UTF-8");
            response.setCharacterEncoding("UTF-8");
            response.setContentType("APPLICATION/OCTET-STREAM");
            response.setHeader("Content-Disposition", "attachment; filename="+ fileName);
        } catch (Exception ex) {
            log.info("发送响应流方法异常，原因=【{}】", ex.getMessage());
        }
    }

拙见按需参考！