POI导入导出
常见的用户导入导出:
1.导出:
action层:
1 //导出用户列表
1 @Resource 2 private UserService userService; 3 private List<User> userList; 4 private User user; 5 private String[] selectedRow; 6 7 private File userExcel; 8 private String userExcelContentType; 9 private String userExcelFileName;
2 public void exportExcel(){ 3 try { 4 //1、查找用户列表 5 userList = userService.findObjects(); 6 //2、导出 7 HttpServletResponse response = ServletActionContext.getResponse(); 8 response.setContentType("application/x-execl"); 9 response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xls".getBytes(), "ISO-8859-1")); 10 ServletOutputStream outputStream = response.getOutputStream(); 11 userService.exportExcel(userList, outputStream); 12 if(outputStream != null){ 13 outputStream.close(); 14 } 15 } catch (Exception e) { 16 e.printStackTrace(); 17 } 18 }
service层:
@Override public 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(25); //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; } }
2.导入:
action层:
1 //导入用户列表 2 public String importExcel(){ 3 //1、获取excel文件 4 if(userExcel != null){ 5 //是否是excel 6 if(userExcelFileName.matches("^.+\\.(?i)((xls)|(xlsx))$")){ 7 //2、导入 8 userService.importExcel(userExcel, userExcelFileName); 9 } 10 } 11 return "list"; 12 }
service层:
1 @Override 2 public void importExcel(File userExcel, String userExcelFileName) { 3 try { 4 FileInputStream fileInputStream = new FileInputStream(userExcel); 5 boolean is03Excel = userExcelFileName.matches("^.+\\.(?i)(xls)$"); 6 //1、读取工作簿 7 Workbook workbook = is03Excel ? new HSSFWorkbook(fileInputStream):new XSSFWorkbook(fileInputStream); 8 //2、读取工作表 9 Sheet sheet = workbook.getSheetAt(0); 10 //3、读取行 11 if(sheet.getPhysicalNumberOfRows() > 2){ 12 User user = null; 13 for(int k = 2; k < sheet.getPhysicalNumberOfRows(); k++){ 14 //4、读取单元格 15 Row row = sheet.getRow(k); 16 user = new User(); 17 //用户名 18 Cell cell0 = row.getCell(0); 19 user.setName(cell0.getStringCellValue()); 20 //帐号 21 Cell cell1 = row.getCell(1); 22 user.setAccount(cell1.getStringCellValue()); 23 //所属部门 24 Cell cell2 = row.getCell(2); 25 user.setDept(cell2.getStringCellValue()); 26 //性别 27 Cell cell3 = row.getCell(3); 28 user.setGender(cell3.getStringCellValue().equals("男")); 29 //手机号 30 String mobile = ""; 31 Cell cell4 = row.getCell(4); 32 try { 33 mobile = cell4.getStringCellValue(); 34 } catch (Exception e) { 35 double dMobile = cell4.getNumericCellValue(); 36 mobile = BigDecimal.valueOf(dMobile).toString(); 37 } 38 user.setMobile(mobile); 39 40 //电子邮箱 41 Cell cell5 = row.getCell(5); 42 user.setEmail(cell5.getStringCellValue()); 43 //生日 44 Cell cell6 = row.getCell(6); 45 if(cell6.getDateCellValue() != null){ 46 user.setBirthday(cell6.getDateCellValue()); 47 } 48 //默认用户密码为 123456 49 user.setPassword("123456"); 50 //默认用户状态为 有效 51 user.setState(User.USER_STATE_VALID); 52 53 //5、保存用户 54 save(user); 55 } 56 } 57 workbook.close(); 58 fileInputStream.close(); 59 } catch (Exception e) { 60 e.printStackTrace(); 61 } 62 }