How to copy Excel UsedRange content into array for faster reading?

I am trying to improve the performance of an application that reads cells from an Excel spreadsheet row by row. I found this solution

stack overflow

You want to do it in one operation:

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


Are all cells assumed to be of type Value2 ?.

In general, I would like to know how to get the contents of the UseRange in a local array for faster access (instead of going back and forth through interop). But I do not know the size in advance, but I know in advance the types of cells on each column. They are a combination of Value2 and Formula.

My code so far

        Application application = new Application();
        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 do not know what to do next.

I was going to do this

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


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

I originally had a for loop which was very 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;




source to share

2 answers

It's all about doing an Excel operation in one step and then using .Net object[,]

. For example:

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


I do not know what to do next.

object[,] objectArray = shtName.get_Range("A1:Z100").Value2;

for (int row = 0; row < objectArray.GetLength(0); row++)
    for (int col = 0; col < objectArray.GetLength(1); col++)


ps I haven't tested this, very busy today

Every time you call Excel, there is a basic RPC call that gets sorted.

This will also help: / ...



I could not specify the range as cell values ​​as in other solutions like



So what I was missing is a way to specify the range to use without entering a range of cells as above and I found the answer from this example

        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();





All Articles