Java tutorial
/******************************************************************************* * * 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()); } }