How to make Progress bar when uploading large excel file (and insert it in database) in mvc .net 6?

Question

view:

@{
    ViewData["Title"] = "Home Page";
}



<script>

    document.addEventListener("DOMContentLoaded", function () {
        // Get the form message element
        var formMessage = document.getElementById("formMessage");

        // Check if the element exists and has content
        if (formMessage && formMessage.innerHTML.trim() !== "") {
            // Set a timeout to hide the element after 3 seconds (3000 milliseconds)
            setTimeout(function () {
                formMessage.style.display = "none";
            }, 3000);  // 3 seconds
        }
    });
    
    function setValue(m) {

        document.getElementById("resultDiv").innerText = "Data are uploading.........";

        var myDiv = document.getElementById("myDiv");

        if (myDiv.style.display === "none") {
            myDiv.style.display = "block";
        } else {
            myDiv.style.display = "none";
        }

        //...

        var countupElement = document.getElementById('countup');
        var countupTime = 0;

        

        function updateCountup() {
            var minutes = Math.floor(countupTime / 60);
            var seconds = countupTime % 60;

            countupElement.innerHTML = 'Time elapsed: ' + minutes + 'm ' + seconds + 's';

         

            if (m != 100) {
                countupTime++;
                setTimeout(updateCountup, 1000);
            }
           
        }

        updateCountup();

        //...

        // The typewriter element
        var typeWriterElement = document.getElementById('typewriter');

        // The TextArray:
        var textArray = [
            "1% Uploading...",
            "3% Uploading...",
            "5% Uploading...",
            "7% Uploading...",
            "9% Uploading...",
            "11% Uploading...",
            "15% Uploading...",
            "19% Uploading...",
            "25% Uploading...",
            "29% Uploading...",
            "32% Uploading...",
            "36% Uploading...",
            "40% Uploading...",
            "43% Uploading...",
            "46% Uploading...",
            "50% Uploading...",
            "55% Uploading...",
            "59% Uploading...",
            "61% Uploading...",
            "65% Uploading...",
            "69% Uploading...",
            "75% Uploading...",
            "79% Uploading...",
            "82% Uploading...",
            "86% Uploading...",
            "90% Uploading...",
            "About to complete..."
        ];

        // You can also do this by transfering it through a data-attribute
        // var textArray = typeWriterElement.getAttribute('data-array');


        // function to generate the backspace effect
        function delWriter(text, i, cb) {
            if (i >= 0) {
                typeWriterElement.innerHTML = text.substring(0, i--);
                // generate a random Number to emulate backspace hitting.
                var rndBack = 10 + Math.random() * 100;
                setTimeout(function () {
                    delWriter(text, i, cb);
                }, rndBack);
            } else if (typeof cb == 'function') {
                setTimeout(cb, 1000);
            }
        };

        // function to generate the keyhitting effect
        function typeWriter(text, i, cb) {
            if (i < text.length + 1) {
                typeWriterElement.innerHTML = text.substring(0, i++);
                // generate a random Number to emulate Typing on the Keyboard.
                var rndTyping = 250 - Math.random() * 100;
                setTimeout(function () {
                    typeWriter(text, i++, cb)
                }, rndTyping);
            } else if (i === text.length + 1) {
                setTimeout(function () {
                    delWriter(text, i, cb)
                }, 1000);
            }
        };

        // the main writer function
        function StartWriter(i) {
            if (typeof textArray[i] == "undefined") {
                setTimeout(function () {
                    StartWriter(0)
                }, 1000);
            } else if (i < textArray[i].length + 1) {
                typeWriter(textArray[i], 0, function () {
                    StartWriter(i + 1);
                });
            }
        };
        // wait one second then start the typewriter
        setTimeout(function () {
            StartWriter(0);
        }, 1000);


    }

</script>

<div class="bg-dangerr">
    <h1 class="head">Upload Excel Data and insert in Database</h1>

    <div class="container">
        <div class="row  rounded p-1 ">


            <div class="col-6 offset-3">


                <h4 class="tab" id="countup">@TempData["ms"]</h4>
                <ul class="nav nav-pills mb-3" id="pills-tab" role="tablist">
                    <li class="nav-item" role="presentation">
                        <button class="nav-link active" id="pills-home-tab" data-bs-toggle="pill" data-bs-target="#pills-home" type="button" role="tab" aria-controls="pills-home" aria-selected="true">Master Sheet 3.0</button>
                    </li>
                    <li class="nav-item" role="presentation">
                        <button class="nav-link" id="pills-profile-tab" data-bs-toggle="pill" data-bs-target="#pills-profile" type="button" role="tab" aria-controls="pills-profile" aria-selected="true">Master Sheet 2.0</button>
                    </li>

                </ul>
                <div class="tab-content" id="pills-tabContent">
                    <div class="tab-pane fade show active" id="pills-home" role="tabpanel" aria-labelledby="pills-home-tab">

                        <form method="post" asp-action="ImportSheet3File" asp-controller="Sheet3" enctype="multipart/form-data">
                            <div class="form-group">
                                <label class="lab">Upload Excel File</label>
                                <input type="file" name="FormFile" class="form-control" accept=".csv, .xls, .xlsx" />
                            </div>
                            <br />
                            <input type="submit" class="btn btn-lg btn-danger" value="Upload and Insert Data" onclick="setValue(@TempData["caln"])" />

                            <div class="p-3" id="myDiv" style="display: none;">
                                <div class="progress" role="progressbar" aria-label="Animated striped example" aria-valuenow="75" aria-valuemin="0" aria-valuemax="100">
                                    @*     <div class="progress-bar progress-bar-striped progress-bar-animated" style="width: 100%">@TempData["cal"]</div> *@

                                    <div class="progress-bar progress-bar-striped progress-bar-animated"  style="width: 100%"> <span id="typewriter" data-array=""></span> <span class="cursor"></span></div>
                                </div>
                            </div>

                            <p id="formMessage" class="text-center text-success font-weight-bold">@TempData["form"]</p>


                            @{
                                if (@TempData["message"] != null)
                                {
                                        <p class="text-center text-success font-weight-bold ">@TempData["s"]</p>
                                        <p class="text-center text-success font-weight-bold ">@TempData["rs"]</p>


                                        <p class="text-center text-success font-weight-bold ">@TempData["message"]</p>
                                        <p class="text-center text-success font-weight-bold ">@TempData["alert"]</p>
                                }
                                else
                                {
                                        <div id="resultDiv"></div>
                                }
                            }
                        </form>

                    </div>
                    <div class="tab-pane fade" id="pills-profile" role="tabpanel" aria-labelledby="pills-profile-tab">
                        <form method="post" asp-action="ImportSheet2File" asp-controller="Sheet2" enctype="multipart/form-data">
                            <div class="form-group">
                                <label class="lab">Upload Excel File</label>
                                <input type="file" name="FormFile" class="form-control" accept=".csv, .xls, .xlsx" />
                            </div>
                            <br />
                            <input type="submit" class="btn btn-lg btn-danger" value="Upload and Insert Data" />
                        </form>
                    </div>

                </div>

            </div>
            <img src="/photos/m2.svg" alt="alternative-text" class="img2">
            <img src="/photos/m.svg" alt="alternative-text" class="img">

        </div>
    </div>


</div>


controller:
using Microsoft.AspNetCore.Mvc; using Microsoft.Data.SqlClient; using System.Configuration; using System.Data.OleDb; using System.Data; using AllExcelToSQLServer.Data; using OfficeOpenXml; using System.Diagnostics;  namespace AllExcelToSQLServer.Controllers.MasterSheet3 {     public class Sheet3Controller : Controller     {         private readonly IConfiguration configuration;          public ApplicationDBContext Context { get; }          public Sheet3Controller(IConfiguration configuration, ApplicationDBContext context)         {             this.configuration = configuration;             Context = context;         }         public IActionResult Index()         {             var countdownTime = TimeSpan.FromMinutes(5);             return View(countdownTime);         }         public IActionResult ErrorView()         {              return View();         }          public IActionResult ImportSheet3File()         {             return View();         }          [HttpPost]         public IActionResult ImportSheet3File(IFormFile formFile)         {             try             {                 // Start the stopwatch                 Stopwatch stopwatch = new Stopwatch();                 stopwatch.Start();                  if (formFile == null || formFile.Length == 0)                 {                      TempData["form"] = "File not selected!";                     //return Content("File not selected");                     return RedirectToAction("Index", "Home");                 }                                                   var mainPath = Path.Combine(Directory.GetCurrentDirectory(), "wwwroot", "UploadExcelFile");                 if (!Directory.Exists(mainPath))                 {                     Directory.CreateDirectory(mainPath);                 }                  var filePath = Path.Combine(mainPath, formFile.FileName);                  using (FileStream stream = new FileStream(filePath, FileMode.Create))                 {                     formFile.CopyTo(stream);                 }                  var fileName = formFile.FileName;                 string extension = Path.GetExtension(fileName);                 string conString = string.Empty;                  switch (extension)                 {                     case ".xls":                         conString = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" + filePath + "; Extended Properties='Excel 8.0; HDR=YES' ";                         break;                      case ".xlsx":                         conString = "Provider=Microsoft.ACE.OLEDB.12.0; Data Source=" + filePath + "; Extended Properties='Excel 12.0; HDR=YES;IMEX=1;' ";                         break;                  }                  DataTable dt = new DataTable();                 conString = string.Format(conString, filePath);                 using (OleDbConnection conExcel = new OleDbConnection(conString))                 {                     using (OleDbCommand cmdExcel = new OleDbCommand())                     {                         cmdExcel.CommandTimeout = 600;                         cmdExcel.CommandType = CommandType.Text;                         using (OleDbDataAdapter odaExcel = new OleDbDataAdapter())                         {                             cmdExcel.Connection = conExcel;                             conExcel.Open();                              DataTable dtExcelSchema = conExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, null);                             string sheetName = dtExcelSchema.Rows[0]["TABLE_NAME"].ToString();                              cmdExcel.CommandText = "SELECT COUNT(*) FROM [" + sheetName + "]";                             int rowCount = (int)cmdExcel.ExecuteScalar();                              cmdExcel.CommandText = "SELECT * FROM [" + sheetName + "]";                              odaExcel.SelectCommand = cmdExcel;                                                            odaExcel.Fill(dt);                                                           int forProgressBar = dt.Rows.Count;                              int cal = (forProgressBar * 100) / rowCount;                              TempData["cal"] = " " + cal + "% ";                             TempData["caln"] = " " + cal + " ";                                                           int columnInde = 5;                                                        // Apply filter to the DataTable based on the values in column 6 (assuming 1-based index)                             int columnIndex = 6;                              for (int i = dt.Rows.Count - 1; i >= 0; i--)                             {                                 var cellValue = dt.Rows[i][columnIndex - 1].ToString();                                  var parts = cellValue.Split('_');                                  // Ensure that there are at least 4 parts before accessing the fourth part                                 if (parts.Length < 4)                                 {                                     // Remove the row from the DataTable if the condition is not met                                     dt.Rows.RemoveAt(i);                                 }                                 else                                 {                                     // Update the value in the DataTable with the extracted value                                     dt.Rows[i][columnIndex - 1] = parts[3];                                 }                                                          }                             ExcelPackage.LicenseContext = LicenseContext.NonCommercial;                             using (var package = new ExcelPackage(new FileInfo(filePath)))                             {                                 // Assuming there is only one worksheet in the Excel file                                 var worksheet = package.Workbook.Worksheets[0];                                  // Get the dimensions of the worksheet                                 var startCell = worksheet.Dimension.Start;                                 var endCell = worksheet.Dimension.End;                                  // Loop through each cell in the worksheet                                 for (int row = endCell.Row; row > startCell.Row; row--)                                 {                                      // Read cell value as string                                     string cellValue = worksheet.Cells[row, 5].Text;                                      dt.Rows[row - 2][4] = cellValue;                                  }                             }                              TempData["alert"] = " " + dt.Rows.Count + " Data Are Filled";                              conExcel.Close();                         }                     }                 }                  conString = configuration.GetConnectionString("sqlconnection");                 using (SqlConnection con = new SqlConnection(conString))                 {                     try                     {                         // Add a "CreatedAt" column to the DataTable with DateTime data type                         dt.Columns.Add("CreatedAt", typeof(DateTime));                          // Set the "CreatedAt" column value for each row in the DataTable                         DateTime currentDateTime = DateTime.Now;                         foreach (DataRow row in dt.Rows)                         {                             row["CreatedAt"] = currentDateTime;                         }                                                   using (SqlBulkCopy sqlBulkCopy = new SqlBulkCopy(con))                         {                             sqlBulkCopy.DestinationTableName = "Sheet3Class";                              // Column mappings (assuming "CreatedAt" is the last column)                             for (int i = 0; i < dt.Columns.Count; i++)                             {                                 sqlBulkCopy.ColumnMappings.Add(dt.Columns[i].ColumnName, dt.Columns[i].ColumnName);                             }                              sqlBulkCopy.BatchSize = 20000;                             con.Open();                             sqlBulkCopy.WriteToServer(dt);                             con.Close();                         }                                             }                     catch (Exception ex)                     {                          TempData["ErrorMessage"] = $"An error occurred: {ex.Message}";                         return RedirectToAction("ErrorView");                     }                  }                  // Stop the stopwatch                 stopwatch.Stop();                  // Calculate the speed                 double elapsedTimeInSeconds = stopwatch.ElapsedMilliseconds / 1000.0;                 double speedInRowsPerSecond = dt.Rows.Count / elapsedTimeInSeconds;                  int minutes = (int)Math.Floor(elapsedTimeInSeconds / 60);                 double remainingSeconds = (int)Math.Floor(elapsedTimeInSeconds % 60);                  TempData["s"] = " " + elapsedTimeInSeconds + " seconds";                 TempData["rs"] = " " + speedInRowsPerSecond + " rows/second";                  TempData["ms"] = "Time elapsed: " + minutes + " Minutes " + remainingSeconds + " Second";                  TempData["message"] = "File Imported Successfully, Data Save into DataBase!";                  //return RedirectToAction("Index");                  //return View();             }               catch (Exception ex)             {                  TempData["ErrorMessage"] = $"An error occurred: {ex.Message}";                 return RedirectToAction("ErrorView");             }              //return View();             return RedirectToAction("Index", "Home");         }     } } 

, i shared my view and controller code , in this code static progress bar is added, but i want dynamic progress bar (means progress bar showing % of consuming time during file uploading and inserting), please help me .... use .net 6 and above. thank you

Answer 1

this is complex problem. you can get the file upload progress easily by uploading via ajax, and using the progress event: something like:

function doSubmit() {
    const form = document.querySelector('FORM');
    const formdata = new FormData(form);
    const action = form.action;
    const fileInput = document.querySelector('[name="FormFile"]');
    const request = new XMLHttpRequest();

    request.upload.addEventListener('progress', function (e) {
        const fileSize = fileInput.files[0].size;

        if (e.loaded <= fileSize) {
            const percent = Math.round(e.loaded / fileSize * 100);
            displayProgress(percent);
        } 

        if(e.loaded == e.total){
            displayProgress(100);
        }
    });   

    request.open('post', action);
    request.send(formdata);
}

to get the server processing time is more complex. first the MVC action has to somehow determine what percent complete it is. then it sent a way to notify the client of its progress. once you have figured out the first. then the easiest is to store the progress in a database or distributed cache key by request id (stored in a form field). the javascript can than use ajax to poll another action on the progress.

a common approach with progress bars is to guess. say the guess is 5 second (you could base on file size). the process moves for evenly a couple seconds updating say every 100ms, then you start to slow down, only going less and less each update.

you can combine the two. say the upload is 90% and server process 10%. then use progress events for the first 90%, then guesses for the las 10%.