2 neal niqiu neal_niqiu 于 2014.02.26 15:22 提问

java程序jxl导出excel内存溢出

困扰了我3天的问题
java程序jxl导出excel内存溢出
在网上找了几个扩大tomcat内存的方法,比如
1.在catalina.bat最前面加入set JAVA_OPTS=-Xms384m -Xmx384m
2.在catalina.sh中echo "Using CATALINA_BASE: $CATALINA_BASE"前面加入JAVA_OPTS="-server -Xms800m -Xmx800m -XX:MaxNewSize=512m"
都不好使,下载10000条数据就会报内存溢出,如下:
xception report

message Handler processing failed; nested exception is java.lang.OutOfMemoryError: Java heap space

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

exception

org.springframework.web.util.NestedServletException: Handler processing failed; nested exception is java.lang.OutOfMemoryError: Java heap space
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:972)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

root cause

java.lang.OutOfMemoryError: Java heap space
com.roiland.maven.control.management.JlhdExcelController.jlhdGrmdExcel(JlhdExcelController.java:201)
sun.reflect.NativeMethodAccessorImpl.invoke0(Native Method)
sun.reflect.NativeMethodAccessorImpl.invoke(NativeMethodAccessorImpl.java:39)
sun.reflect.DelegatingMethodAccessorImpl.invoke(DelegatingMethodAccessorImpl.java:25)
java.lang.reflect.Method.invoke(Method.java:597)
org.springframework.web.method.support.InvocableHandlerMethod.invoke(InvocableHandlerMethod.java:213)
org.springframework.web.method.support.InvocableHandlerMethod.invokeForRequest(InvocableHandlerMethod.java:126)
org.springframework.web.servlet.mvc.method.annotation.ServletInvocableHandlerMethod.invokeAndHandle(ServletInvocableHandlerMethod.java:96)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.invokeHandlerMethod(RequestMappingHandlerAdapter.java:617)
org.springframework.web.servlet.mvc.method.annotation.RequestMappingHandlerAdapter.handleInternal(RequestMappingHandlerAdapter.java:578)
org.springframework.web.servlet.mvc.method.AbstractHandlerMethodAdapter.handle(AbstractHandlerMethodAdapter.java:80)
org.springframework.web.servlet.DispatcherServlet.doDispatch(DispatcherServlet.java:923)
org.springframework.web.servlet.DispatcherServlet.doService(DispatcherServlet.java:852)
org.springframework.web.servlet.FrameworkServlet.processRequest(FrameworkServlet.java:882)
org.springframework.web.servlet.FrameworkServlet.doGet(FrameworkServlet.java:778)
javax.servlet.http.HttpServlet.service(HttpServlet.java:621)
javax.servlet.http.HttpServlet.service(HttpServlet.java:728)
org.springframework.web.filter.CharacterEncodingFilter.doFilterInternal(CharacterEncodingFilter.java:88)
org.springframework.web.filter.OncePerRequestFilter.doFilter(OncePerRequestFilter.java:76)

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

我的代码:
@RequestMapping(value = "/jlhdGrmdExcel.do", method = { RequestMethod.GET, RequestMethod.POST })
@RequestMappingAssist(paramType=ParamType.httpType,viewType=ViewType.jspView)
public String jlhdGrmdExcel(HttpServletRequest request, HttpServletResponse response) throws Exception {
response.setCharacterEncoding("utf-8");
response.setContentType("application/vnd.ms-excel");
Date date = new Date(System.currentTimeMillis());
String param2 = new SimpleDateFormat("yyyyMMdd").format(date).toString();
response.addHeader("Content-Disposition","attachment; filename=\"" + param2 + ".xls" + "\"");
// 声明输出流对象
OutputStream os = null;

        // 实例流对象
        os = response.getOutputStream();
        // 创建工作簿和sheet表
        WritableFont wf2 = new WritableFont(WritableFont.TIMES, 12,WritableFont.NO_BOLD, false);
        WritableCellFormat wcfF2 = new WritableCellFormat(wf2);
        // 设置单元格格式
        wcfF2.setAlignment(jxl.format.Alignment.CENTRE);
        wcfF2.setAlignment(jxl.format.Alignment.CENTRE);
        // 声明工作簿
        WritableWorkbook wwb = Workbook.createWorkbook(os);

        //获取jlhdid           
         int jlhdid = Integer.parseInt(request.getParameter("jlhdid"));
        //获取jxsdm
         String jxsdm = request.getParameter("jxsdm");

        JlhdGrmd jlhdgrmd = new JlhdGrmd();

        //放入参数
        jlhdgrmd.setJlhdid(jlhdid);
        jlhdgrmd.setJxsdm(jxsdm);

        request.setCharacterEncoding("UTF-8");
        //查询符合条件的结果集
        List<JlhdGrmd> excelGrmd = new ArrayList<JlhdGrmd>();
        excelGrmd = jlhdGrmdMapper.selectJlhdGrmdByJlhdId(jlhdgrmd);        

        if (excelGrmd != null && excelGrmd.size() > 0) {

                // 设置sheet名称
                WritableSheet sheet = wwb.createSheet("个人名单", 0);
                // 设置列名
                /*sheet.addCell(new Label(0, 0, "序号", wcfF2));
                sheet.addCell(new Label(1, 0, "经销商代码", wcfF2));
                sheet.addCell(new Label(2, 0, "经销商名称", wcfF2));
                sheet.addCell(new Label(3, 0, "用户id", wcfF2));
                sheet.addCell(new Label(4, 0, "个人代码", wcfF2));
                sheet.addCell(new Label(5, 0, "姓名", wcfF2));
                sheet.addCell(new Label(6, 0, "银行卡号", wcfF2));
                sheet.addCell(new Label(7, 0, "身份证", wcfF2));
                sheet.addCell(new Label(8, 0, "职务", wcfF2));
                sheet.addCell(new Label(9, 0, "销售台数", wcfF2));

                sheet.addCell(new Label(10, 0, "总积分", wcfF2));
                sheet.addCell(new Label(11, 0, "总金额", wcfF2));
                sheet.addCell(new Label(12, 0, "总所得税", wcfF2));
                sheet.addCell(new Label(13, 0, "总支付金额", wcfF2));

                sheet.addCell(new Label(14, 0, "是否打款", wcfF2));
                sheet.addCell(new Label(15, 0, "打款失败原因", wcfF2));
                sheet.addCell(new Label(16, 0, "上传次数", wcfF2));
                sheet.addCell(new Label(17, 0, "支付日期", wcfF2));
                sheet.addCell(new Label(18, 0, "添加时间", wcfF2));
                sheet.addCell(new Label(19, 0, "添加人id", wcfF2));*/
                sheet.addCell(new Label(0, 0, "序号", wcfF2));
                sheet.addCell(new Label(1, 0, "经销商代码", wcfF2));
                sheet.addCell(new Label(2, 0, "经销商名称", wcfF2));                   
                sheet.addCell(new Label(3, 0, "个人代码", wcfF2));
                sheet.addCell(new Label(4, 0, "姓名", wcfF2));
                sheet.addCell(new Label(5, 0, "银行卡号", wcfF2));
                sheet.addCell(new Label(6, 0, "身份证", wcfF2));
                sheet.addCell(new Label(7, 0, "职务", wcfF2));
                sheet.addCell(new Label(8, 0, "销售台数", wcfF2));

                sheet.addCell(new Label(9, 0, "总积分", wcfF2));
                sheet.addCell(new Label(10, 0, "总金额", wcfF2));
                sheet.addCell(new Label(11, 0, "总所得税", wcfF2));
                sheet.addCell(new Label(12, 0, "总支付金额", wcfF2));

                sheet.addCell(new Label(13, 0, "是否打款", wcfF2));
                sheet.addCell(new Label(14, 0, "打款失败原因", wcfF2));                   
                sheet.addCell(new Label(15, 0, "支付日期", wcfF2));
                sheet.addCell(new Label(16, 0, "添加时间", wcfF2));



                sheet.addCell(new Label(0, excelGrmd.size() + 2, "合计:", wcfF2));
                //合计销售台数
                int sumXsts = 0;
                //合计总积分
                double sumZjf = 0;
                //合计奖励数量
                double sumZje = 0;
                //合计个人所得税
                double sumGrsds = 0;
                //合计总支付金额
                double sumZzfje = 0;

                // 循环输出经销商列表
                for (int i = 1; i < excelGrmd.size() + 1; i++) {
                    JlhdGrmd emp = excelGrmd.get(i - 1);
                    //宽度设置
                    sheet.setColumnView(0, 10);
                    sheet.setColumnView(1, 20);
                    sheet.setColumnView(2, 50);
                    sheet.setColumnView(3, 20);
                    sheet.setColumnView(4, 20);
                    sheet.setColumnView(5, 20);
                    sheet.setColumnView(6, 20);
                    sheet.setColumnView(7, 20);
                    sheet.setColumnView(8, 20);
                    sheet.setColumnView(9, 20);
                    sheet.setColumnView(10, 20);
                    sheet.setColumnView(11, 20);
                    sheet.setColumnView(12, 20);
                    sheet.setColumnView(13, 20);
                    sheet.setColumnView(14, 20);
                    sheet.setColumnView(15, 20);
                    sheet.setColumnView(16, 30);
                    sheet.setColumnView(17, 30);

                    int xsts = 0;//销售台数
                    double zjf = 0; //总积分
                    double zje = 0;//总金额
                    double grsds = 0;//个人所得税
                    double zzfje = 0;//总支付金额                      

                    xsts = emp.getXsts();                           
                    try {
                        if(emp.getZjf()!=null && emp.getZjf()!=""){
                            zjf = Double.parseDouble(emp.getZjf());
                        }

                        System.err.println("emp.getZje()"+emp.getZje());
                        if(emp.getZje()!=null && emp.getZje()!=""){
                            zje = Double.parseDouble(emp.getZje());
                        }
                        if(emp.getZsds()!=null && emp.getZsds()!=""){
                            grsds = Double.parseDouble(emp.getZsds());
                        }                           
                        if(emp.getZzfje()!=null && emp.getZzfje()!=""){
                            zzfje = Double.parseDouble(emp.getZzfje());
                        }
                    } catch (Exception e) {
                    }

                    sheet.addCell(new Label(0, i, i + "", wcfF2));                      

                    sheet.addCell(new Label(1, i, emp.getJxsdm(), wcfF2));
                    sheet.addCell(new Label(2, i, emp.getJxsmc(), wcfF2));
                    sheet.addCell(new Label(3, i, emp.getGrdm(), wcfF2));
                    sheet.addCell(new Label(4, i, emp.getGrmc(), wcfF2));
                    sheet.addCell(new Label(5, i, emp.getYhkh(), wcfF2));
                    sheet.addCell(new Label(6, i, emp.getSfz(), wcfF2));
                    sheet.addCell(new Label(7, i, emp.getZw(), wcfF2));

                    sheet.addCell(new Number(8, i,xsts, wcfF2));
                    sheet.addCell(new Number(9, i, zjf, wcfF2));
                    sheet.addCell(new Number(10, i, zje, wcfF2));
                    sheet.addCell(new Number(11, i, grsds, wcfF2));                     
                    sheet.addCell(new Number(12, i,zzfje, wcfF2));  

                    sheet.addCell(new Label(13, i, emp.getIsdk(), wcfF2));
                    sheet.addCell(new Label(14, i, emp.getDksbyy(), wcfF2));
                    sheet.addCell(new Label(15, i, emp.getZfrq(), wcfF2));
                    sheet.addCell(new Label(16, i, emp.getAddtime(), wcfF2));


                    sumXsts += xsts;//合计销售台数
                    sumZje += zje;//合计总金额
                    sumZjf += zjf; //合计总积分
                    sumGrsds += grsds;//合计总个人所得税                        
                    sumZzfje += zzfje;  //合计总支付金额         

                    }                   
                sheet.addCell(new Number(8, excelGrmd.size() + 2,sumXsts, wcfF2));
                sheet.addCell(new Number(9, excelGrmd.size() + 2,sumZjf, wcfF2));
                sheet.addCell(new Number(10, excelGrmd.size() + 2,sumZje, wcfF2));
                sheet.addCell(new Number(11, excelGrmd.size() + 2,sumGrsds, wcfF2));                                             
                sheet.addCell(new Number(12, excelGrmd.size() + 2,sumZzfje, wcfF2));
            }

        wwb.write();
        wwb.close();
        os.close();
        //response.flushBuffer();

    return null;
}

哪位大侠能帮小弟解决此问题,万分感谢!

2个回答

jerryfazhi
jerryfazhi   2014.02.27 15:22

jxl是那个版本?我记得有个版本jxl处理excel,jar包里是有对excel大小的限制为200条的。如果你的大于200条就报错的话,试着改下 jxl 的源码,重新编译下。

wqqqianqian
wqqqianqian   2017.06.27 17:14

用pageoffice就不用担心内存溢出的问题了,jxl,POI本身就消耗内存,再把整个文档都加载到内存,加上其他开销,比实际Word、Excel文档还大,遇到打开较大的Word、Excel文档时,JVM很容易内存溢出。二pageoffice是在客户端运行的。所以用PageOffice提取Word、Excel文档中的内容,40MB大小的文档 也没问题,100M的也没压力。

Csdn user default icon
上传中...
上传图片
插入图片
准确详细的回答,更有利于被提问者采纳,从而获得C币。复制、灌水、广告等回答会被删除,是时候展现真正的技术了!