I'm using this code(see below)to import data from an excel spreadsheet into my database.now the problem is that it throws "Object reference not set to an instance of an object"exception whenever it gets to this line
Microsoft.Office.Interop.Excel.
Workbook theWorkbook = ExcelObj.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t", false, false, 0, true, true, true);what am i missing or what am i doing wrong? plz help
public void ImportToStudent()
{
try
{
//Gets the filename, not the path string filename = ofd.SafeFileName; string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\..\\PSAtechZTimetableSystem\\SpreadSheet.xls" + "; Extended Properties='Excel 8.0; IMEX=1; HDR=YES'"; // Here is the call to Open a Workbook in Excel // It uses most of the default values (except for the read-only which we set to true)Microsoft.Office.Interop.Excel.
Workbook theWorkbook = ExcelObj.Workbooks.Open(ofd.FileName, 0, true, 5, "", "", true, Microsoft.Office.Interop.Excel.XlPlatform.xlWindows,"\t", false, false, 0, true, true, true); // get the collection of sheets in the workbookMicrosoft.Office.Interop.Excel.
Sheets sheets = theWorkbook.Worksheets; // get the first and only worksheet from the collection of worksheetsMicrosoft.Office.Interop.Excel.
Worksheet worksheet = (Microsoft.Office.Interop.Excel.Worksheet)sheets.get_Item(1); using (SqlConnection conPSAtechZ = new SqlConnection(connection)){
conPSAtechZ.ConnectionString = connection;
using (SqlCommand commandExcel = conPSAtechZ.CreateCommand()){
System.Data.
DataTable table = new System.Data.DataTable();commandExcel.CommandText =
"SELECT [StudentID] AS [StudentID]," + "[ModuleCode] AS [ModuleCode], " + "FROM [Sheet1$]"; //Open the Excel ConnectionconPSAtechZ.Open();
using (SqlDataReader dr = commandExcel.ExecuteReader(CommandBehavior.CloseConnection)){
while (dr.Read() && dr.HasRows){
lblResults.Text =
"Importing..."; try{
studentdal.RegisterStudents(
new RegisterStudent(Convert.ToString(dr["StudentID"]), Convert.ToString(dr["ModuleCode"])));commandExcel.CommandType =
CommandType.StoredProcedure;lblResults.Text =
"Successfully Imported!!";}
catch (Exception Ex){
MessageBox.Show("Error!!\n " + Ex.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);}
}
dr.Close();
}
}
}
}
catch (Exception EX){
MessageBox.Show("The spreadsheet is not in the correct format!\n\n" + EX.Message, "Error", MessageBoxButtons.OK, MessageBoxIcon.Error);}
Albertto LiePosted Sep 4, 2008, 4:17 AM