Java如何实现超大Excel文件解析(excel,java,开发技术)

一、XSSF

packagecom.yy.demo01;importjava.io.FileInputStream;importjava.io.FileOutputStream;importjava.io.IOException;importjava.util.Date;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.DataFormat;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;importorg.apache.poi.xssf.usermodel.XSSFWorkbook;publicclassdd{publicstaticvoidmain(String[]args){ //开始时间 longbegin=System.currentTimeMillis(); try(//读取一个已存在的Excel文件 Workbookworkbook=newXSSFWorkbook(newFileInputStream("D:\\1\\demo-data.xlsx")); FileOutputStreamout=newFileOutputStream("D:\\1\\100w.xlsx")){ //在“已存在”的Excel文件中，创建新的sheet Sheetsheet=workbook.createSheet(); //获取格式编码值 DataFormatdataFormat=workbook.createDataFormat(); ShortdateFormatCode=dataFormat.getFormat("yyyy年MM月dd日HH:mm:ss"); ShortmoneyFormatCode=dataFormat.getFormat("￥#，###"); //创建日期格式对象 CellStyledateCellStyle=workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormatCode);//设置格式编码 //创建货币格式对象 CellStylemoneyCellStyle=workbook.createCellStyle(); moneyCellStyle.setDataFormat(moneyFormatCode);//设置格式编码值 for(inti=0;i<300000;i++){ Stringname="A"+i; //创建行 Rowrow=sheet.createRow(i+1); //创建单元格 Cellcell0=row.createCell(0);//序号 cell0.setCellValue(String.valueOf(i+1)); Cellcell1=row.createCell(1);//姓名 cell1.setCellValue(name); Cellcell2=row.createCell(2);//日期 cell2.setCellStyle(dateCellStyle);//货币金额格式对象 cell2.setCellValue(newDate()); Cellcell3=row.createCell(3);//红包金额 cell3.setCellStyle(moneyCellStyle);//货币金额格式对象 cell3.setCellValue((int)(Math.random()*10000)); } //写入文件 workbook.write(out); //结束时间 longend=System.currentTimeMillis(); System.out.println("共耗时："+(end-begin)+"毫秒"); }catch(IOExceptione){ e.printStackTrace(); }}}

二、SXSSF

packagecom.yy.demo01;importjava.io.FileOutputStream;importjava.io.IOException;importjava.util.Date;importorg.apache.poi.ss.usermodel.Cell;importorg.apache.poi.ss.usermodel.CellStyle;importorg.apache.poi.ss.usermodel.DataFormat;importorg.apache.poi.ss.usermodel.Row;importorg.apache.poi.ss.usermodel.Sheet;importorg.apache.poi.ss.usermodel.Workbook;importorg.apache.poi.xssf.streaming.SXSSFWorkbook;publicclassDemo10{ publicstaticvoidmain(String[]args){ //开始时间 longbegin=System.currentTimeMillis(); try(//读取一个已存在的Excel文件 Workbookworkbook=newSXSSFWorkbook(100); FileOutputStreamout=newFileOutputStream("D:\\1\\100w.xlsx")){ //在“已存在”的Excel文件中，创建新的sheet Sheetsheet=workbook.createSheet(); //获取格式编码值 DataFormatdataFormat=workbook.createDataFormat(); ShortdateFormatCode=dataFormat.getFormat("yyyy年MM月dd日HH:mm:ss"); ShortmoneyFormatCode=dataFormat.getFormat("￥#，###"); //创建日期格式对象 CellStyledateCellStyle=workbook.createCellStyle(); dateCellStyle.setDataFormat(dateFormatCode);//设置格式编码 //创建货币格式对象 CellStylemoneyCellStyle=workbook.createCellStyle(); moneyCellStyle.setDataFormat(moneyFormatCode);//设置格式编码值 for(inti=0;i<300000;i++){ Stringname="A"+i; //创建行 Rowrow=sheet.createRow(i+1); //创建单元格 Cellcell0=row.createCell(0);//序号 cell0.setCellValue(String.valueOf(i+1)); Cellcell1=row.createCell(1);//姓名 cell1.setCellValue(name); Cellcell2=row.createCell(2);//日期 cell2.setCellStyle(dateCellStyle);//货币金额格式对象 cell2.setCellValue(newDate()); Cellcell3=row.createCell(3);//红包金额 cell3.setCellStyle(moneyCellStyle);//货币金额格式对象 cell3.setCellValue((int)(Math.random()*10000)); } //写入文件 workbook.write(out); //结束时间 longend=System.currentTimeMillis(); System.out.println("共耗时："+(end-begin)+"毫秒"); }catch(IOExceptione){ e.printStackTrace(); } }}

三、easyExcel

准备实体类

publicclassOrder{@ExcelProperty("订单编号")privateStringorderId;//订单编号@ExcelProperty("支付金额")@NumberFormat("￥#,###")privateDoublepayment;//支付金额@ExcelProperty(value="创建日期",converter=LocalDateTimeConverter.class)privateLocalDateTimecreationTime;//创建时间publicOrder(){this.orderId=LocalDateTime.now().format(DateTimeFormatter.ofPattern("yyyyMMddhhmmss"))+UUID.randomUUID().toString().substring(0,5);this.payment=Math.random()*10000;this.creationTime=LocalDateTime.now();}publicStringgetOrderId(){returnorderId;}publicvoidsetOrderId(StringorderId){this.orderId=orderId;}publicDoublegetPayment(){returnpayment;}publicvoidsetPayment(Doublepayment){this.payment=payment;}publicLocalDateTimegetCreationTime(){returncreationTime;}publicvoidsetCreationTime(LocalDateTimecreationTime){this.creationTime=creationTime;}@OverridepublicStringtoString(){return"Order[orderId="+orderId+",payment="+payment+",creationTime="+creationTime+"]";}}

准备converter转换类(兼容LocateDateTime日期时间类)

publicclassLocalDateTimeConverterimplementsConverter<LocalDateTime>{@OverridepublicClass<LocalDateTime>supportJavaTypeKey(){returnLocalDateTime.class;}@OverridepublicCellDataTypeEnumsupportExcelTypeKey(){returnCellDataTypeEnum.STRING;}@OverridepublicLocalDateTimeconvertToJavaData(CellDatacellData,ExcelContentPropertycontentProperty,GlobalConfigurationglobalConfiguration){returnLocalDateTime.parse(cellData.getStringValue(),DateTimeFormatter.ofPattern("yyyy-MM-ddHH:mm:ss"));}@OverridepublicCellData<String>convertToExcelData(LocalDateTimevalue,ExcelContentPropertycontentProperty,GlobalConfigurationglobalConfiguration){returnnewCellData<>(value.format(DateTimeFormatter.ofPattern("yyyy-MM-ddHH:mm:ss")));}}

写入300000条数据

publicclassDemo{publicstaticvoidmain(String[]args){//写入100wEasyExcel.write("c:\\test\\run\\easy.xlsx",Order.class).sheet("订单列表").doWrite(data());}//创建100w条订单数据privatestaticList<Order>data(){List<Order>list=newArrayList<Order>();for(inti=0;i<300000;i++){list.add(newOrder());}returnlist;}}

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