POI导入
@RequestMapping("/user/importExcel") // slat = username + uuid; // 将用户名作为盐值 |
POI导出
@RequestMapping("/user/exportExcel") @ResponseBody public void exportExcel(HttpServletRequest request, HttpServletResponse response) throws Exception { response.setContentType("application/x-execl"); response.setHeader("Content-Disposition", "attachment;filename=" + new String("用户列表.xlsx".getBytes(), "ISO-8859-1")); //创建一个空的工作簿 Workbook wb = new XSSFWorkbook(); //给工作簿创建一个工作表(sheet) Sheet sheet = wb.createSheet("人员信息"); //处理表头 //1.合并单元格 CellRangeAddress rangeCell = new CellRangeAddress(0, 0, 0, 4); //将合并单元格赋予工作表 sheet.addMergedRegion(rangeCell); CellStyle headerStyle = wb.createCellStyle(); headerStyle.setAlignment(HorizontalAlignment.CENTER); headerStyle.setVerticalAlignment(VerticalAlignment.CENTER); Font headerFont = wb.createFont(); headerFont.setBold(true); headerFont.setFontHeightInPoints((short)20); headerStyle.setFont(headerFont); //获取第一行的合并单元格 Cell head = sheet.createRow(0).createCell(0); head.setCellValue("人员信息"); head.setCellStyle(headerStyle); //封装表头 String[] titles = {"用户ID","用户名","昵称","生日","性别"}; Row titleRow = sheet.createRow(1); for(int i=0;i<titles.length;i++) { titleRow.createCell(i).setCellValue(titles[i]); } SimpleDateFormat sdf = new SimpleDateFormat("yyyy/MM/dd"); //查询表中数据 List<UserInfo> list = authService.findAllUser(); for (int j=0;j<list.size();j++) { UserInfo userInfo = list.get(j); Row row = sheet.createRow(j+2); row.createCell(0).setCellValue(userInfo.getUid()); row.createCell(1).setCellValue(userInfo.getUsername()); row.createCell(2).setCellValue(userInfo.getName()); row.createCell(3).setCellValue(sdf.format(new Date())); row.createCell(4).setCellValue(1); } ServletOutputStream os = response.getOutputStream(); wb.write(os); wb.close(); } |