POI根据Excel模板导出数据

最新推荐文章于 2025-05-03 23:47:39 发布

原创最新推荐文章于 2025-05-03 23:47:39 发布 · 7.1k 阅读

9 ·

CC 4.0 BY-SA版权

文章标签：

#excel #null #buffer #object #string

J2SE 专栏收录该内容

35 篇文章

订阅专栏

本文介绍了一种将数据从DTO对象数组批量导出到Excel文件的方法。该程序能够处理大量数据，并通过创建多个工作表来避免单个工作表数据过多的问题。文章提供了关键代码片段，展示了如何初始化Excel工作簿、复制模板工作表以及填充数据。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

Excel和Word数据导入导出通用程序今天已经完成了。把excel的部分代码贴过来吧。

    public File export(Class dtoClass, Object[] dtos, int recordCountPerSheet) {

    Assert.notNull(dtoClass);
    Assert.isTrue(dtos instanceof Dto[]);

    POIFSFileSystem fis = null;
    try {
        //获得excel模板
        fis = new POIFSFileSystem(loadExcleTemplate(dtoClass));
    } catch(IOException e) {
        if(logger.isErrorEnabled()) {
        logger.error(e);
        }
        throw new ExportException(e);
    }

    OutputStream fileOut = null;
    File excelOutput = null;
    try {
        HSSFWorkbook wb = new HSSFWorkbook(fis);
        excelOutput = this.generateFile();
        fileOut =
        new BufferedOutputStream(new FileOutputStream(excelOutput), Constants.DEFAULT_BUFFER_SIZE);

        if(dtos != null) {
        ExportDto exportDto = (ExportDto) mappingCache.get(dtoClass.getName());
        int dtoLength = dtos.length;
        //根据总的记录条数和每个sheet输出的条数计算出sheet个数
        int sheetCount = caculateSheetCount(dtoLength, recordCountPerSheet);
        //获得第一页的模板
        HSSFSheet templateSheet = wb.getSheetAt(0);
        int dtoIndexStart = 0;
        int dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
        for(int sheetIndex = 0; sheetIndex < sheetCount; sheetIndex ++) {
            //根据模板的sheet复制一个新的sheet
            HSSFSheet newSheet = createHSSFSheet(sheetIndex, wb, templateSheet, exportDto);
            //对新建的sheet填充数据
            for(int dtoIndex = dtoIndexStart; dtoIndex < dtoIndexEnd; dtoIndex ++) {
            processRow(exportDto.getStartRow() - 1 + dtoIndex % recordCountPerSheet, exportDto, (Dto)dtos[dtoIndex], newSheet);
            }
            dtoIndexStart = dtoIndexEnd;
            dtoIndexEnd = caculateDtoIndexEnd(dtoIndexStart, recordCountPerSheet, dtoLength);
        }
        }
        wb.removeSheetAt(0);//删除拷贝模板的第一页
        wb.write(fileOut);
        fileOut.flush();
        return excelOutput;
    } catch(Throwable e) {
        if(logger.isErrorEnabled()) {
        logger.error(e);
        }

        if(excelOutput != null && excelOutput.exists()) {
            excelOutput.delete();
        }
        throw new ExportException(e);
    } finally {
        if(fileOut != null) {
        try {
            fileOut.close();
        } catch(IOException e) {
            if(logger.isErrorEnabled()) {
            logger.error(e);
            }
        }
        }
    }
    }

    private InputStream loadExcleTemplate(Class dtoClass) throws IOException {
    Assert.notNull(templateCache);
    Resource template = (Resource) templateCache.get(dtoClass.getName());
    return template.getInputStream();
    }

    private HSSFSheet createHSSFSheet(int sheetIndex, HSSFWorkbook wb, HSSFSheet templateSheet, ExportDto exportDto) {

    HSSFSheet newSheet = wb.createSheet();

    int startRow = exportDto.getStartRow();
    int columnCount = exportDto.getExportDtoFieldsCount() + exportDto.getStartColumn() - 1;

    //拷贝设置的开始行的上面所有行
    for(int i = 0; i < startRow; i ++) {
        HSSFRow templateRow = templateSheet.getRow(i);
        if(templateRow != null) {
            HSSFRow newRow = newSheet.createRow(i);
            for(int j = 0; j < columnCount; j ++) {
            HSSFCell templateCell = templateRow.getCell((short)j);
            if(templateCell != null) {
                HSSFCell newCell = newRow.createCell((short)j);
                copyCell(templateCell, newCell);
            }
            }
        }
    }
    return newSheet;
    }

    private void copyCell(HSSFCell srcCell, HSSFCell distCell) {
    distCell.setCellStyle(srcCell.getCellStyle());
    if(srcCell.getCellComment() != null) {
        distCell.setCellComment(srcCell.getCellComment());
    }
    int srcCellType = srcCell.getCellType();
    distCell.setCellType(srcCellType);
    if (srcCellType == HSSFCell.CELL_TYPE_NUMERIC) {
        if (HSSFDateUtil.isCellDateFormatted(srcCell)) {
        distCell.setCellValue(srcCell.getDateCellValue());
        } else {
        distCell.setCellValue(srcCell.getNumericCellValue());
        }
    } else if (srcCellType == HSSFCell.CELL_TYPE_STRING) {
        distCell.setCellValue(srcCell.getRichStringCellValue());
    } else if (srcCellType == HSSFCell.CELL_TYPE_BLANK) {

    } else if (srcCellType == HSSFCell.CELL_TYPE_BOOLEAN) {
        distCell.setCellValue(srcCell.getBooleanCellValue());
    } else if (srcCellType == HSSFCell.CELL_TYPE_ERROR) {
        distCell.setCellErrorValue(srcCell.getErrorCellValue());
    } else if (srcCellType == HSSFCell.CELL_TYPE_FORMULA) {
        distCell.setCellFormula(srcCell.getCellFormula());
    } else {
    }
    }