Search algorithm for excel document in Java

Question

I'm writing a program to read an xslx file using Apache POI in java, and create a search algorithm to search for s string in the records. I've written the code to print all the records but I can't seem to find how to create the search algorithm. It's meant to show records with "zgheib" only. I would really appreciate a hand. This is my code:

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();
    }
}

}

Instead of printing the cell values out, change that to test the value? — Gagravarr
– Gagravarr, Commented Apr 24, 2020 at 13:34
So it should check the values for both numeric and string types? Can you please write a code for me to see that — Junaid Bashir
– Junaid Bashir, Commented Apr 24, 2020 at 16:10
Numeric, String, Formula, Error - see poi.apache.org/components/spreadsheet/… — Gagravarr
– Gagravarr, Commented Apr 24, 2020 at 21:35
I've already done that, what I need is the search algorithm. Please go through my code and question to clearly understand what I'm saying. Thank you — Junaid Bashir
– Junaid Bashir, Commented Apr 24, 2020 at 22:20
You have the string values of cells containing strings (cell.getStringCellValue()). What stops you checking whether this value equals your search string? Then, if it equals, do something with the Row row in this case. For example do collecting it in a List<Row>. — Axel Richter
– Axel Richter, Commented Apr 25, 2020 at 6:10

Axel Richter · Accepted Answer · 2020-04-28 08:01:02Z

If the need is to get only rows where cell values contain a search string, then this can be achieved by traversing all rows and cells in the sheet and get the cell values. If the cell value contains the search string, then add the row to a list of rows List<Row>. Since all cell values must be converted to string as the search value is a string, DataFormatter can be used. The formatCellValue methods of DataFormatter get all cell values as formatted strings. To support formula cells too, DataFormatter must be used together with FormulaEvaluator.

The following example provides a method

 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;
 }

This method traverses the given sheet and gets all cell values using DataFormatter and FormulaEvaluator. If found cell value contains the search value, the row is added to the list, else not. So the result is a List<Row> which only contains rows where cells contain the search string.

Complete example:

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();
 }
}

Thanks alot for your help and the time you took to work this out. It worked perfectly. Thanks once again.

Collectives™ on Stack Overflow

Search algorithm for excel document in Java

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related