java 之导出百万数据到excel

迷南。 2022-11-06 14:54 261阅读 0赞

## 说明 ##

首先申明，本人不是标题党。使用如下代码测试100w条数据导出到excel文件，单线程使用17s，多线程使用13s。当然，针对类似方法的使用，有人做过测试，比我的还详细。但是具体到个人，建议你使用的时候还是要自己测试一下。

[测试链接][Link 1]

## 依赖 ##

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

## 代码 ##

public static void main(String[] args) throws IOException { 
    		FileOutputStream targetFile = new FileOutputStream(new File("d:\\Users\\Desktop\\laji\\Import-device-exampleyy.xlsx"));
    
    // testSXSSFThreadPool(targetFile, 1000003);
    // testSXSSF(targetFile);
    	}
    
    	private static  final int PER_SHEET_LIMIT=50000;	//每页5w
    	private static void testSXSSFThreadPool(OutputStream targetFile, int total) throws IOException{ 
    		int pageNum = total / PER_SHEET_LIMIT;
    		int lastCount = total % PER_SHEET_LIMIT;
    		if (total > PER_SHEET_LIMIT) { 
    			long start = System.currentTimeMillis();
    			SXSSFWorkbook wb = new SXSSFWorkbook();
    			int sheet = lastCount == 0 ? pageNum : pageNum + 1;
    			CountDownLatch downLatch = new CountDownLatch(sheet);
    			Executor executor = Executors.newFixedThreadPool(sheet);
    			for (int c = 0; c < sheet; c++) { 
    				int rowNum = PER_SHEET_LIMIT;
    				if (c == pageNum) { 
    					if (lastCount == 0) { 
    						continue;
    					}
    					rowNum = lastCount;
    				}
    				Sheet wbSheet = wb.createSheet("page" + (c+1));
    				executor.execute(new PageTask(downLatch, wbSheet, rowNum));
    			}
    			try { 
    				downLatch.await();
    				wb.write(targetFile);
    				targetFile.flush();
    				targetFile.close();
    				long end = System.currentTimeMillis();
    				long seconds = (end - start)/1000;
    				System.out.println("线程池导出100w条数据花费时间："+seconds+" s");
    			} catch (InterruptedException e) { 
    				e.printStackTrace();
    			}
    		}else{ 
    			//这里是单页小于一定数直接使用单线程就好
    			long start = System.currentTimeMillis();
    			SXSSFWorkbook wb = new SXSSFWorkbook();
    			Sheet sheet = wb.createSheet("page" + 1);
    			Row row =null;
    
    			for (int i = 0; i < total; i++) { 
    				row = sheet.createRow(i + 1);
    				for (int j = 0; j < 10; j++) { 
    					row.createCell(j).setCellValue("test "+j);
    				}
    			}
    
    			wb.write(targetFile);
    			targetFile.flush();
    			targetFile.close();
    			long end = System.currentTimeMillis();
    			long seconds = (end - start)/1000;
    			System.out.println("导出100w条数据花费时间："+seconds+" s");
    		}
    
    	}

public class PageTask implements Runnable { 
        public static Logger logger = Logger.getLogger(PageTask.class);
        private CountDownLatch countDownLatch;
        private Sheet sheet;
        private List<TerminalExportDTO> data;
        private String lang;
        private CellStyle style;
    
        public PageTask(CountDownLatch countDownLatch, Sheet sheet, String lang, CellStyle style, List<TerminalExportDTO> data){ 
            this.countDownLatch = countDownLatch;
            this.sheet = sheet;
            this.data = data;
            this.lang = lang;
            this.style = style;
        }
    
        @Override
        public void run() { 
            try { 
                Row row =null;
                int total = data.size();
                //设置列标题
                Row row2 = sheet.createRow(0);
                String[] rowsName ;
                rowsName = new String[] { };
                for (int i = 0; i < rowsName.length; i++) { 
                    Cell cell2 = row2.createCell(i);
                    cell2.setCellStyle(style);
                    cell2.setCellValue(rowsName[i]);
                }
    
                //设置数据
              	for (int i = 0; i < total; i++) { 
    				row = sheet.createRow(i + 1);
    				for (int j = 0; j < 10; j++) { 
    					row.createCell(j).setCellValue("test "+j);
    				}
    			}
               
            } catch (Exception e) { 
                e.printStackTrace();
                logger.error("PageTask error", e);
            } finally { 
                if (countDownLatch != null) { 
                    countDownLatch.countDown();
                }
            }
        }
    
    }

//单元格样式
    	private static CellStyle createCellStyle(SXSSFWorkbook workbook, short fontsize) { 
    		// TODO Auto-generated method stub
    		CellStyle style = workbook.createCellStyle();
    		style.setAlignment(CellStyle.ALIGN_CENTER);// 水平居中
    		style.setVerticalAlignment(CellStyle.VERTICAL_CENTER);// 垂直居中
    		// 创建字体
    		Font font = workbook.createFont();
    		font.setBoldweight(Font.BOLDWEIGHT_BOLD);
    		font.setFontHeightInPoints(fontsize);
    		// 加载字体
    		style.setFont(font);
    		return style;
    	}

## 备注 ##

不好意思，看代码的人估计要哭了，有些代码确实对不上，你拷贝以上代码也可能运行不了，上面只是个代码框架，你只要改改数据，传入你要写入的数据，就差不多能用了。因为我再测试完之后，直接原地修改，用于项目了，所以设计相关信息，只能删除了。。。。抱歉。

总之，说下思路，数据大，你就用多线程写入多个sheet。数据小，单线程搞定。  
而且excel这个类针对数据大用的，如果小也没啥优势。至于线程池怎么用，基本功，不适合本篇内容。

关于`大批量` `多用户` `同时导入`情况，可以[参考这里][Link 2]

[Link 1]: https://segmentfault.com/a/1190000018437496
[Link 2]: https://mp.weixin.qq.com/s/5EsZVJXD4iR5-Q_QloybUg