POI导出MySQL数据库数据到excel文件（xls格式），附实验结果（单张sheet最多可有65536行）& FileNotFoundException 大量数据建议使用SXSSFWorkbook

最新推荐文章于 2024-09-04 16:33:53 发布

Be_insighted

最新推荐文章于 2024-09-04 16:33:53 发布

阅读量640

点赞数 1

分类专栏： Java 文章标签： java spring excel 数据库 poi

本文链接：https://blog.csdn.net/be_insighted/article/details/107360587

版权

Java 专栏收录该内容

101 篇文章 2 订阅

订阅专栏

使用POI导出MySQL数据库数据到excel文件（xls格式）

注意事项：单张sheet最多存储65536行！否则报错！

Caused by: java.lang.IllegalArgumentException: Invalid row number (65536) outside allowable range (0..65535) !

单张sheet 数据过多阅读时比较麻烦，建议数据量10000条以下！

/**
 * @Author： Be.insighted
 * Description：
 * @date Create on 2020/7/15 14:35
 **/

    @PostMapping(value = "/merchant/export", produces = MediaType.APPLICATION_OCTET_STREAM_VALUE)
    @ApiOperation(value = "导出商家维度数据")
    public void exportMerchantRecord(@RequestBody QueryRecordReq req, HttpServletResponse response) {
        if (req == null || req.getStartTime() == null || req.getEndTime() == null) {
            throw new BaseRetException(BaseRet.createMissParamFailureRet());
        }
        SimpleDateFormat format = new SimpleDateFormat("yyyyMMddHHmmss");
        String filename = format.format(new Date());
        response.setContentType("application/binary;charset=UTF-8");
        response.setHeader("Content-disposition", "attachment;filename=" + filename + ".xls");
        response.setHeader("Pragma", "no-cache");
        response.setHeader("Cache-Control", "no-cache");
        response.setDateHeader("Expires", 0);
        try (ServletOutputStream os = response.getOutputStream()) {
            byte[] content = exportRecordService.exportMerchantCuisineRecord(req, ReportDownloadRecordEntity.DIMENSION.MERCHANT.getDimensionName(), filename + ".xls");
            os.write(content);
            os.flush();
        } catch (IOException e) {
            throw new BaseRetException(BaseRet.createFailureRet("生成EXCEL异常！"));
        }
    }

    /**
     * 商家维度数据导出
     *
     * @param req
     * @param dimensionName
     * @param fileName
     * @return
     * @throws IOException
     */
    public byte[] exportMerchantCuisineRecord(QueryRecordReq req, String dimensionName, String fileName) throws IOException {
        List<RecordMerchantVO> merchantVOS = cuisineRecordService.listMerchantRecords(req);
        HSSFWorkbook xwb = cuisineRecordService.exportMerchantData(merchantVOS);
        return getBytes(dimensionName, fileName, xwb, req.getEndTime(), req.getStartTime());

    }


/**
     * 导出商家维度记录
     *
     * @param merchantVOS
     * @return
     */
    public HSSFWorkbook exportMerchantData(List<RecordMerchantVO> merchantVOS) {
        String sheetName = ReportDownloadRecordEntity.DIMENSION.MERCHANT.getDimensionName().concat(convertCalender2String());
        HSSFWorkbook wb = createWorkbook(sheetName, SHEET_HEADERS.MERCHANT);
        if (wb == null) {
            throw new BaseRetException(BaseRet.createFailureRet("创建EXCEL失败!"));
        }
        int rowNum = 1;
        HSSFSheet sheet = wb.getSheet(sheetName);
        if (sheet == null) {
            throw new BaseRetException(BaseRet.createFailureRet("获取EXCEL工作簿失败!"));
        }
        if (CollectionUtils.isEmpty(merchantVOS)) {
            return wb;
        }
        HSSFCellStyle cellStyle = wb.createCellStyle();
        for (RecordMerchantVO vo : merchantVOS) {
            if (null == vo) {
                continue;
            }
            Integer cellNum = 0;
            HSSFRow row = sheet.createRow(rowNum++);
            // "商家账号"
            HSSFCell cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getMerchantId());

            //"店铺名称"
            cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getStoreName());

            // "店铺ID"
            cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getStoreId());

            //"烹饪菜谱次数"
            cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getCuisineCount());

            // "烹饪个数"
            // 烹饪菜谱个数（统计商家烹饪的菜谱个数，如果商家有**100**个，使用了其中**80**个烹饪总次数2000，展示为**80**）
            //  烹饪菜谱次数（统计商家烹饪的菜谱个数，如果商家有**100**个，使用了其中80个烹饪总次数**2000**，展示为**2000**）
            cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getRecipeUsedCount());

            // "烹饪异常次数"
            cell = row.createCell(cellNum++);
            cell.setCellStyle(cellStyle);
            cell.setCellValue(vo.getErrorCuisineCount());

            // 设备使用天数
            cell = row.createCell(cellNum++);
            cell.setCellValue(vo.getDeviceUsageDays());
        }
        return wb;
    }


// 保存导出记录到MySQL、上传文件到文件服务器
private byte[] getBytes(String dimensionName, String fileName, HSSFWorkbook xwb, Long endTime, Long startTime) throws IOException {
        UploadOrderRecordDto recordDto = new UploadOrderRecordDto();
        byte[] bContent = cuisineRecordService.XWorkbook2ByteArray(xwb);
        recordDto.setContent(bContent);
        recordDto.setFileName(fileName);
        // 上传到文件服务器
        String fileKey = uploadExcelFile(recordDto);
        log.info("导出记录：fileKey{}", fileKey);
        DurationDto dto = new DurationDto();
        dto.setEndTime(endTime).setStartTime(startTime);
        //  保存到DB
        saveExportRecord(dto, fileName, dimensionName, fileKey);
        return bContent;
    }

public enum SHEET_HEADERS {
        String[] MERCHANT = {"商家账号", "店铺名称", "店铺ID", "烹饪菜谱次数", "菜谱烹饪个数", "烹饪异常次数", "使用设备的天数"};
    }


    public enum DIMENSION {
        MERCHANT (0,"商家维度");     
        private Integer dimensionCode;
        private String dimensionName;
        DIMENSION(){}
        DIMENSION(Integer dimensionCode, String dimensionName){
            this.dimensionCode = dimensionCode;
            this.dimensionName = dimensionName;
        }

POM依赖：

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.9</version>
</dependency>
<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.9</version>
</dependency>

实验结果

写在最后：

POI导出Excel有三种形式，分别是：

1.HSSFWorkbook

2.XSSFWorkbook

3.SXSSFWorkbook。

HSSFWorkbook:是操作Excel2003以前（包括2003）的版本，扩展名是.xls；

XSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；

SXSSFWorkbook:是操作Excel2007后的版本，扩展名是.xlsx；

第一种：HSSFWorkbook

poi导出excel最常用的方式；但是此种方式的局限就是导出的行数至多为65536行，超出65536条后系统就会报错。

行数不足65536行可以使用、或者超出时使用多张sheet。

第二种：XSSFWorkbook

这种形式的出现是为了突破HSSFWorkbook的65535行局限。其对应的是excel2007(1048576行，16384列)扩展名为“.xlsx”，最多可以导出104万行，不过这样就伴随着一个问题---OOM内存溢出，原因是你所创建的book sheet row cell等此时是存在内存的并没有持久化。

第三种：SXSSFWorkbook

从POI 3.8版本开始，提供了一种基于XSSF的低内存占用的SXSSF方式。对于大型excel文件的创建，一个关键问题就是，要确保不会内存溢出。其实，就算生成很小的excel（比如几Mb），它用掉的内存是远大于excel文件实际的size的。如果单元格还有各种格式（比如，加粗，背景标红之类的），那它占用的内存就更多了。对于大型excel的创建且不会内存溢出的，就只有SXSSFWorkbook了。它的原理很简单，用硬盘空间换内存（就像hash map用空间换时间一样）。

SXSSFWorkbook是streaming版本的XSSFWorkbook,它只会保存最新的excel rows在内存里供查看，在此之前的excel rows都会被写入到硬盘里（Windows电脑的话，是写入到C盘根目录下的temp文件夹）。被写入到硬盘里的rows是不可见的/不可访问的。只有还保存在内存里的才可以被访问到。

SXSSF与XSSF的对比：

a. 在一个时间点上，只可以访问一定数量的数据

b. 不再支持Sheet.clone()

c. 不再支持公式的求值

d. 在使用Excel模板下载数据时将不能动态改变表头，因为这种方式已经提前把excel写到硬盘的了就不能再改了

当数据量超出65536条后，在使用HSSFWorkbook或XSSFWorkbook，程序会报OutOfMemoryError：Javaheap space;内存溢出错误。这时应该用SXSSFworkbook。

如果你只是想试试POI、请看这段内容：

得先创建文件目录

File file  = new File("D:/excel/test-xls.xls")

if (file.exists()) { 
    file.delete(); 
    file.createNewFile(); 
}

否则报错：Exception in thread "main" java.io.FileNotFoundException: D:\excel\test-xls.xls (系统找不到指定的路径。)

/**
 * @Author： Be.insighted
 * Description：
 * @date Create on 2020/8/7 15:17
 **/
public class TestProduceExcel {

    public static void main(String[] args) throws Exception {

        testExcel03();

        testExcel07();
    }

    static void testExcel03() throws Exception {
        // 1、创建工作簿
        HSSFWorkbook hssfWorkbook = new HSSFWorkbook();

        // 2、创建表格 sheet
        HSSFSheet hssfSheet = hssfWorkbook.createSheet("sheet1");

        // 3、创建行
        HSSFRow row = hssfSheet.createRow(0);

        // 4、创建单元格
        HSSFCell cell = row.createCell(0);

        // 5、设置单元格内容
        cell.setCellValue("生成Excel xls 格式");

        FileOutputStream out = new FileOutputStream("D:/excel/test-xls.xls");

        hssfWorkbook.write(out);

        out.close();

    }

    static void testExcel07() throws Exception {
        // 1、创建工作簿
        XSSFWorkbook workbook = new XSSFWorkbook();

        // 2、创建表格 sheet
        XSSFSheet sheet = workbook.createSheet("sheet1");

        // 3、创建行
        XSSFRow row = sheet.createRow(0);

        // 4、创建单元格
        XSSFCell cell = row.createCell(0);

        // 5、设置单元格内容
        cell.setCellValue("生成Excel xlsx 格式");

        FileOutputStream out = new FileOutputStream("D:/excel/test-xls.xlsx");

        workbook.write(out);

        out.close();

    }


}