Selenium : Excel operations

Selenium : Excel operations 

Please visit http://poi.apache.org/spreadsheet/quick-guide.html#NewWorkbook  for more details 

Link to download JAR files for Apache POI : http://poi.apache.org/download.html#POI-3.9 and http://www.apache.org/dyn/closer.cgi/poi/release/bin/poi-bin-3.9-20121203.zip (might change according to the new update ).

//New Workbook

Workbook wb = new HSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

Workbook wb = new XSSFWorkbook();

FileOutputStream fileOut = new FileOutputStream("workbook.xlsx");

wb.write(fileOut);

fileOut.close();

//New Sheet

Workbook wb = new HSSFWorkbook(); // or new XSSFWorkbook();

Sheet sheet1 = wb.createSheet("new sheet");

Sheet sheet2 = wb.createSheet("second sheet");

// Note that sheet name is Excel must not exceed 31 characters

// and must not contain any of the any of the following characters:

// 0x0000

// 0x0003

// colon (:)

// backslash (\)

// asterisk (*)

// question mark (?)

// forward slash (/)

// opening square bracket ([)

// closing square bracket (])

// You can use org.apache.poi.ss.util.WorkbookUtil#createSafeSheetName(String nameProposal)}

// for a safe way to create valid names, this utility replaces invalid characters with a space (' ')

String safeName = WorkbookUtil.createSafeSheetName("[O'Brien's sales*?]"); // returns " O'Brien's sales "

Sheet sheet3 = wb.createSheet(safeName);

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//Working with different types of cells

Workbook wb = new HSSFWorkbook();

Sheet sheet = wb.createSheet("new sheet");

Row row = sheet.createRow((short)2);

row.createCell(0).setCellValue(1.1);

row.createCell(1).setCellValue(new Date());

row.createCell(2).setCellValue(Calendar.getInstance());

row.createCell(3).setCellValue("a string");

row.createCell(4).setCellValue(true);

row.createCell(5).setCellType(Cell.CELL_TYPE_ERROR);

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();

//Iterate over rows and cells

Sheet sheet = wb.getSheetAt(0);

for (Row row : sheet) {

for (Cell cell : row) {

// Do something here

}

}

//Getting the cell contents

To get the contents of a cell, you first need to know what kind of cell it is

(asking a string cell for its numeric contents will get you a

NumberFormatException for example). So, you will want to switch on the cells

type, and then call the appropriate getter for that cell.

In the code below, we loop over every cell in one sheet, print out the cells

reference (eg A3), and then the cells contents.

// import org.apache.poi.ss.usermodel.*;

Sheet sheet1 = wb.getSheetAt(0);

for (Row row : sheet1) {

for (Cell cell : row) {

CellReference cellRef = new CellReference(row.getRowNum(), cell.getColumnIndex());

System.out.print(cellRef.formatAsString());

System.out.print(" - ");

switch (cell.getCellType()) {

case Cell.CELL_TYPE_STRING:

System.out.println(cell.getRichStringCellValue().getString());

break;

case Cell.CELL_TYPE_NUMERIC:

if (DateUtil.isCellDateFormatted(cell)) {

System.out.println(cell.getDateCellValue());

} else {

System.out.println(cell.getNumericCellValue());

}

break;

case Cell.CELL_TYPE_BOOLEAN:

System.out.println(cell.getBooleanCellValue());

break;

case Cell.CELL_TYPE_FORMULA:

System.out.println(cell.getCellFormula());

break;

default:

System.out.println();

}

}

}

//Reading and Rewriting Workbooks

InputStream inp = new FileInputStream("workbook.xls");

//InputStream inp = new FileInputStream("workbook.xlsx");

Workbook wb = WorkbookFactory.create(inp);

Sheet sheet = wb.getSheetAt(0);

Row row = sheet.getRow(2);

Cell cell = row.getCell(3);

if (cell == null)

cell = row.createCell(3);

cell.setCellType(Cell.CELL_TYPE_STRING);

cell.setCellValue("a test");

// Write the output to a file

FileOutputStream fileOut = new FileOutputStream("workbook.xls");

wb.write(fileOut);

fileOut.close();