Java导出百万条数据到Excecl

最新推荐文章于 2024-09-12 15:02:09 发布

芭比萌妹

最新推荐文章于 2024-09-12 15:02:09 发布

阅读量681

点赞数 7

文章标签： java windows python

本文链接：https://blog.csdn.net/weixin_49456013/article/details/135001644

版权

文章讲述了在项目中遇到的将百万级数据导出到Excel的问题，由于Excel版本限制，单线程处理耗时且可能导致OOM。作者提供了多线程、多sheet页以及分页查询的解决方案，并给出了相关Java代码示例。

摘要由CSDN通过智能技术生成

背景：项目中遇到了把百万条数据导出到excel中的业务，发现只能导出65536行

研究发现：
Excel 2003版：zhi列数dao最大256(IV，2的8次方)列，行数最大65536(2的16次方)行；

Excel 2007版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；

Excel 2013版：列数最大16384(XFD，2的14次方)，行数最大1048576(2的20次方)；

在改造过程中，发现单线程查询非常耗时，会导致超时，而且一次性拿百万条数据放到list中，会OOM。

最终解决方案：多线程+多sheet页+分页查询，我这里数据100W条，通过浏览器直接下载。废话不多说，直接上代码
ExportExcelController.java

@Autowired
private ExportExcelService exportExcelService;

//param参数是你业务需要的一些参数
@PostMapping("/exportExcel")
public void exportExcel(@RequestBody ExportParam param, HttpServletResponse response){
    exportExcelService.exportExcel(param,response);
  }

ExportExcelService.java

void exportExcel(ExportParam param, HttpServletResponse response) {
//表头数据，根据自己实际业务
List<Map<String,Object>> tableTitleInfoList;

//查询需要导出的总数
long count =  exportExcelMapper.getCount();
//2007版Excel最大行数是1048576,表头占用一行，数据最大占用1048575,超过后进行分sheet
//sheet页数
 long sheet =  (count + 1048574) / 1048575;
// 创建工作簿
SXSSFWorkbook workbook = new SXSSFWorkbook();
List<SXSSFSheet> sheets = new ArrayList<>();
for (int i =1 ;i<= sheet;i++) {
 // 创建数据 sheet
  SXSSFSheet dataSheet = workbook.createSheet("数据"+i);
  //将第一列隐藏，根据自己的业务可不加，我这里导出的时候把表的id也导出了，但不给看给隐藏了
  dataSheet.setColumnHidden(0,true);
// 创建表头行
SXSSFRow headerRow = dataSheet.createRow(0);
 //设置样式
 CellStyle blackStyle = workbook.createCellStyle();
 //自动换行
  blackStyle.setWrapText(true);
  //存储最大列宽
  Map<Integer,Integer> maxWidth = new HashMap<>();
// 新增第一列id列
SXSSFCell idDataCell = headerRow.createCell(0);
idDataCell.setCellValue("id");
int columnIndex = 1;
 for (Map<String,Object> tableTitle : tableTitleInfoList) {
     String columnName = tableTitle.get("tableTitle").toString();
     SXSSFCell cell = headerRow.createCell(columnIndex);
     cell.setCellValue(columnName);
     maxWidth.put(columnIndex,columnName.getBytes().length  * 256 + 200);
     cell.setCellStyle(blackStyle);//设置自动换行
     columnIndex++;
 }

 sheets.add(dataSheet);
}
int size = 50000;
//循环次数
long cycles = count / size;
List<FutureTask<List<Map<String,Object>>> > resultList = new ArrayList<>();
for (int i = 0; i <= cycles+1; i++) {
    long offset = i * size;
    //具体的查询任务，也就是你实际的数据查询
    FutureTask<List<Map<String,Object>>> futureTask = new FutureTask<>(() ->  exportExcelMapper.getData(offset,size));
    //把任务丢给线程池调度执行
    threadPool.execute(futureTask);
    //future异步模式，把任务放进去，先不取结果
    resultList.add(futureTask);
}
 // 按行填充数据
 int rowIndex = 1;
 //已写进excel的行数
 int totalExcelCount = 1;
while (resultList.size() > 0) {
Iterator<FutureTask<List<Map<String,Object>>>> iterator = resultList.iterator();
	while (iterator.hasNext()) {
	  try {
	  //得到数据
	  List<Map<String, Object>> allDataList = iterator.next().get();
       //获取一个就删除一个任务
       iterator.remove();
       for (Map<String, Object> data : allDataList) {
		//找到数据该放到第几个sheet页
		long oneSheet =  (totalExcelCount + 1048574) / 1048575;
		SXSSFSheet dataSheet = sheets.get((int) (oneSheet - 1));
		//每次换新的sheet页行号需要重新算
		if (rowIndex % 1048576 == 0 ) {
           rowIndex = 1;
         }
SXSSFRow dataRow = dataSheet.createRow(rowIndex++);
totalExcelCount++;
int columnIndex = 1;
// 首先添加该行的第一列id数据
String id = data.get("id").toString();
SXSSFCell idTitleCell = dataRow.createCell(0);
idTitleCell.setCellValue(id);
// 再循环遍历其余列，添加数据
 for (Map<String,Object> tableTitle : tableTitleInfoList) {
 //表头和数据是通过code字段对应的
     String titleName = tableTitle.get("code").toString().toLowerCase();
     Object value = data.get(titleName);
     SXSSFCell cell = dataRow.createCell(columnIndex);
     if (value != null) {
         cell.setCellValue(value.toString());
     }
     columnIndex++;
 }
       }
	  }catch (InterruptedException  | ExecutionException e) {
      log.error("多线程查询出现异常：{}", e.getMessage());
       }
	
	}
}

// 导出Excel
        String fileName = "data";
        try {
            response.setContentType("application/octet-stream");
            response.setHeader("Content-Disposition", "attachment; filename="
                    + URLEncoder.encode(fileName + ".xls", "UTF-8"));
            OutputStream out = response.getOutputStream();
            workbook.write(out);
            workbook.close();
        } catch (Exception e) {
            e.printStackTrace();
        }
}