jdescopingtool/NEW/tests/JdeScoping.ExcelIO.Tests/Fixtures/ExcelTestHelpers.cs

using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.XSSF.UserModel;

namespace JdeScoping.ExcelIO.Tests.Fixtures;

public static class ExcelTestHelpers
{
    private static readonly DataFormatter Formatter = new();

    public static List<string> GetHeadersFromSheet(ISheet sheet)
    {
        var headers = new List<string>();
        var headerRow = sheet.GetRow(0);
        if (headerRow == null)
        {
            return headers;
        }

        var col = 0;
        while (true)
        {
            var cell = headerRow.GetCell(col);
            var text = cell == null ? string.Empty : Formatter.FormatCellValue(cell);
            if (string.IsNullOrEmpty(text))
            {
                break;
            }

            headers.Add(text);
            col++;
        }

        return headers;
    }

    public static string GetCellText(ISheet sheet, int row1Based, int col1Based)
    {
        var cell = GetCell(sheet, row1Based, col1Based);
        return cell == null ? string.Empty : Formatter.FormatCellValue(cell);
    }

    public static double GetCellNumber(ISheet sheet, int row1Based, int col1Based)
    {
        var cell = GetCell(sheet, row1Based, col1Based);
        if (cell == null)
        {
            return 0;
        }

        if (cell.CellType == CellType.Numeric)
        {
            return cell.NumericCellValue;
        }

        var text = Formatter.FormatCellValue(cell);
        return double.TryParse(text, out var value) ? value : 0;
    }

    public static ICell? GetCell(ISheet sheet, int row1Based, int col1Based)
    {
        return sheet.GetRow(row1Based - 1)?.GetCell(col1Based - 1);
    }

    public static XSSFSheet GetXssfSheet(IWorkbook workbook, string sheetName)
    {
        return (XSSFSheet)workbook.GetSheet(sheetName)!;
    }

    public static XSSFWorkbook OpenWorkbook(byte[] bytes)
    {
        return new XSSFWorkbook(new MemoryStream(bytes));
    }

    public static double GetColumnWidthChars(ISheet sheet, int col1Based)
    {
        return sheet.GetColumnWidth(col1Based - 1) / 256d;
    }

    public static bool IsSheetProtected(ISheet sheet)
    {
        return sheet is XSSFSheet xssf && xssf.IsSheetLocked;
    }

    public static byte[]? GetFillForegroundRgb(ICell cell)
    {
        if (cell.CellStyle is XSSFCellStyle xssfStyle)
        {
            return xssfStyle.FillForegroundXSSFColor?.RGB;
        }

        return null;
    }

    public static bool IsMerged(ISheet sheet, int firstRow1Based, int lastRow1Based, int firstCol1Based, int lastCol1Based)
    {
        for (var i = 0; i < sheet.NumMergedRegions; i++)
        {
            var region = sheet.GetMergedRegion(i);
            if (region.FirstRow == firstRow1Based - 1 &&
                region.LastRow == lastRow1Based - 1 &&
                region.FirstColumn == firstCol1Based - 1 &&
                region.LastColumn == lastCol1Based - 1)
            {
                return true;
            }
        }

        return false;
    }

    public static XSSFTable GetFirstTable(ISheet sheet)
    {
        return ((XSSFSheet)sheet).GetTables().First();
    }

    public static XSSFTable GetTableByName(ISheet sheet, string name)
    {
        return ((XSSFSheet)sheet).GetTables().First(t => t.Name == name || t.DisplayName == name);
    }

    public static int GetTableRowCount(XSSFTable table)
    {
        // header row + data rows
        return table.RowCount;
    }

    public static bool TableExists(ISheet sheet, string tableName)
    {
        return ((XSSFSheet)sheet).GetTables().Any(t => t.Name == tableName || t.DisplayName == tableName);
    }

    public static bool RegionIntersectsCell(CellRangeAddress region, int row1Based, int col1Based)
    {
        var row = row1Based - 1;
        var col = col1Based - 1;
        return region.IsInRange(row, col);
    }
}