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; }

以此记录下...