Update cell values in the same Excel File. || File Handling

Sometimes we need to update cell values in the same excel file. There is no need to create a new copy of the excel file. In this blog, we will learn how to update cell values in the same excel file. Here we will use the same excel file to read and write the data.

Update Cell Values

Suppose we have a excel file named “Test.xlsx” in which data is present in below format.

Template of excel file having values in 6 rows and 2 columns to update cell values.

This process is very simple, we just need to take care of few things.

  1. Locate the physical location of the file using File object. 
  2. Load the file into the memory stream using FileInputStream object.
  3. Now create an object of XSSFWorkbook by passing the file input stream object.
  4. Next step to create an object of XSSFSheet in the above workbook and locate the correct sheet using getSheetAt(index) method.
  5. Now, locate any cell reference which you want to update. For example, we want to replace “Laptop” with “CPU“. Hence cell reference would be row=3 and column=1.
  6. First will store this value in another String variable for future reference using getStringCellValue().
  7. Then, create an object of FileOutputStream and load the excel file, so that we could update data in memory.
  8. The next step is very important as now we are going to locate and update the data. To update data we will use setCellValue(newValue) method.
  9. Now we have to update the data from memory to a physical excel file. We could perform this action using the write() method and passing the FileOutputStream object in it.
  10. Print the same cell reference using the getStringCellValue() to verify the data.
  11. The last step is to close the workbook object using close() method. It is advisory to avoid any conflict in a large program.
  12. You can open the document and check the update as below:
Excel file having text 6 rows and 2 column after a cell value replaced.

Code Example

import java.io.File;
import java.io.FileInputStream;
import java.io.FileNotFoundException;
import java.io.FileOutputStream;
import java.io.IOException;
import org.apache.poi.xssf.usermodel.XSSFSheet;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;

/**
 * @author ashok.kumar
 *
 */
public class ExcelOverwrite {

	/**
	 * @param args
	 * @throws IOException 
	 */
	public static void main(String[] args) throws IOException {
		// TODO Auto-generated method stub
		
		// Locate the Physical excel file
		File file=new File("input\\Test.xlsx");
		
		// Create object of FileInputStream and load the file
		FileInputStream fis=new FileInputStream(file);
		
		// Create object to load workbook
		XSSFWorkbook wb=new XSSFWorkbook(fis);
		
		// Extract the required sheet in the workbook.
		XSSFSheet sheet=wb.getSheetAt(0);
		
		// Store specific cell value in the String Object
		String currentData=sheet.getRow(3).getCell(1).getStringCellValue();
		
		System.out.println("Original Data: "+currentData);
		
		// Create object of FileOutputStream and load the same XLSX file.
		FileOutputStream fos=new FileOutputStream(file);
		
		// Locate to the specific cell and give new Value via setCellValue method.
		sheet.getRow(3).getCell(1).setCellValue("CPU");
		
               System.out.println("After replacing the value");
		
              // Again read the value from the same cell reference.
		currentData=sheet.getRow(3).getCell(1).getStringCellValue();
		System.out.println("New Data: "+currentData);
		
		// update the data in physical XLSX file using write()
		wb.write(fos);
		
		// Close the workbook to avoid any conflict while using in large program.
		wb.close();
	}
}

// Program Output
Original Data: Laptop
 After replacing the value
 New Data: CPU

File Handling:

Leave a Comment

Your email address will not be published. Required fields are marked *

   YouTube ChannelQuora

            Ashok Kumar is working in an IT Company as a QA Consultant. He has started his career as a Test Trainee in manual testing in August 2010. Then he moves towards the automation testing after 4 years. He started learning JAVA and Selenium by self to get the knowledge of automation.

       While learning these tools and working on multiple projects, he found that sometimes people get stuck in live scenarios in their project and they have to do lots of RnD to get out of it. So he decided to start blogging only for such scenarios, where anyone facing any problem in their project, can ask any question or give a solution or you can say an alternate solution to achieve the goal successfully.

Later on, he observed that some people want to learn Java but they have few questions in their mind like how to start Java, whether we should go for the online or offline course. So he started writing tutorials on Java, Jira, Selenium, Excel etc.