java使用poi导入,java使用poi导入非标EXCEL

最新推荐文章于 2024-07-26 10:35:42 发布
最新推荐文章于 2024-07-26 10:35:42 发布
阅读量279 收藏
点赞数
文章标签： java使用poi导入

public ManifestImportResponse importManifest(ManifestImportModel manifestImportEntity, String user) {

ManifestImportResponse response = new ManifestImportResponse();

List 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 contacts = Lists.newArrayList();

List 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 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 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 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;

}

功能总结 做这项功能时，由于导入的文件内容过多，写的很疲劳，也很烦，导致了代码的质量不够高，出现恶性循环，总的来说，难点主要集中在，判断下一行有没有货物，是不是货物信息行，如果是货物信息行，对象和集合要如何处理。

校验和插入数据不要进行耦合，这样可以让代码更清晰。

小米智能生活
关注
java 不规则excel导入_随手笔记（二十八）——— Java解析不规则Excel
weixin_30878111的博客
02-24 690
@Overridepublic R stackExcelProduct(Integer id, Integer fileId) {//1.通过fileId定位文件FilesDO filesDO = FilesService.get(fileId);String filePath = filesDO.getFilePath();//2.对文件进行解析Integer aId = readExcelTo...
java 不规则excel导入_java生成不规则xls相关
weixin_42401356的博客
02-24 388
展开全部使用poi来生成xls文件：引用的包poi-3.2.final.jar;要了解e5a48de588b63231313335323631343130323136353331333339666162的类HSSFWorkbook,HSSFSheet,HSSFRow,HSSFCellHSSFWorkbook workbook=new HSSFWorkbook();得到一个Excel文件的引用HSS...
java Poi导入
12-27
其中 包含poi使用jar包，。。。。。。。。。。。。。。。。
java使用poi实现Excel文件导入解析
最新发布
qq_33651286的博客
07-26 444
引用Maven依赖如下： <dependency> <groupId>org.apache.poi</groupId> <artifactId>poi</artifactId> <version>5.2.2</version> </dependency> <dependency>
java 不规则excel导入_工具类篇——Java之Excel导入生成
weixin_29190651的博客
02-16 678
packagecom.excel;importjava.io.FileNotFoundException;importjava.io.FileOutputStream;importjava.io.IOException;importjava.io.OutputStream;importjava.text.SimpleDateFormat;importjava.util.ArrayList;impo...
java poi导入功能
shannon8的博客
07-30 1862
package com.suning.mercury.service.mock.impl; import java.io.File; import java.io.FileInputStream; import java.io.InputStream; import java.text.SimpleDateFormat; import java.util.ArrayList; impo...
Java 使用poi导入excel 并使用xml做数据验证
07-19
Apache POI是一个流行的库，它允许开发者使用Java来读取、写入和修改Microsoft Office格式的文件，包括Excel（.xlsx, .xls）。在本项目中，我们结合了POI库和XML技术来实现Excel数据的验证与导入数据库。 首先，...
Java 中POI 导入EXCEL2003 和EXCEL2007的实现方法
08-29
Java 中POI 导入EXCEL2003 和EXCEL2007的实现方法是使用 Apache POI 库来实现的，该库提供了一个 Java API 用于读写 Microsoft Office 文件，包括 Excel 文件。本文将介绍如何使用 POI 库来实现 Java 中的 EXCEL2003...
java 利用POI 导入导出Excel
06-03
前几天项目上需要加上导入导出，在网上找了一下，么有...于是自己写，源码里所需要的jar都有，从jsp界面进行Excel的导入导出，并可用于上传和下载。如有所用，喜不自禁,分享，开源的意义。 写的不是特别好，不喜勿喷！
java_poi导入excel通用工具类
09-09
这个"java_poi导入excel通用工具类"是利用Java的POI库和一些额外的技术来实现对Excel数据的导入功能，使得开发人员能够方便地将Excel数据转化为Java对象或者对已有对象进行填充。下面我们将深入探讨相关的知识点。 ...
Java poi导入导出EXCEL工具类（兼容各版本）
08-21
概述：Java poi导入导出EXCEL工具类（兼容各版本） 一、功能说明 允许同时导入或导出多个sheet，同一sheet可同时存在多个数据块，按数据块划分处理数据。 二、配置文件示例及详细说明 1、导入xml配置示例如下（见src/main/resources/import-config.xml） 2、导出xml配置示例如下（见src/main/resources/export-config.xml） 三、调用示例 1、导入：src/test/java/com/reports/test/ExcelImportReportTest.java 2、导出：src/test/java/com/reports/test/ExcelExportReportTest.java 四、测试用例及数据 路径src/test/resources/
Java使用POI实现Excel报表的导入和导出
06-06
利用开源组件POI3.0.2，用java语言实现了动态导出Excel文档，并且也实现了Excel文档的导入。有完整的例子，可直接使用。ExportExcel类为导出Excel报表通用类。 1.将源代码导入eclipse中 2.部署项目，启动tomcat服务器 3.web页面具体路径：http://localhost:80/poi 4.点击Read excel 2003 or 2007，可以读取2003或2007版Excel 5.点击Export excel 2003，可以导出Excel 2003版 6.点击Export excel 2007，可以导出Excel 2007版
Java poi 实现excel导入导出
10-23
Java使用poi实现excel导入导出，导入导出已做成通用方法，内附说明文档
java_poi实现excel导入导出
09-01
java_poi实现excel导入导出功能，有详细的注解
java poi 导入导出_java poi 导入导出
weixin_42465051的博客
02-12 132
导入excel不上传文件到服务器@RequestMapping(value = "intoDatabases")@ResponseBodypublic Map intoDatabases(HttpServletRequest request,MultipartFile file)throws Exception {Map map = new HashMap();if (file.isEmpty()...
Java POI 导入/导出 Excel
cui884658的博客
09-08 402
引入依赖 # EasyExcel 也引用了 POI，所以导入这个包 EasyExcel 和 Poi 都可以使用 <dependency> <groupId>com.alibaba</groupId> <artifactId>easyexcel</artifactId> <version>2.2.3</version> </dependency> POI 导入 public void im
java poi excel导出导入
weixin_43109130的博客
04-27 161
java excel导出导入 1.导出excel模板 /** * 导出模板 * * @param request * @return */ @PostMapping("/getDictionaryTemplate") @ResponseBody @ApiOperation("导出模板") @ControllerLog(description = "导出模板", logLevel = 6) public Respon
评论
添加红包

请填写红包祝福语或标题

个

红包个数最小为10个

元

红包金额最低5元

当前余额3.43元 前往充值 >
需支付：10.00元
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付元
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明：

1.余额是钱包充值的虚拟货币，按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载，可以购买VIP、付费专栏及课程。

余额充值