excel code to write test case result to excel sheet in selenium WebDriver.

To Download Apache POI API Jar files, GO TO THIS PAGE and click on link "poi-bin-3.10-FINAL-20140208.tar.gz" (Version 3.10 may change In future.) under Binary Distribution table. It will take you to mirror sites list page to download Apache POI API Jar files folder. Click on any mirror site link and download and extract the folder.

You will find bellow given files Inside that folder or It's sub folders. Copy all bellow given files and paste them In "JarFiles" folder of "WDDF" project.

1.               poi-3.10-FINAL-20140208.jar

2.               poi-ooxml-3.10-FINAL-20140208.jar

3.               poi-ooxml-schemas-3.10-FINAL-20140208.jar

4.               xmlbeans-2.3.0.jar (Inside ooxml-lib folder)

5.               dom4j-1.6.1.jar (Inside ooxml-lib folder)

(Note : Above jar files versions may change In future. Always Use latest version only.).

public void setTestStatus(int testID, String status) {
try {

int colNum = -1;
FileInputStream f = new FileInputStream(new File("DataSheet.xls"));
HSSFWorkbook workbook = new HSSFWorkbook(f);
HSSFSheet sheet = workbook.getSheet("Sheet1");
HSSFRow row = sheet.getRow(0);
for (int i = 0; i < row.getLastCellNum(); i++) {
if (row.getCell(i).getStringCellValue().trim().equals("status")) {
colNum = i;
System.out.println("last cell number is" + "---" + i);
}
}
int rows = sheet.getPhysicalNumberOfRows();
for (int j = 1; j < rows; j++) {
HSSFRow grow = sheet.getRow(j);
String cellvalue = grow.getCell(0).getStringCellValue();
int excelID = Integer.parseInt(cellvalue);
if (excelID == testID) {

if (status.contains("Pass")) {
HSSFCellStyle style = workbook.createCellStyle();
style.setFillForegroundColor(HSSFColor.GREEN.index);
style.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);

HSSFFont blueFont = workbook.createFont();
blueFont.setColor(HSSFColor.DARK_GREEN.index);
HSSFCellStyle style2 = workbook.createCellStyle();
style.setFont(blueFont);

grow.getCell(colNum).setCellValue(status);
grow.getCell(colNum).setCellStyle(style2);
grow.getCell(colNum).setCellStyle(style);

} else {
HSSFCellStyle style1 = workbook.createCellStyle();
style1.setFillForegroundColor(HSSFColor.RED.index);
style1.setFillPattern(HSSFCellStyle.SOLID_FOREGROUND);
grow.getCell(colNum).setCellValue(status);
grow.getCell(colNum).setCellStyle(style1);
}
FileOutputStream fileOut = new FileOutputStream(new File(
"DataSheet.xls"));
workbook.write(fileOut);
fileOut.close();

break;
}

}

}

catch (Exception e) {
System.out.println(e.getLocalizedMessage());
}
}

/**
* @param args
* @throws IOException
*/
public static void main(String[] args) throws IOException {

ExcelCode obj = new ExcelCode();
obj.setTestStatus(200, "Pass");
obj.setTestStatus(100, "Fail");
obj.setTestStatus(300, "Pass");
obj.setTestStatus(700, "Pass");
}

}