Reading data from spreadsheet

• Create a project and package
• Download the"poi-3.13"
• Add the "xmlbeans" jar and the "poi" jars.
• Create a test data spreadsheet (In this example, path of spread sheet is D:\Hemanth\TestData\TestData.xlsx" and fill any one sheet with the data)
• In the below example, path of the spreadsheet is given as "source"
• workbook(wb) was read
• Identified the first row, first column(cell), last row and last column
• Data was added to the ArrayList using For loops
• Printed the ArrayList data.

Example Spreadsheet:

Example Code:

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.util.ArrayList;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

public class ReadMain {
 public static void main(String[] args) throws Exception {

  File source = new File("D:\\Hemanth\\TestData\\TestData.xlsx");
  FileInputStream fis = new FileInputStream(source);
  XSSFWorkbook wb = new XSSFWorkbook(fis);
  int FirstRow = wb.getSheet("Sheet3").getFirstRowNum();
  int FirstCell = wb.getSheet("sheet3").getRow(FirstRow).getFirstCellNum();
  System.out.println("FirstRow:"+FirstRow);
  System.out.println("FirstCell:"+FirstCell);
  int Lastrow = wb.getSheet("Sheet3").getLastRowNum();
  System.out.println("LastRow:" +Lastrow);
  int Lastcol = wb.getSheet("Sheet3").getRow(Lastrow).getLastCellNum();
  System.out.println("LastCol:" +Lastcol);
  String activecell = wb.getSheet("Sheet3").getActiveCell();
  System.out.println("Active Cell:" + activecell);
  ArrayList mylist = new ArrayList();
  String Value;

  for (int i=0;i<=Lastrow;i++)
  {
   for(int j=0;j<Lastcol;j++)
   {
    Value = wb.getSheet("Sheet3").getRow(i).getCell(j).getStringCellValue();
    System.out.println("Read Value:"+Value);
    mylist.add(Value);
   }
 
  }

    System.out.println("Values:"+mylist); 
 }

Output: