Java Excel 读取

古城微笑少年丶 2022-12-06 01:12 230阅读 0赞

# 项目场景： #

前几天要读取第三方公司放在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);
                }
            }
        }

--------------------