关于java:Excel导入导出数据

导出

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();
  }