reading excel rows properly

Question

I am trying to read multiple rows of data from .xlsx file. The output in console shows the all values one below another.

Issue is they are not being displayed in the table like manner as they are displayed in source excel sheet.

My excel file is .xlsx, so I am coding with XSSF POI api. It contains two columns(Name and Score) with 5 rows total.

Console output looks like this

Name
Score
TOM
1
DICK
2
HARRY
3
JERRY
4

I want it to print like this:

Name            Score
TOM              1
DICK             2
HARRY            3
JERRY            4

code:

package gmailExcel;

import java.io.FileInputStream;
import java.io.IOException;

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 ReadXl {

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

 // Locate xl file.
            FileInputStream fis = new FileInputStream
            ("File location on local host");

 // Load file, workbook and sheet.
            XSSFWorkbook wb = new XSSFWorkbook(fis);
            XSSFSheet ws = wb.getSheet("sheetName");


 // Declare row and cell variable.      
            XSSFRow row;
            XSSFCell cells;

 // Get row and column count.
            int rowCount = ws.getLastRowNum();
            int colCount = ws.getRow(0).getLastCellNum();

 // Iterate over rows and columns.
            for(int r = 0; r < rowCount; r++) {
                row = ws.getRow(r);

                    for(int c = 0; c < colCount; c++) {
                         cells  = row.getCell(c);



 // Output the values from Excel sheet.                 
            String cellval = cells.toString();
            System.out.println(cellval);
                        }

                    }

                }
            }

Answer 1

The issue is in your nested for-loop. In each iteration, you are printing the value with a newline after it. What you want to do is to print the newline only after you've printed the cell in the second column I presume.

This can be accomplished by printing the newline outside the nested loop like this:

for (int r = 0; r < rowCount; r++) {
    for (int c = 0; c < colCount; c++) {
        cells = row.getCell(c);
        String cellval = cells.toString();
        System.out.print(" | " + cellval);   //New line IS NOT printed
    }
    System.out.println(" |");                //New line IS printed
}