Question

Can anyone show how to modify an existing excel sheet using Apache POI?

Answer 1

Hello Prakash, to modify an existing excel sheet using Apache POI, checkout this code. Here we write some text to 3rd column of the sheet:

public class WriteToExcel{
  WebDriver driver;
  WebDriverWait wait;
  HSSFWorkbook workbook;
  HSSFSheet sheet;
  HSSFCell cell;
        
  public void ReadData() throws IOException
  {
	System.setProperty("webdriver.gecko.driver", "C:\\Users\\geckodriver.exe");
	driver = new FirefoxDriver();
	driver.get("http://www.linkedin.com/");
	driver.manage().window().maximize();
	wait = new WebDriverWait(driver,30);
	driver.manage().timeouts().implicitlyWait(30, TimeUnit.SECONDS);
 
	File src=new File("C:\\Users\\Admin\\Desktop\\TestData.xls");
	FileInputStream finput = new FileInputStream(src);
        workbook = new HSSFWorkbook(finput);
	 
	sheet= workbook.getSheetAt(0);
	 
	for(int i=1; i<=sheet.getLastRowNum(); i++){
		 // Import data for Email.
		 cell = sheet.getRow(i).getCell(1);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-email")).sendKeys(cell.getStringCellValue());
		 
		 // Import data for password.
		 cell = sheet.getRow(i).getCell(2);
		 cell.setCellType(Cell.CELL_TYPE_STRING);
		 driver.findElement(By.id("login-password")).sendKeys(cell.getStringCellValue());
		 
		 // Write data in the excel.
	   FileOutputStream foutput=new FileOutputStream(src);
		
		// Specify the message needs to be written.
		String message = "Data Imported Successfully.";
		
		// Create cell where data needs to be written.
		sheet.getRow(i).createCell(3).setCellValue(message);
		 
		// Specify the file in which data needs to be written.
	    FileOutputStream fileOutput = new FileOutputStream(src);
	    
	    // finally write content
	    workbook.write(fileOutput);
		
	     // close the file
	    fileOutput.close();
	    	
	 }
 } 
}