困扰了我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;
}
哪位大侠能帮小弟解决此问题,万分感谢!