1.常规读取方法
通常在读取excel文件时(.xlsx),是使用如下代码进行加载的:
FileInputStream file= new FileInputStream("d:/test.xlsx"); XSSFWorkbook wk = new XSSFWorkbook(file);
然后再获取对应的Sheet、Row和Cell,然后获取excel中的内容,但是这种方式POI会把文件的所有内容都加载到内存中,读取大的excel文件时很容易占用大量内存(一旦上w行数据就会造成内存吃紧)。
2.数据量较大的处理方法
使用Excel Streaming Reader,这个第三方工具会把一部分的行(可以设置)缓存到内存中,在迭代时不断加载行到内存中,而不是一次性的加载所有记录到内存,这样就可以不断的读取excel内容并且不影响内存的使用。
这个工具也有一定的限制:只能用于读取excel的内容,写入操作不可用;可以使用getSheetAt()方法获取到对应的Sheet,因为当前只是加载了有限的row在内存中,因此不能随机访问row,即不能使用getRow(int rowNum)方法;由于行数据已经加载到了内存,因此可以随机的访问Cell数据,即可以使用getCell(int cellnum)方法。使用这个工具,建议使用迭代器来进行迭代。原理参考:https://www.cnblogs.com/cksvsaaa/p/7280261.html
工具源码地址参见:https://github.com/monitorjbl/excel-streaming-reader。
在pom.xml文件中引入需要的jar包:(亲测:单表单sheet跑60w数据都是没有问题的)
<dependency> <groupId>com.monitorjbl</groupId> <artifactId>xlsx-streamer</artifactId> <version>1.2.0</version> </dependency>
例子:
public static void testXlsx(String sheetName) throws Exception { String path = "C:\\FileRecv\\"; File file = new File(path + "test1.xlsx"); InputStream stream = new FileInputStream(file); Workbook xssfWorkbook = new XSSFWorkbook(stream); //表1:数据量较少 Sheet sheet = xssfWorkbook.getSheetAt(0); File file2 = new File(path + "test2.xlsx"); System.out.println("file2------" + file2.exists()); //获取输入流 InputStream stream2 = new FileInputStream(file2); //表2:数据量较大 Workbook xssfWorkbook2 = StreamingReader.builder() .rowCacheSize(100) //缓存到内存中的行数,默认是10 .bufferSize(4096) //读取资源时,缓存到内存的字节大小,默认是1024 .open(stream2); Sheet sheet2 = xssfWorkbook2.getSheet(sheetName); //根据SheetName获取Sheet List<Row> listRow = new ArrayList<Row>(); for(Row row2 : sheet2){ listRow.add(row2); } sheet = xssfWorkbook.getSheetAt(0); //根据SheetNum获取Sheet int row2Size = listRow.size(); for(int i=1; i<=372; i++){ Row row = sheet.getRow(i); BigDecimal big15 = new BigDecimal(row.getCell(15).getNumericCellValue()); if(big15.compareTo(new BigDecimal("0.00")) == 0){ String province = row.getCell(0).getStringCellValue(); String year = BigDecimal.valueOf(row.getCell(1).getNumericCellValue()).stripTrailingZeros().toPlainString(); String flag1 = year + province; //2009北京 BigDecimal sumAmt = new BigDecimal("0.00"); for(int j = 0; j < row2Size; j++){ String amt = listRow.get(j).getCell(3).getStringCellValue(); String year2 = listRow.get(j).getCell(5).getStringCellValue(); String province2 = listRow.get(j).getCell(6).getStringCellValue(); String flag2 = year2.substring(0, 4) + province2; if(flag2.indexOf(flag1) > -1){ sumAmt = sumAmt.add(new BigDecimal(amt)); } } if(sumAmt.compareTo(new BigDecimal("0.00")) > 0){ System.out.println(province + year + "---" + sumAmt); } row.getCell(15).setCellValue(Double.parseDouble(sumAmt.toPlainString())); //在Excel中写入数据 } } OutputStream output = new FileOutputStream(file); //在输出流中写入数据: 在写入数据时不要打开该文件,不然会报错另外进程打开了该文件 xssfWorkbook.write(output); output.flush(); output.close(); if(stream != null){ stream.close(); } if(stream2 != null){ stream2.close(); } }
总结:对于大量数据处理时,一般可以考虑使用分多个Excel或者一个Excel分多个Sheet,和数据库的分库分表原理一样。也可以考虑使用异步方式处理大数据。