javascript - How to import csv file and display the data on html table then save on sql database in asp .net core

Question

I'm struggling with importing csv file in my project. I want to upload a csv file and display the data before saving to sql database. I'm using asp .net core razor pages. Here is my C# method for Importing:

  public ActionResult OnPostImport()
        {
            IFormFile file = Request.Form.Files[0];
            string folderName = "Upload";
            string webRootPath = _hostingEnvironment.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")//This will read the Excel 97-2000 formats    
                    {
                        HSSFWorkbook hssfwb = new HSSFWorkbook(stream);
                        sheet = hssfwb.GetSheetAt(0);
                    }
                    else //This will read 2007 Excel format    
                    {
                        XSSFWorkbook hssfwb = new XSSFWorkbook(stream);
                        sheet = hssfwb.GetSheetAt(0);
                    }
                    IRow headerRow = sheet.GetRow(0);
                    int cellCount = headerRow.LastCellNum;
                    // Start creating the html which would be displayed in tabular format on the screen  
                    sb.Append("<table class='table'><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>");
                    for (int i = (sheet.FirstRowNum + 1); i <= sheet.LastRowNum; i++)
                    {
                        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>");
                    }
                    sb.Append("</table>");
                }
            }
            return this.Content(sb.ToString());
        }

Here is my frontend code:

    <h2>Import</h2>
<br />
<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>
    <br />
    <div id="dvData"></div>
</form>

Javascript code:

   @Html.AntiForgeryToken()
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<script src="https://ajax.aspnetcdn.com/ajax/jQuery/jquery-3.3.1.min.js"></script>
<script>
    $(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: "/Home/ImportCSVFile?handler=ImportFromExcel",
                beforeSend: function (xhr) {
                    xhr.setRequestHeader("RequestVerificationToken",
                        $('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);
                }
            });
        });
    });
</script>

they ajax call doesnt reach the c# method. Please help I'm not sure what is it I'm missing out on this code above.And also pleaae advise how i can get to save the data to sql after importing the csv file.

question from:https://stackoverflow.com/questions/65945168/how-to-import-csv-file-and-display-the-data-on-html-table-then-save-on-sql-datab

Answer 1

1.You can try to change public ActionResult OnPostImport() to public ActionResult OnPostImportFromExcel().Becasue your ajax url is /Home/ImportCSVFile?handler=ImportFromExcel. 2.Or you can change ajax url to /Home/ImportCSVFile?handler=Import.

result:

And about saving data to db,you can refer to this.