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 file
Path = Server.Map
Path("~/Content/" + fileName);
file.SaveAs(file
Path);
DataTable dt = Read
ExcelFile(file
Path);
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 Read
ExcelFile(
string file
Path)
{
using (
ExcelPackage package = new
ExcelPackage(new FileInfo(file
Path)))
{
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 connection
String = ConfigurationManager.Connection
Strings["DefaultConnection"].Connection
String;
using (SqlConnection connection = new SqlConnection(connection
String))
{
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>
}
```
1470
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
