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; nextFunction = "=ACOS(0.23)"; sheet1.Cells[rowIndex, 0].Formula = nextFunction; sheet1.Cells[rowIndex++, 1].Value = nextFunction; nextFunction = "=RADIANS(45)"; sheet1.Cells[rowIndex, 0].Formula = nextFunction; sheet1.Cells[rowIndex++, 1].Value = nextFunction; nextFunction = @"=HYPERLINK(""http://www.GemBoxSoftware.com"",""GemBox Software"")"; sheet1.Cells[rowIndex, 0].Formula = nextFunction; sheet1.Cells[rowIndex, 0].Style.Font.UnderlineStyle = UnderlineStyle.Single; sheet1.Cells[rowIndex, 0].Style.Font.Color = Color.Blue; 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."); } } } }