In ASP.NET Core-6 MVC, I have excel file (student.xlsx) with this code:

Model:

public class Students
{
    public string FirstName { get; set; }
    public string LastName { get; set; }
    public int AdmissionYear { get; set; }
    public string RegistrationNo { get; set; }
}

Controller:

    public async Task<IActionResult> Upload(IFormFile file)
    {
        if (file == null)
        {
            return RedirectToAction(nameof(Index));
        }
        List<Students> studentList = new List<Student>();
        
        using(var memoryStream = new MemoryStream())
        {
            await file.CopyToAsync(memoryStream).ConfigureAwait(false);
            using(var package = new ExcelPackage(memoryStream))
            {
                var worksheet = package.Workbook.Worksheets[0];
                if (worksheet.Dimension.Rows > 0 && worksheet.Dimension.Columns > 0)
                {
                    for (int row = 2; row <= worksheet.Dimension.Rows; row  )   // start at row 2 to skip header
                    {
                        Students student = new Students();
                        student.FirstName = worksheet.Cells[row, 1].Value.ToString();
                        student.LastName = worksheet.Cells[row, 2].Value.ToString();
                        student.AdmissionYear = int.Parse(worksheet.Cells[row, 3].Value.ToString());
                        student.RegistrationNo = worksheet.Cells[row, 4].Value.ToString();
                        studentList.Add(student);

                    }
                }
            }
        }
        return View("Index", studentList);
    }

View:

<div >
    <label name-for="file">Excel xlsx Upload</label>
    <input type="file" name="file" />      
</div>
<input type="button" name="display" value="Show Data" id="btnShow"  />

<table >
    <thead>
    <tr>
        <th>First Name</th>
        <th>Last Name</th>
        <th>AdmissionYear</th>
        <th>RegistrationNo</th>
    </tr>
    </thead>
    <tbody>

    </tbody>

</table>

As soon as the user choose the excel file to be uploaded, when he clicks on choose file, and clicks on Show Data, it should display the excel data in the table in html without even the submit upload button

I want to first display the imported data into HTML table in the view page before the user then submits and insert into the DB.

How do I achieve this?

Thanks

CodePudding user response：

You can use JavaScript to monitor the status of the file, then read the file and load it through xlsx.full.

You can refer to my test code below:

<div >
    <h2 >Convert Excel to HTML Table using JavaScript</h2>
    <div >
        <form enctype="multipart/form-data" asp-action="Upload" method="post">
            <div ><b>Select Excel File</b></div>
            <div >
                <input type="file" name="file" id="excel_file" />
            </div>
            <input  type="submit" value="Upload" />
       </form>
    </div>
    <div id="excel_data" ></div>
</div>

<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.0.0/css/bootstrap.min.css" integrity="sha384-Gn5384xqQ1aoWXA 058RXPxPg6fy4IWvTNh0E263XmFcJlSAwiGgFAW/dAiS6JXm" crossorigin="anonyous" />
<script type="text/javascript" src="https://unpkg.com/[email protected]/dist/xlsx.full.min.js"></script>
<script>
    var excel_file = document.getElementById("excel_file");
    excel_file.addEventListener("change",(event) =>{

        var reader = new FileReader();

        reader.readAsArrayBuffer(event.target.files[0]);

        reader.onload = function(event){
            var data = new Uint8Array(reader.result);

            var work_book = XLSX.read(data,{type:'array'});

            var sheet_name = work_book.SheetNames;

            var sheet_data = XLSX.utils.sheet_to_json(work_book.Sheets[sheet_name[0]],{hearder:1});
            
            if(sheet_data.length > 0)
            {
                var table_output = '<table >';

                table_output  = '<thead><tr><th>First Name </th><th> Last Name </th><th> AdmissionYear </th><th> RegistrationNo </th></tr></thead>';
                table_output  = '<tbody>';

                for(var row = 0; row < sheet_data.length; row  )
                {
                    table_output  = '<tr>';
                    table_output  = '<td>'   sheet_data[row].FirstName   '</td>';
                    table_output  = '<td>'   sheet_data[row].LastName   '</td>';
                    table_output  = '<td>'   sheet_data[row].AdmissionYear   '</td>';
                    table_output  = '<td>'   sheet_data[row].RegistrationNo   '</td>';
                    table_output  = '</tr>';
                }

                table_output  = '</tbody></table>';

                document.getElementById("excel_data").innerHTML = table_output;
            }
        }
    })

</script>

Test Result:

When you select you an Excel file:

Then you can pass the file to the controller to do what you want:

CodePudding user response：

1. You can import the Sheetjs library which can read your excel file and render it to a table. Demo
2. The library can read your data into Json, you can modify that Json to add an empty column and then hook into the render event to render the checkbox.