Section 39.4. Reading Tests from a Spreadsheet

39.4. Reading Tests from a Spreadsheet

A similar approach can be taken when the tests are organized in a spreadsheet, such as the one shown in Figure 39.2. We want to produce the same report as the first example, as shown in Figure 39.1.

Figure 39.2. Spreadsheet Data

Poi is an open source package for reading from and writing to Excel spreadsheets. In the class XlsSumRunner, shown in Listing 39.2, we use Poi to read from the rows in the spreadsheet.

The main() method here creates an XlsSumRunner and then calls the method runAndReportCounts(), inherited by XlsSumRunner from CustomRunner. This means that the runner itself doesn't exit(), allowing for this runner to be used as a part of a test suite.

The constructor of XlsSumRunner shown in Listing 39.2 uses a Poi HSSFWorkbook to access the input Excel file. The constructor then calls the makeTable() method with a HSSFSheet, representing the first sheet of the spreadsheet, and then runs Fit on the resulting table, as in the previous section.

Listing 39.2. XlsSumRunner.java

 public class XlsSumRunner extends CustomRunner {    public static void main(String args[]) throws IOException {       if (args.length != 2) {          System.err.println(          "Usage: java XlsSumRunner inputFile.xls reportFile.html");          System.exit(-1);       }       XlsSumRunner runner = new XlsSumRunner(new File(args[0]),args[1]);       System.exit(runner.runAndReportCounts());    }    public XlsSumRunner(File inFile, String reportFileName)                           throws IOException {       super("Sum",inFile,reportFileName);       HSSFWorkbook workbook = new HSSFWorkbook(new FileInputStream(inFile));       makeTable(workbook.getSheetAt(0));    }    private void makeTable(HSSFSheet sheet) throws IOException {       addTable("Sum");       addRow("a | b | sum()");       for (Iterator it = sheet.rowIterator(); it.hasNext();) {           String[] cells = getCellValues((HSSFRow)it.next());          if (cells.length == 0 || cells[0].equals(""))             break;          addRow(cells);       }       addTableWithLeaderText("fit.Summary","<BR>");    }    private String[] getCellValues(HSSFRow row) {       // ..    } } 

The makeTable() in turn processes each row in the HSSFSheet, calling the method getCellValues() to convert the value from each cell in the row into a suitably formatted string. This string is used to construct the next row in the Fit table.

Questions & Answers

Q1:	Is it possible to select particular spreadsheet cells based on their color or borders or whatever?
A1:	Yes, Poi provides lots of information about the formatting of the cells in a spreadsheet. For example, the SpreadSheetRunner discussed in Chapter 26 makes use of this feature.