After a long discussion in Data Driven frameworks in Selenium: Part1: Data Driven Framework in Selenium Webdriver Using Apache POI Part2: Passing data to DataProvider from Excel Sheet in TestNG We know about how we can pass parameters from excel sheet to our program by selenium. But in this article we will know about how user can create a excel workbook and sheet by selenium. Write data to excel using column name will discuss about how we write test data/test results to the excel sheet while automating any application using selenium web driver. We might get doubt in mind that why we do it? what is purpose to put test result in excel sheet. The main reason behind is that if application need so much data, and thousands numbers of data to be feed to test like product creation, we need multiple data at that time i.e price, sku, colour, brand…. etc. So we will manage those data using external sources like test file, XML file, and excel file…..etc. Among those excel is user friendly we can organize the data very easily, we can read and write data how we want. so we mostly use excel to manage our data. Creating a New Work Book package framework; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class createworkbook { public static void main(String args[]) throws IOException { //To create a new WorkBook with xls extension Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("D:\\Test.xls"); wb.write(fileOut); fileOut.close(); //To create a new WorkBook with xlsx extension Workbook wb1 = new XSSFWorkbook(); FileOutputStream fileOut1 = new FileOutputStream("D:\\Test1.xlsx"); wb.write(fileOut1); fileOut1.close(); } } 1234567891011121314151617181920212223242526272829303132333435363738394041 package framework; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.xssf.usermodel.XSSFWorkbook; public class createworkbook { public static void main(String args[]) throws IOException { //To create a new WorkBook with xls extension Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("D:\\Test.xls"); wb.write(fileOut); fileOut.close(); //To create a new WorkBook with xlsx extension Workbook wb1 = new XSSFWorkbook(); FileOutputStream fileOut1 = new FileOutputStream("D:\\Test1.xlsx"); wb.write(fileOut1); fileOut1.close(); } } By Run as java application a file created as below : Creating a New Sheet package framework; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hslf.model.Sheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.WorkbookUtil; public class createnewsheet { public static void main(String args[]) throws IOException { //create a new workbook Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("/home/users/shivani.kukreti/Documents/Test.xls"); //create a new sheet with sheet name Vineet1 org.apache.poi.ss.usermodel.Sheet sheet1 = wb.createSheet("Sheet1"); //create a new sheet with sheet name Vineet2 org.apache.poi.ss.usermodel.Sheet sheet2 = wb.createSheet("Sheet2"); //create a new sheet with sheet name Expense Details String safeName = WorkbookUtil.createSafeSheetName("[Test Details*?]"); org.apache.poi.ss.usermodel.Sheet sheet3 = wb.createSheet(safeName); wb.write(fileOut); fileOut.close(); } } 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869 package framework; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; import org.apache.poi.hslf.model.Sheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Workbook; import org.apache.poi.ss.util.WorkbookUtil; public class createnewsheet { public static void main(String args[]) throws IOException { //create a new workbook Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("/home/users/shivani.kukreti/Documents/Test.xls"); //create a new sheet with sheet name Vineet1 org.apache.poi.ss.usermodel.Sheet sheet1 = wb.createSheet("Sheet1"); //create a new sheet with sheet name Vineet2 org.apache.poi.ss.usermodel.Sheet sheet2 = wb.createSheet("Sheet2"); //create a new sheet with sheet name Expense Details String safeName = WorkbookUtil.createSafeSheetName("[Test Details*?]"); org.apache.poi.ss.usermodel.Sheet sheet3 = wb.createSheet(safeName); wb.write(fileOut); fileOut.close(); } } Output results A new excelsheet with name Test.xls is created with Sheet name as “sheet1”, “sheet2”, “sheet3”. Creating Cells package framework; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; //import org.apache.poi.hslf.model.Sheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; public class CreatingCell { public static void main(String args[]) throws IOException { Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("/home/users/shivani.kukreti/Documents/Test.xls"); // Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); org.apache.poi.ss.usermodel.Sheet sheet = wb .createSheet("ShivaniSheet1"); // Create the first Row Row row1 = sheet.createRow((short) 0); // inserting first row cell value row1.createCell(0).setCellValue( createHelper.createRichTextString("Serial Number")); row1.createCell(1).setCellValue( createHelper.createRichTextString("Product-Name")); row1.createCell(2).setCellValue( createHelper.createRichTextString("Product-description")); row1.createCell(3).setCellValue( createHelper.createRichTextString("Product-sku")); row1.createCell(4).setCellValue( createHelper.createRichTextString("Porduct-model")); // creating second row Row row2 = sheet.createRow((short) 1); // inserting first row cell value row2.createCell(0).setCellValue(001); row2.createCell(1).setCellValue( createHelper.createRichTextString("Tshirt")); row2.createCell(2).setCellValue( createHelper.createRichTextString("Tshirt-description")); row2.createCell(3).setCellValue( createHelper.createRichTextString("0087l")); row2.createCell(4).setCellValue( createHelper.createRichTextString("Tshirt-model")); // Write the output to a file wb.write(fileOut); fileOut.close(); } } 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263646566676869707172737475767778798081828384858687888990919293949596979899100101102103104105106107108109110111112113114115116117118119120121122123124125126127128 package framework; import java.io.FileNotFoundException; import java.io.FileOutputStream; import java.io.IOException; //import org.apache.poi.hslf.model.Sheet; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.ss.usermodel.Cell; import org.apache.poi.ss.usermodel.CreationHelper; import org.apache.poi.ss.usermodel.Row; import org.apache.poi.ss.usermodel.Workbook; public class CreatingCell { public static void main(String args[]) throws IOException { Workbook wb = new HSSFWorkbook(); FileOutputStream fileOut = new FileOutputStream("/home/users/shivani.kukreti/Documents/Test.xls"); // Workbook wb = new XSSFWorkbook(); CreationHelper createHelper = wb.getCreationHelper(); org.apache.poi.ss.usermodel.Sheet sheet = wb .createSheet("ShivaniSheet1"); // Create the first Row Row row1 = sheet.createRow((short) 0); // inserting first row cell value row1.createCell(0).setCellValue( createHelper.createRichTextString("Serial Number")); row1.createCell(1).setCellValue( createHelper.createRichTextString("Product-Name")); row1.createCell(2).setCellValue( createHelper.createRichTextString("Product-description")); row1.createCell(3).setCellValue( createHelper.createRichTextString("Product-sku")); row1.createCell(4).setCellValue( createHelper.createRichTextString("Porduct-model")); // creating second row Row row2 = sheet.createRow((short) 1); // inserting first row cell value row2.createCell(0).setCellValue(001); row2.createCell(1).setCellValue( createHelper.createRichTextString("Tshirt")); row2.createCell(2).setCellValue( createHelper.createRichTextString("Tshirt-description")); row2.createCell(3).setCellValue( createHelper.createRichTextString("0087l")); row2.createCell(4).setCellValue( createHelper.createRichTextString("Tshirt-model")); // Write the output to a file wb.write(fileOut); fileOut.close(); } } Ouput Results: Insert extra cells or overwrite cells package framework; import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.*; import java.util.Iterator; public class create_extracell { public static void main(String[] args) throws Exception{ FileInputStream fsIP= new FileInputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Read the spreadsheet that needs to be updated HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it. Cell cell = null; // declare a Cell object cell = worksheet.getRow(1).getCell(0); // Access the second cell in second row to update the value cell.setCellValue("0001"); // Get current cell value value and overwrite the value cell=worksheet.getRow(0).createCell(5); //Set Result is pass in that cell number cell.setCellValue("Produc-export"); cell=worksheet.getRow(1).createCell(5); //Set Result is pass in that cell number cell.setCellValue("yes"); cell = worksheet.getRow(1).getCell(1); cell.setCellValue("pants"); cell = worksheet.getRow(1).getCell(2); cell.setCellValue("pants-description"); cell = worksheet.getRow(1).getCell(3); cell.setCellValue("pants-sku009"); cell = worksheet.getRow(1).getCell(4); cell.setCellValue("pantstype"); fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream } } 123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960 package framework;import java.io.*;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.ss.usermodel.*;import java.util.Iterator;public class create_extracell { public static void main(String[] args) throws Exception{ FileInputStream fsIP= new FileInputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Read the spreadsheet that needs to be updated HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it. Cell cell = null; // declare a Cell object cell = worksheet.getRow(1).getCell(0); // Access the second cell in second row to update the value cell.setCellValue("0001"); // Get current cell value value and overwrite the value cell=worksheet.getRow(0).createCell(5); //Set Result is pass in that cell number cell.setCellValue("Produc-export"); cell=worksheet.getRow(1).createCell(5); //Set Result is pass in that cell number cell.setCellValue("yes"); cell = worksheet.getRow(1).getCell(1); cell.setCellValue("pants"); cell = worksheet.getRow(1).getCell(2); cell.setCellValue("pants-description"); cell = worksheet.getRow(1).getCell(3); cell.setCellValue("pants-sku009"); cell = worksheet.getRow(1).getCell(4); cell.setCellValue("pantstype"); fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream }} Output Result Deleting cell package framework; import java.io.*; import org.apache.poi.hssf.usermodel.HSSFWorkbook; import org.apache.poi.hssf.usermodel.HSSFSheet; import org.apache.poi.ss.usermodel.*; import java.util.Iterator; public class delete_cell { public static void main(String[] args) throws Exception{ FileInputStream fsIP= new FileInputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Read the spreadsheet that needs to be updated HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it. // Cell cell = null; // declare a Cell object Row row = wb.getSheetAt(0).getRow(0); // Access the second cell in second row to update the value row.removeCell(row.getCell(0)); row.removeCell(row.getCell(1)); //Set Result is pass in that cell number row.removeCell(row.getCell(2)); row.removeCell(row.getCell(3)); row.removeCell(row.getCell(4)); fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream } } 1234567891011121314151617181920212223242526272829303132333435363738394041 package framework;import java.io.*;import org.apache.poi.hssf.usermodel.HSSFWorkbook;import org.apache.poi.hssf.usermodel.HSSFSheet;import org.apache.poi.ss.usermodel.*;import java.util.Iterator;public class delete_cell { public static void main(String[] args) throws Exception{ FileInputStream fsIP= new FileInputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Read the spreadsheet that needs to be updated HSSFWorkbook wb = new HSSFWorkbook(fsIP); //Access the workbook HSSFSheet worksheet = wb.getSheetAt(0); //Access the worksheet, so that we can update / modify it. // Cell cell = null; // declare a Cell object Row row = wb.getSheetAt(0).getRow(0); // Access the second cell in second row to update the value row.removeCell(row.getCell(0)); row.removeCell(row.getCell(1)); //Set Result is pass in that cell number row.removeCell(row.getCell(2)); row.removeCell(row.getCell(3)); row.removeCell(row.getCell(4)); fsIP.close(); //Close the InputStream FileOutputStream output_file =new FileOutputStream(new File("/home/users/shivani.kukreti/Documents/Test.xls")); //Open FileOutputStream to write updates wb.write(output_file); //write changes output_file.close(); //close the stream }} Output Results: Category(s) Uncategorized