Excel Write(simple and conditional) - Apache POI- Selenium - MacOS

 

As we already gave an idea  on our last tutorial of minimum requirement to run excel read /write Jar file of Apache POI

A simple way to enter data in excel file is  where our data file is blank and present in the path "/Users/priyankac/Desktop/Datatest.xlsx" reference to get a path location in Mac check the blog

Blank excel present with header

        @Test

public void test2() throws IOException{

//create object of fileoutput stream

FileInputStream fis = new FileInputStream("/Users/priyankac/Desktop/Datatest.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(fis);

XSSFSheet sheet = workbook.getSheetAt(0);

//As 0th row is occupied with header we will start entering data from 1st row, 0th column to 2nd column

one by one

sheet.getRow(1).createCell(0).setCellValue("Priyanka Chauhan");

sheet.getRow(1).createCell(1).setCellValue("500");

sheet.getRow(1).createCell(2).setCellValue("Java");

//entering data from 1st row, 0th column to 2nd column one by one

sheet.getRow(2).createCell(0).setCellValue("Mahendra das ");

sheet.getRow(2).createCell(1).setCellValue("450");

sheet.getRow(2).createCell(2).setCellValue("Python");

sheet.getRow(3).createCell(0).setCellValue("Dawar Monta");

sheet.getRow(3).createCell(1).setCellValue("450");

sheet.getRow(3).createCell(2).setCellValue("C#");

sheet.getRow(4).createCell(0).setCellValue("Poskar Ghosh");

sheet.getRow(4).createCell(1).setCellValue("650");

sheet.getRow(4).createCell(2).setCellValue("Pearl"); 

//To enter data in file we need to create object of fileoutput stream it will be used to write data in the workbook

FileOutputStream fos1 = new FileOutputStream("/Users/priyankac/Desktop/Datatest.xlsx");

workbook.write(fos1);

workbook.close();

}

}

After the program is run

Result- Excel

Now in the 2nd case we will take an example where you need to fill the data on condition.

for example iF sal is >500 then  "status" coulmn should be filled with Supervisor  if sal is <=500 then with  Director

Lets enter data In the excel 

First create a cell with name "Status" at (0 th row and 3rd column) and then  will create a for loop which will start from 1st row (O th row is header) and will go through all rows of column 1 which is "sal"

use switch statement to handles data is if its in string boolean or number and then put the if condition

if column 1 "sal" is >= 500 its director and if its less then its analyst

@Test

public class Exceldatawrite {

public void test2() throws IOException{

FileInputStream fis = new FileInputStream("/Users/priyankac/Desktop/Datatest.xlsx");

XSSFWorkbook workbook = new XSSFWorkbook(fis);

XSSFSheet sheet = workbook.getSheetAt(0);

sheet.getRow(0).createCell(3).setCellValue("Status");

//Total number of rows present in the sheeting

        int rowcount = sheet.getLastRowNum();

System.out.println("Number of Rows present" +" "+ rowcount);

//Total no of column present in the sheet

int  colcount = sheet.getRow(1).getLastCellNum();

System.out.println("Numer of columns present" +" "+ colcount);

//Setting up header "status" in (0 th row and 3rd column)

sheet.getRow(0).createCell(3).setCellValue("Status");

//for loop which will start from 1st row (O th row is header) 

for(int i=1; i<=rowcount; i++)

                   {

//starting from 1st row till last row and column 1 which is "sal"

XSSFCell cell= sheet.getRow(i).getCell(1);

// store it in String type variable which is cell text

String celltext="";

// handle the type of data in column 1 

                       switch(cell.getCellType())

{

case STRING:

celltext=cell.getStringCellValue();

        break;

case NUMERIC:

  celltext=String.valueOf(cell.getNumericCellValue());

  break;

case BOOLEAN:

  celltext=String.valueOf(cell.getBooleanCellValue());

  break;

}

//if value stored in celltext  which is column 1 value is >=500 its director

      if(Double.parseDouble(celltext)>=500)

        {

      sheet.getRow(i).createCell(3).setCellValue("Director");

        }

   

    else{

// if its less then 500 its Analyst

    sheet.getRow(i).createCell(3).setCellValue("Analyst");

        }

   

}

 // Close the file

fis.close();

//To enter data in excel

FileOutputStream fos = new FileOutputStream("/Users/priyankac/Desktop/Datatest.xlsx");

workbook.write(fos);

fos.close(); 

}

}

Final Result

Excel after update 

Remember:-1.Make sure your workbook(excel) is closed  before you run the program.

Remember:-2. We are using here apache poi version 5.0.0

few methods which worked in 4 and lower version won't work here like CELL_TYPE_STRING  so if you use this to handle datatype pf column 1 then it won't work

The overall run video of the above programs is here