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

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