Using C# to read mass data from Excel 2003

[zlf]

Hi all£º
I am using C# to read data from Excel 2003, However, I found the method
I taken is too inefficient. Read a 250X250 sized matrix will take several
minutes. May u provide me some high efficient method to solve mass data
reading problem£¿Thanks


int ColNum = mySheet.UsedRange.Columns.Count;
int RowNum = mySheet.UsedRange.Rows.Count;

Range range = mySheet.get_Range(mySheet.Cells[1, 1],
mySheet.Cells[RowNum, ColNum]);
...
for (int i = 1; i <= RowNum; ++ i)
{
DataRow dr;
dr = dt.NewRow();

for (int j = 1; j <= ColNum; ++ j)
{
dr[j - 1] = ((Range) range.get_Item(i, j)).Text.ToString();
}
dt.Rows.Add((dr));
}


zlf

 

[Christophe Niel]

Try avoiding unnessecary operation like '.Text.ToString()' (dunno it the
CRL Compiler will know how to not convert text into text!!)
and use the direct coordinates of the cells without using range (I always
found that using a range object instead off a Cells was a bit longer)

With the col_offset and the row_offset you can manage the begining of your
range (not always "A1")

Col_Offset = 1 // beginning of your range
Row_Offset = 1

for (int i=0; i<RowNum; i++)
{
DataRow dr;
dr = dt.NewRow();

for (int j=0; j<ColNum; j++)
{
dr[j] = mySheet.Cells(i + Row_Offset, j + Col_Offset).Value //
or .Text
}

dt.Rows.Add(dr);
}

But there is nothing magic a 250*250 matrix is really big for a DataTable (I
assume dt is a datatable) especially the 250 columns, so the problem may
come not from the excel reading part but simply from the "Rows.Add()"
method.

HTH,
Christophe