Click here to Skip to main content
15,887,267 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I am able to read the target cell, but the cell uses a formula to calculate a value. What is needed is the calculated value, not the formula. How do I do that with C# and using OpenXml?

Thanks.

re is more of the code.  

        public void ReadMyData()//double idx) // idx = xmHt + bf + heavy duty
        {

            string fileName = @"c:\cax\resource\bpsys\programs\dose_data.xlsx";
            string sheetName = "RFML_DATA";
            string addressName = "A1";
            double idx = 77.50;
            int count = 1;
            string key = "";
            string myData = idx.ToString();
            try
            {
                do
                {
                    addressName = "A" + count.ToString();
                    key = GetCellValue(fileName, sheetName, addressName);
                    count++;
                }
                while (key != myData);
            }
            catch (ArgumentException e)
            {
                MessageBox.Show(e.Message);
            }
        }

        // GetCellValue code:
        // Adapted from "How to: retrieve the values of cells in a spreadsheet document (open xml sdk)"
        // 
        // Retrieve the value of a cell, given a file name, sheet name, and address name.
        public static string GetCellValue(string fileName, string sheetName, string addressName)
        {
            string value = null;

            // Open the spreadsheet document for read-only access.
            using (DOpkg.SpreadsheetDocument document = DOpkg.SpreadsheetDocument.Open(fileName, false))
            {
                // Retrieve a reference to the workbook part.
                DOpkg.WorkbookPart wbPart = document.WorkbookPart;

                // Find the sheet with the supplied name, and then use that 
                // Sheet object to retrieve a reference to the first worksheet.
                DOxls.Sheet theSheet = wbPart.Workbook.Descendants<doxls.sheet>().Where(s => s.Name == sheetName).FirstOrDefault();

                // Throw an exception if there is no sheet.
                if (theSheet == null)
                {
                    throw new ArgumentException("sheetName");
                }

                // Retrieve a reference to the worksheet part.
                DOpkg.WorksheetPart wsPart = (DOpkg.WorksheetPart)(wbPart.GetPartById(theSheet.Id));

                // Use its Worksheet property to get a reference to the cell 
                // whose address matches the address you supplied.
                DOxls.Cell theCell = wsPart.Worksheet.Descendants<doxls.cell>().Where(c => c.CellReference == addressName).FirstOrDefault();

                // If the cell does not exist, return an empty string.
                if (theCell != null)
                {
                    value = theCell.InnerText;

                    // If the cell represents an integer number, you are done.  For dates, this code returns the serialized value that 
                    // represents the date. The code handles strings and Booleans individually. For shared strings, the code 
                    // looks up the corresponding value in the shared string table. For Booleans, the code converts the value into 
                    // the words TRUE or FALSE.
                    if (theCell.DataType != null)
                    {
                        switch (theCell.DataType.Value)
                        {
                            case DOxls.CellValues.SharedString:

                                // For shared strings, look up the value in the shared strings table.
                                var stringTable = wbPart.GetPartsOfType<dopkg.sharedstringtablepart>().FirstOrDefault();

                                // If the shared string table is missing, something is wrong. Return the index that is in
                                // the cell. Otherwise, look up the correct text in the table.
                                if (stringTable != null)
                                {
                                    value = stringTable.Share


What I have tried:

value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
Posted
Updated 12-Jul-18 9:50am
v2
Comments
Patrice T 6-Jun-18 14:23pm    
Try to give context (like try to read the value of a cell in Excel.
Try to give an example.
try to show your code and explain the problem.
sonny3g 12-Jul-18 15:26pm    
Thanks, new to this forum and don't know all the ins-n-outs yet. As for the question, well, the solution is so simple that I am embarrassed. I needed to use "result = thecurrentcell.CellValue.InnerText;" instead of "result = thecurrentcell.InnerText;". I now get the value instead of the formula.
Patrice T 12-Jul-18 15:48pm    
Nice to see it is solved.
If solution 1 was helpful, accept it, otherwize add your own solution and accept it.
Accepting a solution tells eceryone the question is solved;
Patrice T 7-Jun-18 17:26pm    
Use Improve question to update your question.
So that everyone can pay attention to this information.

I needed to use "result = thecurrentcell.CellValue.InnerText;" instead of "result = thecurrentcell.InnerText;". I now get the value instead of the formula.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900