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:
while( lRowIndex < lSheet.Rows.Count ){
String lText = (( Excel.Range )(lSheet.Cells[lRowIndex, 1])).Text.ToString(); int lKey; if( int.TryParse( lText, out lKey ) ){
String[] lDataRow = new String[lColHeaders.Count];lDataRow[0] = lText;
for( int lColIndex = 2; lColIndex < lColHeaders.Count; lColIndex++ ){
lDataRow[lColIndex - 1] = (( Excel.
Range )(lSheet.Cells[lRowIndex, lColIndex])).Text.ToString();}
lMyTableObject.Set( lKey, lDataRow );
lRowIndex++;
}
else{
break;}
}
How can I speed this up. Thanks,
~S
How can I speed this up: excel spreadsheet data loading
TPM01
public DataTable GetDataTableFromExcel( string filename, string sheetName )
{
OleDbConnection dbConn = null;
DataTable resultTable = new DataTable( sheetName );
try
{
// Build connection string.
string connString = "Provider=Microsoft.Jet.OLEDB.4.0;" +
"Data Source=" + filename + ";Extended Properties=Excel 8.0;";
// Create connection and open it.
dbConn = new OleDbConnection( connString );
dbConn.Open();
if ( !sheetName.EndsWith( "$" ) )
{
sheetName += '$';
}
string query = string.Format( "SELECT * FROM [{0}]", sheetName );
using ( OleDbDataAdapter adapter = new OleDbDataAdapter( query, dbConn ) )
{
adapter.Fill( resultTable );
}
return resultTable;
}
finally
{
if ( dbConn != null )
{
dbConn.Close();
dbConn.Dispose();
}
}
}
Shimon273
yes for that you can have the data's in datatable, and you can directly bind to excel sheet using method specified in the the previous mail. it will speed up the data loading
-thanks
Tim1
Hi you can use the XMap's it is nothing but dataset binding with the worksheet.
see the following methods and properties of the workbook, to bind and get the data back from the work sheet.
workbook.XmlImportXml
workbook.XmlMaps
some related links are
http://msdn2.microsoft.com/en-us/library/microsoft.office.tools.excel.workbook.xmlimportxml(VS.80).aspx
http://msdn.microsoft.com/library/default.asp url=/library/en-us/vbaxl11/html/xlmthXmlImportXml_HV01040130.asp
http://www.thescripts.com/forum/thread418854.html
Thanks
Knoen
Instead of loading cell by cell, I load in a range of 100 rows at a time, this sped things up byt about 100 fold. From about 6 minutes to 30 seconds.
~S