Java Program to read Excel File and Load into Array


Excel is one of the most important productivity tools used in offices. It is also a great organizational tool, and its really easy to create tables in excel to store different kinds of data. 

When doing automation, excel is an important tool to store data for automation scripts, which makes reading Excel data using Java code an important Interview Question.

So, today we'll learn how to read an excel sheet using Java, and return the contents as a String Array. The Array can then be utilized as per your requirement. 

We are reading an xlsx file, the most recent and most popular excel format.

We need to import the below classes:

import java.io.File;
import java.io.FileInputStream;

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;

java.io.File and java.io.FileInputStream will help us to read the file from the disk.

XSSF classes are used to read the contents of the excel file. 

XSSF classes are not included by default, you need to import the jar files or dependency for it. 

The best way is to use Maven to import dependencies. You can learn to Create a Java Project using Maven

Below are the dependencies required : 


<!-- Apache POI for Reading and Writing Excel files -->
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
	<artifactId>poi-ooxml</artifactId>
	<version>5.0.0</version>
</dependency>

<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
	<groupId>org.apache.poi</groupId>
	<artifactId>poi</artifactId>
	<version>5.0.0</version>
</dependency>

We've included the path to the jar files in Maven Repository, in case you want to download and include the jar files. 

Now, we need to read the excel file, below code will do that : 

// Create a file input stream to read Excel workbook and worksheet
File xlFile = new File("./DataSources/" + fileName);
FileInputStream fis = new FileInputStream(xlFile);
XSSFWorkbook xlWB = new XSSFWorkbook(fis);
XSSFSheet xlSheet = xlWB.getSheet(sheetName);

As we need to load the excel data to an array, it is important to know the total number of rows and columns. Here's how we do that : 

// Get the number of rows and columns
int numRows = xlSheet.getLastRowNum() + 1;
int numCols = xlSheet.getRow(0).getLastCellNum();


And now, we will create a String array where we will load all the data:

// Create double array data table - rows x cols
// We will return this data table
dataTable = new String[numRows][numCols];


Now comes the most important part, i.e. load the data into the array. We'll utilize a for loop to do that. Here's how we do it:

// For each row, create a HSSFRow, then iterate through the "columns"
// For each "column" create an HSSFCell to grab the value at the specified cell
// (i,j)
for (int i = 0; i < numRows; i++) {
	XSSFRow xlRow = xlSheet.getRow(i);
	for (int j = 0; j < numCols; j++) {
		XSSFCell xlCell = xlRow.getCell(j);
		dataTable[i][j] = xlCell.toString();
	}
}

We need to close the excel workbook once done. 

//close the workbook once done
xlWB.close();

That's it! Now for the complete working code. You can directly use it for your reference. 

You just need to pass the fileName and sheetName to the function, and you are all set!

Note: We have created a directory "Datasources" in the Project base directory and placed the file there. If your file is going to be at a different location, you need to change the directory name.
package utilities;

import java.io.File;
import java.io.FileInputStream;

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

	public static void main(String args[]) {

		System.out.println(readExcel("ComputengineDatasheet.xlsx", "Calculators").length);

	}

	/**
	 * Read an Excel xlsx file and return the results in a String 2D array
	 * 
	 * @author computengine.com
	 * @param fileName
	 * @param sheetName
	 * @return
	 */
	public static String[][] readExcel(String fileName, String sheetName) {

		String[][] dataTable = null;

		try {

			// Create a file input stream to read Excel workbook and worksheet
			File xlFile = new File("./DataSources/" + fileName);
			FileInputStream fis = new FileInputStream(xlFile);
			XSSFWorkbook xlWB = new XSSFWorkbook(fis);
			XSSFSheet xlSheet = xlWB.getSheet(sheetName);

			// Get the number of rows and columns
			int numRows = xlSheet.getLastRowNum() + 1;
			int numCols = xlSheet.getRow(0).getLastCellNum();

			// Create double array data table - rows x cols
			// We will return this data table
			dataTable = new String[numRows][numCols];

			// For each row, create a HSSFRow, then iterate through the "columns"
			// For each "column" create an HSSFCell to grab the value at the specified cell
			// (i,j)
			for (int i = 0; i < numRows; i++) {
				XSSFRow xlRow = xlSheet.getRow(i);
				for (int j = 0; j < numCols; j++) {
					XSSFCell xlCell = xlRow.getCell(j);
					dataTable[i][j] = xlCell.toString();
				}
			}
			
			//close the workbook once done
			xlWB.close();
		} catch (Exception e) {
			System.out.println("Error when reading xlsx file:" + e.getMessage());
		}

		return dataTable;
	}

}

Thanks for Reading the Article. If you have reached this far, we hope that the article was useful to you! Please Like/Share/Follow us on FacebookTwitterTumblr.

Comments

Popular posts from this blog

Calculate Your CTC Hike Percentage

Java Program to Find a List of Prime Numbers (Step by Step)

Java Program to check Palindrome

What is my IP Address - How to find your IP address on Local Network using Command Prompt, and External Network using JavaScript Code

Reduce Server Load from Dynamic Page Search - Create a Dynamic JavaScript based Table filter