将数据从Excel文件移动到数据库 [英] Moving data from an Excel file to a database

问题描述

我是MVC ASP.NET的新手，正在尝试从Excel文件导入数据并将其加载到数据库中.

I am new to MVC ASP.NET and trying to import data from an Excel file and load it into a database.

已经使用与Excel文件中的信息匹配的列名创建了数据库.当我上传Excel文件并单击提交"时，出现错误:

The database is already created with the column names that matches the information in the Excel file. When I upload the Excel file and click submit, I get the error:

初始化字符串的格式不符合规范

format of the initialization string does not conform to specification

基于调试，故障位于以下行，目前甚至不确定其余代码是否正确:

Based on the debug the fault lies in the following line and at present not even sure if rest of code is correct:

excelConnection.Open();

查找了类似的错误问题，但答案不起作用.此部分的完整代码:

Looked up similar error issues but the answer doesn't work. Full code for this portion:

//Code at Controller and cshtml
public ActionResult Import()
    {
        return View();
    }

    public ActionResult ImportExcel()
    {
        try
        {
            if (Request.Files["FileUpload1"].ContentLength > 0)
            {
                string extension = Path.GetExtension(Request.Files["FileUpload1"].FileName);
                string path1 = string.Format("{0}/{1}", Server.MapPath("~/App_Data/uploads"), Request.Files["FileUpload1"].FileName);
                if (System.IO.File.Exists(path1))
                    System.IO.File.Delete(path1);

                Request.Files["FileUpload1"].SaveAs(path1);

                string sqlConnectionString = @"Data Source=(LocalDb)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\ExampleDB.mdf;Initial Catalog=aspnet-FormulaOne-20151105055609;Integrated Security=True";

                //Create connection string to Excel work book
                string excelConnectionString = @"Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" + path1 + ";Extended Properties=Excel 12.0;Persist Security Info=False, HDR=YES";
                //Create Connection to Excel work book
                OleDbConnection excelConnection = new OleDbConnection(excelConnectionString);
                //Create OleDbCommand to fetch data from Excel
                OleDbCommand cmd = new OleDbCommand("Select [Speed],[Average],[Power],[Comment] from [Sheet1$]", excelConnection);

                //ERROR OCCURING AT THIS LINE
                excelConnection.Open();
                OleDbDataReader dReader;
                dReader = cmd.ExecuteReader();

                SqlBulkCopy sqlBulk = new SqlBulkCopy(sqlConnectionString);
                //Give your Destination table name
                sqlBulk.DestinationTableName = "Stats";
                sqlBulk.WriteToServer(dReader);
                excelConnection.Close();

                // SQL Server Connection String
            }

            return RedirectToAction("Import");
        }

        catch (Exception e)
        {
            Console.WriteLine(e.StackTrace.ToString());
            return RedirectToAction("Import");
        }
    }
 }

@using (Html.BeginForm("Importexcel", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    <table>
        <tr><td>Excel file</td><td><input type="file" id="FileUpload1" name="FileUpload1" /></td></tr>
        <tr><td></td><td><input type="submit" id="Submit" name="Submit" value="Submit" /></td></tr>
    </table>
}

推荐答案

我认为您的问题是程序无法正确找到excel文件，我建议使用 OleDbConnection

I think that you problem is that program is not capable to find excel file correctly , I reccomend using OleDbConnection

            //path to your file
            string path = @"D:\your\path\to\excel\file.xlsx";
            // noitice that parameter HRD=YES if your file has header
            string connStr = String.Format(@"Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties=""Excel 12.0 Xml;HDR=YES""", path);


            using (OleDbConnection connection = new OleDbConnection(connStr))
                {
                   connection.Open();
                   // ensure that sheet name is correct
                   OleDbCommand command = new OleDbCommand("select * from [sheet$]", connection);
                   using (OleDbDataReader dr = command.ExecuteReader())
                       {
                          // here you can access rows and insert them respectively
                          //first column , first row
                          var name = dr[0].toString();
                          //second column , first row
                          var lastname = dr[1].toString();
                          //here you can do anything with this variables (ex insert to db)
                       }

                }

这篇关于将数据从Excel文件移动到数据库的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！