关于Jxl导出excel的简单实例

1.导入JAR包  链接：http://pan.baidu.com/s/1mMIEM  密码：o9hw  可以百度网盘下载

2.JAVA代码

package com.netmarch.personinfo.controller;

import java.io.OutputStream;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

import javax.servlet.http.HttpServletRequest;
import javax.servlet.http.HttpServletResponse;

import jxl.Workbook;
import jxl.format.Colour;
import jxl.format.UnderlineStyle;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;

import org.apache.log4j.Logger;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Controller;
import org.springframework.web.bind.annotation.RequestMapping;
import org.springframework.web.bind.annotation.ResponseBody;

import com.netmarch.common.Login;
import com.netmarch.personinfo.bean.Fangke;
import com.netmarch.personinfo.bean.Fangkewupin;
import com.netmarch.personinfo.service.FangkeService;
import com.netmarch.personinfo.service.FangkeZhaopianService;
import com.netmarch.personinfo.service.FangkewupinService;

@RequestMapping("/fk/*")
@Controller
@Login
public class FangkeexportController {
@Autowired
FangkeService fkService;
@Autowired
FangkewupinService fangkewupinservice;
@Autowired
FangkeZhaopianService fangkezhaopianservice;
private Logger logger = Logger.getLogger(FangkeexportController.class);

@ResponseBody
@RequestMapping("export")
public void fdzp(HttpServletResponse res,HttpServletRequest request,Fangke fk) {
try {
List<Fangke> list=new ArrayList<Fangke>();
//获取参数
//if(request.getParameter("xqid")!=null) fk.setXqid(Integer.parseInt(request.getParameter("xqid")));
//if(request.getParameter("ldid")!=null) fk.setLdid(Integer.parseInt(request.getParameter("ldid")));
//if(request.getParameter("fjid")!=null) fk.setFjid(Integer.parseInt(request.getParameter("fjid")));
//if(request.getParameter("zjhm")!=null){
//String zjhm=request.getParameter("zjhm").toString();
//java.net.URLDecoder.decode(zjhm,"UTF-8");  
//zjhm = new String(zjhm.getBytes("iso-8859-1"),"UTF-8"); //对中文参数的转换 
//fk.setZjhm(zjhm);
//}
//if(request.getParameter("xm")!=null){
//String xm=request.getParameter("xm").toString();
//java.net.URLDecoder.decode(xm,"UTF-8");  
//xm = new String(xm.getBytes("iso-8859-1"),"UTF-8"); //对中文参数的转换 
//fk.setXm(xm);
//}
//if(request.getParameter("status")!=null) fk.setStatus(Integer.parseInt(request.getParameter("status")));
//if(request.getParameter("zlzt")!=null) fk.setZlzt(Integer.parseInt(request.getParameter("zlzt")));
//获取房客信息
list=fkService.getFangkeExport(fk);

SimpleDateFormat df = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss");//设置日期格式
OutputStream os = res.getOutputStream();// 取得输出流   
res.reset();// 清空输出流   
res.setHeader("Content-disposition", "attachment; filename=fkinfo"+df.format(new Date())+".xls");// 设定输出文件头   
res.setContentType("application/msexcel");// 定义输出类型 

        WritableWorkbook wbook = Workbook.createWorkbook(os); // 建立excel文件   
        String tmptitle = "房客信息"; // 标题   
        WritableSheet wsheet = wbook.createSheet(tmptitle, 0); // sheet名称  

        // 设置excel标题   
WritableFont wfont = new WritableFont(WritableFont.ARIAL, 16,WritableFont.BOLD, 
                       false,UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
WritableCellFormat wcfFC = new WritableCellFormat(wfont); 
wcfFC.setBackground(Colour.AQUA); 
wsheet.addCell(new Label(1, 0, "日期："+df.format(new Date())+"导出的房客信息", wcfFC));   
wfont = new jxl.write.WritableFont(WritableFont.ARIAL, 14,WritableFont.BOLD, 
                   false, UnderlineStyle.NO_UNDERLINE,Colour.BLACK);   
wcfFC = new WritableCellFormat(wfont);  

// 开始生成主体内容                   
wsheet.addCell(new Label(0, 1, "序号"));   
wsheet.addCell(new Label(1, 1, "房客姓名")); 
wsheet.addCell(new Label(2, 1, "房客性别")); 
wsheet.addCell(new Label(3, 1, "身份证号")); 
wsheet.addCell(new Label(4, 1, "户籍地址")); 
wsheet.addCell(new Label(5, 1, "手机号码")); 
wsheet.addCell(new Label(6, 1, "所在单位")); 
wsheet.addCell(new Label(7, 1, "入住日期")); 
wsheet.addCell(new Label(8, 1, "小区"));

wsheet.addCell(new Label(9, 1, "楼栋"));
wsheet.addCell(new Label(10, 1, "单元"));
wsheet.addCell(new Label(11, 1, "房间"));
wsheet.addCell(new Label(12, 1, "小房间"));

wsheet.addCell(new Label(13, 1, "房产所有者姓名"));
wsheet.addCell(new Label(14, 1, "房产所有者电话"));
wsheet.addCell(new Label(15, 1, "房产所有者身份证号"));
wsheet.addCell(new Label(16, 1, "房屋出租人姓名"));
wsheet.addCell(new Label(17, 1, "房屋出租人电话"));
wsheet.addCell(new Label(18, 1, "房屋出租人身份证号"));

wsheet.addCell(new Label(19, 1, "手机品牌"));
wsheet.addCell(new Label(20, 1, "手机颜色"));
wsheet.addCell(new Label(21, 1, "手机型号"));
wsheet.addCell(new Label(22, 1, "手机号码"));
wsheet.addCell(new Label(23, 1, "手机串号"));

wsheet.addCell(new Label(24, 1, "电脑品牌"));
wsheet.addCell(new Label(25, 1, "电脑颜色"));
wsheet.addCell(new Label(26, 1, "电脑型号"));
wsheet.addCell(new Label(27, 1, "电脑MAC地址"));
wsheet.addCell(new Label(28, 1, "QQ号码"));
wsheet.addCell(new Label(29, 1, "宽带号码"));

wsheet.addCell(new Label(30, 1, "电瓶车品牌"));
wsheet.addCell(new Label(31, 1, "电瓶车型号"));
wsheet.addCell(new Label(32, 1, "车牌号码"));
wsheet.addCell(new Label(33, 1, "电机号码"));

wsheet.setColumnView(0,6);
wsheet.setColumnView(1,8);
wsheet.setColumnView(2,8);
wsheet.setColumnView(3,20);
wsheet.setColumnView(4,23);
wsheet.setColumnView(5,13);
wsheet.setColumnView(6,20);
wsheet.setColumnView(7,13);
wsheet.setColumnView(8,8);

wsheet.setColumnView(9,8);
wsheet.setColumnView(10,8);
wsheet.setColumnView(11,8);
wsheet.setColumnView(12,8);

wsheet.setColumnView(13, 8);
wsheet.setColumnView(14, 13);
wsheet.setColumnView(15, 20);
wsheet.setColumnView(16, 8); 
wsheet.setColumnView(17, 25);  
wsheet.setColumnView(18, 20);  

wsheet.setColumnView(19, 13);  
wsheet.setColumnView(20, 13);  
wsheet.setColumnView(21, 13);  
wsheet.setColumnView(22, 13);  
wsheet.setColumnView(23, 13);  

wsheet.setColumnView(24, 13);  
wsheet.setColumnView(25, 13);  
wsheet.setColumnView(26, 13);
wsheet.setColumnView(27, 13);
wsheet.setColumnView(28, 13);
wsheet.setColumnView(29, 13);

wsheet.setColumnView(30, 13);
wsheet.setColumnView(31, 13);
wsheet.setColumnView(32, 13);
wsheet.setColumnView(33, 13);

for(int i=0;i<list.size();i++){ 

    wsheet.addCell(new Label(0, i+2, (i+1)+""));   //数据库的城市代码字段
    wsheet.addCell(new Label(1, i+2, list.get(i).getXm()));  //数据库的城市名字段
    wsheet.addCell(new Label(2, i+2, list.get(i).getXb() == 1 ? "男" : "女"));  //数据库的城市名字段
    wsheet.addCell(new Label(3, i+2, list.get(i).getZjhm()));  //数据库的城市名字段
    wsheet.addCell(new Label(4, i+2, list.get(i).getHjdz()));  //数据库的城市名字段
    wsheet.addCell(new Label(5, i+2, list.get(i).getSjhm()));  //数据库的城市名字段
    wsheet.addCell(new Label(6, i+2, list.get(i).getDwmc()));  //数据库的城市名字段
    wsheet.addCell(new Label(7, i+2, list.get(i).getRzrq()));  //数据库的城市名字段
    wsheet.addCell(new Label(8, i+2, list.get(i).getXqmc()));  //数据库的城市名字段

    wsheet.addCell(new Label(9, i+2, list.get(i).getLdmc()));  //数据库的城市名字段
    wsheet.addCell(new Label(10, i+2, list.get(i).getDyh()+"单元 "));  //数据库的城市名字段
    wsheet.addCell(new Label(11, i+2, list.get(i).getFjmc()));  //数据库的城市名字段
    wsheet.addCell(new Label(12, i+2, ""+list.get(i).getXfjh()));  //数据库的城市名字段

    wsheet.addCell(new Label(13, i+2, list.get(i).getFdname()));  //数据库的城市名字段
    wsheet.addCell(new Label(15, i+2, list.get(i).getFdsj()));  //数据库的城市名字段
    wsheet.addCell(new Label(14, i+2, list.get(i).getFdzjh()));  //数据库的城市名字段
    wsheet.addCell(new Label(16, i+2, list.get(i).getCname()));  //数据库的城市名字段
    wsheet.addCell(new Label(17, i+2, list.get(i).getCsj()));  //数据库的城市名字段
    wsheet.addCell(new Label(18, i+2, list.get(i).getCzjh()));  //数据库的城市名字段

    List<Fangkewupin> sjlist=new ArrayList<Fangkewupin>();
List<Fangkewupin> dnlist=new ArrayList<Fangkewupin>();
List<Fangkewupin> dpclist=new ArrayList<Fangkewupin>();
sjlist=fangkewupinservice.getSjList(list.get(i).getId());
dnlist=fangkewupinservice.getDnList(list.get(i).getId());
dpclist=fangkewupinservice.getDpcList(list.get(i).getId());

//获取照片信息  
Fangkewupin sj=new Fangkewupin();
Fangkewupin dn=new Fangkewupin();
Fangkewupin dpc=new Fangkewupin();
if(sjlist != null && sjlist.size()>0){sj = sjlist.get(0);}else{sj = new Fangkewupin();}
if(dnlist != null && dnlist.size()>0){dn = dnlist.get(0);}else{dn = new Fangkewupin();}
if(dpclist != null && dpclist.size()>0){dpc = dpclist.get(0);}else{dpc = new Fangkewupin();}
System.out.println("ID:"+list.get(i).getId()+"-手机品牌："+sj.getPp());
wsheet.addCell(new Label(19, i+2, sj.getPp()));  //数据库的城市名字段
wsheet.addCell(new Label(20, i+2, sj.getYs()));  //数据库的城市名字段
wsheet.addCell(new Label(21, i+2, sj.getXh()));  //数据库的城市名字段
wsheet.addCell(new Label(22, i+2, sj.getSjhm()));  //数据库的城市名字段
wsheet.addCell(new Label(23, i+2, sj.getDzch()));  //数据库的城市名字段

wsheet.addCell(new Label(24, i+2, dn.getPp()));  //数据库的城市名字段
wsheet.addCell(new Label(25, i+2, dn.getYs()));  //数据库的城市名字段
wsheet.addCell(new Label(26, i+2, dn.getXh()));  //数据库的城市名字段
wsheet.addCell(new Label(27, i+2, dn.getMacdz()));  //数据库的城市名字段
wsheet.addCell(new Label(28, i+2, dn.getQqhm()));  //数据库的城市名字段
wsheet.addCell(new Label(29, i+2, dn.getKdhm()));  //数据库的城市名字段

wsheet.addCell(new Label(30, i+2, dpc.getPp()));  //数据库的城市名字段
wsheet.addCell(new Label(31, i+2, dpc.getXh()));  //数据库的城市名字段
wsheet.addCell(new Label(32, i+2, dpc.getCphm()));  //数据库的城市名字段
wsheet.addCell(new Label(33, i+2, dpc.getDjhm()));  //数据库的城市名字段
}           
// 主体内容生成结束           
wbook.write(); // 写入文件   
wbook.close();  
os.close(); // 关闭流
} 
catch(Exception ex) 
{ 
logger.error("导出房客信息出错！", ex);
} 
}
}

本人的框架是springMVC+Mybatis

3.页面JSP的调用 和请求action是一样的