create a bulk upload from excel

Reading a large amount of data from an Excel file might require an optimized approach, so you can use EPPlus and batch processing to handle large Excel files. Check the below example, this may help you.

public class EmployeeAttendance
{
    public long EmployeeID { get; set; }
    public DateTime Date { get; set; }
    public string AttendanceStatus { get; set; }
}

using OfficeOpenXml;

public ActionResult ReadAndProcessExcel()
{
    string filePath = @"D:\ATTENDANCE.xlsx";

    try
    {
        int batchSize = 1000; // Process 1000 rows at a time
        if (!string.IsNullOrEmpty(filePath))
        {
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (var package = new ExcelPackage(new System.IO.FileInfo(filePath)))
            {
                ExcelWorksheet worksheet = package.Workbook.Worksheets[0];
                int rowCount = worksheet.Dimension.Rows;
                int columnCount = worksheet.Dimension.Columns;

                if (rowCount > 1)
                {
                    for (int startRow = 2; startRow <= rowCount; startRow += batchSize)
                    {
                        int endRow = Math.Min(startRow + batchSize - 1, rowCount);
                        List employeeAttendances = new List();

                        for (int row = startRow; row <= endRow; row++)
                        {
                            var employeeId = Convert.ToInt64(worksheet.Cells[row, 1].Value);

                            for (int col = 2; col <= columnCount; col++)
                            {
                                var date = DateTime.FromOADate(Convert.ToDouble(worksheet.Cells[1, col].Value)); // Assuming dates start from second row, second column
                                var attendanceStatus = worksheet.Cells[row, col].Value?.ToString();

                                var attendance = new EmployeeAttendance
                                {
                                    EmployeeID = employeeId,
                                    Date = date,
                                    AttendanceStatus = attendanceStatus
                                };

                                employeeAttendances.Add(attendance);
                            }
                        }

                        // Perform bulk insert into SQL Server database for this batch
                        BulkInsertToDatabase(employeeAttendances);
                    }

                    return Json(new { Success = "Success" });
                }
                else
                {
                    return Json(new { ErrorMsg = "No data available in excel file" });
                }
            }
        }
        else
        {
            return Json(new { ErrorMsg = "filePath is empty" });
        }
    }
    catch (Exception ex)
    {
        throw ex;
    }
}

public void BulkInsertToDatabase(List employeeAttendances)
{
    DataTable dataTable = ConvertListToDataTable(employeeAttendances);
    string connectionString = "Your_SQL_Server_Connection_String";
    using (SqlConnection connection = new SqlConnection(connectionString))
    {
        connection.Open();
        using (SqlBulkCopy bulkCopy = new SqlBulkCopy(connection))
        {
            bulkCopy.DestinationTableName = "Your_Destination_Table_Name";
            foreach (DataColumn col in dataTable.Columns)
            {
                bulkCopy.ColumnMappings.Add(col.ColumnName, col.ColumnName);
            }
            bulkCopy.WriteToServer(dataTable);
        }
    }
}

private DataTable ConvertListToDataTable(List data)
{
    DataTable dataTable = new DataTable();
    var properties = typeof(T).GetProperties();

    foreach (var prop in properties)
    {
        dataTable.Columns.Add(prop.Name, prop.PropertyType);
    }

    foreach (T item in data)
    {
        DataRow row = dataTable.NewRow();
        foreach (var prop in properties)
        {
            row[prop.Name] = prop.GetValue(item);
        }
        dataTable.Rows.Add(row);
    }
    return dataTable;
}