MobileShop Website Part 31 | How to Import and Export Products List in ASP.NET Core MVC

on

 


Welcome to Part 31 of our series! Today we are diving into an incredibly practical administrative feature for any enterprise platform: Data Portability.

In Step 1, we implement the asynchronous ExportToExcel action method using the powerful ClosedXML library. This method extracts your active inventory database records, constructs a highly formatted Excel spreadsheet in system memory, auto-fits the columns, and streams a clean .xlsx file binary download right to the administrator's browser.

Here is the step-by-step breakdown of how this server-side data extraction works.

Part 30 — Step 1: Excel Generation Lifecycle

The Database-to-Spreadsheet Pipeline

C# / Areas/Admin/Controllers/ProductsController.cs (ExportToExcel Method) 
public async Task<IActionResult> ExportToExcel()
{
    var products = await _context.Products
        .Include(p => p.Category)
        .Include(p => p.Brand)
        .Where(p => p.IsActive)
        .ToListAsync();

    using var workbook = new XLWorkbook();
    var worksheet = workbook.Worksheets.Add("Products");

    // Headers
    worksheet.Cell(1, 1).Value = "ID";
    worksheet.Cell(1, 2).Value = "Name";
    worksheet.Cell(1, 3).Value = "Model";
    worksheet.Cell(1, 4).Value = "Category";
    worksheet.Cell(1, 5).Value = "Brand";
    worksheet.Cell(1, 6).Value = "Original Price";
    worksheet.Cell(1, 7).Value = "Sale Price";
    worksheet.Cell(1, 8).Value = "Stock";
    worksheet.Cell(1, 9).Value = "Featured";
    worksheet.Cell(1, 10).Value = "Active";

    // Data
    for (int i = 0; i < products.Count; i++)
    {
        var p = products[i];
        worksheet.Cell(i + 2, 1).Value = p.Id;
        worksheet.Cell(i + 2, 2).Value = p.Name;
        worksheet.Cell(i + 2, 3).Value = p.Model;
        worksheet.Cell(i + 2, 4).Value = p.Category?.Name;
        worksheet.Cell(i + 2, 5).Value = p.Brand?.Name;
        worksheet.Cell(i + 2, 6).Value = p.OriginalPrice;
        worksheet.Cell(i + 2, 7).Value = p.SalePrice;
        worksheet.Cell(i + 2, 8).Value = p.StockQuantity;
        worksheet.Cell(i + 2, 9).Value = p.IsFeatured ? "Yes" : "No";
        worksheet.Cell(i + 2, 10).Value = p.IsActive ? "Yes" : "No";
    }

    worksheet.Columns().AdjustToContents();

    using var stream = new MemoryStream();
    workbook.SaveAs(stream);
    stream.Position = 0;

    return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Products.xlsx");
}

Step-by-Step Code Explanation

Eager Loading and Context Filtering

var products = await _context.Products
    .Include(p => p.Category)
    .Include(p => p.Brand)
    .Where(p => p.IsActive)
    .ToListAsync();

  • Before exporting, we fetch our relational ecosystem from SQL Server. We use .Include() to pull down nested text values like Category.Name and Brand.Name.

  • .Where(p => p.IsActive): An intentional filter logic layer. This ensures that any catalog items that have been deactivated via our Soft Delete feature in Part 30 are safely omitted from the standard inventory reports.

Workbook Initialization & Worksheet Spawning

using var workbook = new XLWorkbook();
var worksheet = workbook.Worksheets.Add("Products");

  • using var workbook: Leverages the C# using declaration syntax to instantiate a ClosedXML instance. Once this action method completes its lifecycle loop, the system automatically disposes of the heavy underlying memory resources.

  • Worksheets.Add("Products"): Generates a distinct spreadsheet canvas and names the bottom layout sheet tab "Products".

Defining Tabular Columns (Header Mapping)

worksheet.Cell(1, 1).Value = "ID";
worksheet.Cell(1, 2).Value = "Name";
...

  • ClosedXML references data using an explicit (row, column) coordinate grid structure.

  • Crucial Note: Unlike standard zero-indexed C# programming arrays, spreadsheet engines use a 1-based index system. Therefore, Cell(1, 1) targets Row 1, Column A, mapping out a rigid header array across your spreadsheet grid layout.

The Index Offset Mapping Loop (Data Hydration)

for (int i = 0; i < products.Count; i++)
{
    var p = products[i];
    worksheet.Cell(i + 2, 1).Value = p.Id;
    ...
    worksheet.Cell(i + 2, 4).Value = p.Category?.Name;
    ...
    worksheet.Cell(i + 2, 9).Value = p.IsFeatured ? "Yes" : "No";
}

  • Because our product database list is zero-indexed ($i = 0$), but Row 1 is already taken by our header labels, we set our vertical grid coordinate target using i + 2. This places our first actual product record securely into Row 2.

  • Null-Safety Controls: Uses the null-conditional operator (p.Category?.Name) to elegantly prevent fatal NullReferenceException crashes if an item somehow bypasses relation requirements.

  • Boolean Normalization: Rather than outputting confusing computer-style database strings (True/False), we clean up the output data format for administrators using a ternary operator to print friendly "Yes" or "No" text states.

Layout Optimization and In-Memory Streaming

worksheet.Columns().AdjustToContents();

using var stream = new MemoryStream();
workbook.SaveAs(stream);
stream.Position = 0;

  • AdjustToContents(): Loops through every populated string value in the workbook canvas and automatically stretches column widths so text never gets clipped or displays as generic ### length overflow flags in Excel.

  • MemoryStream: Rather than writing a temporary physical file to your web server's hard drive—which introduces heavy I/O performance bottlenecks—we convert the entire workbook structure directly into an in-memory byte layout stream. Setting stream.Position = 0; resets the reading pointer back to the start of the file structure so it is ready for transmission.

Streaming the Binary File Response

return File(stream.ToArray(), "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "Products.xlsx");

  • Constructs a native ASP.NET Core FileResult.

  • It takes the raw stream bytes array (stream.ToArray()), attaches the official open-xml spreadsheet MIME-type string definition, and specifies a clean default file download title (Products.xlsx) to smoothly execute the download on the client side.

In Step 2 of Part 31, we implement the flip side of our data portability feature: the HTTP POST ImportFromExcel method. This action allows administrators to bulk-upload products via an Excel spreadsheet. It parses the uploaded file bytes, matches relational strings (like category and brand names) to their respective database IDs, filters out duplicate products, and inserts new records into the system.

Here is the step-by-step breakdown of how this ingestion logic executes behind the scenes.

Step 2: Excel Ingestion & Bulk Parsing Lifecycle

The Spreadsheet-to-Database Pipeline

C# / Areas/Admin/Controllers/ProductsController.cs (ImportFromExcel Method) 
[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        TempData["Error"] = "Please select a file.";
        return RedirectToAction(nameof(Index));
    }

    try
    {
        using var stream = new MemoryStream();
        await file.CopyToAsync(stream);
        stream.Position = 0;

        using var workbook = new XLWorkbook(stream);
        var worksheet = workbook.Worksheet(1);
        var rows = worksheet.RowsUsed().Skip(1); // Skip header

        foreach (var row in rows)
        {
            var categoryid = _context.Categories.Where(c => c.Name.Contains(row.Cell(4).GetString())).FirstOrDefault().Id;
            var brandid = _context.Brands.Where(c => c.Name.Contains(row.Cell(5).GetString())).FirstOrDefault().Id;
            
            if (_context.Products.Where(p => p.CategoryId == categoryid && p.BrandId == brandid && p.Name == row.Cell(2).GetString() && p.Model == row.Cell(3).GetString()).Count() == 0)
            {
                var product = new Product
                {
                    Name = row.Cell(2).GetString(),
                    Model = row.Cell(3).GetString(),
                    CategoryId = categoryid,
                    BrandId = brandid,
                    OriginalPrice = (decimal)row.Cell(6).GetDouble(),
                    SalePrice = (decimal)row.Cell(7).GetDouble(),
                    StockQuantity = (int)row.Cell(8).GetDouble(),
                    IsFeatured = row.Cell(9).GetString() == "Yes",
                    IsActive = true
                };
                _context.Products.Add(product);
            }
        }

        await _context.SaveChangesAsync();
        TempData["Success"] = "Products imported successfully.";
    }
    catch (Exception ex)
    {
        TempData["Error"] = "Error importing products.";
    }

    return RedirectToAction(nameof(Index));
}

Step-by-Step Code Explanation

File Presence Validation Safeguard

[HttpPost]
public async Task<IActionResult> ImportFromExcel(IFormFile file)
{
    if (file == null || file.Length == 0)
    {
        TempData["Error"] = "Please select a file.";
        return RedirectToAction(nameof(Index));
    }

  • IFormFile file: Automatically binds the uploaded multi-part form file data from your view.

  • Null & Length Checks: Verifies that a file was actually selected and that it isn't an empty 0-byte upload. If validation fails, it safely sets an error notification in TempData and routes the administrator right back to the inventory list view without executing further logic.

Stream Allocation & ClosedXML Initialization

using var stream = new MemoryStream();
await file.CopyToAsync(stream);
stream.Position = 0;

using var workbook = new XLWorkbook(stream);
var worksheet = workbook.Worksheet(1);
var rows = worksheet.RowsUsed().Skip(1); // Skip header

  • CopyToAsync(stream): Asynchronously copies the file stream payload directly into system memory (MemoryStream), avoiding slow physical disk writes on the server.

  • RowsUsed().Skip(1): Reads the first tab (Worksheet(1)) and isolates rows containing data. The .Skip(1) extension is critical here; it tells ClosedXML to jump over Row 1 (the text headers like "ID", "Name") so we don't try to parse labels as database objects.

Database Relational Lookups

foreach (var row in rows)
{
    var categoryid = _context.Categories.Where(c => c.Name.Contains(row.Cell(4).GetString())).FirstOrDefault().Id;
    var brandid = _context.Brands.Where(c => c.Name.Contains(row.Cell(5).GetString())).FirstOrDefault().Id;

  • As the code loops through each Excel row, it reads Column 4 (Category string name) and Column 5 (Brand string name).

  • It runs an immediate .Where(...) lookup on the DB context to find an existing record whose name matches or contains that cell string, extracting its database Id.

The Duplication Preventer Block

f (_context.Products.Where(p => p.CategoryId == categoryid && p.BrandId == brandid && p.Name == row.Cell(2).GetString() && p.Model == row.Cell(3).GetString()).Count() == 0)

Before executing an insert statement, this block runs a data-integrity check. It queries the Products table to verify whether an entry with the exact same Name, Model, CategoryId, and BrandId already exists. If the returned record count is exactly 0, the application confirms the item is unique and proceeds to stage it.

Object Initialization & Type Casting

var product = new Product
{
    Name = row.Cell(2).GetString(),
    Model = row.Cell(3).GetString(),
    CategoryId = categoryid,
    BrandId = brandid,
    OriginalPrice = (decimal)row.Cell(6).GetDouble(),
    SalePrice = (decimal)row.Cell(7).GetDouble(),
    StockQuantity = (int)row.Cell(8).GetDouble(),
    IsFeatured = row.Cell(9).GetString() == "Yes",
    IsActive = true
};
_context.Products.Add(product);

  • Instantiates a new Product domain entity and populates its fields using the matching Excel cell index layout positions.

  • Explicit Casting: ClosedXML numerical data is retrieved via .GetDouble(), requiring explicit type casting like (decimal) for prices and (int) for inventory quantities to comply with your strongly typed model definition properties.

  • Boolean Normalization: Dynamically transforms the cell string value back into a backend tracking flag by checking if row.Cell(9).GetString() == "Yes".

Batch DB Persistence & Exception Shielding

        await _context.SaveChangesAsync();
        TempData["Success"] = "Products imported successfully.";
    }
    catch (Exception ex)
    {
        TempData["Error"] = "Error importing products.";
    }
    return RedirectToAction(nameof(Index));
}

  • SaveChangesAsync(): Outside the processing loop, this executes a single batch transaction to persist all staged Product records into the database at once.

  • try-catch Block: Wraps the parsing process. If an administrative user accidentally uploads a malformed sheet or inputs a typo in a category name that breaks the lookup logic, the exception is caught safely, preserving server uptime and alerting the user through a TempData["Error"] toast container message.

In Step 3 of Part 31, we are building the user interface trigger for our spreadsheet ingestion system: a professional, animated Bootstrap 5 Pop-up Modal.

Instead of cluttering the main dashboard with bulky dropzones, this modal stays hidden until needed. It features a file validation filter, includes a clever UX trick to let users download an export template directly from the popup, and configures the necessary transport encryption for file streaming over HTTP.

Here is the step-by-step breakdown of how this front-end component integrates with your backend.

Step 3: Designing the Import Modal View

The Modal Form Submission Architecture

HTML / Bootstrap Import Modal Snippet 
<!-- Import Modal -->
<div class="modal fade" id="importModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">
            <div class="modal-header">
                <h5 class="modal-title">Import Products</h5>
                <button type="button" class="btn-close" data-bs-dismiss="modal"></button>
            </div>
            <form asp-action="ImportFromExcel" method="post" enctype="multipart/form-data">
                <div class="modal-body">
                    <div class="mb-3">
                        <label class="form-label">Select Excel File</label>
                        <input type="file" name="file" class="form-control" accept=".xlsx,.xls" required />
                        <small class="text-muted">Download the <a asp-action="ExportToExcel">template</a> for correct format.</small>
                    </div>
                </div>
                <div class="modal-footer">
                    <button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
                    <button type="submit" class="btn btn-primary">Import</button>
                </div>
            </form>
        </div>
    </div>
</div>

Step-by-Step Layout Breakdown

Structural Containers & Accessibility

<div class="modal fade" id="importModal" tabindex="-1">
    <div class="modal-dialog">
        <div class="modal-content">

  • class="modal fade": Registers this container as a Bootstrap modal and applies a smooth fade-in animation when it is triggered on the screen.

  • id="importModal": The unique identifier for this overlay element. This ID must exactly match the data-bs-target="#importModal" attribute on the dashboard button used to launch it.

  • tabindex="-1": An essential accessibility attribute that ensures screen readers handle keyboard focus properly and allows users to dismiss the popup using the Esc key.

Form Target & File Stream Encryption (enctype)

<form asp-action="ImportFromExcel" method="post" enctype="multipart/form-data">

  • asp-action="ImportFromExcel": Points directly to the asynchronous data-ingestion controller action method we coded in Step 2.

  • enctype="multipart/form-data": This is absolutely critical. By default, normal HTML forms submit as plain text. Adding this encryption property alters the request headers, breaking down selected documents into binary stream fragments so the server can process them via the IFormFile container. Without this attribute, your file parameter will always arrive at the backend as null.

Restrictive File Selection Input

<input type="file" name="file" class="form-control" accept=".xlsx,.xls" required />

  • name="file": This must match the exact case-insensitive variable parameter name defined in your controller method signature from Step 2 (IFormFile file) for model binding to sync them.

  • accept=".xlsx,.xls": Filters user file exploration panels at the browser layer, graying out incompatible documents (like PDFs or images) to ensure only valid Excel files are submitted.

  • required: Client-side validation fallback that prevents the form from executing if an administrator clicks the submission trigger without selecting an input target.

Double-Duty Template UX Link

<small class="text-muted">Download the <a asp-action="ExportToExcel">template</a> for correct format.</small>

  • This is an elegant design pattern. Instead of coding a separate page button or forcing the user to guess column positions, we embed a template link right into the instruction copy.

  • By targeting asp-action="ExportToExcel" (the method created in Step 1), administrators can click it to immediately download the current live inventory spreadsheet. They can wipe the existing rows, add fresh inventory rows underneath the generated headers, and upload it safely—ensuring 100% alignment with your database schema columns!

Control Footers & Dismissal Triggers

<button type="button" class="btn btn-secondary" data-bs-dismiss="modal">Cancel</button>
<button type="submit" class="btn btn-primary">Import</button>

  • data-bs-dismiss="modal": Native Bootstrap JavaScript hook. It clears out the display without writing custom scripts, gracefully closing the overlay canvas if the user decides to cancel.

  • type="submit": Fires the file binary array downstream through the secure multi-part pipeline into your backend database management system.

In Step 4 of Part 31, we are wrapping up our Excel data management feature by adding the actual user action buttons to our Product Index View. These buttons give administrators a clean, highly visible entry point to execute exports and trigger the import pop-up modal we built in Step 3.

While they sit right next to each other in the UI layout, they operate under completely different mechanisms: one is a direct hyperlink routing anchor, and the other is a JavaScript state controller.

Here is the step-by-step breakdown of how these two buttons operate.

Step 4: Index View Action Button Integration

The Control Panel Layout Grid

HTML / Export and Import Buttons Snippet 
<a asp-action="ExportToExcel" class="btn btn-success"><i class="bi bi-file-earmark-excel"></i> Export</a>
<button type="button" class="btn btn-info" data-bs-toggle="modal" data-bs-target="#importModal">
    <i class="bi bi-upload"></i> Import
</button>

Step-by-Step Code Explanation

The Export Action Button

<a asp-action="ExportToExcel" class="btn btn-success">
    <i class="bi bi-file-earmark-excel"></i> Export
</a>

  • <a> (Anchor Tag): Because exporting data is a straightforward retrieval operation that triggers a file stream download from the server, we use a standard HTML anchor hyperlink tag instead of a form button.

  • asp-action="ExportToExcel": The ASP.NET Core MVC Tag Helper automatically targets our asynchronous backend export method from Step 1, mapping the link URL to /Products/ExportToExcel.

  • class="btn btn-success": Applies Bootstrap's signature green styling layout rules. Using green is a standard UX convention for Excel and sheet-based operations, instantly signaling to the store manager that clicking this will handle spreadsheet assets.

  • <i class="bi bi-file-earmark-excel"></i>: Injects a specialized Bootstrap Icon depicting a spreadsheet document sheet to give a polished, intuitive visual cue.

The Import Modal Trigger Button

<button type="button" class="btn btn-info" data-bs-toggle="modal" data-bs-target="#importModal">
    <i class="bi bi-upload"></i> Import
</button>

  • <button type="button">: Unlike the Export link, the Import button does not change the browser's URL path. It stays on the same page and functions strictly as a client-side layout coordinator, which is why it is defined as a standard button type.

  • class="btn btn-info": Differentiates the action by painting the component in Bootstrap's bright cyan/blue tone, separating the input ingestion system visually from the green download container.

  • data-bs-toggle="modal": This is a native Bootstrap 5 data attribute attribute rule. It tells the browser's built-in JavaScript engine to intercept the click event and prepare to launch an overlay dialog structure.

  • data-bs-target="#importModal": This connects the button to the UI. The target parameter value here uses a CSS ID selector hash token matching #importModal. When clicked, Bootstrap scans the page DOM, locates the exact pop-up modal wrapper we constructed in Step 3, and animates it onto the screen.

  • <i class="bi bi-upload"></i>: Interposes an upward arrow glyph to emphasize an outbound data upload workflow path.


Comments

Post a Comment