有没有办法找到Excel中的一个范围内的范围是多少？ [英] Is there a way to find a range within a range in Excel?

问题描述

我有一个xls文件看起来是这样的：

I have an xls file that looks this way:

的 http://i.stack.imgur.com/d2fux.png

好吧，这就是某物像同一块信息依赖于标头，在这种情况下，报头是行22164，下一个报头是22178，等

Well, and that's sth like the same piece of info that depends on the headers, in this case the header is the row 22164, next header is 22178, and so on.

在GUI中，我有让用户事从参数选择和选项，从xls文件取决于他们的选择加载信息，例如，他们可以选择：

In the GUI, i have something that let users choose from parameters, and option to load the info from the xls file depending on their choices, for example, they can choose:

WR |随机| 4 | 6 | 15

WR | random | 4 | 6 | 15

在寻找这种情况下，存储在A22165的信息：O22177

looking in that case for the info stored in A22165:O22177

我已经有工作簿中所有信息的表，让我们把这个对象[，]'valueArray'：

I have already the sheet with all the info in the workbook, let's call that object[,] 'valueArray':

Workbook workBook = _excelApp.Workbooks.Open(censFilePath);
        Worksheet sheet = (Worksheet)workBook.Sheets[1];
        Range sheetRange = sheet.UsedRange;
        object[,] valueArray = 
            (object[,])sheetRange.get_Value(XlRangeValueDataType.xlRangeValueDefault);

和我创建了存储用户的选择以相同的顺序和格式的XLS的另一目的。让我们把这个对象[，]某物像'look4it

and I have created another object that stores the user's choices in the same order and format that the xls has. Let's call that object[,] sth like 'look4it'

我试图找到一种方法，我可以告诉：我想找到'valueArraylook4it ，并且该方法的答案将是A22164：E22164，所以我可以以后计算从复制的范围内。valueArray，并使用正确的信息

I'm trying to find a method that I can tell: I want to find 'look4it' in 'valueArray', and that method's answer would be A22164:E22164 so I can after that calculate the range to copy from 'valueArray' and use the correct info.

我试过Range.Find但它似乎是寻找一个范围内唯一的一块信息，并返回该地址，但我发现没有什么可以让我看起来为另一个人以内的范围的内容。

I've tried Range.Find but it seems to be that looks for a unique piece of info within a Range, and returns the address, but I've found nothing that allows me to looks for a Range's content within another one.

任何帮助表示赞赏！

推荐答案

我想我会做什么，在这种情况下，首先构建由每个头和它的位置的查找表。对于列表中的关键，我会串连标题单元格在一起，使一个单一的查询值。所以，你的代码后紧跟的：

I think what I would do in this scenario is first construct a look-up list consisting of each header and its location. For the key in the list, I would concatenate the header cells together to make a single look-up value. So following on immediately after your code:

Dictionary<string, int> lookUpList = new Dictionary<string, int>();

// Loop through the rows

for (int i = 0; i < valueArray.GetLength(0); i++)
{
    // Identify the header rows

    if (valueArray[i, 0].ToString() == "WR")
    {
        // Concatenate the header strings into a single string

        string header = "";

        for (int j = 0; j < valueArray.GetLength(1); j++)
        {
            header += valueArray[i, j].ToString();
        }

        // Store the header location

        lookUpList.Add(header, i + 1);
    }
}

请记住，这是即兴，我的天堂 ŧ进行了测试。我不知道究竟是怎么 Range.get_Value 方法的地方数据放入数组，这样的尺寸可能是周围的错误的方式。我也正在做关于如何在电子表格中根据您所提供的数据，在图像上确定标题行假设。

Bear in mind that this is extempore and I haven't tested it. I'm not sure exactly how the Range.get_Value method places data into the array, so the dimensions might be the wrong way around. Also I'm making assumptions about how to identify header rows in your spreadsheet based on the image of the data you supplied.

无论如何，一旦构造可以执行简单的外观到提取头的行号：

Anyway, once constructed you can perform a simple look up to extract the row number of the header:

string userSelectedHeader = "concatenation of whatever the user selected";
int rowNumber = lookUpList[userSelectedHeader];

这是否在一定程度上帮助回答你的问题？

Does that go some way to helping answer your question?

这篇关于有没有办法找到Excel中的一个范围内的范围是多少？的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！