How to Import Excel File into Database in ASP.NET MVC

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:

  • How to upload an Excel File to the Server with an input control of type=”file”.
  • How to Read the uploaded Excel File data with OleDbDataReader class.
  • How to Import Excel File’s data into a Database table with SqlBulkCopy class. This is the fastest method of importing excel’s data.

The Excel File Structure

You can Import Excel file that can 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:

  • 1. Date – containing date values.
  • 2. Region – contains string.
  • 3. Person – contains string.
  • 4. Item – contains string.
  • 5. Units – contains values of type int.
  • 6. Unit Cost – contains decimal values.
  • 7. Total – contains decimal values.

You can download this excel file byclicking here.

Creating Database Table

Based on the structure of my excel file, I have to create a database table into which the data of this excel file will be copied.

I name this table Sale and create its structure like this:

  • 1. Id – Primary key and identity column of type int.
  • 2. Region – varchar(25).
  • 3. Person – varchar(25).
  • 4. Item – varchar(25).
  • 5. Units – int.
  • 6. UnitCost – money.
  • 7. Total – money.
  • 8. AddedOn – date.

Notice that I used varchar columns for storing string values from excel and for decimal values I created columns of money datatype.

Download the DatabaseTable here.

The Create table Query 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]

Note: After this tutorial learn how to doExport to Excel in ASP.NET MVC.

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 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 named FileExt and add this 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 add the following namespaces to the controller:

using System.Data.OleDb;
using System.Data.SqlClient;
using System.Configuration;

Explanation :

  • The first procedure to Import Excel file is by uploading it into the “Upload” folder. Then a connection string is made using Microsoft.ACE.OLEDB.12.0 provider.
  • Once the Excel’s file table is fetched into tableName variable I am creating OleDbCommand object, passing to it the select Query and the connection string.
  • Then I am creating OleDbDataReader object that will read every record of the Excel file.
  • Next the Import Excel procedure starts, for this I create the SqlBulkCopy class object and sets the SQL Database Table name to its DestinationTableName property. Then through the sqlBulk.ColumnMappings.Add(SourceColumn,DesinationColumn) , the column mappings are done.
  • Finally through .WriteToServe() function the Excel File’s Data is copied into the database table.

Step 4: Create View

Add the following code to the Index View:

@ViewBag.Result

@using (Html.BeginForm("Index", "ImportExcel", FormMethod.Post, new { enctype = "multipart/form-data" })) { @Html.TextBoxFor(m => m.file, new { type = "file" }) @Html.ValidationMessageFor(model => model.file) }

You can also create PDF Files from ASP.NET website. For this check my tutorial – How toCreate a PDF file in ASP Net MVC using iTextSharp DLL.

Conclusion

Now Run the application and Import the Excel File. After a couple of second the excel records will be copied into the database table. You can check your database table to find the newly copied records.

Next: You can also check my tutorialHow to Read Excel and show it in Grid with Paging.

DOWNLOAD

稿源:ASP.NET Daily Articles (源链) | 关于 | 阅读提示

本站遵循[CC BY-NC-SA 4.0]。如您有版权、意见投诉等问题,请通过eMail联系我们处理。
酷辣虫 » 后端存储 » How to Import Excel File into Database in ASP.NET MVC

喜欢 (0)or分享给?

专业 x 专注 x 聚合 x 分享 CC BY-NC-SA 4.0

使用声明 | 英豪名录