When reading a column in Excel file, this program reads data and empty columns

Question

I want to read an Excel file and skip blank lines. My code reads data and blank cells. How can I skip blank lines, I am using Apache POI. Help me with this problem

package com.company;
import org.apache.commons.compress.archivers.dump.InvalidFormatException;
import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.ss.usermodel.WorkbookFactory;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.lang.String;
import java.util.Iterator;
import java.util.Scanner;
public class Main {
    public static void main(String[] args) throws IOException {
Scanner in = new Scanner(System.in);

        XSSFWorkbook library_table = new XSSFWorkbook(new FileInputStream("C:\\Users\\admin\\Desktop\\Курсовая\\Table for course work.xlsx"));
        XSSFSheet library_sheet = library_table.getSheet("Library");
        Iterator rowiter = ((XSSFSheet) library_sheet).rowIterator();
        boolean continue_first_row = true;
        System.out.println("1 - Name\n2 - Author\n3 - Date of publishing");
        System.out.print("Choose type of search: ");
        int choice = in.nextInt(), count = 0;

        while(rowiter.hasNext()){
            XSSFRow row = (XSSFRow) rowiter.next();
            if (count == 0){
                count++;
                continue;
            }
            else {
                System.out.println(row.getCell(choice));
            }

        }
}
}

Axel Richter · Accepted Answer · 2021-03-19 08:58:55Z

Sheet.rowIterator does not contain totally empty rows. But it of course contains rows only having part of the cells filled. And it contains rows having format or having cells having formats even if those cells are empty.

So, if the need is to skip empty cells, then your program must check whether the found cell is empty or not. Simplest possibility is to check whether the Cell is null or Cell.toString equals a empty String.

...
  while(rowiter.hasNext()) { // does not contain totally empty rows
   XSSFRow row = (XSSFRow) rowiter.next();
   if (count == 0) { //skip header row
    count++;
    continue;
   } else {
    XSSFCell cell = row.getCell(choice);
    if (cell == null || "".equals(cell.toString())) { // check whether cell is empty
     // cell is empty
    } else {
     System.out.println(cell.toString());
    }
   }
  }
...

Note: Relying on Cell.toString is not good practice. Instead do using DataFormatter to get the cell values as String.

Example:

...
import org.apache.poi.ss.usermodel.DataFormatter;
...
  while(rowiter.hasNext()) { // does not contain totally empty rows
   XSSFRow row = (XSSFRow) rowiter.next();
   if (count == 0) { //skip header row
    count++;
    continue;
   } else {
    XSSFCell cell = row.getCell(choice);
    String cellValue = dataFormatter.formatCellValue(cell);
    if ("".equals(cellValue)) { // check whether cell is empty
     // cell is empty
    } else {
     System.out.println(cellValue);
    }
   }
  }
...

Collectives™ on Stack Overflow

When reading a column in Excel file, this program reads data and empty columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related