strut1中jxl导出excel

1.模板文件为moban.xls

2.利用jxl包

3.这里描述的是，导出企业列表和，导出三张危险表到对应一个sheet中

public ActionForward check_excel(ActionMapping mapping, ActionForm form,
HttpServletRequest request, HttpServletResponse response) throws IOException {



// 定义文件的上传路径
String realpath = request.getSession().getServletContext().getRealPath("/upload/");
Workbook wb = null;
WritableWorkbook wwb = null;
WritableSheet wws = null;
SimpleDateFormat sdf = new SimpleDateFormat("yyyy-MM-dd");
WritableFont font= new WritableFont(WritableFont.createFont("宋体"),12);
WritableCellFormat cellFormat = new WritableCellFormat(font);
OutputStream os = response.getOutputStream();  //取得输出流  
        response.reset(); //清空输出流  
        response.setContentType ( "application/ms-excel" );
response.setHeader ( "Content-Disposition","attachment;filename="+new String("企业和危险源列表.xls".getBytes(),"iso-8859-1"));
try{
cellFormat.setAlignment(Alignment.CENTRE);
wb = Workbook.getWorkbook(new File(realpath + "/moban.xls"));
// File targetFile = new File(realpath + "/excel/" + "qyAndWxyList.xls");
wwb = Workbook.createWorkbook(os, wb);
List<BtDwjbqk> qyList=excelControl.getQyxxList();
for (int i = 0; i < qyList.size();i++) {
for(int j=0;j<wwb.getSheet(0).getColumns();j++){
BtDwjbqk jcxx = qyList.get(i);
wws = wwb.getSheet(0);
// 企业编码
if (jcxx.getDwId() != null) {
jxl.write.Number qybm = new jxl.write.Number(0, i+1, jcxx.getDwId(),cellFormat);
wws.addCell(qybm);
}
// 企业名称
Label qymc = new Label(1, i+1, jcxx.getDwMc(),cellFormat);
wws.addCell(qymc);
// 企业地址
Label qydz = new Label(2, i+1, jcxx.getDwTxdz(),cellFormat);
wws.addCell(qydz);
// 法定代表人
Label fzr = new Label(3, i+1, jcxx.getDwFrdb(),cellFormat);
wws.addCell(fzr);
// 邮政编码
Label yzbm = new Label(5, i+1, jcxx.getDwYzbm(),cellFormat);
wws.addCell(yzbm);
// 单位传真
Label dwcz = new Label(6, i+1, jcxx.getDwCz(),cellFormat);
wws.addCell(dwcz);
// 安全负责人
Label aqfzr = new Label(7, i+1, jcxx.getDwAqfzr(),cellFormat);
wws.addCell(aqfzr);
// 安全负责人联系电话
Label aqfzrlxdh = new Label(8, i+1, jcxx.getDwAqfzrdh(),cellFormat);
wws.addCell(aqfzrlxdh);
// 行政区划
Label xzqh = new Label(9, i+1, jcxx.getDwSzdajjg(),cellFormat);
wws.addCell(xzqh);
// 年销售收入
if (jcxx.getDwNzcz() != null) {
jxl.write.Number nxssr = new jxl.write.Number(12, i+1, jcxx.getDwNzcz(),cellFormat);
wws.addCell(nxssr);
}
// 年利润
if (jcxx.getDwNlr() != null) {
jxl.write.Number nlr = new jxl.write.Number(13, i+1, jcxx.getDwNlr(),cellFormat);
wws.addCell(nlr);
}
// 占地面积
if (jcxx.getDwZdmj() != null) {
jxl.write.Number zdmj = new jxl.write.Number(14, i+1, jcxx.getDwZdmj(),cellFormat);
wws.addCell(zdmj);
}
// 职工人数
if (jcxx.getDwZgrs() != null) {
jxl.write.Number zgrs = new jxl.write.Number(15, i+1, jcxx.getDwZgrs(),cellFormat);
wws.addCell(zgrs);
}
// 企业成立时间
if (jcxx.getDwClsj() != null) {
wws.addCell(new Label(16,i+1,sdf.format(jcxx.getDwClsj()),cellFormat));
}
// 所属行业
if(jcxx.getDwbbzl()!=null && jcxx.getDwbbzl().equals("1")){
wws.addCell(new Label(23,i+1,"危险化学品",cellFormat));
}else if(jcxx.getDwbbzl()!=null && (jcxx.getDwbbzl().equals("3")||jcxx.getDwbbzl().equals("4"))){
wws.addCell(new Label(23,i+1,"非煤矿山",cellFormat));
}else if(jcxx.getDwbbzl()!=null && jcxx.getDwbbzl().equals("2")){
wws.addCell(new Label(23,i+1,"煤矿",cellFormat));
}else{wws.addCell(new Label(23,i+1,"其它",cellFormat));}
// 隶属关系
if(jcxx.getDwLsgx()!=null && jcxx.getDwLsgx().equals("1")){
wws.addCell(new Label(24,i+1,"中央",cellFormat));
}else if(jcxx.getDwLsgx()!=null && jcxx.getDwLsgx().equals("2")){
wws.addCell(new Label(24,i+1,"省属",cellFormat));
}else if(jcxx.getDwLsgx()!=null && jcxx.getDwLsgx().equals("3")){
wws.addCell(new Label(24,i+1,"市属",cellFormat));
}else if(jcxx.getDwLsgx()!=null && jcxx.getDwLsgx().equals("4")){
wws.addCell(new Label(24,i+1,"县属",cellFormat));
}else{
wws.addCell(new Label(24,i+1,"其它",cellFormat));
}
// 经济类型
if(jcxx.getDwZcdjlx()!=null && jcxx.getDwZcdjlx().equals("1")){
wws.addCell(new Label(25,i+1,"国有",cellFormat));
}else if(jcxx.getDwZcdjlx()!=null && jcxx.getDwZcdjlx().equals("2")){
wws.addCell(new Label(25,i+1,"集体",cellFormat));
}else if(jcxx.getDwZcdjlx()!=null && (jcxx.getDwZcdjlx().equals("10") || jcxx.getDwZcdjlx().equals("6"))){
wws.addCell(new Label(25,i+1,"股份合作",cellFormat));
}else if(jcxx.getDwZcdjlx()!=null && jcxx.getDwZcdjlx().equals("5")){
wws.addCell(new Label(25,i+1,"联营",cellFormat));
}else if(jcxx.getDwZcdjlx()!=null && jcxx.getDwZcdjlx().equals("3")){
wws.addCell(new Label(25,i+1,"私营",cellFormat));
}else if(jcxx.getDwZcdjlx()!=null && (jcxx.getDwZcdjlx().equals("8") || jcxx.getDwZcdjlx().equals("7"))){
wws.addCell(new Label(25,i+1,"外商投资",cellFormat));
}else {wws.addCell(new Label(25,i+1,"其它",cellFormat));}
 }
}
// 列表--重大危险源(危险化学品)
List<BtWxhxpqk> hxpList = excelControl.getHxpList();
wws = wwb.getSheet(1);
for (int i1 = 0;i1<hxpList.size();i1++) {
for(int j1=0;j1<wwb.getSheet(1).getColumns();j1++){
BtWxhxpqk whp = hxpList.get(i1);
// 企业编号
if (whp.getDwId() != null) {
jxl.write.Number qybh = new jxl.write.Number(0, i1+1, whp.getDwId(),cellFormat);
wws.addCell(qybh);
BtDwjbqk entity=excelControl.getQyById(whp.getDwId());
Label qymc1=new Label(1,i1+1,(entity!=null?entity.getDwMc():""),cellFormat);
wws.addCell(qymc1);
}
// 等级规模
Label djgm = new Label(4, i1+1, whp.getWhpSbdj(),cellFormat);
wws.addCell(djgm);
// 所处位置物理性质
Label wlxz = new Label(5, i1+1, whp.getWhpXxdz(),cellFormat);
wws.addCell(wlxz);
}
}
//尾矿库
List<BtWkk> wkkList=excelControl.getWkkList();
int length=wws.getRows();
for (int i2=0;i2<wkkList.size();i2++) {
for(int j2=0;j2<wwb.getSheet(1).getColumns();j2++){
BtWkk wkk = wkkList.get(i2);
// 企业编号
if (wkk.getDwId() != null) {
jxl.write.Number qybh = new jxl.write.Number(0, i2+length, wkk.getDwId(),cellFormat);
wws.addCell(qybh);
BtDwjbqk entity=excelControl.getQyById(wkk.getDwId());
Label qymc1=new Label(1,i2+length,entity.getDwMc(),cellFormat);
wws.addCell(qymc1);
}
// 危险源名称
Label wxymc = new Label(2, i2+length, wkk.getWkkMc(),cellFormat);
wws.addCell(wxymc);
// 等级规模
Label djgm = new Label(4, i2+length, wkk.getWkkSbdj(),cellFormat);
wws.addCell(djgm);
// 所处位置物理性质
Label wlxz = new Label(5, i2+length, wkk.getWkkXxdz(),cellFormat);
wws.addCell(wlxz);
}
}
//金属非金属地下矿
List<BtJsfjsdxk> fjsList=excelControl.getFjsList();
int length1=wws.getRows();
for (int i3=0;i3<fjsList.size();i3++) {
for(int j3=0;j3<wwb.getSheet(1).getColumns();j3++){
BtJsfjsdxk fjs = fjsList.get(i3);
// 企业编号
if (fjs.getDwId() != null) {
jxl.write.Number qybh = new jxl.write.Number(0, i3+length1, fjs.getDwId(),cellFormat);
wws.addCell(qybh);
BtDwjbqk entity=excelControl.getQyById(fjs.getDwId());
Label qymc1=new Label(1,i3+length1,entity.getDwMc(),cellFormat);
wws.addCell(qymc1);
}
// 危险源名称
Label wxymc = new Label(2, i3+length1, fjs.getDxkMc(),cellFormat);
wws.addCell(wxymc);
// 等级规模
Label djgm = new Label(4, i3+length1, fjs.getDxkSbdj(),cellFormat);
wws.addCell(djgm);
// 所处位置物理性质
Label wlxz = new Label(5, i3+length1, fjs.getDxkXxdz(),cellFormat);
wws.addCell(wlxz);
}
}
//煤矿
List<BtJgkcmk> mkList=excelControl.getMcList();
int length2=wws.getRows();
for (int i4=0;i4<mkList.size();i4++) {
for(int j4=0;j4<wwb.getSheet(1).getColumns();j4++){
BtJgkcmk mk = mkList.get(i4);
// 企业编号
if (mk.getDwId() != null) {
jxl.write.Number qybh = new jxl.write.Number(0, i4+length2, mk.getDwId(),cellFormat);
wws.addCell(qybh);
BtDwjbqk entity=excelControl.getQyById(mk.getDwId());
Label qymc1=new Label(1,i4+length2,entity.getDwMc(),cellFormat);
wws.addCell(qymc1);
}
// 危险源名称
Label wxymc = new Label(2, i4+length2, mk.getKjMc(),cellFormat);
wws.addCell(wxymc);
// 等级规模
Label djgm = new Label(4, i4+length2, mk.getKjSbdj(),cellFormat);
wws.addCell(djgm);
// 所处位置物理性质
Label wlxz = new Label(5, i4+length2, mk.getKjXxdz(),cellFormat);
wws.addCell(wlxz);
}
}
wwb.write();
wwb.close();
wb.close();
os.close();
}catch (Exception e) {
e.printStackTrace();
}
return null;
}

以此记录下...