Java实现超大Excel文件解析(XSSF,SXSSF,easyExcel)

目录一、XSSF二、SXSSF三、easyExcel一、XSSFpackagecom.yy.demo01;importjava.io.FileInputStream;importjava.io...

目录

一、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.usermoZbHqwcZdel.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 dateFoZbHqwcZrmatCode = 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() {
      javascript  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文件解析的资料请关注我们其它相关文章！