وارد کردن داده ها از فایل اکسل به SQL Server در ASP.Net MVC
با سلام در این مقاله با یک مثال توضیح خواهیم داد که چگونه می توان داده های مربوط به فایل اکسل را با استفاده از SqlBulkCopy وارد جدول داده SQL Server در ASP.Net MVC Razor کرد.
داده های فایل بارگذاری شده اکسل با استفاده از کتابخانه OLEDB خوانده می شوند و داده های خوانده شده با استفاده از SqlBulkCopy در جدول پایگاه داده SQL Server قرار می گیرند.
همانطور که از نام آن وجود دارد ، کلاس SqlBulkCopy را وارد می کند و از این رو تمام ردیف های صفحه اکسل را می توان با استفاده از کلاس SqlBulkCopy به راحتی در جدول پایگاه داده SQL Server بخوانید و وارد کرد.
بانک اطلاعات
از جدول مشتریان با طرحی به شرح زیر استفاده کرده ایم. CustomerId یک ستون افزایش خودکار است.
توجه :
می توانید جدول بانک اطلاعاتی SQL را با کلیک روی لینک دانلو کنید. فایل SQL را بارگیری کنید
رشته های اتصال
فایل های اکسل نسخه های 97-2003 و 2007 و بالاتر از ارائه دهنده های مختلف OLEDB استفاده می کنند و از این رو دو رشته اتصال مختلف در فایلWeb.Config ذخیره شده اند.
ویژگی DataSource به Placeholder {0} اختصاص داده شده است ، که با مسیر واقعی فایل جایگزین خواهد شد.
<connectionStrings>
<add name="Excel03ConString" connectionString="Provider=Microsoft.Jet.OLEDB.4.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
<add name="Excel07ConString" connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source={0};Extended Properties='Excel 8.0;HDR=YES'"/>
</connectionStrings>
فضاهای نام
باید نامهای زیر را وارد کنید.
using System.IO;
using System.Data;
using System.Data.OleDb;
using System.Configuration;
Controller
Controller از دو روش تشکیل شده است.
روش انجام عملیات GET
در داخل این روش Action ، به سادگی نمایش داده می شود.
روش مدیریت عملیات POST برای بارگذاری و خواندن فایل اکسل
با انتخاب فایل Excel و کلیک بر روی دکمه Import ، این روش Action استفاده می شود و فایل بارگذاری شده را در پارامتر HttpPostedFileBase دریافت می کند.
فایل بارگذاری شده در اکسل در پوشه ای به نام Uploads ذخیره می شود و بر اساس برنامه افزودنی XLS (97-2003) یا XLSX (2007 و بالاتر) ، رشته اتصال مناسب از فایل Web.Config خوانده و جای متغیردر پرونده اکسل جایگزین می شود
توجه :
تمام فایل های اکسل را در ردیف اول به عنوان Header Row که حاوی نام ستون ها است ، در نظر می گیریم ، اگر فایل اکسل شما دارای یک Header Row نباشد ، می توانید HDR=’No’را تنظیم کنید.
با استفاده از نام Sheet ، یک عبارت SELECT اجرا می شود و تمام سوابق موجود در برگه اکسل به DataTable منتقل می شوند.
اکنون ارتباطی با بانک اطلاعاتی برقرار شده است و هدف SqlBulkCopy شروع می شود و با استفاده از خاصیت DestinationTableName نام جدول را مشخص میکنیم.
سرانجام ستون ها ترسیم و تمام ردیف های DataTable در جدول SQL Server وارد می شوند.
توجه :
ظاهر ستون های DataTable و جدول SQL Server اختیاری است و شما باید فقط در مواردی که DataTable و / یا جدول SQL Server شما دارای تعداد ستون های یکسانی نباشد یا نام ستون ها متفاوت باشد انجام دهید .
public class HomeController : Controller
{
// GET: Home
public ActionResult Index()
{
return View();
}
[HttpPost]
public ActionResult Index(HttpPostedFileBase postedFile)
{
string filePath = string.Empty;
if (postedFile != null)
{
string path = Server.MapPath("~/Uploads/");
if (!Directory.Exists(path))
{
Directory.CreateDirectory(path);
}
filePath = path + Path.GetFileName(postedFile.FileName);
string extension = Path.GetExtension(postedFile.FileName);
postedFile.SaveAs(filePath);
string conString = string.Empty;
switch (extension)
{
case".xls": //Excel 97-03.
conString = ConfigurationManager.ConnectionStrings["Excel03ConString"].ConnectionString;
break;
case".xlsx": //Excel 07 and above.
conString = ConfigurationManager.ConnectionStrings["Excel07ConString"].ConnectionString;
break;
}
DataTable dt = new DataTable();
conString = string.Format(conString, filePath);
using (OleDbConnection connExcel = new OleDbConnection(conString))
{
using (OleDbCommand cmdExcel = new OleDbCommand())
{
using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())
{
cmdExcel.Connection = connExcel;
//Get the name of First Sheet.
connExcel.Open();
DataTable dtExcelSchema;
dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);
string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();
connExcel.Close();
//Read Data from First Sheet.
connExcel.Open();
cmdExcel.CommandText = "SELECT * From [" + sheetName + "]";
odaExcel.SelectCommand = cmdExcel;
odaExcel.Fill(dt);
connExcel.Close();
}
}
}
conString = ConfigurationManager.ConnectionStrings["Constring"].ConnectionString;
using (SqlConnection con = new SqlConnection(conString))
{
using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))
{
//Set the database table name.
sqlBulkCopy.DestinationTableName = "dbo.Customers";
//[OPTIONAL]: Map the Excel columns with that of the database table
sqlBulkCopy.ColumnMappings.Add("Id", "CustomerId");
sqlBulkCopy.ColumnMappings.Add("Name", "Name");
sqlBulkCopy.ColumnMappings.Add("Country", "Country");
con.Open();
sqlBulkCopy.WriteToServer(dt);
con.Close();
}
}
}
return View();
}
}
View
View شامل یک عنصر HTML FileUpload و یک دکمه ارسال است که در یک عنصر فرم قرار دارد.
فرم HTML با استفاده از روش Html.BeginForm ایجاد شده است که پارامترهای زیر را می پذیرد.
ActionName - نام عمل در این حالت Index است.
ControllerName - نام Controller در این حالت Home است.
FormMethod - این روش فرم یعنی GET یا POST را مشخص می کند. در این حالت روی POST تنظیم می شود.
HtmlAttributes - این مجموعه می تواند ویژگی های فرم اضافی را مشخص کند. در اینجا باید enctype = "multipart / form-data" را مشخص کنیم که برای بارگذاری فایل ها ضروری است.
@{
Layout = null;
}
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width"/>
<title>Index</title>
</head>
<body>
@using (Html.BeginForm("Index", "Home", FormMethod.Post, new { enctype = "multipart/form-data" }))
{
<input type="file" name="postedFile"/>
<input type="submit" value="Import"/>
}
</body>
</html>
عکسهای صفحه
فایل اکسل
جدول حاوی داده های فایل اکسل
