Bug 41972 - Problem with Excel when reading large size file.
Summary: Problem with Excel when reading large size file.
Status: RESOLVED WORKSFORME
Alias: None
Product: POI
Classification: Unclassified
Component: HSSF (show other bugs)
Version: 2.5-FINAL
Hardware: PC Linux
: P2 critical (vote)
Target Milestone: ---
Assignee: POI Developers List
URL:
Keywords:
Depends on:
Blocks:
 
Reported: 2007-03-28 09:24 UTC by Vedamoorthi
Modified: 2008-05-27 06:56 UTC (History)
2 users (show)



Attachments
TestExcel (3.92 KB, application/octet-stream)
2007-03-28 09:32 UTC, Vedamoorthi
Details

Note You need to log in before you can comment on or make changes to this bug.
Description Vedamoorthi 2007-03-28 09:24:30 UTC
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);
	}
}
-----------------------------------------------------------------------------
Comment 1 Vedamoorthi 2007-03-28 09:32:09 UTC
Created attachment 19833 [details]
TestExcel
Comment 2 Nick Burch 2008-05-27 06:56:34 UTC
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.