apache poi excel export

红衣女妖仙

已于 2024-06-17 17:19:27 修改

阅读量449

点赞数 3

文章标签： apache excel export

于 2024-06-17 15:29:15 首次发布

本文链接：https://blog.csdn.net/XGLLHZ/article/details/139745441

版权

apache poi excel export

水一篇凑个数（啊水文章好羞耻啊！请原谅我私密马赛！）

1 ExcelColumn

@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class ExcelColumn implements Serializable {

    @Serial
    private static final long serialVersionUID = -14668049202148498L;

    private String column;   // 字段名

    private String columnName;   // 列名

    private int width;   // 列宽
    
    // 可自定义其它表格属性
}

2 MultipleSheetExcel

@Data
@Builder
@ToString
@NoArgsConstructor
@AllArgsConstructor
public class MultipleSheetExcel implements Serializable {

    @Serial
    private static final long serialVersionUID = -1469875755370531986L;

    private String sheetName;

    private List<ExcelColumn> columns;

    private List<Map<String, Object>> data;
}

3 ExcelExport

public class ExcelExport {

    public static final DateTimeFormatter DATE_TIME_FORMATTER = DateTimeFormatter.ofPattern("yyyyMMddHHmmss");

    public static final int MAX_ROW_2003 = 65536;

    public static final int MAX_ROW_2007 = 1048576;

    public static final String DEFAULT_SHEET = "sheet";

    private static final Integer DEFAULT_COLUMN_WIDTH = 3000;

    private static final String DEFAULT_CELL_VALUE=  "";

    /**
     * export multiple sheet excel
     * @param response
     * @param columns
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,
                                   List<Map<String, Object>> data) throws Exception {
        if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
            return;
        }

        exportExcel(response, generateName(), columns, data);
    }

    /**
     * export excel
     * @param response
     * @param fileName
     * @param columns
     * @param data
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, String fileName, List<ExcelColumn> columns,
                                   List<Map<String, Object>> data) throws Exception {
        if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
            return;
        }

        if (!StringUtils.hasText(fileName)) {
            fileName = generateName();
        }

        exportMultipleExcel(response, buildMultipleSheetExcel(columns, data, null), fileName);
    }

    /**
     * export excel
     * @param response
     * @param columns
     * @param data
     * @param excelType
     * @throws Exception
     */
    public static void exportExcel(HttpServletResponse response, List<ExcelColumn> columns,
                                   List<Map<String, Object>> data, String excelType) throws Exception {
        if (CollectionUtils.isEmpty(columns) || CollectionUtils.isEmpty(data)) {
            return;
        }

        List<MultipleSheetExcel> list = buildMultipleSheetExcel(columns, data, excelType);

        exportMultipleExcel(response, generateName(), excelType,
                list, calculateSize(list));
    }

    /**
     * export multiple sheet excel
     * @param response
     * @param list
     * @throws Exception
     */
    public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list) throws Exception {

        exportMultipleExcel(response, list, generateName());
    }

    /**
     * export multiple sheet excel
     * @param response
     * @param excelType
     * @param list
     * @throws Exception
     */
    public static void exportMultipleExcel(HttpServletResponse response, String excelType, List<MultipleSheetExcel> list) throws Exception {

        exportMultipleExcel(response, generateName(), excelType, list, calculateSize(list));
    }

    /**
     * export multiple sheet excel
     * @param response
     * @param list
     * @param fileName
     * @throws Exception
     */
    public static void exportMultipleExcel(HttpServletResponse response, List<MultipleSheetExcel> list, String fileName) throws Exception {

        if (!StringUtils.hasText(fileName)) {
            fileName = generateName();
        }

        exportMultipleExcel(response, fileName, ExcelTypeEnum.XLSX.getValue(), list, calculateSize(list));
    }

    /**
     * export multiple sheet excel
     * @param response
     * @param fileName
     * @param excelType
     * @param list
     * @param dataSize
     * @throws Exception
     */
    public static void exportMultipleExcel(HttpServletResponse response, String fileName, String excelType,
                                           List<MultipleSheetExcel> list, int dataSize)
            throws Exception {

        Workbook workbook = createWorkbook(excelType, dataSize);

        CellStyle titleStyle = workbook.createCellStyle();
        Font titleFont = workbook.createFont();
        titleFont.setFontHeightInPoints((short) 16);
        titleStyle.setFont(titleFont);
        titleStyle.setAlignment(HorizontalAlignment.CENTER);

        CellStyle dataStyle = workbook.createCellStyle();
        dataStyle.setAlignment(HorizontalAlignment.CENTER);

        String sheetName;
        List<ExcelColumn> columns;
        List<Map<String, Object>> data;
        Sheet sheet;
        Row title;
        Cell titleCell;
        Row heads;
        for (MultipleSheetExcel sheetExcel : list) {
            if (CollectionUtils.isEmpty(columns = sheetExcel.getColumns()) || CollectionUtils.isEmpty(data = sheetExcel.getData())) {
                continue;
            }

            if (!StringUtils.hasText(sheetName = sheetExcel.getSheetName())) {
                sheetName = DEFAULT_SHEET;
            }

            sheet = workbook.createSheet(sheetName);

            title = sheet.createRow(0);
            titleCell = title.createCell(0);
            sheet.addMergedRegion(new CellRangeAddress(0, 0, 0, columns.size()));
            titleCell.setCellValue(sheetName);

            titleCell.setCellStyle(titleStyle);

            heads = sheet.createRow(1);

            Cell headCell;
            CellStyle headStyle = workbook.createCellStyle();
            Font headFont = workbook.createFont();
            headFont.setFontHeightInPoints((short) 12);
            headStyle.setFont(headFont);
            headStyle.setAlignment(HorizontalAlignment.CENTER);

            int width;
            ExcelColumn excelColumn;
            for (int i = 0; i < columns.size(); i++) {
                excelColumn = columns.get(i);
                if ((width = excelColumn.getWidth()) <= 0) {
                    width = DEFAULT_COLUMN_WIDTH;
                }

                headCell = heads.createCell(i);
                headCell.setCellValue(excelColumn.getColumnName());
                headCell.setCellStyle(headStyle);
                sheet.setColumnWidth(i, width);
            }

            Row row;
            Map<String, Object> map;
            for (int i = 0; i < data.size(); i++) {

                map = data.get(i);
                row = sheet.createRow(i + 2);

                Object value;
                Cell dataCell;
                ExcelColumn column;
                for (int j = 0; j < columns.size(); j++) {
                    column = columns.get(j);
                    if (ObjectUtils.isEmpty(value = map.get(column.getColumn()))) {
                        value = DEFAULT_CELL_VALUE;
                    }

                    dataCell = row.createCell(j);
                    dataCell.setCellValue(String.valueOf(value));
                    dataCell.setCellStyle(dataStyle);
                }
            }
        }

        downloadExcel(response, fileName, excelType, workbook);
    }

    /**
     * download
     * @param response
     * @param fileName
     * @param excelType
     * @param workbook
     * @throws IOException
     */
    public static void downloadExcel(HttpServletResponse response, String fileName, String excelType, Workbook workbook) throws IOException {
        try {
            response.setCharacterEncoding("UTF-8");
            response.setHeader("Content-Type", "application/vnd.ms-excel");
            response.setHeader("Content-Disposition", "attachment;filename="
                    + URLEncoder.encode(fileName + "." + excelType, StandardCharsets.UTF_8));
            workbook.write(response.getOutputStream());
        } catch (Exception e) {
            throw new IOException(e.getMessage());
        }
    }

    /**
     * create workbook
     * @param excelType
     * @param size
     * @return
     */
    private static Workbook createWorkbook(String excelType, int size) {
        if (ExcelTypeEnum.XLS.getValue().equals(excelType)) {
            return new HSSFWorkbook();
        } else if (size < 100000) {
            return new XSSFWorkbook();
        } else {
            return new SXSSFWorkbook();
        }
    }

    /**
     * build MultipleSheetExcel
     * @param columns
     * @param data
     * @param excelType
     * @return
     */
    private static List<MultipleSheetExcel> buildMultipleSheetExcel(List<ExcelColumn> columns,
                                                                    List<Map<String, Object>> data, String excelType) {
        int maxSize;
        if (StringUtils.hasText(excelType) && ExcelTypeEnum.XLS.getValue().equals(excelType)) {
            maxSize = MAX_ROW_2003;
        } else {
            maxSize = MAX_ROW_2007;
        }

        int sheets;
        if ((data.size() % maxSize) > 0) {
            sheets = (data.size() / maxSize) + 1;
        } else {
            sheets = data.size() / maxSize;
        }

        int remainNumber = data.size(), fromIndex, toIndex;
        List<MultipleSheetExcel> list = new ArrayList<>(sheets);
        for (int i = 0; i < sheets; i++) {

            fromIndex = i * maxSize;
            toIndex = fromIndex + Math.min(remainNumber, maxSize);

            list.add(MultipleSheetExcel.builder()
                    .sheetName(DEFAULT_SHEET + i)
                    .columns(columns)
                    .data(data.subList(fromIndex, toIndex))
                    .build());

            remainNumber = remainNumber - (toIndex - fromIndex);
        }

        return list;
    }

    /**
     * calculate data size
     * @param list
     * @return
     */
    private static int calculateSize(List<MultipleSheetExcel> list) {
        int size = 0;
        List<Map<String, Object>> data;
        for (MultipleSheetExcel sheetExcel : list) {
            if (CollectionUtils.isEmpty(data = sheetExcel.getData())) {
                continue;
            }

            size += data.size();
        }
        return size;
    }

    /**
     * generate name
     * @return
     */
    private static String generateName() {
        LocalDateTime now = LocalDateTime.now();
        return DATE_TIME_FORMATTER.format(now);
    }

    /**
     * excel type enum
     */
    enum ExcelTypeEnum {

        XLS("xls"),

        XLSX("xlsx");

        private String value;

        ExcelTypeEnum(String value) {
            this.value = value;
        }

        public String getValue() {
            return value;
        }

        public void setValue(String value) {
            this.value = value;
        }
    }
}