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