Java實現超大Excel文件解析(XSSF,SXSSF,easyExcel)

Posted on by

一、XSSF

package com.yy.demo01;
 
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
 
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 dd {public static void main(String[] args) {
	//開始時間
	long begin = System.currentTimeMillis();
	try (//讀取一個已存在的Excel文件
			 Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\1\\demo-data.xlsx"));
					FileOutputStream out = new FileOutputStream("D:\\1\\100w.xlsx")) {
			  
			    //在“已存在”的Excel文件中，創建新的sheet
				Sheet sheet = workbook.createSheet();
				
				//獲取格式編碼值
				DataFormat dataFormat = workbook.createDataFormat();
				Short dateFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
				Short moneyFormatCode = dataFormat.getFormat("￥#，###");
				
				//創建日期格式對象
				CellStyle dateCellStyle = workbook.createCellStyle();
				dateCellStyle.setDataFormat(dateFormatCode);//設置格式編碼
				
				//創建貨幣格式對象
				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(dateCellStyle);//貨幣金額格式對象
			    	cell2.setCellValue(new Date());
			    	
			    	Cell cell3 = row.createCell(3);//紅包金額
			    	cell3.setCellStyle(moneyCellStyle);//貨幣金額格式對象
			    	cell3.setCellValue((int)(Math.random()*10000));
			    	
			    }
			    //寫入文件
				workbook.write(out);
				//結束時間
				long end = System.currentTimeMillis();
				
				System.out.println("共耗時：" +(end - begin) + "毫秒");
			
			} catch (IOException e) {
 
				e.printStackTrace();
			}
}
 
}

二、SXSSF

package com.yy.demo01;
 
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Date;
 
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;
 
public class Demo10 {
	public static void main(String[] args) {
		//開始時間
		long begin = System.currentTimeMillis();
		try (//讀取一個已存在的Excel文件
				 Workbook workbook = new SXSSFWorkbook(100);
						FileOutputStream out = new FileOutputStream("D:\\1\\100w.xlsx")) {
				  
				    //在“已存在”的Excel文件中，創建新的sheet
					Sheet sheet = workbook.createSheet();
					
					//獲取格式編碼值
					DataFormat dataFormat = workbook.createDataFormat();
					Short dateFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
					Short moneyFormatCode = dataFormat.getFormat("￥#，###");
					
					//創建日期格式對象
					CellStyle dateCellStyle = workbook.createCellStyle();
					dateCellStyle.setDataFormat(dateFormatCode);//設置格式編碼
					
					//創建貨幣格式對象
					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(dateCellStyle);//貨幣金額格式對象
				    	cell2.setCellValue(new Date());
				    	
				    	Cell cell3 = row.createCell(3);//紅包金額
				    	cell3.setCellStyle(moneyCellStyle);//貨幣金額格式對象
				    	cell3.setCellValue((int)(Math.random()*10000));
				    	
				    }
				    //寫入文件
					workbook.write(out);
					//結束時間
					long end = System.currentTimeMillis();
					
					System.out.println("共耗時：" +(end - begin) + "毫秒");
				
				} catch (IOException e) {
 
					e.printStackTrace();
				}
	}
 
}

三、easyExcel

準備實體類

public class Order {
    @ExcelProperty("訂單編號")
    private String orderId; // 訂單編號
    
    @ExcelProperty("支付金額")
    @NumberFormat("￥#,###")
    private Double payment; // 支付金額
    
    @ExcelProperty(value = "創建日期",converter = LocalDateTimeConverter.class)
    private LocalDateTime creationTime; // 創建時間
 
    public Order() {
        this.orderId = LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddhhmmss"))
                + UUID.randomUUID().toString().substring(0, 5);
        this.payment = Math.random() * 10000;
        this.creationTime = LocalDateTime.now();
    }
 
    public String getOrderId() {
        return orderId;
    }
 
    public void setOrderId(String orderId) {
        this.orderId = orderId;
    }
 
    public Double getPayment() {
        return payment;
    }
 
    public void setPayment(Double payment) {
        this.payment = payment;
    }
 
    public LocalDateTime getCreationTime() {
        return creationTime;
    }
 
    public void setCreationTime(LocalDateTime creationTime) {
        this.creationTime = creationTime;
    }
 
 
 
    @Override
    public String toString() {
        return "Order [orderId=" + orderId + ", payment=" + payment + ", creationTime=" + creationTime + "]";
    }
}

準備converter轉換類(兼容LocateDateTime日期時間類)

public class LocalDateTimeConverter implements Converter<LocalDateTime> {
 
    @Override
    public Class<LocalDateTime> supportJavaTypeKey() {
        return LocalDateTime.class;
    }
 
    @Override
    public CellDataTypeEnum supportExcelTypeKey() {
        return CellDataTypeEnum.STRING;
    }
 
    @Override
    public LocalDateTime convertToJavaData(CellData cellData, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) {
        return LocalDateTime.parse(cellData.getStringValue(), DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"));
    }
 
    @Override
    public CellData<String> convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty,
            GlobalConfiguration globalConfiguration) {
        return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss")));
    }
 
}

寫入300000條數據

public class Demo {
    public static void main(String[] args) {
        // 寫入100w
        EasyExcel.write("c:\\test\\run\\easy.xlsx", Order.class)
                 .sheet("訂單列表")
                 .doWrite(data());
    }
    
    // 創建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;
    }
}

所以easyExcel最快，XSSF最慢且占用cpu最高

以上就是Java實現超大Excel文件解析(XSSF,SXSSF,easyExcel)的詳細內容，更多關於Java Excel文件解析的資料請關註WalkonNet其它相關文章！

推薦閱讀：