In this article, I am going to explain how to search excel sheet using java. To implement this program you need to download Apache POI library and have it in your build path (copy POI jars to lib folder).
There is no direct method to search excel sheet using java. You need to follow the below flow.

Flow of the program

  1. Get Excel files
  2. Get Work Book of the excel files
  3. Get sheets of the workbook 
  4. Iterate rows
  5. Iterate columns
  6. Compare column value based on its type
  7. Return all filtered rows

Java Code to Search Excel Sheet

import java.io.File;
import java.io.FileInputStream;
import java.util.ArrayList;
import org.apache.poi.hssf.usermodel.HSSFCell;
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 SearchExcel {
    public static void main(String[] args) {
        try {
            // excel files
            FileInputStream excellFile = new FileInputStream(new File(
                    "C:\\excel1.xlsx"));
            
            // Create Workbook instance holding reference to .xlsx file
            XSSFWorkbook workbook = new XSSFWorkbook(excellFile);
            
            // Get first/desired sheet from the workbook
            XSSFSheet sheet = workbook.getSheetAt(0);
            
            // add sheet2 to sheet1
            ArrayList<Row> rows = searchSheet("Srinivas", sheet);
            System.out.println("filtered rows : \n"+rows);
            excellFile.close();

            System.out
                    .println("Files were searched successfulyy ");
        } catch (Exception e) {
            e.printStackTrace();
        }

    }
        
    public static ArrayList<Row> searchSheet(String searchText, XSSFSheet sheet) {
        // This parameter is for appending sheet rows to mergedSheet in the end
        
        Double doubleValue = null;
        Boolean booleanValue = null;
        ArrayList<Row> filteredRows = new ArrayList<Row>();

        //Get double value if searchText is double
        try {
            doubleValue = Double.parseDouble(searchText);
        } catch(Exception e) {  
        }
        
        //Get boolean value if searchText is boolean
        try {
            booleanValue = Boolean.parseBoolean(searchText);
        } catch(Exception e) {  
        }
        
        //Iterate rows
        for (int j = sheet.getFirstRowNum(); j <= sheet.getLastRowNum(); j++) {

            XSSFRow row = sheet.getRow(j);
           
            //Iterate columns
            for (int k = row.getFirstCellNum(); k < row.getLastCellNum(); k++) {
                XSSFCell cell = row.getCell(k);
            
               //Search value based on cell type
               switch (cell.getCellType()) {
                case HSSFCell.CELL_TYPE_NUMERIC:
                    if(doubleValue != null && doubleValue.doubleValue() == cell.getNumericCellValue()) {
                        filteredRows.add(row);
                    }
                    break;
                case HSSFCell.CELL_TYPE_STRING:
                    if(searchText != null && searchText.equals(cell.getStringCellValue())) {
                        filteredRows.add(row);
                    }
                    break;
                case HSSFCell.CELL_TYPE_BOOLEAN:
                    if(booleanValue != null && booleanValue.booleanValue() == cell.getBooleanCellValue()) {
                        filteredRows.add(row);
                    }
                    break;
                default:
                    if(searchText != null && searchText.equals(cell.getStringCellValue())) {
                        filteredRows.add(row);
                    }
                    break;
                }
            }
        }
        return filteredRows;
    }

}

4 comments:

  1. I am telling you all the different way for searching which are heaving all for excel and whole which they define here for essay writing. I hope you just like it with monster resume writing service review but it is not with it then you never can easily understand it. Hence you can easily understand whole which is define ever.

    ReplyDelete
  2. I simply couldn’t go away your web site before suggesting that I actually loved the standard info a person provide for your guests? Is gonna be again steadily to inspect new posts.
    토토
    경마

    ReplyDelete
  3. you have a great blog here! would you like to make some invite posts on my blog?
    바카라사이트
    토토사이트

    ReplyDelete
  4. This comment has been removed by the author.

    ReplyDelete

Blogroll

Popular Posts