WorkTracker/Services/Exports/MonthlyTimesheetExcelExporter.cs

using ClosedXML.Excel;
using WorkTracker.Domain;

namespace WorkTracker.Services.Exports;

public sealed class MonthlyTimesheetExcelExporter : IMonthlyTimesheetExcelExporter
{
    private const int DayHeaderRow = 1;
    private const int DayStartColumn = 3;
    private const int TemplateDayCount = 30;
    private const int SaturdaySampleColumn = 6;
    private const int SundayHolidaySampleColumn = 7;

    private static readonly IReadOnlyDictionary<string, int> TimesheetRowMap = new Dictionary<string, int>(StringComparer.Ordinal)
    {
        ["office"] = 2,
        ["home"] = 4,
        ["overtime"] = 5,
        ["weekend"] = 6,
        ["night"] = 7,
        ["vacation"] = 8,
        ["permit"] = 9,
        ["compensatory-rest"] = 10,
        ["sick"] = 11,
        ["holiday"] = 12
    };

    public byte[] Export(MonthlyTimesheetModel timesheet, Stream templateStream)
    {
        ArgumentNullException.ThrowIfNull(timesheet);
        ArgumentNullException.ThrowIfNull(templateStream);

        if (templateStream.CanSeek)
        {
            templateStream.Position = 0;
        }

        using var workbook = new XLWorkbook(templateStream);
        workbook.DefinedNames.DeleteAll();
        var worksheet = workbook.Worksheet(1);
        var dayCount = timesheet.Days.Count;
        var styleCatalog = CaptureDayStyleCatalog(worksheet);

        AdjustDayColumns(worksheet, dayCount);
        ApplyDayHeaders(worksheet, timesheet.Days, styleCatalog);
        ApplyRowValues(worksheet, timesheet.Rows, dayCount);
        ApplyTotalFormulas(worksheet, dayCount);

        using var output = new MemoryStream();
        workbook.SaveAs(output);
        return output.ToArray();
    }

    private static void AdjustDayColumns(IXLWorksheet worksheet, int dayCount)
    {
        var totalColumn = DayStartColumn + TemplateDayCount;
        var delta = dayCount - TemplateDayCount;

        if (delta > 0)
        {
            worksheet.Column(totalColumn).InsertColumnsBefore(delta);
        }
        else if (delta < 0)
        {
            worksheet.Columns(DayStartColumn + dayCount, DayStartColumn + TemplateDayCount - 1).Delete();
        }
    }

    private static void ApplyDayHeaders(IXLWorksheet worksheet, IReadOnlyList<MonthlyTimesheetDayModel> days, DayStyleCatalog styleCatalog)
    {
        var lastDayColumn = DayStartColumn + days.Count - 1;

        for (var index = 0; index < days.Count; index++)
        {
            var day = days[index];
            var column = DayStartColumn + index;

            var headerCell = worksheet.Cell(DayHeaderRow, column);
            headerCell.Value = day.Date.Day;
            ApplyDayStyle(headerCell, day, column, lastDayColumn, 1, styleCatalog);

            foreach (var rowNumber in TimesheetRowMap.Values)
            {
                ApplyDayStyle(worksheet.Cell(rowNumber, column), day, column, lastDayColumn, rowNumber, styleCatalog);
            }
        }
    }

    private static void ApplyRowValues(IXLWorksheet worksheet, IReadOnlyList<MonthlyTimesheetRowModel> rows, int dayCount)
    {
        foreach (var row in rows)
        {
            if (!TimesheetRowMap.TryGetValue(row.Key, out var worksheetRow))
            {
                continue;
            }

            for (var dayIndex = 0; dayIndex < dayCount; dayIndex++)
            {
                var cell = worksheet.Cell(worksheetRow, DayStartColumn + dayIndex);
                var value = dayIndex < row.DailyValues.Count ? row.DailyValues[dayIndex] : null;
                if (value.HasValue && value.Value > 0m)
                {
                    cell.Value = value.Value;
                }
                else
                {
                    cell.Clear(XLClearOptions.Contents);
                }
            }
        }
    }

    private static void ApplyTotalFormulas(IXLWorksheet worksheet, int dayCount)
    {
        var lastDayColumn = DayStartColumn + dayCount - 1;
        var totalColumn = lastDayColumn + 1;
        var firstDayColumnLetter = XLHelper.GetColumnLetterFromNumber(DayStartColumn);
        var lastDayColumnLetter = XLHelper.GetColumnLetterFromNumber(lastDayColumn);

        foreach (var rowNumber in TimesheetRowMap.Values)
        {
            worksheet.Cell(rowNumber, totalColumn).FormulaA1 = $"SUM({firstDayColumnLetter}{rowNumber}:{lastDayColumnLetter}{rowNumber})";
        }
    }

    private static DayStyleCatalog CaptureDayStyleCatalog(IXLWorksheet worksheet)
    {
        return new DayStyleCatalog(
            CaptureStylesForColumn(worksheet, DayStartColumn),
            CaptureStylesForColumn(worksheet, DayStartColumn + 1),
            CaptureStylesForColumn(worksheet, DayStartColumn + TemplateDayCount - 1),
            CaptureStylesForColumn(worksheet, SaturdaySampleColumn),
            CaptureStylesForColumn(worksheet, SundayHolidaySampleColumn));
    }

    private static IReadOnlyDictionary<int, IXLStyle> CaptureStylesForColumn(IXLWorksheet worksheet, int column)
    {
        var rowNumbers = new[] { 1, 2, 4, 12 };
        var styles = new Dictionary<int, IXLStyle>(rowNumbers.Length);

        foreach (var rowNumber in rowNumbers)
        {
            styles[rowNumber] = worksheet.Cell(rowNumber, column).Style;
        }

        return styles;
    }

    private static void ApplyDayStyle(IXLCell targetCell, MonthlyTimesheetDayModel day, int column, int lastDayColumn, int rowNumber, DayStyleCatalog styleCatalog)
    {
        var baseStyle = styleCatalog.GetBaseStyle(rowNumber, column == DayStartColumn, column == lastDayColumn);

        if (!ShouldHighlight(day))
        {
            targetCell.Style = baseStyle;
            return;
        }

        var sampleStyle = styleCatalog.GetHighlightStyle(rowNumber, day);

        targetCell.Style = sampleStyle;
        targetCell.Style.Border.LeftBorder = baseStyle.Border.LeftBorder;
        targetCell.Style.Border.LeftBorderColor = baseStyle.Border.LeftBorderColor;
        targetCell.Style.Border.RightBorder = baseStyle.Border.RightBorder;
        targetCell.Style.Border.RightBorderColor = baseStyle.Border.RightBorderColor;
        targetCell.Style.Border.TopBorder = baseStyle.Border.TopBorder;
        targetCell.Style.Border.TopBorderColor = baseStyle.Border.TopBorderColor;
        targetCell.Style.Border.BottomBorder = baseStyle.Border.BottomBorder;
        targetCell.Style.Border.BottomBorderColor = baseStyle.Border.BottomBorderColor;
    }

    private static bool ShouldHighlight(MonthlyTimesheetDayModel day)
    {
        return day.IsWeekend || day.IsHoliday;
    }

    private static int GetStyleRow(int rowNumber)
    {
        return rowNumber switch
        {
            1 => 1,
            2 => 2,
            4 or 5 or 6 or 7 or 8 or 9 or 10 or 11 => 4,
            12 => 12,
            _ => rowNumber
        };
    }

    private sealed class DayStyleCatalog(
        IReadOnlyDictionary<int, IXLStyle> firstColumnStyles,
        IReadOnlyDictionary<int, IXLStyle> middleColumnStyles,
        IReadOnlyDictionary<int, IXLStyle> lastColumnStyles,
        IReadOnlyDictionary<int, IXLStyle> saturdayStyles,
        IReadOnlyDictionary<int, IXLStyle> sundayHolidayStyles)
    {
        public IXLStyle GetBaseStyle(int rowNumber, bool isFirstColumn, bool isLastColumn)
        {
            var styleRow = GetStyleRow(rowNumber);

            if (isFirstColumn)
            {
                return firstColumnStyles[styleRow];
            }

            if (isLastColumn)
            {
                return lastColumnStyles[styleRow];
            }

            return middleColumnStyles[styleRow];
        }

        public IXLStyle GetHighlightStyle(int rowNumber, MonthlyTimesheetDayModel day)
        {
            var styleRow = GetStyleRow(rowNumber);

            if (rowNumber is 1 or 2)
            {
                return day.IsHoliday || day.Date.DayOfWeek == DayOfWeek.Sunday
                    ? sundayHolidayStyles[styleRow]
                    : saturdayStyles[styleRow];
            }

            return saturdayStyles[styleRow];
        }
    }
}