How can I speed this up: excel spreadsheet data loading

[EDN Admin]

I am iterating over data, in an excel spreadsheet, and loading it into memory.
My current method for doing this is extremely slow on large (~12,000 rows) tables: <font size=2></font><font size=2>
</font><font color="#0000ff" size=2>while</font><font size=2>( lRowIndex < lSheet.Rows.Count )
{
</font><font color="#008080" size=2>String</font><font size=2> lText = (( Excel.</font><font color="#008080" size=2>Range</font><font size=2> )(lSheet.Cells[lRowIndex, 1])).Text.ToString();
</font><font color="#0000ff" size=2>int</font><font size=2> lKey;
</font><font color="#0000ff" size=2>if</font><font size=2>( </font><font color="#0000ff" size=2>int</font><font size=2>.TryParse( lText, </font><font color="#0000ff" size=2>out</font><font size=2> lKey ) )
{
</font><font color="#008080" size=2>String</font><font size=2>[] lDataRow = </font><font color="#0000ff" size=2>new</font><font size=2> </font><font color="#008080" size=2>String</font><font size=2>[lColHeaders.Count];
lDataRow[0] = lText;
</font><font color="#0000ff" size=2>for</font><font size=2>( </font><font color="#0000ff" size=2>int</font><font size=2> lColIndex = 2; lColIndex < lColHeaders.Count; lColIndex++ )
{
lDataRow[lColIndex - 1] = (( Excel.</font><font color="#008080" size=2>Range</font><font size=2> )(lSheet.Cells[lRowIndex, lColIndex])).Text.ToString();
}
lMyTableObject.Set( lKey, lDataRow );
lRowIndex++;
}
</font><font color="#0000ff" size=2>else </font><font size=2>
{
</font><font color="#0000ff" size=2>break</font><font size=2>;
}
}
How can I speed this up.  Thanks,

~S</font>

View the full article