Overview
In this post, we will go through how to update excel file and modify its cell values.
Steps to download and configure Apache POI in the Java project:
https://www.testingdocs.com/download-apache-poi/
Add Jar file to the project:
https://www.testingdocs.com/add-apache-poi-to-java-project/
Let’s take the same file TDocs.xlsx as an example. The file consists of sample table with movie names in two columns.
Program to Update Excel Cell
import java.io.File; import java.io.FileInputStream; import java.io.FileOutputStream; import java.io.IOException; import java.util.ArrayList; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.xssf.usermodel.XSSFRow; import org.apache.poi.xssf.usermodel.XSSFSheet; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class WriteXL { String filename = null; File file = null; FileInputStream fis = null; XSSFWorkbook workbook = null; XSSFSheet sheet = null ; FileOutputStream fos = null ; public WriteXL(String filename) { this.filename=filename; } public void modifyCell(int rowNumber,int columnNumber,String sheetName, String valueToWrite) throws IOException{ try { file = new File(filename); fis = new FileInputStream(file); workbook = new XSSFWorkbook(fis); sheet = workbook.getSheet(sheetName); Cell cell = sheet.getRow(rowNumber).getCell(columnNumber); XSSFRow row = sheet.getRow(rowNumber); cell = row.getCell(columnNumber, Row.RETURN_BLANK_AS_NULL); if (cell != null) { cell.setCellValue(valueToWrite); } else { throw new Exception("Cell Exception."); } fos = new FileOutputStream(filename); workbook.write(fos); }catch (Exception e) { System.out.println("ERROR : Not able to update the cell"); e.printStackTrace(); } finally{ if(fis!= null || fos != null) { fis.close(); fos.flush(); fos.close(); } } } public static void main(String... args) throws IOException { WriteXL writeExcel = new WriteXL("TDocs.xlsx"); writeExcel.modifyCell(2, 0, "MovieList", "Oz"); writeExcel.modifyCell(2, 1, "MovieList", "The Wizard"); ArrayList<String> consume = new ArrayList<String>(); ReadXL readExcel = new ReadXL("TDocs.xlsx"); consume = readExcel.getMyRow(2, 1, "MovieList"); System.out.println("**********OutPut************"); for(String s : consume) { System.out.println(s); } } }
Output of the program:
**********OutPut************
Oz
The Wizard
File After running the program:
—
Apache POI Tutorials:
https://www.testingdocs.com/apache-poi-tutorials/
More Information on Apache POI API: