spec/formula_builder_spec.rb in rubyfromexcel-0.0.10 vs spec/formula_builder_spec.rb in rubyfromexcel-0.0.13
- old
+ new
@@ -33,15 +33,16 @@
it "Should convert powers to their ruby equivalent" do
ruby_for("1^12").should == "1.0**12.0"
end
- it "Should join strings together, adding to_s where appropriate" do
+ it "Should join strings together, adding to_s " do
ruby_for('"Hello"&"world"').should == '"Hello"+"world"'
- ruby_for('"Hello "&A1').should == '"Hello "+a1.to_s'
- ruby_for('AA1&"GW"').should == 'aa1.to_s+"GW"'
- ruby_for('"GW"&ISERR($AA$1)').should == '"GW"+iserr(aa1).to_s'
+ ruby_for('"Hello "&A1').should == '"Hello "+(a1).to_s'
+ ruby_for('AA1&"GW"').should == '(aa1).to_s+"GW"'
+ ruby_for('"GW"&ISERR($AA$1)').should == '"GW"+(iserr(aa1)).to_s'
+ ruby_for('"GW"&23/15').should == '"GW"+(23.0/15.0).to_s'
end
it "Should convert area references to a(start,end) functions" do
ruby_for('$A$1:BZ$2000').should == "a('a1','bz2000')"
end
@@ -53,16 +54,21 @@
it "should convert row references to r(start,end) functions" do
ruby_for('1:1000').should == 'r(1,1000)'
end
it "should leave strings as they are, even if they look like formulas" do
- ruby_for('"A1+3*2"&$A3').should == '"A1+3*2"+a3.to_s'
+ ruby_for('"A1+3*2"&$A3').should == '"A1+3*2"+(a3).to_s'
end
it "should properly escape strings where necessary" do
ruby_for(%q{"String with 'quotes' in it"}).should == '"String with \'quotes\' in it"'
end
+
+ it "should convert double sets of quotes (\"\") into single sets (\")" do
+ ruby_for('"A ""quote"""').should == '"A \"quote\""'
+ end
+
it "should convert sheet names to ruby methods" do
ruby_for("asheetname!A3:B20").should == "sheet1.a('a3','b20')"
ruby_for("'a long sheet name with spaces'!A3:B20").should == "sheet2.a('a3','b20')"
@@ -96,32 +102,47 @@
@builder.formula_cell = nil
ruby_for("Control!#REF!").should == ":name"
end
it "should convert table names to references" do
- Table.new(mock(:worksheet,:to_s => 'sheet1'),'Vectors','a1:c41',['ColA','Description','ColC'],1)
+ Table.new(mock(:worksheet,:name => 'sheet1',:to_s => 'sheet1'),'Vectors','a1:c41',['ColA','Description','ColC'],1)
ruby_for("Vectors[Description]").should == "sheet1.a('b2','b40')"
ruby_for("Vectors[#all]").should == "sheet1.a('a1','c41')"
ruby_for("Vectors[#totals]").should == "sheet1.a('a41','c41')"
ruby_for("Vectors[[#totals],[Description]]").should == "sheet1.b41"
@builder.formula_cell = mock(:cell,:reference => Reference.new('f30'))
ruby_for("Vectors[[#This Row],[Description]]").should == "sheet1.b30"
ruby_for("Vectors[[#This Row],[Description]:[ColC]]").should == "sheet1.a('b30','c30')"
end
+ it "should convert table names inside indirects" do
+ workbook = mock(:workbook, :named_references => {'named_cell' => 'sheet2.z10', 'named_cell2' => "sheet2.a('z10','ab10')"})
+ worksheet1 = mock(:worksheet,:name => "sheet1", :to_s => 'sheet1', :workbook => workbook, :named_references => {'named_cell' => 'sheet1.a1','this_year' => 'sheet1.a1'})
+ worksheet2 = mock(:worksheet,:name => "sheet2", :to_s => 'sheet2', :workbook => workbook, :named_references => {})
+ workbook.stub!(:worksheets => {'sheet1' => worksheet1, 'sheet2' => worksheet2 })
+ worksheet1.should_receive(:cell).with('c102').twice.and_return(mock(:cell,:value_for_including => 'XVI.a',:can_be_replaced_with_value? => true))
+ worksheet1.should_receive(:cell).with('a1').and_return(mock(:cell,:value_for_including => '2050',:can_be_replaced_with_value? => true))
+ cell = mock(:cell,:worksheet => worksheet1, :reference => Reference.new('c30',worksheet1))
+ @builder.formula_cell = cell
+ Table.new(worksheet1,'XVI.a.Outputs','a1:c41',['2050','Description','Vector'],1)
+
+ ruby_for('INDIRECT($C102&".Outputs["&this.Year&"]")').should == "sheet1.a30"
+ ruby_for('INDIRECT($C102&".Outputs[Vector]")').should == "sheet1.c30"
+ end
+
it "should ignore external references, assuming that they point to an internal equivalent" do
formula_with_external = "INDEX([1]!Modules[Module], MATCH($C5, [1]!Modules[Code], 0))"
- Table.new(mock(:worksheet,:to_s => 'sheet1'),'Modules','a1:c41',['Module','Code','ColC'],1)
+ Table.new(mock(:worksheet,:to_s => 'sheet1',:name => "sheet1",),'Modules','a1:c41',['Module','Code','ColC'],1)
ruby_for(formula_with_external).should == "index(sheet1.a('a2','a40'),match(c5,sheet1.a('b2','b40'),0.0))"
end
it "should convert unkown table names to :ref" do
ruby_for("Unknown[Not likely]").should == ":ref"
end
it "should convert unqualified table names to references" do
- sheet = mock(:worksheet,:to_s => 'sheet1')
+ sheet = mock(:worksheet,:name => "sheet1",:to_s => 'sheet1')
Table.new(sheet,'Vectors','a1:c41',['ColA','Description','ColC'],1)
@builder.formula_cell = mock(:cell,:reference => Reference.new('c30',sheet), :worksheet => sheet)
ruby_for("[Description]").should == "sheet1.b30"
ruby_for("[#all]").should == "sheet1.a('a1','c41')"
ruby_for("[#headers]").should == "sheet1.c1"
@@ -154,15 +175,15 @@
worksheet.should_receive(:workbook).and_return(workbook)
cell = mock(:cell,:value => 'ASD',:can_be_replaced_with_value? => false)
worksheet.should_receive(:cell).with('a1').and_return(cell)
workbook.should_receive(:indirects_used=).with(true)
@builder.formula_cell = mock(:cell,:reference => Reference.new('f30'),:worksheet => worksheet)
- ruby_for('INDIRECT("ONE"&A1)').should == 'indirect("ONE"+a1.to_s,\'f30\')'
+ ruby_for('INDIRECT("ONE"&A1)').should == 'indirect("ONE"+(a1).to_s,\'f30\')'
end
it "should attempt to interpret indirect functions where that is appropriate" do
- worksheet = mock(:worksheet, :to_s => 'sheet1')
+ worksheet = mock(:worksheet, :to_s => 'sheet1',:name => "sheet1")
@builder.formula_cell = mock(:cell,:reference => Reference.new('f30',worksheet),:worksheet => worksheet)
ruby_for('INDIRECT("A1")').should == "a1"
ruby_for('INDIRECT("A"&"1")').should == "a1"
ruby_for('INDIRECT("A"&1)').should == "a1"
@@ -199,12 +220,12 @@
worksheet.should_receive(:cell).with('c120').and_return(cell)
ruby_for('INDIRECT($C120&".Outputs[Vector]")').should == ":ref"
end
- it "should convert comparators into single expressions so that they work in ifs" do
- ruby_for('IF(A1=3,"A","B")').should == 'excel_if(a1==3.0,"A","B")'
+ it "should convert comparators into a function, so that can cope with excels version of equality" do
+ ruby_for('IF(A1=3,"A","B")').should == 'excel_if(excel_comparison(a1,"==",3.0),"A","B")'
end
it "should convert complex formulas" do
# SheetNames.instance['DUKES 09 (2.5)'] = 'sheet100'
# SheetNames.instance['DUKES 09 (1.2)'] = 'sheet101'
@@ -218,26 +239,27 @@
it "should replace MATCH() with its answer where it depends only on cells that can be replaced with their values" do
worksheet = mock(:worksheet, :to_s => 'sheet1')
a1 = mock(:cell,:reference => Reference.new('a1',worksheet),:worksheet => worksheet,:value_for_including => 'A', :can_be_replaced_with_value? => true)
a2 = mock(:cell,:reference => Reference.new('a2',worksheet),:worksheet => worksheet,:value_for_including => 'A', :can_be_replaced_with_value? => true)
a3 = mock(:cell,:reference => Reference.new('a3',worksheet),:worksheet => worksheet,:value_for_including => 'B', :can_be_replaced_with_value? => true)
- worksheet.should_receive(:cell).with('a1').and_return(a1)
- worksheet.should_receive(:cell).with('a2').and_return(a2)
- worksheet.should_receive(:cell).with('a3').and_return(a3)
+ worksheet.should_receive(:cell).with('a1').exactly(3).times.and_return(a1)
+ worksheet.should_receive(:cell).with('a2').exactly(2).times.and_return(a2)
+ worksheet.should_receive(:cell).with('a3').exactly(2).times.and_return(a3)
@builder.formula_cell = mock(:cell,:reference => Reference.new('f30',worksheet),:worksheet => worksheet)
ruby_for('MATCH("A",A1:A3)').should == "2.0"
ruby_for('MATCH("X",A1:A3,FALSE)').should == "na"
+ ruby_for('MATCH("A",A1:A3,FALSE)').should == "1.0"
end
it "should replace INDEX() with a cell reference where it depends only on cells that can be replaced with their values" do
worksheet = mock(:worksheet, :to_s => 'sheet1')
@builder.formula_cell = mock(:cell,:reference => Reference.new('f30',worksheet),:worksheet => worksheet)
ruby_for('INDEX(A1:A3,3.0)').should == "sheet1.a3"
ruby_for('INDEX(A1:A3,2.0,1.0)').should == "sheet1.a2"
end
it "should put multiple items as single arguments in a function" do
- ruby_for("IF(A23>=(1.0+B38),1.0,2.0)").should == "excel_if(a23>=(1.0+b38),1.0,2.0)"
+ ruby_for("IF(A23>=(1.0+B38),1.0,2.0)").should == "excel_if(excel_comparison(a23,\">=\",(1.0+b38)),1.0,2.0)"
ruby_for("MAX(F60+(G$59-F$59)*G38,0)").should == "max(f60+(g59-f59)*g38,0.0)"
end
it "should convert formulas with null arguments, replacing the null with 0.0" do
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)"