在项目poi.xml中导入excel-poi相关的依赖
2. 公共实体org.apache.poi poi4.1.2 org.apache.poi poi-ooxml4.1.2
import io.swagger.annotations.ApiModelProperty; import lombok.Data; @Data public class User { @ApiModelProperty("姓名") private String name; @ApiModelProperty("性别") private String sex; @ApiModelProperty("年龄") private String age; @ApiModelProperty("手机号") private String mobile; @ApiModelProperty("备注") private String remarks; public User() {} public User(String name,String sex,String age,String mobile,String remarks) { this.name = name; this.sex = sex; this.age = age; this.mobile = mobile; this.remarks = remarks; } }3. 读取excel
@ApiOperation("上传excel-读取excel内容") @PostMapping("/readExcel") public ApiRes readExcel(MultipartFile file) { ApiRes resp = new ApiRes(); int rowNum = 0; Listlist = new ArrayList<>(); try { InputStream fis = file.getInputStream(); Workbook book = WorkbookFactory.create(fis); Sheet sheet = book.getSheet("sheet1"); if (sheet == null) { resp.fail("找不到sheet1表格"); return resp; } //校验表头 List titles = Arrays.asList("姓名","性别","年龄","手机号","备注"); Row row0 = sheet.getRow(0); for (int i = 0; i < row0.getLastCellNum(); i++) { Cell cell = row0.getCell(i); String val = getCellStringValue(cell); if (!val.equals(titles.get(i))) { resp.fail("该表格表头与模板不一致:" + val + "!=" + titles.get(i)); return resp; } } //读取数据 for (rowNum = 1; rowNum <= sheet.getLastRowNum(); rowNum++) { Row row = sheet.getRow(rowNum); User user = new User(); user.setName(getCellStringValue(row.getCell(0))); user.setSex(getCellStringValue(row.getCell(1))); user.setAge(getCellStringValue(row.getCell(2))); user.setMobile(getCellStringValue(row.getCell(3))); user.setRemarks(getCellStringValue(row.getCell(4))); list.add(user); } } catch (Exception e) { e.printStackTrace(); resp.fail("第" + (rowNum + 1) + "行数据解析错误"); return resp; } if (!CollectionUtils.isEmpty(list)) { list.forEach(System.out::println); } return resp.ok(list); }
public static String getCellStringValue(Cell cell) { String cellValue = ""; if (cell == null) { return cellValue; } switch (cell.getCellType()) { //1. 字符串类型 case STRING: cellValue = cell.getStringCellValue(); if (cellValue.trim().equals("") || cellValue.trim().length() <= 0) { cellValue = " "; } break; //2. 数值类型 case NUMERIC: // cell.getNumericCellValue();cell.getDateCellValue(); cell.setCellType(CellType.STRING); cellValue = cell.getStringCellValue(); break; //3. 表达式类型 case FORMULA: // cell.getCellFormula(); cell.setCellType(CellType.NUMERIC); cellValue = String.valueOf(cell.getNumericCellValue()); break; case BLANK: //4. 空 break; case BOOLEAN: //5. 布尔类型 // cell.getBooleanCellValue(); break; case ERROR: //6. 异常类型 // cell.getErrorCellValue(); break; default: break; } return cellValue; }4. 写入数据,导出为excel文件
@ApiOperation("输出excel-生成excel") @GetMapping("/exportExcel") public void exportExcel(HttpServletResponse response) { List5. 扩展-生成excel文件时,同时设置单元格的样式list = new ArrayList<>(); list.add(new User("张三", "男", "22", "13133265542", "是个帅哥")); list.add(new User("张二", "男", "22", "13552112222", "是张三的弟弟")); list.add(new User("张一", "", "21", "13133265542", "是张二的弟弟")); List titles = Arrays.asList("姓名", "性别", "年龄", "手机号", "备注"); Workbook workbook = null; OutputStream out = null; //场景二 FileOutputStream fileOut = null; try { workbook = new SXSSFWorkbook(); Sheet sheet = workbook.createSheet(); Row row0 = sheet.createRow(0); for (int i = 0; i < titles.size(); i++) { Cell cell = row0.createCell(i); cell.setCellValue(titles.get(i)); } int rowNum = 1; for (User user : list) { Row row = sheet.createRow(rowNum++); int cellNum = 0; Cell cell = row.createCell(cellNum++); cell.setCellValue(user.getName()); cell = row.createCell(cellNum++); cell.setCellValue(user.getSex()); cell = row.createCell(cellNum++); cell.setCellValue(user.getAge()); cell = row.createCell(cellNum++); cell.setCellValue(user.getMobile()); cell = row.createCell(cellNum++); cell.setCellValue(user.getRemarks()); } // String excelName = "excel导出"; // String fileName = excelName + new SimpleDateFormat("yyyy-MM-dd HH:mm:ss").format(new Date()) + ".xlsx"; // fileName = new String(fileName.getBytes("UTF-8"), "iso8859-1"); // response.setHeader("Content-Disposition", "attachment;filename=" + fileName); // response.setContentType("application/x-download"); // response.setCharacterEncoding("UTF-8"); // response.addHeader("Pargam", "no-cache"); // response.addHeader("Cache-Control", "no-cache"); // response.flushBuffer(); // // out = response.getOutputStream(); // workbook.write(out); // out.flush(); //场景二 String filePath = "F:\jwork\excel\excel文件\writeExcel.xlsx"; File exportFile = new File(filePath); if (!exportFile.exists()) { exportFile.createNewFile(); } fileOut = new FileOutputStream(filePath); workbook.write(fileOut); fileOut.flush(); } catch (Exception e) { System.out.println("写入Excel过程出错,错误原因:" + e.getMessage()); } finally { // try { // if (null != workbook) { // workbook.close(); // } // if (null != out) { // out.close(); // } // } catch (Exception e) { // System.out.println("关闭workbook或者outputStream出错" + e.getMessage()); // } //场景二 try { if (null != fileOut) { fileOut.close(); } if (null != workbook) { workbook.close(); } } catch (Exception e) { System.out.println("关闭workbook或者FileOutputStream出错" + e.getMessage()); } } }
欢迎分享,转载请注明来源:内存溢出
评论列表(0条)