本文主要采用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