SSI框架下，用jxl实现导出功能

先说明一下，这个是SSI框架下，前端用ExtJs，应用在一个企业级的系统中的导出功能，因为是摸索着做的，所以里面有一些代码想整理一下，如果有人看到了，请视自己的架构酌情借用

JS获取查询条件并跳转页面

//eform中按钮的click事件jsvar wtly = comboBox.lastSelectionText;//问题来源if(wtly==undefined)wtly="";var fxwt = searchField_fxwt.getValue();//分析问题var whyslb = comboBox_1.lastSelectionText;//不安全行为类别if(whyslb==undefined)whyslb="";var wzxwlb = comboBox_2.lastSelectionText;//违章类型类别if(wzxwlb==undefined)wzxwlb="";var wzxz = comboBox_3.lastSelectionText;//违章性质if(wzxz==undefined)wzxz="";var fxr = searchField_fxr.getValue();//发现人var fxsjStart = searchField_fxsj.getValue();//开始时间var fxsjEnd = dateField.getValue();//结束时间var url='doBzaqAqjcZcxwAction_expWorkList?fxr='+fxr+'&wtly='+wtly+'&fxwt='+fxwt+'&whyslb='+whyslb+'&wzxwlb='+wzxwlb+'&wzxz='+wzxz+'&fxsjStart='+fxsjStart+'&fxsjEnd='+fxsjEnd;window.location.href =url;

下面是Spring 中配置相关的业务层和DAO层

//Spring applicationContext注入相关的service，dao<!-- 次日高风险作业表的配置 -->    <bean id="BzaqXxglCrgfxzybAction" class="md.bzaq.xxbs.crgfx.action.BzaqXxglCrgfxzybAction" scope="prototype">        <property name="bzaqXxglCrgfxzybService">            <ref bean="bzaqXxglCrgfxzybService" />        </property>        <property name="bzaqAqjcZcxwService">            <ref bean="bzaqAqjcZcxwService" />        </property>        <property name="bzaqAqjcZczzService">            <ref bean="bzaqAqjcZczzService" />        </property>        <property name="bzaqAqjcZcglService">            <ref bean="bzaqAqjcZcglService" />        </property>    </bean>    <bean id="bzaqXxglCrgfxzybService" class="md.bzaq.xxbs.crgfx.service.BzaqXxglCrgfxzybServiceImpl">        <property name="bzaqXxglCrgfxzybDao">            <ref bean="bzaqXxglCrgfxzybDao" />        </property>    </bean>    <bean id="bzaqXxglCrgfxzybDao" class="md.bzaq.xxbs.crgfx.dao.BzaqXxglCrgfxzybDAOImpl">        <property name="sqlMapClientTemplate" ref="sqlMapClientTemplate" />    </bean>

下面是后台控制层处理查询数据以及导出数据

//Controller  后台Action中的方法public void expWorkList(){        SimpleDateFormat sdf=new SimpleDateFormat("yyyy-MM-dd");//小写的mm表示的是分钟        SimpleDateFormat sdf1=new SimpleDateFormat("yyyy-MM");//小写的mm表示的是分钟        Date today = new Date();        //定义查找对象        BzaqAqjcXwhzSearch bzaqAqjcXwhzSearch= new BzaqAqjcXwhzSearch();        try {            //解码，并且将参数存入查找对象中            if(fxr!=null&&!"".equals(fxr)){                fxr = java.net.URLDecoder.decode(fxr,"utf-8");//                bzaqAqjcXwhzSearch.setFxr(fxr);            }            if(wtly!=null&&!"".equals(wtly)){                wtly = java.net.URLDecoder.decode(wtly,"utf-8");                bzaqAqjcXwhzSearch.setWtly(wtly);            }            if(fxwt !=null&&!"".equals(fxwt )){                fxwt  = java.net.URLDecoder.decode(fxwt ,"utf-8");                bzaqAqjcXwhzSearch.setFxwt(fxwt);            }            if(whyslb!=null&&!"".equals(whyslb)){                whyslb = java.net.URLDecoder.decode(whyslb,"utf-8");                bzaqAqjcXwhzSearch.setWhyslb(whyslb);            }            if(wzxwlb !=null&&!"".equals(wzxwlb)){                wzxwlb  = java.net.URLDecoder.decode(wzxwlb ,"utf-8");                bzaqAqjcXwhzSearch.setWzxwlb(wzxwlb);            }            if(wzxz !=null&&!"".equals(wzxz )){                wzxz  = java.net.URLDecoder.decode(wzxz ,"utf-8");                bzaqAqjcXwhzSearch.setWzxz(wzxz );            }            if(fxsjStart !=null&&!"".equals(fxsjStart )){                                bzaqAqjcXwhzSearch.setFxsjStart(fxsjStart );            }            if(fxsjEnd!=null&&!"".equals(fxsjEnd)){                                bzaqAqjcXwhzSearch.setFxsjEnd(fxsjEnd);            }            if(sfww!=null&&sfww.equals("是")){                bzaqAqjcXwhzSearch.setSfww("是");            }        } catch (UnsupportedEncodingException e2) {            // TODO Auto-generated catch block            e2.printStackTrace();        }                //排序属性        bzaqAqjcXwhzSearch.setOrderByClause(" fxsj  desc");                        HttpServletRequest request = (HttpServletRequest)ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_REQUEST);        String filePath=request.getSession().getServletContext().getRealPath("/");//模板路径        String fileName = "行为违章.xls";//导出文件名        if(ServletActionContext.getRequest().getHeader( "USER-AGENT" ).toLowerCase().indexOf( "msie" ) >  0  ){            try {                fileName =java.net.URLEncoder.encode(fileName,"utf-8");            } catch (UnsupportedEncodingException e1) {                // TODO Auto-generated catch block                e1.printStackTrace();            }        }        filePath=filePath.replaceAll("\\\\", "/");//模板路径        filePath = filePath + "docs/xwwz.xls";//模板路径        InputStream is;        //所有的任务清单        List bzaqAqjcZcxwList = new ArrayList();        try {            is = new FileInputStream(filePath);            //Workbook为只读            jxl.Workbook wb = jxl.Workbook.getWorkbook(is);               //WritableWorkbook为可写入。            //将WritableWorkbook直接写入到输出流。            HttpServletResponse response = (HttpServletResponse)ActionContext.getContext().get(org.apache.struts2.StrutsStatics.HTTP_RESPONSE);            OutputStream os = response.getOutputStream();           jxl.write.WritableWorkbook wbook = jxl.Workbook.createWorkbook(os,wb);                      //可写入的单元格格式。           jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(); // 单元格定义            wcf.setBackground(jxl.format.Colour.BLACK); // 设置单元格的背景颜色            WritableFont font = new WritableFont(WritableFont.createFont("宋体"),10);//设置字体            wcf.setFont(font);            wcf.setAlignment(jxl.format.Alignment.LEFT); // 设置对齐方式左右居中            wcf.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE);// 设置对齐方式上下居中            wcf.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN);            wcf.setWrap(true);                      //工作表。           jxl.write.WritableSheet wsheet = wbook.getSheet(0);           wsheet.setName("行为违章.xls");           jxl.write.Label label = null;            //任务清单列表数据           bzaqAqjcZcxwList = bzaqAqjcZcxwService.getBzaqAqjcZcxwshz1(bzaqAqjcXwhzSearch);//查出要导出的数据            int rwrows = 2;            if(bzaqAqjcZcxwList.size()>0){                for(int i=0;i<bzaqAqjcZcxwList.size();i++){                    BzaqAqjcXwhz obj=(BzaqAqjcXwhz)bzaqAqjcZcxwList.get(i);                           label =new  jxl.write.Label(0,rwrows+i ,obj.getFxwt(),wcf);                       wsheet.addCell(label);                                       label =new  jxl.write.Label(1,rwrows+i ,obj.getWzxz(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(2,rwrows+i ,obj.getWzxwlb(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(3,rwrows+i ,obj.getZrdw(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(4,rwrows+i ,obj.getZrbm(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(5,rwrows+i ,obj.getZrbz(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(6,rwrows+i ,obj.getSfww(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(7,rwrows+i ,obj.getZrr(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(8,rwrows+i ,obj.getJzqk(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(9,rwrows+i ,obj.getZrrjf(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(10,rwrows+i ,obj.getZrrcfje(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(11,rwrows+i ,obj.getLdkhzrr(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(12,rwrows+i ,obj.getLdkhje(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(13,rwrows+i ,obj.getWzyyfx(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(14,rwrows+i ,obj.getJzyfcs(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(15,rwrows+i ,obj.getJcbm(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(16,rwrows+i ,obj.getZw(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(17,rwrows+i ,obj.getFxr(),wcf);                       wsheet.addCell(label);                                              label =new  jxl.write.Label(18,rwrows+i ,obj.getWtly(),wcf);                       wsheet.addCell(label);                       Date fxsj = obj.getFxsj();                       if(fxsj !=null)                       {                              label =new  jxl.write.Label(19,rwrows+i ,sdf.format(obj.getFxsj()),wcf);                           wsheet.addCell(label);                       }                       if(obj.getYf()!=null)                       {                           label =new  jxl.write.Label(20,rwrows+i ,sdf1.format(obj.getYf()),wcf);                           wsheet.addCell(label);                       }                       label =new  jxl.write.Label(21,rwrows+i ,obj.getWhyslb(),wcf);                       wsheet.addCell(label);                                       }                        }                        ///////在这里继续加第二个sheet页///////            jxl.write.WritableSheet wsheet = wbook.getSheet(1);                                    //获取客户端浏览器和操作系统信息             if(ServletActionContext.getRequest().getHeader( "USER-AGENT" ).toLowerCase().indexOf( "msie" ) >  0  ){                    response.setHeader("Content-Disposition", "attachment; filename="+fileName);                }else{                    response.addHeader("Content-Disposition", "attachment;filename="+ new String(fileName.getBytes("GB2312"),"ISO-8859-1"));                }               response.setContentType("application/vnd.ms-excel");               wbook.write();               wbook.close();               os.close();                    } catch (FileNotFoundException e) {            // TODO Auto-generated catch block            e.printStackTrace();        }catch (IOException e) {            // TODO Auto-generated catch block            e.printStackTrace();        } catch (Exception e1) {            // TODO Auto-generated catch block            e1.printStackTrace();        }    }

模板

在对应的路径下，做一个对应文件名的EXCEL模板，SHEET页和标题，以及各字段的数据类型要和后台查出来的一致；