注意：
1.区分后缀名
后缀名为xlsx的Excel文件使用XSSFWorkbook对象进行读取文件数据；
后缀名为xls的Excel文件使用HSSFWorkbook对象进行读取文件数据；
2.注意是否有样式，获取有效数据行数
需要注意要导入的Excel文件有没有样式。如果有样式，可能对有效数据行数获取也有影响。因为在使用POI中获取excel文件最后有效行数是获取的带样式的最后有效行数。而非真正有效行数。
如果没有带样式，直接使用()获取最后有效行数。
如果带有样式，可以使用以下方法进行获取最后有效行数。可以直接输入要读取的Excel文件路径。
/**
* 获取有格式的xls的最后行数
* */
public Integer getXlsLastRow(String destPath) {
Workbook wb = null;
try {
wb = new HSSFWorkbook(new FileInputStream(destPath));
} catch (Exception e) {
();
}
Sheet sheet = (0);
CellReference cellReference = new CellReference("A4");
for (int i = cellReference.getRow(); i <= sheet.getLastRowNum(); ) {
Row row = sheet.getRow(i);
if (row == null) {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
continue;
}
boolean flag = false;
for (Cell c : row) {
if (c.getCellType() != BLANK) {
flag = true;
break;
}
}
if (flag) {
i++;
continue;
} else {
if (i == sheet.getLastRowNum()) {
sheet.removeRow(row);
} else {
sheet.shiftRows(i + 1, sheet.getLastRowNum(), -1);
}
}
}
return sheet.getLastRowNum() + 1;
}
3.读取Excel文件中对电话号码的处理。【1.184382753E7 转换为 18438275307】
从Excel文件中读取下来的电话号码因数据过大，长度过长可能会被自动转成科学技术法的形式，需要进行处理才能够使用。处理如下：
DecimalFormat df = new DecimalFormat("#");
double str = Double.parseDouble(row.getCell(5).toString());
String format = df.format(str);
得到的format就是正确格式的电话号码
4.读取Excel文件对日期格式的处理 【17-四月-2020 转换成 2020-4-17】
/**
* 17-四月-2020 转换成 2020-4-17
* */
public static String covertDate(String date){
String[] split = ("-");
(split[1]);
switch(split[1]){
case "一月":{split[1]="1";break;}
case "二月":{split[1]="2";break;}
case "三月":{split[1]="3";break;}
case "四月":{split[1]="4";break;}
case "五月":{split[1]="5";break;}
case "六月":{split[1]="6";break;}
case "七月":{split[1]="7";break;}
case "八月":{split[1]="8";break;}
case "九月":{split[1]="9";break;}
case "十月":{split[1]="10";break;}
case "十一月":{split[1]="11";break;}
case "十二月":{split[1]="12";break;}
default:{split[1]="0";};
}
String time=split[2]+'-'+split[1]+'-'+split[0];
return time;
}
读取Excel文件，此处只展示后缀为xlsx的Excel文件数据的导入
@Override
public void importOneExcel(String destPath) {
try {
//创建输入流
InputStream inputStream = new FileInputStream(destPath);
Workbook workbook = null;
try {
//直接从流中读取文件数据
workbook = new XSSFWorkbook(inputStream);
} catch (IOException e) {
();
}
Sheet sheet = (0);
List<TelPhoneDetail> telPhoneDetailList = new ArrayList<>();
for (int i = 1; i < sheet.getLastRowNum() + 1; i++) {
Row row = sheet.getRow(i);
CustomerDevelopment customerDevelopment = new CustomerDevelopment();
customerDevelopment.setCompanyName(row.getCell(0).toString());
customerDevelopment.setAddress(row.getCell(1).toString());
customerDevelopment.setArea(row.getCell(2).toString());
customerDevelopment.setLegalPerson(row.getCell(4).toString());
if (row.getCell(5).toString().contains("-")) {
customerDevelopment.setTelPhone(row.getCell(5).toString());
} else {
DecimalFormat df = new DecimalFormat("#");
double str = Double.parseDouble(row.getCell(5).toString());
String format = df.format(str);
customerDevelopment.setTelPhone(format);
}
customerDevelopment.setVehicleCount(Double.valueOf(row.getCell(6).toString()).intValue());
CustomerDevelopment oldCustomerDevelopment = customerDevelopmentBatis.findByCompanyName(customerDevelopment.getCompanyName());
if (oldCustomerDevelopment == null) {
//保存customerDevelopment对象，保存对象并返回主键
customerDevelopmentMapper.saveAndFlush(customerDevelopment);
if(StringUtils.isEmpty(customerDevelopment.getTelPhone())){
TelPhoneDetail telPhoneDetail = new TelPhoneDetail();
telPhoneDetail.setCdId(customerDevelopment.getId());
telPhoneDetail.setTelStatus(1);
telPhoneDetail.setTelNumber(customerDevelopment.getTelPhone().trim());
telPhoneDetail.setTelDescription("");
telPhoneDetail.setTelImportBatches(1);
telPhoneDetail.setIsRepeat(0);
telPhoneDetail.setChannelName("渠道1");
telPhoneDetailList.add(telPhoneDetail);
}
} else {
List<String> telPhones = telPhoneDetailBatis.findTelPhoneDetailByCdId(oldCustomerDevelopment.getId());
if (!telPhones.contains(customerDevelopment.getTelPhone().trim())) {
TelPhoneDetail telPhoneDetail = new TelPhoneDetail();
telPhoneDetail.setCdId(oldCustomerDevelopment.getId());
telPhoneDetail.setTelStatus(1);
telPhoneDetail.setTelNumber(customerDevelopment.getTelPhone().trim());
telPhoneDetail.setTelDescription("");
telPhoneDetail.setTelImportBatches(1);
telPhoneDetail.setIsRepeat(0);
telPhoneDetail.setChannelName("渠道1");
telPhoneDetailList.add(telPhoneDetail);
} else {
TelPhoneDetail telPhoneDetail = new TelPhoneDetail();
telPhoneDetail.setCdId(oldCustomerDevelopment.getId());
telPhoneDetail.setTelStatus(1);
telPhoneDetail.setTelNumber(customerDevelopment.getTelPhone().trim());
telPhoneDetail.setTelDescription("");
telPhoneDetail.setTelImportBatches(1);
telPhoneDetail.setIsRepeat(1);
telPhoneDetail.setChannelName("渠道1");
telPhoneDetailList.add(telPhoneDetail);
}
}
}
telPhoneDetailMapper.saveAll(telPhoneDetailList);
} catch (FileNotFoundException e) {
();
}
}