二、sxssfpackage 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文件中，创建新的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(); } } }
三、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文件解析的详细内容。