I have performance issue while reading a large size excel file using Event User Model. I have simple java program which will read a excel file and print the values Its working fine with smaller size file(26 column and 100 rows), But I have excel file with 26 column and 32,000 rows when try to read this excel sheets, Its taking more than 5 hrs to read the entire sheet (16 Mb approx) Is there any way to improve the performance, Because in real scenario I have some excel files which is more than 40 MB. Kindly please help he resolve this issue. This is the code I am using ----------------------------------------------------------------------------- public class CopyExcelExample { String infile; SSTRecord sstrec; public CopyExcelExample() {} public void setInputFile(String infile) { this.infile = infile; } public void run() throws IOException { System.out.println("Loading the input stream..."); FileInputStream fin = new FileInputStream(infile); System.out.println("Creating the POIFSFileSystem object..."); POIFSFileSystem poifs = new POIFSFileSystem(fin); System.out.println("Creating createDocument InputStream"); InputStream din = poifs.createDocumentInputStream("Workbook"); HSSFRequest req = new HSSFRequest(); req.addListenerForAllRecords(new MetronetExcelListener(this)); HSSFEventFactory factory = new HSSFEventFactory(); System.out.println("Start event process"); factory.processEvents(req, din); fin.close(); din.close(); LOG.info("done."); } public void blankRecordHandler(Record record, int sheetnum) { BlankRecord blankrec = (BlankRecord) record; System.out.println("Ignore this row." + blankrec.getRow()); } public void numberRecordHandler(Record record, int sheetnum) { NumberRecord numrec = (NumberRecord) record; int currentRowIndex = numrec.getRow(); System.out.println("numrec " + numrec.getColumn() + "row " + currentRowIndex + " " + getFormattedValue((int) numrec.getColumn(), numrec.getValue())); } public void lableRecordHandler(Record record, int sheetNum) { LabelSSTRecord lrec = (LabelSSTRecord) record; int currentRowIndex = lrec.getRow(); System.out.println("label " + lrec.getColumn() + "row "+ currentRowIndex + " " + sstrec.getString(lrec.getSSTIndex())); } public void recordHandler(Record record) { int sheetnum = -1; switch (record.getSid()) { case BOFRecord.sid: BOFRecord bof = (BOFRecord) record; if (BOFRecord.TYPE_WORKBOOK == bof.getType()) { LOG.info("A New Work book found"); sheetnum = 0; } else if (BOFRecord.TYPE_WORKSHEET == bof.getType()) { LOG.info("A new work sheet found"); sheetnum++; } break; case NumberRecord.sid: numberRecordHandler(record, sheetnum); break; case SSTRecord.sid: sstrec = (SSTRecord) record; System.out.println(record); break; case LabelSSTRecord.sid: lableRecordHandler(record, sheetnum); break; case MulBlankRecord.sid: System.out.println(record); break; case BlankRecord.sid: blankRecordHandler(record, sheetnum); break; } } public static void main(String[] args) { if ((args.length < 2) || !args[0].equals("--help")) { try { CopyExcelExample viewer = new CopyExcelExample(); viewer.setInputFile(args[1]); long l1 = System.currentTimeMillis(); Date st = new Date(); viewer.run(); Date end = new Date(); long l2 = System.currentTimeMillis(); System.out.println("\n\n done \n\n"); System.out.println(end); System.out.println(st); System.out.println("L2 - L1 <<<<<>>>>>>>>>>>>>> " + (l2-l1)/1000 +" Sec.."); } catch (Exception e) { e.printStackTrace(); } } else { System.out.println("Usage: java com.buildonline.bulkimport.metronet.CopyExcelExample "+ "file1 file2"); } } } class MetronetExcelListener implements HSSFListener { CopyExcelExample efhssf; public MetronetExcelListener(CopyExcelExample efhssf) { this.efhssf = efhssf; } public void processRecord(Record record) { efhssf.recordHandler(record); } } -----------------------------------------------------------------------------
Created attachment 19833 [details] TestExcel
I suspect you are suffering very poor System.out performance, or your logging framework. I've never had any excel file take more than a few seconds to process with eventusermodel Try running your app with no output, no logging etc, and I suspect it'll run in a few seconds. However, without your excel file, there's no way to know for sure.