excel数据与文件常用交互处理（Java）

1. 读取excel中的数据作为key，在一个文件夹查找所有文件(包含子文件)确定是否包含该key。以每行读取。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import java.io.*;
import java.util.Scanner;

public class readExcel {
    static boolean Haveinline = false;
    static File curFile;
    static int curLine;
    public static void search1(File folder,String word){
        File fs[] = folder.listFiles();
        for(File f : fs) {
            int lines=0;
            if (f.isFile()) {
                try (FileReader fr = new FileReader(f);
                     BufferedReader br = new BufferedReader(fr)) {
                    while (true) {
                        lines++;
                        String line = br.readLine();
                        if (null == line)
                            break;
                        if (line.contains(word)){
                            curFile = f;
                            curLine = lines;
                            System.out.println(f +"文件的第"+lines+"行包含---"+ word);
                            Haveinline=true;
                        }
                    }
                } catch (IOException e) {
                    e.printStackTrace();
                }
            }
//			递归
            else if (f.isDirectory()) {
                search1(f, word);
            }
        }
    }

    public static void write(int row1,String context,String cellValue) throws IOException{
        String url="E:\\result.xlsx";
        FileInputStream fs;
        try {
            fs = new FileInputStream(url);
            Workbook wb=new XSSFWorkbook(fs);//获取sheet
            fs.close();
            Sheet sheet=wb.getSheetAt(0);  //获取到工作表，获取第一个sheet
            Row row=sheet.createRow(row1);
            FileOutputStream out=new FileOutputStream(url);  //向excel文件中写数据
            row.createCell(0).setCellValue(cellValue);
            row.createCell(1).setCellValue(context);
            out.flush();
            //往excel中写入数据流
            wb.write(out);
            //结束
            out.close();
        } catch (IOException e) {
            e.printStackTrace();
        }
    }

    public static void main(String[] args) throws IOException {
        //得到Excel工作簿对象
        FileInputStream inputStream=new FileInputStream("E:\\ww.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 获取第一个工作表 sheet1
        Sheet sheet = workbook.getSheetAt(0);

        Scanner scanner = new Scanner(System.in);
        System.out.println("请输入要做的：");
        String s = scanner.nextLine();//3.读取输入

        File folder = new File(s);


        // 获取第二列
        for (Row row : sheet) {
            Cell cell = row.getCell(1);
            if (cell==null){
                continue;
            }
            Haveinline = false;
            cell.setCellType(CellType.STRING);
            String cellValue = cell.getStringCellValue();
            search1(folder, cellValue);
            if (!Haveinline) {
                write(cell.getRowIndex(),"不存在",cellValue);
                System.out.println(cell + "不在文件中");
            }else {
                write(cell.getRowIndex(),"存在",cellValue);
            }
            // 下面的方法为遍历所有数据
            /* for (Cell cell : row) {
                    Haveinline = false;
                    cell.setCellType(Cell.CELL_TYPE_STRING);
                    String cellValue = cell.getStringCellValue();
                    search1(folder, cellValue);
                    if (!Haveinline){
                        System.out.println(cell+"不在文件中");
                    }
                } */
        }
        workbook.close();
    }
}

2、读取excel的数据作为目录名，查看一个文件夹下是否有该目录名的目录。

import org.apache.poi.ss.usermodel.*;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.util.ArrayList;
import java.util.List;

public class readExcel {
    static List<String> dirNameList = new ArrayList<String>();
    public static void hasContain() throws IOException {
        //得到Excel工作簿对象
        FileInputStream inputStream=new FileInputStream("E:\\ww.xlsx");
        Workbook workbook = new XSSFWorkbook(inputStream);
        // 获取第一个工作表 sheet1
        Sheet sheet = workbook.getSheetAt(0);
        // 获取第一列
        for (Row row : sheet) {
            Cell cell = row.getCell(0);
            cell.setCellType(CellType.STRING);
            String cellValue = cell.getStringCellValue();
            if (dirNameList.contains(cellValue)){
                System.out.println(cellValue);
            }
        }
    }

    public static void getFileList(File file) throws IOException {
        File[] fs = file.listFiles();
        for (File f : fs) {
            if (f.isDirectory()) {   //若是目录，则递归打印该目录下的文件
                String dirname = f.getName();
                dirNameList.add(dirname);
            }
            if (f.isFile()) {     //若是文件，直接打印
                // System.out.println(f);
            }
        }
        // 输出所有目录名字
        /*for (int i = 0; i < dirNameList.size(); i++) {
            System.out.println(""+ dirNameList.get(i));
        }*/
        hasContain();
    }

    public static void main(String[] args) throws IOException {
        String inputPath = "E:\\毕设项目不可修改\\src\\main\\java\\com";;		//要遍历的路径
        File file = new File(inputPath);		//获取其file对象
        getFileList(file);
    }
}

两个代码块使用的jar包为：

	<dependencies>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi-ooxml</artifactId>
            <version>4.1.2</version>
        </dependency>
        <dependency>
            <groupId>org.apache.poi</groupId>
            <artifactId>poi</artifactId>
            <version>4.1.2</version>
        </dependency>
    </dependencies>
    
    <properties>
        <maven.compiler.source>8</maven.compiler.source>
        <maven.compiler.target>8</maven.compiler.target>
    </properties>