Reading of Excel File c#

Question

Hi guys! I encountered a problem is that I have difficulty reading this Excel file into data grid view as shown above. Any help will be greatly appreciated. Thanks.

These were my codes to read Excel file but there's a error that I have difficulty troubleshooting it.

private void btnSearch_Click(object sender, EventArgs e)
    {
        Excel.Workbook workbook;
        Excel.Worksheet NwSheet;
        Excel.Range ShtRange;
        Microsoft.Office.Interop.Excel.Application ExcelObj = new Microsoft.Office.Interop.Excel.Application();
        OpenFileDialog filedlgExcel = new OpenFileDialog();
        filedlgExcel.Title = "Select file";
        filedlgExcel.InitialDirectory = @"c:\";
        filedlgExcel.FileName = txtFileName.Text;
        filedlgExcel.Filter = "Excel Sheet(*.xlsx)|*.xlsx|All Files(*.*)|*.*";
        filedlgExcel.FilterIndex = 1;
        filedlgExcel.RestoreDirectory = true;
        if (filedlgExcel.ShowDialog() == DialogResult.OK)
        {
           workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName);
            NwSheet = (Excel.Worksheet)workbook.Sheets.get_Item(1);
        ShtRange = NwSheet.UsedRange; //gives the used cells in sheet
        //Reading Excel file.
        //Creating dataTable to read the containt of the Sheet in File.

        //Set header name
        for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
        {
            dt.Columns.Add(new DataColumn((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));
        }
        dt.AcceptChanges();
        //store coumn names to array
        string[] columnNames = (from dc in dt.Columns.Cast<DataColumn>() select dc.ColumnName).ToArray();

        //populate fields
        for (int Rnum = 2; Rnum <= ShtRange.Rows.Count; Rnum++)
        {
            DataRow dr = dt.NewRow();
            for (int Cnum = 1; Cnum <= ShtRange.Columns.Count; Cnum++)
            {
                dr[Cnum - 1] = (ShtRange.Cells[Rnum, Cnum] as Excel.Range).Value2.ToString();
            }
            dt.Rows.Add(dr);
            dt.AcceptChanges();
        }
        workbook.Close(true, Missing.Value, Missing.Value);
        ExcelObj.Quit();

        foreach (DataRow dr in dt.Rows)
        {
            string strEmployee = dr["Employee Name"].ToString();
            obj1 = new List<employeeschedule>();
            for (int i = 1; i < dt.Columns.Count; i++)
            {
                string period = dr[i].ToString();
                string[] split = period.Split('–');
                employeeschedule es = new employeeschedule();
                string day = columnNames[i];
                if (split[0] == "REST")
                {
                    es.day = day;
                    es.startTime = "0000";
                    es.endTime = "0000";
                    es.restDay = "Yes";
                }
                if (split[0] == "OFF")
                {
                    es.day = day;
                    es.startTime = "0000";
                    es.endTime = "0000";
                    es.restDay = "Yes";
                }
                else
                {
                    es.day = day;
                    es.startTime = split[0];
                    es.endTime = split[1];
                    es.restDay = "No";
                }
                obj1.Add(es);
            }

            dict.Add(strEmployee, obj1);
            dgvEmployeeShift.DataSource = dt;
        }
            }
        }

The error falls on this part:

 dt.Columns.Add(new DataColumn((ShtRange.Cells[1, Cnum] as Excel.Range).Value2.ToString()));

It states that "Cannot perform runtime binding on a null reference".

Answer 1

This worked for me ........... Browses your local folder , picks up the excel ,displays in grid view. Using oledb is what i suggest

private void OpenFile_Click(object sender, EventArgs e) { OpenFileDialog openfiledialog1 = new OpenFileDialog(); if (openfiledialog1.ShowDialog()==System.Windows.Forms.DialogResult.OK) { this.textBox_path.Text = openfiledialog1.FileName;

        }


    }

    private void LoadExcel_Click(object sender, EventArgs e)
    {
        //string pathConn = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+textBox_path.Text+";Extended Properties='Excel 8.0;HDR=Yes;IMEX=1'";
        string pathConn = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + textBox_path.Text + ";Extended Properties='Excel 12.0;HDR=Yes;IMEX=1'";
        OleDbConnection conn = new OleDbConnection(pathConn);

        conn.Open();
        OleDbDataAdapter mydataadapter = new OleDbDataAdapter("Select * from ["+textBox_sheet.Text+"$]", conn);
        DataTable dt = new DataTable();

        mydataadapter.Fill(dt);
        dataGridView1.DataSource = dt;

        conn.Close();
     }

Answer 2

I love using Interop when automating Excel but for your requirement why not use OleDb? It is much faster than using Interop?

TRIED AND TESTED

    private void btnSearch_Click(object sender, EventArgs e)
    {
        System.Data.OleDb.OleDbConnection MyCon ;
        System.Data.DataSet DtSet ;
        System.Data.OleDb.OleDbDataAdapter MyCommand ;

        //~~> Replace this with relevant file path
        MyCon = new System.Data.OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=C:\\Sample.xlsx;Extended Properties=\"Excel 12.0 Xml;HDR=YES;IMEX=1\"");

        //~~> Replace this with the relevant sheet name
        MyCommand = new System.Data.OleDb.OleDbDataAdapter("select * from [Sheet1$]", MyCon);

        MyCommand.TableMappings.Add("Table", "MyTable");
        DtSet = new System.Data.DataSet();

        //~~> Fill Dataset
        MyCommand.Fill(DtSet);

        //~~> Set Source
        dataGridView1.DataSource = DtSet.Tables[0];
        MyCon.Close();
    }