0

I have a problem with large amounts of excel data and need to display it in the datagrid, if the data is small this code works but is not effective for large amounts of data and has to wait a long time. is there a brilliant way to solve this problem?

this code

        Stopwatch sw1 = Stopwatch.StartNew();
        String path = "D:\\test.xlsb";
        String sheet = "Sheet1";
        String constr = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" +
                        path+
                        ";Extended Properties='Excel 12.0 XML;HDR=YES;';";

        OleDbConnection con = new OleDbConnection(constr);
        OleDbCommand oconn = new OleDbCommand("Select * From [" + sheet + "$]", con);
        try
        {
            con.Open();

            OleDbDataAdapter sda = new OleDbDataAdapter(oconn);
            DataTable data = new DataTable();
            sda.Fill(data);
            datagrid1.ItemsSource = (System.Collections.IEnumerable)data.DefaultView;
            con.Close();
            sw1.Stop(); 
            Console.WriteLine("Time taken for excel roots: {0} ms", sw1.Elapsed.TotalMilliseconds);
        }
        catch (Exception ex)
        {
            Console.WriteLine(ex.Message);
        }
Robby Ramadhan
  • 31
  • 5
  • just add Pagination... you can find some examples how to do it... like this https://stackoverflow.com/questions/2825771/how-can-we-do-pagination-in-datagridview-in-winform – demo Feb 24 '21 at 11:34
  • if me change query for search like "Select * From [" + sheet + "$] Where nik like '% bla bla that very slow looking for of 230 thousand record – Robby Ramadhan Feb 24 '21 at 12:37
  • Didn't get what you said. Ok. Do you have all records in 1 sheet? And with pagination I didn't mean use like %%... but order your item, select top N elements for 1st page... for next pages skipp N * page number and select next N items... I hope you got idea in general – demo Feb 24 '21 at 13:22

0 Answers0