ExcelHelper.cs

// =============================================================================
// GPAL - Generally Positive Automation Library
// Copyright © 2026 Software Decisions, Inc. All rights reserved.
//
// This file is part of GPAL.
// Licensed under the Business Source License 1.1
//
// Primary development, architecture, and vision by Michael B. Vederman,
// CEO of Software Decisions, Inc., Texas.
//
// Internal development maintained privately.
// Public releases appear on GitHub: https://github.com/SoftwareDecisionsInc/GPAL.
//
// See LICENSE for full terms, including Additional Use Grant.
// =============================================================================
 
using System;
using System.Collections.Generic;
using System.Linq;
using ClosedXML.Excel;
 
namespace GenerallyPositive
{
    internal class ExcelHelper
    {
        // Existing methods unchanged
        internal static void TokenizeExcelFile(GPALFile gPALFile)
        {
            foreach (string filename in gPALFile.Filenames)
            {
                try
                {
                    IGPALGrid<string> data = GPAL.Grid.ToGPALObject();
 
                    using (var workbook = new XLWorkbook(filename))
                    {
                        var worksheet = workbook.Worksheet(1);
 
                        foreach (var row in worksheet.RowsUsed())
                        {
                            var rowData = new List<string>();
                            foreach (var cell in row.CellsUsed())
                            {
                                rowData.Add(cell.GetString());
                            }
                            data.AddRow(rowData);
                        }
                        ((IGPALFileInternal)gPALFile).FileGrid.Add(data);
                    }
                }
                catch (Exception ex)
                {
                    GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Unable to tokenize file [{filename}]. Continuing.", gPALFile, Enums.GPALObjectType.GPALFile, ex);
                }
            }
        }
 
        internal static IGPALGrid<string> TokenizeExcelFile(string filename)
        {
            IGPALGrid<string> data = GPAL.Grid.ToGPALObject();
 
            try
            {
                using (var workbook = new XLWorkbook(filename))
                {
                    foreach (IXLWorksheet worksheet in workbook.Worksheets)
                    {
                        var lastRow = worksheet.LastRowUsed()?.RowNumber() ?? 0;
                        var lastCol = worksheet.LastColumnUsed()?.ColumnNumber() ?? 0;
 
                        for (int r = 1; r <= lastRow; r++)
                        {
                            var rowData = new List<string>();
                            var row = worksheet.Row(r);
 
                            for (int c = 1; c <= lastCol; c++)
                            {
                                rowData.Add(row.Cell(c).GetString());
                            }
 
                            data.AddRow(rowData);
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION,
                    $"Unable to tokenize file [{filename}]. Continuing.", null,
                    Enums.GPALObjectType.None, ex);
            }
 
            return data;
        }
 
        internal static string SaveToExcelFile(string fileName, IGPALGrid<string> data)
        {
            using (var workbook = new XLWorkbook())
            {
                var worksheet = workbook.Worksheets.Add("Sheet1");
 
                for (int rowIndex = 0; rowIndex < data.Count(); rowIndex++)
                {
                    var row = data[rowIndex];
                    for (int colIndex = 0; colIndex < row.Count; colIndex++)
                    {
                        try
                        {
                            worksheet.Cell(rowIndex + 1, colIndex + 1).Value = row[colIndex];
                        }
                        catch (Exception ex)
                        {
                            GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Error saving to Excel [{fileName}]", data, Enums.GPALObjectType.Other, ex);
                        }
                    }
                }
 
                workbook.SaveAs(fileName);
            }
 
            return fileName;
        }
        internal static void CopyWorksheet(IXLWorksheet src, IXLWorksheet dest)
        {
            try
            {
                // Clear the destination worksheet to avoid overlapping data
                dest.Clear();
 
                // Get all used cells from the source worksheet
                foreach (var row in src.RowsUsed())
                {
                    int rowIndex = row.RowNumber();
                    foreach (var cell in row.CellsUsed())
                    {
                        int colIndex = cell.Address.ColumnNumber;
                        // Copy the cell value to the same position in the destination worksheet
                        dest.Cell(rowIndex, colIndex).Value = cell.GetString();
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to copy worksheet", null, Enums.GPALObjectType.None, ex);
            }
        }
        internal static void WriteRange(IXLWorksheet worksheet, string range, IGPALGrid<string> data)
        {
            try
            {
                var rangeObj = worksheet.Range(range);
                var firstCell = rangeObj.FirstCell();
 
                int startRow = firstCell.Address.RowNumber;
                int startColumn = firstCell.Address.ColumnNumber;
 
                for (int rowIndex = 0; rowIndex < data.Count(); rowIndex++)
                {
                    var row = data[rowIndex];
                    for (int colIndex = 0; colIndex < row.Count; colIndex++)
                    {
                        worksheet.Cell(startRow + rowIndex, startColumn + colIndex).Value = row[colIndex];
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to write range [{range}] in worksheet", null, Enums.GPALObjectType.None, ex);
            }
        }
 
        // New concrete class for comparison results
        public class CellDifference
        {
            public string CellAddress { get; set; }
            public string OldValue { get; set; }
            public string NewValue { get; set; }
            public string SourceRange { get; set; } // New: Tracks the source range
            public string TargetRange { get; set; } // New: Tracks the target range
        }
        // New methods using XLWorkbook and concrete objects
        internal static List<CellDifference> CompareExcelFiles(XLWorkbook workbook1, XLWorkbook workbook2)
        {
            var differences = new List<CellDifference>();
 
            try
            {
                var worksheet1 = workbook1.Worksheet(1);
                var worksheet2 = workbook2.Worksheet(1);
                var range1 = worksheet1.RangeUsed();
                var range2 = worksheet2.RangeUsed();
                int maxRows = Math.Max(range1?.RowCount() ?? 0, range2?.RowCount() ?? 0);
                int maxCols = Math.Max(range1?.ColumnCount() ?? 0, range2?.ColumnCount() ?? 0);
 
                for (int row = 1; row <= maxRows; row++)
                {
                    for (int col = 1; col <= maxCols; col++)
                    {
                        string value1 = worksheet1.Cell(row, col).GetString();
                        string value2 = worksheet2.Cell(row, col).GetString();
                        if (value1 != value2)
                        {
                            differences.Add(new CellDifference
                            {
                                CellAddress = $"{worksheet1.Column(col).ColumnLetter()}{row}",
                                OldValue = value1,
                                NewValue = value2
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to compare workbooks", null, Enums.GPALObjectType.None, ex);
            }
 
            return differences;
        }
        internal static List<CellDifference> CompareRanges(IGPALGrid<string> data1, IGPALGrid<string> data2)
        {
            var differences = new List<CellDifference>();
 
            try
            {
                int maxRows = Math.Max(data1.Count(), data2.Count());
                int maxCols = Math.Max(data1.Any() ? data1[0].Count : 0, data2.Any() ? data2[0].Count : 0);
 
                for (int i = 0; i < maxRows; i++)
                {
                    for (int j = 0; j < maxCols; j++)
                    {
                        string value1 = i < data1.Count() && j < data1[i].Count ? data1[i][j] : null;
                        string value2 = i < data2.Count() && j < data2[i].Count ? data2[i][j] : null;
 
                        if (value1 != value2)
                        {
                            string cellAddress = $"{(char)('A' + j)}{i + 1}";
                            differences.Add(new CellDifference
                            {
                                CellAddress = cellAddress,
                                OldValue = value1 ?? "",
                                NewValue = value2 ?? ""
                            });
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to compare ranges", null, Enums.GPALObjectType.None, ex);
            }
 
            return differences;
        }
        internal static string GetCellValue(XLWorkbook workbook, string cellAddress)
        {
            try
            {
                return workbook.Worksheet(1).Cell(cellAddress).GetString();
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get cell value [{cellAddress}]", null, Enums.GPALObjectType.None, ex);
                return string.Empty;
            }
        }
 
        internal static IGPALGrid<string> ReadRange(IXLWorksheet worksheet, string range)
        {
            IGPALGrid<string> data = GPAL.Grid.ToGPALObject();
 
            try
            {
                var xlRange = worksheet.Range(range);
 
                foreach (var row in xlRange.RowsUsed())
                {
                    var rowData = new List<string>();
                    foreach (var cell in row.CellsUsed())
                    {
                        rowData.Add(cell.GetString());
                    }
                    data.AddRow(rowData);
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to extract range [{range}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return data;
        }
 
        internal static double GetColumnSum(XLWorkbook workbook, string column)
        {
            double sum = 0;
 
            try
            {
                var col = workbook.Worksheet(1).Column(column);
                foreach (var cell in col.CellsUsed())
                {
                    if (double.TryParse(cell.GetString(), out double value))
                    {
                        sum += value;
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to calculate sum for column [{column}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return sum;
        }
 
        internal static List<string> GetColumnValues(IXLWorksheet worksheet, string column)
        {
            var values = new List<string>();
 
            try
            {
                var col = worksheet.Column(column);
                foreach (var cell in col.CellsUsed())
                {
                    values.Add(cell.GetString());
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get values for column [{column}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return values;
        }
 
        internal static List<string> GetRowValues(IXLWorksheet worksheet, int rowNumber)
        {
            var values = new List<string>();
 
            try
            {
                var row = worksheet.Row(rowNumber);
                foreach (var cell in row.CellsUsed())
                {
                    values.Add(cell.GetString());
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to get values for row [{rowNumber}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return values;
        }
 
        internal static void SearchAndReplaceByValue(IXLWorksheet worksheet, string range, string searchValue, string replaceValue)
        {
            try
            {
                var xlRange = worksheet.Range(range);
                foreach (var cell in xlRange.CellsUsed())
                {
                    if (cell.GetString() == searchValue)
                    {
                        cell.Value = replaceValue;
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to search and replace in range [{range}]", null, Enums.GPALObjectType.None, ex);
            }
        }
 
        internal static int SearchHeaderIndexByValue(XLWorkbook workbook, string range, string headerValue)
        {
            try
            {
                var worksheet = workbook.Worksheet(1);
                var xlRange = worksheet.Range(range);
                foreach (var cell in xlRange.CellsUsed())
                {
                    if (cell.GetString() == headerValue)
                    {
                        return cell.Address.ColumnNumber;
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to search header in range [{range}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return -1;
        }
 
        internal static string SearchValue(XLWorkbook workbook, string range, string searchValue)
        {
            try
            {
                var worksheet = workbook.Worksheet(1);
                var xlRange = worksheet.Range(range);
                foreach (var cell in xlRange.CellsUsed())
                {
                    if (cell.GetString() == searchValue)
                    {
                        return cell.Address.ToString();
                    }
                }
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to search value in range [{range}]", null, Enums.GPALObjectType.None, ex);
            }
 
            return null;
        }
 
        internal static void SetCellValue(XLWorkbook workbook, string cellAddress, string value)
        {
            try
            {
                workbook.Worksheet(1).Cell(cellAddress).Value = value;
            }
            catch (Exception ex)
            {
                GPAL.PublishSimpleEvent(Enums.GPALEventType.EXCEPTION, $"Failed to set cell value [{cellAddress}]", null, Enums.GPALObjectType.None, ex);
            }
        }

        public static void WriteXlsxFromTokenizedData(
            string outputPath,
            IGPALGrid<string> grid,
            List<int> rowsPerSheet,
            IGPALGrid<string> columnNamesPerSheet,
            List<string> sheetNames,
            bool writeHeaders = true)
        {
            if (grid == null) throw new ArgumentNullException(nameof(grid));
            if (rowsPerSheet == null) throw new ArgumentNullException(nameof(rowsPerSheet));
            if (columnNamesPerSheet == null) throw new ArgumentNullException(nameof(columnNamesPerSheet));
 
            using var workbook = new XLWorkbook();
 
            int currentRowInGrid = 0;
 
            for (int sheetIdx = 0; sheetIdx < rowsPerSheet.Count; sheetIdx++)
            {
                int totalRowsThisSheet = rowsPerSheet[sheetIdx];
                if (totalRowsThisSheet == 0) continue;
 
                var columnNames = columnNamesPerSheet[sheetIdx];
 
                string sheetName = sheetNames != null && sheetIdx < sheetNames.Count
                    ? sheetNames[sheetIdx]
                    : $"Sheet{sheetIdx + 1}";
 
                var worksheet = workbook.Worksheets.Add(sheetName);
 
                int destRow = 1;
 
                // Header
                if (writeHeaders)
                {
                    for (int col = 0; col < columnNames.Count; col++)
                        worksheet.Cell(1, col + 1).Value = columnNames[col];
 
                    worksheet.Row(1).Style.Font.Bold = true;
                    destRow = 2;
                }
 
                int startOffset = writeHeaders ? 1 : 0;
                int dataRowsToWrite = totalRowsThisSheet - startOffset;
 
                // === THIS IS THE ONLY IMPORTANT CHANGE ===
                for (int i = 0; i < dataRowsToWrite; i++)
                {
                    var sourceRow = grid[currentRowInGrid + startOffset + i];
 
                    // Always write ALL columns defined by the header, even if sourceRow has fewer items
                    for (int col = 0; col < columnNames.Count; col++)
                    {
                        string value = col < sourceRow.Count ? sourceRow[col] ?? string.Empty : string.Empty;
                        worksheet.Cell(destRow, col + 1).Value = value;
                    }
 
                    destRow++;
                }
 
                // Keep empty columns visible
                if (columnNames.Count > 0)
                    worksheet.Columns(1, columnNames.Count).AdjustToContents();
 
                currentRowInGrid += totalRowsThisSheet;
            }
 
            workbook.SaveAs(outputPath);
        }
    }
}