Slow Cell Access in Excel

  • Thread starter Thread starter =?iso-8859-1?Q?Michel_M=FCller?=
  • Start date Start date
?

=?iso-8859-1?Q?Michel_M=FCller?=

Is there a faster way to access cells in excel with c#?
Here is a codesample how i access the cells in excel to get the values.

I did the same before with vb and it was 100-time faster than now! As a found out the most expensive functions are the following two lines.

tempRange = (Excel.Range)dataRange.Cells[z,i];

testValue = tempRange.get_Value(Type.Missing);



Here is the for loop through the excelsheet.
for(int z=intStartRow; z<intRowCount+1; z++ )

{ intLineNumber = z;

newRow = rteTable.NewRow();

for(int i=1; i<intColumnCount+1; i++ )

{ testValue = "dd";

// //testValue = ((Excel.Range)dataRange.Cells[z,i]).get_Value(Type.Missing);

tempRange = (Excel.Range)dataRange.Cells[z,i];

testValue = tempRange.get_Value(Type.Missing);

if(testValue != null)

{

theValues = GetPreparedString(testValue.ToString());

}

else

{

theValues = "";

}


//Fill the Columns

newRow[i-1] = theValues;

}



Thank's for ideas.

Michel
 
Michel,

There isn't much you are going to be able to do in order to set the
values. After all, you have to perform the cast (which is really a call to
QueryInterface under the covers) and then set the value, and all of this is
done through COM interop.

You might want to check using the PIA for Office, they might be slightly
faster. Also, there is a property on the Application called ScreenUpdating
which you can set to false, so that Excel doesn't waste time updating the
screen while the values change (you have to set it to true at the end, and
it will do one update).

Hope this helps.


--
- Nicholas Paldino [.NET/C# MVP]
- (e-mail address removed)

Is there a faster way to access cells in excel with c#?
Here is a codesample how i access the cells in excel to get the values.

I did the same before with vb and it was 100-time faster than now! As a
found out the most expensive functions are the following two lines.

tempRange = (Excel.Range)dataRange.Cells[z,i];
testValue = tempRange.get_Value(Type.Missing);

Here is the for loop through the excelsheet.
for(int z=intStartRow; z<intRowCount+1; z++ )
{ intLineNumber = z;
newRow = rteTable.NewRow();
for(int i=1; i<intColumnCount+1; i++ )
{ testValue = "dd";
// //testValue =
((Excel.Range)dataRange.Cells[z,i]).get_Value(Type.Missing);
tempRange = (Excel.Range)dataRange.Cells[z,i];
testValue = tempRange.get_Value(Type.Missing);
if(testValue != null)
{
theValues = GetPreparedString(testValue.ToString());
}
else
{
theValues = "";
}
//Fill the Columns
newRow[i-1] = theValues;
}

Thank's for ideas.
Michel
 
Back
Top