diff options
33 files changed, 3441 insertions, 7 deletions
@@ -109,6 +109,15 @@ under the License. <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 @@ under the License. <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 @@ under the License. </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 @@ under the License. <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 @@ under the License. <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 @@ under the License. <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 @@ under the License. <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 @@ under the License. </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 @@ under the License. </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 @@ under the License. <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 @@ under the License. <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 @@ under the License. </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 @@ under the License. <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 @@ under the License. <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 @@ under the License. <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 @@ under the License. <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 @@ under the License. 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 @@ under the License. <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 0000000000..12b2b15a11 --- /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 09743224c6..15ac2a26f4 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 0000000000..6594bc1b87 --- /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 0000000000..d35cc6a7d8 --- /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 0000000000..af210f63ef --- /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 0000000000..0739ef5833 --- /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 0000000000..974b1b9e4c --- /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 0000000000..a1113509a2 --- /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 0000000000..1b9d5f0539 --- /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 0000000000..12221ee170 --- /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 0000000000..1bbbb7d01f --- /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 0000000000..b97d1ae917 --- /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 0000000000..f69e9b6366 --- /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 0000000000..3cd477abc8 --- /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 0000000000..6633357157 --- /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 0000000000..af23d69c0c --- /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 0000000000..6c29eeba74 --- /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 0000000000..31dadfaf60 --- /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 0000000000..4b9a325cdf --- /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 0000000000..68e4b6957f --- /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 0000000000..ad9851ae8f --- /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 0000000000..5a30744cd0 --- /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 0000000000..4d8f0d5a81 --- /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 0000000000..0c6f9cbf83 --- /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 0000000000..cfba01b585 --- /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 0000000000..1b9e527e07 --- /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 0000000000..cf7538c20b --- /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 0000000000..833dea67a6 --- /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 0000000000..5b0d3e8027 --- /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 0000000000..79c898a034 --- /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 Binary files differnew file mode 100644 index 0000000000..e41edfbfd2 --- /dev/null +++ b/test-data/spreadsheet/excelant.xls diff --git a/test-data/spreadsheet/mortgage-calculation.xls b/test-data/spreadsheet/mortgage-calculation.xls Binary files differnew file mode 100644 index 0000000000..92e5779b5e --- /dev/null +++ b/test-data/spreadsheet/mortgage-calculation.xls |