java - How to read .xlsx file with apache poi?

Question

See the picture below, I am trying to write a program that can "scan" a given row with no limit of from which cell to which cell, then, find all the "strings" that are identical the same. Is it possible to do that? Thank you.

To give an example so that this will not be very confusing, for ex.: On row H, you see there are customer's names, there are "Coresystem", "Huawei", "VIVO", etc... Now the problem is, what if the names are not grouped together, they are all split up, like, On H5, it will be "Huawei" and On H9, it will be "VIVO", etc, it's like, unlike the picture provided below, on row H all the names are split up, and I want apache POI to find all the customers that have the same name, for ex.: If user enter "coReSysteM", it should be able to find all the .equalsIgnoreCase of all Coresystem on row H (btw, the user should be able to enter the customer's name that they want to enter and the row they want to search for), and display from A5, B5, C5, D5, E5, F5, G5, H5 to A14, B14, C14, D14, E14, F14, G14, H5, is it possible? I was thinking about setting a formula to find all the customer, for example: =CountIf

These are the code that I am currently trying to do, but then I am stuck with it:

package excel_reader;

import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.Scanner;

import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ExcelReader2d {
    ExcelReader link = new ExcelReader();
    Scanner scan = new Scanner(System.in);

    // instance data
    private static int numberGrid[][] = null;
    private static String stringGrid[][] = null;

    // constructor
    public ExcelReader2d(String desLink) {

    }

    // methods
    public void ExeScan() throws FileNotFoundException, IOException {
        Scanner scan = new Scanner(System.in);
        XSSFWorkbook workbook = new XSSFWorkbook(new FileInputStream("C:\\Users\\Sonic\\Desktop\\20191223 IMPORTS.xlsx"));

        XSSFSheet sheet = workbook.getSheetAt(0);
        final int rowStart = Math.min(15, sheet.getFirstRowNum()), rowEnd = Math.max(1400, sheet.getLastRowNum());

        System.out.print("Enter the rows that you want to search for: (for ex. the rows that stores customer's name) ");
        int searchRows = scan.nextInt();
        System.out.print("Enter the customer's name that you are looking for: ");
        String name = scan.nextLine();
        //int rowNum;

        // Search given row
        XSSFRow row = sheet.getRow(searchRows);
        try {
            for (int j = 4; j < rowEnd; j++) {
                Row r = sheet.getRow(j);
                if (name.equalsIgnoreCase(name)) {
                    row.getCell(j).getStringCellValue();
                }

                // skip to next iterate if that specific cell is empty
                if (r == null)
                    continue;

            }
        } catch (Exception e){
            System.out.println("Something went wrong.");
        }



    }

}

ps. I know that this will be very confusing, but please feel free to ask for any kind of questions to help you get rid of the confusion and help me either because this has been a problem for me. Thank you very much and I will super appreciated for your help. Currently using apache poi, vscode, java.

标签： javaexcelapache-poi

Answer 1

I would iterate over the rows in the sheet and get the string content of cell 7 (H) from each row. If that string fulfills the requirement equalsIgnoreCase the searched value, that row is one of the result rows, else not.

One could collect the result rows in a List<Row>. Then this List contains the result rows after that.

Example:

ExcelWorkbook.xlsx:

Code:

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

import java.util.List; 
import java.util.ArrayList; 

import java.io.FileInputStream;

public class ExcelReadRowsByColumnValue {

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

  String filePath = "./ExcelWorkbook.xlsx";

  String toSearch = "coresystem";
  int searchColumn = 7; // column H
  List<Row> results = new ArrayList<Row>();

  DataFormatter dataFormatter = new DataFormatter();
  Workbook workbook = WorkbookFactory.create(new FileInputStream(filePath));
  FormulaEvaluator formulaEvaluator = workbook.getCreationHelper().createFormulaEvaluator(); 
  Sheet sheet = workbook.getSheetAt(0);

  for (Row row : sheet) { // iterate over all rows in the sheet
   Cell cellInSearchColumn = row.getCell(searchColumn); // get the cell in seach column (H)
   if (cellInSearchColumn != null) { // if that cell is present
    String cellValue = dataFormatter.formatCellValue(cellInSearchColumn, formulaEvaluator); // get string cell value
    if (toSearch.equalsIgnoreCase(cellValue)) { // if cell value equals the searched value
     results.add(row); // add that row to the results
    }
   }
  }

  // print the results
  System.out.println("Found results:");
  for (Row row : results) {
   int rowNumber = row.getRowNum()+1;
   System.out.print("Row " + rowNumber + ":\t");
   for (Cell cell : row) {
    String cellValue = dataFormatter.formatCellValue(cell, formulaEvaluator);
    System.out.print(cellValue + "\t");
   }
   System.out.println();
  }

  workbook.close();
 }
}

Result: