poi读取excel

一、poi读取excel

 public static void main(String[] args) throws Exception{
//        String pathName = "/Users/mac/Documents/work/record/datacenter/dc-wmq-resource/commands.xls";
        String pathName = "/Users/mac/Downloads/demo_test_data.xlsx";
        File file = null;
        FileInputStream fileInputStream = null;
//        HSSFWorkbook workbook = null;
        Workbook workbook = null;
        //创建返回对象，把每行中的值作为一个数组，所有行作为一个集合返回
        List<String[]> list = new ArrayList<String[]>();
        try {
            file = new File(pathName);
            fileInputStream = new FileInputStream(file);

            if(pathName.contains(".xlsx")){
                workbook = new XSSFWorkbook(fileInputStream); //2007+版本支持
            } else {
                workbook = new HSSFWorkbook(fileInputStream); //老版
            }
            for (int sheetNum = 0; sheetNum < workbook.getNumberOfSheets(); sheetNum++){
                Sheet sheet = workbook.getSheetAt(sheetNum);
                if(sheet == null)
                    break;

                //获得当前sheet的开始行
                int firstRowNum  = sheet.getFirstRowNum();
                //获得当前sheet的结束行
                int lastRowNum = sheet.getLastRowNum();

                //循环所有行
                for(int rowNum = firstRowNum; rowNum <= lastRowNum; rowNum++){
                    //获得当前行
                    Row row = sheet.getRow(rowNum);
                    if(row == null){
                        continue;
                    }
                    //获得当前行的开始列
                    int firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();
                    String[] cells = new String[row.getPhysicalNumberOfCells()];
                    //循环当前行
                    for(int cellNum = firstCellNum; cellNum < lastCellNum;cellNum++){
                        Cell cell = row.getCell(cellNum);
                        cells[cellNum] = getCellValue(cell);
                    }
                    list.add(cells);
                }
            }
        } catch (Exception e) {
            e.printStackTrace();
        } finally {
            try {
                if(workbook != null){
                    workbook.close();
                }

                if(fileInputStream != null){
                    fileInputStream.close();
                }

            } catch (IOException e) {
                e.printStackTrace();
            }
        }



    }

    public static String getCellValue(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;
    }

二、所需jar

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
    <version>3.13</version>
</dependency>

<dependency>
    <groupId>dom4j</groupId>
    <artifactId>dom4j</artifactId>
    <version>1.6.1</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml</artifactId>
    <version>3.13</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-ooxml-schemas</artifactId>
    <version>3.13</version>
</dependency>

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi-scratchpad</artifactId>
    <version>3.13</version>
</dependency>

<dependency>
    <groupId>org.apache.xmlbeans</groupId>
    <artifactId>xmlbeans</artifactId>
    <version>2.6.0</version>
</dependency>

三、参考

https://www.cnblogs.com/sprinkle/p/6426204.html