导出
service
流程:
生成文件名->创立工作簿->设置表头信息(在两行定义上表的属性信息)->写入数据(与表头信息一一对应)->返回响应流
public void exportApparatus(boolean allExport, HttpServletResponse response) { String fileName = "仪器设备模板-" + LocalDateTime.now().format(DateTimeFormatter.ISO_LOCAL_DATE) + ".xlsx"; fileName = new String(fileName.getBytes(), StandardCharsets.ISO_8859_1); // 创立工作簿 try (Workbook workbook = WorkbookFactory.create(true)) { Sheet sheet = workbook.createSheet(); CellStyle cellStyle = workbook.createCellStyle(); cellStyle.setWrapText(true); cellStyle.setVerticalAlignment(VerticalAlignment.CENTER); cellStyle.setAlignment(HorizontalAlignment.CENTER); // 设置表头 // 1.1创立合并单元格对象 CellRangeAddress callRangeAddress = new CellRangeAddress(0, 0, 1, 6); // 2.1加载合并单元格对象 sheet.addMergedRegion(callRangeAddress); for (int i = 0; i < 1; i++) { Row row = sheet.createRow(i); Cell cell0 = row.createCell(0); cell0.setCellValue("xxxxx"); Cell cell1 = row.createCell(1); cell1.setCellValue("请将单元格格局设置为文本模式"); } createTitleRow(sheet); createTitleKeyRow(sheet); // 须要导出的数据，若为false则导出空模板 List<Apparatus> entities = new ArrayList<>(); if (BooleanUtils.isTrue(allExport)) { //获取数据源中的数据 entities = xxx.findAll(); } createDataRow(sheet, entities); // 响应给浏览器 BufferedOutputStream outputStream = new BufferedOutputStream(response.getOutputStream()); response.setContentType("application/octet-stream;charset=ISO_8859_1"); response.setHeader("Content-disposition", "attachment; filename=" + fileName); workbook.write(response.getOutputStream()); outputStream.flush(); response.getOutputStream().close(); } catch (IOException e) { throw new RuntimeException("导出仪器设备失败", e.getCause()); } }//创建表格的第一行数据 private void createTitleRow(Sheet sheet) { Row row = sheet.createRow(1); Cell cell0 = row.createCell(0); cell0.setCellValue("属性名称"); Cell cell1 = row.createCell(1); cell1.setCellValue("*仪器设备名称"); Cell cell2 = row.createCell(2); cell2.setCellValue("*型号规格"); Cell cell3 = row.createCell(3); cell3.setCellValue("*生产厂家"); Cell cell4 = row.createCell(4); cell4.setCellValue("分类"); Cell cell5 = row.createCell(5); cell5.setCellValue("计量编号"); Cell cell6 = row.createCell(6); cell6.setCellValue("出厂编号"); Cell cell7 = row.createCell(7); cell7.setCellValue("溯源形式"); Cell cell8 = row.createCell(8); cell8.setCellValue("所属实验室"); Cell cell9 = row.createCell(9); cell9.setCellValue("性能参数"); Cell cell10 = row.createCell(10); cell10.setCellValue("所属单位"); Cell cell11 = row.createCell(11); cell11.setCellValue("性能形容"); Cell cell12 = row.createCell(12); cell12.setCellValue("适合的检测办法"); Cell cell13 = row.createCell(13); cell13.setCellValue("校准周期"); Cell cell14 = row.createCell(14); cell14.setCellValue("计量证书编号"); Cell cell15 = row.createCell(15); cell15.setCellValue("有效期"); Cell cell16 = row.createCell(16); cell16.setCellValue("状态"); Cell cell17 = row.createCell(17); cell17.setCellValue("是否是关键性设施"); Cell cell18 = row.createCell(18); cell18.setCellValue("责任人"); Cell cell19 = row.createCell(19); cell19.setCellValue("购入工夫"); Cell cell20 = row.createCell(20); cell20.setCellValue("购入原值"); Cell cell21 = row.createCell(21); cell21.setCellValue("租户id"); }//创建表格的第二行数据 private void createTitleKeyRow(Sheet sheet) { Row row = sheet.createRow(2); Cell cell0 = row.createCell(0); cell0.setCellValue("属性代号"); Cell cell1 = row.createCell(1); cell1.setCellValue("name"); Cell cell2 = row.createCell(2); cell2.setCellValue("specification"); Cell cell3 = row.createCell(3); cell3.setCellValue("manufacturer"); Cell cell4 = row.createCell(4); cell4.setCellValue("apparatusGroup"); Cell cell5 = row.createCell(5); cell5.setCellValue("measurementCode"); Cell cell6 = row.createCell(6); cell6.setCellValue("factoryCode"); Cell cell7 = row.createCell(7); cell7.setCellValue("backWay"); Cell cell8 = row.createCell(8); cell8.setCellValue("ownLab"); Cell cell9 = row.createCell(9); cell9.setCellValue("functionParam"); Cell cell10 = row.createCell(10); cell10.setCellValue("ownTenant"); Cell cell11 = row.createCell(11); cell11.setCellValue("description"); Cell cell12 = row.createCell(12); cell12.setCellValue("detectionWay"); Cell cell13 = row.createCell(13); cell13.setCellValue("calibrationCycle"); Cell cell14 = row.createCell(14); cell14.setCellValue("measurementCertificateCode"); Cell cell15 = row.createCell(15); cell15.setCellValue("periodValidity"); Cell cell16 = row.createCell(16); cell16.setCellValue("state"); Cell cell17 = row.createCell(17); cell17.setCellValue("isKeyApparatus"); Cell cell18 = row.createCell(18); cell18.setCellValue("principalName"); Cell cell19 = row.createCell(19); cell19.setCellValue("buyTime"); Cell cell20 = row.createCell(20); cell20.setCellValue("buyPrice"); Cell cell21 = row.createCell(21); cell21.setCellValue("tenantId"); }//把在数据源中查到的数据,写到表格中,从第三行开始 private void createDataRow(Sheet sheet, List<Apparatus> entities) { for (int i = 0; i < entities.size(); i++) { Row row = sheet.createRow(i + 3); Cell cell0 = row.createCell(0); cell0.setCellValue(i + 1); Cell cell1 = row.createCell(1); cell1.setCellValue(entities.get(i).getName()); Cell cell2 = row.createCell(2); cell2.setCellValue(entities.get(i).getSpecification()); Cell cell3 = row.createCell(3); cell3.setCellValue(entities.get(i).getManufacturer()); Cell cell4 = row.createCell(4); cell4.setCellValue(entities.get(i).getApparatusGroup()); Cell cell5 = row.createCell(5); cell5.setCellValue(entities.get(i).getMeasurementCode()); Cell cell6 = row.createCell(6); cell6.setCellValue(entities.get(i).getFactoryCode()); Cell cell7 = row.createCell(7); cell7.setCellValue(entities.get(i).getBackWay()); Cell cell8 = row.createCell(8); cell8.setCellValue(entities.get(i).getOwnLab()); Cell cell9 = row.createCell(9); cell9.setCellValue(entities.get(i).getFunctionParam()); Cell cell10 = row.createCell(10); cell10.setCellValue(entities.get(i).getOwnTenant()); Cell cell11 = row.createCell(11); cell11.setCellValue(entities.get(i).getDescription()); Cell cell12 = row.createCell(12); cell12.setCellValue(entities.get(i).getDetectionWay()); Cell cell13 = row.createCell(13); cell13.setCellValue(entities.get(i).getCalibrationCycle()); Cell cell14 = row.createCell(14); cell14.setCellValue(entities.get(i).getMeasurementCertificateCode()); Cell cell15 = row.createCell(15); String periodValidity = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") .format(Date.from(entities.get(i).getPeriodValidity())); cell15.setCellValue(periodValidity); Cell cell16 = row.createCell(16); cell16.setCellValue(entities.get(i).getState()); Cell cell17 = row.createCell(17); cell17.setCellValue(BooleanUtils.isTrue(entities.get(i).getIsKeyApparatus()) ? "是" : "否"); Cell cell18 = row.createCell(18); cell18.setCellValue(entities.get(i).getPrincipalName()); Cell cell19 = row.createCell(19); cell19.setCellValue(entities.get(i).getBuyTime()); Cell cell20 = row.createCell(20); cell20.setCellValue(entities.get(i).getBuyPrice()); Cell cell21 = row.createCell(21); cell21.setCellValue(String.valueOf(entities.get(i).getTenantId())); } }controller
@GetMapping(value = "/xxx/export", name = "导出或下载空模版") ResponseEntity<Void> exportApparatus( @Parameter(description = "是否全副导出") @RequestParam(required = false) Boolean allExport, @Parameter(description = "响应流") HttpServletResponse response) { apparatusExpImpService.exportApparatus(allExport, response); return ResponseEntity.ok().build();导入
service
流程:
获取文件->解析文件->封装form->创立数据
public void importApparatus(InputStream stream) { ImportParams importParams = new ImportParams(); importParams.setHeadRows(2); try (Workbook wb = WorkbookFactory.create(stream)) { // 获取sheet页数 int numberOfSheets = wb.getNumberOfSheets(); for (int i = 0; i < numberOfSheets; i++) { // 获取sheet页 Sheet sheet = wb.getSheetAt(i); // 解析数据 List<ApparatusDto> list = resolveExcelData(sheet); if (list != null) { for (ApparatusDto importDTO : list) { apparatusService.createApparatus(importDTO); } } } } catch (IOException e) { throw new RuntimeException("导入租户失败", e.getCause()); } }public List<ApparatusDto> resolveExcelData(Sheet sheet) { // 寄存后果 List<ApparatusDto> list = new ArrayList<>(); // 获取最大行 int lastRowNum = sheet.getLastRowNum(); // 从第四行开始 for (int j = 3; j <= lastRowNum; j++) { // 获取每一行 Row row = sheet.getRow(j); ApparatusDto apparatusDto = new ApparatusDto(); if (row != null) { apparatusDto.setName(getCellStringValue(row, 1)); apparatusDto.setSpecification(getCellStringValue(row, 2)); apparatusDto.setManufacturer(getCellStringValue(row, 3)); apparatusDto.setApparatusGroup(getCellStringValue(row, 4)); apparatusDto.setMeasurementCode(getCellStringValue(row, 5)); apparatusDto.setFactoryCode(getCellStringValue(row, 6)); apparatusDto.setBackWay(getCellStringValue(row, 7)); apparatusDto.setOwnLab(getCellStringValue(row, 8)); apparatusDto.setFunctionParam(getCellStringValue(row, 9)); apparatusDto.setOwnTenant(getCellStringValue(row, 10)); apparatusDto.setDescription(getCellStringValue(row, 11)); apparatusDto.setDetectionWay(getCellStringValue(row, 12)); apparatusDto.setCalibrationCycle(getCellStringValue(row, 13)); apparatusDto.setMeasurementCertificateCode(getCellStringValue(row, 14)); String periodValidity = getCellStringValue(row, 15); if (StringUtils.isNotBlank(periodValidity)) { try { Instant period = new SimpleDateFormat("yyyy-MM-dd HH:mm:ss") .parse(getCellStringValue(row, 15)) .toInstant(); apparatusDto.setPeriodValidity(period); } catch (Exception e) { throw new RuntimeException(e.getMessage()); } } apparatusDto.setState(getCellStringValue(row, 16)); apparatusDto.setIsKeyApparatus(StringUtils.equals(getCellStringValue(row, 17), "是")); apparatusDto.setPrincipalName(getCellStringValue(row, 18)); apparatusDto.setBuyTime(getCellStringValue(row, 19)); apparatusDto.setBuyPrice(getCellStringValue(row, 20)); apparatusDto.setTenantId(getCellStringValue(row, 21)); list.add(apparatusDto); } } return list; } private String getCellStringValue(Row row, int colIndex) { Cell cell = row.getCell(colIndex); if (cell == null) { return null; } return getCellValue(cell); } /** 判断类型 */ private String getCellValue(Cell cell) { String flag = ".0"; String value = null; CellType cellCode = cell.getCellType(); if (CellType.NUMERIC == cellCode) { long round = Math.round(cell.getNumericCellValue()); if (Double.parseDouble(round + flag) == cell.getNumericCellValue()) { value = String.valueOf(round); } else { DecimalFormat df = new DecimalFormat("#.###########"); value = df.format(cell.getNumericCellValue()); } } else if (CellType.STRING == cellCode) { value = cell.getStringCellValue(); } else if (CellType.FORMULA == cellCode) { value = cell.getStringCellValue(); } return value; }controller
@PostMapping( value = "/xxx/import", consumes = MediaType.MULTIPART_FORM_DATA_VALUE, name = "Excel导入") ResponseEntity<Void> importApparatus( @Parameter(description = "文件") @RequestPart(value = "file") MultipartFile file) { try (InputStream stream = file.getInputStream()) { apparatusExpImpService.importApparatus(stream); } catch (IOException e) { throw new RuntimeException("导入失败", e); } return ResponseEntity.ok().build(); }