Extract Data from Excel File to use it in an ASP.net core application

I am developing an application where I have to get the data from an Excel sheet and use it in the application, but I am having trouble accessing the data. Should I use Entity Framework and move the Data to a database or do I just import the data from Excel and use it?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

Not sure what trouble you are meeting to access the data. But here is a sample about importing Excel data to Asp.net core application, you could refer to it.

In this sample, it will upload the excel file to the wwwroot folder using JavaScript first, then, using the DotNetCore.NPOI package (open source, you could install it via Nuget) to extract the data from excel. Details steps as below:

Suppose there have a testdata.xlsx file, the content as below:

Extract Data from Excel File to use it in an ASP.net core application

Code in the controller:

    private readonly IWebHostEnvironment _hostEnvironment;
    public HomeController(ILogger<HomeController> logger, IWebHostEnvironment environment)
    {
        _logger = logger; 
        _hostEnvironment = environment;
    }

    public IActionResult Upload()
    {
        return View();
    }
    [HttpPost]
    public IActionResult Import()
    {
        IFormFile file = Request.Form.Files[0]; 
        string folderName = "UploadExcel"; 
        string webRootPath = _hostEnvironment.WebRootPath; 
        string newPath = Path.Combine(webRootPath, folderName); 
        StringBuilder sb = new StringBuilder(); 
        if (!Directory.Exists(newPath)) 
        { 
            Directory.CreateDirectory(newPath); 
        } 
        if (file.Length > 0) 
        { 
            string sFileExtension = Path.GetExtension(file.FileName).ToLower(); 
            ISheet sheet; 
            string fullPath = Path.Combine(newPath, file.FileName); 
            using (var stream = new FileStream(fullPath, FileMode.Create)) 
            { 
                file.CopyTo(stream); 
                stream.Position = 0; 
                if (sFileExtension == ".xls") 
                { 
                    HSSFWorkbook hssfwb = new HSSFWorkbook(stream); //This will read the Excel 97-2000 formats   
                    sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook   
                } 
                else 
                { 
                    XSSFWorkbook hssfwb = new XSSFWorkbook(stream); //This will read 2007 Excel format   
                    sheet = hssfwb.GetSheetAt(0); //get first sheet from workbook    
                } 
                IRow headerRow = sheet.GetRow(0); //Get Header Row 
                int cellCount = headerRow.LastCellNum; 
                sb.Append("<table class='table table-bordered'><tr>");                     
                for (int j = 0; j < cellCount; j++) 
                { 
                    NPOI.SS.UserModel.ICell cell = headerRow.GetCell(j); 
                    if (cell == null || string.IsNullOrWhiteSpace(cell.ToString())) continue; 
                    sb.Append("<th>" + cell.ToString() + "</th>"); 
                } 
                sb.Append("</tr>"); 
                sb.AppendLine("<tr>");

                var emplist = new List<EmployeeViewModel>();

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File 
                { 
                    IRow row = sheet.GetRow(i); 
                    if (row == null) continue; 
                    if (row.Cells.All(d => d.CellType == CellType.Blank)) continue; 
                    for (int j = row.FirstCellNum; j < cellCount; j++) 
                    { 
                        if (row.GetCell(j) != null) 
                            sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); 
                    } 
                    sb.AppendLine("</tr>");
                    EmployeeViewModel emp = new EmployeeViewModel() {
                        EmployeeID = Convert.ToInt32(row.Cells[0].ToString()),
                        EmployeeName = row.Cells[1].ToString(),
                        Age = Convert.ToInt32(row.Cells[2].ToString()),
                        Sex = row.Cells[3].ToString(),
                        Designation = row.Cells[4].ToString()
                    };

                    emplist.Add(emp);
                }

                var result = emplist;
                sb.Append("</table>"); 
            } 
        }  
        return this.Content(sb.ToString());
        //return View("Upload");
    }

Code in the Upload view:
<form asp-controller="Home" asp-action="Export">
    <div class="container">
        <div class="row">
            <div class="col-md-4">
                <input type="file" id="fileupload" name="files" class="form-control" />
            </div>
            <div class="col-md-3">
                <input type="button" name="Upload" value="Upload" id="btnupload" class="btn btn-primary" />
                <a href="@Url.Action("Download", "Home")">Download</a>
            </div>
            <div class="col-md-5">
                <input type="submit" name="Export" value="Create and Export" id="btnExport"
                       class="btn btn-primary" asp-action="Export" />
            </div>
        </div>
        <div class="clearfix">&nbsp;</div>
        <div class="row">
            <div id="divPrint"></div>
        </div>
    </div>
</form>

And the Java Script code:
<script type="text/javascript">
$(function () {
    $('#btnupload').on('click', function () {
        var fileExtension = ['xls', 'xlsx'];
        var filename = $('#fileupload').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 = $("#fileupload").get(0);
        var files = fileUpload.files;
        fdata.append(files[0].name, files[0]);
        $.ajax({
            type: "POST",
            url: "/Home/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 {
                    $('#divPrint').html(response);
                }
            },
            error: function (e) {
                $('#divPrint').html(e.responseText);
            }
        });
    })
    $('#btnExport').on('click', function () {
        var fileExtension = ['xls', 'xlsx'];
        var filename = $('#fileupload').val();
        if (filename.length == 0) {
            alert("Please select a file then Import");
            return false;
        }
    });
});
</script>

The result like this:

Extract Data from Excel File to use it in an ASP.net core application

You could also create a Employee model to store the data:

public class EmployeeViewModel
{
    public int EmployeeID { get; set; }
    public string EmployeeName { get; set; }
    public int Age { get; set; }
    public string Sex { get; set; }
    public string Designation { get; set; }
}

Then, when loop through the excel rows, use the following code to get the Employee List:
                var emplist = new List<EmployeeViewModel>();

                for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++) //Read Excel File 
                { 
                    IRow row = sheet.GetRow(i); 
                    if (row == null) continue; 
                    if (row.Cells.All(d => d.CellType == CellType.Blank)) continue; 
                    for (int j = row.FirstCellNum; j < cellCount; j++) 
                    { 
                        if (row.GetCell(j) != null) 
                            sb.Append("<td>" + row.GetCell(j).ToString() + "</td>"); 
                    } 
                    sb.AppendLine("</tr>");
                    EmployeeViewModel emp = new EmployeeViewModel() {
                        EmployeeID = Convert.ToInt32(row.Cells[0].ToString()),
                        EmployeeName = row.Cells[1].ToString(),
                        Age = Convert.ToInt32(row.Cells[2].ToString()),
                        Sex = row.Cells[3].ToString(),
                        Designation = row.Cells[4].ToString()
                    };

                    emplist.Add(emp);
                }

If the above sample doesn’t achieve your requirement, please explain more details about what trouble are you having and what library you are using to upload the excel.

Reference:

Import and Export Excel file using NPOI

Import (Insert) Excel file data into Database ASP.Net Core MVC (using OLEDB library)


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x