c# - LinQtoExcel 使用 asp fileupload

Question

我在使用此功能时遇到问题。所以基本上用户会上传一个 excel 文件 .xls(2003 版)，然后单击导入按钮后，它将读取 excel 文件并将其导入到 sql 数据库中。

这是我的代码

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

我总是收到这个错误

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

有人可以帮我吗？

Answer 1

您的错误消息不言自明。错误在这一行:-

var excelFile = new ExcelQueryFactory(pathToExcelFile);

ExcelQueryFactory 需要完整的文件路径，但您只是使用 string pathToExcelFile = filebiometrics.FileName; 传递 excel 文件名，显然它无法读取文件.

你需要读取用户上传的excel文件，保存到服务器，然后这样读取:-

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