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?

score 2 · Accepted Answer · answered Dec 11 '15 at 10:47

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);

sorry about the rookie mistake man im just an intern and new to this language, and my supervisor is not around. thanks alot! really helped me! — evil.toaster, Dec 11 '15 at 11:22

LinQtoExcel using asp fileupload

1 Answers1