读取指定目录下的excel表格

接到一个小需求,读取指定目录下的excel表格

我们这边用的是springboot来创建工程

public static void main(String[] args) throws IOException {
        ConfigurableApplicationContext context = SpringApplication.run(ReadExcelApplication.class, args);
        String excelPath = context.getEnvironment().getProperty("excel");
        String outPath = context.getEnvironment().getProperty("outFile");
        ReadExcel.getXlsxFile(excelPath,outPath);
    }

然后我们在properties文件中配置地址

excel=D:\\readFile
# 四个\反斜杠转义过来就是两个\反斜杠
outFile=D:\\\\aaa

然后是获取指定目录下的文件夹

 		//从properties中获取的文件路径excelPath
        File file = new File(excelPath);
        if (!file.exists()){
            System.out.println("文件不存在");
        }
        ArrayList<String> arrayList = new ArrayList<>();
        File[] listFiles = file.listFiles();
        //excel文件路径
        String excelPaths = null;
        //文件夹名称
        String filePath = null;

        for (int i = 0; i < listFiles.length; i++) {
            if (listFiles[i].isFile()){
                excelPaths = listFiles[i].getPath();
            }
            if (listFiles[i].isDirectory()){
                filePath = listFiles[i].getPath();
            }
        }
        File readFile = new File(filePath);
        File[] files = readFile.listFiles();
        for (int i = 0; i < files.length; i++) {
            String path = files[i].getPath();
            //这是文件夹中的数据
            arrayList.add(path);
        }
        //文件后缀
        String[] split = excelPaths.split("\\.");
        String fileSuffix = split[1];
        InputStream inputStream = null;
        try {
            inputStream = new FileInputStream(excelPaths);
        } catch (FileNotFoundException e) {
            e.printStackTrace();
        }
        Workbook workBook = getWorkBook(inputStream, fileSuffix);
        //接下来就是读excel表格

判断excel的类型,是xsl还是xlsx类型

public static Workbook getWorkBook(InputStream inputStream, String fileSuffix) {
        //创建工作簿
        Workbook workbook = null;
        try {
            workbook = WorkbookFactory.create(inputStream);
        } catch (IOException e) {
            e.printStackTrace();
        } catch (InvalidFormatException e) {
            e.printStackTrace();
        }
        if ("xls".equals(fileSuffix)){
            try {
                workbook = new HSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }else if ("xlsx".equals(fileSuffix)){
            try {
                workbook = new XSSFWorkbook(inputStream);
            } catch (IOException e) {
                e.printStackTrace();
            }
        }
        return workbook;
    }

接下来开始读excel表格

//开始读excel表格
        List<String> strings = new ArrayList<>();
        if (workBook != null){
            for (int sheetNum  = 0; sheetNum  < workBook.getNumberOfSheets(); sheetNum ++) {
                //获得当前的sheet工作表
                Sheet sheet = workBook.getSheetAt(sheetNum);
                if (sheet == null){
                    continue;
                }
                //获得当前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;
                    }
                    //获得当前行的开始列
                    short firstCellNum = row.getFirstCellNum();
                    //获得当前行的列数
                    int lastCellNum = row.getPhysicalNumberOfCells();

                    //循环当前行
                    for (int cellNum = firstCellNum;cellNum <= lastCellNum; cellNum++){
                        Cell cell = row.getCell(cellNum);
                        //数据的处理
                        String cellValue = getCellValue(cell);
                        if (cellValue != ""){
                            //这是excel中的数据
                            strings.add(cellValue);
                        }
                    }
                }
            }
            try {
                workBook.close();
            } catch (IOException e) {
                e.printStackTrace();
            }
        }

读的时候还要对数据进行处理

public static String getCellValue(Cell cell){
        String cellValue = "";
        if (cell == null){
            return cellValue;
        }
        //如果当前单元格内容为日期类型,需要特殊处理
        String dataFormatString = cell.getCellStyle().getDataFormatString();
        if (dataFormatString.equals("m/d/yy")){
            cellValue = new SimpleDateFormat("yyyy/MM/dd").format(cell.getDateCellValue());
            return cellValue;
        }
        //把数字当字符串来读
        if (cell.getCellType() == CELL_TYPE_NUMERIC){
            cell.setCellType(CELL_TYPE_STRING);
        }
        //判断数据类型
        switch (cell.getCellType()){
            case CELL_TYPE_NUMERIC:
                cellValue = String.valueOf(cell.getNumericCellValue());
                break;
            case CELL_TYPE_STRING :
                cellValue = String.valueOf(cell.getStringCellValue());
                break;
            case CELL_TYPE_BOOLEAN :
                cellValue = String.valueOf(cell.getBooleanCellValue());
                break;
            case CELL_TYPE_FORMULA:
                cellValue = String.valueOf(cell.getCellFormula());
                break;
            case CELL_TYPE_BLANK:
                cellValue = "";
                break;
            case CELL_TYPE_ERROR:
                cellValue = "非法字符";
                break;
            default:
                cellValue = "未知类型";
                break;
        }
        return cellValue;
    }

我是将读取excel中的数据放在list集合中的,拿出里面的的数据,只要遍历集合就可以了