How to Import Excel Data to Database in ASP.NET Core MVC

How to Import Excel Data to Database in ASP.NET Core MVC

In this article, I will discuss How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples. Please read our previous article discussing How to Export Data to an Excel File in ASP.NET Core MVC Application with Examples. Let’s continue with the application we worked on in previous articles.

How do you import Excel data to the database in ASP.NET Core MVC?

Importing Excel data into a database in an ASP.NET Core MVC application involves several steps. Here’s a general approach:

  • Create a Model Corresponding to Your Data: Define a C# class that matches the structure of the data in your Excel file. This class will be used to map the Excel data to your database. In our previous article, we created the Employee model, and we will use the same model in this demo.
  • Set Up Database Context: Ensure you have a database context set up for Entity Framework Core (or another ORM you are using) that includes a DbSet for the model you created. We have already created the DbContext class and DbSet property to generate the Employees database table in the database using EF Core in our previous article.
  • Install a Library for Reading Excel Files: You can use libraries like ClosedXML, EPPlus, or NPOI to read Excel files. These libraries make it easy to read data from Excel files. We have already added the ClosedXML package from NuGet in our previous article.
  • Create an Action Method for File Upload: In your controller, create an action method that presents a view to upload the Excel file.
  • Create a View for Uploading Excel File: Create a Razor view with a form that allows users to upload an Excel file.
  • Create an Action Method to Handle the Excel Import: In your controller, create another action method that handles the file upload and processes the Excel file.
  • Process the Excel File: Read the Excel file using ClosedXML. Map the data from the Excel rows to your model objects.
  • Save the Data to the Database: Add the model objects to your database context and save the changes.
Create a Method to Parse Excel Data:

You need a method to read the Excel file and convert it into a list of Employee objects. So, please add the following method to the Excel FileHandling class.

//This Method will Stream Object as an input which contains the Excel file
//And then convert that Excel file to List of Employees
public List<Employee> ParseExcelFile(Stream stream)
{
    var employees = new List<Employee>();

    //Create a workbook instance
    //Opens an existing workbook from a stream.
    using (var workbook = new XLWorkbook(stream))
    {
        //Lets assume the First Worksheet contains the data
        var worksheet = workbook.Worksheet(1);

        //Lets assume first row contains the header, so skip the first row
        var rows = worksheet.RangeUsed().RowsUsed().Skip(1);

        //Loop Through all the Rows except the first row which contains the header data
        foreach (var row in rows)
        {
            //Create an Instance of Employee object and populate it with the Excel Data Row
            var employee = new Employee
            {
                Name = row.Cell(1).GetValue<string>(),
                Departmet = row.Cell(2).GetValue<string>(),
                Salary = row.Cell(3).GetValue<long>(),
                Position = row.Cell(4).GetValue<string>(),
                DateOfJoining = row.Cell(5).GetValue<DateTime>(),
            };

            //Add the Employee to the List of Employees
            employees.Add(employee);
        }
    }

    //Finally return the List of Employees
    return employees;
}
Create a Controller Action to Handle File Upload and Import:

Create an action method in your controller to handle the file upload and trigger the import process. So, add the following action method within the FileUpload Controller.

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile file)
{
    if (file != null && file.Length > 0)
    {
        //Create an Instance of ExcelFileHandling
        ExcelFileHandling excelFileHandling = new ExcelFileHandling();

        //Call the CreateExcelFile method by passing the stream object which contains the Excel file
        var employees = excelFileHandling.ParseExcelFile(file.OpenReadStream());

        // Now save these employees to the database
        using (var context = new EFCoreDbContext())
        {
            await context.Employees.AddRangeAsync(employees);
            await context.SaveChangesAsync();
        }

        return View("UploadSuccess"); // Redirect to a view showing success or list of products
    }

    return View(); // Redirect back to upload view in case of failure
}
Create a View for Excel File Upload:

You’ll need a view where users can upload the Excel file. So, add the following action method within the FileUpload Controller.

public IActionResult ImportExcel()
{
    return View();
}

Next, add a view named ImportExcel.cshtml within the Views/FileUpload directory and then copy and paste the following code.

@{
    ViewData["Title"] = "ImportExcel";
}

<h1>ImportExcel</h1>
<hr />
<div class="row">
    <div class="col-md-12">
        <form method="post" asp-controller="FileUpload" asp-action="ImportFromExcel"
              enctype="multipart/form-data">
            <div asp-validation-summary="All" class="text-danger"></div>
            <input type="file" name="file" class="form-control" />
            <button type="submit" name="Upload" class="btn btn-primary">Upload</button>
        </form>
    </div>
</div>
Testing the Application:

Before testing the application, let us first truncate the Employee table data by executing the following SQL statement.

TRUNCATE TABLE Employees;

Generating Excel File:

Please create an Excel file with the following structure. The structure is important. We are going to upload the following Excel file.

When should you use import Excel Data to the database in ASP.NET Core MVC?

Run the application and visit the FileUpload/ImportExcel URL, which should open the following page. Select the Excel file from this page and click on the Upload button, as shown in the image below.

How to Import Excel Data to Database in ASP.NET Core MVC

Once you click the Upload button, the Excel sheet data must be uploaded to the Employees database table. If you verify the Employees table, then you should see the following records.

How do you import Excel data to the database in ASP.NET Core MVC?

Points to Consider:
  • Error Handling: The example above is quite basic and does not include comprehensive error handling. You should add proper error handling for scenarios like file format issues, data validation errors, and database operation failures.
  • Asynchronous Operations: Consider making the file processing and database operations asynchronous for better performance, especially with large files or slow database operations.
  • Security: Ensure that you validate the file type and contents to protect against malicious uploads.
  • Data Mapping: The example assumes a direct mapping between the Excel columns and the model properties. If your data requires transformation or additional processing, incorporate that logic.
  • Performance Optimization: For importing large Excel files, consider performance implications. Batch processing and bulk insert operations can significantly improve performance.
  • Front-End Validation: Adding client-side validation for the file upload (like checking file size and type) can improve the user experience.
When should you import Excel Data to the database in ASP.NET Core MVC?

Using the feature to import Excel data into a database in an ASP.NET Core MVC application is particularly useful in several scenarios. Here are some common use cases:

  • Data Migration: Excel files often serve as an intermediate format when migrating data from an older system or a different platform. Importing from Excel can simplify the migration process.
  • Bulk Data Entry: Manually entering large amounts of data into a system can be time-consuming and error-prone. If the data already exists in an Excel format, importing it directly into the database is much more efficient.
  • Interoperability with Non-Technical Users: Many non-technical users are comfortable with Excel but might not be familiar with database operations. They can provide data in Excel format, which the application can then import into the system.
  • Reporting and Data Analysis: Data might be collected and aggregated in Excel files for reporting and analysis purposes. Importing this data into your application’s database allows for more complex queries and integration with other data.
  • Initial Data Setup: During the initial setup of an application, you might need to load starting data (like product lists, price tables, etc.) into the database. Using Excel files for this purpose can make the process easier.
  • Regular Updates from External Sources: If your application regularly receives data updates from external sources (like vendor price lists, customer data updates, etc.) in the form of Excel files, automating the import process can save a lot of time and effort.
  • Data Syncing: If your application needs to sync with another system that exports data as Excel files, importing these files can be a practical solution.

In the next article, I will discuss How to Generate a PDF in the ASP.NET Core MVC Application with examples. In this article, I try to explain How to Import Excel Data to a Database in an ASP.NET Core MVC Application with Examples. I hope you enjoy this article on How to Import Excel Data to a Database in ASP.NET Core MVC.

Leave a Reply

Your email address will not be published. Required fields are marked *