java 导出Excel

1、controller

@PostMapping("/exportExcel")
@ApiOperation("导出")
public void exportExcel(@RequestBody Map<String,Object> param, HttpServletResponse response) {
    Service.exportExcel(response, param);
}

2、service

public void exportExcel(HttpServletResponse response, Map<String,Object> param);

3、serviceImp

 @Override
    public void exportExcel(HttpServletResponse response, Map<String,Object> param) {
        String title = (String) param.get("title");

        //sheet1列 头部信息
        List<Map<String, Object>> sheet1List = (List<Map<String, Object>>)param.get("first");
        //sheet2列 头部信息
        List<Map<String, Object>> sheet2List = (List<Map<String, Object>>)param.get("second");

        // 获取数据--sheet1
        List<Map<String, Object>> dataList = new ArrayList<>();
        // 获取数据--sheet2
        List dataList2 = new ArrayList<>();

        if(StringUtils.containsIgnoreCase(title,"姓名")){
            //sheet1
            dataList = findCountInfoByDiameter(param);
            //sheet2
            dataList2 = selectInfo(param);
        }else if(StringUtils.containsIgnoreCase(title,"性别")){
            dataList = findCountInfoByMaterial(param);
            dataList2 = selectInfo(param);
        }else if(StringUtils.containsIgnoreCase(title,"年龄")){
            dataList = findByDrawCircularWithRoad(param);
            dataList2 = selectInfo(param);
        }else if(StringUtils.containsIgnoreCase(title,"户籍")){
            dataList = findByDrawPolygonWithCaliber(param);
            dataList2 = findList(param);
        }else if(StringUtils.containsIgnoreCase(title,"身份证号")){
            dataList = findAll(param);

        }

//        for (int i=0; i<dataList.size(); i++) {
//            for (Map<String, Object> head : sheet1List) {
//                String colName = (String) head.get("field");
//                // 获取当前列的第i行的值
//                Object colValue = dataList.get(i).get(colName);
//                // 判断值是否为null
//                if (colValue == null) {
//                    dataList.get(i).put(colName, 0);
//                }
//                else {
//                    dataList.get(i).put(colName, Double.parseDouble((String)colValue));
//                }
//            }
//        }

        try (ExcelWriter writer = ExcelUtil.getWriter()) {

            //sheet1
            setWorkSheet(writer,title,"列表页",sheet1List,dataList,0);

            //sheet2
            if(sheet2List != null && sheet2List.size()>0){
                ExcelWriter excelWriter = writer.setSheet(1);
                setWorkSheet(excelWriter,title,"明细",sheet2List,dataList2,1);
            }

            //自定义标题别名
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(title, "UTF-8")+".xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);
        } catch (Exception e) {
            e.printStackTrace();
//            log.error("导出数据报表错误：", e);
        }
    }

    public void setWorkSheet(ExcelWriter writer,
                             String title,
                             String sheetName,
                             List<Map<String,Object>>sheet1List,
                             List dataList,
                             int sheetIndex){

        int cols = sheet1List.size() ;
        int rows = dataList.size();

        for(int i = 0;i<sheet1List.size();i++){
            Map<String, Object> column = sheet1List.get(i);
            String field = (String) column.get("field");
            String columnTitle = (String) column.get("title");
            writer.addHeaderAlias(field, columnTitle);
        }
        //合并单元格，设置标题
        writer.merge(cols-1, title);
        //设置是否只保留别名中的字段值，如果为true，则不设置alias的字段将不被输出，false表示原样输出
        writer.setOnlyAlias(true);

        writer.write(dataList, true);

        writer.getWorkbook().getCreationHelper().createFormulaEvaluator().evaluateAll();
        //重命名sheet名字
        writer.getWorkbook().setSheetName(sheetIndex,sheetName);

        writer.setRowHeight(1, 40);
        for (int i = 0; i < cols; i++) {
            writer.setColumnWidth(i, 45);
            for(int j = 0 ;j < rows; j++){
                Cell cell = writer.getCell(i, j+2);
                CellType cellType = cell.getCellType();
                if(cellType.name().equals("NUMERIC")){
                    cell.getCellStyle().setDataFormat((short) 0);
                }
            }
        }



        Cell cell = writer.getCell(0, 0);
        CellStyle cellStyle = cell.getCellStyle();
        cellStyle.setFillForegroundColor(IndexedColors.PALE_BLUE.getIndex());
        cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);
        Font font = writer.createFont();
        font.setBold(true);
        font.setFontHeightInPoints((short) 16);
        cellStyle.setFont(font);
        cell.setCellStyle(cellStyle);

    }

3、serviceImp的另一种方法

@Override
    public void exportExcel(LabourOddVisaBO labourOddVisaBO, HttpServletResponse response) {
        List<Map> list = getBaseMapper().selectListNoPage(labourOddVisaBO);

        try (ExcelWriter writer = ExcelUtil.getWriter()) {
            writer.addHeaderAlias("name","姓名");
            writer.addHeaderAlias("tTime","时间");
            writer.addHeaderAlias("reason","原因");

            writer.setOnlyAlias(true);
            writer.write(list, true);
            writer.setRowHeight(0, 30);
            writer.setRowHeight(1, 20);
            for (int i = 0; i <= 9; i++) {
                writer.setColumnWidth(i, 30);
            }

            Cell cell = writer.getCell(0, 0);
            CellStyle cellStyle = cell.getCellStyle();
            cellStyle.setFillForegroundColor(IndexedColors.SKY_BLUE.getIndex());
            cellStyle.setFillPattern(FillPatternType.SOLID_FOREGROUND);

            Font font = writer.createFont();
            font.setBold(true);
            font.setFontHeightInPoints((short) 16);
            cellStyle.setFont(font);
            cellStyle.setWrapText(true);
            cell.setCellStyle(cellStyle);
            //自定义标题别名
            //response为HttpServletResponse对象
            response.setContentType("application/vnd.ms-excel;charset=utf-8");
            response.setCharacterEncoding("UTF-8");
            //test.xls是弹出下载对话框的文件名，不能为中文，中文请自行编码
            String title = "零工签证导出数据";
            response.setHeader("Content-Disposition", "attachment;filename="+ URLEncoder.encode(title, "UTF-8")+".xls");
            ServletOutputStream out = response.getOutputStream();
            writer.flush(out);

        } catch (Exception e) {
            log.error("导出错误：", e);
        }
    }

4、Vue 前端

//导出
export function exportExcel(param) {
  return request({
    url: 'xxx/xxxxxx/exportExcel',
    method: 'post',
    data: param,
    headers: {
      'Content-Type': 'application/json;charset=UTF-8'
    },
    responseType: 'blob',
    timeout: 60000
  }).then(res => {
    // console.log(res)
      downloadExcelWithResData(res)
  }).catch((err) => {
    console.log("====catch=="+JSON.stringify(err))
  })
}

/**
 * 下载excel
 */
export function downloadExcelWithResData(res) {
  let fileName = res.headers['content-disposition'].split('filename=')[1]
  if (!fileName) {
    fileName = res.headers['content-disposition'].split('UTF-8\'\'')[1]
  }
  fileName = fileName ? fileName.replace(/\"/g, '') : 'file.xlsx'
  fileName = decodeURI(fileName) || ''
  downloadFileWithBuffer(res.data, fileName, 'application/vnd.ms-excel;charset=utf-8')
}

export function downloadFileWithBuffer(data, name, type) {
  var blob = new Blob([data], {
    type: type || ''
  })
  var downloadElement = document.createElement('a')
  var href = window.URL.createObjectURL(blob) // 创建下载的链接
  downloadElement.href = href
  downloadElement.download = name // 下载后文件名
  document.body.appendChild(downloadElement)
  downloadElement.click() // 点击下载
  document.body.removeChild(downloadElement) // 下载完成移除元素
  window.URL.revokeObjectURL(href) // 释放掉blob对象
}