poi SXSSFWorkbook 大数据导出 支持多sheet 多文件

/**
 * @author one.xu
 * @version v1.0
 * @description excel 导出支持多excel 多sheet
 * @date 2019/4/3 16:14
 */
@Slf4j
public abstract class ExcelPagingExport<T> {
    public static final String EXCEL_TEMP_PATH_KEY = "tempPath";
    public static final String EXCEL_FILE_NAME_KEY = "fileName";
    public static final String EXCEL_SHEET_NAME_KEY = "sheetName";
    public static final String EXCEL_MAX_SHEET_KEY = "maxSheet";
    public static final String EXCEL_MAX_ROW_KEY = "maxRow";
    public static final String EXCEL_PAGE_SIZE_KEY = "pageSize";
    public static final String EXCEL_DATE_FORMAT_PATTERN_KEY = "datePattern";
    /**
     * 一个sheet最多条数
     */
    public static final int EXCEL_MAX_ROW_COUNT = 65536;
    /**
     * 最多sheet数
     */
    public static final int EXCEL_MAX_SHEET_COUNT = 255;
    /**
     * 默认分页size
     */
    public static final int EXCEL_DEFAULT_PAGE_SIZE = 5000;
    /**
     * excel临时目录
     */
    public static final String EXCEL_TEMP_PATH = "/tmp/excel/";
    /**
     *
     */
    public static final int EXCEL_ROW_ACCESS_WINDOW_SIZE = 100;
    /**
     * 列最小宽度
     */
    public static final int EXCEL_COLUMN_MIN_WIDTH = 200;
    /**
     * 列最大宽度
     */
    public static final int EXCEL_COLUMN_MAX_WIDTH = 15000;

    public ExcelPagingExport(int totalCount, LinkedHashMap<String, String> excelTitle) {
        this(totalCount, EXCEL_DEFAULT_PAGE_SIZE, excelTitle);
    }

    public ExcelPagingExport(int totalCount, int pageSize, LinkedHashMap<String, String> excelTitle) {
        this(totalCount, pageSize, null, excelTitle);
    }

    public ExcelPagingExport(int totalCount, int pageSize, String fileName, LinkedHashMap<String, String> excelTitle) {
        this(totalCount, pageSize, fileName, null, excelTitle);
    }

    public ExcelPagingExport(int totalCount, int pageSize, String fileName, String sheetName, LinkedHashMap<String, String> excelTitle) {
        this(totalCount, excelTitle, null);
        this.exportConfig.put(EXCEL_PAGE_SIZE_KEY, pageSize < 1 ? EXCEL_DEFAULT_PAGE_SIZE : pageSize);
        if (fileName != null) {
            this.exportConfig.put(EXCEL_FILE_NAME_KEY, fileName);
        }
        if (sheetName != null) {
            this.exportConfig.put(EXCEL_SHEET_NAME_KEY, sheetName);
        }
    }

    public ExcelPagingExport(int totalCount, LinkedHashMap<String, String> excelTitle, Map<String, Object> exportConfig) {
        if (totalCount < 1) {
            throw new RuntimeException("暂无可导出数据");
        }
        this.totalCount = totalCount;
        if (excelTitle == null || excelTitle.isEmpty()) {
            throw new RuntimeException("请正确配置excelTitle参数");
        }
        this.excelTitle = excelTitle;
        this.taskNo = UUID.randomUUID().toString().replace("-", "");
        setDefaultConfig(exportConfig);
    }

    /**
     * 设置默认参数
     *
     * @param exportConfig
     * @return
     */
    private Map<String, Object> setDefaultConfig(Map exportConfig) {
        if (exportConfig == null) {
            exportConfig = new HashMap<>();
        }
        this.exportConfig = exportConfig;
        //临时目录
        if (getExportConfig(EXCEL_TEMP_PATH_KEY) == null) {
            exportConfig.put(EXCEL_TEMP_PATH_KEY, EXCEL_TEMP_PATH);
        }
        //文件名 不带后缀
        if (getExportConfig(EXCEL_FILE_NAME_KEY) == null) {
            exportConfig.put(EXCEL_FILE_NAME_KEY, this.taskNo);
        }
        //sheetName
        if (getExportConfig(EXCEL_SHEET_NAME_KEY) == null) {
            exportConfig.put(EXCEL_SHEET_NAME_KEY, "sheet");
        }
        //最大sheet数
        Integer maxSheet = getExportConfig(EXCEL_MAX_SHEET_KEY);
        if (maxSheet == null || maxSheet < 1 || maxSheet > EXCEL_MAX_SHEET_COUNT) {
            exportConfig.put(EXCEL_MAX_SHEET_KEY, EXCEL_MAX_SHEET_COUNT);
        }
        //一个sheet最大条数含标题
        Integer maxRow = getExportConfig(EXCEL_MAX_ROW_KEY);
        if (maxRow == null || maxRow < 2 || maxRow > EXCEL_MAX_ROW_COUNT) {
            exportConfig.put(EXCEL_MAX_ROW_KEY, EXCEL_MAX_ROW_COUNT);
        }
        //一个sheet最大条数
        Integer pageSize = getExportConfig(EXCEL_PAGE_SIZE_KEY);
        if (pageSize == null || pageSize < 1) {
            exportConfig.put(EXCEL_PAGE_SIZE_KEY, EXCEL_DEFAULT_PAGE_SIZE);
        }
        //时间格式
        if (getExportConfig(EXCEL_DATE_FORMAT_PATTERN_KEY) == null) {
            exportConfig.put(EXCEL_DATE_FORMAT_PATTERN_KEY, "yyyy-MM-dd HH:mm:ss");
        }
        return exportConfig;
    }

    /**
     * 实例化后设置单个配置
     *
     * @param key
     * @param value
     */
    public void setConfig(String key, Object value) {
        this.exportConfig.put(key, value);
    }

    /**
     * 导出配置参数
     */
    private Map<String, Object> exportConfig;
    /**
     * 记录总条数
     */
    private int totalCount;
    private LinkedHashMap<String, String> excelTitle;
    /***********************************************************/
    private String taskNo;
    private boolean dataIsMap;
    private int toatlExcel;
    private int toatlSheet;
    private SXSSFWorkbook currentWorkbook;
    private SXSSFSheet currentSheet;
    private int currentExcelIndex = 0;
    private int currentRowCount = 0;
    private List<File> resultFileList = null;
    private CellStyle titleStyle;
    private CellStyle bodyStyle;
    //存储最大列宽
    private Map<Integer, Integer> maxWidth = new HashMap<>();
    /***********************************************************/
    /**
     * 获取导出配置参数
     *
     * @param key
     * @param <C>
     * @return
     */
    private <C> C getExportConfig(String key) {
        if (this.exportConfig == null) {
            return null;
        }
        return (C) this.exportConfig.get(key);
    }

    /**
     * 分页回调
     *
     * @param page
     * @param pageSize
     * @return
     */
    public abstract List<T> callBack(int page, int pageSize);

    /**
     * 生产excel文件
     */
    public List<File> generateExcel() {
        int maxRow = getExportConfig(EXCEL_MAX_ROW_KEY);
        int maxSheet = getExportConfig(EXCEL_MAX_SHEET_KEY);
        int pageSize = getExportConfig(EXCEL_PAGE_SIZE_KEY);
        int maxData = maxRow - 1;
        //标题
        int maxExcelCount = maxData * maxSheet;
        this.toatlSheet = totalCount % maxData == 0 ? totalCount / maxData : totalCount / maxData + 1;
        this.toatlExcel = totalCount % maxExcelCount == 0 ? totalCount / maxExcelCount : totalCount / maxExcelCount + 1;
        List<T> dataList;
        long start = System.currentTimeMillis();
        int totalPage = totalCount % pageSize == 0 ? totalCount / pageSize : totalCount / pageSize + 1;
        log.info("任务:{},导出数据:{}条,需要分页查询:{}次,预计生成:{}个excel,共:{}个sheet", taskNo, totalCount, totalPage, toatlExcel, toatlSheet);
        for (int i = 1; i <= totalPage; i++) {
            dataList = callBack(i, pageSize);
            if (i == 1 && dataList != null && dataList.size() > 0) {
                dataIsMap = dataList.get(0) instanceof Map;
                createExcel();
            }
            //写入数据
            for (T rowData : dataList) {
                //当前sheet数达到最大,且sheet 达到maxRow 则需要创建新的excel
                if (currentWorkbook.getNumberOfSheets() == maxSheet && currentRowCount + 1 > maxRow) {
                    createExcel();
                }
                if (currentRowCount + 1 > maxRow) {
                    createSheet();
                }
                buildRow(Boolean.FALSE, rowData);
            }

        }
        writeFile();
        long end = System.currentTimeMillis();
        log.info("任务:{},导出数据耗时:{}毫秒", taskNo, end - start);
        return resultFileList;
    }

    /**
     * 删除生成文件
     */
    public void clearFile() {
        String dirPath = getExportConfig(EXCEL_TEMP_PATH_KEY) + taskNo;
        clearFile(dirPath);
    }

    /**
     * 删除指定目录
     *
     * @param dirPath
     */
    private void clearFile(String dirPath) {
        File file = new File(dirPath);
        if (!file.exists()) {
            return;
        }
        if (file.isFile()) {
            log.info("删除文件:{},状态:{}", file.getName(), file.delete());
        } else {
            File[] files = file.listFiles();
            if (files == null) {
                log.info("删除文件夹:{},状态:{}", file.getName(), file.delete());
            } else {
                for (int i = 0; i < files.length; i++) {
                    clearFile(files[i].getAbsolutePath());
                }
                log.info("删除文件夹:{},状态:{}", file.getName(), file.delete());
            }
        }
    }

    /**
     * 写入文件
     */
    private boolean writeFile() {
        if (currentWorkbook == null) {
            return Boolean.FALSE;
        }
        //最后一个sheet
        setAutoSize();
        String tempPath = getExportConfig(EXCEL_TEMP_PATH_KEY);
        File excelFile = new File(tempPath + taskNo);
        if (!excelFile.exists()) {
            excelFile.mkdirs();
        }
        String excelPath = tempPath + taskNo + "/" + getExportConfig(EXCEL_FILE_NAME_KEY);
        if (toatlExcel > 1) {
            excelPath = excelPath + "_" + currentExcelIndex;
        }
        excelPath = excelPath + ".xlsx";
        excelFile = new File(excelPath);
        if (resultFileList == null) {
            resultFileList = new ArrayList<>();
        }
        resultFileList.add(excelFile);
        BufferedOutputStream outputStream = null;
        try {
            outputStream = new BufferedOutputStream(new FileOutputStream(excelFile));
            currentWorkbook.write(outputStream);
            outputStream.flush();
            // 释放workbook所占用的所有windows资源
            currentWorkbook.dispose();
        } catch (IOException e) {
            log.error(e.getMessage(), e);
            return Boolean.FALSE;
        } finally {
            if (outputStream != null) {
                try {
                    outputStream.close();
                } catch (IOException e) {
                    log.error(e.getMessage(), e);
                }
            }
        }
        return Boolean.TRUE;
    }

    /**
     * 创建excel
     *
     * @return
     */
    private SXSSFWorkbook createExcel() {
        writeFile();
        //这样表示SXSSFWorkbook只会保留100条数据在内存中，其它的数据都会写到磁盘里
        currentWorkbook = new SXSSFWorkbook(EXCEL_ROW_ACCESS_WINDOW_SIZE);
        createSheet();
        currentExcelIndex = currentExcelIndex + 1;
        createTitleStyle();
        createBodyStyle();
        return currentWorkbook;
    }

    /**
     * 创建Sheet
     *
     * @return
     */
    private SXSSFSheet createSheet() {
        //设置上一个sheet宽度
        setAutoSize();
        String sheetName = getExportConfig(EXCEL_SHEET_NAME_KEY);
        if (toatlSheet > 1) {
            sheetName = sheetName + currentWorkbook.getNumberOfSheets();
        }
        currentSheet = currentWorkbook.createSheet(sheetName);
        currentRowCount = 0;
        buildRow(Boolean.TRUE, null);
        return currentSheet;
    }

    /**
     * 设置自动列宽
     */
    private void setAutoSize() {
        if (currentSheet == null) {
            return;
        }
        //调整列宽度
        for (int i = 0; i < excelTitle.size(); i++) {
            currentSheet.setColumnWidth(i, maxWidth.get(i) == null ? EXCEL_COLUMN_MIN_WIDTH : maxWidth.get(i));
        }
        //新sheet,重新赋值
        maxWidth.clear();
    }

    /**
     * 创建row并写入cell数据
     *
     * @param isTitle
     * @param t
     */
    private void buildRow(boolean isTitle, T t) {
        Row row = currentSheet.createRow(currentRowCount);
        int cIndex = 0;
        CellStyle cellStyle = isTitle ? titleStyle : bodyStyle;
        for (Map.Entry<String, String> titleField : excelTitle.entrySet()) {
            Cell cell = row.createCell(cIndex);
            cell.setCellValue(isTitle ? titleField.getValue() : formatVal(t, titleField.getKey(), getCellVal(t, titleField.getKey())));
            cell.setCellStyle(cellStyle);
            int length = 0;
            if (cell.getStringCellValue() != null) {
                length = cell.getStringCellValue().getBytes().length * 256;
            }
            length = length > EXCEL_COLUMN_MAX_WIDTH ? EXCEL_COLUMN_MAX_WIDTH : length;
            maxWidth.put(cIndex, Math.max(length, maxWidth.get(cIndex) == null ? EXCEL_COLUMN_MIN_WIDTH : maxWidth.get(cIndex)));
            cIndex++;
        }
        currentRowCount = currentRowCount + 1;
    }

    /**
     * 设置标题字体样式
     *
     * @return
     */
    protected CellStyle createTitleStyle() {
        // 设置字体
        Font font = currentWorkbook.createFont();
        //设置字体大小
        font.setFontHeightInPoints((short) 10);
        //字体加粗
        //font.setBold(true);
        //设置字体名字
        font.setFontName("Arial");
        //设置样式;
        CellStyle style = currentWorkbook.createCellStyle();
        //在样式用应用设置的字体;
        style.setFont(font);
        //设置自动换行;
        style.setWrapText(true);
        //设置水平对齐的样式为居中对齐;
        //style.setAlignment(HorizontalAlignment.CENTER);
        //设置垂直对齐的样式
        style.setVerticalAlignment(VerticalAlignment.BOTTOM);
        titleStyle = style;
        return style;
    }

    /**
     * 设置内容字体样式
     *
     * @return
     */
    protected CellStyle createBodyStyle() {
        bodyStyle = createTitleStyle();
        return bodyStyle;
    }

    /**
     * 获取单元格内容
     *
     * @param obj
     * @param fieldStr
     * @return
     */
    protected Object getCellVal(T obj, String fieldStr) {
        Object val;
        if (dataIsMap) {
            val = ((Map) obj).get(fieldStr);
            return formatVal(obj, fieldStr, val);
        }
        //使用spring反射工具类
        Field field = ReflectionUtils.findField(obj.getClass(), fieldStr);
        if (field == null) {
            throw new RuntimeException("字段:{" + fieldStr + "}不存在,请检查配置");
        }
        field.setAccessible(true);
        val = ReflectionUtils.getField(field, obj);
        return val;
    }

    /**
     * 格式化值
     *
     * @param obj
     * @param field
     * @param val
     * @return
     */
    protected String formatVal(T obj, String field, Object val) {
        if (val == null) {
            return "";
        }
        if (val instanceof Date) {
            return DateUtils.parseDate((Date) val, getExportConfig(EXCEL_DATE_FORMAT_PATTERN_KEY));
        }
        return String.valueOf(val);
    }

    public static void main(String[] args) {
        int totalCount = 100;
        LinkedHashMap<String, String> excelTitle = new LinkedHashMap<>();
        excelTitle.put("dpd", "序号");
        excelTitle.put("number", "数字");
        ExcelPagingExport<Map<String, Object>> export = new ExcelPagingExport(totalCount, excelTitle) {

            @Override
            public List callBack(int start, int pageSize) {
                int total = 100;
                int end = start * pageSize;
                end = end > total ? total : end;
                int begin = (start - 1) * pageSize + 1;
                log.info("执行第{}页查询,start:{}-{}", start, begin, end);
                List<Map<String, Object>> data = new ArrayList();
                for (int i = begin; i <= end; i++) {
                    Map map = new HashMap<>();
                    map.put("number", "18080711014340007704827");
                    map.put("dpd", i);
                    data.add(map);
                }

                return data;
            }
        };
        export.setConfig(EXCEL_MAX_ROW_KEY, 2);
        export.setConfig(EXCEL_MAX_SHEET_KEY, 1);
        export.generateExcel();
        //export.clearFile();
    }
}