java 导出数据到EXCEL

在工作中我们经常遇到写数据到EXCEL中，今天发型代码中有很多导出EXCEL的代码，但是每人都开了一个自己的导出EXCEL的方法，流程都一样：
先是从DB读取数据到list中，然后创建workbook -> 创建worksheet -> 创建EXCEL的header -> 创建row -> 填充数据到row -> 写数据到response中

正好这次需求也是导出用户信息到EXCEL中，顺便一起把这些重复的工作一起重构了，也方便以后再新增导出其他信息到EXCEL时不用写那些重复的逻辑。
重构代码的思路：

1：把EXCEL的 header 修改成动态设置

创建一个EXCEL的注解，提供出数据每个属性在EXCEL中的顺序和中文名称

@Target({ElementType.FIELD})
@Retention(RetentionPolicy.RUNTIME)
@Documented
public @interface ExcelCellTitle {

    //默认值
    String value() default "";

    //excel中的序号
    int order() default 0;

    //表头
    String title() default "";
}

对于每个需要写入EXCEL中的数据模型都自己添加该注解并设置好order和title

2：把EXCEL 中写入Row 的代码抽象到接口里，交给具体的数据对象来实现

public interface ImportExcelDTO {

    /**
     * 添加数据到EXCEL中的row中
     * @Author xiaoyue.chen
     * @param row
     */
    void addOneRowOfDataToExcel(Row row);
}

定义具体的业务数据模型

public class EmployeePermsDTO implements ImportExcelDTO {

    @ExcelCellTitle(order = 1, title = "工号")
    private String userId;

    @ExcelCellTitle(order = 2, title = "姓名")
    private String userCn;

    @ExcelCellTitle(order = 3, title = "部门编号")
    private String userOrgId;

    @ExcelCellTitle(order = 4, title = "部门名称")
    private String orgname;

    public String getUserId() {
        return userId;
    }

    public void setUserId(String userId) {
        this.userId = userId;
    }

    public String getUserCn() {
        return userCn;
    }

    public void setUserCn(String userCn) {
        this.userCn = userCn;
    }

    public String getUserOrgId() {
        return userOrgId;
    }

    public void setUserOrgId(String userOrgId) {
        this.userOrgId = userOrgId;
    }

    public String getOrgname() {
        return orgname;
    }

    public void setOrgname(String orgname) {
        this.orgname = orgname;
    }

    /**
     * 添加数据到EXCEL中的row中
     * @Author xiaoyue.chen
     * @param row
     */
    @Override
    public void addOneRowOfDataToExcel(Row row) {
        WorkSheetBuilder.createCell(row, 0).setCellValue(this.getUserId());
        WorkSheetBuilder.createCell(row, 1).setCellValue(this.getUserCn());
        WorkSheetBuilder.createCell(row, 2).setCellValue(this.getUserOrgId());
        WorkSheetBuilder.createCell(row, 3).setCellValue(this.getOrgname());
    }
}

通过这样的设计方式就可以把具体的数据填充交给具体的数据对象处理，对于工具类就可以使用接口来填充数据到row中，降低代码的耦合度

3：在创建EXCEL的header时需要设置EXCEL的header格式，字体格式，再抽一个EXCEL的格式工具类

public class WorkSheetBuilder {

    private static void setCellBorder(CellStyle cellStyle) {
        //边框颜色和宽度设置
        cellStyle.setBorderBottom(HSSFCellStyle.BORDER_THIN);
        cellStyle.setBottomBorderColor(IndexedColors.BLACK.getIndex()); // 下边框
        cellStyle.setBorderLeft(HSSFCellStyle.BORDER_THIN);
        cellStyle.setLeftBorderColor(IndexedColors.BLACK.getIndex()); // 左边框
        cellStyle.setBorderRight(HSSFCellStyle.BORDER_THIN);
        cellStyle.setRightBorderColor(IndexedColors.BLACK.getIndex()); // 右边框
        cellStyle.setBorderTop(HSSFCellStyle.BORDER_THIN);
        cellStyle.setTopBorderColor(IndexedColors.BLACK.getIndex()); // 上边框
    }

    private static void setCellBackground(CellStyle cellStyle) {
        //设置背景颜色
        cellStyle.setFillForegroundColor(IndexedColors.GREY_25_PERCENT.getIndex());
        cellStyle.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
    }

    private static void setCellFont(CellStyle cellStyle, Workbook writeWorkbook) {
        //粗体字设置
        Font font = writeWorkbook.createFont();
        font.setBoldweight((short)3);//.setBold(true);
        cellStyle.setFont(font);
    }

    public static Row createRow(Sheet sheet, int rowNum) {
        return sheet.createRow(rowNum);
    }

    public static Cell createCell(Row row, int colNum) {
        Cell cell = row.createCell(colNum);
        return cell;
    }

    public static Cell createCell(Row row, int colNum, CellStyle cellStyle) {
        Cell cell = row.createCell(colNum);
        cell.setCellStyle(cellStyle);
        return cell;
    }

    public static CellStyle buildHeadCellStyle(Workbook writeWorkbook) {
        CellStyle style = writeWorkbook.createCellStyle();
        //对齐方式设置
        style.setAlignment(CellStyle.ALIGN_CENTER);
        setCellBorder(style);
        setCellBackground(style);
        setCellFont(style, writeWorkbook);
        return style;
    }
}

4：实现具体的EXCEL工具类

完成对数据的header解析，导入，并写入response。同时对外提供统一的导入接口。

public class ExcelWriterBuilder {

    /**
     * work book
     */
    private Workbook writeWorkbook;

    /**
     * work sheet
     */
    private Sheet writeSheet;

    /**
     * excel file
     */
    private File file;

    /**
     * excel 文件名称
     */
    private String fileName;

    /**
     * 创建excel title
     * @Author xiaoyue.chen
     * @param tClass
     */
    private void createExcelHeader(Class tClass) {
        CellStyle headerStyle = WorkSheetBuilder.buildHeadCellStyle(this.writeWorkbook);
        List<String> titles = getCellHeader(tClass);
        Row row = WorkSheetBuilder.createRow(this.writeSheet, 0);
        for (int i=0; i < titles.size(); i++) {
            WorkSheetBuilder.createCell(row, i, headerStyle).setCellValue(titles.get(i));
        }
    }

    /**
     * 填充数据到Excel表格
     * @Author xiaoyue.chen
     * @param writeTable 数据
     */
    private void addContent(List<? extends ImportExcelDTO> writeTable) {
        int rowIndex = 1;
        for (ImportExcelDTO dto : writeTable) {
            Row row = WorkSheetBuilder.createRow(this.writeSheet, rowIndex);
            dto.addOneRowOfDataToExcel(row);
            rowIndex++;
        }
    }

    /**
     * 获取excel titile
     * @Author xiaoyue.chen
     * @param tClass
     * @return
     */
    private List<String> getCellHeader(Class<T> tClass) {
        List<String> cellTitle = Arrays.stream(tClass.getDeclaredFields())
                .sorted(Comparator.comparing(field -> field.getAnnotation(ExcelCellTitle.class).order()))
                .map(field -> field.getAnnotation(ExcelCellTitle.class).title())
                .collect(Collectors.toList());
        return cellTitle;
    }

    /**
     * 浏览器下载excel
     * @Author xiaoyue.chen
     * @param wb
     * @param response
     */
    private void buildExcelDocument(Workbook wb, HttpServletResponse response) throws IOException {
        OutputStream outStream = null;//response.getOutputStream();

        try {
            //response.setContentType(MediaType.APPLICATION_OCTET_STREAM_VALUE);
            response.reset();
            response.setContentType("application/binary;charset=utf-8");
            response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(this.fileName, "utf-8"));
            response.flushBuffer();
            outStream = response.getOutputStream();
            wb.write(outStream);
            outStream.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (!ObjectUtils.isEmpty(outStream)) {
                outStream.close();
            }
        }
    }

    /**
     * 创建excel文件到本地目录
     * @throws IOException
     */
    private void createExcelToLocal() throws IOException {
        FileOutputStream fileOut = null;

        try {
            fileOut = new FileOutputStream(this.file);
            this.writeWorkbook.write(fileOut);
            fileOut.flush();
        } catch (IOException e) {
            e.printStackTrace();
        } finally {
            if (!ObjectUtils.isEmpty(fileOut)) {
                fileOut.close();
            }
        }
    }

    /**
     * 构造函数， 创建work boot
     * @Author xiaoyue.chen
     */
    public ExcelWriterBuilder() {
        this.writeWorkbook = new XSSFWorkbook();
    }

    /**
     * 构造函数
     * @Author xiaoyue.chen
     * @param fileName 文件名称
     */
    public ExcelWriterBuilder(String fileName) {
        this();
        this.fileName = fileName;
    }

    /**
     * 创建excel sheet 文件
     * @Author xiaoyue.chen
     * @param sheetName sheet name
     * @return
     */
    public ExcelWriterBuilder buildSheet(String sheetName) {
        this.writeSheet = this.writeWorkbook.createSheet(sheetName);
        return this;
    }

    /**
     * 将数据导入到excel
     * @Author xiaoyue.chen
     * @param writeTable
     * @param tClass
     */
    public void importDataToExcel(List<? extends ImportExcelDTO> writeTable, Class tClass) {
        this.createExcelHeader(tClass);
        this.addContent(writeTable);
    }

    /**
     * 生成文档供浏览器下载
     * @Author xiaoyue.chen
     */
    public void buildDownLoadExcel(HttpServletResponse response) {
        try {
            this.buildExcelDocument(this.writeWorkbook, response);
        } catch (IOException e) {
            e.printStackTrace();
        }
    }
}

5: 外部调用

  private void writeEmployeeInfo2Excel(List<EmployeeDTO> employee, HttpServletResponse response) {
    ExcelWriterBuilder builder = new ExcelWriterBuilder("userInfo.xlsx");
    builder.buildSheet("userinfo").importDataToExcel(employee, EmployeePermsDTO.class);
    builder.buildDownLoadExcel(response);
  }


  private void writePerm2Excel(List<PermBasicDTO> perms, HttpServletResponse response) {
    ExcelWriterBuilder builder = new ExcelWriterBuilder("perm.xlsx");
    builder.buildSheet("permInfo").importDataToExcel(perms, PermBasicDTO.class);
    builder.buildDownLoadExcel(response);
  }

如果以后再有新的数据对象需要导入EXCEL中就只需要完成数据的获取和写入规则，然后创建一个ExcelWriterBuilder ,依次调用buidShett和importDataToExcel方法就可以完成数据导入。
减少了EXCEL操作的重复代码。

6：controller接口定义

 @ApiOperation("导出员工信息")
    @GetMapping(value = "/user/export")
    public void exportEmployeePerms( HttpServletResponse response) {
        logger.info("导出员工信息");
        service.exportEmployeeInfo(response);
    }

【注】 需要在maven中引入下面的依赖

        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>3.8</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>3.8</version>
        </dependency>