98 lines
4.1 KiB
C#
98 lines
4.1 KiB
C#
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<ExportService> _logger;
|
|
|
|
public ExportService(ILogger<ExportService> logger)
|
|
{
|
|
_logger = logger;
|
|
}
|
|
|
|
/// <summary>
|
|
/// Exports all entries for the given month as an Excel workbook.
|
|
/// Returns the raw bytes of the .xlsx file.
|
|
/// </summary>
|
|
public async Task<byte[]> 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();
|
|
}
|
|
|
|
/// <summary>
|
|
/// Returns the suggested filename for the export, e.g. "suivi-01-2024.xlsx".
|
|
/// </summary>
|
|
public static string GetFileName(int monthNumber, int year)
|
|
=> $"suivi-{monthNumber:00}-{year}.xlsx";
|
|
}
|