通过Open XML SDK Linq查询获取Excel单元格的行列位置对应的值

Question

有人知道如何使用Open XML SDK 2.0和Linq获取Excel单元格的值，已知行和列位置，例如（A2）吗？

- Rustovich

Answer 1

因此，Excel将单元格值直接存储或者（如果是字符串）存储在称为SharedString表的公共数据结构中。

在下面的示例中，我们首先使用LINQ获取特定地址处的单元格，然后将其传递给另一个函数以获取单元格的值。

还要注意，您必须在每个阶段添加空值检查以确定行/列是否存在。

using System.IO;
using System.Linq;
using System.Text.RegularExpressions;
using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;

namespace TestOpenXmlSDK
{
    class Program
    {
        static void Main(string[] args)
        {
            string pathSource = @"D:\sample.xlsx";
            using (FileStream fsSource = new FileStream(pathSource, FileMode.Open, FileAccess.Read))
            {
                byte[] bytes = new byte[fsSource.Length];
                fsSource.Read(bytes, 0, (int)fsSource.Length);
                using (MemoryStream mem = new MemoryStream())
                {
                    mem.Write(bytes, 0, (int)bytes.Length);
                    using (SpreadsheetDocument excelDocument = SpreadsheetDocument.Open(mem, true))
                    {
                        var wbPart = excelDocument.WorkbookPart;
                        var wsPart = wbPart.WorksheetParts.First();
                        var sheetData = wsPart.Worksheet.GetFirstChild<SheetData>();

                        var cellValue = GetCellValue(GetCell(sheetData, "B2"), wbPart);
                    }
                }
            }
        }

        public static Cell GetCell(SheetData sheetData, string cellAddress)
        {
            uint rowIndex = uint.Parse(Regex.Match(cellAddress, @"[0-9]+").Value);
            return sheetData.Descendants<Row>().FirstOrDefault(p => p.RowIndex == rowIndex).Descendants<Cell>().FirstOrDefault(p => p.CellReference == cellAddress);
        }

        public static string GetCellValue(Cell cell, WorkbookPart wbPart)
        {
            string value = cell.InnerText;
            if (cell.DataType != null)
            {
                switch (cell.DataType.Value)
                {
                    case CellValues.SharedString:
                        var stringTable = wbPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();
                        if (stringTable != null)
                        {
                            value = stringTable.SharedStringTable.ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                }
            }
            return value;
        }
    }
}

- cvraman

Answer 2

只需将此函数复制粘贴到您的程序中，然后按照以下示例调用该函数。

（它将在不打开Excel或任何东西的情况下立即获取您的 CellValue <1秒）

// Example GetCellValue(@"c:\test.xlsx", "Sheet1", "A1");
// 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 (SpreadsheetDocument document = 
        SpreadsheetDocument.Open(fileName, false))
    {
        // Retrieve a reference to the workbook part.
        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.
        Sheet theSheet = wbPart.Workbook.Descendants<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.
        WorksheetPart wsPart = 
            (WorksheetPart)(wbPart.GetPartById(theSheet.Id));

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

        // If the cell does not exist, return an empty string.
        if (theCell.InnerText.Length > 0)
        {
            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 CellValues.SharedString:

                        // For shared strings, look up the value in the
                        // shared strings table.
                        var stringTable = 
                            wbPart.GetPartsOfType<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.SharedStringTable
                                .ElementAt(int.Parse(value)).InnerText;
                        }
                        break;

                    case CellValues.Boolean:
                        switch (value)
                        {
                            case "0":
                                value = "FALSE";
                                break;
                            default:
                                value = "TRUE";
                                break;
                        }
                        break;
                }
            }
        }
    }
    return value;
}

参考文献： https://learn.microsoft.com/zh-cn/office/open-xml/how-to-retrieve-the-values-of-cells-in-a-spreadsheet

- Chandraprakash