jeesite/jeeplus导入导出

目录

用 [TOC]来生成目录：

jeesitejeeplus导入导出 目录jsp代码块XML代码块Controller代码块

jsp代码块

按数据库中年月查询条件：

年月：

XML代码块

<!--查询年月--> <select id="findYearMonthList" resultType="String"> SELECT DISTINCT ( --数据格式化

– DATE_FORMAT(a.import_year_month, ‘%Y-%m-%d’) DATE_FORMAT(a.import_year_month, ‘%Y-%m’) ) AS yearMonth FROM rfvd_other_duty a WHERE a.del_flag = 0 –必加代码 ORDER BY a.import_year_month DESC

Controller代码块

@RequiresPermissions(“other:produty:list”) @RequestMapping(value = {“list”, “”}) public String list(Produty produty, HttpServletRequest request, HttpServletResponse response, Model model) {

// 获取年月下拉列表的列表值集合 List list = produtyService.findYearMonthList(produty); model.addAttribute(“yearMonthList”, list); SimpleDateFormat formDate; try { if (produty != null && produty.getyearMonth() == null && list != null && list.size() > 0) { formDate = new SimpleDateFormat(“yyyy-MM”); produty.setyearMonth(formDate.parse(list.get(0))); }

// web端获取表头的年月 String yearMonth = “”; formDate = new SimpleDateFormat(“yyyy-MM”); if (produty != null && produty.getyearMonth() != null && list != null && list.size() > 0) { yearMonth = formDate.format(produty.getyearMonth()); } Page page = produtyService.findPage(new Page(request, response), produty); model.addAttribute(“page”, page); model.addAttribute(“yearMonth”, yearMonth); return “modules/other/produtyList”; } catch (ParseException e) { produty.setyearMonth(null); } return “modules/other/produtyList”;

}

/** * 导出excel文件 */ @RequiresPermissions(“other:produty:export”) @RequestMapping(value = “export”, method=RequestMethod.POST) public String exportFile(Produty produty, HttpServletRequest request, HttpServletResponse response, RedirectAttributes redirectAttributes) { try { String fileName = “全省维管责任表”+DateUtils.getDate(“yyyyMMddHHmmss”)+”.xlsx”; Page page = produtyService.findPage(new Page(request, response, -1), produty); //new ExportExcel(“全省维管责任表”, Produty.class).setDataList(page.getList()).write(response, fileName).dispose(); exportExcel(produty,request, response, fileName, page.getList()); return null; } catch (Exception e) { addMessage(redirectAttributes, “导出全省维管责任表记录失败！失败信息：”+e.getMessage()); }

return "redirect:"+Global.getAdminPath()+"/other/produty/?repage"; } //导出设置Excel代码

// public ExportExcel exportExcel(HttpServletRequest request, HttpServletResponse response, String fileName, List list) { public ExportExcel exportExcel(Produty firstProduty, HttpServletRequest request, HttpServletResponse response, String fileName, List list) { try { String yearMonth=”“; SimpleDateFormat formDate = new SimpleDateFormat(“yyyy-MM”); if (firstProduty!=null&&firstProduty.getyearMonth()!=null&&list!=null&&list.size()>0){ yearMonth = formDate.format(firstProduty.getyearMonth()); } // 第一步，创建一个webbook，对应一个Excel文件 SXSSFWorkbook wb = new SXSSFWorkbook(); // 第二步，在webbook中添加一个sheet,对应Excel文件中的sheet Sheet sheet = wb.createSheet(fileName); // 第三步，在sheet中添加表头第0行,注意老版本poi对Excel的行数列数有限制short //Row row = sheet.createRow((int) 0); // 第四步，创建单元格，并设置值表头 设置表头居中 CellStyle style = wb.createCellStyle(); CellStyle styleAndBorder = wb.createCellStyle(); style.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 style.setVerticalAlignment(CellStyle.VERTICAL_CENTER); styleAndBorder.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 styleAndBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font titleFont = wb.createFont(); titleFont.setFontName(“宋体”); // Ziti ziti = titleFont.setFontHeightInPoints((short) 72); titleFont.setFontHeightInPoints((short) 11); titleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); style.setFont(titleFont);//设置字体 styleAndBorder.setFont(titleFont);//设置字体 styleAndBorder.setBorderBottom(CellStyle.BORDER_THIN); //下边框 styleAndBorder.setBorderLeft(CellStyle.BORDER_THIN);//左边框 styleAndBorder.setBorderTop(CellStyle.BORDER_THIN);//上边框 styleAndBorder.setBorderRight(CellStyle.BORDER_THIN);//右边框 styleAndBorder.setWrapText(true);

// 设置标题字体 CellStyle tstyle = wb.createCellStyle(); CellStyle tstyleAndBorder = wb.createCellStyle(); tstyle.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 tstyle.setVerticalAlignment(CellStyle.VERTICAL_CENTER); tstyleAndBorder.setAlignment(CellStyle.ALIGN_CENTER); // 创建一个居中格式 tstyleAndBorder.setVerticalAlignment(CellStyle.VERTICAL_CENTER); Font ttitleFont = wb.createFont(); ttitleFont.setFontName(“宋体”); ttitleFont.setFontHeightInPoints((short) 20); ttitleFont.setBoldweight(Font.BOLDWEIGHT_BOLD); tstyle.setFont(ttitleFont);//设置字体 tstyleAndBorder.setFont(ttitleFont);//设置字体 tstyleAndBorder.setBorderBottom(CellStyle.BORDER_THIN); //下边框 tstyleAndBorder.setBorderLeft(CellStyle.BORDER_THIN);//左边框 tstyleAndBorder.setBorderTop(CellStyle.BORDER_THIN);//上边框 tstyleAndBorder.setBorderRight(CellStyle.BORDER_THIN);//右边框 tstyleAndBorder.setWrapText(true);

//第一行 Row row = sheet.createRow(0); //第二行 row = sheet.createRow(0); row.setHeight((short) (256 * 3)); Integer rowNum = 0; ExportExcelUtils.createCell(sheet, row, 0, yearMonth+ "全省国有人防工程维护管理责任表", tstyle, 0, 0, 0, 6); //第三行 row = sheet.createRow(1); row.setHeight((short) (256 * 3)); ExportExcelUtils.createCell(sheet, row, 0, "序号", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 1, "单位", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 2, "主要领导", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 3, "责任领导", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 4, "维管部门责任人", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 5, "维管直接责任人", styleAndBorder); ExportExcelUtils.createCell(sheet, row, 6, "备注", styleAndBorder); rowNum = 2; //设置表格内容 if (list != null && list.size() > 0) { for (int i = 0; i < list.size(); i++) { Produty produty = list.get(i); row = sheet.createRow(2 + i); //设置为2，在导出的Excel中从第三行开始表格内容 rowNum = 2 + i; row.setHeight((short) (256 * 3)); sheet.setColumnWidth(0, 1700); ExportExcelUtils.createCell(sheet, row, 0, produty.getNo(), styleAndBorder); sheet.setColumnWidth(1, 3800); ExportExcelUtils.createCell(sheet, row, 1, produty.getName(), styleAndBorder); sheet.setColumnWidth(2, 3800); ExportExcelUtils.createCell(sheet, row, 2, produty.getManager(),styleAndBorder); sheet.setColumnWidth(3, 3800); ExportExcelUtils.createCell(sheet, row, 3, produty.getLeader(), styleAndBorder); sheet.setColumnWidth(4, 3800); ExportExcelUtils.createCell(sheet, row, 4, produty.getDeptLeader(),styleAndBorder); sheet.setColumnWidth(5, 3800); ExportExcelUtils.createCell(sheet, row, 5, produty.getDirectLeader(), styleAndBorder); sheet.setColumnWidth(6, 1700); ExportExcelUtils.createCell(sheet, row, 6, produty.getRemarks(), styleAndBorder); ExportExcelUtils.setFileNameInResponse(request, response, fileName, wb); } catch (IOException c) { c.printStackTrace(); } return null; }