java处理Excel数据

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,和数据库的分库分表原理一样。也可以考虑使用异步方式处理大数据。

上一篇:day13


下一篇:Python入门们day13——文件修改的两种方式