{{announcement.body}}
{{announcement.title}}

Import and Export Excel file in ASP .NET Core 3.1 Razor age

DZone 's Guide to

Import and Export Excel file in ASP .NET Core 3.1 Razor age

Home .NET Core In this article we will discuss how to import and export excel file using ASP .NET Core 3.1 razor pages. We will discuss the below points >> H...

· Web Dev Zone ·
Free Resource

In this article we will discuss how to import and export excel file using ASP .NET Core 3.1 razor pages. We will discuss the below points:

>>  How to import Excel file in .NET Core and preview the uploaded excel file?
>>  How to export the excel file ? 


NPOI Package
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. 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. 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 know more about NPOI, Refer the official documentation.

Import and Export Excel in ASP.NET Core 3.1 Razor Pages  

Let's create a .NET Core web application with .NET Core 3.1 . Open VS 2019 ➠ Select ASP .NET Core web application

Creating an ASP.NET Core application

In this example, we create an input file for upload the Excel file, and after uploading it, append the Excel data into DIV. Then, we download that Excel data and also create an Excel file using dummy data. The design is shown below:

Application design


HTML
 




x
21


 
1
<form asp-controller="Home" asp-action="Export">
2
    <div class="container">
3
        <div class="row">
4
            <div class="col-md-4">
5
                <input type="file" id="fileupload" name="files" class="form-control" />
6
            </div>
7
            <div class="col-md-3">
8
                <input type="button" name="Upload" value="Upload" id="btnupload" class="btn btn-primary" />
9
                <a href="@Url.Action("Download", "Home")">Download</a>
10
            </div>
11
            <div class="col-md-5">
12
                <input type="submit" name="Export" value="Create and Export" id="btnExport"
13
                       class="btn btn-primary" asp-action="Export" />
14
            </div>
15
        </div>
16
        <div class="clearfix">&nbsp;</div>
17
        <div class="row">
18
            <div id="divPrint"></div>
19
        </div>
20
    </div>
21
</form>



Code Snippet Explanation

The HTML code has the file upload control and a button to upload the file. The below JQuery code uploads the Excel file using Ajax. The code also performs 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 3.1 razor pages and this post for uploading files in ASP.NET Core 3.1 Razor Pages.

JavaScript
 




xxxxxxxxxx
1
53


 
1
<script type="text/javascript" src="https://code.jquery.com/jquery-3.4.1.min.js"></script>
2
<script type="text/javascript">
3
    $(function () {
4
        $('#btnupload').on('click', function () {
5
            var fileExtension = ['xls', 'xlsx'];
6
            var filename = $('#fileupload').val();
7
            if (filename.length == 0) {
8
                alert("Please select a file.");
9
                return false;
10
            }
11
            else {
12
                var extension = filename.replace(/^.*\./, '');
13
                if ($.inArray(extension, fileExtension) == -1) {
14
                    alert("Please select only excel files.");
15
                    return false;
16
                }
17
            }
18
            var fdata = new FormData();
19
            var fileUpload = $("#fileupload").get(0);
20
            var files = fileUpload.files;
21
            fdata.append(files[0].name, files[0]);
22
            $.ajax({
23
                type: "POST",
24
                url: "/Home/Import",
25
                beforeSend: function (xhr) {
26
                    xhr.setRequestHeader("XSRF-TOKEN",
27
                        $('input:hidden[name="__RequestVerificationToken"]').val());
28
                },
29
                data: fdata,
30
                contentType: false,
31
                processData: false,
32
                success: function (response) {
33
                    if (response.length == 0)
34
                        alert('Some error occured while uploading');
35
                    else {
36
                        $('#divPrint').html(response);
37
                    }
38
                },
39
                error: function (e) {
40
                    $('#divPrint').html(e.responseText);
41
                }
42
            });
43
        })
44
        $('#btnExport').on('click', function () {
45
            var fileExtension = ['xls', 'xlsx'];
46
            var filename = $('#fileupload').val();
47
            if (filename.length == 0) {
48
                alert("Please select a file then Import");
49
                return false;
50
            }
51
        });
52
    });
53
</script>



To import the file, create a post method in the Homecontroller.cs file and save the uploaded file into wwwroot folder then append it into the div.

C#


To download the file, follow the code below:

C#


To create and export the file, let's take dummy data for an employee and export it.

C#


Let's run the application and see the output.

Application output


Find the Source Code in Github.com/CoreProgramm/.

File structure

You can see after upload the file it save it into the specified path. For more details, go through the following link:  https://www.coreprogramm.com/2020/01/import-and-export-excel-file-in-dotnet-core.html.


Topics:
.net core ,asp.net ,c# ,tutorial ,web dev

Published at DZone with permission of Jayant Tripathy . See the original article here.

Opinions expressed by DZone contributors are their own.

{{ parent.title || parent.header.title}}

{{ parent.tldr }}

{{ parent.urlSource.name }}