Formula Utility Methods

Following sample demonstrates some GemBox.Spreadsheet utility methods that can be useful for creating formulas.

Screenshot

Formula Utility Methods Screenshot

See the full code below, use RUN EXAMPLE to execute.


1using System;
2using System.Collections.Generic;
3using System.Text;
4using GemBox.Spreadsheet;
5using GemBox.Spreadsheet.ConditionalFormatting;
6using GemBox.Spreadsheet.PivotTables;
7
8class Sample
9{
10    [STAThread]
11    static void Main(string[] args)
12    {
13        // If using Professional version, put your serial key below.
14        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY");
15
16        ExcelFile ef = new ExcelFile();
17        ExcelWorksheet ws = ef.Worksheets.Add("Formula Utility Methods");
18
19        // Fill first column with values.
20        for (int i = 0; i < 10; i++)
21            ws.Cells[i, 0].Value = i + 1;
22
23        // Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
24        for (int i = 0; i < 10; i++)
25            ws.Cells[i, 1].Formula = String.Format("={0}*2", CellRange.RowColumnToPosition(i, 0));
26
27        // Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
28        for (int i = 0; i < 10; i++)
29            ws.Cells[i, 2].Formula = String.Format("=SUM(A{0}:B{0})", ExcelRowCollection.RowIndexToName(i));
30
31        // Cell A12 contains sum of all values from the first row.
32        ws.Cells["A12"].Formula = String.Format("=SUM(A1:{0}1)", ExcelColumnCollection.ColumnIndexToName(ws.Rows[0].AllocatedCells.Count - 1));
33
34        ef.Save("Formula Utility Methods.xls");
35    }
36}
1Imports System
2Imports System.Collections.Generic
3Imports System.Text
4Imports GemBox.Spreadsheet
5Imports GemBox.Spreadsheet.ConditionalFormatting
6Imports GemBox.Spreadsheet.PivotTables
7
8Module Samples
9
10    Sub Main()
11
12        ' If using Professional version, put your serial key below.
13        SpreadsheetInfo.SetLicense("FREE-LIMITED-KEY")
14
15        Dim ef As ExcelFile = New ExcelFile
16        Dim ws As ExcelWorksheet = ef.Worksheets.Add("Formula Utility Methods")
17
18        ' Fill first column with values.
19        Dim i As Int32
20        For i = 0 To 9 Step 1
21            ws.Cells(i, 0).Value = i + 1
22        Next
23
24        ' Cell B1 has formula '=A1*2', B2 '=A2*2', etc.
25        For i = 0 To 9 Step 1
26            ws.Cells(i, 1).Formula = String.Format("={0}*2", CellRange.RowColumnToPosition(i, 0))
27        Next
28
29        ' Cell C1 has formula '=SUM(A1:B1)', C2 '=SUM(A2:B2)', etc.
30        For i = 0 To 9 Step 1
31            ws.Cells(i, 2).Formula = String.Format("=SUM(A{0}:B{0})", ExcelRowCollection.RowIndexToName(i))
32        Next
33
34        ' Cell A12 contains sum of all values from the first row.
35        ws.Cells("A12").Formula = String.Format("=SUM(A1:{0}1)", ExcelColumnCollection.ColumnIndexToName(ws.Rows(0).AllocatedCells.Count - 1))
36
37        ef.Save("Formula Utility Methods.xls")
38
39    End Sub
40
41End Module

Check next sample or find out more about GemBox.Spreadsheet and GemBox Software.