用jxl导出资料创建多个sheet(克服大数据量的导出)

用过jxl的一看就明白了

<%@page import="jxl.write.WritableFont,jxl.write.WritableCellFormat,java.sql.ResultSetMetaData,java.io.*,jxl.write.Label,java.net.*,jxl.write.WritableCell,jxl.write.WritableWorkbook,
	java.util.*,com.running.crm.model.*,jxl.Workbook,jxl.write.WritableSheet"
	contentType="application/x-msdownload" pageEncoding="utf-8"%>
	<%
	OutputStream os = response.getOutputStream();
	WritableWorkbook wwb = Workbook.createWorkbook(os);
	Label label;
	response.setHeader("Content-disposition", "attachment; filename="+ URLEncoder.encode("客户资料", "utf-8") + ".xls");
	List customers=(List<Customer>)request.getAttribute("billResultSet");
	//String deptName=(String)request.getAttribute("deptName");
	int length=customers.size();//查询结果记录数
	int sheetSize=20000;
	int sheetNum=1;
	if(length%sheetSize>0){
	   sheetNum=length/sheetSize+1;
	}else{
	   sheetNum=length/sheetSize;
	}
	for(int kk=0;kk<sheetNum;kk++){
        WritableSheet ws=wwb.createSheet(("查询结果"+kk),kk);	
          //设定第一行的行高
         ws.setRowView(0,320);
         //将第一列的宽度设为20 
         
         ws.setColumnView(0,15);
         ws.setColumnView(1,15);
         ws.setColumnView(2,20);
         ws.setColumnView(3,20);
         ws.setColumnView(4,20);
         ws.setColumnView(5,20);
         ws.setColumnView(6,20);
         ws.setColumnView(7,20);
        
             
		String ss[] = {"姓名","性别","手机号码","证件类型","证件号码","公司","家庭住址","爱好" };
	  for (int i = 0; i < 8; i++) {
		 WritableFont arial12pt = new WritableFont(WritableFont.ARIAL,11, WritableFont.BOLD);
		 WritableCellFormat arial12format = new WritableCellFormat(arial12pt);
		 arial12format.setWrap(false);
		 label = new Label(i, 0, ss[i], arial12format);
		 ws.addCell(label);
	    }
	  for(int i = kk*sheetSize; i< (kk+1)*sheetSize ; i++ ){
         if(i<length ){
                   Customer customer = (Customer) customers.get(i);
           for (int j = 0; j < 8; j++) {
            switch(j){
                case 0:label=new  Label(j, i+1-(kk*sheetSize), customer.getCustomerName());break;
                case 1:label=new  Label(j,i+1-(kk*sheetSize), customer.getCustomerSex());break;
                case 2:label=new  Label(j, i+1-(kk*sheetSize), customer.getPhoneNo());break;
              //  case 3:lable=new  Label(j, i, customer.getCategoryName());break;
                case 3:label=new  Label(j, i+1-(kk*sheetSize), customer.getCardType());break;
                case 4:label=new  Label(j, i+1-(kk*sheetSize), customer.getCardId());break;
                case 5:label=new  Label(j, i+1-(kk*sheetSize), customer.getCustomerCorp());break;
                case 6:label=new  Label(j, i+1-(kk*sheetSize), customer.getHomeAddr());break;
                case 7:label=new  Label(j, i+1-(kk*sheetSize), customer.getCustomerJoy());break;
                default:label=new Label(j,i+1-(kk*sheetSize),"");
                
            }
           
			ws.addCell(label);
		}
           }else{
                break;
            }
	    }
	 }
	  wwb.write();
	  wwb.close();
	  os.close();
      out.clear();
      out = pageContext.pushBody();
	  return;
%>

 

评论

2 楼 还有也许 2010-09-29  

emlyn_1180 写道

excel2003中sheet的最大记录是65535  2007更大一些
另外不知道你所谓的大数据量到底有多大？有没有试过超过50W的记录？

我遇到的是excel2003的，大数据量我没有去测试他到底有多大，不过如果你后台能查出来，前台页面的这段导出应该都能写入吧。对于你说的2007能更大好像和我的程序不冲突吧，每个sheet显示多少条，自己设置，只要不超过excel自身所能承受的大小不就可以了。

1 楼 emlyn_1180 2010-09-28  

excel2003中sheet的最大记录是65535  2007更大一些
另外不知道你所谓的大数据量到底有多大？有没有试过超过50W的记录？