0

I made the following code to load data from excel files to present it in a datagridview table, and it work fine, but it takes a long time to load data from big files (contains about 330,000 rows X 20 columns) and the loading process is not completed.

So I am looking for a faster way to load big size data to the datagridview table.

private void Load_Click(object sender, EventArgs e)
{
   DataGrid_1.datasource = null;
   DataGrid_1.datasource = Get_Data_Excel("C:\Test.xls","Select * from [Sheet1$]");
}

public static DataTable Get_Data_Excel(string File_Pth, string Qrr)
{
    OleDbConnection con = new OleDbConnection(
        "provider=Microsoft.ACE.OLEDB.12.0;data source="
            + File_Pth
            + ";Extended Properties=Excel 12.0;");

    StringBuilder stbQuery = new StringBuilder();

    stbQuery.Append(Qrr);

    OleDbDataAdapter adp = new OleDbDataAdapter(stbQuery.ToString(), con);

    DataSet dsXLS = new DataSet();
    adp.Fill(dsXLS);

    return dsXLS.Tables[0];
}
Uwe Keim
  • 39,551
  • 56
  • 175
  • 291
Doicare
  • 361
  • 1
  • 3
  • 15
  • How about using a background worker and doing things asynchronously? – Uwe Keim Sep 17 '15 at 11:43
  • 4
    This question has been asked and answered before. Please try searching a bit in the futre. http://stackoverflow.com/questions/17577184/importing-excel-into-a-datatable-quickly – SpaceSteak Sep 17 '15 at 11:51
  • This may solve the freezing form issue, but still it will take a long time to be loaded i think. – Doicare Sep 17 '15 at 11:51
  • @SpaceSteak Your linked question uses PIA, whereas this question uses OleDB. – Uwe Keim Sep 17 '15 at 13:36
  • This is dependant upon how much data you are requesting/query. how efficient is your query? how big is the excel file? all these things matter. putting it on a different thread certainly will not help perf issues. The illusion that it may help but really it doesn't. it just unlocks the main thread – Ahmed ilyas Sep 17 '15 at 13:36
  • 1
    Thanks @UweKeim, I was just going to say this. – Doicare Sep 17 '15 at 13:40
  • @SpaceSteak, is it possible to remove the linked post as I don't get any help because of the duplication note u added to my question. – Doicare Sep 18 '15 at 09:11

0 Answers0