Quality Testing

Quality is delighting customers

how to update data in a xls file using selenium webdriver

how to update data in a xls file using selenium webdriver

Views: 919

Reply to This

Replies to This Discussion

Hi,

Please ref below code. 

package interview;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.IOException;

import org.apache.poi.EncryptedDocumentException;
import org.apache.poi.openxml4j.exceptions.InvalidFormatException;
import org.apache.poi.ss.usermodel.Sheet;
import org.apache.poi.ss.usermodel.Workbook;
import org.apache.poi.ss.usermodel.WorkbookFactory;

public class WriteExcel_1way {

public static void main(String[] args) throws IOException {

File src=new File("C:\\Deepika\\excel\\testdeep1.xlsx");
FileInputStream fis=new FileInputStream(src);
Workbook wb = null;
try {
wb=WorkbookFactory.create(src);
wb=WorkbookFactory.create(fis);
Sheet sh=wb.getSheetAt(0);


//To create new row & new cell
sh.createRow(9).createCell(0).setCellValue("22222");


//To updated created new cell value
sh.getRow(9).getCell(0).setCellValue("*******");

FileOutputStream fout=new FileOutputStream(src);
wb.write(fout);

System.out.println(sh.getRow(9).getCell(0).getStringCellValue());

} catch (EncryptedDocumentException | InvalidFormatException
| IOException e) {
// TODO Auto-generated catch block
e.printStackTrace();
}
finally{
wb.close();
}

}

}

Please let me know. If you have any doubt.

try {
File file = new File("EXCEL_FILE_PATH");

FileInputStream fis = new FileInputStream(new File(
file ));
XSSFWorkbook workbook = new XSSFWorkbook(fis);

XSSFSheet sheet = workbook.getSheetAt(0);

Cell c1= sheet.getRow(2).getCell(1);
c1.setCellValue("Base Product");
Cell c2= sheet.getRow(2).getCell(3);
c2.setCellValue("prodNumber");
Cell c3= sheet.getRow(2).getCell(4);
c3.setCellValue("LEM product");
Cell c4= sheet.getRow(2).getCell(5);
c4.setCellValue(Product_Number);

fis.close();

FileOutputStream outFile = new FileOutputStream(new File(
"filepath"));
workbook.write(outFile);
outFile.close();
System.out.println("Data succesfully Added to Excel File");

} catch (FileNotFoundException fnfe) {
fnfe.printStackTrace();
} catch (IOException ioe) {
ioe.printStackTrace();
}

}

Hi,

Please use below code snippet generally used by testing experts in website automated testing:

public class excel {

public static void writeXLSXFile(int row, int col) throws IOException {
try {
FileInputStream file = new FileInputStream("C:\\Users\\\\Documents\\Book1.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(file);
XSSFSheet sheet = workbook.getSheetAt(0);
Cell cell = null;

//Retrieve the row and check for null
XSSFRow sheetrow = sheet.getRow(row);
if(sheetrow == null){
sheetrow = sheet.createRow(row);
}
//Update the value of cell
cell = sheetrow.getCell(col);
if(cell == null){
cell = sheetrow.createCell(col);
}
cell.setCellValue("Pass");

file.close();

FileOutputStream outFile =new FileOutputStream(new File("C:\\Users\\\\Documents\\Book2.xlsx"));
workbook.write(outFile);
outFile.close();

} catch (FileNotFoundException e) {
e.printStackTrace();
} catch (IOException e) {
e.printStackTrace();
}
}

public static void main(String[] args) throws IOException {
// TODO Auto-generated method stub
writeXLSXFile(3, 3);
}

}

org.apache.poi
poi-ooxml
3.9


--> Above dependencies need to be available while running this code.

Hope this will help you.

Thanks
Anand Singh

RSS

TTWT Magazine


Advertisement

Advertisement

Advertisement

Advertisement

© 2022   Created by Quality Testing.   Powered by

Badges  |  Report an Issue  |  Terms of Service