最近写的一个小程序:
需求:遍历数据库的数据,然后生成Excel表 ,导出数据(利用jxl.jar包)
有兴趣的同学可以参考一下(其他同事介绍使用poi.jar,但没试过,听说比jxl.jar好)
- 生成excel表
- @RequestMapping(value="/xixi/xuan/createExcel",method={RequestMethod.GET,RequestMethod.POST})
- publicStringcreateExcel(HttpServletRequestrequest,HttpServletResponseresponse)throwsIOException,RowsExceededException,WriteException{
- OutputStreamos=response.getOutputStream();
- ParamUtilpu=newParamUtil(request);
- Stringqid=pu.getString("qid");
- StringfileName="VisitorData";
- response.setContentType("application/vnd.ms-excel");
- response.addHeader("Content-Disposition","attachment;filename=\""+fileName+".xls"+"\"");
- List<QuserInfoRecord>list=quserInfoRecordService.findQuserInfoRecord(qid);
- if(list.size()>0){
- /*StringexcelPath="D:\\data.xls";
- FileexcelFile=newFile(excelPath);
- if(!excelFile.exists()){
- excelFile.createNewFile();
- }*/
- System.out.println("=====kaisi");
- ///打开文件
- WritableWorkbookbook=Workbook.createWorkbook(os);
- ///生成一个名为"用户信息"的工作表
- WritableSheetws=book.createSheet(fileName,0);//新建一个sheet
- //在Label对象的构造子中指名单元格位置是第一列第一行(0,0)
- //以及单元格内容为test
- //Labellabel=newLabel(0,0,"test");
- //往表中添加内容
- //将定义好的单元格添加到工作表中
- JSONArrayjsonArray=JSONArray.fromObject(list.get(0).getExt());
- intlenght=jsonArray.size();
- WritableCellFormatwcf=null;
- //字体样式
- WritableFontwf=newWritableFont(WritableFont.TIMES,12,WritableFont.BOLD,false);//最后一个为是否italic
- wf.setColour(Colour.BLACK);
- wcf=newWritableCellFormat(wf);
- //对齐方式
- wcf.setAlignment(Alignment.CENTRE);
- wcf.setVerticalAlignment(VerticalAlignment.CENTRE);
- //边框
- wcf.setBorder(Border.ALL,BorderLineStyle.THIN);
- for(inti=0;i<list.size();i++){
- JSONArrayjsonlist=JSONArray.fromObject(list.get(i).getExt());
- Labellabel11=newLabel(0,0,"日期",wcf);
- ws.setColumnView(0,20);
- ws.addCell(label11);
- Labellabel1=newLabel(0,i+1,list.get(i).getDate());
- ws.addCell(label1);
- Labellabel12=newLabel(1,0,"来源标题",wcf);
- ws.setColumnView(1,30);
- ws.addCell(label12);
- Labellabel2=newLabel(1,i+1,list.get(i).getSourcetitle());
- ws.addCell(label2);
- Labellabel13=newLabel(2,0,"页面链接",wcf);
- ws.setColumnView(2,50);
- ws.addCell(label13);
- Labellabel3=newLabel(2,i+1,list.get(i).getUserhref());
- ws.addCell(label3);
- Labellabel14=newLabel(3,0,"IP来源",wcf);
- ws.setColumnView(3,20);
- ws.addCell(label14);
- Labellabel4=newLabel(3,i+1,list.get(i).getIp());
- ws.addCell(label4);
- for(intj=0;j<lenght;j++){
- JSONObjectjsonObject=JSONObject.fromObject(jsonlist.getString(j));
- Labellabel15=newLabel(j+4,0,jsonObject.getString("name"),wcf);
- ws.setColumnView(j+4,20);
- ws.addCell(label15);
- Labellabel=newLabel(j+4,i+1,jsonObject.getString("value"));
- ws.addCell(label);
- }
- }
- //写入数据并关闭文件
- //将数据写入所建的excel
- book.write();
- book.close();
- os.close();
- response.flushBuffer();
- }
- returnnull;
- }