jxl实现导出excel

jxl在web项目中如何实现导出excel处理

 

1.固定模板导出的处理，具体excel模板如下

 

 

struts2中的对应action 方法代码。

/** * 生成个人信息excel的action * @return */ public String printInformgr(){ //以下变量名按照具体模板中对应的（列数行数名命名，具体插入注意行数列数都从0开始的） inform=(Inform)this.publicDao.getObjectByHsql("from Inform where id='"+id+"'"); //查询信息 List datalist=this.publicDao.findObjectListByHsql("from DataDiy "); //得到字典信息 Map<String,String> dayMap= new HashMap<String,String>(); for(int i=0; i<datalist.size();i++){ dataDiy=(DataDiy)datalist.get(i); dayMap.put(dataDiy.getDataCode(), dataDiy.getDataName()); } response.setContentType("application/msexcel"); String workSheetName ="xxxx登记表"; String dirpath = ServletActionContext.getServletContext().getRealPath("/"); String xlspath=dirpath+"template/informport_gr.xls"; //模板绝对路径 try { workSheetName = new String(workSheetName.getBytes(), "ISO-8859-1"); response.setHeader("Content-disposition", "attachment; filename=" + workSheetName + ".xls"); Workbook wb = Workbook.getWorkbook(new File(xlspath)); //第二步：通过模板得到一个可写的Workbook：第一个参数是一个输出流对象,第二个参数代表了要读取的模板 WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream(),wb); //第三步：选择模板中名称为StateResult的Sheet： WritableSheet sheet = book.getSheet("登记表个人"); // 如果需要也可以创建Sheet // WritableSheet sheet = book.createSheet("Sheet名称",1); //第四步：选择单元格，写入动态值，根据单元格的不同类型转换成相应类型的单元格：//(列，行) Label A2 = (Label)sheet.getWritableCell(0,1); A2.setString("编号: 时间："+inform.getSubmitTime()+""); WritableFont font2= new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.BOLD); WritableCellFormat cellFormat2 = new WritableCellFormat(font2); cellFormat2.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border //cellFormat2.setBackground(Colour.BLUE);//设置单元格背景颜色为天蓝色 cellFormat2.setAlignment(jxl.format.Alignment.RIGHT);//设置文本对其方式，设置为右对其方式 A2.setCellFormat(cellFormat2); //数字类型设置为统一样式 jxl.write.NumberFormat format = new jxl.write.NumberFormat("#"); //替代数字类型中#号 // jxl.write.WritableCellFormat wcf = new jxl.write.WritableCellFormat(format); //文字类型设置为统一样式 WritableFont fonte= new WritableFont(WritableFont.createFont("宋体"),12,WritableFont.NO_BOLD); WritableCellFormat cellFormat= new WritableCellFormat(fonte,format); cellFormat.setBorder(Border.ALL, jxl.format.BorderLineStyle.THIN); //Border是jxl.format.Border cellFormat.setAlignment(jxl.format.Alignment.CENTRE);//设置文本对其方式，设置填充内容为中间对齐 cellFormat.setVerticalAlignment(VerticalAlignment.CENTRE); cellFormat.setWrap(true); //例如：格式化数字 //jxl.write.Number C4 = new jxl.write.Number (2,3,3,wcf); //sheet.addCell(C4); //C4.setCellFormat(cellFormat); //举报人姓名 Label B3 = new Label(1, 2,inform.getFmenName()!=null?inform.getFmenName():"", cellFormat); sheet.addCell(B3); //要举报单位或个人 Label D3 = new Label(3, 2,inform.getInformType()==1?"〇单位 ⊙个人":"⊙单位 〇个人", cellFormat); sheet.addCell(D3); //单位及职务 String dwhzw=(inform.getFunit()!=null?inform.getFunit():"")+" "+(inform.getFposition()!=null?inform.getFposition():""); Label B4 = new Label(1, 3,dwhzw, cellFormat); sheet.addCell(B4); //联系电话 String lxdh=(inform.getFmobilephone()!=null?inform.getFmobilephone():"")+" "+(inform.getFfixedtelephone()!=null?inform.getFfixedtelephone():""); Label B5 = new Label(1, 4,lxdh, cellFormat); sheet.addCell(B5); //E-mail Label D5 = new Label(3, 4,inform.getFemail()!=null?inform.getFemail():"", cellFormat); sheet.addCell(D5); //联系地址 Label B6 = new Label(1, 5,inform.getFcontact()!=null?inform.getFcontact():"", cellFormat); sheet.addCell(B6); //被举报人姓名 Label B7 = new Label(1, 6,inform.getTmenName(), cellFormat); sheet.addCell(B7); //被举报人所在地区 String bjbrszdq=""; if(dayMap.get(inform.getTarea())!=null){ bjbrszdq=dayMap.get(inform.getTarea()); } Label B8 = new Label(1, 7,bjbrszdq, cellFormat); sheet.addCell(B8); //所在单位 Label D8 = new Label(3, 7,inform.getTunit(), cellFormat); sheet.addCell(D8); //职务 Label B9 = new Label(1, 8,inform.getTposition(), cellFormat); sheet.addCell(B9); //政治面貌 String bjbrzzmm=""; if(inform.getTpolitical()!=null && !"1".equals(inform.getTpolitical()) && !"0".equals(inform.getTpolitical()) && dayMap.get(inform.getTpolitical())!=null){ bjbrzzmm=dayMap.get(inform.getTpolitical()); } Label D9 = new Label(3, 8,bjbrzzmm, cellFormat); sheet.addCell(D9); //职级 String bjbrzj=""; if(dayMap.get(inform.getTlevel())!=null){ bjbrzj=dayMap.get(inform.getTlevel()); } Label B10 = new Label(1, 9,bjbrzj, cellFormat); sheet.addCell(B10); //举报类型 String jblx=""; if(dayMap.get(inform.getProblemType())!=null){ jblx=dayMap.get(inform.getProblemType()); } Label B11 = new Label(1, 10,jblx, cellFormat); sheet.addCell(B11); //主要问题 Label B12 = new Label(1, 11,inform.getMainProblems(), cellFormat); sheet.addCell(B12); book.write(); book.close(); return null; } catch (Exception e) { // TODO: handle exception e.printStackTrace(); return "error"; } }

 

 

2.按照相关动态列表方式

 

 

 

struts2中的对应action 方法代码。

/** * 导出统计查询 * @return * @throws Exception */ public String dcinformlist() { try { response.setContentType("application/msexcel"); String workSheetName ="情况汇总表"; workSheetName = new String(workSheetName.getBytes(), "ISO-8859-1"); response.setHeader("Content-disposition", "attachment; filename=" + workSheetName + ".xls"); WritableWorkbook book = Workbook.createWorkbook(response.getOutputStream()); WritableSheet sheet = book.createSheet("情况汇总表", 0); sheet.setRowView(0, 1000, false); sheet.mergeCells(0,0,6,0); WritableFont wf_merge = new WritableFont(WritableFont.ARIAL, 24,WritableFont.BOLD, false,jxl.format.UnderlineStyle.NO_UNDERLINE,jxl.format.Colour.BLACK); WritableCellFormat wff_merge = new WritableCellFormat(wf_merge); wff_merge.setVerticalAlignment(VerticalAlignment.CENTRE); wff_merge.setAlignment(Alignment.CENTRE); sheet.addCell(new Label(0,0,"12380简要情况汇总表",wff_merge)); WritableFont wfont = new WritableFont(WritableFont.ARIAL, 15, WritableFont.BOLD, false, jxl.format.UnderlineStyle.NO_UNDERLINE, jxl.format.Colour.BLACK); WritableCellFormat titleFormat = new WritableCellFormat(wfont); titleFormat.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); titleFormat.setVerticalAlignment(VerticalAlignment.CENTRE); titleFormat.setAlignment(Alignment.CENTRE); titleFormat.setWrap(true); String[] stitle = { "时间", "举报人姓名","举报类型", "被举报人姓名或被举报单位名称", "所在地区", "被举报人所在单位", "被举报人职务" }; for (int i = 0; i < stitle.length; i++) { Label excelTitle = new Label(i, 1, stitle[i], titleFormat); sheet.addCell(excelTitle); } sheet.setColumnView(0 , 15); sheet.setColumnView(1 , 20); sheet.setColumnView(2 , 15); sheet.setColumnView(3 , 50); sheet.setColumnView(4 , 20); sheet.setColumnView(5 , 30); sheet.setColumnView(6 , 20); WritableFont content = new WritableFont(WritableFont.ARIAL, 8,WritableFont.NO_BOLD, false); WritableCellFormat forcontent = new WritableCellFormat(content); forcontent.setBorder(jxl.format.Border.ALL,jxl.format.BorderLineStyle.THIN); forcontent.setVerticalAlignment(VerticalAlignment.CENTRE); forcontent.setAlignment(Alignment.CENTRE); forcontent.setWrap(true); List lis=(List)session.getAttribute("viewlist"); for(int i=0;i<lis.size();i++){ Inform entry= (Inform)lis.get(i); String acceptanceTime = ""; if(null != entry.getAcceptanceTime()) acceptanceTime = entry.getAcceptanceTime(); String fmenName = ""; if(null != entry.getFmenName()) fmenName= entry.getFmenName(); String InformType = ""; if(1 == entry.getInformType()) InformType = "个人举报"; else InformType = "单位举报"; String tmenName = ""; if(1 == entry.getInformType()) tmenName = entry.getTmenName(); else tmenName = entry.getDunitName(); String tarea = ""; if(null != entry.getTarea()) tarea = entry.getTarea(); String tunit = ""; if(null != entry.getTunit()) tunit = entry.getTunit(); String tposition = ""; if(null != entry.getTposition()) tposition = entry.getTposition(); Label enumbber = new Label(0, i+2,acceptanceTime, forcontent); Label etitle = new Label(1, i+2,fmenName, forcontent); Label unitname = new Label(2, i+2,InformType , forcontent); Label eprint = new Label(3, i+2,tmenName , forcontent); Label etime = new Label(4, i+2,tarea, forcontent); Label euser = new Label(5, i+2,tunit ,forcontent); Label er = new Label(6, i+2,tposition ,forcontent); sheet.addCell(enumbber); sheet.addCell(etitle); sheet.addCell(unitname); sheet.addCell(eprint); sheet.addCell(etime); sheet.addCell(euser); sheet.addCell(er); } book.write(); book.close(); return null; } catch (Exception e) { e.printStackTrace(); return "error"; } }

 

 

3.关于下拉框

Label lblColumn = null; WritableSheet ws = wb.createSheet("标签", 0); lblColumn = new Label(12, nRow, "请选择", fmtCenterCaption); WritableCellFeatures wcf = new WritableCellFeatures(); List angerlist = new ArrayList(); angerlist.add("电话"); angerlist.add("手机"); angerlist.add("呼机"); wcf.setDataValidationList(angerlist); lblColumn.setCellFeatures(wcf); ws.addCell(lblColumn);

 

4.关于excel中函数的使用

做了一点实验，大体下面三种情况

第一  读模板的excel生成，模板中无函数设置

这种情况 要使用 函数格式就可以了

Formula C5=new Formula(1,2,"SUM(C6:C21)",cellFormat);    
sheet.addCell(C5);

 

 

第二 读模板的excel生成，模板中有函数设置

这种情况只要放入基本值后，excel就会生成对应值，不用做后台处理，但是格式款显示的格式内容会有变化

如原来=SUM(F6:F21) 会变成  =SUM($F$6:$F$21)

 

如果使用

Formula C6=new Formula(2,4,"SUM(C7:C21)",cellFormat);    
 sheet.addCell(C6);

Formula C5=new Formula(1,2,"SUM(C6:C21)",cellFormat);    
sheet.addCell(C5);

但是我用了下出现有问题，比如第二次用函数求一个单元格中的数据是第一次调用函数的结果时，会出现计算问题。而且有合并单元格

的时候会出现重复导入数据的异常。

 

第三种情况，自己生成excel,这种方式没有怎么试过，

估计仍然会出现第二种情况的问题。