// création de l'onglet AuditTrail
        // cet onglet est utilisé pour stocker l'historique
        Worksheet CreateAuditTrailSheet(SpreadSheetFile spreadSheet, string name)
        {
            var auditTrail = spreadSheet.FindSheet(name);

            if (auditTrail == null)
            {
                auditTrail = spreadSheet.FindOrCreateSheet(name);
                spreadSheet.SetCellValue(auditTrail, "A", 1, "Event ID");
                spreadSheet.SetCellValue(auditTrail, "B", 1, "TimeStamp (UTC)");
                spreadSheet.SetCellValue(auditTrail, "C", 1, "User Id");
                spreadSheet.SetCellValue(auditTrail, "D", 1, "Action");
            }
            return(auditTrail);
        }
        // génération du fichier de sortie
        async Task WriteAuditInfo(List <AuditLog> audits)
        {
            if (audits != null && audits.Count > 0)
            {
                var guids = new Dictionary <string, string>();
                for (var i = 0; i < audits.Count; i++)
                {
                    guids.Clear();
                    var audit = audits[i];

                    var symbols = new Dictionary <string, string>()
                    {
                        { "companyid", audit.CompanyId },
                        { "timestamp", DateTime.UtcNow.ToString("yyyyMMdd_HHmmss") }
                    };
                    var outputFileName = Template.ApplySymbols(_auditFile, symbols);

                    var dirName = Path.GetDirectoryName(outputFileName);
                    if (string.IsNullOrWhiteSpace(dirName))
                    {
                        dirName = ".";
                    }
                    outputFileName = Path.GetFullPath(Path.Combine(dirName, outputFileName));

                    if (!File.Exists(outputFileName))
                    {
                        if (!await ReusePreviousSpreadsheet(outputFileName))
                        {
                            CreateSpreadSheet(outputFileName);
                        }
                    }
                    _logger.Log($"Saving result to {outputFileName}");

                    using (var spreadSheet = new SpreadSheetFile(outputFileName)) {
                        var auditTrail = CreateAuditTrailSheet(spreadSheet, SheetNames.AuditTrail);
                        var colA       = spreadSheet.LoadColumnCells(auditTrail, "A");
                        for (var j = 0; j < colA.Count; j++)
                        {
                            var c    = colA[j];
                            var guid = spreadSheet.GetCellValue(c)?.ToString() ?? string.Empty;
                            if (!string.IsNullOrWhiteSpace(guid))
                            {
                                guids.Add(guid, null);
                            }
                        }
                        var maxRow = spreadSheet.GetMaxRow(colA);
                        foreach (var log in audit.Result.OrderBy(_ => _.date))
                        {
                            if (!guids.ContainsKey(log.Guid))
                            {
                                maxRow += 1;
                                spreadSheet.SetCellValue(auditTrail, "A", maxRow, log.Guid);
                                spreadSheet.SetCellValue(auditTrail, "B", maxRow, log.Date);
                                spreadSheet.SetCellValue(auditTrail, "C", maxRow, log.UserId);
                                spreadSheet.SetCellValue(auditTrail, "D", maxRow, log.Action);
                                auditTrail.Save();
                            }
                        }

                        spreadSheet.Save();
                    }
                }
            }
        }
        // création de l'onglet "Summary" (tableau qui consolide le nombre d'évènements mois par mois)
        void CreateSummarySheet(SpreadSheetFile spreadSheet, string name, int year)
        {
            name = name.Replace("{year}", year.ToString(), StringComparison.InvariantCultureIgnoreCase);
            if (spreadSheet.FindSheet(name) == null)
            {
                var summary = spreadSheet.FindOrCreateSheet(name);
                spreadSheet.SetCellValue(summary, "B", 2, "Automation UserId");
                spreadSheet.SetCellValue(summary, "E", 2, "0000");
                spreadSheet.SetCellValue(summary, "B", 3, "Administration UserId");
                spreadSheet.SetCellValue(summary, "E", 3, "0000");
                spreadSheet.SetCellValue(summary, "B", 4, "CAST UserId");
                spreadSheet.SetCellValue(summary, "E", 4, "0000");

                var events = new [] {
                    new EventInfo()
                    {
                        Category = "LOGIN", Label = "Login: Successfull connection"
                    },
                    new EventInfo()
                    {
                        Category = "LOGIN", Label = "Login: Incorrect password"
                    },
                    new EventInfo()
                    {
                        Category = "LOGIN", Label = "Switch Company Context"
                    },
                    new EventInfo()
                    {
                        Category = "LOGIN", Label = "Disconnect"
                    },
                    new EventInfo()
                    {
                        Category = "ADMIN", Label = "Create Domain"
                    },
                    new EventInfo()
                    {
                        Category = "ADMIN", Label = "Delete Domain"
                    },
                    new EventInfo()
                    {
                        Category = "ADMIN", Label = "Create or Update Custom Dashboard"
                    },
                    new EventInfo()
                    {
                        Category = "ADMIN", Label = "Update Computed Indicator"
                    },
                    new EventInfo()
                    {
                        Category = "ADMIN", Label = "Create or Update Tag"
                    },
                    new EventInfo()
                    {
                        Category = "APPLICATION", Label = "Create Application"
                    },
                    new EventInfo()
                    {
                        Category = "APPLICATION", Label = "Update Application"
                    },
                    new EventInfo()
                    {
                        Category = "APPLICATION", Label = "Add Tag to application"
                    },
                    new EventInfo()
                    {
                        Category = "APPLICATION", Label = "Remove Tag from application"
                    },
                    new EventInfo()
                    {
                        Category = "APPLICATION", Label = "Delete Application"
                    },
                    new EventInfo()
                    {
                        Category = "CAMPAIGN", Label = "Create Campaign"
                    },
                    new EventInfo()
                    {
                        Category = "CAMPAIGN", Label = "Update Campaign"
                    },
                    new EventInfo()
                    {
                        Category = "RESULT", Label = "Upload Files"
                    },
                    new EventInfo()
                    {
                        Category = "RESULT", Label = "Remove Result Files"
                    },
                    new EventInfo()
                    {
                        Category = "RESULT", Label = "Create Result"
                    },
                    new EventInfo()
                    {
                        Category = "RESULT", Label = "Update Result"
                    },
                    new EventInfo()
                    {
                        Category = "RESULT", Label = "Update Survey Answers"
                    },
                    new EventInfo()
                    {
                        Category = "USER", Label = "Create User"
                    },
                    new EventInfo()
                    {
                        Category = "USER", Label = "Update User"
                    },
                    new EventInfo()
                    {
                        Category = "USER", Label = "Update User Password"
                    },
                };

                var prevCat = string.Empty;
                for (var i = 0; i < events.Length; i++)
                {
                    var eventInfo = events[i];
                    if (eventInfo.Category != prevCat)
                    {
                        var col = spreadSheet.GetColumnName(7 + i);
                        spreadSheet.SetCellValue(summary, col, 5, eventInfo.Category);
                        prevCat = eventInfo.Category;
                    }
                }

                spreadSheet.SetCellValue(summary, "B", 6, "Année");
                spreadSheet.SetCellValue(summary, "C", 6, "Mois");
                spreadSheet.SetCellValue(summary, "D", 6, "Début");
                spreadSheet.SetCellValue(summary, "E", 6, "Fin");
                spreadSheet.SetCellValue(summary, "F", 6, "TOTAL");
                for (var i = 0; i < events.Length; i++)
                {
                    var eventInfo = events[i];
                    var col       = spreadSheet.GetColumnName(7 + i);
                    spreadSheet.SetCellValue(summary, col, 6, eventInfo.Label);
                }

                for (uint month = 1; month <= 12; month++)
                {
                    var row = 6 + month;
                    spreadSheet.SetCellValue(summary, "B", row, year);
                    spreadSheet.SetCellValue(summary, "C", row, month);
                    spreadSheet.SetCellFormula(summary, "D", row, $"DATE(B{row},C{row},1)");
                    spreadSheet.SetCellFormula(summary, "E", row, $"DATE(B{row},C{row}+1,1)");
                    spreadSheet.SetCellFormula(summary, "F", row, $"SUM(G{row}:AC{row})");
                    for (var i = 0; i < events.Length; i++)
                    {
                        var eventInfo = events[i];
                        var col       = spreadSheet.GetColumnName(7 + i);
                        spreadSheet.SetCellFormula(summary, col, row, $@"COUNTIFS({SheetNames.AuditTrail}!$B:$B,"">=""&$D{row},{SheetNames.AuditTrail}!$B:$B,""<""&$E{row},{SheetNames.AuditTrail}!$D:$D,{col}$6,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$2,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$3,{SheetNames.AuditTrail}!$C:$C,""<>""&$E$4)");
                    }
                }

                spreadSheet.SetCellFormula(summary, "F", 19, $"SUM(F7:F18)");
                for (var i = 0; i < events.Length; i++)
                {
                    var eventInfo = events[i];
                    var col       = spreadSheet.GetColumnName(7 + i);
                    spreadSheet.SetCellFormula(summary, col, 19, $"SUM({col}7:{col}18)");
                }
            }
        }