如何将Excel UsedRange的内容复制到数组中以更快地读取? [英] How to copy contents of Excel UsedRange into an array for faster reading?

问题描述

我正在尝试提高从Excel工作表中逐行读取单元格的应用程序的性能.我找到了解决方法

I'm trying to improve the performance of an application that is reading cells from an Excel worksheet row by row. I found this solution

https://stackoverflow.com/a/25842904/1462656

您要执行一次操作:

object[,] objectArray = cSheet.get_Range("A1:C4").Value2; 
dataRange.Value2 = objectArray;

是否假设所有单元格均为Value2类型?

Is it assuming all the cells are of type Value2?.

通常，我想知道如何将UsedRange的内容放入本地数组以进行更快的访问(而不是通过互操作来回移动).但是我事先不知道大小，但是我事先知道每列的单元格类型.它们是Value2和Formula的组合.

In general I would like to know how to get the contents of the UsedRange into a local array for faster access (instead of keep going back and forth through the interop). But I don't know in advance the size, but I know in advance the cell types on each column. They are a combination of Value2 and Formula.

到目前为止我的代码

        Application application = new Application();
        application.Workbooks.Open(file);
        Workbook workbook = application.Workbooks.get_Item(1);
        Worksheet worksheet = workbook.Worksheets.get_Item(1);

        Range UsedRange = worksheet.UsedRange;
        int rows = UsedRange.Rows.Count;
        int cols = UsedRange.Columns.Count;

        object[,] objectArray = new object[rows,cols];

我不知道下一步该怎么做.

I don't know what to do next.

我正要这样做

objectArray = (object[,])UsedRange.Range[?,?];

但是我不知道使用上面发现的列数和行数来指定范围的语法.

But I don't know the syntax to specify the Range using the columns count and rows count I found above.

最初我有这样的for循环，这非常慢

Originally I had a for loop like this which was extremely slow

for (int rowIndex = 2; rowIndex < UsedRange.Rows.Count; rowIndex++){

    string str1= UsedRange.Rows.Cells[rowIndex, 0 + 1].Value2.ToString();
    string str2= UsedRange.Rows.Cells[rowIndex, 1 + 1].Formula as string;

}

推荐答案

我无法像其他解决方案一样将范围指定为单元格值

I could not specify the range as cell values as in the other solutions for example

shtName.get_Range("A1:Z100")

所以我所缺少的是一种无需在上面输入单元格范围的情况下指定使用范围的方法，而我从此示例中找到了答案

So what I was missing was a way to specify the used range without entering cell range as above, and I found the answer from this example

https://www.dotnetperls.com/excel-vbnet

        Range UsedRange = worksheet.UsedRange;

        object[,] objectArray = (object[,])UsedRange.Value[XlRangeValueDataType.xlRangeValueDefault];

        for (int row = 2; row < objectArray.GetUpperBound(0); row++)
        {
             string str1= (objectArray[row, 1] == null) ? string.Empty : objectArray[row, 1].ToString();

             string str2= (objectArray[row, 2] == null) ? string.Empty : objectArray[row, 2].ToString();

             //...etc
        }

这篇关于如何将Excel UsedRange的内容复制到数组中以更快地读取?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！