Import and Export excel in ASP.NET Core 2.0 Razor Pages

Last year around this time I wrote about Import and Export xlsx in ASP.NET Core, using an unofficial version of EPPlus.Core as EPPlus didn’t have .NET core support. In fact, many such popular libraries lacked support for .NET Core as the framework was not mature enough then. When .NET Standards came, the situation improved a bit, but the release of .NET Standards 2.0 was a booster for .NET Core as that helped to port existing .NET Framework based libraries to use with .NET Core.

NPOI is another very popular package for reading/writing excel files, and it also has .NET core version as well. This post talks about how to import or export excel files (xls or xlsx) using NPOI package with ASP.NET Core 2.0 Razor pages. The solution here uses ASP.NET Core 2.0 razor pages, the solution will work for ASP.NET Core MVC or Web API as well.

Import and Export excel in ASP.NET Core 2.0

NPOI is a free tool which supports xls, xlsx and docx extensions. This project is the .NET version of POI Java project at http://poi.apache.org/. POI is an open source project, which can help you read/write XLS, DOC, PPT files. It covers most features of Excel like styling, formatting, data formulas, extract images, etc.. The good thing is, it doesn’t require Microsoft Office to be installed on the server.

For example, you can use it to

  • Generate an Excel report without Microsoft Office suite installed on your Server and more efficient than calling Microsoft Excel ActiveX in the background.
  • Extract text from Office documents to help you implement full-text indexing feature (most of the time this feature is used to create search engines).
  • Extract images from Office documents.
  • Generate Excel sheets, which contains formulas.

Let’s create an ASP.NET Core Razor Page application. Open Visual Studio 2017. Hit File-> New Project -> Select ASP.NET Core Web Application. Enter the project name and say “OK”. Select “Web Application” from the next dialog.

Create new Razor Page application

Once the project is created, install NPOI package for .NET Core. To install, run the following command in the Package Manager Console:

PM> Install-Package DotNetCore.NPOI

To show,

  • Import: We’ll upload an excel file on the server and then process it using NPOI.
  • Export: We’ll create an excel file with some dummy data using NPOI and download the same in the browser.

Let’s add a new razor page and name it “ImportExport”. Put the following code in the ImportExport.cshtml.

<form method="post" enctype="multipart/form-data">
    <div class="row">
        <div class="col-md-4">
            <input type="file" id="fUpload" name="files" class="form-control" />
        </div>
        <div class="col-md-8">
            <input type="button" id="btnUpload" value="Upload" />
        </div>
    </div>
    <div class="row">
        <div class="col-md-8" style="padding-top:10px;">
            <button asp-page-handler="Export">Export</button>
        </div>
    </div>
    <br/>
    <div id="dvData"></div>
</form>

Export xlsx/xls in ASP.NET Core

For the demo, we will create the “xlsx” file in the wwwroot folder. To get wwwroot folder path, we need to inject IHostingEnvironment dependency in the constructor. Read how to Get application base and wwwroot path in ASP.NET Core.

private IHostingEnvironment _hostingEnvironment;
public ImportExportModel(IHostingEnvironment hostingEnvironment)
{
    _hostingEnvironment = hostingEnvironment;
}

The NPOI package supports both “xls” and “xlsx” extensions using HSSFWorkbook and XSSFWorkbook classes. The HSSFWorkbook class is for “xls”, where the other one is for “xlsx”. To export, define a new handler method named Export which generates an excel file, put dummy data and returns the generated file to the browser. See the following code,
Run the application and call the export handler method and you will find “demo.xlsx” gets downloaded.

Export excel in ASP.NET Core 2.0 Razor Pages

Import xlsx/xls in ASP.NET Core

Real world import functionality is complex, and it involves validation, applying business rules and finally saving it in the database. But to show you, will define an import handler method to read uploaded excel file and returns the content in a tabular format.

The HTML code has file upload control and a button to upload the file. The below jQuery code uploads the excel file using Ajax. The code also performs the client side validation for file selection and extension checking. Once the request is successful, it appends the server response to the HTML. Read this post for handling Ajax request with ASP.NET Core 2.0 razor pages and this post for uploading files in ASP.NET Core 2.0 Razor Pages.

$(document).ready(function () {
    $('#btnUpload').on('click', function () {
        var fileExtension = ['xls', 'xlsx'];
        var filename = $('#fUpload').val();
        if (filename.length == 0) {
            alert("Please select a file.");
            return false;
        }
        else {
            var extension = filename.replace(/^.*\./, '');
            if ($.inArray(extension, fileExtension) == -1) {
                alert("Please select only excel files.");
                return false;
            }
        }
        var fdata = new FormData();
        var fileUpload = $("#fUpload").get(0);
        var files = fileUpload.files;
        fdata.append(files[0].name, files[0]);
        $.ajax({
            type: "POST",
            url: "/ImportExport?handler=Import",
            beforeSend: function (xhr) {
                xhr.setRequestHeader("XSRF-TOKEN",
                    $('input:hidden[name="__RequestVerificationToken"]').val());
            },
            data: fdata,
            contentType: false,
            processData: false,
            success: function (response) {
                if (response.length == 0)
                    alert('Some error occured while uploading');
                else {
                    $('#dvData').html(response);
                }
            },
            error: function (e) {
                $('#dvData').html(e.responseText);
            }
        });
    })
});

The Import handler method first stores the file and based on the file extension, creates an appropriate object using NPOI. The method gets the row and column count and then loops through them to create a tabular format string and returns the same.
Run the application and call to import, you should see the following in the browser.

Import and Export excel in ASP.NET Core 2.0 Razor PagesThat’s it.

NPOI is an open source component and you can use it everywhere. This post shows you the basic functionalities of NPOI, but you can do much more with NPOI like styling the individual cell or rows, creating excel formulas and other stuff. To know more about NPOI, Refer the official documentation.

Thank you for reading. Keep visiting this blog and share this in your network. Please put your thoughts and feedback in the comments section.

2 thoughts on “Import and Export excel in ASP.NET Core 2.0 Razor Pages

Leave a Reply

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

10 + two =