/*******************************************************************************
*
* Copyright 2010 Alexandru Craciun, and individual contributors as indicated
* by the @authors tag.
*
* This is free software; you can redistribute it and/or modify it
* under the terms of the GNU Lesser General Public License as
* published by the Free Software Foundation; either version 3 of
* the License, or (at your option) any later version.
*
* This software is distributed in the hope that it will be useful,
* but WITHOUT ANY WARRANTY; without even the implied warranty of
* MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the GNU
* Lesser General Public License for more details.
*
* You should have received a copy of the GNU Lesser General Public
* License along with this software; if not, write to the Free
* Software Foundation, Inc., 51 Franklin St, Fifth Floor, Boston, MA
* 02110-1301 USA, or see the FSF site: http://www.fsf.org.
******************************************************************************/
package org.netxilia.spi.impl.formula;
import java.util.Arrays;
import java.util.List;
import junit.framework.Assert;
import org.joda.time.LocalDate;
import org.joda.time.LocalDateTime;
import org.junit.Before;
import org.junit.Ignore;
import org.junit.Test;
import org.netxilia.api.exception.AlreadyExistsException;
import org.netxilia.api.exception.NetxiliaBusinessException;
import org.netxilia.api.exception.NetxiliaResourceException;
import org.netxilia.api.exception.NotFoundException;
import org.netxilia.api.exception.StorageException;
import org.netxilia.api.formula.Formula;
import org.netxilia.api.formula.FormulaParsingException;
import org.netxilia.api.impl.NetxiliaSystemImpl;
import org.netxilia.api.model.ISheet;
import org.netxilia.api.model.SheetType;
import org.netxilia.api.reference.CellReference;
import org.netxilia.api.reference.ReferenceTransformers;
import org.netxilia.api.user.AclPrivilegedMode;
import org.netxilia.api.value.ErrorValue;
import org.netxilia.api.value.ErrorValueType;
import org.netxilia.api.value.GenericValueType;
import org.netxilia.api.value.IGenericValue;
import org.netxilia.api.value.NumberValue;
import org.netxilia.spi.impl.structure.NoCheckAclServiceImpl;
import org.netxilia.spi.impl.structure.SheetUtils;
import org.springframework.context.ApplicationContext;
import org.springframework.context.support.ClassPathXmlApplicationContext;
public class TestFormulaParser {
private ApplicationContext context;
@Before
public void setup() {
context = new ClassPathXmlApplicationContext("classpath:test-domain-services.xml");
NetxiliaSystemImpl nx = context.getBean(NetxiliaSystemImpl.class);
nx.setAclService(new NoCheckAclServiceImpl());
AclPrivilegedMode.set();
}
private JavaCCFormulaParserImpl getParser() {
return context.getBean(JavaCCFormulaParserImpl.class);
}
@Test
public void testNumberOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(3.0,
parser.executeFormula(new Formula("=a1 + 1"), SheetUtils.sheetWithCell("A1", 2.0), null)
.getNumberValue(), 0.001);
Assert.assertEquals(125.0, parser.executeFormula(new Formula("=5^3"), SheetUtils.sheetWithCell(), null)
.getNumberValue(), 0.001);
Assert.assertEquals(1.0,
parser.executeFormula(new Formula("=a1 - 1"), SheetUtils.sheetWithCell("A1", 2.0), null)
.getNumberValue(), 0.001);
Assert.assertEquals(4.0,
parser.executeFormula(new Formula("=a1 * 2"), SheetUtils.sheetWithCell("A1", 2.0), null)
.getNumberValue(), 0.001);
Assert.assertEquals(1.0,
parser.executeFormula(new Formula("=a1 / 2"), SheetUtils.sheetWithCell("A1", 2.0), null)
.getNumberValue(), 0.001);
IGenericValue value = parser.executeFormula(new Formula("=a1 / 0"), SheetUtils.sheetWithCell("A1", 2.0), null);
Assert.assertTrue(value instanceof ErrorValue);
Assert.assertEquals(ErrorValueType.DIV_ZERO, ((ErrorValue) value).getErrorType());
try {
value = parser.executeFormula(new Formula("=a1 % 10"), SheetUtils.sheetWithCell("A1", 2.0), null);
Assert.fail("Expecting FormulaParsingException");
} catch (FormulaParsingException ex) {
//
}
}
@Test
public void testDateOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(
new LocalDateTime(2001, 1, 2, 0, 0),
parser.executeFormula(new Formula("=a1 + 1"),
SheetUtils.sheetWithCell("A1", new LocalDate(2001, 1, 1)), null).getDateValue());
Assert.assertEquals(
new LocalDateTime(2000, 12, 31, 0, 0),
parser.executeFormula(new Formula("=a1 - 1"),
SheetUtils.sheetWithCell("A1", new LocalDate(2001, 1, 1)), null).getDateValue());
Assert.assertEquals(
-2.0,
parser.executeFormula(new Formula("=a1 - b1"),
SheetUtils.sheetWithCell("A1", new LocalDate(2001, 1, 1), "B1", new LocalDate(2001, 1, 3)),
null).getNumberValue(), 0.001);
Assert.assertEquals(
new LocalDateTime(2001, 1, 2, 0, 0),
parser.executeFormula(new Formula("=1 + a1"),
SheetUtils.sheetWithCell("A1", new LocalDate(2001, 1, 1)), null).getDateValue());
}
@Test
public void testStringOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals("abcxy",
parser.executeFormula(new Formula("=a1 & \"xy\""), SheetUtils.sheetWithCell("A1", "abc"), null)
.getStringValue());
Assert.assertEquals("=A1 & \"xy\"", parser.parseFormula(new Formula("=a1 & \"xy\"")).getFormula());
}
@Test
public void testLogicalOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(true,
parser.executeFormula(new Formula("=A1=\"2\""), SheetUtils.sheetWithCell("A1", "2"), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(false,
parser.executeFormula(new Formula("=A1=2"), SheetUtils.sheetWithCell("A1", "2"), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(false,
parser.executeFormula(new Formula("=A1 < 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(false,
parser.executeFormula(new Formula("=A1 > 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(true,
parser.executeFormula(new Formula("=A1 <= 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(true,
parser.executeFormula(new Formula("=A1 >= 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(false,
parser.executeFormula(new Formula("=A1 <> 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(false,
parser.executeFormula(new Formula("=A1 != 2"), SheetUtils.sheetWithCell("A1", 2), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(
ErrorValueType.VALUE,
((ErrorValue) parser.executeFormula(new Formula("=A1 != 2"),
SheetUtils.sheetWithCell("A1", ErrorValueType.VALUE), null)).getErrorType());
Assert.assertEquals(
ErrorValueType.VALUE,
((ErrorValue) parser.executeFormula(new Formula("=2 != A1"),
SheetUtils.sheetWithCell("A1", ErrorValueType.VALUE), null)).getErrorType());
}
@Test
public void testFilter() throws NetxiliaResourceException, NetxiliaBusinessException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell("A1", "abc", "A2", "cde", "A3", "abc");
List<Integer> rows = parser.filterWithFormula(new Formula("=A1=\"abc\""), sheet);
Assert.assertNotNull(rows);
Assert.assertTrue(Arrays.deepEquals(new Integer[] { 0, 2 }, rows.toArray(new Integer[2])));
}
@Test
public void testFind() throws NetxiliaResourceException, NetxiliaBusinessException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell("A1", "abc", "A2", "cde", "A3", "abc");
CellReference ref = parser.find(null, new Formula("=A1=\"abc\""), sheet);
Assert.assertNotNull(ref);
Assert.assertEquals(0, ref.getRowIndex());
Assert.assertEquals(0, ref.getColumnIndex());
ref = parser.find(ref, new Formula("=A1=\"abc\""), sheet);
Assert.assertNotNull(ref);
Assert.assertEquals(2, ref.getRowIndex());
Assert.assertEquals(0, ref.getColumnIndex());
ref = parser.find(ref, new Formula("=A1=\"abc\""), sheet);
Assert.assertNull(ref);
}
@Test
public void testCrossReference() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell(context.getBean(NetxiliaSystemImpl.class), "A1", "100", "A2", "cde",
"A3", "abc");
ISheet sheet2 = sheet.getWorkbook().addNewSheet("test2", SheetType.normal);
Assert.assertEquals(110.0, parser.executeFormula(new Formula("=test!A1 + 10"), sheet2, null).getNumberValue(),
0.001);
}
@Test
public void testInvalidSheetReference() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell(context.getBean(NetxiliaSystemImpl.class), "A1", "100", "A2", "cde",
"A3", "abc");
IGenericValue value = parser.executeFormula(new Formula("=test2!A1 + 10"), sheet, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.ERROR, value.getValueType());
Assert.assertEquals(ErrorValueType.REF, ((ErrorValue) value).getErrorType());
}
@Test
public void testCrossReferenceComplexSheetName() throws FormulaParsingException, AlreadyExistsException,
StorageException, NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell(context.getBean(NetxiliaSystemImpl.class), "A1", "100", "A2", "cde",
"A3", "abc");
ISheet sheet2 = sheet.getWorkbook().addNewSheet("Sheet - �", SheetType.normal);
sheet2.sendValue(new CellReference("A1"), new NumberValue(101));
Assert.assertEquals(111.0, parser.executeFormula(new Formula("='Sheet - �'!A1 + 10"), sheet, null)
.getNumberValue(), 0.001);
}
@Test
public void testShortReferenceMainSheetName() throws FormulaParsingException, AlreadyExistsException,
StorageException, NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell(context.getBean(NetxiliaSystemImpl.class), "A1", "100", "A2", "cde",
"A3", "abc");
ISheet sheet2 = sheet.getWorkbook().addNewSheet("test.summary", SheetType.summary);
Assert.assertEquals(110.0, parser.executeFormula(new Formula("=test!A1 + 10"), sheet2, null).getNumberValue(),
0.001);
Assert.assertEquals(110.0, parser.executeFormula(new Formula("=.!A1 + 10"), sheet2, null).getNumberValue(),
0.001);
}
@Test
public void testFunctionWithReference() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell("A1", "2", "A2", 3, "B3", 2.5);
// use reference outside
IGenericValue value = parser.executeFormula(new Formula("=AND(A1, A5)"), sheet, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.BOOLEAN, value.getValueType());
Assert.assertEquals(Boolean.TRUE, value.getBooleanValue());
// use reference outside
value = parser.executeFormula(new Formula("=IF(B1, A1, A2)"), sheet, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.NUMBER, value.getValueType());
Assert.assertEquals(3, value.getNumberValue(), 0.01);
}
@Test
public void testWrongFunction() throws FormulaParsingException, StorageException, AlreadyExistsException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
// with IGenericValue
ISheet sheet = SheetUtils.sheetWithCell();
IGenericValue value = parser.executeFormula(new Formula("=XXSUM(20, 10)"), sheet, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.ERROR, value.getValueType());
Assert.assertEquals(new ErrorValue(ErrorValueType.NAME), value);
}
@Ignore
public void testTrueFalseFunction() throws FormulaParsingException {
JavaCCFormulaParserImpl parser = getParser();
// with IGenericValue
IGenericValue value = parser.executeFormula(new Formula("=TRUE()"), null, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.BOOLEAN, value.getValueType());
Assert.assertEquals(Boolean.TRUE, value.getBooleanValue());
}
@Test
public void testParseFormula() throws FormulaParsingException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(new Formula("=A1 + 1"), parser.parseFormula(new Formula("=a1 +1")));
}
@Test
public void testTransformFormula() throws FormulaParsingException {
JavaCCFormulaParserImpl parser = getParser();
// cells
Assert.assertEquals(
new Formula("=A1 + 1"),
parser.transformFormula(new Formula("=b2 +1"),
ReferenceTransformers.shiftCell(new CellReference("D2"), new CellReference("C1"))));
Assert.assertEquals(
new Formula("=#REF + 1"),
parser.transformFormula(new Formula("=b2 +1"),
ReferenceTransformers.shiftCell(new CellReference("D2"), new CellReference("B1"))));
// areas
Assert.assertEquals(
new Formula("=SUM(A1:B1)"),
parser.transformFormula(new Formula("=sum(b2:c2)"),
ReferenceTransformers.shiftCell(new CellReference("D2"), new CellReference("C1"))));
Assert.assertEquals(
new Formula("=SUM(#REF)"),
parser.transformFormula(new Formula("=sum(b2:c2)"),
ReferenceTransformers.shiftCell(new CellReference("D2"), new CellReference("B1"))));
// delete row
Assert.assertEquals(new Formula("=SUM(A2:A3)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.deleteRow(2)));
Assert.assertEquals(new Formula("=SUM(A2:A3)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.deleteRow(1)));
Assert.assertEquals(new Formula("=SUM(A1:A3)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.deleteRow(0)));
Assert.assertEquals(new Formula("=SUM(A2:A4)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.deleteRow(5)));
Assert.assertEquals(new Formula("=#REF + 1"),
parser.transformFormula(new Formula("=a2 + 1"), ReferenceTransformers.deleteRow(1)));
// insert row
Assert.assertEquals(new Formula("=SUM(A2:A5)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.insertRow(2)));
Assert.assertEquals(new Formula("=SUM(A3:A5)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.insertRow(1)));
Assert.assertEquals(new Formula("=SUM(A2:A4)"),
parser.transformFormula(new Formula("=sum(A2:A4)"), ReferenceTransformers.insertRow(5)));
// delete col
Assert.assertEquals(new Formula("=SUM(B1:C1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.deleteColumn(2)));
Assert.assertEquals(new Formula("=SUM(B1:C1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.deleteColumn(1)));
Assert.assertEquals(new Formula("=SUM(A1:C1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.deleteColumn(0)));
Assert.assertEquals(new Formula("=SUM(B1:D1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.deleteColumn(5)));
Assert.assertEquals(new Formula("=#REF + 1"),
parser.transformFormula(new Formula("=b1 + 1"), ReferenceTransformers.deleteColumn(1)));
// insert col
Assert.assertEquals(new Formula("=SUM(B1:E1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.insertColumn(2)));
Assert.assertEquals(new Formula("=SUM(C1:E1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.insertColumn(1)));
Assert.assertEquals(new Formula("=SUM(B1:D1)"),
parser.transformFormula(new Formula("=sum(B1:D1)"), ReferenceTransformers.insertColumn(5)));
}
@Test
public void testErrorParseFormula() throws FormulaParsingException {
JavaCCFormulaParserImpl parser = getParser();
try {
parser.parseFormula(null);
Assert.fail("No exception thrown");
} catch (Exception e) {
Assert.assertEquals(NullPointerException.class, e.getClass());
}
try {
parser.parseFormula(new Formula("=A + 4 -"));
Assert.fail("No exception thrown");
} catch (Exception e) {
Assert.assertEquals(FormulaParsingException.class, e.getClass());
}
}
@Test
public void testMatchFunction() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
ISheet sheet = SheetUtils.sheetWithCell("A1", "2", "A2", 3, "B3", 2.5);
IGenericValue value = parser.executeFormula(new Formula("=MATCH(3, A1:A2, 0)"), sheet, null);
Assert.assertNotNull(value);
Assert.assertEquals(GenericValueType.NUMBER, value.getValueType());
Assert.assertEquals(2, value.getNumberValue(), 0.01);
}
@Test
public void testUnaryOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(-1.0, parser.executeFormula(new Formula("=-a1"), SheetUtils.sheetWithCell("A1", "1"), null)
.getNumberValue());
Assert.assertEquals(1.0, parser.executeFormula(new Formula("=+a1"), SheetUtils.sheetWithCell("A1", "1"), null)
.getNumberValue());
Assert.assertEquals("=+A1", parser.parseFormula(new Formula("=+a1")).getFormula());
}
@Test
public void testPercentOperations() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(13.0,
parser.executeFormula(new Formula("=10 * a1 % + 3"), SheetUtils.sheetWithCell("A1", 100), null)
.getNumberValue());
Assert.assertEquals("=10 * A1% + 3", parser.parseFormula(new Formula("=10 * a1 % + 3")).getFormula());
}
@Test
public void testVector() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals("=SUM(A:A)", parser.parseFormula(new Formula("=sum(A:A)")).getFormula());
Assert.assertEquals("=SUM(3:3)", parser.parseFormula(new Formula("=sum(3:3)")).getFormula());
}
@Test
public void testBoolean() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(false,
parser.executeFormula(new Formula("=a1 != true"), SheetUtils.sheetWithCell("A1", true), null)
.getBooleanValue().booleanValue());
Assert.assertEquals(true,
parser.executeFormula(new Formula("=a1 != false"), SheetUtils.sheetWithCell("A1", true), null)
.getBooleanValue().booleanValue());
Assert.assertEquals("=A1 != true", parser.parseFormula(new Formula("=a1 != true")).getFormula());
}
@Test
public void testParanthesis() throws FormulaParsingException, AlreadyExistsException, StorageException,
NotFoundException {
JavaCCFormulaParserImpl parser = getParser();
Assert.assertEquals(1030.0,
parser.executeFormula(new Formula("=10 * (a1 + 3)"), SheetUtils.sheetWithCell("A1", 100), null)
.getNumberValue());
Assert.assertEquals("=10 * (A1 + 3)", parser.parseFormula(new Formula("=10 * (a1 + 3)")).getFormula());
}
}