36114

Java完成POI的功能

  POM文件      <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter</artifactId> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> </dependency> <!--mysql驱动--> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi-ooxml</artifactId> <version>3.17</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <scope>provided</scope> </dependency> <dependency> <groupId>tk.mybatis</groupId> <artifactId>mapper-spring-boot-starter</artifactId> <version>1.2.3</version> </dependency> <dependency> <groupId>com.alibaba</groupId> <artifactId>fastjson</artifactId> <version>1.2.47</version> </dependency> <dependency> <groupId>net.sourceforge.nekohtml</groupId> <artifactId>nekohtml</artifactId> <version>1.9.15</version> </dependency> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-thymeleaf</artifactId> </dependency> 实体bean@Data @AllArgsConstructor @NoArgsConstructor public class TRole { private Integer id; private String name; private Integer age; } /** *Excel工具类 */ public class ExcelUtil { public static final String FILE_NAME = "用户表"; public static final String EXCEL_XLS = "xls"; public static final String EXCEL_XLSX = "xlsx"; public static boolean isExcelXls(String filePath) { return filePath.matches("^.+\\.(?i)(xls)$"); } public static boolean isExcelXlsx(String filePath) { return filePath.matches("^.+\\.(?i)(xlsx)$"); } /** * 有些单元格为Numeric格式,带有指数E。因此,若想获取其String类型时,需要进行格式转换。 * * @param cell * @return */ public static String getStringFromNumericCell(Cell cell) { return new DecimalFormat("#").format(cell.getNumericCellValue()); } /** * 通过WorkBook对象和sheet对象生成title * * @param wb WorkBook对象 * @param sheet sheet对象 */ public static void createTitle(Workbook wb, Sheet sheet) { //生成表的标题行 Row row = sheet.createRow(0); //设置列宽 sheet.setColumnWidth(1, 12 * 256); sheet.setColumnWidth(3, 17 * 256); //设置风格:字体加粗,居中 CellStyle cellStyle = wb.createCellStyle(); cellStyle.setAlignment(HorizontalAlignment.CENTER); Font font = wb.createFont(); font.setBold(true); cellStyle.setFont(font); //根据对象属性设置单元格标题 Cell cell0 = row.createCell(0); cell0.setCellValue("ID"); cell0.setCellStyle(cellStyle); Cell cell1 = row.createCell(1); cell1.setCellValue("name"); cell1.setCellStyle(cellStyle); Cell cell2 = row.createCell(2); cell2.setCellValue("age"); cell2.setCellStyle(cellStyle); } /** * 生成文件在本地 * @param fileName * @param wb */ public static void buildExcelFile(String fileName, Workbook wb) { try { FileOutputStream fos = new FileOutputStream(fileName); wb.write(fos); fos.flush(); fos.close(); } catch (FileNotFoundException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } /** * 返回给浏览器 * @param fileName * @param wb * @param response */ public static void buildExcelFile(String fileName, Workbook wb, HttpServletResponse response) { try { response.setContentType("application/vnd.ms-excel"); response.setHeader("Content-Disposition", "attachment;filename=" + URLEncoder.encode(fileName, "utf-8")); ServletOutputStream outputStream = response.getOutputStream(); wb.write(outputStream); outputStream.flush(); outputStream.close(); } catch (UnsupportedEncodingException e) { e.printStackTrace(); } catch (IOException e) { e.printStackTrace(); } } } /***Controller层操作,省略service和dao*/@Controller@RequestMapping("/excel")public class ExcelController { @Autowired ExcelService excelService; @RequestMapping("/") public String toIndex() { return "excel"; } @ResponseBody @RequestMapping("/download/{way}") public String excelFileDownload(@PathVariable String way, HttpServletResponse response) throws IOException { Workbook wb = null; String filename = ExcelUtil.FILE_NAME; if (ExcelUtil.EXCEL_XLS.equals(way)) { wb = new HSSFWorkbook(); filename += ".xls"; } else if ((ExcelUtil.EXCEL_XLSX.equals(way))) { wb = new XSSFWorkbook(); filename += ".xlsx"; } Sheet sheet = wb.createSheet("角色表"); ExcelUtil.createTitle(wb, sheet); //从数据库查出数据 List<TRole> tRoles = excelService.getAllRoles(); //记录新增行数据 int rowNum = 1; for (TRole tRole : tRoles) { Row row = sheet.createRow(rowNum); row.createCell(0).setCellValue(tRole.getId()); row.createCell(1).setCellValue(tRole.getName()); row.createCell(2).setCellValue(tRole.getAge()); rowNum++; } //生成excel文件(在当前文件夹下) //ExcelUtil.buildExcelFile(filename, wb); //浏览器下载excel ExcelUtil.buildExcelFile(filename, wb, response); wb.close(); return "File downloaded successfully"; } @ResponseBody @RequestMapping("/upload") public String excelFileUpload(MultipartFile file) throws IOException { Workbook wb = null; List<TRole> tRoleList = new ArrayList<>(); if (!ExcelUtil.isExcelXlsx(file.getOriginalFilename()) && !ExcelUtil.isExcelXls(file.getOriginalFilename())) { return "The file must be of excel type"; } if (file.getOriginalFilename() == null || file.getOriginalFilename().equals("") || file.getSize() == 0) { return "The file cannot be empty"; } try { if (ExcelUtil.isExcelXlsx(file.getOriginalFilename())) { wb = new XSSFWorkbook(file.getInputStream()); } else { wb = new HSSFWorkbook(file.getInputStream()); } //获取第一张表(默认就设置成1张表吧) Sheet sheet = wb.getSheetAt(0); //获取行数 int rowsNum = sheet.getPhysicalNumberOfRows(); //遍历行 for (int i = 0; i < rowsNum; i++) { //标题行省略 if (i == 0) { continue; } Row row = sheet.getRow(i); //遍历每行的单元格// for (int j=0;j<row.getPhysicalNumberOfCells();j++){// Cell cell = row.getCell(j);// } //这里我们直接操作具体的单元格 Integer id = Integer.valueOf(Double.valueOf(row.getCell(0).getNumericCellValue()).intValue()); String name = row.getCell(1).getStringCellValue(); Integer age = Integer.valueOf(Double.valueOf(row.getCell(2).getNumericCellValue()).intValue()); TRole tRole = new TRole(id, name, age); tRoleList.add(tRole); } } catch (IOException e) { e.printStackTrace(); } finally { wb.close(); } return JSON.toJSONString(tRoleList); }}

来源:博客园

作者:loading---

链接:https://www.cnblogs.com/hucheng1997/p/11426086.html

Recommend