public void MakePreview(BBAddIn addIn)
{
addIn.bbColorSmells.decolorCells(transformationCells);
if (addIn.theRibbon.dropDown1.Items.Count > 0) //if we have transformations available
{
FSharpTransformationRule T = AllTransformations.FirstOrDefault(x => x.Name == addIn.theRibbon.dropDown1.SelectedItem.Label);
Excel.Range R = (addIn.Application.Selection);
string formula = BBTransformations.RemoveFirstSymbol((string)R[1, 1].Formula);
addIn.theRibbon.Preview.Text = T.ApplyOn(formula);
addIn.theRibbon.valuePreview.Text = getValue((ExcelRaw.Range)R[1, 1].UnderlyingObject, addIn.theRibbon.Preview.Text);
addIn.theRibbon.valuePreview.ShowImage = (addIn.theRibbon.valuePreview.Text != R[1, 1].Value.ToString());
if (R.Count == 1)
{
foreach (Excel.Worksheet worksheet in addIn.Application.Worksheets)
{
applyInRange(T, (ExcelRaw.Range)worksheet.UsedRange.UnderlyingObject, true);
}
}
else
{
applyInRange(T, addIn.Application.Selection, true);
}
}
}
public void SUM_in_argument_should_not_match_with_cell()
{
FSharpTransformationRule T6 = new FSharpTransformationRule();
T6.from = T6.ParseToTree("SUM([c])+[e]");
Assert.AreEqual(false, T6.CanBeAppliedonBool("A2+B1"));
}
public void DynamicCell_Should_not_map_with_Function()
{
FSharpTransformationRule T4 = new FSharpTransformationRule();
T4.from = T4.ParseToTree("SUM({i,j+1}, {i,j})");
Assert.AreEqual(false, T4.CanBeAppliedonBool("SUM(A1,A8+A5)"));
}
public void SUM_in_argument_should_match()
{
FSharpTransformationRule T6 = new FSharpTransformationRule();
T6.from = T6.ParseToTree("[c]+[d]");
Assert.AreEqual(true, T6.CanBeAppliedonBool("SUM(A1,A8+A5)"));
}
public void Range_with_dymanice_cells_matches_range_with_cells()
{
FSharpTransformationRule R = new FSharpTransformationRule();
R.from = R.ParseToTree("{i,j}: {i,j+1}");
Assert.AreEqual(true, R.CanBeAppliedonBool("A1:A2"));
}
public void ConvertRange()
{
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse("A1:B7"));
Assert.IsNotNull(F);
}
public void SUM_in_argument_should_match6()
{
FSharpTransformationRule T6 = new FSharpTransformationRule();
T6.from = T6.ParseToTree("SUM({i,j}:{i+2,j})/3");
Assert.AreEqual(true, T6.CanBeAppliedonBool("SUM(B3:D3)/3"));
}
public void SUM_in_argument_should_match5()
{
FSharpTransformationRule S = new FSharpTransformationRule();
S.from = S.ParseToTree("SUM({i,j+1}, [c], {i,j}, [d])");
S.to = S.ParseToTree("SUM({i,j},[c],{i,j+1},[d])");
Assert.AreEqual(false, S.CanBeAppliedonBool("SUM(A1,SUM(A8,SUM(A5,SUM(A3,A2))))"));
}
public void Formula_With_Dynamic_Formula_Arguments()
{
FSharpTransformationRule T4 = new FSharpTransformationRule();
T4.from = T4.ParseToTree("[c]+[d]");
T4.to = T4.ParseToTree("SUM([c],[d])");
Assert.AreEqual("SUM((A1*B1),(A2*B2))", T4.ApplyOn("(A1*B1)+(A2*B2)"));
}
public void Dynamic_Argument_Can_Be_Formula()
{
var Original = ExcelFormulaParser.Parse("SUM(A1)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("[c]");
Assert.AreEqual(true, S1.CanBeAppliedonBool(Original));
}
public void Can_Not_Apply_Different_Dynamic_Cells()
{
var Original = ExcelFormulaParser.Parse("A3");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,i}");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void Can_Not_Apply_Different_Dynamic_Range()
{
var Original = ExcelFormulaParser.Parse("SUM(A2:C7)+SUM(A2:C6)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})+SUM({r})");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void Can_Apply_Normal_Cell_Reference()
{
string Cell = "A1";
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = ExcelFormulaParser.Parse(Cell);
Assert.IsTrue(T.CanBeAppliedonBool(ExcelFormulaParser.Parse(Cell)));
}
public void Fancy_Merge()
{
var Original = ExcelFormulaParser.Parse("(SUM(K3:K4,K5,K6,K7))/COUNT(K3:K7)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("SUM({x,y}: {i,j}, {i,j+1},[k])");
S1.to = S1.ParseToTree("SUM({x,y}:{i,j+1},[k])");
Assert.AreEqual(true, S1.CanBeAppliedonBool(Original));
}
public void Merge_Three_Ranges()
{
FSharpTransformationRule R = new FSharpTransformationRule();
R.from = R.ParseToTree("SUM({i,j}: {i,j+1}, {i,j+2})");
R.to = R.ParseToTree("SUM({i,j}:{i,j+2})");
Assert.AreEqual(true, R.CanBeAppliedonBool("SUM(A1:A2,A3)"));
Assert.AreEqual("SUM(A1:A3)", R.ApplyOn("SUM(A1:A2,A3)"));
}
public void Dynamic_Range_Transform_on_Argument_list()
{
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM([c])");
T.to = T.ParseToTree("SUM([c])+1");
Assert.AreEqual(true, T.CanBeAppliedonBool("SUM(A1,B1)"));
Assert.AreEqual("SUM(A1,B1)+1", T.ApplyOn("SUM(A1,B1)"));
}
public void If_error()
{
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("IF(ISERROR({r}),[c],{r})");
S1.to = S1.ParseToTree("IFERROR({r},[c])");
var Result = S1.ApplyOn("IF(ISERROR(B2),\"Error\",B2)");
Assert.AreEqual("IFERROR(B2,\"Error\")", Result);
}
public void Repeat_Merge()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2,A3,A4)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("SUM({i,j}, {i,j+1})");
S1.to = S1.ParseToTree("SUM({i,j}:{i,j+1})");
Assert.AreEqual(false, S1.CanBeAppliedonBool(Original));
}
public void ConvertFunction()
{
string Cell = "SUM(A1:B7)";
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse(Cell));
Assert.IsNotNull(F);
}
public void Can_not_Apply_Dynamic_Cell_Reference_Multiple_Places()
{
var Original = ExcelFormulaParser.Parse("A1+A2");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,j}+{i+1,j}");
T.to = null;
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void SUM_in_argument_should_match4()
{
FSharpTransformationRule T6 = new FSharpTransformationRule();
T6.from = T6.ParseToTree("SUM([c],SUM([d]), [e])");
T6.to = T6.ParseToTree("SUM([c], [d], [e])");
Assert.AreEqual(true, T6.CanBeAppliedonBool("SUM(A1,SUM(A5), A4,A7)"));
Assert.AreEqual("SUM(A1,A5,A4,A7)", T6.ApplyOn("SUM(A1,SUM(A5),A4,A7)"));
}
public void ConvertSheetReference()
{
string Cell = "Sheet!A1";
FSharpTransformationRule T = new FSharpTransformationRule();
FSharpTransform.Formula F = T.CreateFSharpTree(ExcelFormulaParser.Parse(Cell));
Assert.IsNotNull(F);
}
public void Can_not_Apply_Calculation()
{
var Original = ExcelFormulaParser.Parse("A1*A2");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
Assert.IsFalse(T.CanBeAppliedonBool(Original));
}
public void Dynamic_Range_Transform()
{
var Original = ExcelFormulaParser.Parse("SUM(A1:B1)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({r})");
T.to = T.ParseToTree("SUM({r})+1");
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("SUM(A1:B1)+1", T.Print(Result));
}
public void Transforming_strings()
{
FSharpTransformationRule T6 = new FSharpTransformationRule();
T6.from = T6.ParseToTree("[c]/[d]");
T6.to = T6.ParseToTree("IF([d]<>0,[c]/[d],\"Error\")");
Assert.AreEqual(true, T6.CanBeAppliedonBool("A1/A2"));
string g = T6.ApplyOn("A1/A2");
Assert.AreEqual("IF(A2<>0,A1/A2,\"Error\")", g);
}
public void Super_Simple_Transform()
{
var Original = ExcelFormulaParser.Parse("A1");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1"));
T.to = (ExcelFormulaParser.Parse("A2"));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("A2", T.Print(Result));
}
public void SUM_COUNT_AVERAGE()
{
var Original = ExcelFormulaParser.Parse("SUM(A1:A5)/COUNT(A1:A5)");
FSharpTransformationRule S = new FSharpTransformationRule();
S.from = S.ParseToTree("SUM({r})/COUNT({r})");
S.to = S.ParseToTree("AVERAGE(A1:A5)");
FSharpTransform.Formula Result = S.ApplyOn(Original);
Assert.AreEqual("AVERAGE(A1:A5)", S.Print(Result));
}
public void Double_Transform()
{
var Original = ExcelFormulaParser.Parse("(A1+A2)*(A1+A2)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("(SUM(A1:A2))*(SUM(A1:A2))", T.Print(Result));
}
public void If_error2()
{
var Original = ExcelFormulaParser.Parse("IF(ISERROR(A1+A2+B1),\"Error\",A1+A2+B1)");
FSharpTransformationRule S1 = new FSharpTransformationRule();
S1.from = S1.ParseToTree("IF(ISERROR([d]),[c],[d])");
S1.to = S1.ParseToTree("IFERROR([d],[c])");
Assert.IsTrue(S1.CanBeAppliedonBool(Original));
Assert.AreEqual("IFERROR(A1+A2+B1,\"Error\")", S1.ApplyOn(Original).Print());
}
public void Merge_Some_Ranges()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2,A3)");
FSharpTransformationRule S = new FSharpTransformationRule();
S.from = S.ParseToTree("SUM({i,j}, {i,j+1}, {r})");
S.to = S.ParseToTree("SUM({i,j}:{i,j+1}, {r})");
FSharpTransform.Formula Result = S.ApplyOn(Original);
Assert.AreEqual("SUM(A1:A2,A3)", S.Print(Result));
}
