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

Code Factory
4 min readApr 14, 2020

--

Reference Link : Link

Donate : 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");
}
}

--

--