记录一次工作中Springboot的Excel导入导出功能

清疚 2023-06-03 04:57 9阅读 0赞

前不久有个朋友让我写个导入导出功能给他，由于当时忙于工作，就让他在网上找找看，网上有很多。  
没多久，自己就做了个导入导出的功能。Excel导入导出功能在开发中还是蛮常见的。而碰巧的是，这个需求碰巧是我写的上一篇博客的拓展功能。这里记录一下自己做的excel导入导出功能。  
需求，原型图很简单：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]  
很常见的一个excel导入导出需求  
excel模板长这样：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]  
下载下来的客户清单如图：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 2]  
这个没什么难度，就是从数据库抓取数据，把数据带下来放在excel文件里  
废话不多说，直接看代码

Controller层代码

@Autowired
    private FirstOrderHistoryDataService firstOrderHistoryDataService;
    
    @RequestMapping(value = "/download-customer-list", method = RequestMethod.GET, produces = "application/xls")
    public ResponseEntity<byte[]> downCustomerList() { 
    	return firstOrderHistoryDataService.downCustomerList();
    }

Service层代码

ResponseEntity<byte[]> downCustomerList();

实现类

@Autowired
    private ResourceLoader resourceLoader;//spring自带的获取资源的接口
    @Autowired
    private CustomerRepository customerRepository;
    
    @Override
    public ResponseEntity<byte[]> downCustomerList() { 
    	HttpHeaders headers = new HttpHeaders();
    	headers.setContentType(MediaType.parseMediaType("application/xls"));
    	String excelName = "客户清单.xls";
    	headers.setContentDispositionFormData(excelName, excelName);
    	byte[] contents = buildExcelDocument();
    	ResponseEntity<byte[]> response = new ResponseEntity<byte[]>(contents, headers, HttpStatus.OK);
    	return response;
    }
    
    private byte[] buildExcelDocument() { 
    	ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    	Workbook workbook;
    	try { 
    		//在reource资源包下放个excel模板，并加载这个excel文件
    		Resource resource = resourceLoader.getResource("classpath:excel-templates/customerlist.xls");
    		InputStream stream = resource.getInputStream();
    		workbook = WorkbookFactory.create(stream);
    		Sheet sheet = workbook.getSheetAt(0);
    		setSheetBody(sheet, workbook);
    		workbook.write(outputStream);
    		workbook.close();
    	} catch (IOException e) { 
    		e.printStackTrace();
    	} catch (EncryptedDocumentException e) { 
    		e.printStackTrace();
    	} catch (InvalidFormatException e) { 
    		e.printStackTrace();
    	}
    	return outputStream.toByteArray();
    }
    
    private void setSheetBody(Sheet sheet, Workbook workbook) { 
    	int rowSeq = 0;
    	int dataRowNum = 3;
    	//获取所有的客户数据（Repository层的findAll()方法获取所有数据）
    	List<Customer> customers = Lists.newArrayList(customerRepository.findAll()).stream()
    			.sorted(Comparator.comparing(Customer::getCustomerNo)).collect(Collectors.toList());//按照客户编号排序
    	for (Customer customer : customers) { 
    		rowSeq++;
    		sheet.shiftRows(dataRowNum, sheet.getLastRowNum(), 1);
    		Row dataRow = sheet.createRow(dataRowNum);
    		dataRow.createCell(1).setCellValue(rowSeq);
    		dataRow.createCell(2).setCellValue(customer.getCustomerNo());
    		dataRow.createCell(3).setCellValue(customer.getShortName());
    
    		for (int i = 1; i <= 3; i++) { 
    			//调整excel格式
    			ExcelFormat.setCellStyle(workbook, dataRow, i, false, "", HorizontalAlignment.CENTER, null, true, true,
    					true, true);
    		}
    		dataRowNum++;
    	}
    }

OK，下载功能就做好了，接下来就是上传功能，上传功能也不是很难，不过，校验比较多。  
现在把这个代码放下来。  
Controller层代码：

import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.http.ResponseEntity;
    import org.springframework.web.bind.annotation.RequestMapping;
    import org.springframework.web.bind.annotation.RequestMethod;
    import org.springframework.web.bind.annotation.RequestParam;
    import org.springframework.web.bind.annotation.RestController;
    import org.springframework.web.multipart.MultipartFile;
    
    
    
    @RestController
    @RequestMapping("/api/historydata/first-order-history-data")
    public class FirstOrderHistoryDataController extends ExceptionResponse { 
    	@Autowired
    	private FirstOrderHistoryDataService firstOrderHistoryDataService;
    
    	@RequestMapping(value = "/download-customer-list", method = RequestMethod.GET, produces = "application/xls")
    	public ResponseEntity<byte[]> downCustomerList() { 
    		return firstOrderHistoryDataService.downCustomerList();
    	}
    
    	//上传excel功能
    	@RequestMapping(value = "/upload-history-data-excel", method = RequestMethod.POST)
    	public String uploadHistoryDataExcel(@RequestParam("file") MultipartFile file) { 
    		return firstOrderHistoryDataService.uploadHistoryDataExcel(file);
    	}
    }

Service层代码

import org.springframework.http.ResponseEntity;
    import org.springframework.web.multipart.MultipartFile;
    
    public interface FirstOrderHistoryDataService { 
    	//下载
    	ResponseEntity<byte[]> downCustomerList();
    	//上传
    	String uploadHistoryDataExcel(MultipartFile file);
    }

实现类

import java.io.ByteArrayOutputStream;
    import java.io.IOException;
    import java.io.InputStream;
    import java.util.ArrayList;
    import java.util.Comparator;
    import java.util.Date;
    import java.util.List;
    import java.util.Set;
    import java.util.regex.Pattern;
    import java.util.stream.Collectors;
    
    import javax.transaction.Transactional;
    
    import org.apache.poi.EncryptedDocumentException;
    import org.apache.poi.hssf.usermodel.HSSFDateUtil;
    import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
    import org.apache.poi.ss.usermodel.CellType;
    import org.apache.poi.ss.usermodel.HorizontalAlignment;
    import org.apache.poi.ss.usermodel.Row;
    import org.apache.poi.ss.usermodel.Sheet;
    import org.apache.poi.ss.usermodel.Workbook;
    import org.apache.poi.ss.usermodel.WorkbookFactory;
    import org.springframework.beans.factory.annotation.Autowired;
    import org.springframework.core.io.Resource;
    import org.springframework.core.io.ResourceLoader;
    import org.springframework.http.HttpHeaders;
    import org.springframework.http.HttpStatus;
    import org.springframework.http.MediaType;
    import org.springframework.http.ResponseEntity;
    import org.springframework.stereotype.Service;
    import org.springframework.web.multipart.MultipartFile;
    
    import com.google.common.collect.Lists;
    
    
    @Service
    @Transactional
    public class FirstOrderHistoryDataServiceImpl implements FirstOrderHistoryDataService { 
    	@Autowired
    	private ResourceLoader resourceLoader;
    	@Autowired
    	private CustomerRepository customerRepository;
    	@Autowired
    	private FirstOrderHistoryDataRepository firstOrderHistoryDataRepository;
    
    	//下载功能
    	@Override
    	public ResponseEntity<byte[]> downCustomerList() { 
    		HttpHeaders headers = new HttpHeaders();
    		headers.setContentType(MediaType.parseMediaType("application/xls"));
    		String excelName = "客户清单.xls";
    		headers.setContentDispositionFormData(excelName, excelName);
    		byte[] contents = buildExcelDocument();
    		ResponseEntity<byte[]> response = new ResponseEntity<byte[]>(contents, headers, HttpStatus.OK);
    		return response;
    	}
    
    	private byte[] buildExcelDocument() { 
    		ByteArrayOutputStream outputStream = new ByteArrayOutputStream();
    		Workbook workbook;
    		try { 
    			Resource resource = resourceLoader.getResource("classpath:excel-templates/customerlist.xls");
    			InputStream stream = resource.getInputStream();
    			workbook = WorkbookFactory.create(stream);
    			Sheet sheet = workbook.getSheetAt(0);
    			setSheetBody(sheet, workbook);
    			workbook.write(outputStream);
    			workbook.close();
    		} catch (IOException e) { 
    			e.printStackTrace();
    		} catch (EncryptedDocumentException e) { 
    			e.printStackTrace();
    		} catch (InvalidFormatException e) { 
    			e.printStackTrace();
    		}
    		return outputStream.toByteArray();
    	}
    
    	private void setSheetBody(Sheet sheet, Workbook workbook) { 
    		int rowSeq = 0;
    		int dataRowNum = 3;
    		List<Customer> customers = Lists.newArrayList(customerRepository.findAll()).stream()
    				.sorted(Comparator.comparing(Customer::getCustomerNo)).collect(Collectors.toList());
    		for (Customer customer : customers) { 
    			rowSeq++;
    			sheet.shiftRows(dataRowNum, sheet.getLastRowNum(), 1);
    			Row dataRow = sheet.createRow(dataRowNum);
    			dataRow.createCell(1).setCellValue(rowSeq);
    			dataRow.createCell(2).setCellValue(customer.getCustomerNo());
    			dataRow.createCell(3).setCellValue(customer.getShortName());
    
    			for (int i = 1; i <= 3; i++) { 
    				ExcelFormat.setCellStyle(workbook, dataRow, i, false, "", HorizontalAlignment.CENTER, null, true, true,
    						true, true);
    			}
    			dataRowNum++;
    		}
    	}
    	//上传功能
    	@Override
    	public String uploadHistoryDataExcel(MultipartFile file) { 
    		StringBuilder sBuilder = new StringBuilder();
    		Workbook workbook;
    		try { 
    			InputStream stream = file.getInputStream();
    			workbook = WorkbookFactory.create(stream);
    			Sheet sheet = workbook.getSheetAt(0);
    			//构建FirstOrderHistoryData对象数据，之后返回List集合
    			List<FirstOrderHistoryData> firstOrderHistoryDatas = genFirstOrderHistoryDataList(sheet);
    			workbook.close();
    			isExcelRepeat(sheet, firstOrderHistoryDatas);//校验excel里面的数据是否重复
    			isDataBaseRepeat(sheet, firstOrderHistoryDatas);//跟数据库的数据做校验
    			validCustomerNoAndCustomerName(firstOrderHistoryDatas);//校验客户编号和客户名称是否对应
    			//在数据库中找到所有的数据
    			List<FirstOrderHistoryData> allHistoryDatas = firstOrderHistoryDataRepository.findAll();
    			//用excel里面的数据和数据库中的数据进行对比，方便之后进行操作（存在的数据进行覆盖，没有的数据直接插入）
    			firstOrderHistoryDatas.stream().forEach(row -> { 
    				FirstOrderHistoryData firstOrderHistoryData = allHistoryDatas.stream()
    						.filter(hdata -> hdata.getCustomerName().equals(row.getCustomerName())).findFirst()
    						.orElse(null);
    				if (firstOrderHistoryData != null) { 
    					row.setId(firstOrderHistoryData.getId());
    				}
    			});
    
    			firstOrderHistoryDataRepository.save(firstOrderHistoryDatas);
    			sBuilder.append("导入数据成功.更新历史数据" + firstOrderHistoryDatas.size() + "条.");
    		} catch (IOException | EncryptedDocumentException | InvalidFormatException e) { 
    			e.printStackTrace();
    		}
    		return sBuilder.toString();
    	}
    
    	//客户编号和客户名称是一一对应的，为了以防被修改，所以进行这个校验
    	private void validCustomerNoAndCustomerName(List<FirstOrderHistoryData> firstOrderHistoryDatas) { 
    		List<Customer> customers = Lists.newArrayList(customerRepository.findAll());
    		firstOrderHistoryDatas.stream().forEach(row -> { 
    			Customer cus = customers.stream().filter(customer -> customer.getCustomerNo().equals(row.getCustomerNo()))
    					.findFirst().orElse(null);
    			if (!(cus != null
    					&& cus.getShortName().trim().toLowerCase().equals(row.getCustomerName().trim().toLowerCase()))) { 
    				throw new ExcelContentException(
    						"上传历史数据文件中客户编号与客户名称不对应! " + "请查看客户主数据中与客户名称对应的客户编号! 客户名称为：" + row.getCustomerName());
    			}
    		});
    	}
    
    	//构建FirstOrderHistoryData集合
    	private List<FirstOrderHistoryData> genFirstOrderHistoryDataList(Sheet sheet) { 
    		List<FirstOrderHistoryData> firstOrderHistoryDatas = new ArrayList<FirstOrderHistoryData>();
    		int lastRowNum = sheet.getLastRowNum();
    		if (lastRowNum <= 2) { 
    			throw new ExcelContentException("上传历史数据文件中没有有效数据.");
    		}
    		for (int i = 3; i <= lastRowNum; i++) { 
    			Row row = sheet.getRow(i);
    			if (row == null) { 
    				throw new ExcelContentException("上传历史数据文件中的第" + (i + 1) + "行为空,请先确认删除本行");
    			}
    			String customerNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 2);
    			String customerName = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 3);
    			Date orderDate = null;
    			Date productionDate = null;
    			Date stockDate = null;
    			orderDate = validOrderDate(i, row, orderDate);//日期校验
    			String orderNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 5);//后面有这个excel工具类的代码
    			String orderAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 6);
    			productionDate = validProductionDate(i, row, productionDate);//日期校验
    			String productionNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 8);
    			String productionAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 9);
    			stockDate = validStockDate(i, row, stockDate);//日期校验
    			String stockNo = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 11);
    			String stockAmount = ExcelUtil.getStringCellValueWithNumOrStr(sheet, i, 12);
    			//构建FirstOrderHistoryData对象
    			firstOrderHistoryDatas.add(genFirstOrder(customerNo, customerName, orderDate, orderNo, orderAmount,
    					productionDate, productionNo, productionAmount, stockDate, stockNo, stockAmount, i));
    		}
    		return firstOrderHistoryDatas;
    	}
    
    	private Date validStockDate(int i, Row row, Date stockDate) { 
    		if (row.getCell(10) != null) { 
    			if (row.getCell(10).getCellTypeEnum() == CellType.NUMERIC) { 
    				if (HSSFDateUtil.isCellDateFormatted(row.getCell(10))) { 
    					stockDate = row.getCell(10).getDateCellValue();
    				} else { 
    					throw new ExcelContentException("第" + (i + 1) + "行的出货日期格式或时间错误，格式为yyyy-MM-dd.");
    				}
    			} else { 
    				throw new ExcelContentException("第" + (i + 1) + "行的出货日期格式或时间错误，格式为yyyy-MM-dd.");
    			}
    		}
    		return stockDate;
    	}
    
    	private Date validProductionDate(int i, Row row, Date productionDate) { 
    		if (row.getCell(7) != null) { 
    			if (row.getCell(7).getCellTypeEnum() == CellType.NUMERIC) { 
    				if (HSSFDateUtil.isCellDateFormatted(row.getCell(7))) { 
    					productionDate = row.getCell(7).getDateCellValue();
    
    				} else { 
    					throw new ExcelContentException("第" + (i + 1) + "行的下生产单日期格式或时间错误，格式为yyyy-MM-dd.");
    				}
    			} else { 
    				throw new ExcelContentException("第" + (i + 1) + "行的下生产单日期格式或时间错误，格式为yyyy-MM-dd.");
    			}
    		}
    		return productionDate;
    	}
    
    	private Date validOrderDate(int i, Row row, Date orderDate) { 
    		if (row.getCell(4) != null) { 
    			if (row.getCell(4).getCellTypeEnum() == CellType.NUMERIC) { 
    				if (HSSFDateUtil.isCellDateFormatted(row.getCell(4))) { 
    					orderDate = row.getCell(4).getDateCellValue();
    				} else { 
    					throw new ExcelContentException("第" + (i + 1) + "行的下订单日期格式或时间错误，格式为yyyy-MM-dd.");
    				}
    			} else { 
    				throw new ExcelContentException("第" + (i + 1) + "行的下订单日期格式或时间错误，格式为yyyy-MM-dd.");
    			}
    		}
    		return orderDate;
    	}
    
    	private FirstOrderHistoryData genFirstOrder(String customerNo, String customerName, Date orderDate, String orderNo,
    			String orderAmount, Date productionDate, String productionNo, String productionAmount, Date stockDate,
    			String stockNo, String stockAmount, int i) { 
    		FirstOrderHistoryData firstOrderHistoryData = new FirstOrderHistoryData();
    		firstOrderHistoryData.setCustomerName(customerName);
    		firstOrderHistoryData.setCustomerNo(customerNo);
    
    		genOrderData(orderDate, orderNo, orderAmount, firstOrderHistoryData, i);
    		genProduction(productionDate, productionNo, productionAmount, firstOrderHistoryData, i);
    		genStock(stockDate, stockNo, stockAmount, firstOrderHistoryData, i);
    		return firstOrderHistoryData;
    	}
    
    	private void genStock(Date stockDate, String stockNo, String stockAmount,
    			FirstOrderHistoryData firstOrderHistoryData, int i) { 
    		firstOrderHistoryData
    				.setStockDate(stockDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", stockDate));
    		if (stockNo != null && !isInteger(stockNo)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的出货订单号格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData.setStockNo(stockNo == null ? "" : stockNo);
    		}
    		if (stockAmount != null && !isInteger(stockAmount)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的出货金额格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData.setStockAmount(stockAmount == null ? 0 : Float.parseFloat(stockAmount));
    		}
    	}
    
    	private void genProduction(Date productionDate, String productionNo, String productionAmount,
    			FirstOrderHistoryData firstOrderHistoryData, int i) { 
    		firstOrderHistoryData.setProductionDate(
    				productionDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", productionDate));
    		if (productionNo != null && !isInteger(productionNo)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的下生产单订单号格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData.setProductionNo(productionNo == null ? "" : productionNo);
    		}
    		if (productionAmount != null && !isInteger(productionAmount)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的下生产单金额格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData
    					.setProductionAmount(productionAmount == null ? 0 : Float.parseFloat(productionAmount));
    		}
    	}
    
    	private void genOrderData(Date orderDate, String orderNo, String orderAmount,
    			FirstOrderHistoryData firstOrderHistoryData, int i) { 
    		firstOrderHistoryData
    				.setOrderDate(orderDate == null ? null : DateFormat.getFormatDate("yyyy-MM-dd", orderDate));
    		if (orderNo != null && !isInteger(orderNo)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的下订单的订单号格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData.setOrderNo(orderNo == null ? "" : orderNo);
    		}
    		if (orderAmount != null && !isInteger(orderAmount)) { 
    			throw new ExcelContentException("第" + (i + 1) + "行的下订单金额格式错误，必须为数字.");
    		} else { 
    			firstOrderHistoryData.setOrderAmount(orderAmount == null ? 0 : Float.parseFloat(orderAmount));
    		}
    	}
    	//校验是否为数字
    	private boolean isInteger(String str) { 
    		Pattern pattern = Pattern.compile("^[-\\+]?[\\d]*$");
    		return pattern.matcher(str).matches();
    	}
    
    	//校验excel中客户名字是否重复（需求：一个客户只能有一条数据）
    	private void isExcelRepeat(Sheet sheet, List<FirstOrderHistoryData> firstOrderHistoryDatas) { 
    		List<FirstOrderHistoryData> repeatRecords = firstOrderHistoryDatas.stream()
    				.collect(Collectors.groupingBy(FirstOrderHistoryData::getCustomerName)).entrySet().stream()
    				.filter(entry -> entry.getValue().size() > 1).map(entry -> entry.getValue().get(0))
    				.collect(Collectors.toList());
    		if (repeatRecords != null && repeatRecords.size() > 0) { 
    			throwExceptionOfRepeatRecordRowNums(sheet, repeatRecords, false);
    		}
    	}
    
    	private void throwExceptionOfRepeatRecordRowNums(Sheet sheet, List<FirstOrderHistoryData> repeatRecords,
    			boolean isDataBaseRepeatException) { 
    		List<String> customerNames = new ArrayList<String>();
    		List<String> rowNumsOfEachCustomer = new ArrayList<String>();
    		repeatRecords.stream().forEach(record -> getRepeatCustomerNamesAndExcelRowNumsOfEachCustomer(sheet, record,
    				customerNames, rowNumsOfEachCustomer));
    		String allCustomerNamesStr = customerNames.stream().collect(Collectors.joining(", ", "[", "]"));
    		String allrowNumsStr = rowNumsOfEachCustomer.stream().collect(Collectors.joining(", ", "[", "]"));
    		if (customerNames != null && customerNames.size() > 0 && rowNumsOfEachCustomer != null
    				&& rowNumsOfEachCustomer.size() > 0) { 
    			if (!isDataBaseRepeatException) { 
    				throw new ExcelContentException("上传的历史数据文件中存在客户名称为 " + allCustomerNamesStr + " 的重复记录,分别位于 "
    						+ allrowNumsStr + " 行.一个客户只能有一条数据!");
    			}
    		}
    	}
    
    	private void getRepeatCustomerNamesAndExcelRowNumsOfEachCustomer(Sheet sheet, FirstOrderHistoryData record,
    			List<String> customerNames, List<String> rowNumsOfEachCustomer) { 
    		customerNames.add(record.getCustomerName());
    		Set<Integer> rowNums = ExcelUtil.findRows(sheet, record.getCustomerName());
    		String rowNumStr = rowNums.stream().map(rowNum -> String.valueOf((rowNum + 1))).collect(Collectors.toSet())
    				.stream().collect(Collectors.joining(", ", "[", "]"));
    		rowNumsOfEachCustomer.add(rowNumStr);
    	}
    
    	//校验上传的excel文件中在数据库中存在不存在
    	private void isDataBaseRepeat(Sheet sheet, List<FirstOrderHistoryData> firstOrderHistoryDatas) { 
    		Set<String> customerNames = firstOrderHistoryDatas.stream().map(customerName -> customerName.getCustomerName())
    				.collect(Collectors.toSet());
    		List<Customer> repeatRecords = customerRepository.getCustomersByCustomerNameIn(customerNames);
    		if (firstOrderHistoryDatas.size() != repeatRecords.size()) { 
    			throw new ExcelContentException("上传历史数据文件中的客户名称在数据库中不存在");
    		}
    	}
    
    }

excel工具类ExcelUtil类的代码

public static String getStringCellValueWithNumOrStr(Sheet sheet, int rowNom, int colNum) { 
    	Cell cell = sheet.getRow(rowNom).getCell(colNum);
    	if (cell != null) { 
    		CellType cellType = cell.getCellTypeEnum();
    		if (cellType == CellType.STRING) { 
    			return cell.getStringCellValue().trim().toUpperCase();
    		} else if (cellType == CellType.NUMERIC) { 
    			return String.valueOf((long) cell.getNumericCellValue());
    		} else { 
    			return null;
    		}
    	}
    	return null;
    }

看效果：  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 3]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70]: /images/20230601/ee72b6ac2229464b9d472d93dd58bf9d.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 1]: /images/20230601/03a90852b21d449a85990eb6dfb2dccc.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 2]: /images/20230601/71a54fbbfe4b4043bc2028304b9d50a2.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L2V4b2R1czM_size_16_color_FFFFFF_t_70 3]: /images/20230601/ceb7f4fa660447509006258102795d67.png