Exporting Excel files from command line

Exporting Excel file with POI

Here is a example for exporting the content of a excel file to txt file. The values that are exported are calculated/evaluated, so when a concatenation value like '=A1&B1' is used for a cell it will export the evaluated text.
In case the are references to excel files with data in it like [testdata.xls]testdata'!$B$3 it will also be used for evaluation. For this Apache POI - the Java API for Microsoft Documents is used to open the files and read its values.

The test data

Suppose we creating a test script for logging in to an application. The data will be like username and pass word, the Excel sheet look like this:

A         B
name      value
username  maikel
password  secret

Safe the file as 'data.xls' First we set up simple testcase which will start the application and login. The content of the Excelsheet:

A        B       C          D
action   field   data       toExport
click    login              click(login; )
type     user    maikel     type(user;maikel)
type     pass    secret     type(pass;secret)
click    login              click(login; )
validate message logged in  validat(message;logged in)

Set the worksheet name to 'Testcase' so that we can refer to it later. Safe the file as 'ftg_1.xls'
Note that:
-The value of C3 is a reference to the data.xls!B2
-The value of C4 is a reference to the data.xls!B3
-The value of D2 is a concatenation of A2, B2 and C2 What can do is to extract the values from column D and put them in a text file. After setting the example.properties file we start the export by typing in the command line:

java Main

The command line output will be looking like this:

Start search in directory:./test/ExcelSheet with prefix filter:ftg_
>>Processing file:ftg_1.xls
>>Skipping file:testdata.xls
>>StartExport file=ftg_1.xls
>>Outputtext:
click(login; )
type(user;maikel)
type(pass;secret)
>>Finished searching

The output file './TestOutput/ftg_1.txt' is created and contains only the values of third column D.

PART 2 Java implementation

As mentioned poi is used for reading the excel file. The main function is opening the file and reading column D, store it in a string buffer. And write is to the out put file:

private static void exportFile(String filename) {
  System.out.println( ">>StartExport file=" + filename);
  HSSFWorkbook wb = openSample(filename);
  System.out.println(">>numer of sheets=" + wb.getNumberOfSheets());
  
  String searchSheet=excelProps.getProperty("sheetname", "Testcase");
  HSSFSheet s = wb.getSheet(searchSheet);
  if (s==null){
    System.out.println(">>Not found searchSheet name:" + searchSheet);
    return;
  }
  int lastrownum=s.getLastRowNum();
  System.out.println( ">>Last rownumber=" + lastrownum);
  HSSFRow row = null;
  Cell cell = null;
  StringBuffer sb=new StringBuffer();
  for (int i=0;i<lastrownum;i++){
    row = s.getRow(i);
    if (row!=null){
      cell = row.getCell(Integer.parseInt(excelProps.getProperty("colomnnumber", "3")));
      if (cell!=null){
        try{
          sb.append(cell.getStringCellValue()+"\n");
        } catch (RuntimeException e){
          sb.append( "#Poi Extract Excel error: "+ e.getMessage()+" in row=" + (i +1) +"\n");
        }
      }
    }
  }
  //
  //Echo to standard output
  //
  System.out.println(">>Outputtext:"+sb.toString());
}

donwload Main.java