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

使用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();

    }


}

结果:

©️2020 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页