Excel To DataGridView Without Using Hard Code

I'm trying to read Excel to data grid view without doing hard coding style. I faced errors while trying to do this. Can anyone help me out with it?

Excel.Workbook workbook;
        Excel.Worksheet NwSheet;
        Excel.Range ShtRange;
        Microsoft.Office.Interop.Excel.Application ExcelObj = null;
        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)
        {
            //Excel.Workbook theWorkbook = ExcelObj.Workbooks.Open(
            //   filedlgExcel.FileName, 0, true, 5,
            //    "", "", true, Excel.XlPlatform.xlWindows, "\t", false, false,
            //    0, true);
           workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName , Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);
            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 Day")
                {
                    es.day = day;
                    es.startTime = split[0];
                    es.endTime = split[0];
                    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;
        }
            }
        }

They showed me a error "Object reference not set to an instance of an object" at this part.

workbook = ExcelObj.Workbooks.Open(filedlgExcel.FileName , Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value,
                Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value, Missing.Value);

Any help will be greatly appreciated.

Answers


You set ExcelObj to null in the beginning and then never initialise it

Microsoft.Office.Interop.Excel.Application ExcelObj = null;

try making it = new Microsoft.Office.Interop.Excel.Application();


Alternately you can use OleDb provider to read Excel sheet(s).

string cnStr=@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=c:\myFolder\myExcel2007file.xlsx;Extended Properties=" + "\"Excel 12.0 Xml;HDR=YES;IMEX=1\"";
DataTable dt=new DataTable();
string sql="Select * From [Sheet1$]";
using(OleDbConnection cn=new OleDbConnection(cnstr))
{
  using(OleDbDataAdapter adapter=new OleDbDataAdapter(sql,cn))
   {
     adapter.Fill(dt);
   }
}

OleDb is provider API of ADO.NET (Database integration API of .net framework) and it exposes data from different data sources including Microsoft Access databases, or Microsoft Excel spreadsheets and many more.

Have a look at links of MSDN articles/tutorial:

  1. How To Use ADO.NET to Retrieve and Modify Records in an Excel Workbook With Visual Basic .NET
  2. How to transfer data to an Excel workbook by using Visual C# 2005 or Visual C# .NET

Need Your Help

Python 3 utf-8 encoding seem to be wrong?

python unicode python-3.x

I've messed in the past with Python 3.2 but now I face a somewhat confusing situation about utf-8 encoding in python.

Exchange server actively refuses my connection

c# asp.net exchange-server

I'm writing a ASP.NET application used within our company. Now I want to send emails to some users via a email account on our exchange server.