Excel导入数据库是指将Excel文件中的数据导入到数据库中。在ASP.NET中,这通常涉及到文件上传、数据解析和数据库操作。
EPPlus
)解析Excel文件中的数据。以下是一个简单的示例,展示如何将Excel文件中的数据导入到SQL Server数据库中。
Install-Package EPPlus
<%@ Page Language="C#" AutoEventWireup="true" CodeBehind="Upload.aspx.cs" Inherits="ExcelToDatabase.Upload" %>
<!DOCTYPE html>
<html xmlns="http://www.w3.org/1999/xhtml">
<head runat="server">
<title>Excel Upload</title>
</head>
<body>
<form id="form1" runat="server" enctype="multipart/form-data">
<div>
<asp:FileUpload ID="FileUpload1" runat="server" />
<asp:Button ID="btnUpload" runat="server" Text="Upload" OnClick="btnUpload_Click" />
</div>
</form>
</body>
</html>
using System;
using System.Data;
using System.Data.SqlClient;
using System.IO;
using OfficeOpenXml;
namespace ExcelToDatabase
{
public partial class Upload : System.Web.UI.Page
{
protected void Page_Load(object sender, EventArgs e)
{
}
protected void btnUpload_Click(object sender, EventArgs e)
{
if (FileUpload1.HasFile)
{
string filePath = Path.Combine(Server.MapPath("~/Uploads"), FileUpload1.FileName);
FileUpload1.SaveAs(filePath);
using (ExcelPackage package = new ExcelPackage(new FileInfo(filePath)))
{
ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
DataTable dataTable = new DataTable();
// Assuming the first row contains column headers
for (int i = 1; i <= worksheet.Dimension.End.Row; i++)
{
DataRow row = dataTable.NewRow();
for (int j = 1; j <= worksheet.Dimension.End.Column; j++)
{
if (i == 1)
{
dataTable.Columns.Add(worksheet.Cells[i, j].Value.ToString());
}
else
{
row[j - 1] = worksheet.Cells[i, j].Value;
}
}
if (i > 1)
{
dataTable.Rows.Add(row);
}
}
// Insert data into SQL Server database
using (SqlConnection connection = new SqlConnection("YourConnectionString"))
{
connection.Open();
foreach (DataRow row in dataTable.Rows)
{
string query = "INSERT INTO YourTable (Column1, Column2, Column3) VALUES (@Column1, @Column2, @Column3)";
using (SqlCommand command = new SqlCommand(query, connection))
{
command.Parameters.AddWithValue("@Column1", row["Column1"]);
command.Parameters.AddWithValue("@Column2", row["Column2"]);
command.Parameters.AddWithValue("@Column3", row["Column3"]);
command.ExecuteNonQuery();
}
}
}
}
}
}
}
}
通过以上步骤和示例代码,你可以实现将Excel文件中的数据导入到ASP.NET应用程序的数据库中。
领取专属 10元无门槛券
手把手带您无忧上云