FormulaSamplesCS

using System;

using GemBox.Spreadsheet;

namespace Samples
{
    /// <summary>
    /// Formula samples.
    /// </summary>
    class FormulaSamplesCS
    {
        [STAThread]
        static void Main(string[] args)
        {
            // TODO: If using GemBox.Spreadsheet Professional, put your serial key below.
            // Otherwise, if you are using GemBox.Spreadsheet Free, comment out the 
            // following line (Free version doesn't have SetLicense method). 
            // SpreadsheetInfo.SetLicense("YOUR-SERIAL-KEY-HERE");

            ExcelFile excelFile = new ExcelFile();

            CreateFormulaSheets(excelFile);

            string fileName = "FormulasCS.xls";
            excelFile.SaveXls(fileName);
            // Uncomment if you want to export in XLSX.
            //string fileName = "FormulasCS.xlsx";
            //excelFile.SaveXlsx(fileName);

            TryToDisplayGeneratedFile(fileName);
        }

        static void CreateFormulaSheets(ExcelFile excelFile)
        {
            int rowIndex = 0;

            ExcelWorksheet sheet1 = excelFile.Worksheets.Add("sheet1");
            ExcelWorksheet sheet2 = excelFile.Worksheets.Add("sheet2");

            sheet1.Columns[0].Width = 35 * 256;
            sheet1.Columns[1].Width = 15 * 256;
            sheet1.Columns[2].Width = 15 * 256;

            sheet1.Cells[rowIndex++, 0].Value = "Examples of typical formulas usage:";

            sheet1.Cells[++rowIndex, 0].Value = "Some data:";
            sheet1.Cells[rowIndex, 1].Value = 3;
            sheet1.Cells[rowIndex, 2].Value = 4.1;
            sheet1.Cells[++rowIndex, 1].Value = 5.2;
            sheet1.Cells[rowIndex, 2].Value = 6;
            sheet1.Cells[++rowIndex, 1].Value = 7;
            sheet1.Cells[rowIndex++, 2].Value = 8.3;

            // Named ranges.
            string namedRange = "Range1";
            sheet1.NamedRanges.Add(namedRange, sheet1.Cells.GetSubrange("B3", "C4"));

            // Floats without first digit.
            sheet1.Cells[++rowIndex, 0].Value = "Float number without first digit:";
            sheet1.Cells[rowIndex, 1].Formula = "=.5/23+.1-2";

            // Function using named range.
            sheet1.Cells[++rowIndex, 0].Value = "Named range:";
            sheet1.Cells[rowIndex, 1].Formula = "=SUM(" + namedRange + ")";

            // 3D sheet references.
            sheet1.Cells[++rowIndex, 0].Value = "3d sheet reference:";
            sheet1.Cells[rowIndex, 1].Formula = "=sheet2!$C$2";

            // 3D area sheet references.
            sheet1.Cells[++rowIndex, 0].Value = "3d area sheet reference:";
            sheet1.Cells[rowIndex, 1].Formula = "=AVERAGE(sheet2!$A$2:C$2)";

            // Function's miss argument.
            sheet1.Cells[++rowIndex, 0].Value = "Function's miss arguments:";
            sheet1.Cells[rowIndex, 1].Formula = "=Count(1,  ,  ,,,2, 23,,,,,, 34,,,54,,,,  ,)";

            // Functions are case-insensitive.
            sheet1.Cells[++rowIndex, 0].Value = "Functions are case-insensitive:";
            sheet1.Cells[rowIndex, 1].Formula = "=cOs( 1 )";

            // Functions.
            sheet1.Cells[++rowIndex, 0].Value = "Supported functions:";

            string nextFunction;
            sheet1.Cells[++rowIndex, 0].Value = "Results";
            sheet1.Cells[rowIndex++, 1].Value = "Formulas";

            nextFunction = "=NOW()+123";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SECOND(12)/23";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MINUTE(24)-1343/35";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=(HOUR(56)-23/35)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=WEEKDAY(5)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=YEAR(23)-WEEKDAY(5)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MONTH(3)-2342/235345";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=((DAY(1)))";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=TIME(1,2,3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=DATE(1,2,3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=RAND()";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=TEXT(\"text\", \"$d\")";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=VAR(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MOD(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=NOT(FALSE)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=OR(FALSE)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=AND(TRUE)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=FALSE()";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=TRUE()";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=VALUE(3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=LEN(\"hello\")";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MID(\"hello\",1,1)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=ROUND(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SIGN(-2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=INT(3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=ABS(-3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=LN(2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=EXP(4)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SQRT(2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=PI()";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=COS(4)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SIN(3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MAX(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=MIN(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=AVERAGE(1,2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SUM(1,3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=IF(1,2,3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=COUNT(1,2,3)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            nextFunction = "=SUBTOTAL(1,sheet2!A2:C2)";
            sheet1.Cells[rowIndex, 0].Formula = nextFunction;
            sheet1.Cells[rowIndex++, 1].Value = nextFunction;

            // Paranthless checks.
            sheet1.Cells[++rowIndex, 0].Value = "Paranthless:";
            sheet1.Cells[rowIndex, 1].Formula = "=((12+2343+34545))";

            // Unary operators.
            sheet1.Cells[++rowIndex, 0].Value = "Unary operators:";
            sheet1.Cells[rowIndex, 1].Formula = "=B5%";
            sheet1.Cells[rowIndex, 2].Formula = "=+++B5";

            // Operand tokens, bool.
            sheet1.Cells[++rowIndex, 0].Value = "Bool values:";
            sheet1.Cells[rowIndex, 1].Formula = "=TRUE";
            sheet1.Cells[rowIndex, 2].Formula = "=FALSE";

            // Operand tokens, int.
            sheet1.Cells[++rowIndex, 0].Value = "Integer values:";
            sheet1.Cells[rowIndex, 1].Formula = "=1";
            sheet1.Cells[rowIndex, 2].Formula = "=20";

            // Operand tokens, num.
            sheet1.Cells[++rowIndex, 0].Value = "Float values:";
            sheet1.Cells[rowIndex, 1].Formula = "=.4";
            sheet1.Cells[rowIndex, 2].Formula = "=2235.5132";

            // Operand tokens, str.
            sheet1.Cells[++rowIndex, 0].Value = "String values:";
            sheet1.Cells[rowIndex, 1].Formula = "=\"hello world!\"";

            // Operand tokens, error.
            sheet1.Cells[++rowIndex, 0].Value = "Error values:";
            sheet1.Cells[rowIndex, 1].Formula = "=#NULL!";
            sheet1.Cells[rowIndex, 2].Formula = "=#DIV/0!";

            // Binary operators.
            sheet1.Cells[++rowIndex, 0].Value = "Binary operators:";
            sheet1.Cells[rowIndex, 1].Formula = "=(1)-(2)+(3/2+34)/2+12232-32-4";

            // Another sheet.
            rowIndex = 0;
            sheet2.Cells[rowIndex++, 0].Value = "Some data on another sheet:";
            sheet2.Cells[rowIndex, 0].Value = 33;
            sheet2.Cells[rowIndex, 1].Value = 44.1;
            sheet2.Cells[rowIndex, 2].Value = 55.2;
            sheet2.Cells[++rowIndex, 0].Value = 66;
            sheet2.Cells[rowIndex, 1].Value = 77;
            sheet2.Cells[rowIndex, 2].Value = 88.3;
        }

        static void TryToDisplayGeneratedFile(string fileName)
        {
            try
            {
                System.Diagnostics.Process.Start(fileName);
            }
            catch (Exception)
            {
                Console.WriteLine(fileName + " created in application folder.");
            }
        }
    }
}