using DocumentFormat.OpenXml.Packaging;
using DocumentFormat.OpenXml.Spreadsheet;
using DocumentFormat.OpenXml;
using System.Data;
using System.Text.RegularExpressions;
using Font = DocumentFormat.OpenXml.Spreadsheet.Font;
using Color = DocumentFormat.OpenXml.Spreadsheet.Color;

namespace qdr.app.studiou.orders2printpack.Labels
{
    /// <summary>
    /// Generates Excel documents for printing labels using OpenXML format
    /// without relying on Microsoft Office libraries.
    /// </summary>
    public class ExcelLabelGenerator
    {
        // Default row height in points
        private const double DEFAULT_ROW_HEIGHT = 15.0;

        // Border styles
        private const int BORDER_STYLE_ID = 1;

        // Template pattern to match column names
        private static readonly Regex TemplatePattern = new Regex(@"\{([^{}]+)\}", RegexOptions.Compiled);

        // Formatting patterns
        private static readonly Regex BoldPattern = new Regex(@"\*\*(.+?)\*\*", RegexOptions.Compiled);
        private static readonly Regex ItalicPattern = new Regex(@"__(.+?)__", RegexOptions.Compiled);
        private static readonly Regex UnderlinePattern = new Regex(@"_(.+?)_", RegexOptions.Compiled);
        private static readonly Regex FontSizePattern = new Regex(@"\^\^(\d+)\^(.*?)\^\^", RegexOptions.Compiled);

        // Style indexes for different formatting
        private const int STYLE_NORMAL = 0;
        private const int STYLE_BORDER = 1;
        private const int STYLE_BOLD = 2;
        private const int STYLE_ITALIC = 3;
        private const int STYLE_UNDERLINE = 4;
        private const int STYLE_BOLD_BORDER = 5;
        private const int STYLE_ITALIC_BORDER = 6;
        private const int STYLE_UNDERLINE_BORDER = 7;

        // Font size style indexes start at 8 and go up
        private const int STYLE_FONT_SIZE_START = 8;

        /// <summary>
        /// Label format template that will be used to generate the content of each label.
        /// Use {columnName} placeholders to reference DataTable columns.
        /// Example: "ID: {ID}\nName: {ProductName}\nPrice: ${Price}"
        /// </summary>
        private readonly string _labelTemplate;

        /// <summary>
        /// Initializes a new instance of the ExcelLabelGenerator class with a specific label template.
        /// </summary>
        /// <param name="labelTemplate">Template string with {columnName} placeholders</param>
        public ExcelLabelGenerator(string labelTemplate)
        {
            _labelTemplate = labelTemplate ?? throw new ArgumentNullException(nameof(labelTemplate));
        }

        /// <summary>
        /// Initializes a new instance of the ExcelLabelGenerator class with a default template.
        /// The default template will simply concatenate all column values.
        /// </summary>
        public ExcelLabelGenerator()
        {
            _labelTemplate = string.Empty;
        }

        /// <summary>
        /// Generates an Excel document with labels formatted according to specified layout
        /// </summary>
        /// <param name="data">DataTable containing the label data</param>
        /// <param name="outputPath">Path where the Excel file will be saved</param>
        /// <param name="columnsPerPage">Number of label columns per A4 page</param>
        /// <param name="rowsPerPage">Number of label rows per A4 page</param>
        /// <param name="labelWidthPt">Width of each label in points</param>
        /// <param name="labelHeightPt">Height of each label in points</param>
        /// <returns>True if generation was successful, otherwise false</returns>
        public bool GenerateLabels(
            DataTable data,
            string outputPath,
            int columnsPerPage,
            int rowsPerPage,
            double labelWidthPt = 200.0,
            double labelHeightPt = 120.0)
        {
            if (data == null || data.Rows.Count == 0)
                throw new ArgumentException("Data table cannot be null or empty");

            if (columnsPerPage <= 0 || rowsPerPage <= 0)
                throw new ArgumentException("Columns and rows per page must be greater than zero");

            try
            {
                // Create the Excel document
                using (SpreadsheetDocument document = SpreadsheetDocument.Create(outputPath, SpreadsheetDocumentType.Workbook))
                {
                    // Add a WorkbookPart to the document
                    WorkbookPart workbookPart = document.AddWorkbookPart();
                    workbookPart.Workbook = new Workbook();

                    // Add styles to the workbook
                    WorkbookStylesPart stylesPart = workbookPart.AddNewPart<WorkbookStylesPart>();
                    stylesPart.Stylesheet = CreateStylesheet();
                    stylesPart.Stylesheet.Save();

                    // Add a WorksheetPart to the WorkbookPart
                    WorksheetPart worksheetPart = workbookPart.AddNewPart<WorksheetPart>();
                    worksheetPart.Worksheet = new Worksheet(new SheetData());

                    // Add Sheets to the Workbook
                    Sheets sheets = workbookPart.Workbook.AppendChild(new Sheets());

                    // Add a Sheet to the Sheets collection
                    Sheet sheet = new Sheet()
                    {
                        Id = workbookPart.GetIdOfPart(worksheetPart),
                        SheetId = 1,
                        Name = "Labels"
                    };
                    sheets.Append(sheet);

                    // Get the SheetData from the WorksheetPart
                    var sheetData = worksheetPart.Worksheet.GetFirstChild<SheetData>();

                    // Configure page layout for A4
                    PageSetup pageSetup = new PageSetup
                    {
                        PaperSize = (UInt32Value)9U, // A4 paper size
                        Orientation = OrientationValues.Portrait,
                        FitToWidth = 1,
                        FitToHeight = 0 // Auto height
                    };

                    PageMargins pageMargins = new PageMargins
                    {
                        Top = 0.75D,
                        Bottom = 0.75D,
                        Left = 0.7D,
                        Right = 0.7D,
                        Header = 0.3D,
                        Footer = 0.3D
                    };

                    // Add page setup and margins
                    worksheetPart.Worksheet.AppendChild(pageSetup);
                    worksheetPart.Worksheet.AppendChild(pageMargins);

                    // Set column widths based on the fixed label width
                    Columns columns = new Columns();

                    // Convert points to Excel column width
                    // Excel column width units are based on the width of the "0" (zero) character in the default font
                    // The conversion is approximate but works for most cases
                    double excelColumnWidth = labelWidthPt / 7.0; // Approximate conversion

                    for (int i = 1; i <= columnsPerPage; i++)
                    {
                        columns.Append(new Column
                        {
                            Min = (uint)i,
                            Max = (uint)i,
                            Width = excelColumnWidth,
                            CustomWidth = true
                        });
                    }

                    worksheetPart.Worksheet.InsertBefore(columns, sheetData);

                    // Process each row of data
                    Dictionary<uint, Row> rows = new Dictionary<uint, Row>();

                    for (int dataIndex = 0; dataIndex < data.Rows.Count; dataIndex++)
                    {
                        // Calculate the label position on the page
                        int pageCol = dataIndex % columnsPerPage + 1;
                        int pageRow = dataIndex / columnsPerPage % rowsPerPage + 1;

                        // Calculate actual row in Excel - one cell per label
                        int excelRowIndex = dataIndex / columnsPerPage / rowsPerPage * rowsPerPage + pageRow;
                        uint rowIndex = (uint)excelRowIndex;

                        // Ensure row exists
                        if (!rows.ContainsKey(rowIndex))
                        {
                            Row row = new Row
                            {
                                RowIndex = rowIndex,
                                Height = labelHeightPt,
                                CustomHeight = true
                            };
                            rows.Add(rowIndex, row);
                        }

                        string columnLetter = ColumnIndexToLetters(pageCol);

                        // Create content for the label using the template with newlines
                        string labelContent = GenerateLabelContent(data.Rows[dataIndex]);

                        // Add cell with border style and content
                        Cell cell = CreateCell(columnLetter, rowIndex, labelContent, BORDER_STYLE_ID);

                        rows[rowIndex].Append(cell);
                    }

                    // Add all rows to the sheet
                    foreach (var row in rows.Values.OrderBy(r => r.RowIndex))
                    {
                        sheetData?.Append(row);
                    }

                    // Save the worksheet
                    worksheetPart.Worksheet.Save();

                    // Save the workbook
                    workbookPart.Workbook.Save();
                }

                return true;
            }
            catch (Exception ex)
            {
                Console.WriteLine($"Error generating Excel labels: {ex.Message}");
                return false;
            }
        }



        /// <summary>
        /// Gets or creates a style for a specific font size
        /// </summary>
        /// <param name="fontSize">Font size in points</param>
        /// <param name="withBorder">Whether to include a border</param>
        /// <returns>Style index</returns>
        private uint GetFontSizeStyle(int fontSize, bool withBorder)
        {
            // We'll use a simple formula to calculate the style index
            // STYLE_FONT_SIZE_START + (fontSize * 2) for no border
            // STYLE_FONT_SIZE_START + (fontSize * 2) + 1 for with border

            int styleOffset = fontSize * 2 + (withBorder ? 1 : 0);
            uint styleIndex = (uint)(STYLE_FONT_SIZE_START + styleOffset);

            // Return the style index - we'll ensure the style exists when creating the stylesheet
            return styleIndex;
        }

        /// <summary>
        /// Parses text for style markers and removes them from the text
        /// Handles nested formatting (e.g. _**text**_ for bold and underlined)
        /// </summary>
        /// <param name="text">Text to parse, will be modified to remove markers</param>
        /// <returns>TextStyle object with detected formatting</returns>
        private TextStyle ParseTextStyle(ref string text)
        {
            TextStyle style = new TextStyle();
            bool madeChanges;

            // We need to repeat the pattern matching until no more changes are made
            // This handles nested formatting like _**text**_
            do
            {
                madeChanges = false;

                // Check for bold
                if (BoldPattern.IsMatch(text))
                {
                    style.IsBold = true;
                    string newText = BoldPattern.Replace(text, "$1");
                    if (newText != text)
                    {
                        text = newText;
                        madeChanges = true;
                    }
                }

                // Check for italic
                if (ItalicPattern.IsMatch(text))
                {
                    style.IsItalic = true;
                    string newText = ItalicPattern.Replace(text, "$1");
                    if (newText != text)
                    {
                        text = newText;
                        madeChanges = true;
                    }
                }

                // Check for underline
                if (UnderlinePattern.IsMatch(text))
                {
                    style.IsUnderlined = true;
                    string newText = UnderlinePattern.Replace(text, "$1");
                    if (newText != text)
                    {
                        text = newText;
                        madeChanges = true;
                    }
                }
            } while (madeChanges);

            return style;
        }

        /// <summary>
        /// Represents text formatting style
        /// </summary>
        private class TextStyle
        {
            public bool IsBold { get; set; }
            public bool IsItalic { get; set; }
            public bool IsUnderlined { get; set; }

            public bool HasFormatting => IsBold || IsItalic || IsUnderlined;
        }

        /// <summary>
        /// The key update is to the CreateCell method and ensuring all cell formats
        /// have WrapText = true properly set. Also ensuring the alignment is properly
        /// applied to each cell format.
        /// </summary>
        private Cell CreateCell(string columnName, uint rowIndex, string text, uint? styleIndex = null)
        {
            // Create the cell
            Cell cell = new Cell
            {
                CellReference = $"{columnName}{rowIndex}",
                DataType = CellValues.InlineString,
                StyleIndex = styleIndex ?? STYLE_NORMAL
            };

            // Create the inline string that will hold our text with preserved formatting
            InlineString inlineString = new InlineString();

            // We need to handle each line of text separately while preserving the newlines
            string[] lines = text.Split(new[] { '\n' }, StringSplitOptions.None);

            for (int lineIndex = 0; lineIndex < lines.Length; lineIndex++)
            {
                string line = lines[lineIndex];

                // Check if this is not the first line, we need to add a line break
                if (lineIndex > 0)
                {
                    // For line breaks in Excel, we need to add a line feed character
                    // within the same text run, not as a separate Break element
                    Run lineBreakRun = new Run();
                    lineBreakRun.AppendChild(new Text
                    {
                        Text = "\n",
                        Space = SpaceProcessingModeValues.Preserve
                    });
                    inlineString.AppendChild(lineBreakRun);
                }

                // Process the current line
                // This can contain formatting markers like bold, italic, etc.
                string remainingText = line;

                // First check for font size formatting
                while (FontSizePattern.IsMatch(remainingText))
                {
                    Match match = FontSizePattern.Match(remainingText);

                    // Add text before the font size marker if any
                    if (match.Index > 0)
                    {
                        string beforeText = remainingText.Substring(0, match.Index);
                        TextStyle beforeStyle = ParseTextStyle(ref beforeText);

                        AddFormattedRun(inlineString, beforeText, beforeStyle);
                    }

                    // Handle the font-sized text
                    int fontSize;
                    if (int.TryParse(match.Groups[1].Value, out fontSize))
                    {
                        string fontSizeText = match.Groups[2].Value;
                        TextStyle fontSizeStyle = ParseTextStyle(ref fontSizeText);

                        // Create a run with font size and any other formatting
                        Run fontSizeRun = new Run();
                        RunProperties fontSizeProps = new RunProperties();
                        fontSizeProps.AppendChild(new FontSize { Val = fontSize });

                        if (fontSizeStyle.IsBold)
                            fontSizeProps.AppendChild(new Bold());
                        if (fontSizeStyle.IsItalic)
                            fontSizeProps.AppendChild(new Italic());
                        if (fontSizeStyle.IsUnderlined)
                            fontSizeProps.AppendChild(new Underline { Val = UnderlineValues.Single });

                        fontSizeRun.AppendChild(fontSizeProps);
                        fontSizeRun.AppendChild(new Text
                        {
                            Text = fontSizeText,
                            Space = SpaceProcessingModeValues.Preserve
                        });

                        inlineString.AppendChild(fontSizeRun);
                    }

                    // Move past the processed part
                    remainingText = remainingText.Substring(match.Index + match.Length);
                }

                // Process any remaining text in this line if any
                if (!string.IsNullOrEmpty(remainingText))
                {
                    TextStyle remainingStyle = ParseTextStyle(ref remainingText);
                    AddFormattedRun(inlineString, remainingText, remainingStyle);
                }
            }

            cell.AppendChild(inlineString);
            return cell;
        }

        /// <summary>
        /// Helper method to add a run with the specified formatting
        /// </summary>
        private void AddFormattedRun(InlineString inlineString, string text, TextStyle style)
        {
            if (string.IsNullOrEmpty(text))
                return;

            Run run = new Run();

            if (style.HasFormatting)
            {
                RunProperties props = new RunProperties();

                if (style.IsBold)
                    props.AppendChild(new Bold());
                if (style.IsItalic)
                    props.AppendChild(new Italic());
                if (style.IsUnderlined)
                    props.AppendChild(new Underline { Val = UnderlineValues.Single });

                run.AppendChild(props);
            }

            run.AppendChild(new Text
            {
                Text = text,
                Space = SpaceProcessingModeValues.Preserve
            });

            inlineString.AppendChild(run);
        }

        /// <summary>
        /// This is a key modification to ensure ALL cell formats have WrapText=true
        /// </summary>
        private Stylesheet CreateStylesheet()
        {
            Stylesheet stylesheet = new Stylesheet();

            // Create Fonts
            Fonts fonts = new Fonts();

            // Font 0: Normal (11pt)
            Font normalFont = new Font();
            normalFont.AppendChild(new FontSize { Val = 11D });
            fonts.Append(normalFont);

            // Font 1: Bold (11pt)
            Font boldFont = new Font();
            boldFont.AppendChild(new FontSize { Val = 11D });
            boldFont.Append(new Bold());
            fonts.Append(boldFont);

            // Font 2: Italic (11pt)
            Font italicFont = new Font();
            italicFont.AppendChild(new FontSize { Val = 11D });
            italicFont.Append(new Italic());
            fonts.Append(italicFont);

            // Font 3: Underline (11pt)
            Font underlineFont = new Font();
            underlineFont.AppendChild(new FontSize { Val = 11D });
            underlineFont.Append(new Underline() { Val = UnderlineValues.Single });
            fonts.Append(underlineFont);

            // Add font sizes from 8pt to 72pt
            for (int size = 8; size <= 72; size++)
            {
                // Regular font with custom size
                Font sizedFont = new Font();
                sizedFont.AppendChild(new FontSize { Val = (double)size });
                fonts.Append(sizedFont);
            }

            fonts.Count = (uint)fonts.ChildElements.Count;

            // Create Fills
            Fills fills = new Fills();
            Fill fill1 = new Fill();
            fill1.PatternFill = new PatternFill { PatternType = PatternValues.None };
            Fill fill2 = new Fill();
            fill2.PatternFill = new PatternFill { PatternType = PatternValues.Gray125 };
            fills.Append(fill1);
            fills.Append(fill2);
            fills.Count = (uint)fills.ChildElements.Count;

            // Create Borders
            Borders borders = new Borders();

            // Border 0: No border
            Border noBorder = new Border();
            borders.Append(noBorder);

            // Border 1: All sides
            Border allBorder = new Border();

            // Left Border
            LeftBorder leftBorder = new LeftBorder { Style = BorderStyleValues.Thin };
            leftBorder.Append(new Color { Auto = true });
            allBorder.Append(leftBorder);

            // Right Border
            RightBorder rightBorder = new RightBorder { Style = BorderStyleValues.Thin };
            rightBorder.Append(new Color { Auto = true });
            allBorder.Append(rightBorder);

            // Top Border
            TopBorder topBorder = new TopBorder { Style = BorderStyleValues.Thin };
            topBorder.Append(new Color { Auto = true });
            allBorder.Append(topBorder);

            // Bottom Border
            BottomBorder bottomBorder = new BottomBorder { Style = BorderStyleValues.Thin };
            bottomBorder.Append(new Color { Auto = true });
            allBorder.Append(bottomBorder);

            // Diagonal Border (blank)
            allBorder.Append(new DiagonalBorder());

            borders.Append(allBorder);
            borders.Count = (uint)borders.ChildElements.Count;

            // Create CellFormats
            CellFormats cellFormats = new CellFormats();

            // *** CRITICALLY IMPORTANT: All cell formats must have WrapText=true ***

            // Format 0: Default / Normal
            CellFormat format0 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyAlignment = true
            };
            format0.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format0);

            // Format 1: Border only
            CellFormat format1 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 0,
                FillId = 0,
                BorderId = 1,
                FormatId = 0,
                ApplyBorder = true,
                ApplyAlignment = true
            };
            format1.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format1);

            // Format 2: Bold
            CellFormat format2 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 1,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyFont = true,
                ApplyAlignment = true
            };
            format2.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format2);

            // Format 3: Italic
            CellFormat format3 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 2,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyFont = true,
                ApplyAlignment = true
            };
            format3.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format3);

            // Format 4: Underline
            CellFormat format4 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 3,
                FillId = 0,
                BorderId = 0,
                FormatId = 0,
                ApplyFont = true,
                ApplyAlignment = true
            };
            format4.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format4);

            // Format 5: Bold + Border
            CellFormat format5 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 1,
                FillId = 0,
                BorderId = 1,
                FormatId = 0,
                ApplyFont = true,
                ApplyBorder = true,
                ApplyAlignment = true
            };
            format5.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format5);

            // Format 6: Italic + Border
            CellFormat format6 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 2,
                FillId = 0,
                BorderId = 1,
                FormatId = 0,
                ApplyFont = true,
                ApplyBorder = true,
                ApplyAlignment = true
            };
            format6.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format6);

            // Format 7: Underline + Border
            CellFormat format7 = new CellFormat
            {
                NumberFormatId = 0,
                FontId = 3,
                FillId = 0,
                BorderId = 1,
                FormatId = 0,
                ApplyFont = true,
                ApplyBorder = true,
                ApplyAlignment = true
            };
            format7.AppendChild(new Alignment
            {
                Vertical = VerticalAlignmentValues.Center,
                Horizontal = HorizontalAlignmentValues.Left,
                WrapText = true,
                Indent = 1,    // Left indent for text margin
                JustifyLastLine = false
            });
            cellFormats.Append(format7);

            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            // Add additional cell formats for font sizes 8pt to 72pt
            // Both with and without borders
            for (int size = 8; size <= 72; size++)
            {
                int fontIndex = 4 + (size - 8); // Font indexes start after our 4 basic fonts

                // Create style without border
                CellFormat formatSize = new CellFormat
                {
                    NumberFormatId = 0,
                    FontId = (uint)fontIndex,
                    FillId = 0,
                    BorderId = 0,
                    FormatId = 0,
                    ApplyFont = true,
                    ApplyAlignment = true
                };
                formatSize.AppendChild(new Alignment
                {
                    Vertical = VerticalAlignmentValues.Center,
                    Horizontal = HorizontalAlignmentValues.Left,
                    WrapText = true,
                    Indent = 1,
                    JustifyLastLine = false
                });
                cellFormats.Append(formatSize);

                // Create style with border
                CellFormat formatSizeBorder = new CellFormat
                {
                    NumberFormatId = 0,
                    FontId = (uint)fontIndex,
                    FillId = 0,
                    BorderId = 1,
                    FormatId = 0,
                    ApplyFont = true,
                    ApplyBorder = true,
                    ApplyAlignment = true
                };
                formatSizeBorder.AppendChild(new Alignment
                {
                    Vertical = VerticalAlignmentValues.Center,
                    Horizontal = HorizontalAlignmentValues.Left,
                    WrapText = true,
                    Indent = 1,
                    JustifyLastLine = false
                });
                cellFormats.Append(formatSizeBorder);
            }

            cellFormats.Count = (uint)cellFormats.ChildElements.Count;

            stylesheet.Append(fonts);
            stylesheet.Append(fills);
            stylesheet.Append(borders);
            stylesheet.Append(cellFormats);

            return stylesheet;
        }

        /// <summary>
        /// Generates the content for a label based on the template and data row
        /// </summary>
        /// <param name="dataRow">The DataRow containing the label data</param>
        /// <returns>String representing the label content with newlines</returns>
        private string GenerateLabelContent(DataRow dataRow)
        {
            string result;

            if (string.IsNullOrEmpty(_labelTemplate))
            {
                // Default behavior: use column name and value for each column
                List<string> lines = new List<string>();
                foreach (DataColumn column in dataRow.Table.Columns)
                {
                    object value = dataRow[column.ColumnName];
                    string lineContent = $"{column.ColumnName}: {(value != DBNull.Value ? value?.ToString() : string.Empty)}";
                    lines.Add(lineContent);
                }

                result = string.Join("\n", lines);
            }
            else
            {
                // Use the specified template
                result = _labelTemplate;

                // Replace all column placeholders with actual values
                result = TemplatePattern.Replace(result, match =>
                {
                    string columnName = match.Groups[1].Value;

                    if (dataRow.Table.Columns.Contains(columnName))
                    {
                        object value = dataRow[columnName];
                        return value != DBNull.Value ? value?.ToString() ?? string.Empty : string.Empty;
                    }

                    return match.Value; // Keep the placeholder if column not found
                });
            }

            return result;
        }

        /// <summary>
        /// Converts a column index to Excel column letters (e.g., 1 = A, 27 = AA)
        /// </summary>
        private string ColumnIndexToLetters(int columnIndex)
        {
            string columnLetters = string.Empty;

            while (columnIndex > 0)
            {
                int remainder = (columnIndex - 1) % 26;
                columnLetters = (char)('A' + remainder) + columnLetters;
                columnIndex = (columnIndex - 1) / 26;
            }

            return columnLetters;
        }
    }
}