How to display excel data in JTable in java

Question

here's my code to display all the data from an excel data file:

package util;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.io.IOException;

public class read {

    public static void main(String[] args) {
        excelRead();
    }

    public static void excelRead(){

        try {
            int i;
            int j;

            String path = "./data/data.xlsx";
            XSSFWorkbook workbook = new XSSFWorkbook(path);
            XSSFSheet sheet = workbook.getSheet("sheet1");
            for (i = 0; i <=5;i++) {
                for (j = 0; j<=2;j++) {
                    String data = sheet.getRow(i).getCell(j).getStringCellValue();
                    System.out.print(data + "   " );
                    if(j == 2){
                        System.out.println("         ");
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (NullPointerException e){
            e.printStackTrace();
        }
    }

}

I have two problems here,

1. it displays a NullPointerException after it displays all data
2. I don't know how to display the data to a JTable

edit :

this is the code to display the excel file data on the Jtable but it gives me an ArrayOutofBounds exception :

package util;

import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

import javax.swing.*;
import java.awt.*;
import java.io.IOException;

public class Jtable extends JPanel {
    JTable table;

    public Jtable() {
        try {


            String path = "./data/data.xlsx";
            XSSFWorkbook workbook = new XSSFWorkbook(path);
            XSSFSheet sheet = workbook.getSheet("sheet1");
            String[] column = {"name", "age", "profession", "gender", "company"};
            String[][] data = {
                    { sheet.getRow(0).getCell(0).getStringCellValue(),
                            sheet.getRow(0).getCell(1).getStringCellValue(),
                            sheet.getRow(0).getCell(2).getStringCellValue()},
                    {sheet.getRow(1).getCell(0).getStringCellValue(),
                            sheet.getRow(1).getCell(1).getStringCellValue(),
                            sheet.getRow(1).getCell(2).getStringCellValue()},
                    {sheet.getRow(2).getCell(0).getStringCellValue(),
                            sheet.getRow(2).getCell(1).getStringCellValue(),
                            sheet.getRow(2).getCell(2).getStringCellValue()},
                    {sheet.getRow(3).getCell(0).getStringCellValue(),
                            sheet.getRow(3).getCell(1).getStringCellValue(),
                            sheet.getRow(3).getCell(2).getStringCellValue()},
                    {sheet.getRow(4).getCell(0).getStringCellValue(),
                            sheet.getRow(4).getCell(1).getStringCellValue(),
                            sheet.getRow(4).getCell(2).getStringCellValue()}
            };
            table = new JTable(data, column);
            table.setPreferredScrollableViewportSize(new Dimension(450, 63));
            table.setFillsViewportHeight(true);
            JScrollPane scrollPane = new JScrollPane(table);
            add(scrollPane);
        }catch (IOException e) {
            e.printStackTrace();
        } catch (NullPointerException e) {
        }
    }


    public static void main(String[] args) {
        JFrame frame = new JFrame();
        Jtable panel = new Jtable();
        frame.add(panel);
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setSize(500, 500);
        frame.setVisible(true);


    }

    public static void excelRead() {

        try {
            int i;
            int j;

            String path = "./data/data.xlsx";
            XSSFWorkbook workbook = new XSSFWorkbook(path);
            XSSFSheet sheet = workbook.getSheet("sheet1");
            for (i = 0; i <= 5; i++) {
                for (j = 0; j <= 2; j++) {
                    String data = sheet.getRow(i).getCell(j).getStringCellValue();
                    System.out.print(data + "   ");
                    if (j == 2) {
                        System.out.println("         ");
                    }
                }
            }
        } catch (IOException e) {
            e.printStackTrace();
        } catch (NullPointerException e) {
        }catch (ArrayIndexOutOfBoundsException e){
            e.printStackTrace();
        }
    }
}

Answer 1

All your ArrayIndexOutOfBoundsException I suppose come from this little part:

for (i = 0; i <= 5; i++) {
    for (j = 0; j <= 2; j++) {
        // ...
    }
}

Do you see the error?

Let's look at it more closely:

i <= 5;               j <= 2;

Arrays have N items, but their indexes go from 0 to N - 1, imagine you have 5 items:

Index    N
  0      1
  1      2
  2      3
  3      4
  4      5
  5      ???

You're telling your program to iterate from 0 to 5, and at index 5 you have nothing, you're checking something that's out of the bounds!

How can you fix this? Removing the = part on your comparison

i < 5;           j < 2;

Or better, use an Iterator for your Excel file, this way, it doesn't matter if you have 1 or 1000 or 1 million entries, you won't have to modify your program for each of those, and you can also add more columns and your program won't break.

As for your JTable, my below answer is based on this answer in which your data comes from an ArrayList of data but you start your DefaultTableModel with 0 entries and add them after.

Here, I'm showing you how to:

1. Display the data that comes from Excel into your console.
2. Add the data to ArrayLists to be displayed after
3. Add a JTable with dummy data
4. Add a JTable with data from the Excel file that you got from step 2.

And all of it is embedded into a proper Minimal, Reproducible Example (MRE) and this is expected from you on your next questions. This question is an exception as you're really lost in how to make your program to work, but on your future questions we will require you to create something like this for us to Copy-Paste and be able to run your program without little to no (preferably) changes.

In this case you can run my program with a single modification: The file path

import java.awt.BorderLayout;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.IOException;
import java.util.ArrayList;
import java.util.Iterator;
import java.util.List;

import javax.swing.JFrame;
import javax.swing.JScrollPane;
import javax.swing.JTable;
import javax.swing.SwingUtilities;
import javax.swing.table.DefaultTableModel;

import org.apache.poi.ss.usermodel.Cell;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.ss.usermodel.Row;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class JTableFromExcel {
    private JFrame frame;
    private JTable simpleTable;
    private JTable excelTable;
    private JScrollPane scrollForSimpleTable;
    private JScrollPane scrollForExcelTable;

    private String[] columnNames = {"Name", "Profession", "Salary"};
    private String[][] data = {{"Foo", "Foo_Prof", "12345"}, {"Bar", "Bar_Prof", "13579"}};

    private List<String> columnNamesFromExcel;
    private List<List<String>> dataFromExcel;

    //Creates the UI
    private void createAndShowGUI() {
        retrieveDataFromExcel();

        frame = new JFrame(getClass().getSimpleName());

        simpleTable = new JTable(data, columnNames);
        scrollForSimpleTable = new JScrollPane(simpleTable);

        DefaultTableModel tableModel = new DefaultTableModel(columnNamesFromExcel.toArray(), 0);
        for (List<String> row : dataFromExcel) {
            tableModel.addRow(row.toArray(new String[0]));
        }

        excelTable = new JTable(tableModel);
        scrollForExcelTable = new JScrollPane(excelTable);

        frame.add(scrollForSimpleTable, BorderLayout.WEST);
        frame.add(scrollForExcelTable, BorderLayout.EAST);
        frame.pack();
        frame.setDefaultCloseOperation(JFrame.EXIT_ON_CLOSE);
        frame.setVisible(true);
    }

    //Opens the Excel file and sets up the data and column names
    @SuppressWarnings("resource")
    private void retrieveDataFromExcel() {
        FileInputStream excelFile;

        columnNamesFromExcel = new ArrayList<>();
        dataFromExcel = new ArrayList<>();
        try {
            excelFile = new FileInputStream(new File("PATH/TO/YOUR/FILE.xlsx"));
            Workbook workbook = new XSSFWorkbook(excelFile);
            Sheet datatypeSheet = workbook.getSheetAt(0);
            Iterator<Row> iterator = datatypeSheet.iterator(); //We use an iterator to get all rows

            while (iterator.hasNext()) {
                Row currentRow = iterator.next();
                Iterator<Cell> cellIterator = currentRow.iterator();

                List <String> dataRow = new ArrayList<>();
                while (cellIterator.hasNext()) {
                    Cell currentCell = cellIterator.next();
                    if (currentRow.getRowNum() == 0) { //Row 0 is the header
                        if (currentCell.getCellType() == CellType.STRING) {
                            columnNamesFromExcel.add(currentCell.getStringCellValue());
                        } else if (currentCell.getCellType() == CellType.NUMERIC) {
                            columnNamesFromExcel.add(currentCell.getStringCellValue());
                        }
                    } else {
                        if (currentCell.getCellType() == CellType.STRING) {
                            dataRow.add(currentCell.getStringCellValue());
                        } else if (currentCell.getCellType() == CellType.NUMERIC) {
                            dataRow.add(String.valueOf(currentCell.getNumericCellValue()));
                        }
                    }
                    System.out.print(currentCell + " ");
                }
                if (currentRow.getRowNum() > 0) { //Row 0 is the header, if we add the first row, it will add an empty array because we didn't add anything to it before, so we skip it
                    dataFromExcel.add(dataRow);
                }
                System.out.println();

            }
        } catch (FileNotFoundException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        } catch (IOException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
    }

    public static void main(String[] args) {
        SwingUtilities.invokeLater(new JTableFromExcel()::createAndShowGUI);
    }
}

This is what you get when you run the above program

And this is the output on the console (The excel file contains the same information)

NAME PROFESSION SALARY 
FOO FOO_PROF 12345.0 
BAR BAR_PROF 13579.0 
WAKANDA WAKANDA_PROF 99999.0