一、Java利用POI实现导入导出Excel表格demo
1.引入依赖
|
1 2 3 4 5 |
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> |
2.导入demo
2.1 controller层
|
1 2 3 4 5 6 7 8 |
/** * Excel导入 */ @PostMapping("/import") public Result userImport2(@RequestParam("file") MultipartFile file) throws Exception{ Result result=userService.userImportExcel(file); return result; } |
2.2 service实现类层
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 |
public Result userImportExcel(MultipartFile file){ try { InputStream inputStream = file.getInputStream(); XSSFWorkbook sheets = new XSSFWorkbook(inputStream); //获取表单sheet 第一个 XSSFSheet sheetAt = sheets.getSheetAt(0); //获取第一行 int firstRowNum = sheetAt.getFirstRowNum(); //最后一行 int lastRowNum = sheetAt.getLastRowNum(); //存入数据集合 List<User> users=new ArrayList<>(); //遍历数据 for(int i=firstRowNum+1;i<lastRowNum+1;i++){ XSSFRow row = sheetAt.getRow(i); if(row!=null){ /* //获取第一行的第一列 int firstCellNum = row.getFirstCellNum(); //获取第一行的最后列 short lastCellNum = row.getLastCellNum(); for (int j=firstCellNum;j<lastCellNum+1;j++){ //放入集合中需要可以用这种方法 String cellValue = getValue(row.getCell(firstCellNum)); }*/ //这里我就直接赋值 User user = new User(); user.setUname(row.getCell(0).getStringCellValue()); user.setUpassword(row.getCell(1).getStringCellValue()); user.setUsex(row.getCell(2).getStringCellValue()); user.setRole(row.getCell(3).getStringCellValue()); user.setUlove((int) row.getCell(4).getNumericCellValue()); user.setUphoto(row.getCell(5).getStringCellValue()); user.setUaddress(row.getCell(6).getStringCellValue()); users.add(user); } } //保存数据 saveBatch(users); return Result.success(); }catch (Exception e){ e.printStackTrace(); log.info("error:{}",e); }
return Result.error("300","导入失败"); }
/** * 判断值的类型 */ public String getValue(HSSFCell cell) {
if(cell==null){ return ""; } String cellValue= ""; try { DecimalFormat df=new DecimalFormat("0.00"); if(cell.getCellType()== CellType.NUMERIC){ //日期时间转换 if(HSSFDateUtil.isCellDateFormatted(cell)){ cellValue=DateFormatUtils.format(cell.getDateCellValue(),"yyyy-MM-dd"); }else{ NumberFormat instance = NumberFormat.getInstance(); cellValue=instance.format(cell.getNumericCellValue()).replace(",",""); }
}else if(cell.getCellType() == CellType.STRING){ //字符串 cellValue=cell.getStringCellValue(); }else if(cell.getCellType() == CellType.BOOLEAN){ //Boolean cellValue= String.valueOf(cell.getBooleanCellValue()); }else if(cell.getCellType() == CellType.ERROR){ //错误 }else if(cell.getCellType() == CellType.FORMULA){ //转换公式 保留两位 cellValue=df.format(cell.getNumericCellValue()); }else{ cellValue=null; }
} catch (Exception e) { e.printStackTrace(); cellValue="-1"; }
return cellValue; } |
3.导出demo
3.1 controller层
|
1 2 3 4 5 6 7 8 9 10 11 |
/** * 导出 * @param response * @return * @throws Exception */ @GetMapping("/export") public Result userExport2(HttpServletResponse response) throws Exception{ Result result=userService.userExportExcel(response); return result; } |
3.2 service实现类
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 |
public Result userExportExcel(HttpServletResponse response) { try { //创建excel XSSFWorkbook sheets = new XSSFWorkbook(); //创建行 XSSFSheet sheet = sheets.createSheet("用户信息"); //格式设置 XSSFCellStyle cellStyle = sheets.createCellStyle(); //横向居中 cellStyle.setAlignment(HorizontalAlignment.CENTER); //创建单元格第一列 XSSFRow row = sheet.createRow(0); //表头 this.titleExcel(row,cellStyle); //查询全部的用户数据 mybatis-plus List<User> list = list(); //遍历设置值 for(int i=0;i<list.size();i++){ XSSFRow rows = sheet.createRow(i+1); User user=list.get(i); //表格里赋值 this.titleExcelValue(user,rows,cellStyle); } //设置浏览器响应格式 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); String filName= URLEncoder.encode("用户信息","UTF-8"); response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");
ServletOutputStream outputStream=response.getOutputStream(); sheets.write(outputStream); outputStream.close(); sheets.close(); return Result.success();
}catch (Exception e){ e.printStackTrace(); log.info("error:{}",e); }
return Result.error("300","导出失败"); }
/** *表格里赋值 **/ public void titleExcelValue(User user, XSSFRow row,XSSFCellStyle cellStyle) { XSSFCell cellId = row.createCell(0); cellId.setCellValue(user.getUid()); cellId.setCellStyle(cellStyle);
XSSFCell cellUserName = row.createCell(1); cellUserName.setCellValue(user.getUname()); cellUserName.setCellStyle(cellStyle);
XSSFCell cellPassword = row.createCell(2); cellPassword.setCellValue(user.getUpassword()); cellPassword.setCellStyle(cellStyle);
XSSFCell cellSex = row.createCell(3); cellSex.setCellValue(user.getUsex()); cellSex.setCellStyle(cellStyle);
XSSFCell cellRole = row.createCell(4); cellRole.setCellValue(user.getRole()); cellRole.setCellStyle(cellStyle);
XSSFCell cellLoveValue = row.createCell(5); cellLoveValue.setCellValue(user.getRole()); cellLoveValue.setCellStyle(cellStyle);
XSSFCell cellPhone = row.createCell(6); cellPhone.setCellValue(user.getUphoto()); cellPhone.setCellStyle(cellStyle);
XSSFCell cellAddress = row.createCell(7); cellAddress.setCellValue(user.getUaddress()); cellAddress.setCellStyle(cellStyle);
} /** 表头 **/ public void titleExcel(XSSFRow row,XSSFCellStyle cellStyle){
XSSFCell cellId = row.createCell(0); cellId.setCellValue("用户ID"); cellId.setCellStyle(cellStyle);
XSSFCell cellUserName = row.createCell(1); cellUserName.setCellValue("用户名"); cellUserName.setCellStyle(cellStyle);
XSSFCell cellPassword = row.createCell(2); cellPassword.setCellValue("密码"); cellPassword.setCellStyle(cellStyle);
XSSFCell cellSex = row.createCell(3); cellSex.setCellValue("性别"); cellSex.setCellStyle(cellStyle);
XSSFCell cellRole = row.createCell(4); cellRole.setCellValue("角色"); cellRole.setCellStyle(cellStyle);
XSSFCell cellLoveValue = row.createCell(5); cellLoveValue.setCellValue("爱心值"); cellLoveValue.setCellStyle(cellStyle);
XSSFCell cellPhone = row.createCell(6); cellPhone.setCellValue("电话号码"); cellPhone.setCellStyle(cellStyle);
XSSFCell cellAddress = row.createCell(7); cellAddress.setCellValue("地址"); cellAddress.setCellStyle(cellStyle);
} |
二、Hutool工具类封装方法导出导入Excel
1.引入依赖
把poi封装到工具类方法里面
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<!-- hutool --> <dependency> <groupId>cn.hutool</groupId> <artifactId>hutool-all</artifactId> <version>5.7.20</version> </dependency>
<dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>4.1.2</version> </dependency> |
2.导入demo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
/** * Excel导入 */ @PostMapping("/import") public Result userImport(@RequestParam("file") MultipartFile file) throws Exception{ System.out.println(file.toString()); //InputStream inputStream = multipartFile.getInputStream(); InputStream inputStream = file.getInputStream(); ExcelReader reader = ExcelUtil.getReader(inputStream); //读取表的内容 List<List<Object>> list = reader.read(1); List<User> users = new ArrayList<>(); for(List<Object> row : list){ User user = new User(); user.setUname(row.get(0).toString()); user.setUpassword(row.get(1).toString()); user.setUsex(row.get(2).toString()); user.setRole(row.get(3).toString()); user.setUlove(Integer.valueOf(row.get(4).toString())); user.setUphoto(row.get(5).toString()); user.setUaddress(row.get(6).toString()); users.add(user); } //批量插入用户信息 mybatis-plus userService.saveBatch(users); return Result.success(); } |
3.导出demo
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 |
/** * Excel导出 方法一 */ @GetMapping("/export") public Result userExport(HttpServletResponse response) throws Exception{ //查询全部的用户数据 List<User> list = userService.list(); //在内存里做操作，保存到浏览器 ExcelWriter writer = ExcelUtil.getWriter(true); //自定义标题别名 writer.addHeaderAlias("uname","用户名"); writer.addHeaderAlias("upassword","密码"); writer.addHeaderAlias("usex","性别"); writer.addHeaderAlias("role","角色"); writer.addHeaderAlias("ulove","爱心值"); writer.addHeaderAlias("uphoto","电话号码"); writer.addHeaderAlias("uaddress","地址"); //一次性写出list内的对象的Excel，使用默认样式，强制输出标题 writer.write(list,true); //设置浏览器响应格式 response.setContentType("application/vnd.openxmlformats-officedocument.spreadsheetml.sheet;charset=utf-8"); String filName= URLEncoder.encode("用户信息","UTF-8"); response.setHeader("Content-Disposition","attachment;filename="+filName+".xls");
ServletOutputStream outputStream=response.getOutputStream(); writer.flush(outputStream,true); outputStream.close(); writer.close(); return Result.success(); } |
