This is my code and it runs but when i click the import button it Gives me the error "Object reference not set to an instance of an object."
i don't know what the problem is plz help.i did add the microsoft.office.interop.excel reference.
using
System;
using System.Collections.Generic;
using
System.ComponentModel;
using
System.Data;
using
System.Drawing;
using
System.Xml.Linq;
using
System.Text;
using
System.Windows.Forms;
using
System.Data.SqlClient;
using
System.Data.SqlTypes;
using
Microsoft.Office.Interop.Excel;
using
TimetableSchedDAC;
namespace
ImportStudent
{
public partial class frmImportStudents : Form
{
private Microsoft.Office.Interop.Excel.Application ExcelObj = null;
string envChecker;
TimetableSchedDAL studentdal;
public frmImportStudents()
{
InitializeComponent();
}
public frmImportStudents(TimetableSchedDAL studObj,string EnvChecker)
{
studentdal = studObj;
envChecker = EnvChecker;
InitializeComponent();
ExcelObj =
new Microsoft.Office.Interop.Excel.Application();
if (ExcelObj == null)
{
MessageBox.Show("ERROR: EXCEL couldn't be started!",
"Error : Starting Excel", MessageBoxButtons.OK, MessageBoxIcon.Error);
System.Windows.Forms.
Application.Exit();
}
ExcelObj.Visible =
true;
}
private void btnBrowse_Click(object sender, EventArgs e)
{
lblResults.Visible =
true;
this.ofd.FileName = "*.xlsx";
if(this.ofd.ShowDialog() == DialogResult.OK)
{
btnImport.Enabled =
true;
}
lblResults.Text =
"File successfully loaded!!";
}
private void frmImportStudents_Load(object sender, EventArgs e)
{
lblResults.Visible =
false;
btnImport.Enabled =
false;
}
private void btnImport_Click(object sender, EventArgs e)
{
lblResults.Visible =
true;
ImportToStudent();
}
public void ImportToStudent()
{
try
{
string filename = ofd.SafeFileName;
string connection = @"Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + "E:\\..\\PSAtechZTimetableSystem\\Spread.xlsx" + "; 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 workbook
Microsoft.Office.Interop.Excel.
Sheets sheets = theWorkbook.Worksheets;
// get the first and only worksheet from the collection of worksheets
Microsoft.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 [Student_Number]," + "[ModuleCode] AS [Module_Code], " + "FROM [" + worksheet.Name.ToString() + "$]";
//Open the Excel Connection
conPSAtechZ.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);
}