Write / Read Data into / from Excel file using Java | Code Factory


Donate : Link

Medium Blog : Link

Applications : Link

We Read and Write on Excel file with the help of Java IO package and Apache POI library.

To read or write an Excel, Apache provides a very famous library POI. This library is capable enough to read and write both XLS and XLSX file format of Excel.

To read XLS files, an HSSF implementation is provided by POI library.

To read XLSX files, an XSSF implementation is provided by POI library.

If you are using Maven in your project, the Maven dependency will be

<dependency>
    <groupId>org.apache.poi</groupId>
    <artifactId>poi</artifactId>
</dependency>

Or you can simply download the latest version POI jars from http://poi.apache.org/download.html & download the latest zip file.

WriteDataIntoExcel.java

package com.example;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
import org.apache.poi.ss.usermodel.Cell;
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;

/**
 * @author code.factory
 */
public class WriteDataIntoExcel {

	public void writeExcel(String filePath, String fileName, String sheetName, String[][] dataToWrite) throws IOException {

		// Create an object of File class to open XLSX or XLS file
		File file = new File(filePath + "\\" + fileName);
		
		// Create an object of FileInputStream class to read excel file
		FileInputStream inputStream = new FileInputStream(file);

		Workbook Workbook = null;

		// Find the file extension by splitting file name in substring and getting only extension name
		String fileExtensionName = fileName.substring(fileName.indexOf("."));

		// Check condition if the file is xlsx file
		if (fileExtensionName.equals(".xlsx")) {

			// If it is xlsx file then create object of XSSFWorkbook class
			Workbook = new XSSFWorkbook(inputStream);

		}

		// Check condition if the file is xls file
		else if (fileExtensionName.equals(".xls")) {

			// If it is xls file then create object of HSSFWorkbook class
			Workbook = new HSSFWorkbook(inputStream);

		}

		// Read excel sheet by sheet name
		Sheet sheet = Workbook.getSheet(sheetName);

		Row newRow = null;
		int rowCount = 0;
		
		if(sheet == null) {
			// If sheet is null then create new sheet
			sheet = Workbook.createSheet(sheetName);
		} else {
			// Get the current count of rows in excel file
			rowCount = (sheet.getLastRowNum() - sheet.getFirstRowNum()) + 1;
		}

		// Create a loop over the cell of newly created Row
		for (int i = 0; i < dataToWrite.length; i++) {
			
			// Create a new row and append it at last of sheet
			newRow = sheet.createRow(rowCount);
			
			// Increase Row number
			rowCount++;
			
			for(int j=0; j < dataToWrite[i].length; j++) {
				// Fill data in row
				Cell cell = newRow.createCell(j);
				cell.setCellValue(dataToWrite[i][j]);
			}

		}

		// Close input stream
		inputStream.close();

		// Create an object of FileOutputStream class to create write data in excel file
		FileOutputStream outputStream = new FileOutputStream(file);

		// write data in the excel file
		Workbook.write(outputStream);

		// close output stream
		outputStream.close();

	}

	public static void main(String... strings) throws IOException {

		// Create an array with the data in the same order in which you expect to be filled in excel file
		String[][] valueToWrite = {{ "Id", "First Name", "Last Name"}, {"id1", "F1", "L1"}};

		// Create an object of current class
		WriteDataIntoExcel objExcelFile = new WriteDataIntoExcel();

		// Write the file using file name, sheet name and the data to be filled
		objExcelFile.writeExcel(System.getProperty("user.dir") + "\\doc", "Example.xlsx", "Data", valueToWrite);

	}
}

ReadDataFromExcel.java

package com.example;

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

import org.apache.poi.hssf.usermodel.HSSFWorkbook;
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;

/**
 * @author code.factory
 */
public class ReadDataFromExcel {

	public void readExcel(String filePath, String fileName, String sheetName) throws IOException {

		// Create an object of File class to open xlsx file
		File file = new File(filePath + "\\" + fileName);

		// Create an object of FileInputStream class to read excel file
		FileInputStream inputStream = new FileInputStream(file);

		Workbook workbook = null;

		// Find the file extension by splitting file name in substring and getting only extension name
		String fileExtensionName = fileName.substring(fileName.indexOf("."));

		// Check condition if the file is xlsx file
		if (fileExtensionName.equals(".xlsx")) {

			// If it is xlsx file then create object of XSSFWorkbook class
			workbook = new XSSFWorkbook(inputStream);

		}

		// Check condition if the file is xls file
		else if (fileExtensionName.equals(".xls")) {

			// If it is xls file then create object of HSSFWorkbook class
			workbook = new HSSFWorkbook(inputStream);

		}

		// Read sheet inside the workbook by its name
		Sheet sheet = workbook.getSheet(sheetName);

		// Find number of rows in excel file
		int rowCount = sheet.getLastRowNum() - sheet.getFirstRowNum();

		// Create a loop over all the rows of excel file to read it
		for (int i = 0; i < rowCount + 1; i++) {
			Row row = sheet.getRow(i);

			System.out.print(i + 1 + " -> ");

			// Create a loop to print cell values in a row
			for (int j = 0; j < row.getLastCellNum(); j++) {

				// Print Excel data in console
				System.out.print(row.getCell(j) + " || ");
			}
			System.out.println();
		}
	}

	// Main function is calling readExcel function to read data from excel file
	public static void main(String[] strings) throws IOException {

		// Create an object of Current class
		ReadDataFromExcel objExcelFile = new ReadDataFromExcel();

		// Prepare the path of excel file
		String filePath = System.getProperty("user.dir") + "\\doc";

		// Call read file method of the class to read data
		objExcelFile.readExcel(filePath, "Example.xlsx", "Data");
	}
}

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s