If AOO supports the OOXML Export, there will be a lot of issues related to the content validation work in UT.

The common method is:

  1. load the original MS office file in AOO.
  2. save it to OOXML format.
  3. open it in AOO and MS office, check the content.

The 1-2 can be automatically run by a Junit script, but the step 3 will be executed by manual, this is in a low performance.

 In apache community, there is a project named 'poi', the Apache POI Project's mission is to create and maintain Java APIs for manipulating various file formats based upon the Office Open XML standards (OOXML) and Microsoft's OLE 2 Compound Document format (OLE2). 

you can see it in http://poi.apache.org/.

build POI in your local:

     1. install Ant and Jdk. set the java_home, ANT_HOME in ENV.

     2. build Poi jar by 'ant jar'

     3. run test of ooxml by 'ant test-ooxml'

     After get POI build, we can use it to check the output of AOO.

     1. copy all the test sample files from POI to AOO.

     2. load the file in AOO.

     3. save it to OOXML format by AOO.

     4. copy the files that created in step 3 to POI.

     5. run POI's automation test program to check the result.

All of all,you should do a local build of POI.

the test case will be like this:


public void testSpreadsheetCont() throws Exception {
   Date date = new Date();
   boolean success = false;
   FileInputStream fileInputStream = new FileInputStream("File Location.xls");
   HSSFWorkbook workbook = new HSSFWorkbook(fileInputStream);
   HSSFSheet worksheet = workbook.getSheetAt(0);
   HSSFRow row1 = worksheet.getRow(2);
   HSSFCell cell0 = row1.getCell(0);
   if (cell0.getDateCellValue() == date)

      success = true;
      success = false;

if you do not want to do a local build of POI, you must create the ant test case by yourself.

Please refer to http://poi.apache.org/spreadsheet/excelant.html

Here is a sample.


<project name="excelant-demo" xmlns:poi="antlib:org.apache.poi.ss.excelant">


<property name="lib.dir" value="lib" />


<path id="excelant.path">
<pathelement location="${lib.dir}/dom4j.jar" />
<pathelement location="${lib.dir}/poi-3.9-20140107.jar" />
<pathelement location="${lib.dir}/poi-excelant-3.9-20140107.jar" />
<pathelement location="${lib.dir}/poi-ooxml-3.9-20140107.jar" />
<pathelement location="${lib.dir}/poi-ooxml-schemas-3.9-20140107.jar" />
<pathelement location="${lib.dir}/xmlbeans.jar" />


<typedef resource="org/apache/poi/ss/excelant/antlib.xml" classpathref="excelant.path" />


<property name="xls.file" value="mortgage-calculation.xls" />


<target name="simpleTest">
<excelant fileName="${xls.file}">
<test name="checkValue" showFailureDetail="true">
<evaluate showDelta="true" cell="'MortgageCalculator'!$B$3" expectedValue="13" precision="1.0e-4" />



in the project , a test case can be added like

<test name="checkValue" showFailureDetail="true">
<evaluate showDelta="true" cell="'MortgageCalculator'!$B$3" expectedValue="13" precision="1.0e-4" />

it is a check point for a float value in cell '$B$3'.

run test case by 'ant 'your target name'', in this sample, you should be type "ant simpleTest".

here is the result.

  • No labels