LinQtoExcel using asp fileupload

Question

im having problems with this function. so basically a user will upload an excel file .xls (2003 version) then once the import button is clicked it will read the excel file and import it into the sql database.

here is my code

protected void btnImport_Click(object sender, EventArgs e)
    {
        Business.Student student = new Business.Student();
        int errorCount = 0;
        int successCount = 0;
        string successTotal;
        int missinglastname = 0;
        int missingfirstname = 0;
        int missingmiddlename = 0;
        if (filebiometrics.HasFile == false)
        {
        }
        else
        {
            string pathToExcelFile = filebiometrics.FileName;
            var excelFile = new ExcelQueryFactory(pathToExcelFile);
            IEnumerable<string> worksheetnames = excelFile.GetWorksheetNames();
            string worksheetName = excelFile.GetWorksheetNames().ToArray()[0];

            var import = from a in excelFile.Worksheet<Business.Student.StudentList>(worksheetName) select a;

            //var emptyfield = excelFile.Worksheet<Business.Employees.EmployeeImport>().Where(x => x.Surname != null).ToList();
            excelFile.AddMapping<Business.Student.StudentList>(x => x.studentnumber, "Student Number");
            excelFile.AddMapping<Business.Student.StudentList>(x => x.firstname, "Firstname");
            excelFile.AddMapping<Business.Student.StudentList>(x => x.lastname, "Lastname");
            excelFile.AddMapping<Business.Student.StudentList>(x => x.middlename, "Middlename");
            string missing = "Missing!";
            foreach (var a in import)
            {
                if (a.studentnumber == 0)
                {
                }
                if (a.lastname == null)
                {
                    a.lastname = missing;
                    missinglastname = missinglastname + 1;
                }
                if (a.firstname == "")
                {
                    a.firstname = missing;
                    missingfirstname = missingfirstname + 1;
                }

                if (a.middlename == null)
                {
                    missingmiddlename = missingmiddlename + 1;
                }
                else if (student.CheckExistingStudentNumber(a.studentnumber))
                {
                    errorCount = errorCount + 1;
                }
                else
                {
                    student.Create(a.studentnumber, a.firstname, a.lastname, a.middlename);
                    successCount = successCount + 1;
                    successTotal = "Total imported record: " + successCount.ToString();
                }
            }
            txtLog.InnerText = "Total duplicate record: " + errorCount.ToString() +
                                Environment.NewLine +
                                "Total missing data on Firstname column: " + missingfirstname.ToString() +
                                Environment.NewLine +
                                "Total missing data on Lastname column: " + missinglastname.ToString() +
                                Environment.NewLine +
                                "Total missing data on middlename column: " + missingmiddlename.ToString() +
                                Environment.NewLine +
                                Environment.NewLine +
                                "Total imported record:  " + successCount.ToString();
            filebiometrics.Attributes.Clear();
        }
    }

im always getting this error

the error is in this line 'IEnumerable worksheetnames = excelFile.GetWorksheetNames();'

can somebody help me with this?

Answer 1

Your error message is self explanatory. Error is at this line:-

var excelFile = new ExcelQueryFactory(pathToExcelFile);

ExcelQueryFactory expects the full file path but you are just passing the excel file name using string pathToExcelFile = filebiometrics.FileName; and obviously it is not able to read the file.

You need to read the excel file which user is uploading and save it to the server and then read it like this:-

string filename = Path.GetFileName(filebiometrics.FileName);
filebiometrics.SaveAs(Server.MapPath("~/") + filename);
var excelFile = new ExcelQueryFactory(Server.MapPath("~/")  + filename);