Skip to main content

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

Talking Dotnet ASP.NET Core

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.

PS: If you found this content valuable and want to return the favour, then Buy Me A Coffee

53 thoughts to “Import and Export excel in ASP.NET Core 2.0 Razor Pages”

  3. return File(memory, “application/vnd.openxmlformats-officedocument.spreadsheetml.sheet”, sFileName);

    it is giving error, we are currently using .netcore 2.2 version : Non invocable member File cannot be used like a method.

    Is this File function is of System.IO assembly.

    Reply

  4. hi
    i am trying to import data but dont work uploadbtn when click on
    please help me by sent a example project to my email
    i am confuse
    email:soltani.sh2016@gmail.com
    thank you very much

    Reply

  6. A mi no me funciono el ajax de esta manera. la url: “/ImportExport?handler=Import” nunca entraba al breakpoint

    $.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);
    }
    });

    —-> así que cambie la url: url: “/ImportExport/OnPostImport” (/[controlador]/[método]), y ya pude leer el archivo en excel, mi pregunta es:
    ¿Esta forma de cambiar la url va a afectar la seguridad de mi petición POST?

    $.ajax({
    type: “POST”,

    url: “/ImportExport/OnPostImport”,
    beforeSend: function (xhr) {
    xhr.setRequestHeader(“XSRF-TOKEN”,
    $(‘input:hidden[name=”__RequestVerificationToken”]’).val());
    },
    data: filedata,
    cache: false,
    contentType: false,
    processData: false,
    success: function (response) {
    if (response.length == 0)
    alert(‘Error occurred while uploading the excel file’);
    else {
    $(‘#divData’).html(response);
    }
    },
    error: function (e) {
    $(‘#divData’).html(e.responseText);
    }
    });

    Saludos

    Reply

    1. Where do I have to add this code?

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

      Reply

  7. This is great works like a charm, just wondering if you can make the fileds editable when displaying on web?

    Reply

  8. Thanks this was really useful and is a good starting point for me trying to make this generate a range of xlsx files from the database.

    Not sure if this has changed but the code generates an error in the JS console for me due to a bad request error. I changed this code:
    “`
    beforeSend: function (xhr) {
    xhr.setRequestHeader(“XSRF-TOKEN”,
    $(‘input:hidden[name=”__RequestVerificationToken”]’).val());
    },
    “`

    to the following and it fixed the error:
    “`
    beforeSend: function (xhr) {
    xhr.setRequestHeader(“RequestVerificationToken”,
    $(‘input:hidden[name=”__RequestVerificationToken”]’).val());
    },
    “`

    Also this code `$(document).ready(function () {` can be simplified to `$(function () {`

    Reply

    1. For anyone looking to get this to create multiple spreadsheets from the database I found a way.
      In ImportExport.cshtml.cs add code to load in an object from the database – I used a list of showrooms (was a database I had handy) where I can either pass in a showroom name to generate a spreadsheet for one showroom or pass in nothing to generate them all:

      IQueryable showroomIQ = from s in _context.Showroom
      select s;

      if (showroomName != null)
      {
      showroomIQ = showroomIQ.Where(a => a.ShowroomName == showroomName);
      }

      Showroom = await showroomIQ.AsNoTracking().ToListAsync();

      foreach(var item in Showroom)
      {
      //existing code here –
      }

      Now all object properties/values are available to be inserted into the Excel file.

      Unless you want to trigger downloads of each file you will want to ensure the return line is outside the foreach loop and just says return Page();. I changed mine from post to get as well.

      Reply

  10. On writing OnPostImport function in the model, VS is not able to recognize “Request” and “Content” at all (“it doesn’t exist in the context” error). Neither is it able to suggest any library needed to be imported to fix it. When I pasted the same code in the controller, it didn’t throw any error at all. Given that both are .cs files, this error is not making sense to me. It would be great if you could help me. Does the code go in controller or model?

    Reply

  12. Thanks for this example, but actually I need to send the excel data to SqlServer do you have an example for this ?

    Reply

  14. Hey please share the whole project may be a github link for the same will do, I am quite new to .net core so I am having issues to implement the same.

    Thanks !!!

    Reply

    1. Hi, it’ din’t do it too me too, i find that that was cause this line: Export

      instead of use that i have to use that: Export

      Reply

      1. looks like i can’t post html, so instead of: asp-page-handler=”export” use asp-action=”OnPostExport”

        Reply

      1. Can you please provide code for saving to MS SQL server database after importing the excel sheet.S till learning how to develop

        Reply

  23. Hi, I would like to know how to delete the Excel file generated in the wwwroot folder after finishing the download.

    Reply

  24. Thanks for such a great tutorial. How to save these data in to DB ? Please share if you have worked on this please share with us
    Thanks

    Reply

  25. Hi i try to passing the data into a database, but only insert the first cycle, for example, only insert value 1 on all the columns in the table.
    How to insert all the values??

    Reply

  26. Thank you for this; worked wonders.Just one thing: I strongly prefer not to save an excel file to the webserver’s disk. Is there any way to do this all in memory please?

    Thanks!

    Reply

    1. I am glad that it helped you. Well, the import is already happening in the memory and for export, it’s not a good idea to perform all operation in the memory unless you know that uploaded file will be small in size and there would be on performance impact.

      Reply

      1. Thanks for coming back to me so quickly. My spreadsheets are very small, and created from structures already in memory. Unfortunately my stream and file handling coding skills are way too basic for me to work out how to make this change. Can you suggest how please?

        Many thanks, Simon

        Reply

    2. This was a well-made article. Thank you very much.

      Simon, the code you’re looking for is the following. I will include it here for anyone else needing it. I find this is useful when I need to send an email from a web server and I don’t want to involve the file system. The reason this isn’t so cut-and-dry with NPOI, is that when the workbook writes to a stream, it closes it (doh!). To get around this, it is necessary to copy it to a stream that is NOT closed, setting its position to 0, so it is ready to be read in FULL. That’s all there is to it.

      #region Write new workbook to email-attachable memorystream:
      MemoryStream memStream = new MemoryStream();
      using (MemoryStream tempStream = new MemoryStream())
      {
      workbook.Write(tempStream);
      var byteArray = tempStream.ToArray();
      memStream.Write(byteArray, 0, byteArray.Length);
      }
      memStream.Position = 0;
      #endregion

      return memStream;

      Reply
  27. Pingback: Ajax Code doesn’t call the right POST method – program faq

  28. If you get a 400 Bad Request error, you are likely missing something that this article forgot.

    Since you are sending an antiforgery token in the request header, you need to configure the antiforgery service to look for the token.

    public void ConfigureServices(IServiceCollection services)
    {
    services.AddMvc();
    services.AddAntiforgery(o => o.HeaderName = “XSRF-TOKEN”);
    }

    Reply
  30. Pingback: Import and Export excel in ASP.NET Core 2.0 Razor Pages – MyIT101
  31. Pingback: Import and Export excel in ASP.NET Core 2.0 Razor Pages – Site Design

Leave a Reply

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