如何使用C＃读取单元格中公式的值而不是公式？ [英] How do I read the value of a formula in a cell instead of the formula with C#?

问题描述

我能够读取目标单元格，但单元格使用公式来计算值。需要的是计算值，而不是公式。我如何使用C＃并使用OpenXml？



谢谢。

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

我尝试过：



value = stringTable.SharedStringTable.ElementAt（int.Parse（value））。InnerText;

What I have tried:

value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;

推荐答案

检查：如何：检索单元格的值在电子表格文档（Open XML SDK） [ ^ ]

我需要使用result = thecurrentcell.CellValue.InnerText;而不是result = thecurrentcell.InnerText;。我现在得到的值而不是公式。

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

这篇关于如何使用C＃读取单元格中公式的值而不是公式？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！