Spreadsheet FixedCell reference ($a$1, $a1, a$1 etc...) issue in ANTLR grammar

Question

I'm working on editing features for a web spreadsheet where I need to identify the cell references, range references and then coloring them as per my requirement. I'm able to identify and color the cell references but failed to identify the fixed cell references ($a$1, $a1, a$1 etc...) as it is throwing an error after entering dollar ('$') symbol.Can anyone help me on this.

When I try to enter the cell reference as "$A$1", it is throwing no viable exception after entering second dollar symbol for row reference and i'm not sure why. How to read the "$" symbol to recognize the fixed cell reference?

FYI, I've extended the grammar found @ antlr3 - Generating a Parse Tree

I'm using the grammar for cell/range reference as below

CELLREFERENCE
    :  '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+
    ;

RANGEREFERENCE
    : '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+ ':' '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+
    ;

This will work for identifying the colon,

RANGEREFERENCE : CELLREFERENCE ((':' CELLREFERENCE)=> ':' CELLREFERENCE)?;

Still not sure about the '$' issue in cell reference.

Please find the complete grammar here:

grammar Excel;

options {
  output=AST;
  language=JavaScript;
}

tokens {
    // define pseudo-operations
    FUNC;
    CALL;
    NEGATE;
}

parse
  :  exp EOF -> exp
  ;

exp
  :  orExp
  ;

orExp
  :  andExp (OR^ andExp)*
  ;

andExp
  :  eqExp (AND^ eqExp)*
  ;

eqExp
    : relExp (( EQUALS | NOTEQUALS)^ relExp)*
    ;

relExp
    : addExp ( (LT^|LTEQ^|GT^|GTEQ^) addExp)*
    ;

addExp
    : multExp ( (PLUS^| MINUS^) multExp)*
    ;
multExp 
    : unaryExp (( MULT^ | DIV^ | MOD^ |POW^| IS^) unaryExp)*
    ;

unaryExp
  :  NOT atom -> ^(NOT atom)
  |  MINUS atom -> ^(NEGATE atom)
  |  ASSIGN atom -> ^(ASSIGN atom)
  |  atom
  ;

atom
  :  TRUE
  |  FALSE
  |  INT
  |  FLOAT
  |  function
  |  CELLREFERENCE
  |  RANGEREFERENCE
  |  '(' exp ')' -> exp
  ;

POW : '^';
DIV : '/';
MOD : '%';
MULT : '*';
PLUS : '+';
MINUS : '-';
LT   : '<';
LTEQ : '<=';
GT   : '>';
GTEQ : '>=';
EQUALS : '==';
ASSIGN: '=';
NOTEQUALS : '<>';
INT    : '0'..'9'+;
FLOAT  : ('0'..'9')* '.' ('0'..'9')+;
OR     : 'or' ;
AND    : 'and' ;
IS     : 'is' ;
NOT    : 'not' ;
TRUE   : 'true' ;
FALSE  : 'false' ;

function
    :   IDENT '(' ( exp (',' exp)* )? ')' -> ^(FUNC IDENT (exp)*)
    ;
CELLREFERENCE
    :  '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+
    ;

RANGEREFERENCE
    : '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+ ':' '$'?('a'..'z'|'A'..'Z')+'$'?('0'..'9')+
    ;

IDENT
    :   ('a'..'z' | 'A'..'Z') ('a'..'z' | 'A'..'Z' |'0'..'9')*
    ;   

SPACE  : (' ' | '\t' | '\r' | '\n') {$channel=HIDDEN;} ;

ERRCHAR : Err =. { /*console.log($Err);*/ } ;

I was able fix the colon issue by changing the code for RANGEREFERENCE : CELLREFERENCE ((':' CELLREFERENCE)=> ':' CELLREFERENCE)?; But, still not able to resolve the problem with '$' for cell reference. — RamSafari, Jan 17 '13 at 06:31

Bart Kiers · Answer 1 · 2013-01-18T06:23:48.680

I cannot reproduce it.

When I copy-paste your original grammar (without the predicate ( ... )=> !), and change the parse rule into this:

parse
  :  (t=. {console.log('type:', $t.type, 'text:', $t.text);})* EOF
     //exp EOF -> exp
  ;

Then I generate a lexer/parser:

java -cp antlr-3.3-complete.jar org.antlr.Tool Excel.g

and use the test html file:

<html>
  <head>
    <script type="text/javascript" src="antlr3-all-min.js"></script>
    <script type="text/javascript" src="ExcelLexer.js"></script>
    <script type="text/javascript" src="ExcelParser.js"></script>    
    <script type="text/javascript">

    function init() {
      var evalButton = document.getElementById("eval");
      evalButton.onclick = evalExpression;
    }

    function evalExpression() {
      document.getElementById("answer").innerHTML = "";
      var Excelression = document.getElementById("src").value;
      if(Excelression) {
        var lexer = new ExcelLexer(new org.antlr.runtime.ANTLRStringStream(Excelression));
        var tokens = new org.antlr.runtime.CommonTokenStream(lexer);
        var parser = new ExcelParser(tokens);
        parser.parse().getTree();
      }
    }

    </script>
  </head>
  <body onload="init()">
    <input id="src" type="text" size="35" />
    <button id="eval">evaluate</button>
    <div id="answer"></div>
  </body>
</html>

and then enter the input $a$1 $a$1:$a$9 into the text field, I see the following output being printed to my Chrome console:

type: 28 text: $a$1
type: 29 text: $a$1:$a$9

where the Excel.tokens file contains:

CELLREFERENCE=28
RANGEREFERENCE=29

Thanks for your time. I have explained this in detail below. You can go through it at your leisure and let me know the mistakes in the grammar. ~Ramesh — RamSafari, Jan 18 '13 at 07:32

Spreadsheet FixedCell reference ($a$1, $a1, a$1 etc...) issue in ANTLR grammar

1 Answers1