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.
There is no direct method to search excel sheet using java. You need to follow the below flow.
Flow of the program
- Get Excel files
- Get Work Book of the excel files
- Get sheets of the workbook
- Iterate rows
- Iterate columns
- Compare column value based on its type
- 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; } }
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.
ReplyDeleteI 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토토
경마
you have a great blog here! would you like to make some invite posts on my blog?
ReplyDelete바카라사이트
토토사이트