CODEWITHSUNDEEP
excelformulaopenxml
devC
devC

Reputation: 1444

Reading cells which have formulae data using OpenXML API

I'm using the OpenXML API to read an excel file. I have a wrapper for the API, so I can directly read the rows and cells in Excel. There are two columns whose values are generated using a formula as follows.

=B12&"."&C12&"@dtestorg.com"

Basically, what it does is - create the email address using the first name and last name cell values for a user.

When I read this cell as follows, I read it as empty:

row.XCells[6].GetValue()

Is there a specific way to read the cells generated using formulae, in OpenXML?

Upvotes: 0

Views: 73

Answers (1)

Vijayakumar
Vijayakumar

Reputation: 140

Essential XlsIO is an option for reading & modifying Excel documents with formulas.

For example, let us consider you have an Excel sheet as shown below.

enter image description here

Step 1: Create a console application Step 2: Add reference to Syncfusion.XlsIO.Base and Syncfusion.Compression.Base assemblies. These assemblies can be downloaded from NuGet too. Step 3: Copy & paste the following code in the main method

//Create an instance of Excel application instance
 using (ExcelEngine engine = new ExcelEngine())
 {
     //Open an existing Excel workbook
     IWorkbook workbook = engine.Excel.Workbooks.Open(@"..\..\Template.xlsx");
     //Access the worksheet with the name "Sheet1"
     IWorksheet worksheet = workbook.Worksheets["Sheet1"];
     //Access the range/cell "C2" from the worksheet
     IRange range = worksheet["C2"];

     //Access the Text to be displayed in the cell
     Console.WriteLine("Display Text: " + range.DisplayText);
     //Access the Formula of the cell
     Console.WriteLine("Formula:" + range.Formula);
 }

The whole suite of controls is available for free (commercial applications also) through the community license program if you qualify. The community license is the full product with no limitations or watermarks.

Note: I work for Syncfusion.

Upvotes: 0

Related Questions