使用POI导入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文件数据的导入

@Override
public 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<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) {
        e.printStackTrace();
    }
}