read excel file in java apache poi code example

Example 1: swing java read excel sheet

import java.io.*;
import java.awt.*;
import java.util.*;
import javax.swing.*;
import java.awt.event.*;
import javax.swing.table.*;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;


public class ExcelToJtable {
public static void main(String[] args) {

Vector headers = new Vector();
Vector data = new Vector();

File file = new File("c:/data.xls");
try {
Workbook workbook = Workbook.getWorkbook(file);
Sheet sheet = workbook.getSheet(0);
headers.clear();
for (int i = 0; i < sheet.getColumns(); i++) {
Cell cell1 = sheet.getCell(i, 0);
headers.add(cell1.getContents());
}
data.clear();
for (int j = 1; j < sheet.getRows(); j++) {
Vector d = new Vector();
for (int i = 0; i < sheet.getColumns(); i++) {
Cell cell = sheet.getCell(i, j);
d.add(cell.getContents());
}
d.add("\n");
data.add(d);
}
}
catch (Exception e) {
e.printStackTrace();
}
JTable table = new JTable();
DefaultTableModel model = new DefaultTableModel(data,headers);
table.setModel(model);
table.setAutoCreateRowSorter(true);
model = new DefaultTableModel(data, headers);
table.setModel(model);
JScrollPane scroll = new JScrollPane(table);
JFrame f=new JFrame();
f.add(scroll);
f.setSize(400, 200);
f.setResizable(true);
f.setVisible(true);
}
}

Example 2: how to read data from excel

I used Apache POI libraries to read and write from
excel file, I add the Apache poi dependencies to my pom.xml file.
In order to connect I use following classes

-FileInputStream from Java. It is used to create connection
to the file. We pass the file path as constructor to it.

-WorkBook is a class that represents the excel file. We create
workbook is a class from Apache POI that represents the excel
file. We create Workbook object using the FileInputStream
object.

-Sheet represents a single sheet from the excel file. We create
sheet using Workbook object. We can create worksheet using
the 0 based index.

public String readExcel(String path, String sheetName, int rowNum, int colNum){
        try {
            FileInputStream file = new FileInputStream(path);
            Workbook book = WorkbookFactory.create(file);
            Sheet sheet = book.getSheet(sheetName);
            Row row = sheet.getRow(rowNum);
	    	Cell cell = row.getCell(colNum);
            String cellData = cell.toString();
            return cellData;
        } catch (Exception e) {
            throw new RuntimeException(e);
        }
    }

How to get row and column numbers:
    int rowCount = workSheet.getLastRowNum()+1; ==> why we add '+1'? Because 
    row num starts from 0.
    int colCount = workSheet.getRow(0).getLastCellNum();
    String sheetName = workSheet.getSheetName();

The data we get from excel can be converted to different formats such as set, 
list, map

-------------------EXCEL WRITING AND SAVING---------------------------------

The first creation part is same then go to a cell where you want to write.

Row row = sheet.getRow(0)
Cell resultCell = row.getCell(2)

Let's imagine you have values in index 0 and index 1.
Now you want to create a cell on index 2. First, check if it is null to 
avoid problems.

if(resultCell==null){
   resultCell = row.createCell(2);
}
resultCell.setValue("Germany");

in order to save:
// class is used to open file and write to it
FileOutputStream fileOutputStream = new FileOutputStream("src/test/resources/
Countries.xlsx");
// write the changes to the file and save
workbook.write(fileOutputStream);