POI通用导出类

一、概述

构建通用Excel导出工具类。通过泛型将传入的对象进行导出

二、项目实现

1. pom依赖

  <!-- POI -->
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi</artifactId>
                <version>${poi.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml</artifactId>
                <version>${poi.ooxml.version}</version>
            </dependency>
            <dependency>
                <groupId>org.apache.poi</groupId>
                <artifactId>poi-ooxml-schemas</artifactId>
                <version>${poi.ooxml-schemas.version}</version>
            </dependency>

2.导出方法

    public void export(List<Long> eventIds, HttpServletResponse response) throws Exception {
        if (eventIds.isEmpty()) {
            throw new Exception("需要导出的数据ID为空");
        }
        // 查询数据库记录
        List<EventExportDO> eventExportDOS = eventRecordDao.queryExportRecords(eventIds);
        // 数据库字段与Excel字段之间需要做转换
        List<EventExportResult> exportResults = excelDataTransfer(eventExportDOS);

        // excel 构建
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("事件库记录");
        // excel 标题样式
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontName("微软雅黑");
        titleCellStyle.setFont(titleFont);
        // 构建标题
        ExcelUtil.createExcelTitle(ExcelCommonConstans.EVENT_RESOURCE_EXPORT_TITLE, sheet, titleCellStyle);

        // excel 正文样式
        CellStyle dataCellStyle = workbook.createCellStyle();
        Font cellFont = workbook.createFont();
        cellFont.setFontName("宋体");
        dataCellStyle.setFont(cellFont);

        ExcelUtil.createExcelContent(exportResults, sheet, dataCellStyle);
        String exportFileName = ExcelCommonConstans.EVENT_EXPORT_FILE_NAME;
        exportFileName = URLEncoder.encode(exportFileName, "utf-8");
        OutputStream out = response.getOutputStream();
        response.reset();
        response.setContentType("application/vnd.ms-excel;charset=utf-8");
        response.setHeader("Content-Disposition", "attachment;filename=" + exportFileName);
        response.addHeader("Pargam", "no-cache");
        response.addHeader("Cache-Control", "no-cache");
        workbook.write(out);
        out.flush();
        out.close();
    }

3.导出工具类

public  class ExcelUtil{
       /**
     * 创建Excel头部信息
     *
     * @param titles    头部标题
     * @param sheet     表格
     * @param cellStyle 单元格
     */
    public static void createExcelTitle(String[] titles, SXSSFSheet sheet, CellStyle cellStyle) {
        SXSSFRow headerRow = sheet.createRow(0);
        for (int i = 0; i < titles.length; i++) {
            SXSSFCell cell = headerRow.createCell(i);
            if (null != cellStyle) {
                cell.setCellStyle(cellStyle);
            }
            cell.setCellValue(titles[i]);
        }
    }

    /**
     * 表格内容
     *
     * @param dataDOs   表格内容对象
     * @param sheet     表格
     * @param cellStyle 单元格样式
     * @param <T>       泛型
     * @throws Exception 异常
     */
    public static <T> void createExcelContent(List<T> dataDOs, SXSSFSheet sheet, CellStyle cellStyle) throws Exception {
        for (int rowIndex = 0; rowIndex < dataDOs.size(); rowIndex++) {
            SXSSFRow row = sheet.createRow(rowIndex + 1);
            // 获取泛型类
            T clazz = dataDOs.get(0);
            // 获取对象属性
            Field[] fields = clazz.getClass().getDeclaredFields();
            int cellSize = fields.length;
            for (int cellIndex = 0; cellIndex < cellSize; cellIndex++) {
                SXSSFCell cell = row.createCell(cellIndex);
                fields[cellIndex].setAccessible(true);
                if (null != cellStyle) {
                    cell.setCellStyle(cellStyle);
                }
                cell.setCellValue(fields[cellIndex].get(clazz) == null ? "" : fields[cellIndex].get(clazz).toString());
            }
        }
    }
}