Code to read data from Excel
package mit;
import java.io.File;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.Iterator;
import org.apache.poi.ss.usermodel.DataFormatter;
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 TestExcelData {
public static void main(String[] args) throws Exception {
File file = new File("Untitled spreadsheet (1) (1).xlsx");
FileInputStream fin = new FileInputStream(file);
Workbook wb = new XSSFWorkbook(fin);
Sheet s1 = wb.getSheet("Sheet1");
int r1=s1.getPhysicalNumberOfRows();
int c1=s1.getRow(0).getPhysicalNumberOfCells();
String[][] data =new String[r1-1][c1];
// String s = r.getCell(1).toString();
// System.out.println(s);
for (int i = 0; i < r1-1; i++)
{
for (int j = 0; j < c1; j++)
{
DataFormatter df=new DataFormatter();
data[i][j]= df.formatCellValue(s1.getRow(i).getCell(j));
}
}
// Clean up
fin.close();
for (String[]dataAStr:data)
{
System.out.println(Arrays.toString(dataAStr));
}
}
}
Package and Imports
package mit;
-
Declares the package name
mit. It organizes classes into namespaces.
import java.io.File;
import java.io.FileInputStream;
import java.util.Arrays;
import java.util.Iterator;
-
File: For representing file and directory pathnames. -
FileInputStream: For reading raw bytes from a file. -
Arrays: For utility functions liketoString()to print array contents. -
Iterator: Though imported, it is not used in this program.
import org.apache.poi.ss.usermodel.DataFormatter;
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;
-
These classes are part of Apache POI for working with Excel files (
.xlsx).-
Workbook: Represents the whole Excel file. -
Sheet: Represents a single worksheet. -
Row: Represents a row in a sheet. -
DataFormatter: Converts cell values to readableStrings. -
XSSFWorkbook: Used specifically for.xlsxfiles.
-
Class Definition
public class TestExcelData {
-
Defines a public class named
TestExcelData.
Main Method
public static void main(String[] args) throws Exception {
-
Entry point of the program.
-
throws Exception: Declares that the method may throw exceptions (e.g.,IOExceptionorFileNotFoundException).
Step-by-step Operations
File file = new File("Untitled spreadsheet (1) (1).xlsx");
-
Creates a
Fileobject pointing to the Excel file. Make sure the path is correct.
FileInputStream fin = new FileInputStream(file);
-
Opens the file for reading its contents.
Workbook wb = new XSSFWorkbook(fin);
-
Loads the Excel workbook using Apache POI. This supports
.xlsxfiles.
Sheet s1 = wb.getSheet("Sheet1");
-
Accesses the sheet named
"Sheet1"from the workbook.
int r1 = s1.getPhysicalNumberOfRows();
-
Gets the number of rows that contain data (non-empty rows).
int c1 = s1.getRow(0).getPhysicalNumberOfCells();
-
Gets the number of cells (columns) in the first row (assumed to be the header).
String[][] data = new String[r1 - 1][c1];
-
Initializes a 2D array to store the Excel data (excluding the header row).
Reading the Excel Data
for (int i = 0; i < r1 - 1; i++) {
for (int j = 0; j < c1; j++) {
DataFormatter df = new DataFormatter();
data[i][j] = df.formatCellValue(s1.getRow(i).getCell(j));
}
}
-
Outer loop (
i): Iterates over rows from0tor1-2(excluding header row atr1-1). -
Inner loop (
j): Iterates over each cell in a row. -
DataFormatter: Converts cell to a formatted string regardless of cell type (numeric, string, date). -
Stores the formatted value in the
dataarray.
Note: Since
r1-1is used in the array, it's implied that the header is included but not stored.
Closing Resources
fin.close();
-
Closes the file input stream.
Printing the Data
for (String[] dataAStr : data) {
System.out.println(Arrays.toString(dataAStr));
}
-
Iterates through the
dataarray. -
Prints each row as a string using
Arrays.toString().
🔚 Summary:
-
This program reads an Excel
.xlsxfile using Apache POI. -
It stores data (excluding the header) in a 2D
Stringarray. -
Uses
DataFormatterto ensure proper formatting of cell values. -
Then it prints the extracted data to the console.
.png)
.png)
.png)