DataUtils.cs

/*
 * NanoXLSX is a small .NET library to generate and read XLSX (Microsoft Excel 2007 or newer) files in an easy and native way
 * Copyright Raphael Stoeckli © 2025
 * This library is licensed under the MIT License.
 * You find a copy of the license in project folder or on: http://opensource.org/licenses/MIT
 */
 
using System;
using System.Collections.Generic;
using System.Globalization;
using System.Linq;
using FormatException = NanoXLSX.Exceptions.FormatException;
 
namespace NanoXLSX.Utils
{
    public static class DataUtils
 
    {
        #region constants
#pragma warning disable CA1805 // Suppress: Do not initialize unnecessarily (to make clear that this is the minimum value)
        public static readonly double MinOADateValue = 0d;
#pragma warning restore CA1805
        public static readonly double MaxOADateValue = 2958465.999988426d;
        public static readonly DateTime FirstAllowedExcelDate = new DateTime(1900, 1, 1, 0, 0, 0, DateTimeKind.Unspecified);
        public static readonly DateTime LastAllowedExcelDate = new DateTime(9999, 12, 31, 23, 59, 59, DateTimeKind.Unspecified);

        public static readonly DateTime FirstValidExcelDate = new DateTime(1900, 3, 1, 0, 0, 0, DateTimeKind.Unspecified);

        public static readonly CultureInfo InvariantCulture = CultureInfo.InvariantCulture;
 
        private const float COLUMN_WIDTH_ROUNDING_MODIFIER = 256f;
        private const float SPLIT_WIDTH_MULTIPLIER = 12f;
        private const float SPLIT_WIDTH_OFFSET = 0.5f;
        private const float SPLIT_WIDTH_POINT_MULTIPLIER = 3f / 4f;
        private const float SPLIT_POINT_DIVIDER = 20f;
        private const float SPLIT_WIDTH_POINT_OFFSET = 390f;
        private const float SPLIT_HEIGHT_POINT_OFFSET = 300f;
        private const float ROW_HEIGHT_POINT_MULTIPLIER = 1f / 3f + 1f;
        private static readonly DateTime ROOT_DATE = new DateTime(1899, 12, 30, 0, 0, 0, DateTimeKind.Unspecified);
        private static readonly double ROOT_MILLIS = (double)new DateTime(1899, 12, 30, 0, 0, 0, DateTimeKind.Unspecified).Ticks / TimeSpan.TicksPerMillisecond;
 
        #endregion
 
        #region enums
        public enum RangeMergeStrategy
        {
            NoMerge,
            MergeColumns,
            MergeRows
        }
        #endregion

        public static string GetOADateTimeString(DateTime date)
        {
            double d = GetOADateTime(date);
            return d.ToString("G", InvariantCulture);
        }

        public static double GetOADateTime(DateTime date, bool skipCheck = false)
        {
            if (!skipCheck && (date < FirstAllowedExcelDate || date > LastAllowedExcelDate))
            {
                throw new FormatException("The date is not in a valid range for Excel. Dates before 1900-01-01 or after 9999-12-31 are not allowed.");
            }
            DateTime dateValue = date;
            if (date < FirstValidExcelDate)
            {
                dateValue = date.AddDays(-1); // Fix of the leap-year-1900-error
            }
            double currentMillis = (double)dateValue.Ticks / TimeSpan.TicksPerMillisecond;
            return ((dateValue.Second + (dateValue.Minute * 60) + (dateValue.Hour * 3600)) / 86400d) + Math.Floor((currentMillis - ROOT_MILLIS) / 86400000d);
        }

        public static string GetOATimeString(TimeSpan time)
        {
            double d = GetOATime(time);
            return d.ToString("G", InvariantCulture);
        }

        public static double GetOATime(TimeSpan time)
        {
            int seconds = time.Seconds + time.Minutes * 60 + time.Hours * 3600;
            return time.Days + (double)seconds / 86400d;
        }

        public static DateTime GetDateFromOA(double oaDate)
        {
            if (oaDate < 60)
            {
                oaDate++;
            }
            return ROOT_DATE.AddSeconds(oaDate * 86400d);
        }

        public static float GetInternalColumnWidth(float columnWidth, float maxDigitWidth = 7f, float textPadding = 5f)
        {
            if (columnWidth < Worksheet.MinColumnWidth || columnWidth > Worksheet.MaxColumnWidth)
            {
                throw new FormatException("The column width " + columnWidth + " is not valid. The valid range is between " + Worksheet.MinColumnWidth + " and " + Worksheet.MaxColumnWidth);
            }
            if (columnWidth <= 0f || maxDigitWidth <= 0f)
            {
                return 0f;
            }
            else if (columnWidth <= 1f)
            {
                return (float)Math.Floor((columnWidth * (maxDigitWidth + textPadding)) / maxDigitWidth * COLUMN_WIDTH_ROUNDING_MODIFIER) / COLUMN_WIDTH_ROUNDING_MODIFIER;
            }
            else
            {
                return (float)Math.Floor((columnWidth * maxDigitWidth + textPadding) / maxDigitWidth * COLUMN_WIDTH_ROUNDING_MODIFIER) / COLUMN_WIDTH_ROUNDING_MODIFIER;
            }
        }

        public static float GetInternalRowHeight(float rowHeight)
        {
            if (rowHeight < Worksheet.MinRowHeight || rowHeight > Worksheet.MaxRowHeight)
            {
                throw new FormatException("The row height " + rowHeight + " is not valid. The valid range is between " + Worksheet.MinRowHeight + " and " + Worksheet.MaxRowHeight);
            }
            if (rowHeight == 0f)
            {
                return 0f;
            }
            double heightInPixel = Math.Round(rowHeight * ROW_HEIGHT_POINT_MULTIPLIER);
            return (float)heightInPixel / ROW_HEIGHT_POINT_MULTIPLIER;
        }

        public static float GetInternalPaneSplitWidth(float width, float maxDigitWidth = 7f, float textPadding = 5f)
        {
            float pixels;
            // TODO: Check the <1 part again. Leads always to 390
            if (width <= 1f)
            {
                width = 0;
                pixels = (float)Math.Floor(width / SPLIT_WIDTH_MULTIPLIER + SPLIT_WIDTH_OFFSET);
            }
            else
            {
                pixels = (float)Math.Floor(width * maxDigitWidth + SPLIT_WIDTH_OFFSET) + textPadding;
            }
            float points = pixels * SPLIT_WIDTH_POINT_MULTIPLIER;
            return points * SPLIT_POINT_DIVIDER + SPLIT_WIDTH_POINT_OFFSET;
        }

        public static float GetInternalPaneSplitHeight(float height)
        {
            if (height < 0)
            {
                height = 0f;
            }
            return (float)Math.Floor(SPLIT_POINT_DIVIDER * height + SPLIT_HEIGHT_POINT_OFFSET);
        }

        public static float GetPaneSplitHeight(float internalHeight)
        {
            if (internalHeight < 300f)
            {
                return 0;
            }
            else
            {
                return (internalHeight - SPLIT_HEIGHT_POINT_OFFSET) / SPLIT_POINT_DIVIDER;
            }
        }

        public static float GetPaneSplitWidth(float internalWidth, float maxDigitWidth = 7f, float textPadding = 5f)
        {
            float points = (internalWidth - SPLIT_WIDTH_POINT_OFFSET) / SPLIT_POINT_DIVIDER;
            if (points < 0.001f)
            {
                return 0;
            }
            else
            {
                float width = points / SPLIT_WIDTH_POINT_MULTIPLIER;
                return (width - textPadding - SPLIT_WIDTH_OFFSET) / maxDigitWidth;
            }
        }

        public static IReadOnlyList<Range> MergeRange(List<Range> givenRanges, Range newRange, RangeMergeStrategy strategy = RangeMergeStrategy.MergeColumns)
        {
            List<Range> result = new List<Range>();
            List<Range> mergedCandidates = new List<Range> { newRange };
            // Step 1: Find intersecting ranges and remove them from existingRanges
            foreach (Range range in givenRanges)
            {
                if (IsMergeCandidate(newRange, range, strategy))
                {
                    mergedCandidates.Add(range);
                }
                else
                {
                    result.Add(range);
                }
            }
            // Step 2: Slice intersecting/adjacent ranges into uniform rectangular pieces.
            List<Range> slicedRanges = SliceRanges(mergedCandidates);
            // Step 3: Merge adjacent rectangles where possible.
            if (strategy == RangeMergeStrategy.MergeColumns)
            {
                result.AddRange(MergeAdjacentRanges(slicedRanges, RangeMergeStrategy.MergeColumns));
                result = MergeAdjacentRanges(result, RangeMergeStrategy.MergeRows);
            }
            else if (strategy == RangeMergeStrategy.MergeRows)
            {
                result.AddRange(MergeAdjacentRanges(slicedRanges, RangeMergeStrategy.MergeRows));
                result = MergeAdjacentRanges(result, RangeMergeStrategy.MergeColumns);
            }
            else
            {
                result.AddRange(slicedRanges);
            }
            return result;
        }

        private static bool IsMergeCandidate(Range a, Range b, RangeMergeStrategy strategy)
        {
            // First, if they overlap, they are candidates.
            if (a.Overlaps(b))
            {
                return true;
            }
 
            // Otherwise, check for adjacency according to the strategy.
            if (strategy == RangeMergeStrategy.MergeColumns)
            {
                // Vertical merging: require same columns.
                if (a.StartAddress.Column == b.StartAddress.Column &&
                    a.EndAddress.Column == b.EndAddress.Column &&
                    (a.EndAddress.Row + 1 == b.StartAddress.Row || b.EndAddress.Row + 1 == a.StartAddress.Row))
                {
                    return true;
                }
            }
            else if (strategy == RangeMergeStrategy.MergeRows)
            {
                // Horizontal merging: require same rows.
                if (a.StartAddress.Row == b.StartAddress.Row &&
                    a.EndAddress.Row == b.EndAddress.Row &&
                    (a.EndAddress.Column + 1 == b.StartAddress.Column || b.EndAddress.Column + 1 == a.StartAddress.Column))
                {
                    return true;
                }
            }
            return false;
        }

        public static IReadOnlyList<Range> SubtractRange(List<Range> givenRanges, Range rangeToRemove, RangeMergeStrategy strategy = RangeMergeStrategy.MergeColumns)
        {
            List<Range> result = new List<Range>();
            // Process each existing range.
            foreach (Range range in givenRanges)
            {
                if (!range.Overlaps(rangeToRemove))
                {
                    // No overlap: keep the range unchanged.
                    result.Add(range);
                }
                else
                {
                    // Overlapping range: subtract the removal area.
                    List<Range> subtractedPieces = SubtractRect(range, rangeToRemove);
                    result.AddRange(subtractedPieces);
                }
            }
            // Slice all ranges before merge
            List<Range> slicedRanges = SliceRanges(result);
            // Merge adjacent pieces if requested.
            if (strategy == RangeMergeStrategy.MergeColumns)
            {
                result = MergeAdjacentRanges(slicedRanges, RangeMergeStrategy.MergeColumns);
                result = MergeAdjacentRanges(result, RangeMergeStrategy.MergeRows);
            }
            else if (strategy == RangeMergeStrategy.MergeRows)
            {
                result = MergeAdjacentRanges(slicedRanges, RangeMergeStrategy.MergeRows);
                result = MergeAdjacentRanges(result, RangeMergeStrategy.MergeColumns);
            }
            else
            {
                result = slicedRanges;
            }
            return result;
        }

        private static List<Range> SliceRanges(List<Range> ranges)
        {
            HashSet<int> uniqueCols = new HashSet<int>();
            HashSet<int> uniqueRows = new HashSet<int>();
 
            // Collect all column and row boundaries
            foreach (Range range in ranges)
            {
                uniqueCols.Add(range.StartAddress.Column);
                uniqueCols.Add(range.EndAddress.Column + 1); // To handle gaps properly
                uniqueRows.Add(range.StartAddress.Row);
                uniqueRows.Add(range.EndAddress.Row + 1);
            }
 
            // Convert to sorted lists for iteration
            List<int> sortedCols = uniqueCols.OrderBy(c => c).ToList();
            List<int> sortedRows = uniqueRows.OrderBy(r => r).ToList();
 
            List<Range> slicedRanges = new List<Range>();
 
            // Step through the row and column boundaries to create the smallest sub-rectangles
            for (int r = 0; r < sortedRows.Count - 1; r++)
            {
                for (int c = 0; c < sortedCols.Count - 1; c++)
                {
                    Range subRange = new Range(
                        sortedCols[c],
                        sortedRows[r],
                        sortedCols[c + 1] - 1,
                        sortedRows[r + 1] - 1
                    );
 
                    // Only keep the sub-range if it was originally covered
                    if (ranges.Exists(range => range.Contains(subRange)))
                    {
                        slicedRanges.Add(subRange);
                    }
                }
            }
            return slicedRanges;
        }

        private static List<Range> SubtractRect(Range original, Range toRemove)
        {
            List<Range> pieces = new List<Range>();
            // Original boundaries:
            int orig_left = original.StartAddress.Column;
            int orig_top = original.StartAddress.Row;
            int orig_right = original.EndAddress.Column;
            int orig_bottom = original.EndAddress.Row;
            // Removal boundaries:
            int rem_left = toRemove.StartAddress.Column;
            int rem_top = toRemove.StartAddress.Row;
            int rem_right = toRemove.EndAddress.Column;
            int rem_bottom = toRemove.EndAddress.Row;
            // Compute intersection boundaries.
            int isct_left = Math.Max(orig_left, rem_left);
            int isct_top = Math.Max(orig_top, rem_top);
            int isct_right = Math.Min(orig_right, rem_right);
            int isct_bottom = Math.Min(orig_bottom, rem_bottom);
 
            // Slice the original rectangle into up to four pieces.
            // Top piece: if any rows exist above the intersection.
            if (orig_top < isct_top)
            {
                pieces.Add(new Range(orig_left, orig_top, orig_right, isct_top - 1));
            }
            // Bottom piece: if any rows exist below the intersection.
            if (isct_bottom < orig_bottom)
            {
                pieces.Add(new Range(orig_left, isct_bottom + 1, orig_right, orig_bottom));
            }
            // Left piece: if any columns exist to the left of the intersection within the vertical boundaries of the intersection.
            if (orig_left < isct_left)
            {
                pieces.Add(new Range(orig_left, isct_top, isct_left - 1, isct_bottom));
            }
            // Right piece: if any columns exist to the right of the intersection within the vertical boundaries of the intersection.
            if (isct_right < orig_right)
            {
                pieces.Add(new Range(isct_right + 1, isct_top, orig_right, isct_bottom));
            }
            return pieces;
        }

        private static List<Range> MergeAdjacentRanges(List<Range> ranges, RangeMergeStrategy strategy)
        {
            if (ranges.Count == 0)
            {
                return new List<Range>();
            }
            List<Range> mergedRanges = new List<Range>();
 
            if (strategy == RangeMergeStrategy.MergeColumns)
            {
                // Vertical merging: Ranges must have identical column boundaries.
                // Group by StartAddress.Column and EndAddress.Column.
                var groups = ranges.GroupBy(r => new
                {
                    StartCol = r.StartAddress.Column,
                    EndCol = r.EndAddress.Column
                });
                foreach (var group in groups)
                {
                    // Order by row (ascending)
                    List<Range> sorted = group.OrderBy(r => r.StartAddress.Row).ToList();
                    Range current = sorted[0];
                    for (int i = 1; i < sorted.Count; i++)
                    {
                        Range next = sorted[i];
                        // Check if the current range is contiguous with or overlapping the next.
                        // (That is, if current.EndAddress.Row + 1 is >= next.StartAddress.Row.)
                        if (current.EndAddress.Row + 1 >= next.StartAddress.Row)
                        {
                            // They share the same columns.
                            // Create a new range from current.StartAddress.Row to the maximum of the two EndAddress.Row values.
                            int newStartRow = current.StartAddress.Row;
                            int newEndRow = Math.Max(current.EndAddress.Row, next.EndAddress.Row);
                            current = new Range(
                                current.StartAddress.Column, newStartRow,
                                current.EndAddress.Column, newEndRow);
                        }
                        else
                        {
                            mergedRanges.Add(current);
                            current = next;
                        }
                    }
                    mergedRanges.Add(current);
                }
            }
            else if (strategy == RangeMergeStrategy.MergeRows)
            {
                // Horizontal merging: Ranges must have identical row boundaries.
                // Group by StartAddress.Row and EndAddress.Row.
                var groups = ranges.GroupBy(r => new
                {
                    StartRow = r.StartAddress.Row,
                    EndRow = r.EndAddress.Row
                });
                foreach (var group in groups)
                {
                    var sorted = group.OrderBy(r => r.StartAddress.Column).ToList();
                    Range current = sorted[0];
                    for (int i = 1; i < sorted.Count; i++)
                    {
                        Range next = sorted[i];
                        // Check if current.EndAddress.Column + 1 is >= next.StartAddress.Column.
                        if (current.EndAddress.Column + 1 >= next.StartAddress.Column)
                        {
                            int newStartCol = current.StartAddress.Column;
                            int newEndCol = Math.Max(current.EndAddress.Column, next.EndAddress.Column);
                            current = new Range(
                                newStartCol, current.StartAddress.Row,
                                newEndCol, current.EndAddress.Row);
                        }
                        else
                        {
                            mergedRanges.Add(current);
                            current = next;
                        }
                    }
                    mergedRanges.Add(current);
                }
            }
            return mergedRanges;
        }
 
    }
}