DEV Community

Cover image for AspNet Core 5 Reading and Exporting Excel
Mohamad Lawand
Mohamad Lawand

Posted on

AspNet Core 5 Reading and Exporting Excel

In this article we will implement Excel import and Export in ASP.Net Core 5

You can also watch the full Video on Youtube

And you can find the full source code on GitHub:
https://github.com/mohamadlawand087/v27-ExcelImportExport

Ingredients
Visual Studio Code (https://code.visualstudio.com/)
.Net 5 SDK (https://dotnet.microsoft.com/download)

The first thing we are going to do is check the dotnet version installed

dotnet --version
Enter fullscreen mode Exit fullscreen mode

Now we need to create our application

dotnet new mvc -n "SampleExcel"
Enter fullscreen mode Exit fullscreen mode

Now let us open our source code

The first thing we are going to do is build the application and run it so we can see that everything is running as it should be.

dotnet run
Enter fullscreen mode Exit fullscreen mode

The next step is for us to install the excel package that we want to utilise and for this we are going to be using EPP

dotnet add package EPPlus.Core --version 1.5.4
Enter fullscreen mode Exit fullscreen mode

Once the package is installed we are going to create our Model which will handle the information output.

In the root directory inside that folder let us create a new class called User

public class User
{
    public string Name { get; set; }
    public string Email { get; set; }
    public string Phone { get; set; }
}
Enter fullscreen mode Exit fullscreen mode

Now let us create a new controller which will be responsible to handle the excel import and export, inside the controllers folder lets create a new Controller called UsersController and will add the export to excel functionality

public class UsersController : Controller
{
    private readonly ILogger<UsersController> _logger;

    public UsersController(ILogger<UsersController> logger)
    {
        _logger = logger;
    }

    public IActionResult Index()
    {
        var users = GetlistOfUsers();

        return View(users);
    }

    public IActionResult ExportToExcel()
    {
        // Get the user list 
        var users = GetlistOfUsers();

        var stream = new MemoryStream();
        using (var xlPackage = new ExcelPackage(stream))
        {
            var worksheet = xlPackage.Workbook.Worksheets.Add("Users");
            var namedStyle = xlPackage.Workbook.Styles.CreateNamedStyle("HyperLink");
            namedStyle.Style.Font.UnderLine = true;
            namedStyle.Style.Font.Color.SetColor(Color.Blue);
            const int startRow = 5;
            var row = startRow;

            //Create Headers and format them
            worksheet.Cells["A1"].Value = "Sample";
            using (var r = worksheet.Cells["A1:C1"])
            {
                r.Merge = true;
                r.Style.Font.Color.SetColor(Color.White);
                r.Style.HorizontalAlignment = OfficeOpenXml.Style.ExcelHorizontalAlignment.CenterContinuous;
                r.Style.Fill.PatternType = OfficeOpenXml.Style.ExcelFillStyle.Solid;
                r.Style.Fill.BackgroundColor.SetColor(Color.FromArgb(23, 55, 93));
            }

            worksheet.Cells["A4"].Value = "Name";
            worksheet.Cells["B4"].Value = "Email";
            worksheet.Cells["C4"].Value = "Phone";
            worksheet.Cells["A4:C4"].Style.Fill.PatternType = ExcelFillStyle.Solid;
            worksheet.Cells["A4:C4"].Style.Fill.BackgroundColor.SetColor(Color.FromArgb(184, 204, 228));
            worksheet.Cells["A4:C4"].Style.Font.Bold = true;

            row = 5;
            foreach (var user in users)
            {
                    worksheet.Cells[row, 1].Value = user.Name;
                    worksheet.Cells[row, 2].Value = user.Email;
                    worksheet.Cells[row, 3].Value = user.Phone;

                    row++;
            }

            // set some core property values
            xlPackage.Workbook.Properties.Title = "User List";
            xlPackage.Workbook.Properties.Author = "Mohamad Lawand";
            xlPackage.Workbook.Properties.Subject = "User List";
            // save the new spreadsheet
            xlPackage.Save();
            // Response.Clear();
        }
        stream.Position = 0;
        return File(stream, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "users.xlsx");
    }

    // Mimic a database operation
    private List<User> GetlistOfUsers()
    {
        var users = new List<User>()
        {
            new User {
                Email = "mohamad@email.com",
                Name = "Mohamad",
                Phone = "123456"
            },
            new User {
                Email = "donald@email.com",
                Name = "donald",
                Phone = "222222"
            },
            new User {
                Email = "mickey@email.com",
                Name = "mickey",
                Phone = "33333"
            }
        };

        return users;
    }
}
Enter fullscreen mode Exit fullscreen mode

Now let us update the views inside the Views folder let us create a new folder called Users and inside the Users folder we will create a new file Index.cshtml

@model List<SampleExcel.Models.User>

<div class="row">
    <div class="col-md-16">
        <a asp-action="ExportToExcel" asp-controller="Users" class="btn btn-primary">Export to Excel</a>
    </div>
    <div class="col-md-16">
        <a asp-action="BatchUserUpload" asp-controller="Users" class="btn btn-primary">Import from Excel</a>
    </div>
</div>    

<div class="row">
    <div class="col-md-12">
        <table class="table">
            <thead>
                <tr>
                    <th>Name</th>
                    <th>Email</th>
                    <th>Phone</th>
                </tr>
            </thead>
            <tbody>
                @foreach (var item in Model)
                {
                    <tr>
                        <td>@item.Name</td>
                        <td>@item.Email</td>
                        <td>@item.Phone</td>
                    </tr>
                }
            </tbody>
        </table>
    </div>
</div>
Enter fullscreen mode Exit fullscreen mode

Now let us update the controller with an import functionality

[HttpGet]
public IActionResult BatchUserUpload()
{
    return View();
}

[HttpPost]
[ValidateAntiForgeryToken]
public IActionResult BatchUserUpload(IFormFile batchUsers)
{

    if (ModelState.IsValid)
    {
        if (batchUsers?.Length > 0)
        {
            var stream = batchUsers.OpenReadStream();
            List<User> users = new List<User>();
            try
            {
                using (var package = new ExcelPackage(stream))
                {
                    var worksheet = package.Workbook.Worksheets.First();//package.Workbook.Worksheets[0];
                    var rowCount = worksheet.Dimension.Rows;

                    for (var row = 2; row <= rowCount; row++)
                    {
                        try
                        {

                            var name = worksheet.Cells[row, 1].Value?.ToString();
                            var email = worksheet.Cells[row, 2].Value?.ToString();
                            var phone = worksheet.Cells[row, 3].Value?.ToString();

                            var user = new User()
                            {
                                Email = email,
                                Name = name,
                                Phone = phone
                            };

                            users.Add(user);

                        }
                        catch(Exception ex)
                        {
                            Console.WriteLine("Something went wrong");
                        }
                    }
                }

                return View("Index", users);

            }
            catch(Exception e)
            {
                return View();
            }
        }
    }

    return View();
}
Enter fullscreen mode Exit fullscreen mode

As well we need to update the view inside the Views ⇒ Users folders we need to create a new file called BatchUserUpload.cshtml and add the following

<div class="row">
    <div class="col-md-12">
        <form asp-action="BatchUserUpload" asp-controller="Users" method="post" id="create-form" enctype="multipart/form-data">
            <div class="form-group">
                <label class="control-label">
                        Upload File:
                </label>
                <input id="batchUsers" name="batchUsers" class="form-control" type="file" tabindex="14">
                <span class="form-text text-muted">Allowed file types:  xlsx, xls.</span>
            </div>
            <div class="card-footer">
                <center>
                    <button type="submit" class="btn btn-primary mr-2">Upload Users</button>
                    <a asp-action="Index" asp-controller="Users" class="btn btn-secondary">Cancel</a>
                </center>
            </div>
        </form>
    </div>
</div>
Enter fullscreen mode Exit fullscreen mode

Thank you for reading, please ask your questions in the comments.

Oldest comments (1)

Collapse
 
vikasjk profile image
Vikas-jk

Nice article, but in this article, we are using EPPlus for importing data into database form excel, which is no longer free, so I will recommend using OleDB
You can take a look here

asp.net core excel import using oledb

Thanks