initial import of ExcelAnt tasks, see Bugzilla 50610
git-svn-id: https://svn.apache.org/repos/asf/poi/branches/excelant@1072119 13f79535-47bb-0310-9956-ffa450edef68
diff --git a/build.xml b/build.xml
index 1f7e757..50e17a6 100644
--- a/build.xml
+++ b/build.xml
@@ -109,6 +109,15 @@
<property name="ooxml.testokfile" location="build/ooxml-testokfile.txt"/>
<property name="ooxml.lite.output.dir" location="build/ooxml-lite-classes"/>
+ <!-- Excelant: -->
+ <property name="excelant.resource.dir" value="src/excelant/resources"/>
+ <property name="excelant.src" location="src/excelant/java"/>
+ <property name="excelant.src.test" location="src/excelant/testcases"/>
+ <property name="excelant.reports.test" location="build/excelant-test-results"/>
+ <property name="excelant.output.dir" location="build/excelant-classes"/>
+ <property name="excelant.output.test.dir" location="build/excelant-test-classes"/>
+ <property name="excelant.testokfile" location="build/excelant-testokfile.txt"/>
+
<!-- jars in the /lib directory, see the fetch-jars target-->
<property name="main.commons-logging.jar" location="${main.lib}/commons-logging-1.1.jar"/>
<property name="main.commons-logging.url"
@@ -117,6 +126,8 @@
<property name="main.log4j.url" value="${repository.m2}/maven2/log4j/log4j/1.2.13/log4j-1.2.13.jar"/>
<property name="main.junit.jar" location="${main.lib}/junit-3.8.1.jar"/>
<property name="main.junit.url" value="${repository.m2}/maven2/junit/junit/3.8.1/junit-3.8.1.jar"/>
+ <property name="main.ant.jar" location="${main.lib}/ant-1.8.2.jar"/>
+ <property name="main.ant.url" value="${repository.m2}/maven2/org/apache/ant/ant/1.8.2/ant-1.8.2.jar"/>
<!-- jars in the lib-ooxml directory, see the fetch-ooxml-jars target-->
<property name="ooxml.dom4j.jar" location="${ooxml.lib}/dom4j-1.6.1.jar"/>
@@ -155,9 +166,9 @@
</propertyset>
<path id="main.classpath">
- <fileset dir="${main.lib}">
- <include name="*.jar"/>
- </fileset>
+ <pathelement location="${main.commons-logging.jar}"/>
+ <pathelement location="${main.log4j.jar}"/>
+ <pathelement location="${main.junit.jar}"/>
</path>
<path id="scratchpad.classpath">
@@ -216,6 +227,18 @@
<pathelement location="${scratchpad.output.dir}"/>
</path>
+ <path id="excelant.classpath">
+ <path refid="ooxml.classpath"/>
+ <pathelement location="${main.ant.jar}"/>
+ <pathelement location="${ooxml.output.dir}"/>
+ </path>
+ <path id="test.excelant.classpath">
+ <path refid="ooxml.classpath"/>
+ <pathelement location="${ooxml.output.dir}"/>
+ <pathelement location="${excelant.output.dir}"/>
+ <pathelement location="${excelant.output.test.dir}"/>
+ <pathelement location="${main.output.test.dir}"/>
+ </path>
<!-- Prints POI's Ant usage help -->
<target name="help" description="Prints Apache POI's Ant usage help">
@@ -248,6 +271,9 @@
<mkdir dir="${ooxml.output.dir}"/>
<mkdir dir="${ooxml.output.test.dir}"/>
<mkdir dir="${ooxml.reports.test}"/>
+ <mkdir dir="${excelant.output.dir}"/>
+ <mkdir dir="${excelant.output.test.dir}"/>
+ <mkdir dir="${excelant.reports.test}"/>
<mkdir dir="${examples.output.dir}"/>
<mkdir dir="${dist.dir}"/>
<mkdir dir="${build.site}"/>
@@ -272,6 +298,7 @@
<available file="${main.commons-logging.jar}"/>
<available file="${main.log4j.jar}"/>
<available file="${main.junit.jar}"/>
+ <available file="${main.ant.jar}"/>
</and>
<isset property="disconnected"/>
</or>
@@ -293,6 +320,10 @@
<param name="sourcefile" value="${main.junit.url}"/>
<param name="destfile" value="${main.junit.jar}"/>
</antcall>
+ <antcall target="downloadfile">
+ <param name="sourcefile" value="${main.ant.url}"/>
+ <param name="destfile" value="${main.ant.jar}"/>
+ </antcall>
</target>
<target name="check-ooxml-jars">
@@ -397,7 +428,7 @@
</target>
<target name="compile" depends="init, compile-main,
- compile-scratchpad, compile-examples"
+ compile-scratchpad, compile-examples, compile-excelant"
description="Compiles the POI main classes, scratchpad and examples"/>
<target name="compile-all" depends="compile,compile-ooxml-lite"/>
@@ -509,6 +540,36 @@
</javac>
</target>
+ <target name="compile-excelant" depends="compile-main,compile-ooxml">
+ <javac target="${jdk.version.class}"
+ source="${jdk.version.source}"
+ destdir="${excelant.output.dir}"
+ srcdir="${excelant.src}"
+ debug="${compile.debug}"
+ encoding="${java.source.encoding}"
+ fork="yes"
+ includeantruntime="false">
+ <classpath refid="excelant.classpath"/>
+ </javac>
+ <javac target="${jdk.version.class}"
+ source="${jdk.version.source}"
+ destdir="${excelant.output.test.dir}"
+ srcdir="${excelant.src.test}"
+ debug="${compile.debug}"
+ encoding="${java.source.encoding}"
+ fork="yes"
+ includeantruntime="false">
+ <classpath>
+ <path refid="excelant.classpath"/>
+ <pathelement location="${excelant.output.dir}"/>
+ <pathelement path="${main.output.test.dir}"/>
+ </classpath>
+ </javac>
+ <copy todir="${excelant.output.dir}">
+ <fileset dir="${excelant.resource.dir}"/>
+ </copy>
+ </target>
+
<target name="compile-version" depends="init"
description="Compiles the version class">
<!-- Generate the .java file -->
@@ -533,7 +594,7 @@
<delete file="${version.java}"/>
</target>
- <target name="test" depends="compile,test-main,test-scratchpad,test-ooxml"
+ <target name="test" depends="compile,test-main,test-scratchpad,test-ooxml,test-excelant"
description="Tests main, scratchpad and ooxml"/>
<target name="test-all" depends="test,test-ooxml-lite"/>
@@ -655,6 +716,37 @@
<ooxml-test-runner classpath="ooxml-lite.classpath"/>
</target>
+ <target name="-test-excelant-check">
+ <uptodate property="excelant.test.notRequired" targetfile="${excelant.testokfile}">
+ <srcfiles dir="${excelant.src}"/>
+ <srcfiles dir="${excelant.src.test}"/>
+ </uptodate>
+ </target>
+
+ <target name="-test-excelant-write-testfile" unless="excelant.test.failed">
+ <echo file="${excelant.testokfile}" append="false" message="testok"/>
+ </target>
+
+ <target name="test-excelant" depends="compile-excelant,-test-excelant-check"
+ unless="excelant.test.notRequired">
+ <junit printsummary="yes" fork="yes" forkmode="once" haltonfailure="${halt.on.test.failure}"
+ failureproperty="excelant.test.failed">
+ <classpath refid="test.excelant.classpath"/>
+ <syspropertyset refid="junit.properties"/>
+ <jvmarg value="${poi.test.locale}"/>
+ <formatter type="plain"/>
+ <batchtest todir="${excelant.reports.test}">
+ <fileset dir="${excelant.src.test}">
+ <include name="**/Test*.java"/>
+ </fileset>
+ </batchtest>
+ </junit>
+ <delete file="${excelant.testokfile}"/>
+ <antcall target="-test-excelant-write-testfile"/>
+ </target>
+
+
+
<target name="-check-docs">
<uptodate property="main.docs.notRequired" targetfile="${build.site}/index.html">
<srcfiles dir="${build.site.src}"/>
@@ -801,6 +893,13 @@
</replacetokens>
</filterchain>
</copy>
+ <copy file="maven/poi-excelant.pom" tofile="${dist.dir}/poi-excelant-${version.id}.pom">
+ <filterchain>
+ <replacetokens>
+ <token key="VERSION" value="${version.id}"/>
+ </replacetokens>
+ </filterchain>
+ </copy>
<copy file="maven/mvn-deploy.sh" todir="${dist.dir}">
<filterchain>
<replacetokens>
@@ -849,6 +948,11 @@
<fileset dir="${ooxml.lite.output.dir}"/>
<metainf dir="legal/"/>
</jar>
+ <jar destfile="${dist.dir}/${jar.name}-excelant-${version.id}-${DSTAMP}.jar"
+ manifest="build/poi-manifest.mf">
+ <fileset dir="${excelant.output.dir}"/>
+ <metainf dir="legal/"/>
+ </jar>
</target>
<target name="jar-src" description="Sources for Maven">
@@ -872,6 +976,11 @@
<fileset dir="${examples.src}"/>
<metainf dir="legal/"/>
</jar>
+ <jar destfile="${dist.dir}/${jar.name}-excelant-${version.id}-sources-${DSTAMP}.jar"
+ manifest="build/poi-manifest.mf">
+ <fileset dir="${excelant.src}"/>
+ <metainf dir="legal/"/>
+ </jar>
</target>
<target name="assemble" depends="jar,jar-src">
@@ -882,6 +991,7 @@
<include name="${jar.name}-ooxml-${version.id}-${DSTAMP}.jar"/>
<include name="${jar.name}-examples-${version.id}-${DSTAMP}.jar"/>
<include name="${jar.name}-ooxml-schemas-${version.id}-${DSTAMP}.jar"/>
+ <include name="${jar.name}-excelant-${version.id}-${DSTAMP}.jar"/>
</patternset>
<!-- patterns to exclude from source assemblies -->
@@ -907,7 +1017,11 @@
<zip destfile="${dist.dir}/${jar.name}-bin-${version.id}-${DSTAMP}.zip">
<zipfileset dir="legal/" prefix="${zipdir}"/>
- <zipfileset dir="${main.lib}" prefix="${zipdir}/lib" includes="*.jar"/>
+ <zipfileset dir="${main.lib}" prefix="${zipdir}/lib">
+ <include name="commons-logging-*.jar"/>
+ <include name="junit-*.jar"/>
+ <include name="log4j-*.jar"/>
+ </zipfileset>
<zipfileset dir="${ooxml.lib}" prefix="${zipdir}/ooxml-lib">
<include name="dom4j-*.jar"/>
<include name="geronimo-stax-*.jar"/>
@@ -931,7 +1045,11 @@
longfile="gnu"
compression="gzip">
<tarfileset dir="legal/" prefix="${zipdir}"/>
- <tarfileset dir="${main.lib}" prefix="${zipdir}/lib" includes="*.jar"/>
+ <zipfileset dir="${main.lib}" prefix="${zipdir}/lib">
+ <include name="commons-logging-*.jar"/>
+ <include name="junit-*.jar"/>
+ <include name="log4j-*.jar"/>
+ </zipfileset>
<tarfileset dir="${ooxml.lib}" prefix="${zipdir}/ooxml-lib">
<include name="dom4j-*.jar"/>
<include name="geronimo-stax-*.jar"/>
@@ -1000,6 +1118,7 @@
<m2-install artifactId="poi-ooxml"/>
<m2-install artifactId="poi-examples"/>
<m2-install artifactId="poi-ooxml-schemas"/>
+ <m2-install artifactId="poi-excelant"/>
</target>
<!-- Runs Apache Rat against the source code, to spot any files -->
diff --git a/maven/poi-excelant.pom b/maven/poi-excelant.pom
new file mode 100755
index 0000000..12b2b15
--- /dev/null
+++ b/maven/poi-excelant.pom
@@ -0,0 +1,73 @@
+<?xml version="1.0"?>
+<!--
+
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+
+-->
+
+<project xmlns="http://maven.apache.org/POM/4.0.0"
+ xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance"
+ xsi:schemaLocation="http://maven.apache.org/POM/4.0.0 http://maven.apache.org/maven-v4_0_0.xsd">
+
+ <modelVersion>4.0.0</modelVersion>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi-excelant</artifactId>
+ <version>@VERSION@</version>
+ <packaging>jar</packaging>
+ <name>Apache POI</name>
+ <url>http://poi.apache.org/</url>
+ <description>Apache POI Excel Ant Tasks</description>
+
+ <mailingLists>
+ <mailingList>
+ <name>POI Users List</name>
+ <subscribe>user-subscribe@poi.apache.org</subscribe>
+ <unsubscribe>user-unsubscribe@poi.apache.org</unsubscribe>
+ <archive>http://mail-archives.apache.org/mod_mbox/poi-user/</archive>
+ </mailingList>
+ <mailingList>
+ <name>POI Developer List</name>
+ <subscribe>dev-subscribe@poi.apache.org</subscribe>
+ <unsubscribe>dev-unsubscribe@poi.apache.org</unsubscribe>
+ <archive>http://mail-archives.apache.org/mod_mbox/poi-dev/</archive>
+ </mailingList>
+ </mailingLists>
+
+ <licenses>
+ <license>
+ <name>The Apache Software License, Version 2.0</name>
+ <url>http://www.apache.org/licenses/LICENSE-2.0.txt</url>
+ </license>
+ </licenses>
+
+ <organization>
+ <name>Apache Software Foundation</name>
+ <url>http://www.apache.org/</url>
+ </organization>
+
+ <dependencies>
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi</artifactId>
+ <version>@VERSION@</version>
+ </dependency>
+ <dependency>
+ <groupId>org.apache.poi</groupId>
+ <artifactId>poi-ooxml</artifactId>
+ <version>@VERSION@</version>
+ </dependency>
+ </dependencies>
+</project>
diff --git a/src/documentation/content/xdocs/spreadsheet/book.xml b/src/documentation/content/xdocs/spreadsheet/book.xml
index 0974322..15ac2a2 100644
--- a/src/documentation/content/xdocs/spreadsheet/book.xml
+++ b/src/documentation/content/xdocs/spreadsheet/book.xml
@@ -40,6 +40,7 @@
<menu-item label="Pictorial Docs" href="diagrams.html"/>
<menu-item label="Limitations" href="limitations.html"/>
<menu-item label="User Defined Functions" href="user-defined-functions.html"/>
+ <menu-item label="ExcelAnt Tests" href="excelant.html"/>
</menu>
<menu label="Contributer's Guide">
diff --git a/src/documentation/content/xdocs/spreadsheet/excelant.xml b/src/documentation/content/xdocs/spreadsheet/excelant.xml
new file mode 100755
index 0000000..6594bc1
--- /dev/null
+++ b/src/documentation/content/xdocs/spreadsheet/excelant.xml
@@ -0,0 +1,319 @@
+<?xml version="1.0" encoding="UTF-8"?>
+<!--
+ ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+ ====================================================================
+-->
+<!DOCTYPE document PUBLIC "-//APACHE//DTD Documentation V1.1//EN" "../dtd/document-v11.dtd">
+
+<document>
+ <header>
+ <title>ExcelAnt - Ant Tasks for Validating Excel Spreadsheets</title>
+ <authors>
+ <person email="jon@loquatic.com" name="Jon Svede" id="JDS"/>
+ <person email="brian.bush@nrel.gov" name="Brian Bush" id="BWB"/>
+ </authors>
+ </header>
+ <body>
+ <section><title>ExcelAnt - Ant Tasks for Validating Excel Spreadsheets</title>
+
+ <section><title>Introduction</title>
+ <p>ExcelAnt is a set of Ant tasks that make it possible to verify or test
+ a workbook without having to write Java code. Of course, the tasks themselves
+ are written in Java, but to use this frame work you only need to know a little
+ bit about Ant.</p>
+ <p>This document covers the basic usage and set up of ExcelAnt.</p>
+ <p>This document will assume basic familiarity with Ant and Ant build files.</p>
+ </section>
+ <section><title>Setup</title>
+ <p>To start with, you'll need to have the POI 3.8 or higher jar files. If you test only .xls
+workbooks then you need to have the following jars in your path:</p>
+ <ul>
+ <li>poi-excelant-$version-YYYYDDMM.jar</li>
+ <li>poi-$version-YYYYDDMM.jar</li>
+ <li>poi-ooxml-$version-YYYYDDMM.jar</li>
+ </ul>
+ <p> If you evaluate .xlsx workbooks then you need to add these: </p>
+ <ul>
+ <li>poi-ooxml-schemas-$version-YYYYDDMM.jar</li>
+ <li>xmlbeans.jar</li>
+ <li>dom4j.jar</li>
+ </ul>
+ <p>For example, if you have these jars in a lib/ dir in your project, your build.xml
+ might look like this:</p>
+<source><![CDATA[
+<property name="lib.dir" value="lib" />
+
+<path id="excelant.path">
+ <pathelement location="${lib.dir}/poi-excelant-3.8-beta1-20101230.jar" />
+ <pathelement location="${lib.dir}/poi-3.8-beta1-20101230.jar" />
+ <pathelement location="${lib.dir}/poi-ooxml-3.8-beta1-20101230.jar" />
+</path>
+]]></source>
+ <p>Next, you'll need to define the Ant tasks. There are several ways to use ExcelAnt:</p>
+
+<ul><li>The traditional way:</li></ul>
+<source><![CDATA[
+ <typedef resource="org/apache/poi/ss/excelant/antlib.xml" classpathref="excelant.path" />
+]]></source>
+<p>
+ Where excelant.path referes to the classpath with POI jars.
+ Using this approach the provided extensions will live in the default namespace. Note that the default task/typenames (evaluate, test) may be too generic and should either be explicitly overridden or used with a namespace.
+</p>
+<ul><li>Similar, but assigning a namespace URI:</li></ul>
+<source><![CDATA[
+<project name="excelant-demo" xmlns:poi="antlib:org.apache.poi.ss.excelant">
+
+ <typedef resource="org/apache/poi/ss/excelant/antlib.xml"
+ classpathref="excelant.classpath"
+ uri="antlib:org.apache.poi.ss.excelant"/>
+
+ <target name="test-nofile">
+ <poi:excelant>
+
+ </poi:excelant>
+ </target>
+</project>
+]]></source>
+ </section>
+
+ <section><title>A Simple Example</title>
+ <p>The simplest example of using Excel is the ability to validate that POI is giving you back
+ the value you expect it to. Does this mean that POI is inaccurate? Hardly. There are cases
+ where POI is unable to evaluate cells for a variety of reasons. If you need to write code
+ to integrate a worksheet into an app, you may want to know that it's going to work before
+ you actually try to write that code. ExcelAnt helps with that.</p>
+
+ <p>Consider the mortgage-calculation.xls file found in the Examples
+ (/examples/src/org/apache/poi/ss/examples/excelant/simple-mortgage-calculation.xls). This sheet
+ is shown below:</p>
+
+ <!--img src="../resources/images/simple-xls-with-function.jpg" alt="mortgage calculation spreadsheet"/-->
+
+ <p>This sheet calculates the principal and interest payment for a mortgage based
+ on the amount of the loan, term and rate. To write a simple ExcelAnt test you
+ need to tell ExcelAnt about the file like this:</p>
+<source><![CDATA[
+<property name="xls.file" value="" />
+
+<target name="simpleTest">
+ <excelant fileName="${xls.file}">
+ <test name="checkValue" showFailureDetail="true">
+ <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="790.7936" precision="1.0e-4" />
+ </test>
+ </excelant>
+</target>
+]]></source>
+
+
+ <p>This code sets up ExcelAnt to access the file defined in the ant property
+ xls.file. Then it creates a 'test' named 'checkValue'. Finally it tries
+ to evaluate the B4 on the sheet named 'MortgageCalculator'. There are some assumptions
+ here that are worth explaining. For starters, ExcelAnt is focused on the testing
+ numerically oriented sheets. The <evaluate> task is actually evaluating the
+ cell as a formula using a FormulaEvaluator instance from POI. Therefore it will fail
+ if you point it to a cell that doesn't contain a formula or a test a plain old number.</p>
+
+ <p>Having said all that, here is what the output looks like:</p>
+
+<source><![CDATA[
+simpleTest:
+ [excelant] ExcelAnt version 0.4.0 Copyright 2011
+ [excelant] Using input file: resources/excelant.xls
+ [excelant] 1/1 tests passed.
+BUILD SUCCESSFUL
+Total time: 391 milliseconds
+]]></source>
+
+ </section>
+
+ <section><title>Setting Values into a Cell</title>
+ <p>So now we know that at a minimum POI can use our sheet to calculate the existing value.
+ This is an important point: in many cases sheets have dependencies, i.e., cells they reference.
+ As is often the case, these cells may have dependencies, which may have dependencies, etc.
+ The point is that sometimes a dependent cell may get adjusted by a macro or a function
+ and it may be that POI doesn't have the capabilities to do the same thing. This test
+ verifies that we can rely on POI to retrieve the default value, based on the stored values
+ of the sheet. Now we want to know if we can manipulate those dependencies and verify
+ the output.</p>
+
+ <p>To verify that we can manipulate cell values, we need a way in ExcelAnt to set a value.
+ This is provided by the following task types:</p>
+ <ul>
+ <li>setDouble() - sets the specified cell as a double.</li>
+ <li>setFormula() - sets the specified cell as a formula.</li>
+ <li>setString() = sets the specified cell as a String.</li>
+ </ul>
+
+ <p>For the purposes of this example we'll use the <setDouble> task. Let's
+ start with a $240,000, 30 year loan at 11% (let's pretend it's like 1984). Here
+ is how we will set that up:</p>
+
+<source><![CDATA[
+<setDouble cell="'MortgageCalculator'!$B$1" value="240000"/>
+<setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/>
+<setDouble cell="'MortgageCalculator'!$B$3" value ="30"/>
+<evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="2285.576149" precision="1.0e-4" />
+]]></source>
+
+ <p>Don't forget that we're verifying the behavior so you need to put all this
+ into the sheet. That is how I got the result of $2,285 and change. So save your
+ changes and run it; you should get the following: </p>
+
+<source><![CDATA[
+Buildfile: C:\opt\eclipse\workspaces\excelant\excelant.examples\build.xml
+simpleTest:
+ [excelant] ExcelAnt version 0.4.0 Copyright 2011
+ [excelant] Using input file: resources/excelant.xls
+ [excelant] 1/1 tests passed.
+BUILD SUCCESSFUL
+Total time: 406 milliseconds
+]]></source>
+
+</section>
+
+ <section><title>Getting More Details</title>
+
+ <p>This is great, it's working! However, suppose you want to see a little more detail. The
+ ExcelAnt tasks leverage the Ant logging so you can add the -verbose and -debug flags to
+ the Ant command line to get more detail. Try adding -verbose. Here is what
+ you should see:</p>
+
+<source><![CDATA[
+simpleTest:
+ [excelant] ExcelAnt version 0.4.0 Copyright 2011
+ [excelant] Using input file: resources/excelant.xls
+ [evaluate] test precision = 1.0E-4 global precision = 0.0
+ [evaluate] Using evaluate precision of 1.0E-4
+ [excelant] 1/1 tests passed.
+BUILD SUCCESSFUL
+Total time: 406 milliseconds
+]]></source>
+
+
+ <p>We see a little more detail. Notice that we see that there is a setting for global precision.
+ Up until now we've been setting the precision on each evaluate that we call. This
+ is obviously useful but it gets cumbersome. It would be better if there were a way
+ that we could specify a global precision - and there is. There is a <precision>
+ tag that you can specify as a child of the <excelant> tag. Let's go back to
+ our original task we set up earlier and modify it:</p>
+
+<source><![CDATA[
+<property name="xls.file" value="" />
+
+<target name="simpleTest">
+ <excelant fileName="${xls.file}">
+ <precision value="1.0e-3"/>
+ <test name="checkValue" showFailureDetail="true">
+ <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="790.7936" />
+ </test>
+ </excelant>
+</target>
+]]></source>
+
+ <p>In this example we have set the global precision to 1.0e-3. This means that
+ in the absence of something more stringent, all tests in the task will use
+ the global precision. We can still override this by specifying the
+ precision attribute of all of our <evaluate> task. Let's first run
+ this task with the global precision and the -verbose flag:</p>
+
+<source><![CDATA[
+simpleTest:
+[excelant] ExcelAnt version 0.4.0 Copyright 2011
+[excelant] Using input file: resources/excelant.xls
+[excelant] setting precision for the test checkValue
+ [test] setting globalPrecision to 0.0010 in the evaluator
+[evaluate] test precision = 0.0 global precision = 0.0010
+[evaluate] Using global precision of 0.0010
+[excelant] 1/1 tests passed.
+]]></source>
+
+
+ <p>As the output clearly shows, the test itself has no precision but there is
+ the global precision. Additionally, it tells us we're going to use that
+ more stringent global value. Now suppose that for this test we want
+ to use a more stringent precision, say 1.0e-4. We can do that by adding
+ the precision attribute back to the <evaluate> task:</p>
+
+<source><![CDATA[
+<excelant fileName="${xls.file}">
+ <precision value="1.0e-3"/>
+ <test name="checkValue" showFailureDetail="true">
+ <setDouble cell="'MortgageCalculator'!$B$1" value="240000"/>
+ <setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/>
+ <setDouble cell="'MortgageCalculator'!$B$3" value ="30"/>
+ <evaluate showDelta="true" cell="'MortgageCalculator'!$B$4" expectedValue="2285.576149" precision="1.0e-4" />
+ </test>
+</excelant>
+]]></source>
+
+
+ <p>Now when you re-run this test with the verbose flag you will see that
+ your test ran and passed with the higher precision:</p>
+<source><![CDATA[
+simpleTest:
+ [excelant] ExcelAnt version 0.4.0 Copyright 2011
+ [excelant] Using input file: resources/excelant.xls
+ [excelant] setting precision for the test checkValue
+ [test] setting globalPrecision to 0.0010 in the evaluator
+ [evaluate] test precision = 1.0E-4 global precision = 0.0010
+ [evaluate] Using evaluate precision of 1.0E-4 over the global precision of 0.0010
+ [excelant] 1/1 tests passed.
+BUILD SUCCESSFUL
+Total time: 390 milliseconds
+]]></source>
+ </section>
+
+ <section><title>Leveraging User Defined Functions</title>
+ <p>POI has an excellent feature (besides ExcelAnt) called <link href="user-defined-functions.html">User Defined Functions</link>,
+ that allows you to write Java code that will be used in place of custom VB
+ code or macros is a spreadsheet. If you have read the documentation and written
+ your own FreeRefFunction implmentations, ExcelAnt can make use of this code.
+ For each <excelant> task you define you can nest a <udf> tag
+ which allows you to specify the function alias and the class name.</p>
+
+ <p>Consider the previous example of the mortgage calculator. What if, instead
+ of being a formula in a cell, it was a function defined in a VB macro? As luck
+ would have it, we already have an example of this in the examples from the
+ User Defined Functions example, so let's use that. In the example spreadsheet
+ there is a tab for MortgageCalculatorFunction, which will use. If you look in
+ cell B4, you see that rather than a messy cell based formula, there is only the function
+ call. Let's not get bogged down in the function/Java implementation, as these
+ are covered in the User Defined Function documentation. Let's just add
+ a new target and test to our existing build file:</p>
+<source><![CDATA[
+ <target name="functionTest">
+ <excelant fileName="${xls.file}">
+ <udf functionAlias="calculatePayment" class="org.apache.poi.ss.examples.formula.CalculateMortgage"/>
+ <precision value="1.0e-3"/>
+ <test name="checkValue" showFailureDetail="true">
+ <setDouble cell="'MortgageCalculator'!$B$1" value="240000"/>
+ <setDouble cell="'MortgageCalculator'!$B$2" value ="0.11"/>
+ <setDouble cell="'MortgageCalculator'!$B$3" value ="30"/>
+ <evaluate showDelta="true" cell="'MortgageCalculatorFunction'!$B$4" expectedValue="2285.576149" precision="1.0e-4" />
+ </test>
+ </excelant>
+ </target>
+]]></source>
+
+ <p>So if you look at this carefully it looks the same as the previous examples. We
+ still use the global precision, we're still setting values, and we still want
+ to evaluate a cell. The only real differences are the sheet name and the
+ addition of the function.</p>
+ </section>
+ </section>
+</body>
+</document>
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java
new file mode 100755
index 0000000..d35cc6a
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntEvaluateCell.java
@@ -0,0 +1,146 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.poi.ss.excelant.util.ExcelAntEvaluationResult;
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.Task;
+
+/**
+ * Instances of this class are used to evaluate a single cell. This is usually
+ * after some values have been set. The evaluation is actually performed
+ * by a WorkbookUtil instance. The evaluate() method of the WorkbookUtil
+ * class returns an EvaluationResult which encapsulates the results and
+ * information from the evaluation.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+
+ *
+ */
+public class ExcelAntEvaluateCell extends Task {
+
+ private String cell ;
+ private double expectedValue ;
+ private double precision ;
+ private double precisionToUse ;
+ private double globalPrecision ;
+ private boolean requiredToPass = false ;
+
+
+ private ExcelAntEvaluationResult result ;
+
+ private ExcelAntWorkbookUtil wbUtil ;
+
+ private boolean showDelta = false ;
+
+
+ public ExcelAntEvaluateCell() {}
+
+ protected void setWorkbookUtil( ExcelAntWorkbookUtil wb ) {
+ wbUtil = wb ;
+ }
+
+ public void setShowDelta( boolean value ) {
+ showDelta = value ;
+ }
+
+ protected boolean showDelta() {
+ return showDelta ;
+ }
+
+ public void setCell(String cell) {
+ this.cell = cell;
+ }
+
+ public void setRequiredToPass( boolean val ) {
+ requiredToPass = val ;
+ }
+
+ protected boolean requiredToPass() {
+ return requiredToPass ;
+ }
+
+ public void setExpectedValue(double expectedValue) {
+ this.expectedValue = expectedValue;
+ }
+
+ public void setPrecision(double precision) {
+ this.precision = precision;
+ }
+
+ protected void setGlobalPrecision( double prec ) {
+ globalPrecision = prec ;
+ }
+
+ protected String getCell() {
+ return cell;
+ }
+
+ protected double getExpectedValue() {
+ return expectedValue;
+ }
+
+ protected double getPrecision() {
+ return precisionToUse;
+ }
+
+ public void execute() throws BuildException {
+
+ precisionToUse = 0 ;
+
+ // if there is a globalPrecision we will use it unless there is also
+ // precision set at the evaluate level, then we use that. If there
+ // is not a globalPrecision, we will use the local precision.
+ log( "test precision = " + precision + "\tglobal precision = " + globalPrecision, Project.MSG_VERBOSE ) ;
+ if( globalPrecision > 0 ) {
+ if( precision > 0 ) {
+ precisionToUse = precision ;
+ log( "Using evaluate precision of " + precision + " over the " +
+ "global precision of " + globalPrecision, Project.MSG_VERBOSE ) ;
+ } else {
+ precisionToUse = globalPrecision ;
+ log( "Using global precision of " + globalPrecision, Project.MSG_VERBOSE ) ;
+ }
+ } else {
+ precisionToUse = precision ;
+ log( "Using evaluate precision of " + precision, Project.MSG_VERBOSE ) ;
+ }
+ result = wbUtil.evaluateCell(cell, expectedValue, precisionToUse ) ;
+
+ StringBuffer sb = new StringBuffer() ;
+ sb.append( "evaluation of cell " ) ;
+ sb.append( cell ) ;
+ sb.append( " resulted in " ) ;
+ sb.append( result.getReturnValue() ) ;
+ if( showDelta == true ) {
+ sb.append( " with a delta of " + result.getDelta() ) ;
+ }
+
+ log( sb.toString(), Project.MSG_DEBUG) ;
+
+ }
+
+ public ExcelAntEvaluationResult getResult() {
+ return result ;
+ }
+
+
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java
new file mode 100755
index 0000000..af210f6
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntHandlerTask.java
@@ -0,0 +1,60 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.Task;
+
+/**
+ * This is the class that backs the <handler> tag in the Ant task.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntHandlerTask extends Task {
+
+ private String className ;
+
+ private ExcelAntWorkbookUtil wbUtil ;
+
+ public void setClassName( String cName ) {
+ className = cName ;
+ }
+
+ protected void setEAWorkbookUtil( ExcelAntWorkbookUtil wkbkUtil ) {
+ wbUtil = wkbkUtil ;
+ }
+
+ public void execute() throws BuildException {
+ log( "handling the workbook with class " + className, Project.MSG_INFO ) ;
+ try {
+ Class clazz = Class.forName( className ) ;
+ Object handlerObj = clazz.newInstance() ;
+ if( handlerObj instanceof IExcelAntWorkbookHandler ) {
+ IExcelAntWorkbookHandler iHandler = (IExcelAntWorkbookHandler)handlerObj ;
+ iHandler.setWorkbook( wbUtil.getWorkbook() ) ;
+ iHandler.execute() ;
+ }
+ } catch( Exception e ) {
+ throw new BuildException( e.getMessage(), e ) ;
+ }
+ }
+ }
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java
new file mode 100755
index 0000000..0739ef5
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntPrecision.java
@@ -0,0 +1,39 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.tools.ant.taskdefs.Typedef;
+
+/**
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntPrecision extends Typedef {
+
+ private double value ;
+
+ public void setValue( double precision ) {
+ value = precision ;
+ }
+
+ public double getValue() {
+ return value ;
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSet.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSet.java
new file mode 100755
index 0000000..974b1b9
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSet.java
@@ -0,0 +1,48 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.tools.ant.Task;
+
+/**
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public abstract class ExcelAntSet extends Task {
+
+ protected String cellStr ;
+
+ protected ExcelAntWorkbookUtil wbUtil ;
+
+ public void setCell( String cellName ) {
+ cellStr = cellName ;
+ }
+
+ public String getCell() {
+ return cellStr ;
+ }
+
+
+ public void setWorkbookUtil( ExcelAntWorkbookUtil wb ) {
+ wbUtil = wb ;
+ }
+
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java
new file mode 100755
index 0000000..a111350
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetDoubleCell.java
@@ -0,0 +1,60 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+
+/**
+ * Class for use in an Ant build script that sets the value of an Excel
+ * sheet cell using the cell id ('Sheet Name'!cellId).
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntSetDoubleCell extends ExcelAntSet {
+
+
+ private double cellValue ;
+
+ public ExcelAntSetDoubleCell() {}
+
+ /**
+ * Set the value of the specified cell as the double passed in.
+ * @param value
+ */
+ public void setValue( double value ) {
+ cellValue = value ;
+ }
+
+ /**
+ * Return the cell value as a double.
+ * @return
+ */
+ public double getCellValue() {
+ return cellValue;
+ }
+
+ public void execute() throws BuildException {
+
+ wbUtil.setDoubleValue(cellStr, cellValue ) ;
+
+ log( "set cell " + cellStr + " to value " + cellValue + " as double.", Project.MSG_DEBUG ) ;
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java
new file mode 100755
index 0000000..1b9d5f0
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetFormulaCell.java
@@ -0,0 +1,52 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+
+/**
+ * Class for use in an Ant build script that sets the formula of an Excel
+ * sheet cell using the cell id ('Sheet Name'!cellId).
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntSetFormulaCell extends ExcelAntSet {
+
+
+ private String cellValue ;
+
+ public ExcelAntSetFormulaCell() {}
+
+ public void setValue( String value ) {
+ cellValue = value ;
+ }
+
+ protected String getCellValue() {
+ return cellValue;
+ }
+
+ public void execute() throws BuildException {
+
+ wbUtil.setFormulaValue( cellStr, cellValue ) ;
+
+ log( "set cell " + cellStr + " to formula " + cellValue, Project.MSG_DEBUG ) ;
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java
new file mode 100755
index 0000000..12221ee
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntSetStringCell.java
@@ -0,0 +1,62 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+
+/**
+ * Class for use in an Ant build script that sets the value of an Excel
+ * sheet cell using the cell id ('Sheet Name'!cellId).
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntSetStringCell extends ExcelAntSet {
+
+
+ private String stringValue ;
+
+
+ public ExcelAntSetStringCell() {}
+
+
+ /**
+ * Set the value of the cell to the String passed in.
+ * @param value
+ */
+ public void setValue(String value ) {
+ stringValue = value ;
+ }
+
+ /**
+ * Return the value that will be set into the cell.
+ * @return
+ */
+ public String getCellValue() {
+ return stringValue;
+ }
+
+ public void execute() throws BuildException {
+
+ wbUtil.setStringValue(cellStr, stringValue ) ;
+
+ log( "set cell " + cellStr + " to value " + stringValue + " as String.", Project.MSG_DEBUG ) ;
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTask.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTask.java
new file mode 100755
index 0000000..1bbbb7d
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTask.java
@@ -0,0 +1,193 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import java.io.File;
+import java.io.FileInputStream;
+import java.io.FileNotFoundException;
+import java.io.IOException;
+import java.text.SimpleDateFormat;
+import java.util.Date;
+import java.util.Iterator;
+import java.util.LinkedList;
+
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory;
+import org.apache.poi.ss.usermodel.Workbook;
+import org.apache.poi.ss.usermodel.WorkbookFactory;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.Task;
+
+/**
+ * Ant task class for testing Excel workbook cells.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntTask extends Task {
+
+ public static final String VERSION = "0.5.0" ;
+
+ private String excelFileName ;
+
+ private boolean failOnError = false ;
+
+ private ExcelAntWorkbookUtil workbookUtil ;
+
+ private ExcelAntPrecision precision ;
+
+ private LinkedList<ExcelAntTest> tests ;
+ private LinkedList<ExcelAntUserDefinedFunction> functions ;
+
+ public ExcelAntTask() {
+ tests = new LinkedList<ExcelAntTest>() ;
+ functions = new LinkedList<ExcelAntUserDefinedFunction>() ;
+ }
+
+ public void addPrecision( ExcelAntPrecision prec ) {
+ precision = prec ;
+ }
+
+ public void setFailOnError( boolean value ) {
+ failOnError = value ;
+ }
+ public void setFileName( String fileName ) {
+ excelFileName = fileName ;
+ }
+
+ public void addTest( ExcelAntTest testElement ) {
+ tests.add( testElement ) ;
+ }
+
+ public void addUdf( ExcelAntUserDefinedFunction def ) {
+ functions.add( def ) ;
+ }
+
+ public void execute() throws BuildException {
+ checkClassPath();
+
+ int totalCount = 0 ;
+ int successCount = 0 ;
+
+ StringBuffer versionBffr = new StringBuffer() ;
+ versionBffr.append( "ExcelAnt version " ) ;
+ versionBffr.append( VERSION ) ;
+ versionBffr.append( " Copyright 2011" ) ;
+ SimpleDateFormat sdf = new SimpleDateFormat( "yyyy" ) ;
+ double currYear = Double.parseDouble( sdf.format( new Date() ) );
+ if( currYear > 2011 ) {
+ versionBffr.append( "-" ) ;
+ versionBffr.append( currYear ) ;
+ }
+ log( versionBffr.toString(), Project.MSG_INFO ) ;
+
+ log( "Using input file: " + excelFileName, Project.MSG_INFO ) ;
+
+ Workbook targetWorkbook = loadWorkbook() ;
+ if( targetWorkbook == null ) {
+ log( "Unable to load " + excelFileName +
+ ". Verify the file exists and can be read.",
+ Project.MSG_ERR ) ;
+ return ;
+ }
+ if( tests != null && tests.size() > 0 ) {
+
+ Iterator<ExcelAntTest> testsIt = tests.iterator() ;
+ while( testsIt.hasNext() ) {
+ ExcelAntTest test = testsIt.next();
+
+ log( "executing test: " + test.getName(), Project.MSG_DEBUG ) ;
+
+ workbookUtil = ExcelAntWorkbookUtilFactory.getInstance( excelFileName ) ;
+
+ if( functions != null ) {
+ Iterator<ExcelAntUserDefinedFunction> functionsIt = functions.iterator() ;
+ while( functionsIt.hasNext() ) {
+ ExcelAntUserDefinedFunction eaUdf = functionsIt.next() ;
+ try {
+ workbookUtil.addFunction(eaUdf.getFunctionAlias(), eaUdf.getClassName() ) ;
+ } catch ( Exception e) {
+ throw new BuildException( e.getMessage(), e );
+ }
+ }
+ }
+ test.setWorkbookUtil( workbookUtil ) ;
+
+ if( precision != null && precision.getValue() > 0 ) {
+ log( "setting precision for the test " + test.getName(), Project.MSG_VERBOSE ) ;
+ test.setPrecision( precision.getValue() ) ;
+ }
+
+ test.execute() ;
+
+ if( test.didTestPass() ) {
+ successCount++ ;
+ } else {
+ if( failOnError == true ) {
+ throw new BuildException( "Test " + test.getName() + " failed." ) ;
+ }
+ }
+ totalCount++ ;
+
+ workbookUtil = null ;
+ }
+ log( successCount + "/" + totalCount + " tests passed.", Project.MSG_INFO ) ;
+ workbookUtil = null ;
+ }
+ }
+
+
+ private Workbook loadWorkbook() {
+ if (excelFileName == null) {
+ throw new BuildException("fileName attribute must be set!",
+ getLocation());
+ }
+
+ Workbook workbook;
+ File workbookFile = new File( excelFileName ) ;
+ try {
+ FileInputStream fis = new FileInputStream( workbookFile ) ;
+ workbook = WorkbookFactory.create( fis ) ;
+ } catch (Exception e) {
+ throw new BuildException("Cannot load file " + excelFileName
+ + ". Make sure the path and file permissions are correct.", e, getLocation());
+ }
+ return workbook ;
+ }
+
+
+ /**
+ * ExcelAnt depends on external libraries not included in the Ant distribution.
+ * Give user a sensible message if any if the required jars are missing.
+ */
+ private void checkClassPath(){
+ try {
+ Class.forName("org.apache.poi.hssf.usermodel.HSSFWorkbook");
+ Class.forName("org.apache.poi.ss.usermodel.WorkbookFactory");
+ } catch (Throwable e) {
+ throw new BuildException(
+ "The <classpath> for <excelant> must include poi.jar and poi-ooxml.jar " +
+ "if not in Ant's own classpath. Processing .xlsx spreadsheets requires " +
+ "additional poi-ooxml-schemas.jar, xmlbeans.jar and dom4j.jar" ,
+ e, getLocation());
+ }
+
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTest.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTest.java
new file mode 100755
index 0000000..b97d1ae
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntTest.java
@@ -0,0 +1,224 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import java.util.Iterator;
+import java.util.LinkedList;
+
+import org.apache.poi.ss.excelant.util.ExcelAntEvaluationResult;
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.Task;
+
+/**
+ * This class represents a single test. In order for the test any and all
+ * ExcelAntEvaluateCell evaluations must pass. Therefore it is recommended
+ * that you use only 1 evaluator but you can use more if you choose.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntTest extends Task{
+
+ private LinkedList<ExcelAntSet> setters ;
+ private LinkedList<ExcelAntEvaluateCell>evaluators ;
+
+ private LinkedList<Task> testTasks ;
+
+ private String name ;
+
+ private double globalPrecision ;
+
+ private boolean showSuccessDetails = false ;
+
+ private boolean showFailureDetail = false ;
+ LinkedList<String> failureMessages ;
+
+
+ private ExcelAntWorkbookUtil workbookUtil ;
+
+ private boolean passed = true ;
+
+
+ public ExcelAntTest() {
+ setters = new LinkedList<ExcelAntSet>() ;
+ evaluators = new LinkedList<ExcelAntEvaluateCell>() ;
+ failureMessages = new LinkedList<String>() ;
+ testTasks = new LinkedList<Task>() ;
+ }
+
+ public void setPrecision( double precision ) {
+ globalPrecision = precision ;
+ }
+
+ public void setWorkbookUtil( ExcelAntWorkbookUtil wbUtil ) {
+ workbookUtil = wbUtil ;
+ }
+
+
+ public void setShowFailureDetail( boolean value ) {
+ showFailureDetail = value ;
+ }
+
+ public void setName( String nm ) {
+ name = nm ;
+ }
+
+ public String getName() {
+ return name ;
+ }
+
+ public void setShowSuccessDetails( boolean details ) {
+ showSuccessDetails = details ;
+ }
+
+ public boolean showSuccessDetails() {
+ return showSuccessDetails ;
+ }
+
+ public void addSetDouble( ExcelAntSetDoubleCell setter ) {
+ addSetter( setter ) ;
+ }
+
+ public void addSetString( ExcelAntSetStringCell setter ){
+ addSetter( setter ) ;
+ }
+
+ public void addSetFormula( ExcelAntSetFormulaCell setter ) {
+ addSetter( setter ) ;
+ }
+
+ public void addHandler( ExcelAntHandlerTask handler ) {
+ testTasks.add( handler ) ;
+ }
+
+ private void addSetter( ExcelAntSet setter ) {
+// setters.add( setter );
+ testTasks.add( setter ) ;
+ }
+
+ public void addEvaluate( ExcelAntEvaluateCell evaluator ) {
+// evaluators.add( evaluator ) ;
+ testTasks.add( evaluator ) ;
+ }
+
+// public LinkedList<ExcelAntSet> getSetters() {
+// return setters;
+// }
+
+ protected LinkedList<ExcelAntEvaluateCell> getEvaluators() {
+ return evaluators;
+ }
+
+ public void execute() throws BuildException {
+
+ Iterator<Task> taskIt = testTasks.iterator() ;
+
+ int testCount = evaluators.size() ;
+ int failureCount = 0 ;
+
+ // roll over all sub task elements in one loop. This allows the
+ // ordering of the sub elements to be considered.
+ while( taskIt.hasNext() ) {
+ Task task = taskIt.next() ;
+
+ // log( task.getClass().getName(), Project.MSG_INFO ) ;
+
+ if( task instanceof ExcelAntSet ) {
+ ExcelAntSet set = (ExcelAntSet) task ;
+ set.setWorkbookUtil(workbookUtil) ;
+ set.execute() ;
+ }
+
+ if( task instanceof ExcelAntHandlerTask ) {
+ ExcelAntHandlerTask handler = (ExcelAntHandlerTask)task ;
+ handler.setEAWorkbookUtil(workbookUtil ) ;
+ handler.execute() ;
+ }
+
+ if (task instanceof ExcelAntEvaluateCell ) {
+ ExcelAntEvaluateCell eval = (ExcelAntEvaluateCell)task ;
+ eval.setWorkbookUtil( workbookUtil ) ;
+
+ if( globalPrecision > 0 ) {
+ log( "setting globalPrecision to " + globalPrecision + " in the evaluator", Project.MSG_VERBOSE ) ;
+ eval.setGlobalPrecision( globalPrecision ) ;
+ }
+
+ try {
+ eval.execute() ;
+ ExcelAntEvaluationResult result = eval.getResult() ;
+ if( result.didTestPass() &&
+ result.evaluationCompleteWithError() == false ) {
+ if( showSuccessDetails == true ) {
+ log("Succeeded when evaluating " +
+ result.getCellName() + ". It evaluated to " +
+ result.getReturnValue() + " when the value of " +
+ eval.getExpectedValue() + " with precision of " +
+ eval.getPrecision(), Project.MSG_INFO ) ;
+ }
+ } else {
+ if( showFailureDetail == true ) {
+ failureMessages.add( "\tFailed to evaluate cell " +
+ result.getCellName() + ". It evaluated to " +
+ result.getReturnValue() + " when the value of " +
+ eval.getExpectedValue() + " with precision of " +
+ eval.getPrecision() + " was expected." ) ;
+
+ }
+ passed = false ;
+ failureCount++ ;
+
+ if( eval.requiredToPass() == true ) {
+ throw new BuildException( "\tFailed to evaluate cell " +
+ result.getCellName() + ". It evaluated to " +
+ result.getReturnValue() + " when the value of " +
+ eval.getExpectedValue() + " with precision of " +
+ eval.getPrecision() + " was expected." ) ;
+ }
+ }
+ } catch( NullPointerException npe ) {
+ // this means the cell reference in the test is bad.
+ log( "Cell assignment " + eval.getCell() + " in test " + getName() +
+ " appears to point to an empy cell. Please check the " +
+ " reference in the ant script.", Project.MSG_ERR ) ;
+ }
+ }
+ }
+
+ if( passed == false ) {
+ log( "Test named " + name + " failed because " + failureCount +
+ " of " + testCount + " evaluations failed to " +
+ "evaluate correctly.",
+ Project.MSG_ERR ) ;
+ if( showFailureDetail == true && failureMessages.size() > 0 ) {
+ Iterator<String> failures = failureMessages.iterator() ;
+ while( failures.hasNext() ) {
+ log( failures.next(), Project.MSG_ERR ) ;
+ }
+ }
+ }
+ }
+
+ public boolean didTestPass() {
+
+ return passed ;
+ }
+ }
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java
new file mode 100755
index 0000000..f69e9b6
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/ExcelAntUserDefinedFunction.java
@@ -0,0 +1,55 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.tools.ant.taskdefs.Typedef;
+
+/**
+ * This class encapsulates the Strings necessary to create the User Defined
+ * Function instances that will be passed to POI's Evaluator instance.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntUserDefinedFunction extends Typedef {
+
+
+ public String functionAlias ;
+
+ public String className ;
+
+
+ public ExcelAntUserDefinedFunction() {}
+
+ protected String getFunctionAlias() {
+ return functionAlias;
+ }
+
+ public void setFunctionAlias(String functionAlias) {
+ this.functionAlias = functionAlias;
+ }
+
+ protected String getClassName() {
+ return className;
+ }
+
+ public void setClassName(String className) {
+ this.className = className;
+ }
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java b/src/excelant/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java
new file mode 100755
index 0000000..3cd477a
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/IExcelAntWorkbookHandler.java
@@ -0,0 +1,46 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant;
+
+import org.apache.poi.ss.usermodel.Workbook;
+
+
+/**
+ * In Excel there are many ways to handle manipulating a workbook based
+ * on some arbitrary user action (onChange, etc). You use this interface
+ * to create classes that will handle the workbook in whatever manner is needed
+ * that cannot be handled by POI.
+ * <p>
+ * For example, suppose that in Excel when you update a cell the workbook
+ * does some calculations and updates other cells based on that change. In
+ * ExcelAnt you would set the value of the cell then write your own handler
+ * then call that from your Ant task after the set task.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public interface IExcelAntWorkbookHandler {
+
+
+ public void setWorkbook( Workbook workbook ) ;
+
+ public void execute() ;
+
+
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java
new file mode 100755
index 0000000..6633357
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntEvaluationResult.java
@@ -0,0 +1,114 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant.util;
+
+/**
+ * A simple class that encapsulates information about a cell evaluation
+ * from POI.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntEvaluationResult {
+
+ /**
+ * This boolean flag is used to determine if the evaluation completed
+ * without error. This alone doesn't ensure that the evaluation was
+ * sucessful.
+ */
+ private boolean evaluationCompletedWithError ;
+
+ /**
+ * This boolean flag is used to determine if the result was within
+ * the specified precision.
+ */
+ private boolean didPass ;
+
+ /**
+ * This is the actual value returned from the evaluation.
+ */
+ private double returnValue ;
+
+ /**
+ * Any error message String values that need to be returned.
+ */
+ private String errorMessage ;
+
+ /**
+ * Stores the absolute value of the delta for this evaluation.
+ */
+ private double actualDelta ;
+
+ /**
+ * This stores the fully qualified cell name (sheetName!cellId).
+ */
+ private String cellName ;
+
+
+
+ public ExcelAntEvaluationResult( boolean completedWithError,
+ boolean passed,
+ double retValue,
+ String errMessage,
+ double delta,
+ String cellId ) {
+
+ evaluationCompletedWithError = completedWithError;
+ didPass = passed;
+ returnValue = retValue;
+ errorMessage = errMessage;
+ actualDelta = delta ;
+ cellName = cellId ;
+ }
+
+ public double getReturnValue() {
+ return returnValue;
+ }
+
+ public String getErrorMessage() {
+ return errorMessage;
+ }
+
+ public boolean didTestPass() {
+ return didPass ;
+ }
+
+ public boolean evaluationCompleteWithError() {
+ return evaluationCompletedWithError ;
+ }
+
+ public double getDelta() {
+ return actualDelta ;
+ }
+
+ public String getCellName() {
+ return cellName ;
+ }
+
+ @Override
+ public String toString() {
+ return "ExcelAntEvaluationResult [evaluationCompletedWithError="
+ + evaluationCompletedWithError + ", didPass=" + didPass
+ + ", returnValue=" + returnValue + ", errorMessage="
+ + errorMessage + ", actualDelta=" + actualDelta + ", cellName="
+ + cellName + "]";
+ }
+
+
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java
new file mode 100755
index 0000000..af23d69
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtil.java
@@ -0,0 +1,384 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant.util;
+
+import org.apache.poi.hssf.usermodel.HSSFFormulaEvaluator;
+import org.apache.poi.hssf.usermodel.HSSFWorkbook;
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.udf.AggregatingUDFFinder;
+import org.apache.poi.ss.formula.udf.DefaultUDFFinder;
+import org.apache.poi.ss.formula.udf.UDFFinder;
+import org.apache.poi.ss.usermodel.*;
+import org.apache.poi.ss.util.CellReference;
+import org.apache.poi.xssf.usermodel.XSSFFormulaEvaluator;
+import org.apache.poi.xssf.usermodel.XSSFWorkbook;
+import org.apache.tools.ant.BuildException;
+import org.apache.tools.ant.Project;
+import org.apache.tools.ant.taskdefs.Typedef;
+
+import java.io.File;
+import java.io.FileInputStream;
+import java.util.ArrayList;
+import java.util.Date;
+import java.util.HashMap;
+import java.util.Iterator;
+
+/**
+ * A general utility class that abstracts the POI details of loading the
+ * workbook, accessing and updating cells.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntWorkbookUtil extends Typedef {
+
+ private String excelFileName;
+
+ private Workbook workbook;
+
+ private HashMap<String, FreeRefFunction> xlsMacroList;
+
+ /**
+ * Constructs an instance using a String that contains the fully qualified
+ * path of the Excel file. This constructor initializes a Workbook instance
+ * based on that file name.
+ *
+ * @param fName
+ */
+ protected ExcelAntWorkbookUtil(String fName) {
+ excelFileName = fName;
+ xlsMacroList = new HashMap<String, FreeRefFunction>() ;
+ loadWorkbook();
+
+ }
+
+ /**
+ * Constructs an instance based on a Workbook instance.
+ *
+ * @param wb
+ */
+ protected ExcelAntWorkbookUtil(Workbook wb) {
+ workbook = wb;
+ xlsMacroList = new HashMap<String, FreeRefFunction>() ;
+ }
+
+ /**
+ * Loads the member variable workbook based on the fileName variable.
+ * @return
+ */
+ private Workbook loadWorkbook() {
+
+ File workbookFile = new File(excelFileName);
+ try {
+ FileInputStream fis = new FileInputStream(workbookFile);
+ workbook = WorkbookFactory.create(fis);
+ } catch(Exception e) {
+ throw new BuildException("Cannot load file " + excelFileName
+ + ". Make sure the path and file permissions are correct.", e);
+ }
+
+ return workbook ;
+ }
+
+ /**
+ * Used to add a UDF to the evaluator.
+ * @param name
+ * @param clazzName
+ * @throws ClassNotFoundException
+ * @throws InstantiationException
+ * @throws IllegalAccessException
+ */
+ public void addFunction( String name, String clazzName ) throws ClassNotFoundException, InstantiationException, IllegalAccessException {
+ Class clazzInst = Class.forName( clazzName ) ;
+ Object newInst = clazzInst.newInstance() ;
+ if( newInst instanceof FreeRefFunction ) {
+ addFunction( name, (FreeRefFunction)newInst ) ;
+ }
+
+ }
+
+ /**
+ * Updates the internal HashMap of functions with instance and alias passed
+ * in.
+ *
+ * @param name
+ * @param func
+ */
+ protected void addFunction(String name, FreeRefFunction func) {
+ xlsMacroList.put(name, func);
+ }
+
+ /**
+ * returns a UDFFinder that contains all of the functions added.
+ *
+ * @return
+ */
+ protected UDFFinder getFunctions() {
+
+ String[] names = new String[xlsMacroList.size()];
+ FreeRefFunction[] functions = new FreeRefFunction[xlsMacroList.size()];
+
+ Iterator<String> keysIt = xlsMacroList.keySet().iterator();
+ int x = 0;
+ while (keysIt.hasNext()) {
+ String name = keysIt.next();
+ FreeRefFunction function = xlsMacroList.get(name);
+ names[x] = name;
+ functions[x] = function;
+ }
+
+ UDFFinder udff1 = new DefaultUDFFinder(names, functions);
+ UDFFinder udff = new AggregatingUDFFinder(udff1);
+
+ return udff;
+
+ }
+
+ /**
+ * Returns a formula evaluator that is loaded with the functions that
+ * have been supplied.
+ *
+ * @param excelFileName
+ * @return
+ */
+ protected FormulaEvaluator getEvaluator( String excelFileName ) {
+ FormulaEvaluator evaluator ;
+ if (excelFileName.endsWith(".xlsx")) {
+ if( xlsMacroList != null && xlsMacroList.size() > 0 ) {
+ evaluator = XSSFFormulaEvaluator.create( (XSSFWorkbook) workbook,
+ null,
+ getFunctions() ) ;
+ }
+ evaluator = new XSSFFormulaEvaluator((XSSFWorkbook) workbook);
+ } else {
+ if( xlsMacroList != null && xlsMacroList.size() > 0 ) {
+ evaluator = HSSFFormulaEvaluator.create( (HSSFWorkbook)workbook,
+ null,
+ getFunctions() ) ;
+ }
+
+ evaluator = new HSSFFormulaEvaluator((HSSFWorkbook) workbook);
+ }
+
+ return evaluator ;
+
+ }
+
+ /**
+ * Returns the Workbook instance associated with this WorkbookUtil.
+ *
+ * @return
+ */
+ public Workbook getWorkbook() {
+ return workbook;
+ }
+
+ /**
+ * Returns the fileName that was used to initialize this instance. May
+ * return null if the instance was constructed from a Workbook object.
+ *
+ * @return
+ */
+ public String getFileName() {
+ return excelFileName;
+ }
+
+ /**
+ * Returns the list of sheet names.
+ *
+ * @return
+ */
+ public ArrayList<String> getSheets() {
+ ArrayList<String> sheets = new ArrayList<String>() ;
+
+ int sheetCount = workbook.getNumberOfSheets() ;
+
+ for( int x=0; x<sheetCount; x++ ) {
+ sheets.add( workbook.getSheetName( x ) ) ;
+ }
+
+ return sheets ;
+ }
+
+ /**
+ * This method uses a String in standard Excel format (SheetName!CellId) to
+ * locate the cell and set it to the value of the double in value.
+ *
+ * @param cellName
+ * @param value
+ */
+ public void setDoubleValue(String cellName, double value) {
+ log("starting setCellValue()", Project.MSG_DEBUG);
+ Cell cell = getCell(cellName);
+ log("working on cell: " + cell, Project.MSG_DEBUG);
+ cell.setCellValue(value);
+ log("after cell.setCellValue()", Project.MSG_DEBUG);
+
+ log("set cell " + cellName + " to value " + value, Project.MSG_DEBUG);
+ }
+
+ /**
+ * Utility method for setting the value of a Cell with a String.
+ *
+ * @param cellName
+ * @param value
+ */
+ public void setStringValue( String cellName, String value ) {
+ Cell cell = getCell(cellName);
+ cell.setCellValue(value);
+ }
+
+ /**
+ * Utility method for setting the value of a Cell with a Formula.
+ *
+ * @param cellName
+ * @param formula
+ */
+ public void setFormulaValue( String cellName, String formula ) {
+ Cell cell = getCell(cellName);
+ cell.setCellFormula( formula );
+ }
+
+ /**
+ * Utility method for setting the value of a Cell with a Date.
+ * @param cellName
+ * @param date
+ */
+ public void setDateValue( String cellName, Date date ) {
+ Cell cell = getCell(cellName);
+ cell.setCellValue( date ) ;
+ }
+ /**
+ * Uses a String in standard Excel format (SheetName!CellId) to locate a
+ * cell and evaluate it.
+ *
+ * @param cellName
+ * @param expectedValue
+ * @param precision
+ */
+ public ExcelAntEvaluationResult evaluateCell(String cellName, double expectedValue,
+ double precision) {
+
+ ExcelAntEvaluationResult evalResults = null;
+
+ Cell cell = getCell(cellName);
+
+ FormulaEvaluator evaluator = getEvaluator( excelFileName );
+
+
+ CellValue resultOfEval = evaluator.evaluate(cell);
+
+ if (resultOfEval.getErrorValue() == 0) {
+ // the evaluation did not encounter errors
+ double result = resultOfEval.getNumberValue();
+ double delta = Math.abs(result - expectedValue);
+ if (delta > precision) {
+ evalResults = new ExcelAntEvaluationResult(false, false,
+ resultOfEval.getNumberValue(),
+ "Results was out of range based on precision " + " of "
+ + precision + ". Delta was actually " + delta, delta, cellName );
+ } else {
+ evalResults = new ExcelAntEvaluationResult(false, true,
+ resultOfEval.getNumberValue(),
+ "Evaluation passed without error within in range.", delta, cellName );
+ }
+ } else {
+ String errorMeaning = null ;
+ try {
+ errorMeaning = ErrorConstants.getText( resultOfEval
+ .getErrorValue() ) ;
+ } catch( IllegalArgumentException iae ) {
+ errorMeaning = "unknown error code: " +
+ Byte.toString( resultOfEval.getErrorValue() ) ;
+ }
+
+ evalResults = new ExcelAntEvaluationResult(false, false,
+ resultOfEval.getNumberValue(),
+ "Evaluation failed due to an evaluation error of "
+ + resultOfEval.getErrorValue()
+ + " which is "
+ + errorMeaning, 0, cellName );
+ }
+
+ return evalResults;
+ }
+
+ /**
+ * Returns a Cell as a String value.
+ *
+ * @param cellName
+ * @return
+ */
+ public String getCellAsString( String cellName ) {
+ Cell cell = getCell( cellName ) ;
+ if( cell != null ) {
+ return cell.getStringCellValue() ;
+ }
+ return "" ;
+ }
+
+
+ /**
+ * Returns the value of the Cell as a double.
+ *
+ * @param cellName
+ * @return
+ */
+ public double getCellAsDouble( String cellName ) {
+ Cell cell = getCell( cellName ) ;
+ if( cell != null ) {
+ return cell.getNumericCellValue() ;
+ }
+ return 0.0 ;
+ }
+ /**
+ * Returns a cell reference based on a String in standard Excel format
+ * (SheetName!CellId). This method will create a new cell if the
+ * requested cell isn't initialized yet.
+ *
+ * @param cellName
+ * @return
+ */
+ private Cell getCell(String cellName) {
+
+ CellReference cellRef = new CellReference(cellName);
+ String sheetName = cellRef.getSheetName();
+ Sheet sheet = workbook.getSheet(sheetName);
+ if(sheet == null) {
+ throw new BuildException("Sheet not found: " + sheetName);
+ }
+
+ int rowIdx = cellRef.getRow();
+ int colIdx = cellRef.getCol();
+ Row row = sheet.getRow(rowIdx);
+
+ if( row == null ) {
+ row = sheet.createRow( rowIdx ) ;
+ }
+
+ Cell cell = row.getCell(colIdx);
+
+ if( cell == null ) {
+ cell = row.createCell( colIdx ) ;
+ }
+
+ return cell;
+ }
+
+}
diff --git a/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java
new file mode 100755
index 0000000..6c29eeb
--- /dev/null
+++ b/src/excelant/java/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilFactory.java
@@ -0,0 +1,63 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+
+package org.apache.poi.ss.excelant.util;
+
+import java.util.HashMap;
+
+
+/**
+ * This is a factory class maps file names to WorkbookUtil instances. This
+ * helps ExcelAnt be more efficient when being run many times in an Ant build.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class ExcelAntWorkbookUtilFactory {
+
+ private static HashMap<String, ExcelAntWorkbookUtil> workbookUtilMap ;
+
+ private static ExcelAntWorkbookUtilFactory factory ;
+
+ private ExcelAntWorkbookUtilFactory() {
+ workbookUtilMap = new HashMap<String, ExcelAntWorkbookUtil>() ;
+ }
+
+ /**
+ * Using the fileName, check the internal map to see if an instance
+ * of the WorkbookUtil exists. If not, then add an instance to the map.
+ *
+ * @param fileName
+ * @return
+ */
+ public static ExcelAntWorkbookUtil getInstance( String fileName ) {
+
+ if( factory == null ) {
+ factory = new ExcelAntWorkbookUtilFactory() ;
+ }
+ if( workbookUtilMap != null &&
+ workbookUtilMap.containsKey( fileName ) ) {
+ return workbookUtilMap.get( fileName ) ;
+ } else {
+ ExcelAntWorkbookUtil wbu = new ExcelAntWorkbookUtil( fileName ) ;
+ workbookUtilMap.put( fileName, wbu ) ;
+ return wbu ;
+ }
+ }
+
+}
diff --git a/src/excelant/resources/org/apache/poi/ss/excelant/antlib.xml b/src/excelant/resources/org/apache/poi/ss/excelant/antlib.xml
new file mode 100755
index 0000000..31dadfa
--- /dev/null
+++ b/src/excelant/resources/org/apache/poi/ss/excelant/antlib.xml
@@ -0,0 +1,30 @@
+<?xml version="1.0"?>
+<!--
+Licensed to the Apache Software Foundation (ASF) under one
+or more contributor license agreements. See the NOTICE file
+distributed with this work for additional information
+regarding copyright ownership. The ASF licenses this file
+to you under the Apache License, Version 2.0 (the
+"License"); you may not use this file except in compliance
+with the License. You may obtain a copy of the License at
+
+http://www.apache.org/licenses/LICENSE-2.0
+
+Unless required by applicable law or agreed to in writing,
+software distributed under the License is distributed on an
+"AS IS" BASIS, WITHOUT WARRANTIES OR CONDITIONS OF ANY
+KIND, either express or implied. See the License for the
+specific language governing permissions and limitations
+under the License.
+-->
+<antlib>
+
+ <typedef name="excelant" classname="org.apache.poi.ss.excelant.ExcelAntTask" />
+ <typedef name="test" classname="org.apache.poi.ss.excelant.ExcelAntTest"/>
+ <typedef name="setDouble" classname="org.apache.poi.ss.excelant.ExcelAntSetDoubleCell"/>
+ <typedef name="setString" classname="org.apache.poi.ss.excelant.ExcelAntSetStringCell"/>
+ <typedef name="setFormula" classname="org.apache.poi.ss.excelant.ExcelAntSetFormulaCell"/>
+ <typedef name="evaluate" classname="org.apache.poi.ss.excelant.ExcelAntEvaluateCell"/>
+ <typedef name="udf" classname="org.apache.poi.ss.excelant.ExcelAntUserDefinedFunction"/>
+
+</antlib>
diff --git a/src/excelant/testcases/org/apache/poi/ss/examples/formula/CalculateMortgage.java b/src/excelant/testcases/org/apache/poi/ss/examples/formula/CalculateMortgage.java
new file mode 100755
index 0000000..4b9a325
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/examples/formula/CalculateMortgage.java
@@ -0,0 +1,93 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.examples.formula;
+
+import org.apache.poi.ss.formula.OperationEvaluationContext ;
+import org.apache.poi.ss.formula.eval.ErrorEval ;
+import org.apache.poi.ss.formula.eval.EvaluationException ;
+import org.apache.poi.ss.formula.eval.NumberEval ;
+import org.apache.poi.ss.formula.eval.OperandResolver ;
+import org.apache.poi.ss.formula.eval.ValueEval ;
+import org.apache.poi.ss.formula.functions.FreeRefFunction ;
+
+/**
+ * A simple user-defined function to calculate principal and interest.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class CalculateMortgage implements FreeRefFunction {
+
+ public ValueEval evaluate( ValueEval[] args, OperationEvaluationContext ec ) {
+
+ // verify that we have enough data
+ if (args.length != 3) {
+ return ErrorEval.VALUE_INVALID;
+ }
+
+ // declare doubles for values
+ double principal, rate, years, result;
+ try {
+ // extract values as ValueEval
+ ValueEval v1 = OperandResolver.getSingleValue( args[0],
+ ec.getRowIndex(),
+ ec.getColumnIndex() ) ;
+ ValueEval v2 = OperandResolver.getSingleValue( args[1],
+ ec.getRowIndex(),
+ ec.getColumnIndex() ) ;
+ ValueEval v3 = OperandResolver.getSingleValue( args[2],
+ ec.getRowIndex(),
+ ec.getColumnIndex() ) ;
+
+ // get data as doubles
+ principal = OperandResolver.coerceValueToDouble( v1 ) ;
+ rate = OperandResolver.coerceValueToDouble( v2 ) ;
+ years = OperandResolver.coerceValueToDouble( v3 ) ;
+
+ result = calculateMortgagePayment( principal, rate, years ) ;
+ System.out.println( "Result = " + result ) ;
+
+ checkValue(result);
+
+ } catch (EvaluationException e) {
+ return e.getErrorEval();
+ }
+
+ return new NumberEval( result ) ;
+ }
+
+ public double calculateMortgagePayment( double p, double r, double y ) {
+ double i = r / 12 ;
+ double n = y * 12 ;
+
+ double principalAndInterest =
+ p * (( i * Math.pow((1 + i),n ) ) / ( Math.pow((1 + i),n) - 1)) ;
+
+ return principalAndInterest ;
+ }
+ /**
+ * Excel does not support infinities and NaNs, rather, it gives a #NUM! error in these cases
+ *
+ * @throws EvaluationException (#NUM!) if <tt>result</tt> is <tt>NaN</> or <tt>Infinity</tt>
+ */
+ private void checkValue(double result) throws EvaluationException {
+ if (Double.isNaN(result) || Double.isInfinite(result)) {
+ throw new EvaluationException(ErrorEval.NUM_ERROR);
+ }
+ }
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/examples/formula/ExcelAntUserDefinedFunctionTestHelper.java b/src/excelant/testcases/org/apache/poi/ss/examples/formula/ExcelAntUserDefinedFunctionTestHelper.java
new file mode 100755
index 0000000..68e4b69
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/examples/formula/ExcelAntUserDefinedFunctionTestHelper.java
@@ -0,0 +1,36 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.examples.formula;
+
+import org.apache.poi.ss.excelant.ExcelAntUserDefinedFunction;
+
+public class ExcelAntUserDefinedFunctionTestHelper extends
+ ExcelAntUserDefinedFunction {
+
+ @Override
+ protected String getFunctionAlias() {
+ // TODO Auto-generated method stub
+ return super.getFunctionAlias();
+ }
+
+ @Override
+ protected String getClassName() {
+ // TODO Auto-generated method stub
+ return super.getClassName();
+ }
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/examples/formula/TestExcelAntUserDefinedFunction.java b/src/excelant/testcases/org/apache/poi/ss/examples/formula/TestExcelAntUserDefinedFunction.java
new file mode 100755
index 0000000..ad9851a
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/examples/formula/TestExcelAntUserDefinedFunction.java
@@ -0,0 +1,51 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.examples.formula;
+
+import junit.framework.TestCase;
+
+public class TestExcelAntUserDefinedFunction extends TestCase {
+
+ private ExcelAntUserDefinedFunctionTestHelper fixture ;
+
+ @Override
+ public void setUp() {
+ fixture = new ExcelAntUserDefinedFunctionTestHelper() ;
+ }
+
+ public void testSetClassName() {
+ String className = "simple.class.name" ;
+
+ fixture.setClassName( className ) ;
+ String value = fixture.getClassName() ;
+
+ assertNotNull( value ) ;
+ assertEquals( className, value ) ;
+ }
+
+ public void testSetFunction() {
+ String functionAlias = "alias" ;
+
+ fixture.setFunctionAlias( functionAlias ) ;
+
+ String alias = fixture.getFunctionAlias() ;
+
+ assertNotNull( alias ) ;
+ assertEquals( functionAlias, alias ) ;
+ }
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/BuildFileTest.java b/src/excelant/testcases/org/apache/poi/ss/excelant/BuildFileTest.java
new file mode 100755
index 0000000..5a30744
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/BuildFileTest.java
@@ -0,0 +1,583 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ *
+ */
+
+package org.apache.poi.ss.excelant;
+
+import junit.framework.TestCase;
+import org.apache.tools.ant.*;
+
+import java.io.File;
+import java.io.PrintStream;
+import java.net.URL;
+
+/**
+ * A BuildFileTest is a TestCase which executes targets from an Ant buildfile
+ * for testing.
+ * <p/>
+ * This class provides a number of utility methods for particular build file
+ * tests which extend this class.
+ *
+ * @see <a href="http://svn.apache.org/repos/asf/ant/core/trunk/src/tests/junit/org/apache/tools/ant/BuildFileTest.java">
+ * http://svn.apache.org/repos/asf/ant/core/trunk/src/tests/junit/org/apache/tools/ant/BuildFileTest.java</a>
+ */
+public abstract class BuildFileTest extends TestCase {
+
+ protected Project project;
+
+ private StringBuffer logBuffer;
+ private StringBuffer fullLogBuffer;
+ private StringBuffer outBuffer;
+ private StringBuffer errBuffer;
+ private BuildException buildException;
+
+ /**
+ * Default constructor for the BuildFileTest object.
+ */
+ public BuildFileTest() {
+ super();
+ }
+
+ /**
+ * Constructor for the BuildFileTest object.
+ *
+ * @param name string to pass up to TestCase constructor
+ */
+ public BuildFileTest(String name) {
+ super(name);
+ }
+
+ /**
+ * Automatically calls the target called "tearDown"
+ * from the build file tested if it exits.
+ * <p/>
+ * This allows to use Ant tasks directly in the build file
+ * to clean up after each test. Note that no "setUp" target
+ * is automatically called, since it's trivial to have a
+ * test target depend on it.
+ */
+ protected void tearDown() throws Exception {
+ if (project == null) {
+ /*
+ * Maybe the BuildFileTest was subclassed and there is
+ * no initialized project. So we could avoid getting a
+ * NPE.
+ * If there is an initialized project getTargets() does
+ * not return null as it is initialized by an empty
+ * HashSet.
+ */
+ return;
+ }
+ final String tearDown = "tearDown";
+ if (project.getTargets().containsKey(tearDown)) {
+ project.executeTarget(tearDown);
+ }
+ }
+
+ /**
+ * run a target, expect for any build exception
+ *
+ * @param target target to run
+ * @param cause information string to reader of report
+ */
+ public void expectBuildException(String target, String cause) {
+ expectSpecificBuildException(target, cause, null);
+ }
+
+ /**
+ * Assert that only the given message has been logged with a
+ * priority <= INFO when running the given target.
+ */
+ public void expectLog(String target, String log) {
+ executeTarget(target);
+ String realLog = getLog();
+ assertEquals(log, realLog);
+ }
+
+ /**
+ * Assert that the given substring is in the log messages.
+ */
+ public void assertLogContaining(String substring) {
+ String realLog = getLog();
+ assertTrue("expecting log to contain \"" + substring + "\" log was \""
+ + realLog + "\"",
+ realLog.indexOf(substring) >= 0);
+ }
+
+ /**
+ * Assert that the given substring is not in the log messages.
+ */
+ public void assertLogNotContaining(String substring) {
+ String realLog = getLog();
+ assertFalse("didn't expect log to contain \"" + substring + "\" log was \""
+ + realLog + "\"",
+ realLog.indexOf(substring) >= 0);
+ }
+
+ /**
+ * Assert that the given substring is in the output messages.
+ *
+ * @since Ant1.7
+ */
+ public void assertOutputContaining(String substring) {
+ assertOutputContaining(null, substring);
+ }
+
+ /**
+ * Assert that the given substring is in the output messages.
+ *
+ * @param message Print this message if the test fails. Defaults to
+ * a meaningful text if <tt>null</tt> is passed.
+ * @since Ant1.7
+ */
+ public void assertOutputContaining(String message, String substring) {
+ String realOutput = getOutput();
+ String realMessage = (message != null)
+ ? message
+ : "expecting output to contain \"" + substring + "\" output was \"" + realOutput + "\"";
+ assertTrue(realMessage, realOutput.indexOf(substring) >= 0);
+ }
+
+ /**
+ * Assert that the given substring is not in the output messages.
+ *
+ * @param message Print this message if the test fails. Defaults to
+ * a meaningful text if <tt>null</tt> is passed.
+ * @since Ant1.7
+ */
+ public void assertOutputNotContaining(String message, String substring) {
+ String realOutput = getOutput();
+ String realMessage = (message != null)
+ ? message
+ : "expecting output to not contain \"" + substring + "\" output was \"" + realOutput + "\"";
+ assertFalse(realMessage, realOutput.indexOf(substring) >= 0);
+ }
+
+ /**
+ * Assert that the given message has been logged with a priority <= INFO when running the
+ * given target.
+ */
+ public void expectLogContaining(String target, String log) {
+ executeTarget(target);
+ assertLogContaining(log);
+ }
+
+ /**
+ * Assert that the given message has not been logged with a
+ * priority <= INFO when running the given target.
+ */
+ public void expectLogNotContaining(String target, String log) {
+ executeTarget(target);
+ assertLogNotContaining(log);
+ }
+
+ /**
+ * Gets the log the BuildFileTest object.
+ * Only valid if configureProject() has been called.
+ *
+ * @return The log value
+ * @pre logBuffer!=null
+ */
+ public String getLog() {
+ return logBuffer.toString();
+ }
+
+ /**
+ * Assert that the given message has been logged with a priority
+ * >= VERBOSE when running the given target.
+ */
+ public void expectDebuglog(String target, String log) {
+ executeTarget(target);
+ String realLog = getFullLog();
+ assertEquals(log, realLog);
+ }
+
+ /**
+ * Assert that the given substring is in the log messages.
+ */
+ public void assertDebuglogContaining(String substring) {
+ String realLog = getFullLog();
+ assertTrue("expecting debug log to contain \"" + substring
+ + "\" log was \""
+ + realLog + "\"",
+ realLog.indexOf(substring) >= 0);
+ }
+
+ /**
+ * Gets the log the BuildFileTest object.
+ * <p/>
+ * Only valid if configureProject() has been called.
+ *
+ * @return The log value
+ * @pre fullLogBuffer!=null
+ */
+ public String getFullLog() {
+ return fullLogBuffer.toString();
+ }
+
+ /**
+ * execute the target, verify output matches expectations
+ *
+ * @param target target to execute
+ * @param output output to look for
+ */
+ public void expectOutput(String target, String output) {
+ executeTarget(target);
+ String realOutput = getOutput();
+ assertEquals(output, realOutput.trim());
+ }
+
+ /**
+ * Executes the target, verify output matches expectations
+ * and that we got the named error at the end
+ *
+ * @param target target to execute
+ * @param output output to look for
+ * @param error Description of Parameter
+ */
+ public void expectOutputAndError(String target, String output, String error) {
+ executeTarget(target);
+ String realOutput = getOutput();
+ assertEquals(output, realOutput);
+ String realError = getError();
+ assertEquals(error, realError);
+ }
+
+ public String getOutput() {
+ return cleanBuffer(outBuffer);
+ }
+
+ public String getError() {
+ return cleanBuffer(errBuffer);
+ }
+
+ public BuildException getBuildException() {
+ return buildException;
+ }
+
+ private String cleanBuffer(StringBuffer buffer) {
+ StringBuffer cleanedBuffer = new StringBuffer();
+ for (int i = 0; i < buffer.length(); i++) {
+ char ch = buffer.charAt(i);
+ if (ch != '\r') {
+ cleanedBuffer.append(ch);
+ }
+ }
+ return cleanedBuffer.toString();
+ }
+
+ /**
+ * Sets up to run the named project
+ *
+ * @param filename name of project file to run
+ */
+ public void configureProject(String filename) throws BuildException {
+ configureProject(filename, Project.MSG_DEBUG);
+ }
+
+ /**
+ * Sets up to run the named project
+ *
+ * @param filename name of project file to run
+ */
+ public void configureProject(String filename, int logLevel)
+ throws BuildException {
+ logBuffer = new StringBuffer();
+ fullLogBuffer = new StringBuffer();
+ project = new Project();
+ project.init();
+ File antFile = new File(System.getProperty("root"), filename);
+ project.setUserProperty("ant.file", antFile.getAbsolutePath());
+ project.addBuildListener(new AntTestListener(logLevel));
+ ProjectHelper.configureProject(project, antFile);
+ }
+
+ /**
+ * Executes a target we have set up
+ *
+ * @param targetName target to run
+ * @pre configureProject has been called
+ */
+ public void executeTarget(String targetName) {
+ PrintStream sysOut = System.out;
+ PrintStream sysErr = System.err;
+ try {
+ sysOut.flush();
+ sysErr.flush();
+ outBuffer = new StringBuffer();
+ PrintStream out = new PrintStream(new AntOutputStream(outBuffer));
+ System.setOut(out);
+ errBuffer = new StringBuffer();
+ PrintStream err = new PrintStream(new AntOutputStream(errBuffer));
+ System.setErr(err);
+ logBuffer = new StringBuffer();
+ fullLogBuffer = new StringBuffer();
+ buildException = null;
+ project.executeTarget(targetName);
+ } finally {
+ System.setOut(sysOut);
+ System.setErr(sysErr);
+ }
+
+ }
+
+ /**
+ * Get the project which has been configured for a test.
+ *
+ * @return the Project instance for this test.
+ */
+ public Project getProject() {
+ return project;
+ }
+
+ /**
+ * Gets the directory of the project.
+ *
+ * @return the base dir of the project
+ */
+ public File getProjectDir() {
+ return project.getBaseDir();
+ }
+
+ /**
+ * Runs a target, wait for a build exception.
+ *
+ * @param target target to run
+ * @param cause information string to reader of report
+ * @param msg the message value of the build exception we are waiting
+ * for set to null for any build exception to be valid
+ */
+ public void expectSpecificBuildException(String target, String cause, String msg) {
+ try {
+ executeTarget(target);
+ } catch (org.apache.tools.ant.BuildException ex) {
+ buildException = ex;
+ if ((null != msg) && (!ex.getMessage().equals(msg))) {
+ fail("Should throw BuildException because '" + cause
+ + "' with message '" + msg
+ + "' (actual message '" + ex.getMessage() + "' instead)");
+ }
+ return;
+ }
+ fail("Should throw BuildException because: " + cause);
+ }
+
+ /**
+ * run a target, expect an exception string
+ * containing the substring we look for (case sensitive match)
+ *
+ * @param target target to run
+ * @param cause information string to reader of report
+ * @param contains substring of the build exception to look for
+ */
+ public void expectBuildExceptionContaining(String target, String cause, String contains) {
+ try {
+ executeTarget(target);
+ } catch (org.apache.tools.ant.BuildException ex) {
+ buildException = ex;
+ if ((null != contains) && (ex.getMessage().indexOf(contains) == -1)) {
+ fail("Should throw BuildException because '" + cause + "' with message containing '" + contains + "' (actual message '" + ex.getMessage() + "' instead)");
+ }
+ return;
+ }
+ fail("Should throw BuildException because: " + cause);
+ }
+
+ /**
+ * call a target, verify property is as expected
+ *
+ * @param target build file target
+ * @param property property name
+ * @param value expected value
+ */
+ public void expectPropertySet(String target, String property, String value) {
+ executeTarget(target);
+ assertPropertyEquals(property, value);
+ }
+
+ /**
+ * assert that a property equals a value; comparison is case sensitive.
+ *
+ * @param property property name
+ * @param value expected value
+ */
+ public void assertPropertyEquals(String property, String value) {
+ String result = project.getProperty(property);
+ assertEquals("property " + property, value, result);
+ }
+
+ /**
+ * assert that a property equals "true".
+ *
+ * @param property property name
+ */
+ public void assertPropertySet(String property) {
+ assertPropertyEquals(property, "true");
+ }
+
+ /**
+ * assert that a property is null.
+ *
+ * @param property property name
+ */
+ public void assertPropertyUnset(String property) {
+ String result = project.getProperty(property);
+ if (result != null) {
+ fail("Expected property " + property
+ + " to be unset, but it is set to the value: " + result);
+ }
+ }
+
+ /**
+ * call a target, verify named property is "true".
+ *
+ * @param target build file target
+ * @param property property name
+ */
+ public void expectPropertySet(String target, String property) {
+ expectPropertySet(target, property, "true");
+ }
+
+ /**
+ * Call a target, verify property is null.
+ *
+ * @param target build file target
+ * @param property property name
+ */
+ public void expectPropertyUnset(String target, String property) {
+ expectPropertySet(target, property, null);
+ }
+
+ /**
+ * Retrieve a resource from the caller classloader to avoid
+ * assuming a vm working directory. The resource path must be
+ * relative to the package name or absolute from the root path.
+ *
+ * @param resource the resource to retrieve its url.
+ * @throws junit.framework.AssertionFailedError
+ * if the resource is not found.
+ */
+ public URL getResource(String resource) {
+ URL url = getClass().getResource(resource);
+ assertNotNull("Could not find resource :" + resource, url);
+ return url;
+ }
+
+ /**
+ * an output stream which saves stuff to our buffer.
+ */
+ protected static class AntOutputStream extends java.io.OutputStream {
+ private StringBuffer buffer;
+
+ public AntOutputStream(StringBuffer buffer) {
+ this.buffer = buffer;
+ }
+
+ public void write(int b) {
+ buffer.append((char) b);
+ }
+ }
+
+ /**
+ * Our own personal build listener.
+ */
+ private class AntTestListener implements BuildListener {
+ private int logLevel;
+
+ /**
+ * Constructs a test listener which will ignore log events
+ * above the given level.
+ */
+ public AntTestListener(int logLevel) {
+ this.logLevel = logLevel;
+ }
+
+ /**
+ * Fired before any targets are started.
+ */
+ public void buildStarted(BuildEvent event) {
+ }
+
+ /**
+ * Fired after the last target has finished. This event
+ * will still be thrown if an error occurred during the build.
+ *
+ * @see BuildEvent#getException()
+ */
+ public void buildFinished(BuildEvent event) {
+ }
+
+ /**
+ * Fired when a target is started.
+ *
+ * @see BuildEvent#getTarget()
+ */
+ public void targetStarted(BuildEvent event) {
+ //System.out.println("targetStarted " + event.getTarget().getName());
+ }
+
+ /**
+ * Fired when a target has finished. This event will
+ * still be thrown if an error occurred during the build.
+ *
+ * @see BuildEvent#getException()
+ */
+ public void targetFinished(BuildEvent event) {
+ //System.out.println("targetFinished " + event.getTarget().getName());
+ }
+
+ /**
+ * Fired when a task is started.
+ *
+ * @see BuildEvent#getTask()
+ */
+ public void taskStarted(BuildEvent event) {
+ //System.out.println("taskStarted " + event.getTask().getTaskName());
+ }
+
+ /**
+ * Fired when a task has finished. This event will still
+ * be throw if an error occurred during the build.
+ *
+ * @see BuildEvent#getException()
+ */
+ public void taskFinished(BuildEvent event) {
+ //System.out.println("taskFinished " + event.getTask().getTaskName());
+ }
+
+ /**
+ * Fired whenever a message is logged.
+ *
+ * @see BuildEvent#getMessage()
+ * @see BuildEvent#getPriority()
+ */
+ public void messageLogged(BuildEvent event) {
+ if (event.getPriority() > logLevel) {
+ // ignore event
+ return;
+ }
+
+ if (event.getPriority() == Project.MSG_INFO ||
+ event.getPriority() == Project.MSG_WARN ||
+ event.getPriority() == Project.MSG_ERR) {
+ logBuffer.append(event.getMessage());
+ }
+ fullLogBuffer.append(event.getMessage());
+ }
+ }
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/TestBuildFile.java b/src/excelant/testcases/org/apache/poi/ss/excelant/TestBuildFile.java
new file mode 100644
index 0000000..4d8f0d5
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/TestBuildFile.java
@@ -0,0 +1,74 @@
+/*
+ * Licensed to the Apache Software Foundation (ASF) under one or more
+ * contributor license agreements. See the NOTICE file distributed with
+ * this work for additional information regarding copyright ownership.
+ * The ASF licenses this file to You under the Apache License, Version 2.0
+ * (the "License"); you may not use this file except in compliance with
+ * the License. You may obtain a copy of the License at
+ *
+ * http://www.apache.org/licenses/LICENSE-2.0
+ *
+ * Unless required by applicable law or agreed to in writing, software
+ * distributed under the License is distributed on an "AS IS" BASIS,
+ * WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ * See the License for the specific language governing permissions and
+ * limitations under the License.
+ *
+ */
+package org.apache.poi.ss.excelant;
+
+/**
+ * JUnit test for the ExcelAnt tasks.
+ * Leverages Ant's test framework.
+ *
+ * @see <a href="http://svn.apache.org/repos/asf/ant/core/trunk/src/tests/junit/org/apache/tools/ant/BuildFileTest.java">
+ * http://svn.apache.org/repos/asf/ant/core/trunk/src/tests/junit/org/apache/tools/ant/BuildFileTest.java</a>
+ */
+public class TestBuildFile extends BuildFileTest {
+
+ public void setUp() {
+ configureProject("src/excelant/testcases/org/apache/poi/ss/excelant/tests.xml");
+ }
+
+ public void testMissingFilename() {
+ expectSpecificBuildException("test-nofile", "required argument not specified",
+ "fileName attribute must be set!");
+ }
+
+ public void testFileNotFound() {
+ expectSpecificBuildException("test-filenotfound", "required argument not specified",
+ "Cannot load file invalid.xls. Make sure the path and file permissions are correct.");
+ }
+
+ public void testEvaluate() {
+ executeTarget("test-evaluate");
+ assertLogContaining("Using input file: test-data/spreadsheet/excelant.xls");
+ assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4.");
+ }
+
+ public void testPrecision() {
+ executeTarget("test-precision");
+
+ assertLogContaining("Using input file: test-data/spreadsheet/excelant.xls");
+ assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " +
+ "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-4");
+ assertLogContaining("Succeeded when evaluating 'MortgageCalculator'!$B$4. " +
+ "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-5");
+ assertLogContaining("Failed to evaluate cell 'MortgageCalculator'!$B$4. " +
+ "It evaluated to 2285.5761494145563 when the value of 2285.576149 with precision of 1.0E-10 was expected.");
+ assertLogContaining("2/3 tests passed");
+ }
+
+ public void testPassOnError() {
+ executeTarget("test-passonerror");
+ }
+
+ public void testFailOnError() {
+ expectBuildException("test-failonerror", "fail on error");
+ }
+
+ public void testUdf() {
+ executeTarget("test-udf");
+ assertLogContaining("1/1 tests passed");
+ }
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntPrecision.java b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntPrecision.java
new file mode 100755
index 0000000..0c6f9cb
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntPrecision.java
@@ -0,0 +1,48 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant;
+
+import junit.framework.TestCase;
+
+public class TestExcelAntPrecision extends TestCase {
+
+ private ExcelAntPrecision fixture ;
+
+ @Override
+ public void setUp() {
+ fixture = new ExcelAntPrecision() ;
+ }
+
+ @Override
+ public void tearDown() {
+ fixture = null ;
+ }
+
+ public void testVerifyPrecision() {
+
+ double value = 1.0E-1 ;
+
+ fixture.setValue( value ) ;
+
+ double result = fixture.getValue() ;
+
+ assertTrue( result > 0 ) ;
+
+ assertEquals( value, result, 0.0 ) ;
+ }
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSet.java b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSet.java
new file mode 100755
index 0000000..cfba01b
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSet.java
@@ -0,0 +1,64 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory;
+
+public class TestExcelAntSet extends TestCase {
+
+
+ // This is abstract in nature, so we'll use a
+ // concrete instance to test the set methods.
+ private ExcelAntSet fixture ;
+
+ private final String mortgageCalculatorFileName =
+ "test-data/spreadsheet/mortgage-calculation.xls" ;
+
+
+ @Override
+ public void setUp() {
+ fixture = new ExcelAntSetDoubleCell() ;
+ }
+
+ @Override
+ public void tearDown() {
+ fixture = null ;
+ }
+
+ public void testSetter() {
+ String cell = "simpleCellRef!$F$1" ;
+
+ fixture.setCell( cell ) ;
+
+ String cellStr = fixture.getCell() ;
+
+ assertNotNull( cellStr ) ;
+ assertEquals( cell, cellStr ) ;
+ }
+
+ public void testSetWorkbookUtil() {
+ ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( util ) ;
+
+ fixture.setWorkbookUtil( util ) ;
+ }
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java
new file mode 100755
index 0000000..1b9e527
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/TestExcelAntSetDoubleCell.java
@@ -0,0 +1,66 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtil;
+import org.apache.poi.ss.excelant.util.ExcelAntWorkbookUtilFactory;
+
+public class TestExcelAntSetDoubleCell extends TestCase {
+
+ private ExcelAntSetDoubleCell fixture ;
+
+ private final String mortgageCalculatorFileName =
+ "test-data/spreadsheet/mortgage-calculation.xls" ;
+
+ private ExcelAntWorkbookUtil util ;
+
+ @Override
+ public void setUp() {
+ fixture = new ExcelAntSetDoubleCell() ;
+ util = ExcelAntWorkbookUtilFactory.getInstance(
+ mortgageCalculatorFileName ) ;
+ fixture.setWorkbookUtil( util ) ;
+ }
+
+ @Override
+ public void tearDown() {
+ fixture = null ;
+ }
+
+ public void testSetDouble() {
+ String cellId = "'Sheet3'!$A$1" ;
+ double testValue = 1.1 ;
+
+ fixture.setCell( cellId ) ;
+ fixture.setValue( testValue ) ;
+
+ double value = fixture.getCellValue() ;
+
+ assertTrue( value > 0 ) ;
+ assertEquals( testValue, value, 0.0 ) ;
+
+ fixture.execute() ;
+
+ double setValue = util.getCellAsDouble( cellId ) ;
+
+ assertEquals( setValue, testValue, 0.0 ) ;
+ }
+
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java b/src/excelant/testcases/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java
new file mode 100755
index 0000000..cf7538c
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/util/ExcelAntWorkbookUtilTestHelper.java
@@ -0,0 +1,55 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant.util;
+
+import org.apache.poi.ss.formula.functions.FreeRefFunction;
+import org.apache.poi.ss.formula.udf.UDFFinder;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Workbook;
+
+/**
+ * A helper class to allow testing of protected methods and constructors.
+ *
+ * @author jsvede
+ *
+ */
+public class ExcelAntWorkbookUtilTestHelper extends ExcelAntWorkbookUtil {
+
+ public ExcelAntWorkbookUtilTestHelper(String fName) {
+ super(fName);
+ // TODO Auto-generated constructor stub
+ }
+
+ public ExcelAntWorkbookUtilTestHelper(Workbook wb) {
+ super(wb);
+ // TODO Auto-generated constructor stub
+ }
+
+ @Override
+ public UDFFinder getFunctions() {
+ // TODO Auto-generated method stub
+ return super.getFunctions();
+ }
+
+ @Override
+ public FormulaEvaluator getEvaluator(String excelFileName) {
+ // TODO Auto-generated method stub
+ return super.getEvaluator(excelFileName);
+ }
+
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java
new file mode 100755
index 0000000..833dea6
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntEvaluationResult.java
@@ -0,0 +1,67 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant.util;
+
+import junit.framework.TestCase;
+
+public class TestExcelAntEvaluationResult extends TestCase {
+
+ private ExcelAntEvaluationResult fixture ;
+
+ private boolean completedWithError = false ;
+ private boolean passed = false ;
+ private double retValue = 1.1 ;
+ private String errMessage = "error message" ;
+ private double delta = 2.2 ;
+ private String cellId = "testCell!$F$1" ;
+
+ public void setUp() {
+ fixture = new ExcelAntEvaluationResult( completedWithError,
+ passed,
+ retValue,
+ errMessage,
+ delta,
+ cellId ) ;
+ }
+
+ public void tearDown() {
+ fixture = null ;
+ }
+
+ public void testCompletedWithErrorMessage() {
+ String errMsg = fixture.getErrorMessage() ;
+ assertNotNull( errMsg ) ;
+ assertEquals( errMsg, errMessage ) ;
+ }
+
+ public void testPassed() {
+ boolean passedValue = fixture.didTestPass() ;
+ assertEquals( passedValue, passed ) ;
+ }
+
+ public void testDelta() {
+ double deltaValue = fixture.getDelta() ;
+ assertEquals(deltaValue, delta, 0.0 ) ;
+ }
+
+ public void testCellId() {
+ String cellIdValue = fixture.getCellName() ;
+ assertNotNull( cellIdValue ) ;
+ assertEquals( cellIdValue, cellId ) ;
+ }
+
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java
new file mode 100755
index 0000000..5b0d3e8
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtil.java
@@ -0,0 +1,140 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant.util;
+
+import java.util.ArrayList;
+
+import junit.framework.TestCase;
+
+import org.apache.poi.ss.examples.formula.CalculateMortgage;
+import org.apache.poi.ss.formula.udf.UDFFinder;
+import org.apache.poi.ss.usermodel.FormulaEvaluator;
+import org.apache.poi.ss.usermodel.Workbook;
+
+public class TestExcelAntWorkbookUtil extends TestCase {
+
+ private final String mortgageCalculatorFileName =
+ "test-data/spreadsheet/excelant.xls" ;
+ private ExcelAntWorkbookUtilTestHelper fixture ;
+
+
+ public void tearDown() {
+ fixture = null ;
+ }
+
+ public void testStringConstructor() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( fixture ) ;
+
+ }
+
+ public void testAddFunction() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( fixture ) ;
+
+ fixture.addFunction("h2_ZFactor", new CalculateMortgage() ) ;
+
+ UDFFinder functions = fixture.getFunctions() ;
+
+ assertNotNull( functions ) ;
+ }
+
+ public void testGetWorkbook() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( fixture ) ;
+
+ Workbook workbook = fixture.getWorkbook() ;
+
+ assertNotNull( workbook ) ;
+ }
+
+ public void testFileName() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( fixture ) ;
+
+ String fileName = fixture.getFileName() ;
+
+ assertNotNull( fileName ) ;
+
+ assertEquals( mortgageCalculatorFileName, fileName ) ;
+
+ }
+
+ public void testGetEvaluator() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ FormulaEvaluator evaluator = fixture.getEvaluator(
+ mortgageCalculatorFileName ) ;
+
+ assertNotNull( evaluator ) ;
+
+
+ }
+
+ public void testEvaluateCell() {
+ String cell = "'MortgageCalculator'!B4" ;
+ double expectedValue = 790.79 ;
+ double precision = 0.1 ;
+
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ ExcelAntEvaluationResult result = fixture.evaluateCell( cell,
+ expectedValue,
+ precision ) ;
+
+ System.out.println( result ) ;
+
+ assertTrue( result.didTestPass() ) ;
+ }
+
+ public void testGetSheets() {
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ ArrayList<String> sheets = fixture.getSheets() ;
+
+ assertNotNull( sheets ) ;
+ assertEquals( sheets.size(), 3 ) ;
+ }
+
+ public void testSetString() {
+ String cell = "'MortgageCalculator'!C14" ;
+ String cellValue = "testString" ;
+
+ fixture = new ExcelAntWorkbookUtilTestHelper(
+ mortgageCalculatorFileName ) ;
+
+ fixture.setStringValue( cell, cellValue ) ;
+
+ String value = fixture.getCellAsString( cell ) ;
+
+ assertNotNull( value ) ;
+
+ assertEquals( cellValue, value ) ;
+
+ }
+}
diff --git a/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java
new file mode 100755
index 0000000..79c898a
--- /dev/null
+++ b/src/excelant/testcases/org/apache/poi/ss/excelant/util/TestExcelAntWorkbookUtilFactory.java
@@ -0,0 +1,69 @@
+/* ====================================================================
+ Licensed to the Apache Software Foundation (ASF) under one or more
+ contributor license agreements. See the NOTICE file distributed with
+ this work for additional information regarding copyright ownership.
+ The ASF licenses this file to You under the Apache License, Version 2.0
+ (the "License"); you may not use this file except in compliance with
+ the License. You may obtain a copy of the License at
+
+ http://www.apache.org/licenses/LICENSE-2.0
+
+ Unless required by applicable law or agreed to in writing, software
+ distributed under the License is distributed on an "AS IS" BASIS,
+ WITHOUT WARRANTIES OR CONDITIONS OF ANY KIND, either express or implied.
+ See the License for the specific language governing permissions and
+ limitations under the License.
+==================================================================== */
+package org.apache.poi.ss.excelant.util;
+
+import junit.framework.TestCase;
+
+
+/**
+ * Tests for the ExcelAntWorbookUtilFactory.
+ *
+ * @author Jon Svede ( jon [at] loquatic [dot] com )
+ * @author Brian Bush ( brian [dot] bush [at] nrel [dot] gov )
+ *
+ */
+public class TestExcelAntWorkbookUtilFactory extends TestCase{
+
+ private final String mortgageCalculatorWorkbookFile =
+ "test-data/spreadsheet/mortgage-calculation.xls" ;
+
+
+ /**
+ * Simple test to determine if the factory properly returns an non-null
+ * instance of the ExcelAntWorkbookUtil class.
+ */
+ public void testGetNewWorkbookUtilInstance() {
+
+ ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance(
+ mortgageCalculatorWorkbookFile ) ;
+
+ assertNotNull( util ) ;
+
+ }
+
+
+ /**
+ * Test whether or not the factory will properly return the same reference
+ * to an ExcelAnt WorkbookUtil when two different Strings, that point to
+ * the same resource, are passed in.
+ */
+ public void testVerifyEquivalence() {
+ String sameFileName = "test-data/spreadsheet/mortgage-calculation.xls" ;
+
+ ExcelAntWorkbookUtil util = ExcelAntWorkbookUtilFactory.getInstance(
+ mortgageCalculatorWorkbookFile ) ;
+
+ ExcelAntWorkbookUtil util2 = ExcelAntWorkbookUtilFactory.getInstance(
+ sameFileName ) ;
+
+ assertNotNull( util ) ;
+ assertNotNull( util2 ) ;
+
+ assertEquals( util, util2 ) ;
+ }
+
+}
diff --git a/test-data/spreadsheet/excelant.xls b/test-data/spreadsheet/excelant.xls
new file mode 100644
index 0000000..e41edfb
--- /dev/null
+++ b/test-data/spreadsheet/excelant.xls
Binary files differ
diff --git a/test-data/spreadsheet/mortgage-calculation.xls b/test-data/spreadsheet/mortgage-calculation.xls
new file mode 100644
index 0000000..92e5779
--- /dev/null
+++ b/test-data/spreadsheet/mortgage-calculation.xls
Binary files differ
