How to create a new sheet and write some data in the existing excel using selenium java

Question

I tried the below code.

But it overwrite the existing sheet.

File f= new File(System.getProperty("user.dir")+"\\src\\test\\resources\\Exceldata.xls");   

    HSSFWorkbook workbook = new HSSFWorkbook();         
    HSSFSheet worksheet= workbook.createSheet("Sheet4");        
    HSSFRow row = worksheet.createRow(1);
    HSSFCell cell= row.createCell(1);
    cell.setCellValue("admin");     

enter code here


    workbook.write(f);
    workbook.close();

How does it relate to `Selenium`? – undetected Selenium Oct 11 '18 at 10:52 — undetected Selenium, Oct 11 '18 at 10:52

Ashish Kamble · Answer 1 · 2018-10-11T10:35:30.777

Use FileInputStream instead of File and and object of XSSFWorkbook I hope this function may help you,

public static void write(){
       try
       {
           FileInputStream myxls = new FileInputStream(System.getProperty("user.dir")+"\\src\\test\\resources\\Exceldata.xls"  );
           HSSFWorkbook studentsSheet = new HSSFWorkbook(myxls);
           workbook = new XSSFWorkbook(myxls );
           workbook.createSheet(sheetname);
           HSSFSheet worksheet = studentsSheet.getSheetAt(0);
           a=worksheet.getLastRowNum();
           System.out.println(a);
           Row row = worksheet.createRow(++a);
           row.createCell(1).setCellValue("");
           myxls.close();
           FileOutputStream output_file =new FileOutputStream(new File(System.getProperty("user.dir")+"\\src\\test\\resources\\Exceldata.xls"));  
           //write changes
           workbook.write(output_file );

           studentsSheet.write(output_file);
           output_file.close();
           System.out.println(" is successfully written");
       }

Try calling this function from main maethod,

public static void main(String args[])
   {
       write();
   }

Possible duplicate of Append Data in existing Excel file using apache poi in java and How to add new sheets to existing excel workbook using apache POI?

Thanks Ashish for the reply. But does your code create a new sheet. My requirement is to create a new sheet in the already existing excel . — Harivignesh Vicky, Oct 11 '18 at 10:24
To brief my requirement : i have a excel named "Records" which has a sheet named Sheet1 and having some data. Now i want to create a new sheet named "Sheet2" in the same excel and wanted to write some data — Harivignesh Vicky, Oct 11 '18 at 10:26
I tried the above links that you have given and it still didn't worked for me. So i posted the question here — Harivignesh Vicky, Oct 11 '18 at 12:52

score 0 · Answer 2 · answered Oct 17 '18 at 05:44

0

I finally found out the solution. i had used poi jar 4.0 in which i could not succeed in writing data. I then downgraded the jar version to 3.14 and i works perfectly

answered Oct 17 '18 at 05:44

Harivignesh Vicky

1
2

How to create a new sheet and write some data in the existing excel using selenium java

2 Answers2