今天小编给大家分享一下Java如何实现超大Excel文件解析的相关知识点，内容详细，逻辑清晰，相信大部分人都还太了解这方面的知识，所以分享这篇文章给大家参考一下，希望大家阅读完这篇文章后有所收获，下面我们一起来了解一下吧。
一、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文件中，创建新的sheetSheet 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文件中，创建新的sheetSheet 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文件解析”这篇文章的所有内容，感谢各位的阅读！相信大家阅读完这篇文章都有很大的收获，小编每天都会为大家更新不同的知识，如果还想学习更多的知识，请关注编程网行业资讯频道。
