一个简单的jxl文件上传功能

众说周知，java导出excel表格到EXCEL的方式有两种。其一是利用第三方插件JXL实现excel文件的生成，另一种方式则是不需要第三方的插件，直接通过jsp页面的设置和action层的response跳转完成excel文件的生成。综合来讲，采用第三方插件不仅能够满足功能性需求，而且还提供函数、字体、颜色及其他方面的接口，如果直接采用jsp跳转形式，则样式会略显低调，但是其实现形式很容易理解，无需了解更多的技术层面的东西。 那么我下面介绍的就是使用jxl实现文件的导出功能。 首先是从前端传值过来！前端的大多大同小异。我就不介绍了。 function exportExcel(){ var xmdw_input = KaTeX parse error: Expected 'EOF', got '#' at position 3: ('#̲xmdw_input').va…(’#start_input’).val(); var end_input=$(’#end_input’).val(); var params = “?start_input=”+start_input+ “&end_input=”+end_input+ “&xmdw_input=”+xmdw_input; window.location.href="/ADXMGL/zhwy/zhwy-xmgl-manage!exportXmtj.action"+params; } 下面就是后端的代码。 public String exportXmtj() throws Exception { try { String xmdw = getParameter(“xmdw_input”); String start = getParameter(“start_input”); String end = getParameter(“end_input”);

PropertyFilter dwPf = new PropertyFilter("xmdw:LIKE_S", xmdw); PropertyFilter startPf = new PropertyFilter("RKSJ:GE_D", start); PropertyFilter endPf = new PropertyFilter("RKSJ:LE_D", end); List<PropertyFilter> pfList = new ArrayList<PropertyFilter>(); pfList.add(dwPf); pfList.add(startPf); pfList.add(endPf); //List<PropertyFilter> pfList = initParam(); List<String> colList = new ArrayList<String>(); //colList.add("guId"); int startPage=this.page.getPageNo()*this.page.getPageSize()+1; int endPage=(this.page.getPageNo()-1)*this.page.getPageSize(); colList.add("xmdw"); colList.add("xmsl"); colList.add("cbzj"); colList.add("dwzj"); colList.add("ml"); colList.add("mll"); List<Map<String, Object>> mapList = this.zhwyXmglManageService .findMapLists(colList, pfList); exportXmtj(mapList); } catch (Exception e) { e.printStackTrace(); } return null; } 然后是导出的时候，创建一个文件。将需要的东西传到这个表格里面去。 public List<PropertyFilter> initParam() { String status = getParameter("status"); String create_start_date = getParameter("create_start_date"); String create_end_date = getParameter("create_end_date"); String flog = getParameter("flog"); UumUser currUser = (UumUser) this.getSessionAttribute(BasicConstant.CURRUSER); List<PropertyFilter> pfList = new ArrayList<PropertyFilter>(); if (create_start_date != null) { pfList.add(new PropertyFilter("rksj:GE_D", create_start_date)); } if (create_end_date != null) { pfList.add(new PropertyFilter("rksj:LE_D", create_end_date)); } return pfList; } public String exportXmtj(List<Map<String, Object>> mapList) throws Exception { WritableFont font1 = new WritableFont(WritableFont.ARIAL, 20, WritableFont.BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat cellFormat1 = new WritableCellFormat(font1); //设置背景颜色; //cellFormat1.setBackground(Colour.BLUE_GREY); //设置边框; //cellFormat1.setBorder(Border.ALL, BorderLineStyle.DASH_DOT); //设置自动换行; cellFormat1.setWrap(true); //设置文字居中对齐方式; cellFormat1.setAlignment(Alignment.CENTRE); //设置垂直居中; cellFormat1.setVerticalAlignment(VerticalAlignment.CENTRE); WritableFont font2 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.NO_BOLD, false, UnderlineStyle.NO_UNDERLINE, Colour.BLACK); WritableCellFormat cellFormat2 = new WritableCellFormat(font2); cellFormat2.setAlignment(Alignment.CENTRE); cellFormat2.setVerticalAlignment(VerticalAlignment.CENTRE); cellFormat2.setWrap(true); SimpleDateFormat df = new SimpleDateFormat("yyyyMMddHHmmss");// 设置日期格式 //String name=df.format(new Date()) + ".xls"; String path = this.getRealRoot() + "template\\price-v3" + new Date().getTime() + ".xls"; WritableWorkbook book = null; File template = new File(path); book = Workbook.createWorkbook(template); // 设置表名 WritableSheet sheet = book.createSheet("表格", 0); // WritableSheet sheet= new WritableSheet(); sheet.setColumnView(0, 20); for (int i = 0; i < 6; i++) { sheet.setColumnView(i, 20); } // 生成表格表头信息 Label labe0 = new Label(0, 0, "项目统计", cellFormat1); Label labe1 = new Label(0, 1, "项目单位", cellFormat2); Label labe2 = new Label(1, 1, "项目数量", cellFormat2); Label labe3 = new Label(2, 1, "成本总价", cellFormat2); Label labe4 = new Label(3, 1, "对外总价", cellFormat2); Label labe5 = new Label(4, 1, "毛利", cellFormat2); Label labe6 = new Label(5, 1, "毛利率", cellFormat2); Label labe7 = new Label(0, mapList.size()+2,"-合计-", cellFormat2); //遍历表头信息 sheet.mergeCells(0, 0, 5, 0); sheet.addCell(labe0); sheet.addCell(labe1); sheet.addCell(labe2); sheet.addCell(labe3); sheet.addCell(labe4); sheet.addCell(labe5); sheet.addCell(labe6); sheet.addCell(labe7); int i = 2; //求和 for (int j=0;j<mapList.size();j++) { labe1 = new Label(0, i, (mapList.get(j).get("xmdw")!= null ?(mapList.get(j).get("xmdw").toString()): "") + "", cellFormat2); labe2 = new Label(5, i, (mapList.get(j).get("mll")!= null ?(mapList.get(j).get("mll").toString().trim()): "") + "", cellFormat2); sheet.addCell(labe1); sheet.addCell(labe2); if(mapList.get(j).get("xmsl")!=null) sheet.addCell(setNumberCell(1, i,mapList.get(j).get("xmsl").toString(), cellFormat2)); if(mapList.get(j).get("cbzj")!=null) sheet.addCell(setNumberCell(2, i,mapList.get(j).get("cbzj").toString(), cellFormat2)); if(mapList.get(j).get("dwzj")!=null) sheet.addCell(setNumberCell(3, i,mapList.get(j).get("dwzj").toString(), cellFormat2)); if(mapList.get(j).get("ml")!=null) sheet.addCell(setNumberCell(4, i,mapList.get(j).get("ml").toString(), cellFormat2)); i++; } //计算 sheet.addCell(new Formula(1,i,"SUM(b3:b"+(mapList.size()+2)+")",cellFormat2)); sheet.addCell(new Formula(2,i,"SUM(C3:C"+(mapList.size()+2)+")",cellFormat2)); sheet.addCell(new Formula(3,i,"SUM(d3:d"+(mapList.size()+2)+")",cellFormat2)); sheet.addCell(new Formula(4,i,"SUM(e3:e"+(mapList.size()+2)+")",cellFormat2)); DisplayFormat displayFormat = NumberFormats.PERCENT_FLOAT; WritableCellFormat wcfF = new WritableCellFormat(displayFormat); wcfF.setAlignment(Alignment.CENTRE); wcfF.setVerticalAlignment(VerticalAlignment.CENTRE); String formula = "e"+(mapList.size()+3)+"/"+"d"+((mapList.size()+3)); sheet.addCell(new Formula(5,i,formula, wcfF)); //当到达数据的最后一行时，新增一行数据 book.write(); book.close(); // path是指欲下载的文件的路径。 File file = new File(path);// 取得文件名。 InputStream fis = new BufferedInputStream(new FileInputStream(path)); byte[] buffer = new byte[fis.available()]; fis.read(buffer); fis.close();// 清空response response.reset();// 设置response的Header String fileName = "项目统计" + df.format(new Date()); fileName = URLEncoder.encode(fileName, "utf-8"); this.response.setHeader("Pragma", "public"); this.response.setHeader("Cache-Control", "must-revalidate, post-check=0, pre-check=0"); response.addHeader("Content-Disposition", "attachment;filename=" + fileName + ".xls"); response.addHeader("Content-Length", "" + file.length()); response.setContentType("application/octet-stream"); OutputStream toClient = new BufferedOutputStream(response.getOutputStream()); toClient.write(buffer); toClient.flush(); toClient.close(); template.delete(); return null; }

最后是接口： public abstract List<Map<String, Object>> findMapLists(List columnNams, List paramList); 可以参照学习一下。