Untitled

public class ExcelUtility
{
    // ... (keep all existing code) ...

    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);

            // Add a hidden helper sheet for tracking row configurations
            var helperSheet = workbook.Worksheets.Add("RowConfig");
            helperSheet.Hide();

            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);

                // Store order start row in helper sheet
                helperSheet.Cell(orderStartRow, 1).Value = "OrderStart";

                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);
                    // Mark booking rows in helper sheet
                    helperSheet.Cell(currentRow - 1, 1).Value = "Booking";
                    helperSheet.Cell(currentRow - 1, 2).Value = orderStartRow;
                }

                for (int i = 0; i < 3; i++)
                {
                    AddBookingRow(worksheet, ref currentRow, new Booking(), uom, orderStartRow);
                    // Mark empty booking rows in helper sheet
                    helperSheet.Cell(currentRow - 1, 1).Value = "Booking";
                    helperSheet.Cell(currentRow - 1, 2).Value = orderStartRow;
                }

                // Add the button row before remaining row
                AddButtonRow(worksheet, ref currentRow, orderStartRow);
                
                AddRemainingRow(worksheet, ref currentRow, orderStartRow, uom);
                worksheet.Row(currentRow - 1).Style.Protection.SetLocked(true);

                // Mark remaining row in helper sheet
                helperSheet.Cell(currentRow - 1, 1).Value = "Remaining";
                helperSheet.Cell(currentRow - 1, 2).Value = orderStartRow;

                currentRow++;
            }

            // Add worksheet change event handling formula
            AddWorksheetChangeHandler(worksheet, helperSheet);

            AddDropdowns(worksheet, truckTypes);
            worksheet.Columns().AdjustToContents();
            
            // Modify protection to allow using formulas
            worksheet.Protect()
                .AllowElement(XLSheetProtectionElements.SelectLockedCells)
                .AllowElement(XLSheetProtectionElements.SelectUnlockedCells)
                .AllowElement(XLSheetProtectionElements.FormatCells)
                .AllowElement(XLSheetProtectionElements.InsertRows)
                .AllowElement(XLSheetProtectionElements.DeleteRows)
                .AllowElement(XLSheetProtectionElements.UseAutoFilter)
                .AllowElement(XLSheetProtectionElements.Sort)
                .AllowElement(XLSheetProtectionElements.UsePivotTables);

            using (var stream = new MemoryStream())
            {
                workbook.SaveAs(stream);
                return stream.ToArray();
            }
        }
    }

    private void AddButtonRow(IXLWorksheet worksheet, ref int currentRow, int orderStartRow)
    {
        var addButtonCell = worksheet.Cell(currentRow, 1);
        addButtonCell.Value = "➕ Add Row";
        addButtonCell.Style.Fill.BackgroundColor = XLColor.FromArgb(200, 230, 201);
        addButtonCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        addButtonCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        addButtonCell.Style.Font.Bold = true;

        var comment = addButtonCell.CreateComment();
        comment.AddText("To add a new row:");
        comment.AddNewLine();
        comment.AddText("1. Select this row");
        comment.AddNewLine();
        comment.AddText("2. Right-click → Insert → Copy formulas from above");
        
        worksheet.Range(currentRow, 1, currentRow, 3).Merge();
        
        var deleteInstructionCell = worksheet.Cell(currentRow, 4);
        deleteInstructionCell.Value = "✖ Delete: Select row → Right-click → Delete";
        deleteInstructionCell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 205, 210);
        deleteInstructionCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
        deleteInstructionCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
        deleteInstructionCell.Style.Font.Bold = true;
        
        worksheet.Range(currentRow, 4, currentRow, 7).Merge();
        
        currentRow++;
    }

    private void AddWorksheetChangeHandler(IXLWorksheet worksheet, IXLWorksheet helperSheet)
    {
        // Add a named range for the entire used range
        var usedRange = worksheet.RangeUsed();
        worksheet.Workbook.NamedRanges.Add("UsedRange", usedRange);

        // Add a worksheet-level formula to detect changes
        var formulaCell = worksheet.Cell(1, worksheet.ColumnCount() + 1);
        formulaCell.FormulaA1 = "=IF(ROW()>1,INDIRECT(\"RC[-1]\",FALSE),\"\")";
        formulaCell.Style.Protection.SetLocked(true);
        
        // Add helper formulas for row operations
        var helperFormula = helperSheet.Cell(1, 3);
        helperFormula.FormulaA1 = "=COUNTA(UsedRange)";
        
        // Add auto-configuration formula for new rows
        for (int row = 2; row <= usedRange.RowCount(); row++)
        {
            var configCell = helperSheet.Cell(row, 3);
            configCell.FormulaA1 = $"=IF(AND(ROW()>1,INDIRECT(\"A\"&ROW())=\"Booking\"),ConfigureNewRow(ROW()),\"\")";
        }
    }

    // Helper function to configure new rows (called by Excel formulas)
    private string ConfigureNewRow(int row)
    {
        var worksheet = (IXLWorksheet)Workbook.Worksheet("Booking Portal");
        var helperSheet = (IXLWorksheet)Workbook.Worksheet("RowConfig");
        
        // Get order start row from helper sheet
        var orderStartRow = int.Parse(helperSheet.Cell(row, 2).Value.ToString());
        
        // Copy validations and formulas from row above
        CopyRowConfiguration(worksheet, row - 1, row, orderStartRow);
        
        return "Configured";
    }

    private void CopyRowConfiguration(IXLWorksheet worksheet, int sourceRow, int targetRow, int orderStartRow)
    {
        // Copy data validation
        CopyDataValidation(worksheet.Row(sourceRow), worksheet.Row(targetRow));
        
        // Copy formulas and formatting
        CopyFormulasAndFormatting(worksheet.Row(sourceRow), worksheet.Row(targetRow));
        
        // Update remaining calculations
        UpdateRemainingCalculations(worksheet, targetRow, orderStartRow);
    }

    private void CopyDataValidation(IXLRow sourceRow, IXLRow targetRow)
    {
        // Trip dropdown
        var tripValidation = targetRow.Cell(3).SetDataValidation();
        tripValidation.List("=TripNumbers");
        
        // Truck type dropdown
        var truckTypeValidation = targetRow.Cell(4).SetDataValidation();
        truckTypeValidation.List("=TruckTypes");
        
        // Time picker
        var timeValidation = targetRow.Cell(5).SetDataValidation();
        timeValidation.List("=TimePicker");
        targetRow.Cell(5).Style.NumberFormat.Format = "HH:mm";
    }

    private void CopyFormulasAndFormatting(IXLRow sourceRow, IXLRow targetRow)
    {
        for (int col = 1; col <= sourceRow.Worksheet.ColumnCount(); col++)
        {
            var sourceCell = sourceRow.Cell(col);
            var targetCell = targetRow.Cell(col);
            
            if (sourceCell.HasFormula)
            {
                targetCell.FormulaA1 = sourceCell.FormulaA1;
            }
            
            targetCell.Style = sourceCell.Style;
        }
    }

    private void UpdateRemainingCalculations(IXLWorksheet worksheet, int row, int orderStartRow)
    {
        // Update the remaining row calculations
        var remainingRow = FindRemainingRow(worksheet, orderStartRow);
        if (remainingRow > 0)
        {
            UpdateRemainingRowFormulas(worksheet, remainingRow, orderStartRow);
        }
    }

    private int FindRemainingRow(IXLWorksheet worksheet, int orderStartRow)
    {
        var currentRow = orderStartRow + 1;
        while (!worksheet.Row(currentRow).IsEmpty())
        {
            if (worksheet.Cell(currentRow, 1).Value.ToString() == "Remaining")
            {
                return currentRow;
            }
            currentRow++;
        }
        return -1;
    }

    // ... (keep rest of existing code) ...
}