Sindika.AspNet.Common 1.14.3

Sindika.AspNet.Common.ExcelTemplate

Modul ExcelTemplate menyediakan infrastruktur generik untuk melakukan proses import data dari file Excel secara aman, fleksibel, dan terstruktur. Komponen ini dirancang agar mudah diintegrasikan dengan berbagai modul aplikasi .NET, mendukung validasi, preview, dan commit data dengan metadata berbasis atribut.

🚀 Fitur Utama

• Deklaratif via Attribute — Setiap properti DTO dapat ditandai dengan ExcelColumnAttribute untuk mendeskripsikan header kolom, contoh nilai, dan aturan wajib.
• Preview Import — Menyediakan hasil pembacaan file Excel tanpa langsung melakukan commit ke database.
• Validasi Otomatis — Setiap sel dapat mengandung informasi error atau peringatan sebelum proses penyimpanan.
• Submit Import — Mendukung commit data berdasarkan hasil preview dengan laporan error yang detail.
• Permission dan Reference Sheet — Menyediakan konteks izin pengguna dan referensi antar sheet untuk validasi silang.

🧩 Struktur Kelas

🚀 Implementasi Fitur

1. Generate Template Excel Menghasilkan file Excel sesuai metadata atribut pada DTO.
2. Preview Import Melakukan validasi awal, menampilkan data yang valid, error, dan status update/insert.
3. Submit Import Melakukan proses penyimpanan (insert/update) ke database berdasarkan hasil preview.

📦 Instalasi

Daftarkan modul ExcelTemplate di Program.cs:

builder.Services.AddExcelTools();

🧩 Endpoint Utama

1. Generate Template

[HttpGet("download-template")]
public async Task<IActionResult> DownloadTemplate()
{
    var name = User.Claims.FirstOrDefault(c => c.Type == "name")?.Value ?? "Unknown User";

    var excelBytes = await _excelTemplateWorkflowService.GenerateTemplateAsync<CostCenterTemplate, CostCenter>(
        dataFunc: async () =>
        {
            return await _costcenterService.GetsAsync<CostCenter>(q => q.Include(c => c.ProfitCenter));
        },
        author: name,
        referenceSheets: new[]
        {
            new ReferenceSheet
            {
                SheetName = nameof(ProfitCenter),
                EntityType = typeof(ProfitCenter),
                Columns = new[] { nameof(ProfitCenter.Id), nameof(ProfitCenter.Code), nameof(ProfitCenter.Name) },
                Data = await _profitCenterService.GetsAsync<ProfitCenter>()
            }
        }
    );

    return File(excelBytes, "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet", "CostCenterTemplate.xlsx");
}

📄 Hasil: file Excel siap diisi pengguna dengan referensi data ProfitCenter.

2. Preview Import

[HttpPost("preview-import")]
[ProducesResponseType(typeof(BaseResponse<ImportPreviewResult<CostCenterTemplate, CostCenter>, object>), StatusCodes.Status200OK)]
public async Task<IActionResult> PreviewImport(IFormFile file)
{
    using var stream = file.OpenReadStream();
    var filePath = $"template/{Guid.NewGuid()}{Path.GetExtension(file.FileName)}";

    var validator = _excelValidatorFactory.Create<CostCenterTemplate>();
    var permission = _excelPermissionFactory.Create<CostCenter>();

    var result = await _excelTemplateWorkflowService.PreviewImportAsync<CostCenterTemplate, CostCenter>(
        excelStream: stream,
        filePath: filePath,
        validators: new[] { validator },
        permission: permission
    );

    await _storageService.UploadFile(filePath, stream);

    return Ok(ResponseHelper.Success<object>(result, "Preview import successful", _costcenterService.GetInfo()));
}

📊 Hasil: JSON yang menampilkan status per baris (Insert, Update, Skip), validasi field, dan error detail.

3. Submit Import

[HttpPost("submit-import")]
[ProducesResponseType(typeof(BaseResponse<ImportSubmitResult<CostCenterTemplate, CostCenter>, object>), StatusCodes.Status200OK)]
public async Task<IActionResult> SubmitImport([FromBody] BaseRequest<string> request)
{
    var stream = await _storageService.GetFile(request.Data);

    var validator = _excelValidatorFactory.Create<CostCenterTemplate>();
    var permission = _excelPermissionFactory.Create<CostCenter>();
    var mapper = _excelMapperFactory.Create<CostCenterTemplate, CostCenter>();

    var previewResult = await _excelTemplateWorkflowService.PreviewImportAsync<CostCenterTemplate, CostCenter>(
        excelStream: stream,
        filePath: request.Data,
        validators: new[] { validator },
        permission: permission
    );

    var result = await _excelTemplateWorkflowService.SubmitImportAsync<CostCenterTemplate, CostCenter, Context>(
        preview: previewResult,
        mapper: mapper,
        batchSize: 200);

    return Ok(ResponseHelper.Success<object>(result, "Submit import successful", _costcenterService.GetInfo()));
}

✅ Hasil: Data valid tersimpan ke database, hasil akhir menampilkan jumlah baris berhasil dan gagal.

🧱 Struktur Template Class

1. Template DTO

[ExcelMetadata(sheetName: "Template", systemName: "SINPRO", version: "1.0.0", Description = "Template untuk upload master data Cost Center")]
public class CostCenterTemplate
{
    [ExcelColumn("Kode Cost Center", true, true, Example = "CST-1")]
    public string Code { get; set; } = string.Empty;

    [ExcelColumn("Nama Cost Center", true, false, Example = "COST CENTER 1")]
    public string Name { get; set; } = string.Empty;

    [ExcelColumn("Kode Profit Center", true, false, Example = "XXXX", Reference = "profitcenter.code")]
    public string ProfitCenterCode { get; set; } = string.Empty;

    [ExcelColumn("", false, false, Ignore = true)]
    public Guid ProfitCenterId { get; set; }

    [ExcelColumn("User", false, false, Example = "Gus Gus", Width = 100)]
    public string CreatedBy { get; set; } = string.Empty;
}

2. Validator

public class CostCenterValidator : IExcelValidator<CostCenterTemplate>
{
    private readonly IProfitCenterRepository _profitCenterRepository;

    public CostCenterValidator(IProfitCenterRepository profitCenterRepository)
    {
        _profitCenterRepository = profitCenterRepository;
    }

    public async Task<List<string>> ValidateAsync(CostCenterTemplate dto)
    {
        var profitCenters = await _profitCenterRepository.GetsAsync();
        var dict = profitCenters.ToDictionary(c => c.Code);

        var errors = new List<string>();
        if (!dict.TryGetValue(dto.ProfitCenterCode, out var profitCenter))
            errors.Add($"ProfitCenterCode '{dto.ProfitCenterCode}' tidak ditemukan");
        else dto.ProfitCenterId = profitCenter.Id;

        return errors;
    }
}

3. Permission

public class CostCenterPermission : IExcelPermission<CostCenter>
{
    private readonly ICostCenterRepository _repo;

    public CostCenterPermission(ICostCenterRepository repo) => _repo = repo;

    public async Task<IDictionary<string, PermissionInfo<CostCenter>>> PermissionAsync()
    {
        var list = await _repo.GetsAsync(q => q.Where(c => !string.IsNullOrWhiteSpace(c.Code)));
        return list.ToDictionary(c => c.Code, c => new PermissionInfo<CostCenter> { Allowed = true, Entity = c });
    }
}

4. Mapper

public class CostCenterMapper : IExcelMapper<CostCenterTemplate, CostCenter>
{
    public async Task<CostCenter> MapAsync(CostCenterTemplate dto, CostCenter? existing)
    {
        await Task.CompletedTask;
        if (existing == null) return dto.Adapt<CostCenter>();
        dto.Adapt(existing);
        existing.UpdatedDate = DateTimeOffset.UtcNow;
        return existing;
    }
}

🪪 Lisensi

© PT Sinergi Dimensi Informatika