Java中excel文档的检索算法

Question

我正在编写一个程序，使用java中的Apache POI读取xslx文件，并创建一个搜索算法来在记录中搜索s字符串。我已经编写了打印所有记录的代码，但我似乎找不到如何创建搜索算法。它只能显示带有"zgheib“的记录。我真的很感激你帮我一把。这是我的代码：

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class test {
    public static void main(String[] args) throws IOException {
        try
        {
            FileInputStream file = new FileInputStream(new File("C:\\Users\\Junaid\\Documents\\IntelliJ Projects\\ReadExcel_Bashar\\src\\assignment.xlsx"));

        //Create Workbook instance holding reference to .xlsx file
        XSSFWorkbook workbook = new XSSFWorkbook(file);

        //Get first/desired sheet from the workbook
        XSSFSheet sheet = workbook.getSheetAt(0);

        //Iterate through each rows one by one
        Iterator<Row> rowIterator = sheet.iterator();
        while (rowIterator.hasNext())
        {
            Row row = rowIterator.next();
            //For each row, iterate through all the columns
            Iterator<Cell> cellIterator = row.cellIterator();

            while (cellIterator.hasNext())
            {
                Cell cell = cellIterator.next();
                //Check the cell type and format accordingly
                switch (cell.getCellType())
                {
                    case Cell.CELL_TYPE_NUMERIC:
                        System.out.print(cell.getNumericCellValue() + "\t");
                        break;
                    case Cell.CELL_TYPE_STRING:
                        System.out.print(cell.getStringCellValue() + "\t");
                        break;
                }
            }
            System.out.println("");
        }
        file.close();
    }
    catch (Exception e)
    {
        e.printStackTrace();
    }
}

}

Answer 1

如果只需要获取单元格值包含搜索字符串的行，则可以通过遍历工作表中的所有行和单元格并获取单元格值来实现。如果单元格值包含搜索字符串，则将该行添加到行列表List<Row>中。由于搜索值是字符串，因此所有单元格值都必须转换为字符串，因此可以使用DataFormatter。DataFormatter的formatCellValue方法以格式化字符串的形式获取所有单元格值。要同时支持公式单元格，必须与FormulaEvaluator一起使用DataFormatter。

下面的示例提供了一个方法

 List<Row> getRows(Sheet sheet, DataFormatter formatter, FormulaEvaluator evaluator, String searchValue) {
  List<Row> result = new ArrayList<Row>();
  String cellValue = "";
  for (Row row : sheet) {
   for (Cell cell : row) {
    cellValue = formatter.formatCellValue(cell, evaluator);
    if (cellValue.contains(searchValue)) {
     result.add(row);
     break;
    }
   }
  }
  return result;
 }

此方法遍历给定的sheet，并使用DataFormatter和FormulaEvaluator获取所有单元格值。如果找到的单元格值包含搜索值，则该行将添加到列表中，否则不添加。因此，结果是一个List<Row>，它只包含单元格包含搜索字符串的行。

完整示例：

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

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

class ReadExcelRows {

 //get only rows where cell values contain search string
 static List<Row> getRows(Sheet sheet, DataFormatter formatter, FormulaEvaluator evaluator, String searchValue) {
  List<Row> result = new ArrayList<Row>();
  String cellValue = "";
  for (Row row : sheet) {
   for (Cell cell : row) {
    cellValue = formatter.formatCellValue(cell, evaluator);
    if (cellValue.contains(searchValue)) {
     result.add(row);
     break;
    }
   }
  }
  return result;
 }

 public static void main(String[] args) throws Exception {

  Workbook workbook = WorkbookFactory.create(new FileInputStream("./inputFile.xlsx"));
  //Workbook workbook = WorkbookFactory.create(new FileInputStream("./inputFile.xls"));
  DataFormatter formatter = new DataFormatter();
  FormulaEvaluator evaluator =  workbook.getCreationHelper().createFormulaEvaluator();
  Sheet sheet = workbook.getSheetAt(0);

  List<Row> filteredRows = getRows(sheet, formatter, evaluator, "zgheib");

  for (Row row : filteredRows) {
   for (Cell cell : row) {
    System.out.print(cell.getAddress()+ ":" + formatter.formatCellValue(cell, evaluator));
    System.out.print(" ");
   }
   System.out.println();
  }

  workbook.close();
 }
}