BRR/ClockifyReport/Business/ReportGenerator.cs

using Spire.Xls;
using System;
using System.Collections.Generic;
using System.Linq;
using ClockifyReport.Models;
using System.Drawing;

namespace BlackRockReportFunction.Bussines
{
    internal class ReportGenerator
    {
        public const string DefaultFontName = "DejaVu Sans";
        public const int DefaultFontSize = 20;

        public static Workbook GenerateReportContent(Root reportObject)
        {
            var excelFile = new Workbook();
            excelFile.Worksheets.Clear();
            var ws = excelFile.Worksheets.Add("Content");

            AddReportItems(reportObject, ws);

            // Autofit
            AutoFitReport(excelFile);

            return excelFile;
        }

        public static void AddReportItems(Root reportObject, Worksheet ws)
        {
            TimeSpan t, time, time1;
            int row = 1;
            string[] sectionNames = { "User     ", "Description     ", "Time (h)     ", "Time (decimal)     " };
            List<string> usersDecimalHours = new List<string>();

            for (int i = 0; i < sectionNames.Length; i++)
            {
                ws.Range[row, i + 1].Style.Font.FontName = "DejaVu Sans";
                ws.Range[row, i + 1].Style.Font.Size = 12;
                ws.Range[row, i + 1].Value = sectionNames[i];
                ws.Range[row, i + 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                ws.Range[row, i + 1].Style.Borders[BordersLineType.EdgeBottom].LineStyle = LineStyleType.Thick;
                ws.Range[row, i + 1].Style.Borders[BordersLineType.EdgeBottom].Color = Color.LightGray;
            }

            row++;

            foreach (var reportPerson in reportObject.groupOne)
            {
                ws.Range[row, 1].Style.Font.FontName = "DejaVu Sans";
                ws.Range[row, 1].Style.Font.Size = 10;
                ws.Range[row, 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                ws.Range[row, 1].Value = reportPerson.name;

                ws.Range[row, 2].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

                time = TimeSpan.FromSeconds(reportPerson.duration);
                var sumOfRecordHours = string.Format("{0:00}:{1:D2}:{2:D2}", Math.Floor(time.TotalHours), time.Minutes, time.Seconds);
                ws.Range[row, 3].Style.Font.FontName = "DejaVu Sans";
                ws.Range[row, 3].Style.Font.Size = 10;
                ws.Range[row, 3].Style.HorizontalAlignment = HorizontalAlignType.Right;
                ws.Range[row, 3].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                ws.Range[row, 3].Value = sumOfRecordHours;

                ws.Range[row, 4].Style.Font.FontName = "DejaVu Sans";
                ws.Range[row, 4].Style.Font.Size = 10;
                ws.Range[row, 4].Style.HorizontalAlignment = HorizontalAlignType.Right;
                ws.Range[row, 4].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                var userDecimalHours = Convert.ToDecimal(TimeSpan.FromSeconds(reportPerson.duration).TotalHours).ToString("0.00"); ;
                ws.Range[row, 4].Value = userDecimalHours;
                usersDecimalHours.Add(userDecimalHours);

                row++;

                foreach (var personRecord in reportPerson.children)
                {
                    ws.Range[row, 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;

                    ws.Range[row, 2].Style.Font.FontName = "DejaVu Sans";
                    ws.Range[row, 2].Style.Font.Size = 10;
                    ws.Range[row, 2].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                    ws.Range[row, 2].Value = personRecord.name;

                    ws.Range[row, 3].Style.Font.FontName = "DejaVu Sans";
                    ws.Range[row, 3].Style.Font.Size = 10;
                    ws.Range[row, 3].Style.HorizontalAlignment = HorizontalAlignType.Right;
                    ws.Range[row, 3].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                    t = TimeSpan.FromSeconds(personRecord.duration);
                    ws[row, 3].Value = string.Format("{0:00}:{1:D2}:{2:D2}", Math.Floor(t.TotalHours), t.Minutes, t.Seconds);

                    ws.Range[row, 4].Style.Font.FontName = "DejaVu Sans";
                    ws.Range[row, 4].Style.Font.Size = 10;
                    ws.Range[row, 4].Style.HorizontalAlignment = HorizontalAlignType.Right;
                    ws.Range[row, 4].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
                    ws.Range[row, 4].Value = Convert.ToDecimal(TimeSpan.FromSeconds(personRecord.duration).TotalHours).ToString("0.00");

                    row++;
                }
            }

            var totalSum = reportObject.totals.Select(total => total.totalTime).FirstOrDefault();

            ws.Range[row, 1].Style.Font.FontName = "DejaVu Sans";
            ws.Range[row, 1].Style.Font.Size = 10;
            ws.Range[row, 1].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            ws.Range[row, 1].Value = "Total";

            ws.Range[row, 3].Style.Font.FontName = "DejaVu Sans";
            ws.Range[row, 3].Style.Font.Size = 10;
            ws.Range[row, 3].Style.HorizontalAlignment = HorizontalAlignType.Right;
            ws.Range[row, 3].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            time1 = t = TimeSpan.FromSeconds(totalSum);
            ws.Range[row, 3].Value = string.Format("{0:00}:{1:D2}:{2:D2}", Math.Floor(time1.TotalHours), time1.Minutes, time1.Seconds);

            ws.Range[row, 4].Style.Font.FontName = "DejaVu Sans";
            ws.Range[row, 4].Style.Font.Size = 10;
            ws.Range[row, 4].Style.HorizontalAlignment = HorizontalAlignType.Right;
            ws.Range[row, 4].Style.Borders[BordersLineType.EdgeRight].LineStyle = LineStyleType.Thin;
            ws.Range[row, 4].Value = Convert.ToDecimal(TimeSpan.FromSeconds(totalSum).TotalHours).ToString("0.00");
        }

        public static void AutoFitReport(Workbook excelFile)
        {
            foreach (var sheet in excelFile.Worksheets)
            {
                var columnCount = sheet.Columns.Count();
                for (int i = 1; i < columnCount; i++)
                {
                    sheet.AutoFitColumn(i);
                }

                var rowCount = sheet.Rows.Count();
                for (int i = 1; i < rowCount; i++)
                {
                    sheet.AutoFitRow(i);
                }

            }
        }
    }
}