效果图:
1. 页面
<input type="button" value="导出" class="s_button" onclick="doExportExcel()"/>
//导出用户列表 function doExportExcel(){ window.open("${basePath}nsfw/user_exportExcel.action"); }
2.跳到指定的action中
//导出用户列表 public void exportExcel(){//弹出下载框,无需跳转到jsp页面 try { //1.查找用户列表 --数据 userList = userService.findObjects(); //2.导出 HttpServletResponse response = ServletActionContext.getResponse(); response.setContentType("application/x-excel");//以excel输出 //设置以附件的形式下载 并兼容多个浏览器 response.setHeader("Content-Disposition", "attachment;filename="+ new String("用户列表.xls".getBytes(), "ISO-8859-1")); ServletOutputStream outputStream = response.getOutputStream(); userService.exportExcel(userList,outputStream); if(outputStream !=null){ outputStream.close(); } } catch (Exception e) { e.printStackTrace(); } }
3.service层
@Override public void exportExcel(List<User> userList,ServletOutputStream outputStream){ ExcelUtil.exportExcel(userList, outputStream); }
4.导出列表
public static void exportExcel(List<User> userList, ServletOutputStream outputStream) { try { //1、创建工作簿 HSSFWorkbook workbook = new HSSFWorkbook(); //1.1、创建合并单元格对象 CellRangeAddress cellRangeAddress = new CellRangeAddress(0, 0, 0, 4);//起始行号，结束行号，起始列号，结束列号 //1.2、头标题样式 HSSFCellStyle style1 = createCellStyle(workbook, (short)16); //1.3、列标题样式 HSSFCellStyle style2 = createCellStyle(workbook, (short)13); //2、创建工作表 HSSFSheet sheet = workbook.createSheet("用户列表"); //2.1、加载合并单元格对象 sheet.addMergedRegion(cellRangeAddress); //设置默认列宽 sheet.setDefaultColumnWidth(20); //3、创建行 //3.1、创建头标题行；并且设置头标题 HSSFRow row1 = sheet.createRow(0); HSSFCell cell1 = row1.createCell(0); //加载单元格样式 cell1.setCellStyle(style1); cell1.setCellValue("用户列表"); //3.2、创建列标题行；并且设置列标题 HSSFRow row2 = sheet.createRow(1); String[] titles = {"用户名","帐号", "所属部门", "性别", "电子邮箱"}; for(int i = 0; i < titles.length; i++){ HSSFCell cell2 = row2.createCell(i); //加载单元格样式 cell2.setCellStyle(style2); cell2.setCellValue(titles[i]); } //4、操作单元格；将用户列表写入excel if(userList != null){ for(int j = 0; j < userList.size(); j++){ HSSFRow row = sheet.createRow(j+2); HSSFCell cell11 = row.createCell(0); cell11.setCellValue(userList.get(j).getName()); HSSFCell cell12 = row.createCell(1); cell12.setCellValue(userList.get(j).getAccount()); HSSFCell cell13 = row.createCell(2); cell13.setCellValue(userList.get(j).getDept()); HSSFCell cell14 = row.createCell(3); cell14.setCellValue(userList.get(j).isGender()?"男":"女"); HSSFCell cell15 = row.createCell(4); cell15.setCellValue(userList.get(j).getEmail()); } } //5、输出 workbook.write(outputStream); workbook.close(); } catch (Exception e) { e.printStackTrace(); } } /** * 创建单元格样式 * @param workbook 工作簿 * @param fontSize 字体大小 * @return 单元格样式 */ private static HSSFCellStyle createCellStyle(HSSFWorkbook workbook, short fontSize) { HSSFCellStyle style = workbook.createCellStyle(); style.setAlignment(HSSFCellStyle.ALIGN_CENTER);//水平居中 style.setVerticalAlignment(HSSFCellStyle.VERTICAL_CENTER);//垂直居中 //创建字体 HSSFFont font = workbook.createFont(); font.setBoldweight(HSSFFont.BOLDWEIGHT_BOLD);//加粗字体 font.setFontHeightInPoints(fontSize); //加载字体 style.setFont(font); return style; }
二,导入用户列表
1.
//导入用户列表 同上传头像 function doImportExcel(){ document.forms[0].action = "${basePath}nsfw/user_importExcel.action"; document.forms[0].submit(); }
2.
//导入用户列表 public String importExcel(){ if(userExcel !=null){//如果获取到文件 if(userExcelFileName.matches("^.+\.(?i)((xls)|(xlsx))$")){ //第一个点代表任意字符 ,\. 代表点因为.和是特殊字符需要转意,?i不区分大小写 userService.importExcel(userExcel,userExcelFileName); } } return "list";//重定向到listUI界面 }
3.
@Override//所谓的导入用户列表 其实就是把excel 每一行中的用户信息保存的数据库中user表中 public void importExcel(File userExcel, String userExcelFileName) { try { FileInputStream fileInputStream = new FileInputStream(userExcel); boolean is03Excel = userExcelFileName.matches("^.+\.(?i)(xls)$"); //1、读取工作簿 Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream); //2、读取工作表 Sheet sheet = workbook.getSheetAt(0); //3、读取行 if(sheet.getPhysicalNumberOfRows() > 2){ User user = null; for(int k = 2; k < sheet.getPhysicalNumberOfRows(); k++){ //4、读取单元格 Row row = sheet.getRow(k); user = new User(); //用户名 Cell cell0 = row.getCell(0); user.setName(cell0.getStringCellValue()); //帐号 Cell cell1 = row.getCell(1); user.setAccount(cell1.getStringCellValue()); //所属部门 Cell cell2 = row.getCell(2); user.setDept(cell2.getStringCellValue()); //性别 Cell cell3 = row.getCell(3); user.setGender(cell3.getStringCellValue().equals("男")); //手机号 excel输入有可能是数字或字符串 String mobile = ""; Cell cell4 = row.getCell(4); try { mobile = cell4.getStringCellValue(); } catch (Exception e) { double dMobile = cell4.getNumericCellValue();//拿到的是double 科学计数法 mobile = BigDecimal.valueOf(dMobile).toString(); } user.setMobile(mobile); //电子邮箱 Cell cell5 = row.getCell(5); user.setEmail(cell5.getStringCellValue()); //生日 Cell cell6 = row.getCell(6); if(cell6.getDateCellValue() != null){ //如果用户输入了生日才设置到user表中 user.setBirthday(cell6.getDateCellValue()); } //默认用户密码为 123456 user.setPassword("123456"); //默认用户状态为 有效 user.setState(User.USER_STATE_VALID); //5、保存用户 save(user); } } workbook.close(); fileInputStream.close(); } catch (Exception e) { e.printStackTrace(); } }