Untitled

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

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

    private void AddBookingRow(IXLWorksheet worksheet, ref int currentRow, Booking booking, string uom, int orderStartRow)
    {
        // ... (existing AddBookingRow code) ...

        currentRow++;

        // Check if this is the last booking row in the group (before the "Remaining" row)
        var nextRow = worksheet.Row(currentRow);
        if (nextRow.Cell(1).Value.ToString() == "Remaining")
        {
            // Add instructions for adding/deleting rows
            var addButtonCell = worksheet.Cell(currentRow, 1);
            addButtonCell.Value = "➕ Add Row";
            addButtonCell.Style.Fill.BackgroundColor = XLColor.FromArgb(200, 230, 201); // Light green
            addButtonCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            addButtonCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            addButtonCell.Style.Font.Bold = true;
            
            // Add comment with instructions
            var comment = addButtonCell.CreateComment();
            comment.AddText("To add a new row:");
            comment.AddNewLine();
            comment.AddText("1. Right-click this row");
            comment.AddNewLine();
            comment.AddText("2. Select 'Insert' → 'Insert Row Above'");
            comment.AddNewLine();
            comment.AddText("3. Copy formulas from row above");
            
            // Merge cells for button appearance
            worksheet.Range(currentRow, 1, currentRow, 3).Merge();
            
            // Add delete instructions in the next cell
            var deleteInstructionCell = worksheet.Cell(currentRow, 4);
            deleteInstructionCell.Value = "✖ Delete: Right-click row → Delete";
            deleteInstructionCell.Style.Fill.BackgroundColor = XLColor.FromArgb(255, 205, 210); // Light red
            deleteInstructionCell.Style.Border.OutsideBorder = XLBorderStyleValues.Thin;
            deleteInstructionCell.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
            deleteInstructionCell.Style.Font.Bold = true;
            
            // Merge cells for delete instruction
            worksheet.Range(currentRow, 4, currentRow, 7).Merge();
            
            currentRow++;
        }
    }

    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;

            // Skip the button/instruction rows
            if (worksheet.Cell(row, 1).Value.ToString() == "➕ Add Row")
                continue;

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

    // Add this helper method to properly handle new rows
    private void ConfigureNewRow(IXLWorksheet worksheet, int newRowNum, int orderStartRow)
    {
        // Copy data validation (dropdowns) from the row above
        var sourceRow = worksheet.Row(newRowNum - 1);
        var targetRow = worksheet.Row(newRowNum);
        
        // Copy validation for Trip column
        var tripValidation = targetRow.Cell(3).SetDataValidation();
        tripValidation.List("=TripNumbers");
        
        // Copy validation for TruckType column
        var truckTypeValidation = targetRow.Cell(4).SetDataValidation();
        truckTypeValidation.List("=TruckTypes");
        
        // Copy validation for ArrivalTime column
        var timeValidation = targetRow.Cell(5).SetDataValidation();
        timeValidation.List("=TimePicker");
        targetRow.Cell(5).Style.NumberFormat.Format = "HH:mm";
        
        // Copy formulas and formatting
        for (int col = 1; col <= worksheet.ColumnCount(); col++)
        {
            var sourceCell = sourceRow.Cell(col);
            var targetCell = targetRow.Cell(col);
            
            if (sourceCell.HasFormula)
            {
                targetCell.FormulaA1 = sourceCell.FormulaA1;
            }
            
            targetCell.Style = sourceCell.Style;
        }
        
        // Clear any values but keep formatting
        for (int col = 1; col <= worksheet.ColumnCount(); col++)
        {
            if (!worksheet.Cell(newRowNum, col).HasFormula)
            {
                worksheet.Cell(newRowNum, col).Value = "";
            }
        }
    }
}