0
            OleDbConnection connection;
            OleDbCommand command;
   

            string commandText = "SELECT pali,sinhala FROM [Sheet3$]";
            string oledbConnectString = "Provider=Microsoft.ACE.OLEDB.12.0;" +
            @"Data Source=Book2.xlsx;" +
            "Extended Properties=\"Excel 12.0;HDR=YES\";";
            connection = new OleDbConnection(oledbConnectString);
            command = new OleDbCommand(commandText, connection);
            OleDbDataAdapter da = new OleDbDataAdapter(command);
            connection.Close();
            DataTable dt = new DataTable();
            da.Fill(dt);
            dataGridView1.DataSource = dt;

Excel sheet have 20 000 rows(UTF8 unicode). even my Lap 8gb ram i3 it takes like 8secs to load. Is their any way to store these data? I want to create a Dictionary(not csharp dictionary).

Hashan
  • 178
  • 8
  • What happened when you tried to profile the code? What happened when you tried using a smaller input (maybe it takes a long time to set up the OLE stuff, and the size of the data doesn't matter very much)? – Karl Knechtel Jul 24 '21 at 12:23
  • Did you measure what is the slow part? Is it the excel reading code or filling a DataGridView with 20000 records? – Steve Jul 24 '21 at 12:24
  • up to da.Fill(dt); is going ok. – Hashan Jul 24 '21 at 12:27
  • da.Fill(dt); this is the slow part. – Hashan Jul 24 '21 at 12:29
  • you saying text file is better than excel file? – Hashan Jul 24 '21 at 12:33
  • no please tell me a better way – Hashan Jul 24 '21 at 12:36
  • 1
    I literally told you a better way. Put the entries in JSON and read it from there rather than Excel. How long does it take? – mjwills Jul 24 '21 at 12:37
  • @mjwills it takes time to serialize – Hashan Jul 25 '21 at 02:04
  • How _long_ does it take to serialise? Clearly it will take time. It isn't magic. How _long_ does it take? – mjwills Jul 25 '21 at 02:22
  • takes time and error 'Invalid JSON primitive: – Hashan Jul 25 '21 at 02:49
  • OK, so the file isn't a valid JSON file. You need to make it a valid JSON file. – mjwills Jul 25 '21 at 06:05
  • @mjwills yes it is fast.less than a sec it came. now I have to display one column only in datagrideview and need to search objects by WHERE LIKE from deserialized object list. – Hashan Jul 25 '21 at 07:57
  • Go back and check the earlier suggestion I made in the earlier question. Or read https://stackoverflow.com/a/38005258/34092 . – mjwills Jul 25 '21 at 08:05
  • @mjwills to load from json to app takes like 1.5 secs. others are totally ok. I did the linq query correctly. every thing ok. thanks alot for your help mjwills. I also have to check with low performance PC too.again thank alot mjwills. – Hashan Jul 25 '21 at 13:52

0 Answers0