DamageAssessment_Backend/DamageAssesmentApi/DamageAssesment.Api.Responses/Providers/ExcelExportService.cs

using ClosedXML.Excel;
using DamageAssesment.Api.Responses.Interfaces;
using DamageAssesment.Api.Responses.Models;
using OfficeOpenXml;
using System.Collections.Generic;

namespace DamageAssesment.Api.Responses.Providers
{
    public class ExcelExportService: IExcelExportService
    {
        public byte[] ExportToExcel<T1>(List<object> responses)
        { 
            ExcelPackage.LicenseContext = LicenseContext.NonCommercial;
            using (var package = new ExcelPackage())
            {
                // Create the first worksheet and populate it with responses
                var workSheet1 = package.Workbook.Worksheets.Add("responses");
                PopulateWorksheet(workSheet1, responses);
                return package.GetAsByteArray();
            }
        }
        private void PopulateWorksheet(ExcelWorksheet worksheet, List<object> data)
        {
            if (data.Count > 0)
            {
                var properties = data[0].GetType().GetProperties();
                List<int> IsAttchments = new List<int>();
                // Add column headers
                for (int col = 1; col <= properties.Length; col++)
                {
                    worksheet.Cells[1, col].Value = properties[col - 1].Name;
                    if(properties[col - 1].Name.ToLower().Contains("attachment"))
                        IsAttchments.Add(col);
                }

                // Add data
                for (int row = 2; row <= data.Count + 1; row++)
                {
                    for (int col = 1; col <= properties.Length; col++)
                    {
                       
                            string value = Convert.ToString(properties[col - 1].GetValue(data[row - 2]));
                        if (IsAttchments.Where(a => a == col).Count() > 0 && !string.IsNullOrEmpty(value))
                        {
                            List<string> attachments = value.Split("##").ToList();
                            try
                            {
                                Uri linkUri = new Uri(attachments[1]);
                                worksheet.Cells[row, col].Value = attachments[0];
                                worksheet.Cells[row, col].Style.Font.UnderLine = true;
                            }
                            catch { worksheet.Cells[row, col].Value = attachments[1]; }
                        }
                        else
                            worksheet.Cells[row, col].Value = value;
                        
                    }
                }
            }
        }
    }
}