ExportToExcelService Implementation in C#

This C# code snippet defines an ExportToExcelService which implements the IExportToExcelService interface. It uses ClosedXML for Excel operations and various other dependencies injected through the constructor.
 avatar
user_6104052
csharp
21 days ago
5.2 kB
2
Indexable
Never
using ClosedXML.Excel;
using Microsoft.Extensions.Options;
using Notenetic.Model.ViewModels;
using Notenetic.Service.Data.Model.Common;
using Notenetic.Service.Services.Contract;
using System.Collections.Generic;
using System.IO;


namespace Notenetic.Service.Services
{
    [ScopedService]
    public class ExportToExcelService : IExportToExcelService
    {
        private XLColor _headerColor = XLColor.FromArgb(0xFDEEDB);
        private readonly IS3Service _s3Service;
        private readonly IClientRepository _clientRepository;
        private readonly AppSettings _appsettings;
        private readonly IClientService _clientService;

        public ExportToExcelService(IS3Service s3Service,
            IOptions<AppSettings> appsettings,
            IClientRepository clientRepository,
            IClientService clientService)
        {
            _s3Service = s3Service;
            _appsettings = appsettings.Value;
            _clientRepository = clientRepository;
            _clientService = clientService;
        }

        public async Task<string> ExportToPdf(List<ComplianceReportClientDetailsModel> complianceReport, LogInStaff logInStaff)
        {
            using var workbook = new XLWorkbook();
            workbook.Author = "Notenetic";
            workbook.Style.Font.FontName = "Manrope";
            var worksheet = workbook.Worksheets.Add("Compliance");
            var columnNames = AddFirstRow(worksheet, complianceReport.First());

            var index = columnNames.RowNumber();

            foreach (var rpt in complianceReport)
            {
                index++;
                await AddRow(worksheet, index, rpt, logInStaff);
            }
           
            using var stream = new MemoryStream();
            workbook.SaveAs(stream, new SaveOptions { EvaluateFormulasBeforeSaving = false, GenerateCalculationChain = false, ValidatePackage = false });
            return await _s3Service.PutStreamInS3Bucket(_appsettings.S3Bucket, stream, $"{_appsettings.S3TempFilePath}/ComplianceReport_{Guid.NewGuid()}.xlsx");
        }

       
      private async Task<List<AllClientComplianceDto>> GetComplianceData(string clientId, int clinicId, int complianceId)
        {
            int parsedClientId = await _clientRepository.GetClientIdByUniqueKey(clientId);
            AllClientComplianceDto complianceDetails = await _clientRepository.GetComplianceDetails(parsedClientId, clinicId, complianceId);
            return new List<AllClientComplianceDto> { complianceDetails };
        }


        private IXLRow AddFirstRow(IXLWorksheet worksheet, ComplianceReportClientDetailsModel rpt)
        {
            var row = worksheet.Row(1);
            var startColumn = 1;

            var headers = new List<string> { "Name", "Sites", "Payer", "Status" };
            headers.AddRange(rpt.complianceReport.Select(cr => cr.complianceName));

            for (int i = 0; i < headers.Count; i++)
            {
                row.Cell(startColumn + i).Value = headers[i];
                row.Cell(startColumn + i).Style.Fill.BackgroundColor = _headerColor;
            }

            return row;
        }
        private async Task AddRow(IXLWorksheet worksheet, int rowIndex, ComplianceReportClientDetailsModel rpt, LogInStaff logInStaff)
        {
            var row = worksheet.Row(rowIndex);
            var startColumn = 1;

            row.Cell(startColumn).Value = $"{rpt.lName}, {rpt.fName} ({rpt.Age})";
            row.Cell(startColumn + 1).Value = rpt.siteName;
            row.Cell(startColumn + 2).Value = rpt.payerName;
            row.Cell(startColumn + 3).Value = rpt.status;
            Console.WriteLine(rpt.clientId);
            var data=await GetComplianceData(rpt.clientId,42,logInStaff.clinicId);
            Console.WriteLine(data);
            for (int i = 0; i < rpt.complianceReport.Count; i++)
            {
                var compliance = rpt.complianceReport[i];

                    // var data=await _clientService.GetComplianceDetails(rpt.clientId,compliance.complianceId,logInStaff);
                    // Console.WriteLine(data);

                var (status, color) = GetComplianceProps(compliance);
                row.Cell(startColumn + 4 + i).Value = status;
                row.Cell(startColumn + 4 + i).Style.Fill.BackgroundColor =color;
            }
        }

        private (string status, XLColor color) GetComplianceProps(ComplianceReportDetails complianceReport)
        {
            if (complianceReport.isCompleted)
            {
                if (complianceReport.isRecurring)
                {
                    return ("due date", XLColor.Green);
                }
                else
                {
                    return ("complete date", XLColor.Green);
                }
            }

            if (!complianceReport.isApplicable)
            {
                return ("NA", XLColor.Green);
            }

            if (complianceReport.isRestrictive)
            {
                return ("Pending", XLColor.Red);
            }

            return ("Pending", XLColor.Yellow);
        }
    }
}
Leave a Comment