Hutools导出excel

Hutool 导出excel

使用hutool工具导出excel。使用函数式接口，可以对导出对象进行个性化操作。

1. 主程序 http接口导出excel

 /**
     * 导出excel
     *
     * @param dataList      数据（可迭代类型）
     * @param aliasMapList 头别名( put("num", "序号");)
     * @param sheetNames    sheet名称
     */
  public void exportExcel(Iterable<?>[] dataList, List<Map<String, String>> aliasMapList, List<String> sheetNames, String fileName, Consumer<cn.hutool.poi.excel.ExcelWriter> consumer) {
        cn.hutool.poi.excel.ExcelWriter excelWriter = cn.hutool.poi.excel.ExcelUtil.getWriter(true);
        ServletOutputStream outputStream = null;
        try {
            HttpServletResponse response = ((ServletRequestAttributes) RequestContextHolder.getRequestAttributes()).getResponse();
            setResponse(response, fileName);
            outputStream = response.getOutputStream();
            for (int i = 0; i < sheetNames.size(); i++) {
                if (i == 0) {
                    excelWriter.renameSheet(sheetNames.get(i));
                } else {
                    excelWriter.setSheet(sheetNames.get(i));
                }
                excelWriter.setOnlyAlias(true);
                excelWriter.setHeaderAlias(aliasMapList.get(i));
                if (ObjectUtils.isEmpty(dataList[i])) {
                    excelWriter.writeHeadRow(aliasMapList.get(i).values());
                }else{
                    excelWriter.write(dataList[i]);
                }
                if (!ObjectUtils.isEmpty(dataList[i])) {
                    setSizeColumn(excelWriter.getSheet(), excelWriter.getSheet().getRow(0).getPhysicalNumberOfCells());
                }
                consumer.accept(excelWriter);
            }
            excelWriter.flush(outputStream);
        } catch (Exception e) {
            e.printStackTrace();
        }finally {
            excelWriter.close();
            IoUtil.close(outputStream);
        }
    }

 private void setResponse(HttpServletResponse response, String fileName) throws UnsupportedEncodingException {
        fileName = URLEncoder.encode(fileName, "UTF-8").replaceAll("\\+", "%20");
        response.setHeader("Content-disposition", "attachment;filename*=utf-8''" + fileName + ".xlsx");
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
    }

 /**
     * 自适应宽度(中文支持)
     *
     * @param sheet sheet
     * @param size  因为for循环从0开始，size值为 列数-1
     */
    private void setSizeColumn(Sheet sheet, int size) {
        for (int columnNum = 0; columnNum <= size; columnNum++) {
            int columnWidth = sheet.getColumnWidth(columnNum) / 256;
            for (int rowNum = 0; rowNum <= sheet.getLastRowNum(); rowNum++) {
                Row currentRow;
                //当前行未被使用过
                if (sheet.getRow(rowNum) == null) {
                    currentRow = sheet.createRow(rowNum);
                } else {
                    currentRow = sheet.getRow(rowNum);
                }

                if (currentRow.getCell(columnNum) != null) {
                    Cell currentCell = currentRow.getCell(columnNum);
                    if (currentCell.getCellType() == CellType.STRING) {
                        int length = currentCell.getStringCellValue().getBytes().length;
                        if (columnWidth < length) {
                            columnWidth = length;
                        }
                    }
                }
            }
            sheet.setColumnWidth(columnNum, columnWidth * 256);
        }
    }

2. 调用示例

// 数据类型为[[{},{}],[[],{}]] 标题头格式为[ {"num", "序号"},{}]  sheet名格式为["",""] excelName 为字符串，最后参数为函数式接口，可以自定义对excel坐操作
excelUtil.exportExcel(data.stream().toArray(List[]::new), linkedHashMaps, sheetNameList, excelName, (cn.hutool.poi.excel.ExcelWriter excelWriter) -> {
             //所有的行加筛选
            int lastCol = excelWriter.getSheet().getRow(0).getLastCellNum();
            //A对应ascii为65
            CellRangeAddress cellAddresses = CellRangeAddress.valueOf((char) (65) + "1:" + (char) (65 + lastCol - 1) + "1");
            excelWriter.getSheet().setAutoFilter(cellAddresses);

        });