Using Formulas with Xceed Workbooks for .NET

Xceed Workbooks for .NET allow developers to easily create advanced spreadsheet-like calculations. All this is possible by using the advanced formula engine, which supports numeric, logical, string and DateTime calculations. In addition, you can use its built-in collection of functions to easily access data values within your workbook, as well as use external data sources using various built-in connectors.

How to Use Formulas

Using formulas in Xceed Workbooks for .NET is easy. All you need to do is enter your formula as a string in your code. For example, a simple formula to calculate the sum of two cells would look like this:

Dim formulaString As String = "=SUM(A1, B2)"

You can also use shorthand notation for referencing a cell or range like so:

Dim formulaString As String = "=SUM(A1:A3)"

Once you have your formula, you can assign it to a cell like this:

Cell cell = worksheet.Cells["C1"];
cell.Formula = formulaString;

Using Built-in Formulas

Xceed Workbooks includes a wide variety of ready-to-use built-in formulas. These offer lots of functionality and make it easier to perform calculations directly in your workbook. For example, to get the maximum value of a range of cells, you can use the MAX formula like so:

Dim formulaString As String = "=MAX(A1:A3)"

You can also use multiple parameters, like so:

Dim formulaString As String = "=MAX(A1:A3, B1:B3)"

These formulas also support custom parameters, like cell references or values, such as dates, strings, and numbers. For example, if you want to find the maximum value of a range of cells, but you also want to add an additional value to the calculation, you can use the MAX formula like this:

Dim formulaString As String = "=MAX(A1:A3, 100)"

Using External Data Sources

Xceed Workbooks for .NET also provides support for connecting to external data sources, such as Microsoft Excel or CSV files. Once the data source is connected, you can use formulas to manipulate the data. For example, if you have a CSV file with a "Price" column, you could use the AVERAGE() formula to calculate the average price of all the records in the file.

Dim formulaString As String = "=AVERAGE([csv/Price])"

You can also reference external data sources in your formulas, such as calculating an average value from an external spreadsheet:

Dim formulaString As String = "=AVERAGE([excel/Sheet1!A1:A10])"

Using C#

Xceed Workbooks for .NET also supports the use of C# to write formulas. This allows you to write formulas with more complex logic, as well as use external libraries, such as LINQ and Entity Framework. For example, if you have a collection of objects and you want to calculate the average of a certain property of those objects, you could write a formula like this:

var formulaString = "=AVERAGE(@Enumerable.Average(@objects, o => o.Price))";

Where @objects is a list of objects with a Price property. You can also use the built-in math and string methods like this:

var formulaString = "=Math.Max(A1, B2)";

Conclusion

Using formulas in Xceed Workbooks for .NET is an easy and powerful way to create sophisticated spreadsheet-like calculations and charting applications. With the built-in formulas, support for external data sources and the ability to use C# in your formulas, the possibilities are endless.