formula_peg_spec.rb in rubyfromexcel-0.0.5

- old
+ new

@@ -50,10 +50,11 @@
   it "returns references to other sheets" do
     Formula.parse('sheet1!$A$1').to_ast.should == [:formula,[:sheet_reference,'sheet1',[:cell,'$A$1']]]    
     Formula.parse('sheet1!$A$1:$Z$1').to_ast.should == [:formula,[:sheet_reference,'sheet1',[:area,'$A$1','$Z$1']]]
     Formula.parse('sheet1!$1:$1000').to_ast.should == [:formula,[:sheet_reference,'sheet1',[:row_range,'$1','$1000']]]
     Formula.parse('sheet1!$C:$AZ').to_ast.should == [:formula,[:sheet_reference,'sheet1',[:column_range,'$C','$AZ']]]
+    Formula.parse('Control!#REF!').to_ast.should == [:formula,[:sheet_reference,'Control',[:named_reference,'#REF!']]]
   end
   
   it "returns references to other sheets with extended names" do
     Formula.parse("'sheet 1'!$A$1").to_ast.should == [:formula,[:quoted_sheet_reference,'sheet 1',[:cell,'$A$1']]]    
     Formula.parse("'sheet 1'!$A$1:$Z$1").to_ast.should == [:formula,[:quoted_sheet_reference,'sheet 1',[:area,'$A$1','$Z$1']]]
@@ -158,8 +159,23 @@
     Formula.parse(spaced2).to_ast.should == [:formula, [:string_join, [:string, "per "],[:named_reference, "Preferences.EnergyUnits"]]]
     spaced3 = ' 0.00403/$F$76'
     Formula.parse(spaced3).to_ast.should == [:formula, [:arithmetic, [:number, "0.00403"], [:operator, "/"], [:cell, "$F$76"]]]
     spaced4 = "SUMIFS(INDEX($G$62:$J$73, , MATCH($E$11, $G$61:$J$61, 0)), $C$62:$C$73, $C195, $D$62:$D$73, $D195)"
     Formula.parse(spaced4).to_ast.should == [:formula, [:function, "SUMIFS", [:function, "INDEX", [:area, "$G$62", "$J$73"], [:null], [:function, "MATCH", [:cell, "$E$11"], [:area, "$G$61", "$J$61"], [:number, "0"]]], [:area, "$C$62", "$C$73"], [:cell, "$C195"], [:area, "$D$62", "$D$73"], [:cell, "$D195"]]]
+  end
+  
+  it "returns formulas that use table range references" do
+    table_with_range = "SUM(EF[[#This Row],[CO2]:[N2O]])"
+    Formula.parse(table_with_range).to_ast.should == [:formula, [:function, "SUM", [:table_reference, "EF", "[#This Row],[CO2]:[N2O]"]]]
+  end
+  
+  it "parses external references" do
+    formula_with_external = "INDEX([1]!Modules[Module], MATCH($C5, [1]!Modules[Code], 0))"
+    Formula.parse(formula_with_external).to_ast.should == [:formula, [:function, "INDEX", [:external_reference, "[1]!", [:table_reference, "Modules", "Module"]], [:function, "MATCH", [:cell, "$C5"], [:external_reference, "[1]!", [:table_reference, "Modules", "Code"]], [:number, "0"]]]]
+  end
+  
+  it "parses buggy references" do
+    Formula.parse("'Calcs'!a17").to_ast.should == [:formula,[:quoted_sheet_reference,'Calcs',[:cell,'a17']]]
+    Formula.parse("'Calcs'!aone2").to_ast.should == [:formula,[:quoted_sheet_reference,'Calcs',[:named_reference,'aone2']]]
   end
   
 end
\ No newline at end of file