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

<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
</dependency>
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);
}
}
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");
}
}

--

--

--

Love podcasts or audiobooks? Learn on the go with our new app.

Recommended from Medium

What the crud is CRUD?

Becoming a “Programer”

Learning Kubernetes with MicroK8s and Multipass. Part-1

Software Engineering

Solutions Architect Tips: How to Build Your First Architecture Diagram

Ensuring Natural-Scrolling Mouse and Touchpad Settings Are Set Correctly When Reconnecting Devices

Observables — reactive event driven coding in Android, Angular, Swift, Xamarin Forms

Create a simple to-do list app with Python Flask and Vue.js. Part 2

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store
Code Factory

Code Factory

More from Medium

Top 10 Git Commands which help you!

Open-source SPL that can execute SQL without RDB

Power of Singleton Design Pattern

Where to Store Application Data in Azure