using ExpenseTracker.Api.Data; using ExpenseTracker.Api.Models; using Microsoft.EntityFrameworkCore; using OfficeOpenXml; using OfficeOpenXml.Style; namespace ExpenseTracker.Api.Services; public class ExportService { private readonly ILogger _logger; public ExportService(ILogger logger) { _logger = logger; } /// /// Exports all entries for the given month as an Excel workbook. /// Returns the raw bytes of the .xlsx file. /// public async Task ExportMonthAsync(Guid monthId, AppDbContext db) { var month = await db.Months .AsNoTracking() .FirstOrDefaultAsync(m => m.Id == monthId); var entries = await db.Entries .AsNoTracking() .Include(e => e.Category) .Where(e => e.MonthId == monthId) .OrderBy(e => e.EntryDate) .ThenBy(e => e.CreatedAt) .ToListAsync(); _logger.LogInformation("Exporting {Count} entries for month {MonthId}.", entries.Count, monthId); using var package = new ExcelPackage(); var ws = package.Workbook.Worksheets.Add("Saisies"); // ── Headers ────────────────────────────────────────────────────────── ws.Cells[1, 1].Value = "Date"; ws.Cells[1, 2].Value = "Type"; ws.Cells[1, 3].Value = "Libellé"; ws.Cells[1, 4].Value = "Catégorie"; ws.Cells[1, 5].Value = "Montant (€)"; using (var headerRange = ws.Cells[1, 1, 1, 5]) { headerRange.Style.Font.Bold = true; headerRange.Style.Fill.PatternType = ExcelFillStyle.Solid; headerRange.Style.Fill.BackgroundColor.SetColor(System.Drawing.Color.FromArgb(59, 130, 212)); headerRange.Style.Font.Color.SetColor(System.Drawing.Color.White); } // ── Data rows ───────────────────────────────────────────────────────── int row = 2; foreach (var entry in entries) { ws.Cells[row, 1].Value = entry.EntryDate.ToString("yyyy-MM-dd"); ws.Cells[row, 2].Value = entry.Type.ToString(); ws.Cells[row, 3].Value = entry.Label; ws.Cells[row, 4].Value = entry.Category?.Name ?? string.Empty; ws.Cells[row, 5].Value = entry.Amount; ws.Cells[row, 5].Style.Numberformat.Format = "#,##0.00 [$€-40C]"; row++; } // ── Totals ──────────────────────────────────────────────────────────── int totalRow = row + 1; decimal totalIncome = entries.Where(e => e.Type == EntryType.INCOME).Sum(e => e.Amount); decimal totalExpense = entries.Where(e => e.Type == EntryType.EXPENSE).Sum(e => e.Amount); ws.Cells[totalRow, 3].Value = "Total Entrées"; ws.Cells[totalRow, 5].Value = totalIncome; ws.Cells[totalRow, 5].Style.Numberformat.Format = "#,##0.00 [$€-40C]"; ws.Cells[totalRow, 3, totalRow, 5].Style.Font.Bold = true; int expenseRow = totalRow + 1; ws.Cells[expenseRow, 3].Value = "Total Dépenses"; ws.Cells[expenseRow, 5].Value = totalExpense; ws.Cells[expenseRow, 5].Style.Numberformat.Format = "#,##0.00 [$€-40C]"; ws.Cells[expenseRow, 3, expenseRow, 5].Style.Font.Bold = true; // ── Auto-fit columns ───────────────────────────────────────────────── ws.Cells[ws.Dimension?.Address ?? "A1:E1"].AutoFitColumns(); return await package.GetAsByteArrayAsync(); } /// /// Returns the suggested filename for the export, e.g. "suivi-01-2024.xlsx". /// public static string GetFileName(int monthNumber, int year) => $"suivi-{monthNumber:00}-{year}.xlsx"; }