How can I get information from excel faster with c#?

Question

I transfer excel files of different sizes(from 8 kB to 12849kB) to datagridview with c#. But the information comes very late. What can be done to speed this up?

Thanks.

Answer 1

@Glcn , Welcome to Microsoft Q&A, I noticed that you used a mix of two methods to display the data in excel in datagirdview. Actually, we only used one method to get what you wanted.

Here is a code example you could refer to.

private void button1_Click(object sender, EventArgs e)  
    {  
        OpenFileDialog openFileDialog1 = new OpenFileDialog();  
        openFileDialog1.Filter = "Text files| *.xlsx";  
        if (openFileDialog1.ShowDialog() == DialogResult.OK)  
        {  
            string path = openFileDialog1.FileName;  
            using (OleDbConnection conn = new OleDbConnection())  
            {  
                DataTable dt = new DataTable();   
                conn.ConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path + ";Extended Properties='Excel 12.0 Xml;HDR=YES;IMEX=1;MAXSCANROWS=0'";   
                using (OleDbCommand comm = new OleDbCommand())  
                {  
                    comm.CommandText = "Select * from [" + "Sheet1" + "$]";   
                    comm.Connection = conn;   
                    using (OleDbDataAdapter da = new OleDbDataAdapter())  
                    {  
                        da.SelectCommand = comm;   
                        da.Fill(dt);   
                        dataGridView1.DataSource = dt;  
                    }  
                }  
            }  
        }  
    }  

Best Regards,
Jack

---

If the answer is the right solution, please click "Accept Answer" and upvote it.If you have extra questions about this answer, please click "Comment".

Note: Please follow the steps in our documentation to enable e-mail notifications if you want to receive the related email notification for this thread.

Answer 2

If this is a non commercial app consider using EPPlus. Otherwise, check out the code here which uses no libraries.

Setup before using EPPlus

ExcelPackage.LicenseContext = LicenseContext.NonCommercial;

Then the following code reads a WorkSheet into a DataTable.

public static DataTable ReaDataTable(string fileName, string workSheetName = "")  
{  
    FileInfo existingFile = new(fileName);  
    using ExcelPackage package = new(existingFile);  
  
    // by default EPPlus reads the first worksheet,  
    // the follow specifies which sheet to read if you don't want the first sheet.  
    if (!string.IsNullOrWhiteSpace(workSheetName))  
    {  
        ExcelWorksheet worksheet = package.Workbook.Worksheets[workSheetName];  
    }  
      
    var dataTable = ExcelPackageToDataTable(package);  
  
    return dataTable;  
  
}  

Answer 3

    DataTable dt;  
      
    OleDbConnection conn= new OleDbConnection(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+ Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName) + "\\Signal.xlsx; Extended Properties='Excel 12.0 Xml;HDR=YES'");  

    OleDbCommand command;  
    OleDbDataReader datareader;  


    public void ÅpneToolStripMenuItem_Click(object sender, EventArgs e)  
    {  

        if (conn.State != ConnectionState.Open) conn.Open();  
        OpenFileDialog openFileDialog1 = new OpenFileDialog();  

        openFileDialog1.Filter = "Text files| *.csv";  
        if (openFileDialog1.ShowDialog() == DialogResult.OK)  
        {  
            SelectFilePath = openFileDialog1.FileName;  
            SelectFileName = openFileDialog1.SafeFileName;  

            ExcelApp.Workbook excelBook = excelApp.Workbooks.Open(SelectFilePath);  
            ExcelApp._Worksheet excelSheet = excelBook.Sheets[1];  
            ExcelApp.Range excelRange = excelSheet.UsedRange;  

            //delete combobox items  
            if (comboBox1VenstreYAkse.Items.Count != 0)  
            {  
                comboBox1VenstreYAkse.Items.Clear(); comboBox1VenstreYAkse.Text = "";  
                comboBox2VenstreYAkse.Items.Clear(); comboBox2VenstreYAkse.Text = "";  
                comboBox3VenstreYAkse.Items.Clear(); comboBox3VenstreYAkse.Text = "";  
                comboBox4VenstreYAkse.Items.Clear(); comboBox4VenstreYAkse.Text = "";  
                comboBox5VenstreYAkse.Items.Clear(); comboBox5VenstreYAkse.Text = "";  
                comboBox6HøyreYAkse.Items.Clear(); comboBox6HøyreYAkse.Text = "";  
                comboBox7HøyreYAkse.Items.Clear(); comboBox7HøyreYAkse.Text = "";  
                comboBox8HøyreYAkse.Items.Clear(); comboBox8HøyreYAkse.Text = "";  
                comboBox9HøyreYAkse.Items.Clear(); comboBox9HøyreYAkse.Text = "";  
                comboBox10HøyreYAkse.Items.Clear(); comboBox10HøyreYAkse.Text = "";  
            }  
           
            nmbrRows = excelRange.Rows.Count; //Gets the number of rows of the page.  
            nmbrColumns = excelRange.Columns.Count;//Gets the number of columns of the page.  
            dt = ToDataTable(excelRange, nmbrRows, nmbrColumns);  
            dataGridView1.DataSource = dt;  
            dataGridView1.Refresh();  
            excelApp.Quit();  
            System.Runtime.InteropServices.Marshal.ReleaseComObject(excelApp);  
        }  
        else  
        {  
            MessageBox.Show("");  
        }  
    }  
   
        public DataTable ToDataTable(ExcelApp.Range range, int rows, int cols)  
        {  
            string colsname1 = "";  
            string colsname2 = "";  
            string[] Header = new string[cols - 1];  


            DataTable table = new DataTable();  
            for (int i = 2; i <= rows; i++)  
            {  
                if (i == 2)  
                { //record second lines them as columnsname.  
                    for (int j = 1; j <= cols; j++)  
                    {  
                        //The content of the columns is checked for empty.  
                        if (range.Cells[i, j] != null && range.Cells[i, j].Value != null)  
                        {  
                            colsname1 = range.Cells[i, j].Value.ToString().Trim();  
                            colsname2 = range.Cells[i + 1, j].Value.ToString().Trim();  
                            colsname = colsname1 + ":" + colsname2;  
                            command = new OleDbCommand("Select AB From [Signal$] Where Engelsk = '" + colsname + "'", conn);  
                            datareader = command.ExecuteReader();  
                            datareader.Read();  
                            colsname = datareader[0].ToString();  
                            table.Columns.Add(colsname);  
                            if (j == 1) j++;  
                            Header[j - 2] = colsname;  
                            continue;  
                        }  

                        if (range.Cells[i, j].Value == null)  
                        {  
                            colsname2 = range.Cells[i + 1, j].Value.ToString().Trim();   
                            colsname = colsname1 + ":" + colsname2;  
                            command = new OleDbCommand("Select AB From [Signal$] Where Engelsk = '" + colsname + "'", conn);  
                            datareader = command.ExecuteReader();  
                            datareader.Read();  
                            colsname = datareader[0].ToString();  
                            table.Columns.Add(colsname);  
                            Header[j - 2] = colsname;  
                        }  

                    }  
                    i++;  
                    continue;  
                }  

             
                // To write data side by side  


                string[] satir = new string[cols - 1];  

                // iterate over all columns to fill the row  

                Loggerdate = range.Cells[i, 1].Value.ToShortDateString();  
                Loggertime = DateTime.FromOADate(range.Cells[i, 2].Value).TimeOfDay;  
                dateandtime = Loggerdate + " " + Loggertime;  

                if (i == 4) { label6.Text = dateandtime; label6.Visible = true; }  

                if (i == rows) { label7.Text = dateandtime; label7.Visible = true; }  

                satir[0] = dateandtime;  

                for (int j = 1; j < cols - 1; j++)  
                {  
                    if (range.Cells[i, j + 2] != null && range.Cells[i, j + 2].Value != null)  
                    { satir[j] = range.Cells[i, j + 2].Value.ToString(); }  
                    else // To prevent the contents from failing in a blank cell  
                        satir[j] = String.Empty;  
                }  

                //add the current row to the DataTable             
                table.Rows.Add(satir);  
            }  

            // fill in combobox  

            for (int c = 1; c < Header.Length; c++)  
            {  
                comboBox1VenstreYAkse.Items.Add(Header[c]);  
                comboBox2VenstreYAkse.Items.Add(Header[c]);  
                comboBox3VenstreYAkse.Items.Add(Header[c]);  
                comboBox4VenstreYAkse.Items.Add(Header[c]);  
                comboBox5VenstreYAkse.Items.Add(Header[c]);  
                comboBox6HøyreYAkse.Items.Add(Header[c]);  
                comboBox7HøyreYAkse.Items.Add(Header[c]);  
                comboBox8HøyreYAkse.Items.Add(Header[c]);  
                comboBox9HøyreYAkse.Items.Add(Header[c]);  
                comboBox10HøyreYAkse.Items.Add(Header[c]);  
            }  
            return table;  
        
    }