Compiler Errors

AFC does its best to give meaningful error messages which clearly identify both the problem and its source. This document shows the most typical errors, why they occur, and what to do about them.

Unreadable .xls Files

AFC uses JExcelAPI to read files in Excel’s .xls format. It may happen that JExcelAPI fails to read the file before AFC has a chance to look at its contents.

Hint

It sometimes helps to start with a new file, and then selectively copy only the necessary formulas and values over from the old, unreadable one.

If JExcelAPI has managed to read the file, there may still be elements within it that AFC cannot handle. The most typical case is that a particular Excel function is not supported at all, meaning AFC cannot even parse expressions containing it. For example, the INFO function cannot be parsed by AFC. The function PROPER, while it can be parsed, is still not supported yet. However, AFC is smart enough to accept spreadsheets containing unsupported functions, provided they are not referenced by any output cell.

Hint

You may sometimes be able to substitute a combination of supported functions for an unsupported one.

Example

The following spreadsheet contains a cell which uses the INFO function in its formula, another cell which references the former cell, and a third cell that whose value does not involve the INFO function at all:

	A	B
1	#N/A =1.0+INFO(B1) (Info)	memused
2	#N/A =A1*2.0 (ReferencesInfo)
3	3 =1.0+2.0 (Independent)
4	Text =ASC(B4) (Unsupported)	Text

Binding an output cell to the first cell containing the INFO function results in:

EngineBuilder builder = builderForComputationOfCellNamed( "Info" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedExpression e) {
  if (getSpreadsheetExtension().equals( ".xls" )) {
    String err = "Unsupported function INFO encountered in expression 1.0+INFO( <<? B1); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1.";
    assertEquals( err, e.getMessage() );
  }
  else if (getSpreadsheetExtension().equals( ".xlsx" )) {
    String err = "Unsupported function INFO encountered in expression 1+INFO( <<? B1); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1.";
    assertEquals( err, e.getMessage() );
  }
  else {
    String err = "Unsupported function INFO encountered in expression 1+INFO( <<? [.B1]); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1.";
    assertEquals( err, e.getMessage() );
  }
}

Note that the failure only occurs when comiling the engine. Not when loading the spreadsheet, and not when binding cells. This is due to AFC’s lazy expression parser which only parses those expressions in the spreadsheet that are actually referenced by a bound output cell.

Binding the output cell to the second cell, which references the first cell, results in the same error, but note that the message identifies A2 as the source of the reference:

EngineBuilder builder = builderForComputationOfCellNamed( "ReferencesInfo" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedExpression e) {
  if (getSpreadsheetExtension().equals( ".xls" )) {
    String err = "Unsupported function INFO encountered in expression 1.0+INFO( <<? B1); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1."
        + "\nReferenced by cell Sheet1!A2.";
    assertEquals( err, e.getMessage() );
  }
  else if (getSpreadsheetExtension().equals( ".xlsx" )) {
    String err = "Unsupported function INFO encountered in expression 1+INFO( <<? B1); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1."
        + "\nReferenced by cell Sheet1!A2.";
    assertEquals( err, e.getMessage() );
  }
  else {
    String err = "Unsupported function INFO encountered in expression 1+INFO( <<? [.B1]); error location indicated by <<?."
        + "\nCell containing expression is Sheet1!A1."
        + "\nReferenced by cell Sheet1!A2.";
    assertEquals( err, e.getMessage() );
  }
}

However, binding to the third cell, which is independent of the first cell containing the INFO function, works ok:

EngineBuilder builder = builderForComputationOfCellNamed( "Independent" );
SaveableEngine engine = builder.compile();
MyFactory factory = (MyFactory) engine.getComputationFactory();
MyComputation computation = factory.newComputation( new MyInputs() );
assertEquals( 3, computation.result() );

The final example shows the error message for a parseable, but unsupported function (PROPER):

EngineBuilder builder = builderForComputationOfCellNamed( "Unsupported" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedExpression e) {
  String err = "Function ASC is not supported for double engines."
      + "\nIn expression  >> ASC( Sheet1!B4 ) << ; error location indicated by >>..<<."
      + "\nCell containing expression is Sheet1!A4."
      + "\nReferenced by cell Sheet1!A4.";
  assertEquals( err, e.getMessage() );
}

Unsupported Function Variants

You may also encounter functions that are supported, but not with all possible variants of argument lists. For example, AFC currently does not support the MATCH function when its last argument (the match type) is bound to an input variable.

Hint

You may sometimes be able to work around such limitations by using IF.

Example

Consider the following spreadsheet:

	A	B	C	D	E	F
1	3 =1.0+MATCH(B1,C1:E1,F1) (Bad)	20 (LookedFor)	10	20	30	4 (Type)
2	6 =A1*2.0 (ReferencesBad)

If we bind the output to Bad and make Type an input, then AFC will throw an error:

EngineBuilder builder = builderForComputationOfCellNamed( "Bad" );
bindInputNamed( builder, "Type" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedExpression e) {
  String err = "The last argument to MATCH, the match type, must be constant, but is MyInputs.value()."
      + "\nIn expression (1.0 + MATCH( Sheet1!B1, Sheet1!C1:E1,  >> Sheet1!F1 <<  )); error location indicated by >>..<<."
      + "\nCell containing expression is Sheet1!A1." + "\nReferenced by cell Sheet1!A1.";
  assertEquals( err, e.getMessage() );
}

The same happens if we bind the output to ReferencesBad and make Type an input, but again notice how the referencing cell is identified:

EngineBuilder builder = builderForComputationOfCellNamed( "ReferencesBad" );
bindInputNamed( builder, "Type" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedExpression e) {
  String err = "The last argument to MATCH, the match type, must be constant, but is MyInputs.value()."
      + "\nIn expression (1.0 + MATCH( Sheet1!B1, Sheet1!C1:E1,  >> Sheet1!F1 <<  )); error location indicated by >>..<<."
      + "\nCell containing expression is Sheet1!A1." + "\nReferenced by cell Sheet1!A2.";
  assertEquals( err, e.getMessage() );
}

Unsupported Data Type Conversions

AFC supports a wide range of automatic data type conversions when obtaining and returning values. Some, however, do not make sense. AFC will not, for example, automatically try to convert string-typed expressions to numeric return values.

Hint

Change the data types in your interface to AFC to fix such problems. If this interface is a given (ie. dictated by some other component), then you may have to create a fa�ade on top of an internal interface used for AFC.

Example 1

Consider the following sheet:

	A	B
1	hello world! =LOWER(B1) (stringOutput)	Hello WORLD! (stringInput)
2	60 =B2*2.0 (numOutput)	30 (numInput)

If we bind the cell stringOutput to an int method,

public static interface MyComputation
{
  public int result();
}

we get the following exception:

EngineBuilder builder = builderForComputationOfCellNamed( "stringOutput" );
bindInputNamed( builder, "stringInput" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedDataType e) {
  String err = "Cannot convert from a string to a int."
      + "\nCaused by return type of input 'public abstract int org.formulacompiler.tutorials.ErrorUnsupportedConversionToOutput$MyComputation.result()'."
      + "\nCell containing expression is Sheet1!A1."
      + "\nReferenced by cell Sheet1!A1.";
  assertEquals( err, e.getMessage() );
}

(Note that binding stringInput to an int works as AFC does automatically convert from int to String.)

Example 2

If we bind the cell numInput to a String method,

public static class MyInputs
{
  public String value()
  {
    return "Hello, world!";
  }
}

we get the following exception:

EngineBuilder builder = builderForComputationOfCellNamed( "numOutput" );
bindInputNamed( builder, "numInput" );
try {
  builder.compile();
  fail();
}
catch (CompilerException.UnsupportedDataType e) {
  String err = "Cannot convert from a java.lang.String to a double."
      + "\nCaused by return type of input 'public java.lang.String org.formulacompiler.tutorials.ErrorUnsupportedConversionFromInput$MyInputs.value()'."
      + "\nCell containing expression is Sheet1!B2." + "\nReferenced by cell Sheet1!B2.";
  assertEquals( err, e.getMessage() );
}

(Note that binding numOutput to an int again works as AFC does automatically convert from int to String.)

Improper Inner Section References

AFC supports repeating sections of dynamic width or height. These can even be nested. However, outer sections may only reference inner sections as range arguments to functions – otherwise it would not be clear which element is meant to be accessed. Inner sections may, of course, reference outer cells as simple values. In addition, the range arguments must always cover the full variable extent (width or height) of the inner section.

Hint

Do reference a section cell directly. Use an aggregator like SUM() over the entire section column (for vertically repeating sections) or row (for horizontally repeating sections) instead.

Examples

Consider the following sheet:

	A	B	C
1	Vertically repeating section:	Name	Value
2		One	1
3		Two	2
4		Three	3
5
6	This works:
7	Aggregate entire column	6 =SUM(C2:C4) (GoodRef)
8
9	This is an error:
10	No aggregation	1 =C2 (BadRef)
11	Not entire column (end bad)	3 =SUM(C2:C3) (BadRange1)
12	Not entire column (start bad)	5 =SUM(C3:C4) (BadRange2)
13	Not entire column (both bad)	2 =SUM(C3:C3) (BadRange3)
14	Not entire column (single cell)	2 =SUM(C3) (BadRange4)

B2:B4 (Name)
B2:C4 (Section)
C2:C4 (Value)

The range Section is bound as a repeating section, as follows:

Range range = sheet.getRange( "Section" );
Section section = root.defineRepeatingSection( range, Orientation.REPEAT_ROWS, "section", MyElement.class, null,
    null );
section.defineInputCell( sheet.getRange( "Name" ).getTopLeft(), "name" );
section.defineInputCell( sheet.getRange( "Value" ).getTopLeft(), "value" );

This works

Binding the cell GoodRef yields

EngineBuilder builder = builderForComputationOfCellNamed( "GoodRef" );
Engine engine = builder.compile();
MyFactory factory = (MyFactory) engine.getComputationFactory();
MyComputation computation = factory.newComputation( new MyInputs() );
assertEquals( 4 + 5 + 6 + 7, computation.result() );

Bad cell ref

If, on the other hand, we bind the cell BadRef to an output, we get

EngineBuilder builder = builderForComputationOfCellNamed( "BadRef" );
try {
  builder.compile();
  fail();
}
catch (SpreadsheetException.SectionExtentNotCovered e) {
  String err = "Sheet1!C2 does not fully cover the height of its parent section Sheet1!B2:C4 (which iterates section()).\n"
      + "Referenced by cell Sheet1!B10.";
  assertEquals( err, e.getMessage() );
}

Bad ranges

Binding the cells with range specifications that don’t fully cover the height of the section also results in an error:

EngineBuilder builder = builderForComputationOfCellNamed( "BadRange1" );
try {
  builder.compile();
  fail();
}
catch (SpreadsheetException.SectionExtentNotCovered e) {
  String err = "Sheet1!C2:C3 does not fully cover the height of its parent section Sheet1!B2:C4 (which iterates section()).\n"
      + "Referenced by cell Sheet1!B11.";
  assertEquals( err, e.getMessage() );
}

EngineBuilder builder = builderForComputationOfCellNamed( "BadRange2" );
try {
  builder.compile();
  fail();
}
catch (SpreadsheetException.SectionExtentNotCovered e) {
  String err = "Sheet1!C3:C4 does not fully cover the height of its parent section Sheet1!B2:C4 (which iterates section()).\n"
      + "Referenced by cell Sheet1!B12.";
  assertEquals( err, e.getMessage() );
}

EngineBuilder builder = builderForComputationOfCellNamed( "BadRange3" );
try {
  builder.compile();
  fail();
}
catch (SpreadsheetException.SectionExtentNotCovered e) {
  String err = "Sheet1!C3 does not fully cover the height of its parent section Sheet1!B2:C4 (which iterates section()).\n"
      + "Referenced by cell Sheet1!B13.";
  assertEquals( err, e.getMessage() );
}

Bad cell sum

Using a sum over a single cell in the section does not work either:

EngineBuilder builder = builderForComputationOfCellNamed( "BadRange4" );
try {
  builder.compile();
  fail();
}
catch (SpreadsheetException.SectionExtentNotCovered e) {
  String err = "Sheet1!C3 does not fully cover the height of its parent section Sheet1!B2:C4 (which iterates section()).\n"
      + "Referenced by cell Sheet1!B14.";
  assertEquals( err, e.getMessage() );
}

Compiler Errors

Unreadable .xls Files

Hint

Unsupported Functions

Hint

Example

Unsupported Function Variants

Hint

Example

Unsupported Data Type Conversions

Hint

Example 1

Example 2

Improper Inner Section References

Hint

Examples

This works

Bad cell ref

Bad ranges

Bad cell sum