Getting Data from Excel into C1CalcEngine

The other week I was asked: What is the best way to load data from an Excel spreadsheet into C1CalcEngine? For this I know of two approaches: 1) Use automation and have Excel load the sheet, then inspect the cell(s) you want for the formulas. There's an example of that here: http://www.learnqtp.com/use-test-data-directly-from-external-sheet/[^] This approach assumes that the client machine has Excel installed (Excel COM Interop). 2) Use a component such as C1Excel to load the sheet. The ComponentOne Excel library for .NET and Silverlight (often referred to as "C1Excel" for clarity) provides an object model that makes it easy to read and write XLSX (and XLS in some versions) files from your C# or VB.NET code. The code would look like this:

var book = new C1XLBook();  
book.Load(fileName);  
var sheet = book.Sheets[0];  
var formula = sheet[0,0].Formula;  

We have several samples showing how to open or save Excel files and display the results in a C1FlexGrid. You can check out the ExcelBook sample, or in the Calculation Engine for FlexGrid articles I described a simple way to add an Excel-like calculation engine to C1FlexGrid. By default, C1Excel returns the values evaluated by Excel before any changes are made to any cells. If you change any cell values, formula results are not updated automatically. By integrating C1Excel with the C1CalcEngine class we can extend the functionality to evaluate Excel cells dynamically. The sample below demonstrates how this can be accomplished. The C1CalcEngine class is part of the C1.Silverlight.Binding or C1.WPF.Binding library. It's a public class and can be used to parse strings into expression objects or parse strings to evaluate resulting expressions. The sample below declares an extension method on the XLCell class which allows you to get the updated cell value based on the current content of the cells. So you are able to modify cells dynamically in code, and then apply the formulas to re-evaluate your expressions. The sample has been configured to work with formulas containing cells referenced across multiple sheets in a work book. Download Sample