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

--

--

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