In the previous tutorial, I have explained about create a new excel file and write data in that new excel file using Java. In this tutorial, I will explain How to open and update existing excel files in Java.
I’m using Apache POI to write data to the excel file. To download and install Apache POI, refer here.
If you are using maven, then you need to add below dependency in pom.xml.
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi</artifactId>
<version>5.0.0</version>
</dependency>
<!-- https://mvnrepository.com/artifact/org.apache.poi/poi-ooxml -->
<dependency>
<groupId>org.apache.poi</groupId>
<artifactId>poi-ooxml</artifactId>
<version>5.0.0</version>
</dependency>
To know more about various interfaces and classes for managing Excel, please refer to this tutorial.
In the below example, I have an existing excel with the name of “EmployeeDetails.xlsx”
This is excel which I am going to update. I will add 2 rows below the last row in the excel.
Below is the complete example of updating the data in existing excel. One thing we need to note here is that we can update the excel file only when we close it first.
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFRow;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class ExcelFileUpdateExample {
public static void main(String[] args) {
try {
FileInputStream fis = new FileInputStream(new File("EmployeeDetails.xlsx"));
// Create object of XSSFWorkbook class
XSSFWorkbook workbook = new XSSFWorkbook(fis);
// Create object of XSSFWorkbook class
XSSFSheet sheet = workbook.getSheet("Write_TestData");
Object[][] bookData = { { "SeleniumTest", "0000A", 9999 }, { "JavaTest", "0000B", 9990 }, };
// Get last row in Sheet
int rowCount = sheet.getLastRowNum();
for (Object[] Book : bookData) {
// Create row for all the new data
XSSFRow row = sheet.createRow(++rowCount);
int columnCount = 0;
// Create new cell for each row
XSSFCell cell = row.createCell(columnCount);
for (Object field : Book) {
cell = row.createCell(columnCount++);
if (field instanceof String) {
cell.setCellValue((String) field);
} else if (field instanceof Integer) {
cell.setCellValue((Integer) field);
}
}
}
fis.close();
// Write the workbook in file system
FileOutputStream outputStream = new FileOutputStream("EmployeeDetails.xlsx");
workbook.write(outputStream);
System.out.println("Excel is updated successfully");
// Close the workbook
workbook.close();
outputStream.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
Below is the updated excel.
Update a specific cell in the Excel file
In the below example, I will update the value in a specific cell. As you can see the above image, I will update 1004S to “Updated Value”. Below is the code snippet for the same.
XSSFCell cell = sheet.getRow(4).getCell(1);
cell.setCellType(CellType.STRING);
cell.setCellValue("Updated Value");
Below is the entire program.
package com.example.seleniumdemo;
import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.ss.usermodel.CellType;
import org.apache.poi.xssf.usermodel.XSSFCell;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
public class UpdateExcelDemo {
public static void main(String[] args) {
try {
// Create an object of FileInputStream class to read excel file
FileInputStream fis = new FileInputStream(new File("EmployeeDetails.xlsx"));
// Create object of XSSFWorkbook class
XSSFWorkbook workbook = new XSSFWorkbook(fis);
// Read excel sheet by sheet name
XSSFSheet sheet = workbook.getSheet("Write_TestData");
// Print data present at row 0 column 2
System.out.println(sheet.getRow(4).getCell(1).getStringCellValue());
// Get the Cell at index 3 from the above row
XSSFCell cell = sheet.getRow(4).getCell(1);
cell.setCellType(CellType.STRING);
cell.setCellValue("Updated Value");
// Write the output to the file
FileOutputStream fileOut = new FileOutputStream(new File("EmployeeDetails.xlsx"));
workbook.write(fileOut);
System.out.println("Id column in Excel is updated successfully");
fileOut.close();
// Closing the workbook
workbook.close();
} catch (IOException e) {
e.printStackTrace();
}
}
}
That’s it! Well done.
Congratulations on making it through this tutorial and hope you found it useful! Happy Learning!! Cheers!!
QA Automation Expert 
Great job. This is quite interesting and we’ll explained.
LikeLike