Java Excel 读取

项目场景：

前几天要读取第三方公司放在ftp中的Excel文件，所以就写了这个功能，这个功能再Java 8是不推荐使用的，如果不是Java 8 大可以使用这一套。

---

问题描述：

其实hutool这个工具jar也是很好用的，想了解的都可以看下，里面相当于所有工具类的集成。但是不能导入这些jar包，所以只能手写了，好下面看代码。

1.先创建一个Excel文件类

public class ZengXianBean {
    /**产品名称*/
    private String productName;
    /**保单号*/
    private String contno;
    /**保额*/
    private String amount;
    /**投保人姓名*/
    private String appntname;
    /**被保人姓名*/
    private String insuredname;
    /**投保人身份证号码*/
    private String appntIdno;
    /**被保险人身份证号码*/
    private String insuredIDNO;
    /**投保人手机号*/
    private String appntMobile;
    /** 投保日期*/
    private String insuranceDate;
    /**生效日期*/
    private String effectiveDate;
    /**终止日期*/
    private String expirationDate;
    /**被保人关系*/
    private String insuredRelation;
    /**保险期间 */
    private String insuyear;

    public String getProductName() {
        return productName;
    }

    public void setProductName(String productName) {
        this.productName = productName;
    }

    public String getContno() {
        return contno;
    }

    public void setContno(String contno) {
        this.contno = contno;
    }

    public String getAmount() {
        return amount;
    }

    public void setAmount(String amount) {
        this.amount = amount;
    }

    public String getAppntname() {
        return appntname;
    }

    public void setAppntname(String appntname) {
        this.appntname = appntname;
    }

    public String getInsuredname() {
        return insuredname;
    }

    public void setInsuredname(String insuredname) {
        this.insuredname = insuredname;
    }

    public String getAppntIdno() {
        return appntIdno;
    }

    public void setAppntIdno(String appntIdno) {
        this.appntIdno = appntIdno;
    }

    public String getInsuredIDNO() {
        return insuredIDNO;
    }

    public void setInsuredIDNO(String insuredIDNO) {
        this.insuredIDNO = insuredIDNO;
    }

    public String getAppntMobile() {
        return appntMobile;
    }

    public void setAppntMobile(String appntMobile) {
        this.appntMobile = appntMobile;
    }

    public String getInsuranceDate() {
        return insuranceDate;
    }

    public void setInsuranceDate(String insuranceDate) {
        this.insuranceDate = insuranceDate;
    }

    public String getEffectiveDate() {
        return effectiveDate;
    }

    public void setEffectiveDate(String effectiveDate) {
        this.effectiveDate = effectiveDate;
    }

    public String getExpirationDate() {
        return expirationDate;
    }

    public void setExpirationDate(String expirationDate) {
        this.expirationDate = expirationDate;
    }

    public String getInsuredRelation() {
        return insuredRelation;
    }

    public void setInsuredRelation(String insuredRelation) {
        this.insuredRelation = insuredRelation;
    }

    public String getInsuyear() {
        return insuyear;
    }

    public void setInsuyear(String insuyear) {
        this.insuyear = insuyear;
    }

    @Override
    public String toString() {
        return "ZengXianBean{" +
                "productName='" + productName + '\'' +
                ", contno='" + contno + '\'' +
                ", amount='" + amount + '\'' +
                ", appntname='" + appntname + '\'' +
                ", insuredname='" + insuredname + '\'' +
                ", appntIdno='" + appntIdno + '\'' +
                ", insuredIDNO='" + insuredIDNO + '\'' +
                ", appntMobile='" + appntMobile + '\'' +
                ", insuranceDate='" + insuranceDate + '\'' +
                ", effectiveDate='" + effectiveDate + '\'' +
                ", expirationDate='" + expirationDate + '\'' +
                ", insuredRelation='" + insuredRelation + '\'' +
                ", insuyear='" + insuyear + '\'' +
                '}';
    }
}

2.封装的方法

 /**
     * 获取WorkBook  根据.xls和.xlsx获取不同的WorkBook
     *
     * @param is       文件输入流
     * @param filename 文件名
     * @return
     */
    private Workbook getWorkBook(InputStream is, String filename) {
        //获得文件名
        //创建Workbook工作薄对象，表示整个excel
        Workbook workbook = null;
        String suffix = filename.substring(filename.lastIndexOf(".") + 1);
        System.out.println("后缀：" + suffix);
        try {
            //获取excel文件的io流
            //根据文件后缀名不同(xls和xlsx)获得不同的Workbook实现类对象
            if ("xls".equals(suffix)) {
                System.out.println("使用的HSSF");
                //2003
                workbook = new HSSFWorkbook(is);
            } else if ("xlsx".equals(suffix)) {
                System.out.println("使用的XSSF");
                //2007
                workbook = new XSSFWorkbook(is);
            }
        } catch (IOException e) {
            e.printStackTrace();
        }
        return workbook;
    }

    private String getCellFormatValue(Cell cell) {
        String cellValue = "";
        if (cell == null) {
            return cellValue;
        }
        //把数字当成String来读，避免出现1读成1.0的情况
        if (cell.getCellType() == Cell.CELL_TYPE_NUMERIC) {
            cell.setCellType(Cell.CELL_TYPE_STRING);
        }
        //判断数据的类型
        switch (cell.getCellType()) {
            case Cell.CELL_TYPE_NUMERIC: //数字
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case Cell.CELL_TYPE_STRING: //字符串
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case Cell.CELL_TYPE_BOOLEAN: //Boolean
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case Cell.CELL_TYPE_FORMULA: //公式
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case Cell.CELL_TYPE_BLANK: //空值
                cellValue = "";
                break;
            case Cell.CELL_TYPE_ERROR: //故障
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

3.测试

@Test
    public void test04() throws FileNotFoundException {
        ZengXianBean zengXianBean  = new ZengXianBean();
        ArrayList arrayList = new ArrayList();
        String filePath = "C:\\Users\\49800\\Desktop\\fsdownload\\NuoYaGiftReport20200910.xls";
        File file = new File(filePath);
        InputStream inputStream = new FileInputStream(file);
        String name = file.getName(); // 文件名
        String suffix = name.substring(name.lastIndexOf(".") + 1);
        System.out.println("后缀：" + suffix);
        Workbook workBook = getWorkBook(inputStream, name);
        int numberOfSheets = workBook.getNumberOfSheets(); // 获取页数
        for (int i = 0; i < numberOfSheets; i++) {
            org.apache.poi.ss.usermodel.Sheet sheet = workBook.getSheetAt(i);
            Row row;
            int rowNum = sheet.getLastRowNum();
            for (int j = 1; j <= rowNum; j++) {
                row = sheet.getRow(j);
                if (row != null) {
                    zengXianBean = new ZengXianBean();
                    zengXianBean.setProductName(getCellFormatValue(row.getCell(0)).trim()); // 产品名称a
                    zengXianBean.setContno(getCellFormatValue(row.getCell(1)).trim()); // 保单号b
                    zengXianBean.setAmount(getCellFormatValue(row.getCell(2)).trim()); // 保额c
                    zengXianBean.setAppntname(getCellFormatValue(row.getCell(3)).trim()); // 投保人姓名d
                    zengXianBean.setInsuredname(getCellFormatValue(row.getCell(4)).trim()); // 被保人姓名e
                    zengXianBean.setAppntIdno(getCellFormatValue(row.getCell(5)).trim()); // 投保人身份证f
                    zengXianBean.setInsuredIDNO(getCellFormatValue(row.getCell(6)).trim()); // 被保人身份证号码g
                    zengXianBean.setInsuredRelation(getCellFormatValue(row.getCell(7)).trim()); //被保人关系h
                    zengXianBean.setInsuyear(getCellFormatValue(row.getCell(8)).trim()); // 保险期间i
                    zengXianBean.setAppntMobile(getCellFormatValue(row.getCell(9)).trim()); // 投保人手机号j
                    zengXianBean.setInsuranceDate(getCellFormatValue(row.getCell(10)).trim()); // 投保日期j
                    zengXianBean.setEffectiveDate(getCellFormatValue(row.getCell(11)).trim()); // 生效日期k
                    zengXianBean.setExpirationDate(getCellFormatValue(row.getCell(12)).trim()); // 失效日期l
                }
                arrayList.add(zengXianBean);
            }
        }
    }

---