您好,登錄后才能下訂單哦!
這篇文章主要介紹“Java怎么實現(xiàn)超大Excel文件解析”,在日常操作中,相信很多人在Java怎么實現(xiàn)超大Excel文件解析問題上存在疑惑,小編查閱了各式資料,整理出簡單好用的操作方法,希望對大家解答”Java怎么實現(xiàn)超大Excel文件解析”的疑惑有所幫助!接下來,請跟著小編一起來學(xué)習(xí)吧!
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文件中,創(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é)束時間 long end = System.currentTimeMillis(); System.out.println("共耗時:" +(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) { //開始時間 long begin = System.currentTimeMillis(); try (//讀取一個已存在的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é)束時間 long end = System.currentTimeMillis(); System.out.println("共耗時:" +(end - begin) + "毫秒"); } catch (IOException e) { e.printStackTrace(); } } }
準(zhǔn)備實體類
public class Order { @ExcelProperty("訂單編號") private String orderId; // 訂單編號 @ExcelProperty("支付金額") @NumberFormat("¥#,###") private Double payment; // 支付金額 @ExcelProperty(value = "創(chuàng)建日期",converter = LocalDateTimeConverter.class) private LocalDateTime creationTime; // 創(chuàng)建時間 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日期時間類)
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條數(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<Order> data() { List<Order> list = new ArrayList<Order>(); for (int i = 0; i < 300000; i++) { list.add(new Order()); } return list; } }
所以easyExcel最快,XSSF最慢且占用cpu最高
到此,關(guān)于“Java怎么實現(xiàn)超大Excel文件解析”的學(xué)習(xí)就結(jié)束了,希望能夠解決大家的疑惑。理論與實踐的搭配能更好的幫助大家學(xué)習(xí),快去試試吧!若想繼續(xù)學(xué)習(xí)更多相關(guān)知識,請繼續(xù)關(guān)注億速云網(wǎng)站,小編會繼續(xù)努力為大家?guī)砀鄬嵱玫奈恼拢?/p>
免責(zé)聲明:本站發(fā)布的內(nèi)容(圖片、視頻和文字)以原創(chuàng)、轉(zhuǎn)載和分享為主,文章觀點不代表本網(wǎng)站立場,如果涉及侵權(quán)請聯(lián)系站長郵箱:is@yisu.com進(jìn)行舉報,并提供相關(guān)證據(jù),一經(jīng)查實,將立刻刪除涉嫌侵權(quán)內(nèi)容。