OpenXML如何获取范围内的单元格 [英] OpenXML how to get cell in range

问题描述

请帮助我获取范围内的单元格(例如，从A:1到E:11都是矩形的单元格). 目前，我的理想是

Please help me to get cell in range (ex from A:1 to E:11 are all cells in rectangular). For now, my ideal is

 Worksheet worksheet = GetWorksheet(document, sheetName);
        SheetData sheetData = worksheet.GetFirstChild<SheetData>();
        IEnumerable<Cell> cells = sheetData.Descendants<Cell>().Where(c =>
            c.CellReference >= A:1 &&
            c.CellReference <= E:11 &&
            );
        int t = cells.Count();

但是此代码不起作用. 谢谢

But this code does not work. Thanks

推荐答案

将单元格的CellReference与字符串进行比较并不是那么容易.是的，您当前正在做的事情是错误的.您根本无法以这种方式比较strings较高或较低.

It won't be that easy to compare cell's CellReference with a string. And yes, what you are currently doing is wrong. You simply cannot compare strings for Higher or Lower in such a way.

您有两个选择.

选项1:

您可以获取单元格引用并将其分解.这意味着将字符和数字分开，然后分别给它们赋值并进行比较

You can take cell reference and break it down. That means separate characters and numbers and then give them values individually and compare

A1 - > A and 1 -> Give A =1 so you have 1 and 1

E11 -> E and 11 -> Give E = 5 so you have 5 and 11

因此，您需要细分CellReference并检查您的要求的有效性.

So you will need to breakdown the CellReference and check the validity for your requirement.

选项2:

如果您在上面注意到它，那我们就简单地采用2D矩阵索引(ex : 1,1 and 5,11 which are COLUMN,ROW format).您可以简单地在比较中使用此功能.但是要注意的是，您不能为此使用LINQ，您需要遍历行和列.我尝试给出以下示例代码，尝试一下

If you notice above it's simply we take a 2D matrix index (ex : 1,1 and 5,11 which are COLUMN,ROW format). You can simply use this feature in comparison. But catch is you cannot use LINQ for this, you need to iterate through rows and columns. I tried to give following example code, try it

 using (SpreadsheetDocument myDoc = SpreadsheetDocument.Open("PATH", true))
    {
        //Get workbookpart
        WorkbookPart workbookPart = myDoc.WorkbookPart;

        // Extract the workbook part
        var stringtable = workbookPart.GetPartsOfType<SharedStringTablePart>().FirstOrDefault();

        //then access to the worksheet part
        IEnumerable<WorksheetPart> worksheetPart = workbookPart.WorksheetParts;

        foreach (WorksheetPart WSP in worksheetPart)
        {
            //find sheet data
            IEnumerable<SheetData> sheetData = WSP.Worksheet.Elements<SheetData>();

            int RowCount = 0;
            int CellCount = 0;

            // This is A1
            int RowMin = 1;
            int ColMin = 1;

            //This is E11              
            int RowMax = 11;
            int ColMax = 5;

            foreach (SheetData SD in sheetData)
            {
                foreach (Row row in SD.Elements<Row>())
                {
                    RowCount++; // We are in a new row

                    // For each cell we need to identify type
                    foreach (Cell cell in row.Elements<Cell>())
                    {
                        // We are in a new Cell
                        CellCount++;

                        if ((RowCount >= RowMin && CellCount >= ColMin) && (RowCount <= RowMax && CellCount <= ColMax))
                        {

                            if (cell.DataType == null && cell.CellValue != null)
                            {
                                // Check for pure numbers
                                Console.WriteLine(cell.CellValue.Text);
                            }
                            else if (cell.DataType.Value == CellValues.Boolean)
                            {
                                // Booleans
                                Console.WriteLine(cell.CellValue.Text);
                            }
                            else if (cell.CellValue != null)
                            {
                                // A shared string
                                if (stringtable != null)
                                {
                                    // Cell value holds the shared string location
                                    Console.WriteLine(stringtable.SharedStringTable.ElementAt(int.Parse(cell.CellValue.Text)).InnerText);
                                }
                            }
                            else
                            {
                                Console.WriteLine("A broken book");
                            }

                        }
                    }
                    // Reset Cell count
                    CellCount = 0;
                }
            }
        }
    }

这确实有效.我测试过.

This actually work. I tested.

这篇关于OpenXML如何获取范围内的单元格的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！