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 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 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 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 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 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()
{
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 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));
}
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 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 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 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 Transform_Dynamic_Cell_Calculation()
{
var Original = ExcelFormulaParser.Parse("A1");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("{i,j}");
T.to = T.ParseToTree("{i,j+1}");
FSharpTransform.Formula Result = T.ApplyOn(Original);
ParseTreeNode Expected = (ExcelFormulaParser.Parse("A2"));
Assert.AreEqual("A2", T.Print(Result));
}
public void Transform_in_Arguments()
{
var Original = ExcelFormulaParser.Parse("(A1+A2)/3");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = (ExcelFormulaParser.Parse("A1+A2"));
T.to = (ExcelFormulaParser.Parse("SUM(A1:A2)"));
bool x = T.CanBeAppliedonBool(Original);
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("(SUM(A1:A2))/3", T.Print(Result));
}
public void Merge_Ranges()
{
var Original = ExcelFormulaParser.Parse("SUM(A1,A2)");
FSharpTransformationRule T = new FSharpTransformationRule();
T.from = T.ParseToTree("SUM({i,j}, {i,j+1})");
T.to = T.ParseToTree("SUM({i,j}:{i,j+1})"); //hier heb je een gewone range met dt=ynamische cellen
Assert.IsTrue(T.CanBeAppliedonBool(Original));
FSharpTransform.Formula Result = T.ApplyOn(Original);
Assert.AreEqual("SUM(A1:A2)", T.Print(Result));
}
private void applyInRange(FSharpTransformationRule T, ExcelRaw.Range Range, bool previewOnly = false)
{
foreach (ExcelRaw.Range cell in Range.Cells)
{
if ((bool)cell.HasFormula)
{
var Formula = RemoveFirstSymbol((string)cell.Formula);
if (T.CanBeAppliedonBool(Formula))
{
if (previewOnly)
{
var transformationCell = new HighlightedCell(cell, cell.Interior.Pattern, cell.Interior.Color, cell.Comment);
cell.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Yellow);
if (!transformationCells.Any(x => x.Equals(transformationCell)))
{
transformationCells.Add(transformationCell);
}
}
else
{
var transformedFormula = T.ApplyOn(Formula);
if (valueChanges(cell, transformedFormula))
{
if (MessageBox.Show("The transformation causes the value of cell " +
cell.Worksheet.Name + ":" + cell.get_Address(false, false, Microsoft.Office.Interop.Excel.XlReferenceStyle.xlA1) +
" to change from " + cell.Value + " to " + getValue(cell, transformedFormula) +
". Do you want to continue?",
"Alert: Cell value change",
MessageBoxButtons.YesNo,
MessageBoxIcon.Warning)
== DialogResult.No)
{
return;
}
}
cell.Formula = "=" + transformedFormula;
cell.Interior.Color = System.Drawing.ColorTranslator.ToOle(System.Drawing.Color.Green);
}
}
}
}
}
