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
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();
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 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
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++)
{
Debug.WriteLine(objectArray[row,col].ToString());
}
}
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: fooobar.com/questions/1744262 / ...
source to share
I could not specify the range as cell values as in other solutions like
shtName.get_Range("A1:Z100")
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
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
}
source to share