如何获取xlsx的Excel文件行数？xlsx的Excel文件使用方法 即时

注意：

1.区分后缀名

后缀名为xlsx的Excel文件使用XSSFWorkbook对象进行读取文件数据；

(相关资料图)

后缀名为xls的Excel文件使用HSSFWorkbook对象进行读取文件数据；

2.注意是否有样式，获取有效数据行数

需要注意要导入的Excel文件有没有样式。如果有样式，可能对有效数据行数获取也有影响。因为在使用POI中获取excel文件最后有效行数是获取的带样式的最后有效行数。而非真正有效行数。

如果没有带样式，直接使用sheet.getLastRowNum()获取最后有效行数。

如果带有样式，可以使用以下方法进行获取最后有效行数。可以直接输入要读取的Excel文件路径。

/*** 获取有格式的xls的最后行数* */public Integer getXlsLastRow(String destPath) {Workbook wb = null;     try {wb = new HSSFWorkbook(new FileInputStream(destPath));    } catch (Exception e) {e.printStackTrace();    }     Sheet sheet = wb.getSheetAt(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 = date.split("-");

System.out.println(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文件数据的导入

@Overridepublic void importOneExcel(String destPath) {try {

//创建输入流         InputStream inputStream = new FileInputStream(destPath);         Workbook workbook = null;         try {

//直接从流中读取文件数据            workbook = new XSSFWorkbook(inputStream);        } catch (IOException e) {e.printStackTrace();        }         Sheet sheet = workbook.getSheetAt(0);         List  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 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) {e.printStackTrace();    }}