大致的思路：先使用XSSF读取到数据，保存在list中的对象，验证数据，循环添加
XSSF的过程：读取所有行数–》开始循环（第一层）—》读取列数—》开始循环（第二层）–》读取当前行列的数据添加到对象中—》循环结束—》将对象add到list中
HSSF的思路差不多
HSSF：读取.xsl文件也就是03版的Excel
XSSF：读取.xslx文件也就是07版的Excel
使用HSSF/XSSF会非常耗费内存建议使用EasyExcel
EasyExcel的使用
public Result dataimport(String path) {
Result result = new Result<>();
SimpleDateFormat dateFormat = new SimpleDateFormat("yyyy/MM/dd");
List bizCardqualities = new ArrayList<>();
XSSFWorkbook workbook = null;
XSSFSheet sheet = null;
XSSFCell cell=null;
// 读取数据
try {
//开始读取
workbook = new XSSFWorkbook(new FileInputStream(new File(uploadpath +"//"+path)));
sheet = workbook.getSheet("damage tracing");
int lastRowNum = ExcelUtils.getRowNums(sheet);
for (int i = 1; i <= lastRowNum; i++) {
BizCardquality bizCardquality = new BizCardquality();
XSSFRow row = sheet.getRow(i);
List list = new ArrayList();
short lastCellNum = row.getLastCellNum();
for (int j = 0; j < lastCellNum; j++) {
//获取数据
cell = row.getCell(j);
if (null == cell) continue;
cell.setCellType(1);
String cellValue = ExcelUtils.getXCellValue(cell);
cellValue = cellValue.trim();
list.add(cellValue);
if (cellValue != null) {
if (j == 0) {
// 报告编号
bizCardquality.setCqReportno(cellValue);
continue;
}
if (j == 1) {
// VIN码
bizCardquality.setCqVehicleVin(cellValue);
continue;
}
if (j == 2) {
// 车型
bizCardquality.setCqVehicleModel(cellValue);
continue;
}
if (j == 3) {
// 点检地点
bizCardquality.setCqCheckingPoint(cellValue);
//填充质损场站
String station = getStationIdBystsAbbrename(cellValue.substring(0,cellValue.indexOf("-")));
if (station==null){
return Result.error("点检地点未查询到");
}
bizCardquality.setCqDamageplace(station);
continue;
}
if (j == 4) {
// 发动机缸数
bizCardquality.setCqTerminal(cellValue);
continue;
}
if (j == 5) {
// 方向
bizCardquality.setCqDerection(cellValue);
continue;
}
if (j == 6) {
// 班次号
bizCardquality.setCqTrainNumber(cellValue);
continue;
}
if (j == 7) {
//实际发运时间ATD
if(StringUtils.isEmpty(cellValue)){
bizCardquality.setCqAtd(null);
continue;
}
bizCardquality.setCqAtd((dateFormat.parse(cellValue)));
continue;
}
if (j == 8) {
//"ATD WK
//发运周数"
bizCardquality.setCqAtdWk(cellValue);
continue;
}
if (j == 9) {
//"ATA
//实际到达时间"
if(StringUtils.isEmpty(cellValue)){
bizCardquality.setCqAta(null);
continue;
}
bizCardquality.setCqAta((dateFormat.parse(cellValue)));
continue;
}
if (j == 10) {
//"ATA WK
//到达周数"
bizCardquality.setCqAtaWk(cellValue);
continue;
}
if (j == 11) {
// 损坏编码
bizCardquality.setCqDamagecode(cellValue);
continue;
}
if (j == 12) {
// 损坏部位编码
bizCardquality.setCqDamagepartcode(cellValue);
continue;
}
// 损坏类型编码
if (j == 13) {
bizCardquality.setCqDamagetypecode(cellValue);
continue;
}
if (j == 14) {
// 损坏部位
bizCardquality.setCqDamagepart(cellValue);
continue;
}
if (j == 15) {
// 损坏类型
bizCardquality.setCqDamagetype(cellValue);
continue;
}
if (j == 16) {
//Instruction from vv vv指令
bizCardquality.setCqInstructions(cellValue);
}
if (j == 17) {
//Qualitative nature 质损性质（PDI)
bizCardquality.setCqQualitativeNature(cellValue);
}
if (j == 18) {
//Damgede typetwo
bizCardquality.setCqDamagetypeTwo(cellValue);
}
if (j == 19) {
// 损坏初始
bizCardquality.setCqDamagefirstseen(cellValue);
String station = getStationIdBystsAbbrename(cellValue.substring(0,cellValue.indexOf("-")));
if (station==null){
return Result.error("损坏初始地未查询到");
}
bizCardquality.setCqFirstdamageplace(station);
continue;
}
if (j == 20) {
//责任方
bizCardquality.setCqResponsibleparty(cellValue);
continue;
}
if (j == 21) {
//检验方
bizCardquality.setCqInspectionGroup(cellValue);
continue;
}
if (j == 22) {
//检验日期
if(StringUtils.isEmpty(cellValue)){
bizCardquality.setCqInspectionDate(null);
continue;
}
bizCardquality.setCqInspectionDate((dateFormat.parse(cellValue)));
continue;
}
if (j == 23) {
//文件地址
bizCardquality.setCqPath(cellValue);
continue;
}
if (j == 24) {
// 备注
bizCardquality.setCqRemark(cellValue);
continue;
}
}
}
bizCardqualities.add(bizCardquality);
}
}
catch (IOException | ParseException e) {
log.error("读取数据失败",e);
e.printStackTrace();
}
if (bizCardqualities != null) {
for (BizCardquality bizCardquality : bizCardqualities) {
//校验是否有重复数据
Boolean check =bizCardqualityMapper.insertCheck(bizCardquality)==null?false:true;
//校验Vin是否有效
Boolean checkVin =bizCardqualityMapper.VinCheck(bizCardquality.getCqVehicleVin())==null?true:false;
if(checkVin){
result.setSuccess(false);
result.setMessage("未找到该车辆VIN码："+bizCardquality.getCqVehicleVin()+"不存在!");
return result;
}
if(check){
result.setSuccess(false);
result.setMessage("添加失败，"+bizCardquality.getCqDamagecode()+"或"+bizCardquality.getCqVehicleVin()+"重复!");
return result;
}
}
for (BizCardquality bizCardquality : bizCardqualities) {
// 添加
int insert = bizCardqualityMapper.insert(bizCardquality);
//通过vin修改车辆信息为质损
bizCardqualityMapper.updateCar(1,bizCardquality.getCqVehicleVin());
if (insert == 0) {
result.setSuccess(false);
result.setMessage("导入失败!");
return result;
}
try {
// 文件保存
BizUpfileinfo upfileinfo = new BizUpfileinfo();
// 相对路径
upfileinfo.setUfiPath(path);
// 业务id
upfileinfo.setUfiBizid(bizCardquality.getId());
// 业务名称
upfileinfo.setUfiBizname("车辆质损记录导入");
upfileinfoService.insertNewPosition(upfileinfo);
} catch (IOException ioException) {
ioException.printStackTrace();
}
}
}
return result;
}
