Apache POI和EasyExcel 第五集：Apache POI的Excel读取不同类型的数据

Apache POI和EasyExcel 第五集：Apache POI的Excel读取不同类型的数据

---

---

一、资源

• 什么是Apache POI
  • Apache POI
• 不同类型的数据的表格（xls）
  • 链接：https://pan.baidu.com/s/1K4p80oBNyecXnCtwBcbRwA
    提取码：0916

---

---

二、代码实现

    @Test
    public void testCellType() throws Exception {

        InputStream is = new FileInputStream(path + "会员消费商品明细表.xls");
        Workbook workbook = new HSSFWorkbook(is);
        Sheet sheet = workbook.getSheetAt(0);

        // 读取标题所有内容
        Row rowTitle = sheet.getRow(0);

        // 行不为空
        if (rowTitle != null) {

            // 读取cell
            int cellCount = rowTitle.getPhysicalNumberOfCells();
            for (int cellNum = 0; cellNum < cellCount; cellNum++) {
                Cell cell = rowTitle.getCell(cellNum);
                if (cell != null) {
                    String cellValue = cell.getStringCellValue();
                    System.out.print(cellValue + "|");
                }
            }
            System.out.println();
        }


        // 读取商品列表数据
        int rowCount = sheet.getPhysicalNumberOfRows();

        for (int rowNum = 1; rowNum < rowCount; rowNum++) {

            Row rowData = sheet.getRow(rowNum);

            // 行不为空
            if (rowData != null && rowTitle != null) {

                // 读取cell
                int cellCount = rowTitle.getPhysicalNumberOfCells();

                for (int cellNum = 0; cellNum < cellCount; cellNum++) {

                    System.out.print("【" + (rowNum + 1) + "-" + (cellNum + 1) + "】");

                    Cell cell = rowData.getCell(cellNum);

                    if (cell != null) {

                        int cellType = cell.getCellType();

                        //判断单元格数据类型
                        String cellValue = "";

                        switch (cellType) {
                            case HSSFCell.CELL_TYPE_STRING:
                                //字符串
                                System.out.print("【STRING】");
                                cellValue = cell.getStringCellValue();
                                break;
                            case HSSFCell.CELL_TYPE_BOOLEAN:
                                //布尔
                                System.out.print("【BOOLEAN】");
                                cellValue = String.valueOf(cell.getBooleanCellValue());
                                break;
                            case HSSFCell.CELL_TYPE_BLANK:
                                //空
                                System.out.print("【BLANK】");
                                break;
                            case HSSFCell.CELL_TYPE_NUMERIC:
                                System.out.print("【NUMERIC】");
                                if (HSSFDateUtil.isCellDateFormatted(cell)) {

                                    // 日期
                                    System.out.print("【日期】");

                                    Date date = cell.getDateCellValue();
                                    cellValue = new DateTime(date).toString("yyyy-MM-dd");
                                } else {

                                    // 不是日期格式，则防止当数字过长时以科学计数法显示
                                    System.out.print("【转换成字符串】");

                                    cell.setCellType(HSSFCell.CELL_TYPE_STRING);
                                    cellValue = cell.toString();
                                }
                                break;
                            case Cell.CELL_TYPE_ERROR:
                                System.out.print("【数据类型错误】");
                                break;
                        }
                        System.out.println(cellValue);
                    }
                }
            }
        }
        is.close();
    }