Untitled

using ClosedXML.Excel;
using FoxHub_TPT.Areas.TPT.Enums;
using System.Data;

public class ExcelUtility
{
    public byte[] ExportToExcel(IEnumerable<BookingOrderVM> orders, IEnumerable<TruckType> truckTypes, string uom)
    {
        using (var workbook = new XLWorkbook())
        {
            var worksheet = workbook.Worksheets.Add("Booking Portal");
            var currentRow = 1;
            var dropdownSheet = workbook.Worksheets.Add("Dropdowns");
            CreateDropdownLists(dropdownSheet, truckTypes);

            AddHeaders(worksheet, ref currentRow, true, uom);
            worksheet.Row(1).Style.Protection.SetLocked(true);

            foreach (var order in orders)
            {
                var orderStartRow = currentRow;
                AddDispatchRow(worksheet, ref currentRow, order, uom);
                worksheet.Row(orderStartRow).Style.Protection.SetLocked(true);

                AddHeaders(worksheet, ref currentRow, false, uom);
                worksheet.Row(currentRow - 1).Style.Protection.SetLocked(true);

                foreach (var booking in order.Bookings)
                {
                    AddBookingRow(worksheet, ref currentRow, booking, uom, orderStartRow);
                }
                for (int i = 0; i < 3; i++)
                {
                    AddBookingRow(worksheet, ref currentRow, new Booking(), uom, orderStartRow);
                }
                AddRemainingRow(worksheet, ref currentRow, orderStartRow, uom);
                worksheet.Row(currentRow - 1).Style.Protection.SetLocked(true);

                currentRow++;
            }
            AddDropdowns(worksheet, truckTypes);
            worksheet.Columns().AdjustToContents();
            worksheet.Protect()
                            .AllowElement(XLSheetProtectionElements.SelectLockedCells)
                            .AllowElement(XLSheetProtectionElements.SelectUnlockedCells)
                            .AllowElement(XLSheetProtectionElements.FormatCells)
                            .AllowElement(XLSheetProtectionElements.InsertRows)
                            .AllowElement(XLSheetProtectionElements.DeleteRows);
            
            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                return stream.ToArray();
            }
        }
    }
    
    private void AddRemainingRow(IXLWorksheet worksheet, ref int currentRow, int orderStartRow, string uom)
    {
        int column = 1;
        var remainingCell = worksheet.Cell(currentRow, column++);
        remainingCell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185);
        remainingCell.Style.Font.Bold = true;

        for (int i = column; i < 8; i++)
        {
            var cell = worksheet.Cell(currentRow, i);
            cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185);
            cell.Style.Font.Bold = true;
        }

        worksheet.Cell(currentRow, column++).Value = "Remaining";

        column = 8;

        foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>())
        {
            var dispatchRowValue = worksheet.Cell(orderStartRow, column).Value.ToString();
            var cell = worksheet.Cell(currentRow, column);
            cell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 218, 185);
            cell.Style.Font.Bold = true;
            if (dispatchRowValue != "")
            {
                var bookingsStartRow = orderStartRow + 2;
                var bookingsEndRow = currentRow - 1;
                var columnLetter = GetExcelColumnName(column);

                cell.FormulaA1 = $"={columnLetter}{orderStartRow}-SUM({columnLetter}{bookingsStartRow}:{columnLetter}{bookingsEndRow})";
            }
            else
            {
                cell.Value = "";
            }

            cell.Style.Protection.SetLocked(true);
            column++;
        }

        currentRow++;
    }
    private void CreateDropdownLists(IXLWorksheet dropdownSheet, IEnumerable<TruckType> truckTypes)
    {
        for (int i = 1; i <= 10; i++)
        {
            dropdownSheet.Cell(i, 1).Value = i;
        }
        dropdownSheet.Workbook.NamedRanges.Add("TripNumbers", dropdownSheet.Range(dropdownSheet.Cell(1, 1), dropdownSheet.Cell(10, 1)));

        int row = 1;
        for (int hour = 0; hour < 24; hour++)
        {
            for (int minute = 0; minute < 60; minute += 15)
            {
                var time = new DateTime(2000, 1, 1, hour, minute, 0);
                dropdownSheet.Cell(row, 2).Value = time;
                dropdownSheet.Cell(row, 2).Style.NumberFormat.Format = "HH:mm";
                row++;
            }
        }
        dropdownSheet.Workbook.NamedRanges.Add("TimePicker", dropdownSheet.Range(dropdownSheet.Cell(1, 2), dropdownSheet.Cell(96, 2)));

        row = 1;
        foreach (var truckType in truckTypes)
        {
            dropdownSheet.Cell(row, 3).Value = truckType.Id;
            row++;
        }
        dropdownSheet.Workbook.NamedRanges.Add("TruckTypes", dropdownSheet.Range(dropdownSheet.Cell(1, 3), dropdownSheet.Cell(row - 1, 3)));
    }

    private void AddDropdowns(IXLWorksheet worksheet, IEnumerable<TruckType> truckTypes)
    {
        var usedRange = worksheet.RangeUsed();
        var lastRow = usedRange.LastRow().RowNumber();

        for (int row = 1; row <= lastRow; row++)
        {
            if (IsHeaderOrDispatchRow(worksheet.Row(row)))
                continue;

            var tripCell = worksheet.Cell(row, 3);
            var tripValidation = tripCell.SetDataValidation();
            tripValidation.List("=TripNumbers");

            var truckTypeCell = worksheet.Cell(row, 4);
            var truckTypeValidation = truckTypeCell.SetDataValidation();
            truckTypeValidation.List("=TruckTypes");

            var arrivalCell = worksheet.Cell(row, 5);
            var timeValidation = arrivalCell.SetDataValidation();
            timeValidation.List("=TimePicker");
            arrivalCell.Style.NumberFormat.Format = "HH:mm";
        }
    }

    private bool IsHeaderOrDispatchRow(IXLRow row)
    {
        var firstCell = row.Cell(1);
        var backgroundColor = firstCell.Style.Fill.BackgroundColor;
        return backgroundColor == XLColor.Green || backgroundColor == XLColor.Gray;
    }

    private void AddHeaders(IXLWorksheet worksheet, ref int currentRow, bool isMainHeader, string uom)
    {
        var headers = isMainHeader ? GetMainHeaders(uom) : GetBookingHeaders(uom);
        for (int i = 0; i < headers.Length; i++)
        {
            var cell = worksheet.Cell(currentRow, i + 1);
            cell.Value = headers[i];

            if (isMainHeader)
            {
                cell.Style.Fill.BackgroundColor = XLColor.Green;
                cell.Style.Font.FontColor = XLColor.White;
            }
            else
            {
                cell.Style.Fill.BackgroundColor = XLColor.LightGray;
            }
            cell.Style.Font.Bold = true;
            cell.Style.Alignment.WrapText = true;
            cell.Style.Alignment.Vertical = XLAlignmentVerticalValues.Center;
        }
        currentRow++;
    }

    private void AddDispatchRow(IXLWorksheet worksheet, ref int currentRow, BookingOrderVM order, string uom)
    {
        int column = 1;
        int lastColumn = 7 + Enum.GetValues(typeof(DispatchType)).Length; // Calculate last column

        for (int i = 1; i <= lastColumn; i++)
        {
            var cell = worksheet.Cell(currentRow, i);
            cell.Style.Fill.BackgroundColor = XLColor.Gray;
            cell.Style.Font.Bold = true;
        }

        worksheet.Cell(currentRow, column++).Value = order.OrderSummaryId;
        worksheet.Cell(currentRow, column++).Value = order.OrderSummary.Customer?.CustomerName;
        worksheet.Cell(currentRow, column++).Value = order.OrderSummary.Customer?.Region;
        worksheet.Cell(currentRow, column++).Value = $"-";
        worksheet.Cell(currentRow, column++).Value = "-";

        switch (uom.ToLower())
        {
            case "tons":
                worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalTons;
                worksheet.Cell(currentRow, column++).Value = 0;
                break;
            case "pallets":
                worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalPallets;
                worksheet.Cell(currentRow, column++).Value = 0;
                break;
            case "volume":
                worksheet.Cell(currentRow, column++).Value = order.OrderSummary.TotalVolume;
                worksheet.Cell(currentRow, column++).Value = 0;
                break;
        }

        foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>())
        {
            var details = order.OrderSummary?.DispatchDetails?.FirstOrDefault(d => d.LoadType == type);
            var cell = worksheet.Cell(currentRow, column++);

            if (details == null)
            {
                cell.Value = "";
            }
            else
            {
                switch (uom.ToLower())
                {
                    case "tons":
                        cell.Value = details.Tons;
                        break;
                    case "pallets":
                        cell.Value = details.Pallets;
                        break;
                    case "volume":
                        cell.Value = details.Volume;
                        break;
                }
            }
        }

        currentRow++;
    }

    private void AddBookingRow(IXLWorksheet worksheet, ref int currentRow, Booking booking, string uom, int orderStartRow)
    {
        int column = 1;

        var idCell = worksheet.Cell(currentRow, column++);
        idCell.Value = booking.Id;
        idCell.Style.Protection.SetLocked(true);

        var truckCell = worksheet.Cell(currentRow, column++);
        truckCell.Value = booking.Truck;
        truckCell.Style.Protection.SetLocked(false);

        var tripCell = worksheet.Cell(currentRow, column++);
        tripCell.Value = booking.Trip;
        tripCell.Style.Protection.SetLocked(false);

        var truckTypeCell = worksheet.Cell(currentRow, column++);
        truckTypeCell.Value = booking.TruckTypeId;
        truckTypeCell.Style.Protection.SetLocked(false);

        var arrivalCell = worksheet.Cell(currentRow, column++);
        if (booking.ArrivalPlanned.HasValue)
        {
            arrivalCell.Value = booking.ArrivalPlanned.Value.ToString("HH:mm");
        }
        arrivalCell.Style.Protection.SetLocked(false);

        var availableCell = worksheet.Cell(currentRow, column++);
        switch (uom.ToLower())
        {
            case "tons":
                availableCell.Value = booking.TruckAvailableTons;
                break;
            case "pallets":
                availableCell.Value = booking.TruckAvailablePallets;
                break;
            case "volume":
                availableCell.Value = booking.TruckAvailableVolume;
                break;
        }
        availableCell.Style.Protection.SetLocked(false);

        var remainingCell = worksheet.Cell(currentRow, column++);
        var columnLetter = GetExcelColumnName(column - 2);
        var dispatchTypeColumns = GetDispatchTypeColumns(currentRow, uom);
        remainingCell.FormulaA1 = $"={columnLetter}{currentRow}-SUM({dispatchTypeColumns})";
        remainingCell.Style.Protection.SetLocked(true);

        foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>())
        {
            var dispatchCell = worksheet.Cell(currentRow, column);
            var dispatchRowValue = worksheet.Cell(orderStartRow, column).Value.ToString();

            if (dispatchRowValue == "")
            {
                dispatchCell.Style.Protection.SetLocked(true);
                dispatchCell.Value = "";
            }
            else
            {
                var details = booking.BookingOrders?.FirstOrDefault()?.DispatchDetails
                    .FirstOrDefault(d => d.LoadType == type);

                switch (uom.ToLower())
                {
                    case "tons":
                        dispatchCell.Value = details?.Tons ?? 0;
                        break;
                    case "pallets":
                        dispatchCell.Value = details?.Pallets ?? 0;
                        break;
                    case "volume":
                        dispatchCell.Value = details?.Volume ?? 0;
                        break;
                }
                dispatchCell.Style.Protection.SetLocked(false);
            }
            column++;
        }

        currentRow++;
    }
    private string GetExcelColumnName(int columnNumber)
    {
        string columnName = "";
        while (columnNumber > 0)
        {
            int remainder = (columnNumber - 1) % 26;
            char columnChar = (char)('A' + remainder);
            columnName = columnChar + columnName;
            columnNumber = (columnNumber - 1) / 26;
        }
        return columnName;
    }
    private string GetDispatchTypeColumns(int row, string uom)
    {
        var dispatchTypes = Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>();
        var startColumn = 8;
        var columns = new List<string>();

        foreach (var type in dispatchTypes)
        {
            columns.Add($"{GetExcelColumnName(startColumn++)}{row}");
        }

        return string.Join(",", columns);
    }

    private string[] GetMainHeaders(string uom)
    {
        var headers = new List<string>
        {
            "ID",
            "Customer Name",
            "Region",
            "Window",
            "Expected Arrival"
        };

        switch (uom.ToLower())
        {
            case "tons":
                headers.AddRange(new[] { "Total Tons", "Fulfilled Tons" });
                break;
            case "pallets":
                headers.AddRange(new[] { "Total Pallets", "Fulfilled Pallets" });
                break;
            case "volume":
                headers.AddRange(new[] { "Total Volume", "Fulfilled Volume" });
                break;
        }

        foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>())
        {
            switch (uom.ToLower())
            {
                case "tons":
                    headers.Add($"{type} Tons");
                    break;
                case "pallets":
                    headers.Add($"{type} Pallets");
                    break;
                case "volume":
                    headers.Add($"{type} Volume");
                    break;
            }
        }

        return headers.ToArray();
    }

    private string[] GetBookingHeaders(string uom)
    {
        var headers = new List<string>
        {
            "Booking ID",
            "Truck",
            "Trip",
            "Type",
            "Arrival Time"
        };

        switch (uom.ToLower())
        {
            case "tons":
                headers.AddRange(new[] { "Available Tons", "Remaining Tons" });
                break;
            case "pallets":
                headers.AddRange(new[] { "Available Pallets", "Remaining Pallets" });
                break;
            case "volume":
                headers.AddRange(new[] { "Available Volume", "Remaining Volume" });
                break;
        }

        foreach (var type in Enum.GetValues(typeof(DispatchType)).Cast<DispatchType>())
        {
            switch (uom.ToLower())
            {
                case "tons":
                    headers.Add($"{type} Tons");
                    break;
                case "pallets":
                    headers.Add($"{type} Pallets");
                    break;
                case "volume":
                    headers.Add($"{type} Volume");
                    break;
            }
        }

        return headers.ToArray();
    }

    public IEnumerable<BookingOrderVM> ImportFromExcel(Stream excelStream)
    {
        using (var workbook = new XLWorkbook(excelStream))
        {
            var worksheet = workbook.Worksheet(1);
            var bookingOrders = new List<BookingOrderVM>();
            var currentRow = 2;

            while (!worksheet.Row(currentRow).IsEmpty())
            {
                var order = ParseDispatchRow(worksheet, ref currentRow);
                currentRow += 2;

                while (!worksheet.Row(currentRow).IsEmpty() && !IsDispatchRow(worksheet.Row(currentRow)))
                {
                    var booking = ParseBookingRow(worksheet, currentRow, order.OrderSummaryId);
                    order.Bookings.Add(booking);
                    currentRow++;
                }

                bookingOrders.Add(order);
                currentRow++;
            }

            return bookingOrders;
        }
    }

    private BookingOrderVM ParseDispatchRow(IXLWorksheet worksheet, ref int row)
    {
        throw new NotImplementedException();
    }

    private Booking ParseBookingRow(IXLWorksheet worksheet, int row, int orderSummaryId)
    {
        throw new NotImplementedException();
    }

    private bool IsDispatchRow(IXLRow row)
    {
        throw new NotImplementedException();
    }
}




iam using closedxml , i need insert row and delete row functionality in bookingrow for each dispatchrow
you can give a button below bookingrow onclicking that same bookingrow adds up
if you want to do it with macro by changing library do it .
dont change the existing c# one keep all the existing c# code i just need insertrow delete row to be in macro