대용량 처리시 POI사용시 얼마나 올라가다가 에러날까?

Excel파일을 DB에 올리는(Insert)작업중이다.

HSSF 경우 xls의 2003버젼.
XSSF의경우 xlsx의 2007버젼 중심.

구조가 크게 달라짐에 따라 이도 완전 다르게 동작하는듯하다.

12월 06, 2011 10:13:15 오후 org.apache.catalina.core.StandardWrapperValve invoke
심각: Servlet.service() for servlet [jsp] in context with path [/stdCost] threw exception [An exception occurred processing JSP page /contents/excel/action/getImportFileInfo.jsp at line 36

33:
34: //XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+new String(lcFile.getBytes("ISO-8859-1"), "UTF-8")));
35:
36: Workbook wb = WorkbookFactory.create(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+new String(lcFile.getBytes("ISO-8859-1"), "UTF-8")));
37: //Workbook wb = new SXSSFWorkbook(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+lcFile));
38:
39: if(wb != null) {

Stacktrace:] with root cause
java.lang.OutOfMemoryError: Java heap space
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3044)
at org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3065)
at org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3259)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1822)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
at org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4808)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
at org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
at org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3454)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1276)
at org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1263)
at org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
at org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
at org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:183)
at org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:175)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:227)
at org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
at org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:174)
at org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:63)
at org.apache.jsp.contents.excel.action.getImportFileInfo_jsp._jspService(getImportFileInfo_jsp.java:220)
at org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
at org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
at org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
at javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
at org.apache.catalina.core.ApplicationFilterChain.internalDoFilter(ApplicationFilterChain.java:304)

결론만 말하자면 새로운 엑셀파일은 같은 내용이라도 전체의 파일 크기는 줄었지만
읽으려면 전체를 메모리에 로드 해 놓고 있어야한다고 한다.
그래서 java heap space 에러가 금방 난다고...(Tomcat기준)

결론은 xls(2003버젼용)으로 저장하여 올리면 용량은 더 늘어나지만 잘 올라간다.
물론 limit은 있겠지? infinity는 아닐것같은데;;;


Ref :
http://gujjy.wordpress.com/2011/06/10/apache-poi-%EC%85%80%EA%B0%92%EC%9D%84-%EB%AC%B8%EC%9E%90%EC%97%B4%EB%A1%9C/

HTTP Status 500 -
--------------------------------------------------------------------------------

type Exception report

message

description The server encountered an internal error () that prevented it from fulfilling this request.

exception
org.apache.jasper.JasperException: An exception occurred processing JSP page /contents/excel/action/getImportFileInfo.jsp at line 36
33:   
34:   //XSSFWorkbook wb = new XSSFWorkbook(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+new String(lcFile.getBytes("ISO-8859-1"), "UTF-8")));
35:   
36:   Workbook wb = WorkbookFactory.create(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+new String(lcFile.getBytes("ISO-8859-1"), "UTF-8"))); 
37:   //Workbook wb = new SXSSFWorkbook(new FileInputStream(UPLOAD_ABS_PATH_WINDOW+lcFile));
38:   
39:   if(wb != null) {

Stacktrace:
org.apache.jasper.servlet.JspServletWrapper.handleJspException(JspServletWrapper.java:567)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:456)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)

root cause
javax.servlet.ServletException: java.lang.OutOfMemoryError: Java heap space
org.apache.jasper.runtime.PageContextImpl.doHandlePageException(PageContextImpl.java:911)
org.apache.jasper.runtime.PageContextImpl.handlePageException(PageContextImpl.java:840)
org.apache.jsp.contents.excel.action.getImportFileInfo_jsp._jspService(getImportFileInfo_jsp.java:369)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
filters.SetCharacterEncodingFilter.doFilter(SetCharacterEncodingFilter.java:122)

root cause
java.lang.OutOfMemoryError: Java heap space
org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3044)
org.apache.xmlbeans.impl.store.Cur$CurLoadContext.attr(Cur.java:3065)
org.apache.xmlbeans.impl.store.Locale$SaxHandler.startElement(Locale.java:3259)
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.reportStartTag(Piccolo.java:1082)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseAttributesNS(PiccoloLexer.java:1822)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseOpenTagNS(PiccoloLexer.java:1521)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseTagNS(PiccoloLexer.java:1362)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXMLNS(PiccoloLexer.java:1293)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.parseXML(PiccoloLexer.java:1261)
org.apache.xmlbeans.impl.piccolo.xml.PiccoloLexer.yylex(PiccoloLexer.java:4808)
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yylex(Piccolo.java:1290)
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.yyparse(Piccolo.java:1400)
org.apache.xmlbeans.impl.piccolo.xml.Piccolo.parse(Piccolo.java:714)
org.apache.xmlbeans.impl.store.Locale$SaxLoader.load(Locale.java:3454)
org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1276)
org.apache.xmlbeans.impl.store.Locale.parseToXmlObject(Locale.java:1263)
org.apache.xmlbeans.impl.schema.SchemaTypeLoaderBase.parse(SchemaTypeLoaderBase.java:345)
org.openxmlformats.schemas.spreadsheetml.x2006.main.WorksheetDocument$Factory.parse(Unknown Source)
org.apache.poi.xssf.usermodel.XSSFSheet.read(XSSFSheet.java:183)
org.apache.poi.xssf.usermodel.XSSFSheet.onDocumentRead(XSSFSheet.java:175)
org.apache.poi.xssf.usermodel.XSSFWorkbook.onDocumentRead(XSSFWorkbook.java:227)
org.apache.poi.POIXMLDocument.load(POIXMLDocument.java:159)
org.apache.poi.xssf.usermodel.XSSFWorkbook.<init>(XSSFWorkbook.java:174)
org.apache.poi.ss.usermodel.WorkbookFactory.create(WorkbookFactory.java:63)
org.apache.jsp.contents.excel.action.getImportFileInfo_jsp._jspService(getImportFileInfo_jsp.java:220)
org.apache.jasper.runtime.HttpJspBase.service(HttpJspBase.java:70)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)
org.apache.jasper.servlet.JspServletWrapper.service(JspServletWrapper.java:433)
org.apache.jasper.servlet.JspServlet.serviceJspFile(JspServlet.java:389)
org.apache.jasper.servlet.JspServlet.service(JspServlet.java:333)
javax.servlet.http.HttpServlet.service(HttpServlet.java:722)

note The full stack trace of the root cause is available in the Apache Tomcat/7.0.21 logs.

--------------------------------------------------------------------------------
Apache Tomcat/7.0.21