這篇文章主要介紹“Java怎么實(shí)現(xiàn)超大Excel文件解析”，在日常操作中，相信很多人在Java怎么實(shí)現(xiàn)超大Excel文件解析問題上存在疑惑，小編查閱了各式資料，整理出簡單好用的操作方法，希望對大家解答”Java怎么實(shí)現(xiàn)超大Excel文件解析”的疑惑有所幫助！接下來，請跟著小編一起來學(xué)習(xí)吧！
天河ssl適用于網(wǎng)站、小程序/APP、API接口等需要進(jìn)行數(shù)據(jù)傳輸應(yīng)用場景，ssl證書未來市場廣闊！成為創(chuàng)新互聯(lián)建站的ssl證書銷售渠道，可以享受市場價(jià)格4-6折優(yōu)惠！如果有意向歡迎電話聯(lián)系或者加微信：18980820575（備注：SSL證書合作）期待與您的合作！
重慶分公司
重慶分公司
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) {
//開始時(shí)間
long begin = System.currentTimeMillis();
try (//讀取一個(gè)已存在的Excel文件
Workbook workbook = new XSSFWorkbook(new FileInputStream("D:\\1\\demo-data.xlsx"));
FileOutputStream out = new FileOutputStream("D:\\1\\100w.xlsx")) {
//在“已存在”的Excel文件中，創(chuàng)建新的sheet
Sheet sheet = workbook.createSheet();
//獲取格式編碼值
DataFormat dataFormat = workbook.createDataFormat();
Short dateFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
Short moneyFormatCode = dataFormat.getFormat("￥#，###");
//創(chuàng)建日期格式對象
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(dateFormatCode);//設(shè)置格式編碼
//創(chuàng)建貨幣格式對象
CellStyle moneyCellStyle = workbook.createCellStyle();
moneyCellStyle.setDataFormat(moneyFormatCode);//設(shè)置格式編碼值
for(int i = 0; i< 300000;i++) {
String name = "A" + i;
//創(chuàng)建行
Row row = sheet.createRow(i + 1);
//創(chuàng)建單元格
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);
//結(jié)束時(shí)間
long end = System.currentTimeMillis();
System.out.println("共耗時(shí)：" +(end - begin) + "毫秒");
} catch (IOException e) {
e.printStackTrace();
}
}
}
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) {
//開始時(shí)間
long begin = System.currentTimeMillis();
try (//讀取一個(gè)已存在的Excel文件
Workbook workbook = new SXSSFWorkbook(100);
FileOutputStream out = new FileOutputStream("D:\\1\\100w.xlsx")) {
//在“已存在”的Excel文件中，創(chuàng)建新的sheet
Sheet sheet = workbook.createSheet();
//獲取格式編碼值
DataFormat dataFormat = workbook.createDataFormat();
Short dateFormatCode = dataFormat.getFormat("yyyy年MM月dd日 HH:mm:ss");
Short moneyFormatCode = dataFormat.getFormat("￥#，###");
//創(chuàng)建日期格式對象
CellStyle dateCellStyle = workbook.createCellStyle();
dateCellStyle.setDataFormat(dateFormatCode);//設(shè)置格式編碼
//創(chuàng)建貨幣格式對象
CellStyle moneyCellStyle = workbook.createCellStyle();
moneyCellStyle.setDataFormat(moneyFormatCode);//設(shè)置格式編碼值
for(int i = 0; i< 300000;i++) {
String name = "A" + i;
//創(chuàng)建行
Row row = sheet.createRow(i + 1);
//創(chuàng)建單元格
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);
//結(jié)束時(shí)間
long end = System.currentTimeMillis();
System.out.println("共耗時(shí)：" +(end - begin) + "毫秒");
} catch (IOException e) {
e.printStackTrace();
}
}
}準(zhǔn)備實(shí)體類
public class Order {
@ExcelProperty("訂單編號")
private String orderId; // 訂單編號
@ExcelProperty("支付金額")
@NumberFormat("￥#,###")
private Double payment; // 支付金額
@ExcelProperty(value = "創(chuàng)建日期",converter = LocalDateTimeConverter.class)
private LocalDateTime creationTime; // 創(chuàng)建時(shí)間
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 + "]";
}
}準(zhǔn)備converter轉(zhuǎn)換類(兼容LocateDateTime日期時(shí)間類)
public class LocalDateTimeConverter implements Converter{ @Override public Class 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 convertToExcelData(LocalDateTime value, ExcelContentProperty contentProperty, GlobalConfiguration globalConfiguration) { return new CellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-dd HH:mm:ss"))); } }
寫入300000條數(shù)據(jù)
public class Demo {
public static void main(String[] args) {
// 寫入100w
EasyExcel.write("c:\\test\\run\\easy.xlsx", Order.class)
.sheet("訂單列表")
.doWrite(data());
}
// 創(chuàng)建100w條訂單數(shù)據(jù)
private static List data() {
List list = new ArrayList();
for (int i = 0; i < 300000; i++) {
list.add(new Order());
}
return list;
}
} 
所以easyExcel最快，XSSF最慢且占用cpu最高
到此，關(guān)于“Java怎么實(shí)現(xiàn)超大Excel文件解析”的學(xué)習(xí)就結(jié)束了，希望能夠解決大家的疑惑。理論與實(shí)踐的搭配能更好的幫助大家學(xué)習(xí)，快去試試吧！若想繼續(xù)學(xué)習(xí)更多相關(guān)知識，請繼續(xù)關(guān)注創(chuàng)新互聯(lián)網(wǎng)站，小編會繼續(xù)努力為大家?guī)砀鄬?shí)用的文章！
