项目中用到了报表导出功能,没有使用组件方法,直接从后台查出数据,用jxl直接写,现在做一下笔记:
首先,在jsp页面中,按钮的代码是这样的:
- οnclick=”downloadOrderMgr();
对应的js方法:
然后,我们来到对应的方法中:
if (StringUtils.isBlank(method)) {
model = “/model/sop/order/orderMgrQuery.jsp”;
PageBean pageBean = sopBusiness.selectSopOrderListForPage(bo, pageNo, pageSize);
view.addObject(“orderList”, pageBean);
} else {
List vendOrderBoList = sopBusiness.selectSopOrderList(bo);
if (method.equals(“download”)) {
WritableWorkbook book = null;
try {
String filename = “订单查看”;ServletOutputStream servletoutputstream = response.getOutputStream(); book = Workbook.createWorkbook(servletoutputstream); // 生成名为"sheet1"的工作表,参数0表示这是第一页 WritableSheet sheet = book.createSheet("sheet1", 0); CellView cellView = new CellView(); cellView.setAutosize(true); // 设置自动大小 // 设置单元格的样式 WritableCellFormat fileNamecellFormat = new WritableCellFormat(); // 设置水平居中 fileNamecellFormat.setAlignment(jxl.format.Alignment.CENTRE); // 设置垂直居中 fileNamecellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 设置显示的字体样式,字体,字号,是否粗体,字体颜色 fileNamecellFormat.setFont(new WritableFont(WritableFont.ARIAL, 15)); sheet.addCell(new Label(0, 0, filename, fileNamecellFormat)); // 设置要合并单元格的下标 sheet.mergeCells(0, 0, columnName.length - 1, 0); // 设置单元格的样式 WritableCellFormat titleCellFormat = new WritableCellFormat(); // 设置水平居中 titleCellFormat.setAlignment(jxl.format.Alignment.CENTRE); // 设置垂直居中 titleCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 设置显示的字体样式,字体,字号,是否粗体,字体颜色 titleCellFormat.setFont(new WritableFont(WritableFont.ARIAL, 14)); if (columnName != null && columnName.length-1 > 0) { for (int i = 0; i < columnName.length-1; i++) { sheet.addCell(new Label(i, 1, columnName[i], titleCellFormat)); sheet.setColumnView(i, cellView);// 根据内容自动设置列宽 } } // 设置单元格的样式 WritableCellFormat centerCellFormat = new WritableCellFormat(); // 设置水平居中 centerCellFormat.setAlignment(jxl.format.Alignment.CENTRE); // 设置垂直居中 centerCellFormat.setVerticalAlignment(jxl.format.VerticalAlignment.CENTRE); // 设置显示的字体样式,字体,字号,是否粗体,字体颜色 centerCellFormat.setFont(new WritableFont(WritableFont.ARIAL, 13)); // 设置单元格的样式 WritableCellFormat cellFormat = new WritableCellFormat(); // 设置显示的字体样式,字体,字号,是否粗体,字体颜色 cellFormat.setFont(new WritableFont(WritableFont.createFont("Arial"), 13)); if (vendOrderBoList != null && !vendOrderBoList.isEmpty()) { for (int i = 0; i < vendOrderBoList.size(); i++) { VendOrderBo vendOrderBo = vendOrderBoList.get(i); sheet.addCell(new Label(0, i + 2, vendOrderBo.getCoNum(), cellFormat)); sheet.addCell(new Label(1, i + 2, vendOrderBo.getOrderTypeName(), cellFormat)); sheet.addCell(new Label(2, i + 2, vendOrderBo.getCustName(), cellFormat)); sheet.addCell(new Label(3, i + 2, vendOrderBo.getNickName(), cellFormat)); sheet.addCell(new Label(4, i + 2, vendOrderBo.getOrderAmt(), cellFormat)); double AbledAmout = Double.valueOf(vendOrderBo.getAbledAmout()); sheet.addCell(new Number(5, i + 2, AbledAmout, cellFormat)); sheet.addCell(new Label(6, i + 2, vendOrderBo.getUnAbleAmout(), cellFormat)); sheet.addCell(new Label(7, i + 2, vendOrderBo.getPayTypeName(), cellFormat)); sheet.addCell(new Label(8, i + 2, vendOrderBo.getStatusName(), cellFormat)); sheet.addCell(new Label(9, i + 2, vendOrderBo.getCrtTime(), cellFormat)); } } book.write(); filename = new String(URLDecoder.decode("查看订单", "UTF-8").getBytes("UTF-8"), "ISO-8859-1"); response.setHeader("Content-disposition", new StringBuilder("attachment; filename=").append(filename).append(".xls").toString()); response.setDateHeader("Expires", 0L); response.setContentType("application/vnd.ms-excel;charset=utf-8"); servletoutputstream.flush(); } catch (Exception e) { e.printStackTrace(); } finally { if (book != null) { try { book.close(); } catch (Exception e) { e.printStackTrace(); } } } return null; }
这段代码中,前部分是设置了表格标题部分格式,数据从form表单中隐藏域获得;后面是设置了正文的格式,数据是从后台中获取:
List vendOrderBoList = sopBusiness.selectSopOrderList(bo); 这个bo就是我们定义的实体类,其中包括了需要查找的所有信息,也包括起止页,查询条件;
该方法是自定义的,就是从后台查数据,这个不同框架有不同框架方法,但是得到的结果是一样的,就是根据当前查询条件查出来的数据;
后面是给浏览器写的消息头,固定写法;
最后return null,是因为我们是在ModelAndView方法中写的,我们只需要导出表格,不需要返回任何页面,所以就return null;
后来测试发现多次点击下载后会有标题重复现象,所以js中的代码改为: