大连城市建设网站,上海注册公司流程及费用,江门网站建设方案,带有互动的网站开发一、背景 有一次项目的需求要求导出excel#xff0c;并且将不同的数据分别写到不同的sheet中。
二、 方案概述 首先一开始使用easyexcel去导出excel#xff0c;结果发现导出时间需要3秒左右。于是想着能不能缩短excel导出时间#xff0c;于是第一次尝试使用异步线程去查询数…一、背景 有一次项目的需求要求导出excel并且将不同的数据分别写到不同的sheet中。
二、 方案概述 首先一开始使用easyexcel去导出excel结果发现导出时间需要3秒左右。于是想着能不能缩短excel导出时间于是第一次尝试使用异步线程去查询数据库却发现接口的时间并没有明显缩短于是自己就开始排查耗时的操作于是发现是写sheet的时候是串行执行并且每个写sheet的时间并不短尤其在sheet比较多的时候会导致导出的时间比较长。于是想着能不能使用异步线程并发去写sheet但是使用的时候报错。后来去找报错的原因是因为easyexcel并不支持并发写。于是我就转战POI。尝试是否能够并发写入多个sheet。 使用easyexcel写入多个sheet try {response.setContentType(application/vnd.openxmlformats-officedocument.spreadsheetml.sheet);response.setCharacterEncoding(utf-8);// 这里URLEncoder.encode可以防止中文乱码 当然和easyexcel没有关系String fileName URLEncoder.encode(EXCEL, UTF-8).replaceAll(\\, %20);response.setHeader(Content-disposition, attachment;filename*utf-8 fileName .xlsx);AtomicInteger atomicInteger new AtomicInteger(0);ExcelWriter build EasyExcel.write(response.getOutputStream(),VirtualInstanceDataPoint.class).build();list.stream().map(i - CompletableFuture.supplyAsync(() - {return service.list();}, executor)).collect(Collectors.toList()).stream().map(CompletableFuture::join).collect(Collectors.toList()).forEach(r-{int andIncrement atomicInteger.getAndIncrement();WriteSheet build1 EasyExcel.writerSheet(andIncrement, r.get(0).getDevice() andIncrement).build();build.write(r, build1);});build.finish();response.flushBuffer();} catch (Exception e) {// 重置responseresponse.reset();response.setContentType(application/json);response.setCharacterEncoding(utf-8);response.getWriter().println(JSON.toJSONString(R.error().message(e.getMessage()).code(20007)));
}
并发写入多个sheet会报
org.apache.poi.openxml4j.exceptions.PartAlreadyExistsException: A part with the name /xl/worksheets/sheet1.xml already exists : Packages shall not contain equivalent part names and package implementers shall neither create nor recognize packages with equivalent part names. [M1.12] POI写入多个sheet String[] EXPORT_HEADER {head1,head2};GetMapping(export3)ApiOperation(value excel导出信息)SneakyThrowspublic void export3(HttpServletResponse response) {OutputStream outputStream response.getOutputStream();response.reset();response.setContentType(application/vnd.ms-excel);response.setHeader(Content-disposition, attachment;filenametemplate.xls);AtomicInteger atomicInteger new AtomicInteger();HSSFWorkbook workbook new HSSFWorkbook();Font font workbook.createFont();font.setBold(true);HSSFCellStyle cellStyle workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFont(font);ListIndexData indexDatas new ArrayList();indexDatas.add(new IndexData(1,1.1));indexDatas.add(new IndexData(2,2.2));indexDatas.add(new IndexData(3,3.3));for (IndexData indexData : indexDatas) {HSSFSheet sheet workbook.createSheet(indexData.getStr());HSSFRow row sheet.createRow(0);// 创建表头for (int i 0; i EXPORT_HEADER.length; i) {HSSFCell cell row.createCell(i);cell.setCellValue(EXPORT_HEADER[i]);cell.setCellStyle(cellStyle);}row sheet.createRow(1);row.createCell(0).setCellValue(indexData.getStr());row.createCell(1).setCellValue(indexData.getDoubleData());}workbook.write(outputStream);outputStream.flush();outputStream.close();}static class IndexData {public IndexData(String string, Double doubleData) {this.str string;this.doubleData doubleData;}public String getStr() {return str;}public Double getDoubleData() {return doubleData;}private String str;private Double doubleData;}
POI多线程写多个sheet String[] EXPORT_HEADER {head1,head2};GetMapping(export3)ApiOperation(value excel导出)SneakyThrowspublic void export3(HttpServletResponse response) {OutputStream outputStream response.getOutputStream();response.reset();response.setContentType(application/vnd.ms-excel);response.setHeader(Content-disposition, attachment;filenametemplate.xls);AtomicInteger atomicInteger new AtomicInteger();HSSFWorkbook workbook new HSSFWorkbook();Font font workbook.createFont();font.setBold(true);HSSFCellStyle cellStyle workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFont(font);ListIndexData indexDatas new ArrayList();indexDatas.add(new IndexData(1,1.1));indexDatas.add(new IndexData(2,2.2));indexDatas.add(new IndexData(3,3.3));indexDatas.stream().map(data -CompletableFuture.runAsync(() -{HSSFSheet sheet workbook.createSheet(data.getStr());HSSFRow row sheet.createRow(0);// 创建表头for (int i 0; i EXPORT_HEADER.length; i) {HSSFCell cell row.createCell(i);cell.setCellValue(EXPORT_HEADER[i]);cell.setCellStyle(cellStyle);}row sheet.createRow(1);row.createCell(0).setCellValue(data.getStr());row.createCell(1).setCellValue(data.getDoubleData());})).collect(Collectors.toList()).stream().map(CompletableFuture::join).collect(Collectors.toList());workbook.write(outputStream);outputStream.flush();outputStream.close();}static class IndexData {public IndexData(String string, Double doubleData) {this.str string;this.doubleData doubleData;}public String getStr() {return str;}public Double getDoubleData() {return doubleData;}private String str;private Double doubleData;}但是有时候会报错
java.lang.IllegalArgumentException: calculated end index (2576) is out of allowable range (2564..2569)
是因为在 子线程中创建sheet产生并发。
一个解决方案是主线程预先创建sheet
另一个方案是为创建sheet的操作加锁 GetMapping(export1)ApiOperation(value excel导出信息)SneakyThrowspublic void export2(HttpServletResponse response) {OutputStream outputStream response.getOutputStream();response.reset();response.setContentType(application/vnd.ms-excel);response.setHeader(Content-disposition, attachment;filenametemplate.xls);AtomicInteger atomicInteger new AtomicInteger();HSSFWorkbook workbook new HSSFWorkbook();Font font workbook.createFont();font.setBold(true);HSSFCellStyle cellStyle workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFont(font);list.stream().map(instanceId - CompletableFuture.runAsync(() - {List collect service.list();HSSFSheet sheet workbook.createSheet(collect.get(0).getDevice() atomicInteger.getAndIncrement());HSSFRow row sheet.createRow(0);// 创建表头for (int i 0; i EXPORT_HEADER.length; i) {HSSFCell cell row.createCell(i);cell.setCellValue(EXPORT_HEADER[i]);cell.setCellStyle(cellStyle);}for (int i 0; i collect.size(); i) {row sheet.createRow(i 1);row.createCell(0).setCellValue(collect.get(i).getInstanceId());row.createCell(1).setCellValue(collect.get(i).getDevice());row.createCell(2).setCellValue(collect.get(i).getDataId());row.createCell(3).setCellValue(collect.get(i).getDataName());}}, executor)).collect(Collectors.toList()).stream().map(CompletableFuture::join).collect(Collectors.toList());workbook.write(outputStream);outputStream.flush();outputStream.close();}
以下使用加锁方式 String[] EXPORT_HEADER {head1,head2};GetMapping(export3)ApiOperation(value excel导出信息)SneakyThrowspublic void export3(HttpServletResponse response) {OutputStream outputStream response.getOutputStream();response.reset();response.setContentType(application/vnd.ms-excel);response.setHeader(Content-disposition, attachment;filenametemplate.xls);AtomicInteger atomicInteger new AtomicInteger();HSSFWorkbook workbook new HSSFWorkbook();Font font workbook.createFont();font.setBold(true);HSSFCellStyle cellStyle workbook.createCellStyle();cellStyle.setAlignment(HorizontalAlignment.CENTER);cellStyle.setVerticalAlignment(VerticalAlignment.CENTER);cellStyle.setFont(font);ListIndexData indexDatas new ArrayList();indexDatas.add(new IndexData(1,1.1));indexDatas.add(new IndexData(2,2.2));indexDatas.add(new IndexData(3,3.3));indexDatas.stream().map(data -CompletableFuture.runAsync(() -{HSSFSheet sheet getSheet(data, workbook);HSSFRow row sheet.createRow(0);// 创建表头for (int i 0; i EXPORT_HEADER.length; i) {HSSFCell cell row.createCell(i);cell.setCellValue(EXPORT_HEADER[i]);cell.setCellStyle(cellStyle);}row sheet.createRow(1);row.createCell(0).setCellValue(data.getStr());row.createCell(1).setCellValue(data.getDoubleData());})).collect(Collectors.toList()).stream().map(CompletableFuture::join).collect(Collectors.toList());workbook.write(outputStream);outputStream.flush();outputStream.close();}org.jetbrains.annotations.NotNullprivate synchronized static HSSFSheet getSheet(IndexData data, HSSFWorkbook workbook) {HSSFSheet sheet workbook.createSheet(data.getStr());return sheet;}
但是这种方式还是会有一些并发带来的错误。 java.lang.NullPointerException: null at org.apache.poi.hssf.record.SSTSerializer.serialize(SSTSerializer.java:70) at org.apache.poi.hssf.record.SSTRecord.serialize(SSTRecord.java:279) at org.apache.poi.hssf.record.cont.ContinuableRecord.getRecordSize(ContinuableRecord.java:60) at org.apache.poi.hssf.model.InternalWorkbook.getSize(InternalWorkbook.java:1072) at org.apache.poi.hssf.usermodel.HSSFWorkbook.getBytes(HSSFWorkbook.java:1474) at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1386) at org.apache.poi.hssf.usermodel.HSSFWorkbook.write(HSSFWorkbook.java:1374) 但是在本机实测100个线程10个循环出错的个数在20-30之间
我们可以捕获这些错误使用do while循环当出错的时候可以清空状态再次重试。
总结 该方法只是本菜鸡的愚见使用这种方式的确可以完成并发写sheet缩短接口的相应速度将3秒左右的接口降低到50ms左右。应该能适合sheet略多但并发量、数据量不多的excel导出但本人也是第一次使用POI,所以可能有错误希望大佬们能够多多指点。
