How to retrieve some specific rows and columns from an excel sheet?

Question

• I am reading an xlsx file using java (Apache POI).
• I have created a Document class (having all excel column heading as variables)
• i have to read each row in the excel and map to the Document class by creating a collection of Document class.

• The problem I am facing is that I have to start reading from row 2 and from column 7 to column 35 and map the corresponding values to the document class.

• Unable to to figure out exactly how the code should be ?

• I have written the following lines of code.

 List sheetData = new ArrayList();
        InputStream excelFile = new BufferedInputStream(new FileInputStream("D:\\Excel file\\data.xlsx"));
        Workbook workBook = new XSSFWorkbook(excelFile); // Creates Workbook
        XSSFSheet sheet = (XSSFSheet) workBook.getSheet("Daily");
        DataFormatter formatter = new DataFormatter();
        for (int i = 7; i <= 35; i++) {
            XSSFRow row = sheet.getRow(i);
            Cell cell = row.getCell(i);
            String val = formatter.formatCellValue(cell);
            sheetData.add(val);
        }

Answer 1

Assuming I've understood your question correctly, I believe you want to process every row which exists from row 2 onwards to the end of the file, and for each of those rows consider the cells in columns 7 through 35. I believe you also might need to process those values, but you haven't said how, so for this example I'll just stuff them in a list of strings and hope for the best...

This is based on the Apache POI documentation for iterating over rows and cells

File excelFile = new File("D:\\Excel file\\data.xlsx");
Workbook workBook = WorkbookFactory.create(excelFile);
Sheet sheet = workBook.getSheet("Daily");
DataFormatter formatter = new DataFormatter();

// Start from the 2nd row, processing all to the end
// Note - Rows and Columns in Apache POI are 0-based not 1-based
for (int rn=1; rn<=sheet.getLastRowNum(); rn++) {
   Row row = sheet.getRow(rn);
   if (row == null) {
      // Whole row is empty. Handle as required here
      continue;
   }
   List<String> values = new ArrayList<String>();
   for (int cn=6; cn<35; cn++) {
      Cell cell = row.getCell(cn);
      String val = null;
      if (cell != null) { val = formatter.formatCellValue(cell); }
      if (val == null || val.isEmpty()) {
         // Cell is empty. Handle as required here
      }
      // Save the value to list. Save to an object instead if required
      values.append(val);
   }
}
workBook.close();

Depending on your business requirements, put in logic for handling blank rows and cells. Then, do whatever you need to do with the values you find, again as per your business requirements!

Answer 2

You could iterate with an Iterator in the document, but there is also an function "getRow() and getCell()"

  Workbook workbook = new XSSFWorkbook(excelFile);

  // defines the standard pointer in document in the first Sheet
  XSSFSheet data = this.workbook.getSheetAt(0);
  // you could iterate the document with an iterator
  Iterator<Cell> iterator = this.data.iterator();

  // x/y pointer at the document
  Row row = data.getRow(y);
  Cell pointingCell = row.getCell(x);

  String pointingString = pointingCell.getStringCellValue();