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
You want to do it in one operation:
object[,] objectArray = cSheet.get_Range("A1:C4").Value2;
dataRange.Value2 = objectArray;
Is it assuming all the cells are of type Value2?.
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.
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 don't know what to do next.
I was going to do this
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.
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
}
Collected from the Internet
Please contact [email protected] to delete if infringement.
Comments