< Summary

Information
Class: MRA.Infrastructure.Excel.EPPlusExcelProvider
Assembly: MRA.Infrastructure
File(s): D:\a\MiguelRomerART\MiguelRomerART\MRA.Infrastructure\Excel\EPPlusExcelProvider.cs
Line coverage
4%
Covered lines: 4
Uncovered lines: 89
Coverable lines: 93
Total lines: 197
Line coverage: 4.3%
Branch coverage
0%
Covered branches: 0
Total branches: 22
Branch coverage: 0%
Method coverage

Feature is only available for sponsors

Upgrade to PRO version

Metrics

MethodBranch coverage Crap Score Cyclomatic complexity Line coverage
.ctor(...)100%11100%
GetPropertiesAttributes()0%4260%
SetTableHeaders(...)0%620%
CreateWorksheetDictionary(...)100%210%
CreateWorksheetDictionary(...)100%210%
FindColumnNumberOf(...)0%620%
FillWorksheetDictionary(...)0%620%
FillWorksheetDictionary(...)0%620%
AddDropdownColumn(...)0%2040%
CreateTable(...)100%210%
SetBold(...)100%210%
StyleCellsHeader(...)100%210%
GetColumnMapDrawing(...)0%2040%

File(s)

D:\a\MiguelRomerART\MiguelRomerART\MRA.Infrastructure\Excel\EPPlusExcelProvider.cs

#LineLine coverage
 1using Microsoft.Extensions.Logging;
 2using OfficeOpenXml;
 3using OfficeOpenXml.Style;
 4using OfficeOpenXml.Table;
 5using MRA.Infrastructure.Excel.Attributes;
 6using System.Drawing;
 7using MRA.Infrastructure.Exceptions.Excel;
 8using MRA.Infrastructure.Enums;
 9
 10namespace MRA.Infrastructure.Excel;
 11
 12public class EPPlusExcelProvider : IExcelProvider
 13{
 14    private const string DICTIONARY_COLUMN_INDEX = "Index";
 15    private const string DICTIONARY_COLUMN_NAME = "Name";
 16
 17    private readonly ILogger<EPPlusExcelProvider> _logger;
 18
 19
 120    public EPPlusExcelProvider(ILogger<EPPlusExcelProvider> logger)
 21    {
 122        _logger = logger;
 123        ExcelPackage.License.SetNonCommercialPersonal("MiguelRomeral");
 124    }
 25
 26    public List<ExcelColumnInfo> GetPropertiesAttributes<T>()
 27    {
 028        _logger.LogTrace("Reading properties of type {Type}.", typeof(T));
 029        return typeof(T)
 030            .GetProperties()
 031            .Where(p => Attribute.IsDefined(p, typeof(ExcelColumnAttribute)))
 032            .Select(p => new ExcelColumnInfo()
 033            {
 034                Property = p,
 035                Attribute = (ExcelColumnAttribute)Attribute.GetCustomAttribute(p, typeof(ExcelColumnAttribute))
 036            })
 037            .OrderBy(p => p.Attribute.Order)
 038            .ToList();
 39    }
 40
 41    public void SetTableHeaders(ref ExcelWorksheet workSheet, List<ExcelColumnInfo> properties)
 42    {
 043        int col = 1;
 044        foreach (var prop in properties)
 45        {
 046            workSheet.Cells[1, col].Value = prop.Attribute.Name;
 047            col++;
 48        }
 049    }
 50
 51    public void CreateWorksheetDictionary(ExcelPackage excel, string sheetName, string tableName, Dictionary<int, string
 52        List<ExcelColumnInfo> properties, ExcelWorksheet mainSheet, string nameColumnDropdown, string nameColumnIndex)
 53    {
 054        var wsTypes = FillWorksheetDictionary(excel, name: sheetName, tableName: tableName, dictionary);
 55
 056        AddDropdownColumn(mainSheet, wsTypes, tableName,
 057            dataRowStart: 2,
 058            dropdownColumn: FindColumnNumberOf(properties, nameColumnDropdown),
 059            indexColumn: FindColumnNumberOf(properties, nameColumnIndex));
 060    }
 61
 62    public void CreateWorksheetDictionary<TEnum>(ExcelPackage excel, string sheetName, string tableName, List<ExcelColum
 63    {
 064        var wsTypes = FillWorksheetDictionary<TEnum>(excel, name: sheetName, tableName: tableName);
 65
 066        AddDropdownColumn(mainSheet, wsTypes, tableName,
 067            dataRowStart: 2,
 068            dropdownColumn: FindColumnNumberOf(properties, nameColumnDropdown),
 069            indexColumn: FindColumnNumberOf(properties, nameColumnIndex));
 070    }
 71
 72    public int FindColumnNumberOf(List<ExcelColumnInfo> properties, string name)
 73    {
 074        var index = properties.FindIndex(x => x.Attribute.Name.Equals(name)) + 1;
 075        if (index < 0) throw new ExcelColumnNotFoundException($"Column with name \"{name}\" was not found");
 076        return index;
 77    }
 78
 79    public ExcelWorksheet FillWorksheetDictionary(ExcelPackage excel, string name, string tableName, Dictionary<int, str
 80    {
 081        var worksheet = excel.Workbook.Worksheets.Add(name);
 82
 083        worksheet.Cells[1, 1].Value = DICTIONARY_COLUMN_NAME;
 084        worksheet.Cells[1, 2].Value = DICTIONARY_COLUMN_INDEX;
 85
 086        int row = 1;
 087        foreach (var item in dictionary)
 88        {
 089            row++;
 090            worksheet.Cells[row, 1].Value = item.Value;
 091            worksheet.Cells[row, 2].Value = item.Key;
 092            worksheet.Cells[row, 2].Style.Numberformat.Format = "#,##0";
 93        }
 94
 095        CreateTable(ref worksheet, tableName, 1, 1, row, 2);
 096        worksheet.Column(1).Width = 60;
 097        return worksheet;
 98    }
 99
 100    public ExcelWorksheet FillWorksheetDictionary<TEnum>(ExcelPackage excel, string name, string tableName) where TEnum 
 101    {
 0102        var worksheet = excel.Workbook.Worksheets.Add(name);
 103
 0104        worksheet.Cells[1, 1].Value = DICTIONARY_COLUMN_NAME;
 0105        worksheet.Cells[1, 2].Value = DICTIONARY_COLUMN_INDEX;
 106
 0107        int row = 1;
 108
 109        // Iterar sobre los valores del enum
 0110        foreach (TEnum item in Enum.GetValues(typeof(TEnum)))
 111        {
 0112            row++;
 0113            worksheet.Cells[row, 1].Value = item.GetDescription();
 0114            worksheet.Cells[row, 2].Value = Convert.ToInt32(item);
 0115            worksheet.Cells[row, 2].Style.Numberformat.Format = "#,##0";
 116        }
 117
 0118        CreateTable(ref worksheet, tableName, 1, 1, row, 2);
 0119        worksheet.Column(1).Width = 60;
 0120        return worksheet;
 121    }
 122
 123    private void AddDropdownColumn(ExcelWorksheet mainSheet, ExcelWorksheet dictionarySheet, string tableName, int dataR
 124    {
 125        // Obtener la tabla desde el worksheet del diccionario
 0126        var dictionaryTable = dictionarySheet.Tables[tableName];
 0127        if (dictionaryTable == null)
 0128            throw new ExcelTableNotFoundException($"La tabla '{tableName}' no fue encontrada en la hoja '{dictionaryShee
 129
 130        // Definir el nombre de rango dinámico para la columna "Name" en la tabla del diccionario
 0131        string dynamicRangeName = $"{tableName}_NameRange";
 132
 133        // Obtener la columna de "Name" en la tabla de diccionario
 0134        int nameColumnIndex = dictionaryTable.Columns[DICTIONARY_COLUMN_NAME].Position + dictionaryTable.Address.Start.C
 135
 136        // Crear el rango dinámico usando un rango estructurado
 0137        dictionarySheet.Workbook.Names.Add(dynamicRangeName, dictionarySheet.Cells[dictionaryTable.Address.Start.Row + 1
 138
 139        // Agregar validación de lista en cada celda de la columna de dropdown en la hoja principal usando el nombre del
 0140        for (int row = dataRowStart; row <= mainSheet.Dimension.End.Row; row++)
 141        {
 0142            var validation = mainSheet.DataValidations.AddListValidation(mainSheet.Cells[row, dropdownColumn].Address);
 0143            validation.Formula.ExcelFormula = dynamicRangeName;
 144
 145            // Obtener la dirección de la celda del dropdown para referencia en VLOOKUP
 0146            string dropdownCellAddress = mainSheet.Cells[row, dropdownColumn].Address;
 147
 148            // Asignar la fórmula VLOOKUP en cada celda de indexColumn
 0149            mainSheet.Cells[row, indexColumn].Formula = $"VLOOKUP({dropdownCellAddress}, '{dictionarySheet.Name}'!A:B, 2
 150        }
 0151    }
 152
 153
 154    public void CreateTable(ref ExcelWorksheet workSheet, string name, int beginRow, int beginColumn, int endRow, int en
 155    {
 0156        var dataRange = workSheet.Cells[beginRow, beginColumn, endRow, endColumn];
 0157        var table = workSheet.Tables.Add(dataRange, name);
 0158        table.TableStyle = TableStyles.Light6;
 0159        StyleCellsHeader(ref workSheet, 1, 1, 1, endColumn);
 0160    }
 161
 162    public void SetBold(ref ExcelWorksheet workSheet, int beginRow, int beginColumn, int endRow, int endColumn)
 163    {
 0164        using (var range = workSheet.Cells[beginRow, beginColumn, endRow, endColumn])
 165        {
 0166            range.Style.Font.Bold = true;
 0167        }
 0168    }
 169
 170    public void StyleCellsHeader(ref ExcelWorksheet workSheet, int beginRow, int beginColumn, int endRow, int endColumn)
 171    {
 0172        using (var range = workSheet.Cells[beginRow, beginColumn, endRow, endColumn])
 173        {
 0174            range.Style.Fill.PatternType = ExcelFillStyle.Solid;
 0175            range.Style.Fill.BackgroundColor.SetColor(Color.RebeccaPurple);
 0176            range.Style.Font.Color.SetColor(Color.White);
 0177            range.Style.Font.Bold = true;
 0178        }
 0179    }
 180
 181    public Dictionary<string, int> GetColumnMapDrawing(ExcelWorksheet workSheet)
 182    {
 183        // Construir un mapeo entre los nombres de encabezado y la posición de columna
 0184        var nameToColumnMap = new Dictionary<string, int>();
 0185        for (int col = 1; col <= workSheet.Dimension.End.Column; col++)
 186        {
 0187            var headerValue = workSheet.Cells[1, col].Text;
 0188            if (!string.IsNullOrWhiteSpace(headerValue))
 189            {
 0190                nameToColumnMap[headerValue] = col;
 191            }
 192        }
 193
 0194        return nameToColumnMap;
 195    }
 196
 197}
Generated by: ReportGenerator 5.4.8.0
7/13/2025 - 2:43:35 PM
GitHub | reportgenerator.io

Methods/Properties

.ctor(Microsoft.Extensions.Logging.ILogger`1<MRA.Infrastructure.Excel.EPPlusExcelProvider>)
GetPropertiesAttributes()
SetTableHeaders(OfficeOpenXml.ExcelWorksheet&,System.Collections.Generic.List`1<MRA.Infrastructure.Excel.Attributes.ExcelColumnInfo>)
CreateWorksheetDictionary(OfficeOpenXml.ExcelPackage,System.String,System.String,System.Collections.Generic.Dictionary`2<System.Int32,System.String>,System.Collections.Generic.List`1<MRA.Infrastructure.Excel.Attributes.ExcelColumnInfo>,OfficeOpenXml.ExcelWorksheet,System.String,System.String)
CreateWorksheetDictionary(OfficeOpenXml.ExcelPackage,System.String,System.String,System.Collections.Generic.List`1<MRA.Infrastructure.Excel.Attributes.ExcelColumnInfo>,OfficeOpenXml.ExcelWorksheet,System.String,System.String)
FindColumnNumberOf(System.Collections.Generic.List`1<MRA.Infrastructure.Excel.Attributes.ExcelColumnInfo>,System.String)
FillWorksheetDictionary(OfficeOpenXml.ExcelPackage,System.String,System.String,System.Collections.Generic.Dictionary`2<System.Int32,System.String>)
FillWorksheetDictionary(OfficeOpenXml.ExcelPackage,System.String,System.String)
AddDropdownColumn(OfficeOpenXml.ExcelWorksheet,OfficeOpenXml.ExcelWorksheet,System.String,System.Int32,System.Int32,System.Int32)
CreateTable(OfficeOpenXml.ExcelWorksheet&,System.String,System.Int32,System.Int32,System.Int32,System.Int32)
SetBold(OfficeOpenXml.ExcelWorksheet&,System.Int32,System.Int32,System.Int32,System.Int32)
StyleCellsHeader(OfficeOpenXml.ExcelWorksheet&,System.Int32,System.Int32,System.Int32,System.Int32)
GetColumnMapDrawing(OfficeOpenXml.ExcelWorksheet)