一、概述

構(gòu)建通用Excel導(dǎo)出工具類。通過泛型將傳入的對(duì)象進(jìn)行導(dǎo)出

二、項(xiàng)目實(shí)現(xiàn)

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.導(dǎo)出方法

    public void export(List<Long> eventIds, HttpServletResponse response) throws Exception {
        if (eventIds.isEmpty()) {
            throw new Exception("需要導(dǎo)出的數(shù)據(jù)ID為空");
        }
        // 查詢數(shù)據(jù)庫(kù)記錄
        List<EventExportDO> eventExportDOS = eventRecordDao.queryExportRecords(eventIds);
        // 數(shù)據(jù)庫(kù)字段與Excel字段之間需要做轉(zhuǎn)換
        List<EventExportResult> exportResults = excelDataTransfer(eventExportDOS);

        // excel 構(gòu)建
        SXSSFWorkbook workbook = new SXSSFWorkbook();
        SXSSFSheet sheet = workbook.createSheet("事件庫(kù)記錄");
        // excel 標(biāo)題樣式
        CellStyle titleCellStyle = workbook.createCellStyle();
        titleCellStyle.setAlignment(HorizontalAlignment.CENTER);
        Font titleFont = workbook.createFont();
        titleFont.setFontName("微軟雅黑");
        titleCellStyle.setFont(titleFont);
        // 構(gòu)建標(biāo)題
        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.導(dǎo)出工具類

public  class ExcelUtil{
       /**
     * 創(chuàng)建Excel頭部信息
     *
     * @param titles    頭部標(biāo)題
     * @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]);
        }
    }

    /**
     * 表格內(nèi)容
     *
     * @param dataDOs   表格內(nèi)容對(duì)象
     * @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);
            // 獲取對(duì)象屬性
            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());
            }
        }
    }
}