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 Facebook, Twitter, Tumblr.

Comments

Post a Comment

Popular posts from this blog

Calculate Your CTC Salary Hike Percentage - Online Calculator to find your New Salary

-
Image
  Did you receive a Salary Hike? Congratulations!  Using this CTC Hike Percentage calculator you can calculate: Percentage Hike Received New Expected Salary Old Salary (yes, you already know it! But if you want to compare, we've included that feature as well!) Receiving a Salary Hike is a really happy occasion. It means that your work is valued in your organization and the organization is willing to invest in you and your career growth. But as any human being, you are interested in exactly how much you have received. Most organizations provide either a lump sum amount in the hike letter or mention a percentage. Employees are left wondering that if I have received a certain amount of hike, what exactly is the percentage when compared to my old or previous salary. In the same way, if percentage is mentioned, then the employee is left wondering what will be my new salary.  So, we have a very simple solution for y...
Read more

Find the Longest Streak of 1's in a Binary Array using Java Code

-
Image
  You have been given an Integer Array which contains only 1s and 0s, and asked to find the longest running streak of 0 or 1 in the array. Sound familiar? Its a common interview question. Let's try to find a solution using Java.  We would required two counters here, 1 counter to keep track of the running count, and the second one to keep track of the maximum running streak found.  We'll loop through the array using a for loop.  Here is the code for your reference: /**  * Find the longest Streak of 1s  * @author computengine.com  * @param a  * @return the length of the longest streak  */ public int findLongestStreak ( int [ ] b ) { System . out . println ( " Array for processing: " + Arrays . toString ( b ) ) ; int count = 0 , max = 0 ; //loop through the array for ( int i = 0 ; i < b . length ; i + + ) { //increment count when 1 is found if ( b [ i ] = = 1 ) count + = 1 ; //if count is greater...
Read more

Java Program to Read a Properties file and return a Property Value

-
Image
  Properties file in Java is used to store configurations, data or settings for a project. They have been utilized as Object Repositories for Automation Projects in the past (and still are used in legacy projects) until the Page Object Model came along.  This makes reading of Properties files an important concept in Java. This Tutorial will cover the below topics: 1. Create  Properties file in a Java Project 2. Set Properties in the file 3. Read the Properties file using Java code and return the value Let's first see how to create a Properties file for your Project. Its really easy.  In Eclipse, right click on the folder where you want to create the file, and navigate to New -> File. Now enter the file name, and make sure to enter the extension as .properties The file has been created! Now, the next step, i.e. enter some data in the file that we'll read using Java. The properties stored in the properties file have to follow a certain format.  You can enter l...
Read more

4 ways to add New Line Character to a String in Java

-
Image
When you are required to output Strings with your Java code, more often than not, there is a requirement to format the output correctly.  Adding a newline character between two strings in order to separate them, or create a paragraph is one of the most common requirements. Let's look at a few ways today, on how to output a newline between two Strings. 1. System.out.println inbuilt function One of the easiest ways, is to use the function System.out.println(). The println() function will automatically add a new line to the output, if using two strings.  Sample: String s1 = "This is a String." ; String s2 = "This is another String." ; System . out . println ( s1 ) ; System . out . println ( s2 ) ; If you don't want to use a separate System.out.println for every String, there are other ways as well. Read on! 2. Add a newline character "\n" You can simply append newline character yourself to the strings, and use a single System.out.print command...
Read more