OpenXML SDK: How do I find the formula in a CellFormula where the type is "dataTable"?

Chris 25 Reputation points
2023-05-05T19:11:14.4866667+00:00

I'm writing an application in C# to read formulas from various cells in an Excel spreadsheet, and one section is giving me trouble. In Excel, it displays the formula as "{=TABLE(,B5)}" for all cells in this range.

I stepped through my C# application to see what the properties are, but there's no actual formula listed anywhere I can find (the CellFormula is an empty string). The InnerXml/OuterXml indicates a CellFormula, I tried reading through the SDK documentation for them, but it is confusing and vague when it comes to "dataTable" types.

Supposedly the relevant information is in the top-left corner of the dataTable section, which is cell D15. This is how the OuterXml looks:

<x:c r="D15" s="377" xmlns:x="http://schemas.openxmlformats.org/spreadsheetml/2006/main"><x:f t="dataTable" ref="D15:G35" dt2D="0" dtr="0" r1="B5" ca="1" /><x:v>3.0000000000000001</x:v></x:c>

Where is the formula? How does Excel know what to calculate in this range D15:G35?

Office Development
Office Development
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Development: The process of researching, productizing, and refining new or existing technologies.
3,653 questions
Office Open Specifications
Office Open Specifications
Office: A suite of Microsoft productivity software that supports common business tasks, including word processing, email, presentations, and data management and analysis.Open Specifications: Technical documents for protocols, computer languages, standards support, and data portability. The goal with Open Specifications is to help developers open new opportunities to interoperate with Windows, SQL, Office, and SharePoint.
126 questions
{count} votes

0 additional answers

Sort by: Most helpful