using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;

namespace iDiTect.Excel.Demo
{
    public static class FormulaHelper
    {
        public static void AddFormula()
        {
            var workbook = new Workbook();
            var worksheet = workbook.Worksheets.Add("FormulaSheet");

            worksheet.Cell("A1").Value = 1;
            worksheet.Cell("A2").Value = 2;
            worksheet.Cell("A3").Value = 3;

            //Add SUM formula
            worksheet.Cell("A5").Formula = "SUM(A1:A3)";
            worksheet.Cell("A6").Formula = "1 + 2 + 3";

            //Set B2 = A1
            worksheet.Cell("B2").FormulaR1C1 = "R[-1]C[-1]";
            //Set B3 = A3
            worksheet.Cell("B3").FormulaR1C1 = "RC[-1]";
            //Set B4 = A5
            worksheet.Cell("B4").FormulaR1C1 = "R[1]C[-1]";
            //Set B5 = B2 + B3
            worksheet.Cell("B5").FormulaR1C1 = "R[-3]C + R[-2]C";

            //Calculate the formula for whole workbook
            workbook.CalculateMode = CalculateMode.Auto;
            workbook.CalculationOnSave = true;

            workbook.Save("Formula.xlsx");
        }

    }
}
