ASP.NET MVC导入excel文件到数据库功能

ASP.NET MVC导入Excel文件到数据库功能可以通过以下步骤实现：

1. 创建一个控制器和视图来上传Excel文件。

2. 使用第三方库如EPPlus来解析Excel文件并将其转换为数据表。

3. 使用ADO.NET连接到数据库并将数据表中的数据插入到数据库中。

以下是一个示例控制器和视图的代码：

控制器：

public class ExcelController : Controller
{
    public ActionResult Index()
    {
        return View();
    }

    [HttpPost]
    public ActionResult Import(HttpPostedFileBase file)
    {
        if (file != null && file.ContentLength > 0)
        {
            string fileName = Path.GetFileName(file.FileName);
            string fileExtension = Path.GetExtension(fileName);

            if (fileExtension == ".xls" || fileExtension == ".xlsx")
            {
                string filePath = Server.MapPath("~/Content/" + fileName);
                file.SaveAs(filePath);

                DataTable dt = ReadExcelFile(filePath);

                InsertDataIntoDatabase(dt);

                return RedirectToAction("Index");
            }
            else
            {
                ViewBag.Message = "Please upload a valid Excel file.";
                return View("Index");
            }
        }
        else
        {
            ViewBag.Message = "Please select an Excel file to upload.";
            return View("Index");
        }
    }

    private DataTable ReadExcelFile(string filePath)
    {
        using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
        {
            ExcelWorksheet worksheet = package.Workbook.Worksheets[1];
            DataTable dt = new DataTable();
            bool hasHeaderRow = true;

            foreach (var firstRowCell in worksheet.Cells[1, 1, 1, worksheet.Dimension.End.Column])
            {
                dt.Columns.Add(hasHeaderRow ? firstRowCell.Text : string.Format("Column {0}", firstRowCell.Start.Column));
            }

            int startRow = hasHeaderRow ? 2 : 1;

            for (int rowNum = startRow; rowNum <= worksheet.Dimension.End.Row; rowNum++)
            {
                var wsRow = worksheet.Cells[rowNum, 1, rowNum, worksheet.Dimension.End.Column];
                DataRow row = dt.Rows.Add();

                foreach (var cell in wsRow)
                {
                    row[cell.Start.Column - 1] = cell.Text;
                }
            }

            return dt;
        }
    }

    private void InsertDataIntoDatabase(DataTable dt)
    {
        string connectionString = ConfigurationManager.ConnectionStrings["DefaultConnection"].ConnectionString;

        using (SqlConnection connection = new SqlConnection(connectionString))
        {
            connection.Open();

            SqlCommand command = connection.CreateCommand();
            command.CommandType = CommandType.Text;
            command.CommandText = "INSERT INTO MyTable (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)";

            foreach (DataRow row in dt.Rows)
            {
                command.Parameters.AddWithValue("@Column1", row["Column1"]);
                command.Parameters.AddWithValue("@Column2", row["Column2"]);
                command.Parameters.AddWithValue("@Column3", row["Column3"]);

                command.ExecuteNonQuery();

                command.Parameters.Clear();
            }
        }
    }
}

视图：

@{
    ViewBag.Title = "Import Excel File";
}

<h2>Import Excel File</h2>

@using (Html.BeginForm("Import", "Excel", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
    @Html.AntiForgeryToken()

    <div class="form-group">
        <label for="file">Select Excel File:</label>
        <input type="file" name="file" id="file" />
    </div>

    <button type="submit" class="btn btn-primary">Import</button>

    <div class="alert alert-danger" role="alert">@ViewBag.Message</div>
}