If you want to Import Excel File into your database in ASP.NET MVC then this tutorial will teach you the fastest way to accomplish this. In this tutorial you will learn:
Would you like to dig deep into the world of database programming with C#? Then you can check this series of tutorials on Entity Framework Core that help you to learn database programming within a few hours time. The Excel File StructureYou can Import Excel file that have any number of rows and columns. Make sure you provide the names to each and every column of the excel file. Here I am using an excel file that contains the sale report of a shop. It has 7 columns:
You can download this excel file by clicking here. Creating Database TableBased on the structure of my excel file, I have to create a database table. To this table the data of this excel file will be copied. I name this table as Sale and create its structure like this:
Notice that I used varchar columns for storing string values from excel, and for decimal values I created the columns of money type. You cna download this table’s script from here. The Create table script of my Sale table is: CREATE TABLE [dbo].[Sale]( [Id] [int] IDENTITY(1,1) NOT NULL, [Region] [varchar](25) NOT NULL, [Person] [varchar](25) NOT NULL, [Item] [varchar](25) NOT NULL, [Units] [int] NOT NULL, [UnitCost] [money] NOT NULL, [Total] [money] NOT NULL, [AddedOn] [date] NOT NULL, CONSTRAINT [PK_Sale] PRIMARY KEY CLUSTERED ( [Id] ASC )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY] ) ON [PRIMARY] Step 1: Create Model The model code is: public class ImportExcel { [Required(ErrorMessage = "Please select file")] [FileExt(Allow = ".xls,.xlsx", ErrorMessage = "Only excel file")] public HttpPostedFileBase file { get; set; } } The FileExt() is the Custom Validation attribute used for doing the Server Side Validation of file upload control. Thus it will allow file upload control to upload only .xls and .xlsx files. Step 2: Create FileExt() Class Create a new class called FileExt and add the below code to it: public class FileExt : ValidationAttribute { public string Allow; protected override ValidationResult IsValid(object value, ValidationContext validationContext) { if (value != null) { string extension = ((System.Web.HttpPostedFileBase)value).FileName.Split('.')[1]; if (Allow.Contains(extension)) return ValidationResult.Success; else return new ValidationResult(ErrorMessage); } else return ValidationResult.Success; } } Step 3: Create Controller In the controller add Index Action of type post as shown below: [HttpPost] public ActionResult Index(ImportExcel importExcel) { if (ModelState.IsValid) { string path = Server.MapPath("~/Content/Upload/" + importExcel.file.FileName); importExcel.file.SaveAs(path); string excelConnectionString = @"Provider='Microsoft.ACE.OLEDB.12.0';Data Source='" + path + "';Extended Properties='Excel 12.0 Xml;IMEX=1'"; OleDbConnection excelConnection = new OleDbConnection(excelConnectionString); //Sheet Name excelConnection.Open(); string tableName = excelConnection.GetSchema("Tables").Rows[0]["TABLE_NAME"].ToString(); excelConnection.Close(); //End OleDbCommand cmd = new OleDbCommand("Select * from [" + tableName + "]", excelConnection); excelConnection.Open(); OleDbDataReader dReader; dReader = cmd.ExecuteReader(); SqlBulkCopy sqlBulk = new SqlBulkCopy(ConfigurationManager.ConnectionStrings["CS"].ConnectionString); //Give your Destination table name sqlBulk.DestinationTableName = "sale"; //Mappings sqlBulk.ColumnMappings.Add("Date", "AddedOn"); sqlBulk.ColumnMappings.Add("Region", "Region"); sqlBulk.ColumnMappings.Add("Person", "Person"); sqlBulk.ColumnMappings.Add("Item", "Item"); sqlBulk.ColumnMappings.Add("Units", "Units"); sqlBulk.ColumnMappings.Add("Unit Cost", "UnitCost"); sqlBulk.ColumnMappings.Add("Total", "Total"); sqlBulk.WriteToServer(dReader); excelConnection.Close(); ViewBag.Result = "Successfully Imported"; } return View(); } Also you need to add the following namespaces to the controller: using System.Data.OleDb; using System.Data.SqlClient; using System.Configuration; Explanation
Step 4: Create View Add the following code to the Index View: <h4>@ViewBag.Result</h4> @using (Html.BeginForm("Index", "ImportExcel", FormMethod.Post, new { enctype = "multipart/form-data" })) { @Html.TextBoxFor(m => m.file, new { type = "file" }) <button id="submitButton" type="submit">Submit</button> @Html.ValidationMessageFor(model => model.file) } You can also create PDF Files from ASP.NET website. For this check my tutorial – How to Create a PDF file in ASP.NET MVC using iTextSharp. Conclusion The below image shows the records added to my SQL database table: You can download the source code by using the below link: DOWNLOAD Next: You can also check my another related tutorial How to Read Excel file and show it in the form of a Grid that also has Paging. |