POI读取并解析xlsx格式的excel文件

本文主要采用POI来解析excel文件。如果需要解析的是支持数据量更大的.xlsx后缀的格式，需要导入的jar包为poi-ooxml-3.15.jar；若是需要解析的是.xls后缀的格式，需要导入的是poi-3.17-beta.jar。jar版本建议采用公司maven库中的最新版。

本文的demo中，解析的是xlsx后缀的excel文件。xls格式的类似，只需要将所有的XSSF开头的都改成HSSF，并稍加改动即可。

上服务器端代码：

/**
	 * @return
	 * @throws Exception
	 * @author jason.gao
	 * 功能描述：交易导入
	 */
	@RequestMapping(value = "/transDetail/upload", method = {RequestMethod.POST, RequestMethod.GET})
	@RequestGuard(perm = "transaction.import.upload")
	public ResponseEntity<ResponseEnvelope<RestApiResp>> uploadFile(@RequestParam("file") CommonsMultipartFile[] files, HttpServletRequest req, HttpServletResponse resp){
		logger.info("uploadFile == >upload button start; fileName:[{}], CommonsMultipartFile[]:[{}]", files[0].getFileItem().getName(), files);
		Long startTime = System.currentTimeMillis();
		String result = "完成交易文件的导入！";
		try {
			for (int i = 0; i < files.length; i++) {
				//从文件名中获取商户号、商户名称、商户批次号、商户批次序号
				//String fileName = files[0].getFileItem().getName().replace(".xlsx", "");
				InputStream is = files[i].getInputStream();
				XSSFWorkbook xssFWorkbook = new XSSFWorkbook(is);
				//只处理第一个工作簿中的数据
				String handleResult = this.handleDetailDate(xssFWorkbook, 0);
				if (!handleResult.equals(TransImportJobStatus.Success.getValue())) {
					result = TransImportJobStatus.getDescByKey(handleResult);
					logger.error(result);
				}
			}
		} catch (CellDataException e ) {
			logger.error("CellDataException: ErrorCode:[{}], ErrorMessage:[{}]",e.getCode(),e.getMessage());
			return RestApiResp.getSuccResponseEntity(e.getMessage(), null);
		} catch (OspException e) {
			logger.error("OspException：[{}]",e.getMessage());
			return RestApiResp.getSuccResponseEntity(e.getMessage(), null);
		} catch (Exception e) {
			logger.error("未知异常：[{}]",e.getMessage());
			return RestApiResp.getSuccResponseEntity("未知异常，请排查日志：" + e.getMessage(), null);
		}
		logger.info("{}，耗时：{}毫秒",result, System.currentTimeMillis()-startTime);
		return RestApiResp.getSuccResponseEntity(result, null);
	}

	public String handleDetailDate(XSSFWorkbook xssFWorkbook, int numSheet) throws CellDataException, OspException{
		XSSFSheet xssFSheet = xssFWorkbook.getSheetAt(numSheet);
		if (xssFSheet == null) {
			return TransImportJobStatus.EmptyData.getValue();
		}

		/**
		 * 获取汇总行数据
		 */
		TransFileHeadModel transFileHeadModel = getHeadData(xssFSheet);
		/**
		 * 校验明细行的汇总结果是否符合汇总行
		 */
		int realDetailLine = checkDetailSummary(xssFSheet, transFileHeadModel);

		/**
		 * 分批的去调用OSP接口
		 */
		return doOspHandle(xssFSheet, transFileHeadModel, realDetailLine);

	}

	private int checkDetailSummary(XSSFSheet xssfSheet, TransFileHeadModel transFileHeadModel) throws CellDataException{
		int realDetailLine = 0;
		// 获取当前工作薄的明细行
		int paymentCount = 0;   //支付总笔数
		BigDecimal paymentAmount = BigDecimal.ZERO;  //支付总金额
		int refundCount = 0;  //退款总笔数
		BigDecimal refundAmount = BigDecimal.ZERO;  //退款总金额

		for (int j = 3; j <= xssfSheet.getLastRowNum() + 1; j++) {
			XSSFRow xssfRowDetail = xssfSheet.getRow(j);
			if (xssfRowDetail != null && !xssfRowDetail.getCell(0).toString().equals("")) {	//校验该行是否为有效数据
				String transType;
				if (xssfRowDetail.getCell(5) == null) {	//默认取值为1：支付
					transType = TransTypeEnums.Payment.getValue();
				} else {
					transType = XssfCellValueCheckHelper.getNumber(xssfRowDetail, 5, 0).toString();
				}
				if (transType.equals(TransTypeEnums.Payment.getValue())){
					paymentCount++;
					paymentAmount = paymentAmount.add(new BigDecimal(XssfCellValueCheckHelper.getNumber(xssfRowDetail, 7, 0)));
				} else if(transType.equals(TransTypeEnums.Refund.getValue())) {
					refundCount++;
					refundAmount = refundAmount.add(new BigDecimal(XssfCellValueCheckHelper.getNumber(xssfRowDetail,7, 0)));
				} else {
					throw new CellDataException(TransImpFileExceptEnums.DetailDateError.getValue(), TransImpFileExceptEnums.DetailDateError.setParams(j+1, "交易类型不为“支付或退款”").getDesc());
				}
			} else {
				realDetailLine = j - 1;
				break;
			}
		}
		if (paymentCount != transFileHeadModel.getPaymentCount()) {
			throw new CellDataException("汇总行数据中的“支付总笔数”错误：汇总行数据=[" + transFileHeadModel.getPaymentCount() + "]，计算数据=[" + paymentCount + "].");
		}
		if (paymentAmount.compareTo(transFileHeadModel.getPaymentAmount()) != 0) {
			throw new CellDataException("汇总行数据中的“支付总金额”错误：汇总行数据=[" + transFileHeadModel.getPaymentAmount() + "]，计算数据=[" + paymentAmount + "].");
		}
		if (refundCount != transFileHeadModel.getRefundCount()) {
			throw new CellDataException("汇总行数据中的“退款总笔数”错误：汇总行数据=[" + transFileHeadModel.getRefundCount() + "]，计算数据=[" + refundCount + "].");
		}
		if (transFileHeadModel.getRefundAmount().compareTo(refundAmount) != 0) {
			throw new CellDataException("汇总行数据中的“退款总金额”错误：汇总行数据=[" + transFileHeadModel.getRefundAmount() + "]，计算数据=[" + refundAmount + "].");
		}
		if (transFileHeadModel.getNetCount() != paymentCount - refundCount) {
			throw new CellDataException("汇总行数据中的“净笔数”错误：汇总行数据=[" + transFileHeadModel.getNe