I am trying to read the fields in an Excel document and store the values into respective variables using Binding List and export it to Sql Server 2008 Database
Following is the code which I Found Online
public void csv() {
try
{
MyApp = new Excel.Application();
MyApp.Visible = false;
OpenFileDialog ofd = new OpenFileDialog();
ofd.FileName = "";
ofd.ShowDialog();
MyBook = MyApp.Workbooks.Open(ofd.FileName);
MySheet = (Excel.Worksheet)MyBook.Sheets[1]; // Explicit cast is not required here
var lastRow = MySheet.Cells.SpecialCells(Excel.XlCellType.xlCellTypeLastCell).Row;
BindingList<ExportExcel> EmpList = new BindingList<ExportExcel>();
for (int index = 2; index <= lastRow; index++)
{
System.Array MyValues = (System.Array)MySheet.get_Range("A" + index.ToString(), "G" + index.ToString()).Cells.Value;
EmpList.Add(new ExportExcel
{
PersonName = MyValues.GetValue(1, 1).ToString(),
Product = MyValues.GetValue(1, 2).ToString(),
StartDate = Convert.ToDateTime(MyValues.GetValue(1, 3).ToString()),
ExpiresOn = Convert.ToDateTime(MyValues.GetValue(1, 4).ToString()),
DaysIssued = MyValues.GetValue(1, 5).ToString(),
TrialsIssued = MyValues.GetValue(1, 6).ToString(),
Contact = MyValues.GetValue(1, 7).ToString()
});
query = "EXEC dbo.proc_ImportExcel'" + PersonName + "', '" + Product + "','" + StartDate + "','" + DaysIssued + "','" + ExpiresOn + "','" + TrialsIssued + "','" + Contact + "'";
}
OleDbCommand cmd = new OleDbCommand(query, con);
cmd.ExecuteNonQuery();
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}
I am getting proper values in the line PersonName = MyValues.GetValue(1, 1).ToString(), and the values are not getting stored in variable personName whereas the the values are shown in line MyValues.GetValue(1, 1).ToString(),I am getting an error object reference not set to instance of an object.
Can anyone help me to solve this issue.
Thanks