XSSFWorkbook,SXSSFWorkbook以及EasyExcel读取Excel文件的比较

 

 同时读取30w数据，比较运行时间及CPU、内存占比

1.EasyExcel

package com.apesource;

import java.util.ArrayList;
import java.util.List;

import com.alibaba.excel.EasyExcel;
import com.apesource.entity.Order;


public class Demo01 {
	public static void main(String[] args) {
		long begin = System.currentTimeMillis();
        // 写入100w
        EasyExcel.write("E:\\test\\demo05.xlsx", Order.class)
                 .sheet("订单列表")
                 .doWrite(data());
        
        long end = System.currentTimeMillis();
        
        System.out.println("EasyExcel读取30w文件共耗时" +(end - begin) +"毫秒");
    }
    
    // 创建100w条订单数据
    private static List<Order> data() {
        List<Order> list = new ArrayList<Order>();
        for (int i = 0; i < 300000; i++) {
            list.add(new Order());
        }
        return list;
    }
}

 

2.XSSFWorkbook

package com.apesource;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Demo10 {
	public static void main(String[] args) {
		long begin = System.currentTimeMillis();
		// 使用
		try (Workbook workbook = new XSSFWorkbook();
				FileOutputStream out = new FileOutputStream("E:\\test\\demo03.xlsx")) {
			
					
			Sheet sheet = workbook.createSheet();
			
			Row headrow = sheet.createRow(0);
			Cell headcell0 = headrow.createCell(0);
			headcell0.setCellValue("序号");
			Cell headcell1 = headrow.createCell(1);
			headcell1.setCellValue("姓名");
			Cell headcell2 = headrow.createCell(2);
			headcell2.setCellValue("日期");
			Cell headcell3 = headrow.createCell(3);
			headcell3.setCellValue("红包金额");
			
			// 获取格式编码值
			DataFormat dataFormat = workbook.createDataFormat();
			short dataFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
			short moneyFormatCode = dataFormat.getFormat("￥#,###");
			
			// 创建日期格式对象
			CellStyle dataCellStyle = workbook.createCellStyle();
			dataCellStyle.setDataFormat(dataFormatCode);
			
			
			// 创建货币格式对象
			CellStyle moneyCellStyle =  workbook.createCellStyle();
			moneyCellStyle.setDataFormat(moneyFormatCode);
			
			
			for(int i = 0 ;i < 300000;i++) {
				String name  = "A" + i;
				
				// 创建行
				Row row = sheet.createRow(i+1);
				
				// 创建单元格
				Cell cell0 = row .createCell(0); // 序号
				cell0.setCellValue(String.valueOf(i+1));
				
				Cell cell1 = row.createCell(1); // 姓名
				cell1.setCellValue(name);
				
				Cell cell2 = row.createCell(2);// 日期
				cell2.setCellStyle(dataCellStyle);// 日期格式对象
				cell2.setCellValue(new Date());
				
				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(moneyCellStyle);
				cell3.setCellValue((int)(Math.random() *1000000));
		
			}
		
			// 写入文件
			workbook.write(out);
			long end = System.currentTimeMillis();
	        
	        System.out.println("XSSFWorkbook读取30w文件共耗时" +(end - begin) +"毫秒");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

 

3.SXSSFWorkbook

package com.apesource;

import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Arrays;
import java.util.Date;
import java.util.List;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellStyle;
import org.apache.poi.ss.usermodel.DataFormat;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.streaming.SXSSFWorkbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class Demo10 {
	public static void main(String[] args) {
		long begin = System.currentTimeMillis();
		// 使用
		try (Workbook workbook = new SXSSFWorkbook(100);
				FileOutputStream out = new FileOutputStream("E:\\test\\demo03.xlsx")) {
			
					
			Sheet sheet = workbook.createSheet();
			
			Row headrow = sheet.createRow(0);
			Cell headcell0 = headrow.createCell(0);
			headcell0.setCellValue("序号");
			Cell headcell1 = headrow.createCell(1);
			headcell1.setCellValue("姓名");
			Cell headcell2 = headrow.createCell(2);
			headcell2.setCellValue("日期");
			Cell headcell3 = headrow.createCell(3);
			headcell3.setCellValue("红包金额");
			
			// 获取格式编码值
			DataFormat dataFormat = workbook.createDataFormat();
			short dataFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
			short moneyFormatCode = dataFormat.getFormat("￥#,###");
			
			// 创建日期格式对象
			CellStyle dataCellStyle = workbook.createCellStyle();
			dataCellStyle.setDataFormat(dataFormatCode);
			
			
			// 创建货币格式对象
			CellStyle moneyCellStyle =  workbook.createCellStyle();
			moneyCellStyle.setDataFormat(moneyFormatCode);
			
			
			for(int i = 0 ;i < 300000;i++) {
				String name  = "A" + i;
				
				// 创建行
				Row row = sheet.createRow(i+1);
				
				// 创建单元格
				Cell cell0 = row .createCell(0); // 序号
				cell0.setCellValue(String.valueOf(i+1));
				
				Cell cell1 = row.createCell(1); // 姓名
				cell1.setCellValue(name);
				
				Cell cell2 = row.createCell(2);// 日期
				cell2.setCellStyle(dataCellStyle);// 日期格式对象
				cell2.setCellValue(new Date());
				
				Cell cell3 = row.createCell(3);
				cell3.setCellStyle(moneyCellStyle);
				cell3.setCellValue((int)(Math.random() *1000000));
		
			}
		
			// 写入文件
			workbook.write(out);
			long end = System.currentTimeMillis();
	        
	        System.out.println("SXSSFWorkbook读取30w文件共耗时" +(end - begin) +"毫秒");
		} catch (IOException e) {
			e.printStackTrace();
		}
	}

}

 综上数据可见：同是30w数据SXSSFWorkbook运行速度最快，CPU占比最小；EasyExcel次之；XSSFWorkbook运行速度最慢，CPU占比最大；