我也用的是POI生成Excel文件,
由于表格比较复杂,我采用了Excel模板,因此只要把数据填到表格内再适当修改某些单格样式就可以了, 但由于表格太复杂, 每次超过1000条记录就会内存溢出.我觉得如果表格不是很复杂,可以生成CSV文件,这样速度快很多,也不会出现内存溢出的情况.
由于表格比较复杂,我采用了Excel模板,因此只要把数据填到表格内再适当修改某些单格样式就可以了, 但由于表格太复杂, 每次超过1000条记录就会内存溢出.我觉得如果表格不是很复杂,可以生成CSV文件,这样速度快很多,也不会出现内存溢出的情况.
解决方案 »
- Servlet
- Jsp map 求高手解惑
- 怎么解决上传文件的时候,用户中途取消的情况
- 求助osworkflow mysql持久化
- java.lang.NumberFormatException: null
- Struts2中怎么用迭代或其他方法得到表中某一列的属性?
- 求救!!!关于调用雅虎查询API报错 附:源码和错误返回信息。
- 我的web程序最高同时在线2000人以上,用tomcat的做服务器的话如何设置呢?服务器的内存应该在多大比较合适?
- jdni的疑惑?
- tomcat启动maven项目后报spring错误
- 一个Hibernate+weblogic的问题
- eclipse 无法启动Tomcat5.0
import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;
import javax.servlet.http.HttpSession;
import javax.servlet.jsp.JspWriter;import org.apache.struts.action.ActionForm;
import org.apache.struts.action.ActionForward;
import org.apache.struts.action.ActionMapping;
import org.apache.struts.actions.DispatchAction;
import org.apache.log4j.*;
import java.sql.*;
import java.util.HashMap;import org.hibernate.*;import org.apache.poi.hssf.record.FontRecord;
import org.apache.poi.hssf.usermodel.*;
import java.io.*;import com.scitel.common.hibernate.*;/**
Author peiDW 保留署名权
email : pdw2009 At yahoo.com.cn
blog : http://pdw2009.54bk.com
*/
public class ReportAction extends DispatchAction{
Logger logger=Logger.getLogger(ReportAction.class);
public void excelReport(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest httpServletRequest,
HttpServletResponse httpServletResponse) {
httpServletResponse.setContentType("application/vnd.ms-excel");
ServletRequest request=(ServletRequest)httpServletRequest;
String str_sql=request.getParameter("str_query");
String str_title=request.getParameter("str_title");
String str_head=request.getParameter("strGridTitle");
HttpSession session=httpServletRequest.getSession();
HashMap hm=(HashMap)session.getAttribute("gzmas_popedom");
try {
OutputStream os=httpServletResponse.getOutputStream();
if(hm==null||!hm.containsKey("Report_Manager")) {
String a="你没有权限,所以不能获取数据!";
byte bytes[]=new byte[a.length()];
bytes=a.getBytes();
os.write(bytes);
}else {
os.write(getWorkbook(str_sql,str_title,str_head).getBytes());
}
os.flush();
os.close();
} catch (IOException e) {
e.printStackTrace();
}
//return null;
}
public void csvReport(ActionMapping actionMapping,
ActionForm actionForm, HttpServletRequest httpServletRequest,
HttpServletResponse httpServletResponse) {
httpServletResponse.setContentType("application/vnd.ms-excel;charset=gb2312");
ServletRequest request=(ServletRequest)httpServletRequest;
String str_sql= (String) request.getParameter("strquery");
String str_title=request.getParameter("strtitle");
String str_head=request.getParameter("strGridTitle");
HttpSession session=httpServletRequest.getSession();
HashMap hm=(HashMap)session.getAttribute("gzmas_popedom");
try {
PrintWriter out=httpServletResponse.getWriter();
if(hm==null||!hm.containsKey("Report_Manager")) {
out.println("你没有权限,所以不能获取数据!");
}else {
out.println(getCsv(str_sql,str_title,str_head));
}
out.flush();
} catch (IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
}
/**
* 以CSV方的方式生成Excel表格
* @param str_sql
* @param title
* @return
*/
public String getCsv(String str_sql,String title,String str_head) {
StringBuffer sb=new StringBuffer();
Connection conn=null;
Session session=null;
Statement stmt=null;
ResultSet rec=null;
try {
session=CommonUtils.currentSession();
conn=session.connection();
stmt=conn.createStatement();
rec=stmt.executeQuery(str_sql);
ResultSetMetaData rmd=rec.getMetaData();
sb.append(" "+title+" ").append("\n");
String[] ary = str_head.split(",");
for(int k=0;k<ary.length;k++) {
sb.append(ary[k]).append((char)9);
}
while(rec.next()) {
sb.append("\n");
for(int i=1;i<=rmd.getColumnCount();i++) {
String temp=rec.getString(i);
if(temp==null) {
temp="";
}
String str_value=temp.replaceAll("[\\\n\\\t]"," ");
sb.append(str_value).append((char)9);
}
}
}catch(Exception e) {
sb.append("生成数据出错!");
e.printStackTrace();
}finally {
try {
if(rec!=null) {
rec.close();
}
if(stmt!=null) {
stmt.close();
}
CommonUtils.closeSession();
}catch(Exception e) {
//logg.er.error(e);
e.printStackTrace();
}
}
return sb.toString();
}
/**
* 使用POI生成Excel
* @param str_sql
* @param title
* @return
*/
public HSSFWorkbook getWorkbook(String str_sql,String title,String str_head) {
HSSFWorkbook result=new HSSFWorkbook();
Connection conn=null;
Session session=null;
Statement stmt=null;
ResultSet rec=null;
try {
session=CommonUtils.currentSession();
conn=session.connection();
stmt=conn.createStatement();
rec=stmt.executeQuery(str_sql);
HSSFSheet sheet=result.createSheet("sheet1");
ResultSetMetaData rmd=rec.getMetaData();
//excel标题 HSSFRow row0=sheet.createRow(0);
HSSFCell cell0=row0.createCell((short)2);
cell0.setEncoding(HSSFCell.ENCODING_UTF_16);
cell0.setCellValue(title);
HSSFRow row1=sheet.createRow(1);
String ary[]=str_head.split(",");
for(int k=0;k<ary.length;k++) {
HSSFCell cell=row1.createCell((short)k);
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(ary[k]);
}
int i=2;
while(rec.next()) {
HSSFRow row=sheet.createRow(i);
for(int k=1;k<=rmd.getColumnCount();k++) {
HSSFCell cell=row.createCell((short)(k-1));
cell.setEncoding(HSSFCell.ENCODING_UTF_16);
cell.setCellValue(rec.getString(k));
}
i++;
}
System.out.println(result.getBytes().length);
return result;
}catch(Exception e) {
//logger.error(e);
e.printStackTrace();
}finally {
try {
if(rec!=null) {
rec.close();
}
if(stmt!=null) {
stmt.close();
}
CommonUtils.closeSession();
}catch(Exception e) {
//logger.error(e);
e.printStackTrace();
}
}
return null;
}
public static void main(String args[]) {
String aa="afsadfasf"+"\n"+"dfasdf";
char c=(char)9;
String vv=aa+c+"sgfdgdfsgdsfg";
String result=vv.replaceAll("[\\\n\\\t]","-");
System.out.println("result->"+result);
}}===========================================
如果你数据量大的话请用CSV方式生成Excel,这是我前几天写的。现贴出来,只想赚你那100分专家分
其实只需要 对齐方式,边框黑色,单元格合并,这3种不知道csv格式可不可以实现
我用的是jxl
另外一个JExcel API 好象比POI强些。