public ManifestImportResponse importManifest(ManifestImportModel manifestImportEntity, String user) {
ManifestImportResponse response = new ManifestImportResponse();
List<ManifestImportModel> list = Lists.newArrayList();
InboundSailing inboundSailing = inboundSailingService.findById(manifestImportEntity.getSailingScheduleId());
try {
File excel = fileStoreService.getFileByFilePath(manifestImportEntity.getId());
String fileName = excel.getName();
if (excel.isFile() && excel.exists()) { //判断文件是否存在
Workbook wookbook = null;
//文件流对象
FileInputStream fis = new FileInputStream(excel);
if (fileName.endsWith(".xls")) {
wookbook = new HSSFWorkbook(fis);
}
if (fileName.endsWith(".xlsx")) {
wookbook = new XSSFWorkbook(fis);
}
//开始解析
Sheet sheet = wookbook.getSheetAt(0); //读取sheet 0
int lastRowIndex = sheet.getLastRowNum();
//----------------------执行校验 第一行 第二行表头的格式---------------------
response = checkExcelHead(sheet);
if (response.getImportItemList().size() > 0 && response.getImportItemList().get(0).getFailureReason() != null) {
return response;
}
//将EXCEL中数据转换到实体类
List<Contact> contacts = Lists.newArrayList();
List<Cargo> cargos = Lists.newArrayList();
Manifest manifest = new Manifest();
ManifestRequest request = new ManifestRequest();
for (int rIndex = 2; rIndex <= lastRowIndex + 1; rIndex++) { //遍历行
ManifestImportModel manifestImportModel = new ManifestImportModel();
Row row = sheet.getRow(rIndex);
if (row == null ||row.getCell(0) ==null || row.getCell(0).toString() == "") {
break;
}
Boolean rowFlag = false;
if (row != null) {
if (rIndex == 2) {
if (checkBody(manifestImportModel, rIndex, row) != null) {
setManifestImportModel(manifestImportEntity, list, rIndex, manifestImportModel, row);
continue;
}
} else {
for (int cIndex = 1; cIndex < 6; cIndex++) {
Cell cell = row.getCell(cIndex);
if (cell != null && cell.toString() != "") {
rowFlag = true;
}
}
for (int dIndex = 11; dIndex < 32; dIndex++) {
Cell cell2 = row.getCell(dIndex);
if (cell2 != null && cell2.toString() != "") {
rowFlag = true;
}
}
if (!rowFlag) {
if (checkCargoNotNull(manifestImportEntity, list, rIndex, row)) continue;
} else {
if (checkBody(manifestImportModel, rIndex, row) != null) {
setManifestImportModel(manifestImportEntity, list, rIndex, manifestImportModel, row);
continue;
}
}
}
if (getCellValue(sheet, rIndex, 1) != null) {
Contact sendContact = new Contact();
Contact receiveContact = new Contact();
Contact informContact = new Contact();
//校验数据内容
if (getCellValue(sheet, rIndex, 1).equals("001进出口货物") || getCellValue(sheet, rIndex, 1).equals("003过境货物")) {
} else {
String failureReason = "序号" + (rIndex - 1) + "海关单状态数据不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
InboundPort inboundPort = portService.getByCode(getCellValue(sheet, rIndex, 3));
if (inboundPort == null) {
String failureReason = "序号" + (rIndex - 1) + "装货港港口代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
InboundPort inboundPortLast = portService.getByCode(getCellValue(sheet, rIndex, 4));
if (inboundPortLast == null) {
String failureReason = "序号" + (rIndex - 1) + "卸货港港口代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
InboundBerth inboundBerth = berthService.getByCode(getCellValue(sheet, rIndex, 5));
if (inboundBerth == null) {
String failureReason = "序号" + (rIndex - 1) + "码头代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
String sendCountryName = "";
InBaseCountry inBaseCountry = inBaseCountryService.findByCode(getCellValue(sheet, rIndex, 14));
if (inBaseCountry == null) {
String failureReason = "序号" + (rIndex - 1) + "发货人国家代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
} else {
sendCountryName = inBaseCountry.getName();
}
String receiveCountryName = "";
if (getCellValue(sheet, rIndex, 20) != null && getCellValue(sheet, rIndex, 20) != "") {
InBaseCountry inBaseCountryRe = inBaseCountryService.findByCode(getCellValue(sheet, rIndex, 20));
if (inBaseCountryRe == null) {
String failureReason = "序号" + (rIndex - 1) + "收货人国家代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
} else {
receiveCountryName = inBaseCountryRe.getName();
}
}
String informCountryName = "";
if (getCellValue(sheet, rIndex, 29) != null && getCellValue(sheet, rIndex, 29) != "") {
InBaseCountry inBaseCountryInform = inBaseCountryService.findByCode(getCellValue(sheet, rIndex, 29));
if (inBaseCountryInform == null) {
String failureReason = "序号" + (rIndex - 1) + "通知人国家代码不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
} else {
informCountryName = inBaseCountryInform.getName();
}
}
//开始组装数据
manifest.setSailingScheduleId(manifestImportEntity.getSailingScheduleId());
manifest.setShippingLine(manifestImportEntity.getShippingLine());
manifest.setShippingLineCode(manifestImportEntity.getShippingLineCode());
//录单人
manifest.setAddBy(user);
manifest.setAddByName(user);
//船名
manifest.setVessel(manifestImportEntity.getShipEnName());
//航次
manifest.setVoyage(manifestImportEntity.getImpVoyageCode());
//预抵时间
manifest.setEtdArrivedDate(inboundSailing.getEtdArrivedTime());
//imo编号
manifest.setImoNo(inboundSailing.getImoNo());
//集散标志 默认散杂货 1
manifest.setCntBulkCode("1");
//付款方式默认到付
manifest.setPayType("CC");
//运输条款 付款方式 默认
if (getCellValue(sheet, rIndex, 1).equals("001进出口货物")) {
//海运单状态
manifest.setCustomStatusCode("001");
manifest.setTransferFlag("0");
//运输类型
} else if (getCellValue(sheet, rIndex, 1).equals("003过境货物")) {
manifest.setCustomStatusCode("002");
manifest.setTransferFlag("3");
}
//装货时间
manifest.setBlConsignmentLoadingDate(manifestImportEntity.getLastEtdDepartTime());
//提单号
manifest.setBlNo(getCellValue(sheet, rIndex, 2));
//装货港代码
manifest.setLoadPortCode(getCellValue(sheet, rIndex, 3));
manifest.setLoadPort(inboundPort.getName());
//卸货港代码
manifest.setDischargePortCode(getCellValue(sheet, rIndex, 4));
manifest.setDischargePort(inboundPortLast.getName());
//码头代码
manifest.setDischargeBerthCode(getCellValue(sheet, rIndex, 5));
manifest.setDischargeBerth(inboundBerth.getName());
if((getCellValue(sheet, rIndex, 1)!=null && getCellValue(sheet, rIndex, 1).toString()!="") && rIndex!=2){
contacts.clear();
request.setContacts(contacts);
}
//发货人
sendContact.setContactType("1");
sendContact.setName(getCellValue(sheet, rIndex, 11));
sendContact.setCode(getCellValue(sheet, rIndex, 12));
sendContact.setStreet(getCellValue(sheet, rIndex, 13));
sendContact.setCountryCode(getCellValue(sheet, rIndex, 14));
sendContact.setCountryName(sendCountryName);
if (getCellValue(sheet, rIndex, 15).equals("TE")) {
sendContact.setTele(getCellValue(sheet, rIndex, 16));
} else if (getCellValue(sheet, rIndex, 15).equals("EM")) {
sendContact.setEmail(getCellValue(sheet, rIndex, 16));
} else if (getCellValue(sheet, rIndex, 15).equals("FX")) {
sendContact.setFx(getCellValue(sheet, rIndex, 16));
} else {
String failureReason = "序号" + (rIndex - 1) + "发货人通讯方式不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
if (getCellValue(sheet, rIndex, 15) == null) {
String failureReason = "序号" + (rIndex - 1) + "发货人通讯方式必填";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
//收货人
receiveContact.setContactType("2");
receiveContact.setName(getCellValue(sheet, rIndex, 17));
receiveContact.setCode(getCellValue(sheet, rIndex, 18));
receiveContact.setStreet(getCellValue(sheet, rIndex, 19));
receiveContact.setCountryCode(getCellValue(sheet, rIndex, 20));
receiveContact.setCountryName(receiveCountryName);
if (getCellValue(sheet, rIndex, 21) != null) {
if (getCellValue(sheet, rIndex, 21).equals("TE")) {
receiveContact.setTele(getCellValue(sheet, rIndex, 22));
} else if (getCellValue(sheet, rIndex, 21).equals("EM")) {
receiveContact.setEmail(getCellValue(sheet, rIndex, 22));
} else if (getCellValue(sheet, rIndex, 21).equals("FX")) {
receiveContact.setFx(getCellValue(sheet, rIndex, 22));
} else {
String failureReason = "序号" + (rIndex - 1) + "收货人通讯方式不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
} else {
if (getCellValue(sheet, rIndex, 22) != null) {
String failureReason = "序号" + (rIndex - 1) + "收货人联系号码必填";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
}
receiveContact.setContactName(getCellValue(sheet, rIndex, 23));
if (getCellValue(sheet, rIndex, 24) != null) {
if (getCellValue(sheet, rIndex, 24).equals("TE")) {
receiveContact.setContactTele(getCellValue(sheet, rIndex, 25));
} else if (getCellValue(sheet, rIndex, 24).equals("EM")) {
receiveContact.setContactEmail(getCellValue(sheet, rIndex, 25));
} else if (getCellValue(sheet, rIndex, 24).equals("FX")) {
receiveContact.setContactFx(getCellValue(sheet, rIndex, 25));
} else {
String failureReason = "序号" + (rIndex - 1) + "收货人联系人通讯方式不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
}
//通知人
informContact.setContactType("3");
informContact.setName(getCellValue(sheet, rIndex, 26));
informContact.setCode(row.getCell(27).toString());
informContact.setStreet(getCellValue(sheet, rIndex, 28));
informContact.setCountryCode(getCellValue(sheet, rIndex, 29));
informContact.setCountryName(informCountryName);
if (getCellValue(sheet, rIndex, 30) != null) {
if (getCellValue(sheet, rIndex, 30).equals("TE")) {
informContact.setTele(getCellValue(sheet, rIndex, 31));
} else if (getCellValue(sheet, rIndex, 30).equals("EM")) {
informContact.setEmail(getCellValue(sheet, rIndex, 31));
} else if (getCellValue(sheet, rIndex, 30).equals("FX")) {
informContact.setFx(getCellValue(sheet, rIndex, 31));
} else {
String failureReason = "序号" + (rIndex - 1) + "通知方通讯方式不准确";
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
}
//通讯方式暂时留空
contacts.add(sendContact);
contacts.add(receiveContact);
contacts.add(informContact);
IManifestGroupService service = serviceMap.get(inboundSailing.getOrgGroupCode());
if (service == null) {
service = serviceMap.get("default");
}
//校验toOrder联系人信息
List<String> checkContacts = service.checkContacts(contacts);
if (!CollectionUtils.isEmpty(checkContacts)) {
String failureReason = "序号" + (rIndex - 1) + checkContacts.get(0);
setErrorModel(manifestImportEntity, list, row, failureReason);
continue;
}
request.setManifest(manifest);
request.setContacts(contacts);
}
Cargo cargo = new Cargo();
//货品名
cargo.setCargoDesc(getCellValue(sheet, rIndex, 6));
cargo.setPackageKindCode(getCellValue(sheet, rIndex, 7));
//包装类型代码
ConfDict confDict = confDictService.findByCodeAndDictTypeCode(cargo.getPackageKindCode(), "PACKAGE_TYPE");
if (checkCargo(manifestImportEntity, list, sheet, rIndex, row, confDict)) {
continue;
}
cargo.setPackageKind(confDict.getCnName());
//件数
cargo.setPackageNumber(new BigDecimal(getCellValue(sheet, rIndex, 8)));
//毛重
cargo.setGrossWeight(new BigDecimal(row.getCell(9).toString()));
//体积
if (getCellValue(sheet, rIndex, 10) != "" && getCellValue(sheet, rIndex, 10) != null) {
cargo.setMeasurement(new BigDecimal(row.getCell(10).toString()));
}
//判断如果当前行不是货物信息行，且不是首行，清空全局货物信息,联系人信息
if((getCellValue(sheet, rIndex, 1)!=null && getCellValue(sheet, rIndex, 1).toString()!="") && rIndex!=2){
cargos.clear();
request.setCargos(cargos);
}
cargos.add(cargo);
request.setCargos(cargos);
Boolean rowFlagNew = false;
Row rowNext = sheet.getRow(rIndex + 1);
if (rowNext == null || rowNext.getCell(0) == null ||rowNext.getCell(0).toString() == "") {
rowFlagNew = true;
} else {
for (int cIndex = 1; cIndex < 6; cIndex++) { //遍历列
Cell cell = rowNext.getCell(cIndex);
if (cell != null && cell.toString() != "") {
rowFlagNew = true;
}
}
for (int dIndex = 11; dIndex < 32; dIndex++) { //遍历列
Cell cell2 = rowNext.getCell(dIndex);
if (cell2 != null && cell2.toString() != "") {
rowFlagNew = true;
}
}
}
if (cargos.size() == 1 && getCellValue(sheet, rIndex, 1) == null) {
continue;
}
//如果rowFlag为false 说明下一行有货物信息
if (rowFlagNew) {
List<Cargo> cargoList = request.getCargos();
if(sheet.getRow(rIndex -cargoList.size()+1).getCell(1)==null || sheet.getRow(rIndex-cargoList.size()+1).getCell(1).toString()==""){
contacts.clear();
cargos.clear();
request.setCargos(cargos);
request.setContacts(contacts);
continue;
}
ManifestSaveResponse manifestSaveResponse = new ManifestSaveResponse();
List<String> errors = manifestSaveService.saveManifest(request, user, manifestSaveResponse);
if (errors.size() > 0) {
if (errors.get(0).equals("操作失败，舱单已存在")) {
manifestImportModel.setFailureReason("序号" + (rIndex - 1) + "舱单已存在");
} else {
manifestImportModel.setFailureReason("序号" + (rIndex - 1) + errors.get(0));
}
manifestImportModel.setShipEnName(manifestImportEntity.getShipEnName());
manifestImportModel.setImpVoyageCode(manifestImportEntity.getImpVoyageCode());
manifestImportModel.setShippingLine(manifestImportEntity.getShippingLine());
manifestImportModel.setBillNo(request.getManifest().getBlNo());
manifestImportModel.setImportStatus("导入失败");
} else {
manifestImportModel.setShipEnName(manifestImportEntity.getShipEnName());
manifestImportModel.setImpVoyageCode(manifestImportEntity.getImpVoyageCode());
manifestImportModel.setShippingLine(manifestImportEntity.getShippingLine());
manifestImportModel.setBillNo(request.getManifest().getBlNo());
manifestImportModel.setImportStatus("导入成功");
}
list.add(manifestImportModel);
response.setImportItemList(list);
contacts.clear();
cargos.clear();
request.setCargos(cargos);
request.setContacts(contacts);
manifest = new Manifest();
}
}
}
} else {
System.out.println("找不到指定的文件");
}
} catch (Exception e) {
e.printStackTrace();
}
response.setImportItemList(list);
return response;
}
功能总结 做这项功能时，由于导入的文件内容过多，写的很疲劳，也很烦，导致了代码的质量不够高，出现恶性循环，总的来说，难点主要集中在，判断下一行有没有货物，是不是货物信息行，如果是货物信息行，对象和集合要如何处理。
校验和插入数据不要进行耦合，这样可以让代码更清晰。
网友评论