Maestria.TypeProviders

RSCG – Maestria.TypeProviders    

Generating strong typed code from Excel.

This is how you can use Maestria.TypeProviders .

The code that you start with is

<project sdk="Microsoft.NET.Sdk">

  <propertygroup>
    <outputtype>Exe</outputtype>
    <targetframework>net10.0</targetframework>
    <implicitusings>enable</implicitusings>
    <nullable>enable</nullable>
  </propertygroup>

  <itemgroup>
    <packagereference version="0.105.0" include="ClosedXML">
    <packagereference version="3.7.2.0" include="Maestria.Extensions">
    <packagereference version="1.3.1" include="Maestria.TypeProviders">
      <privateassets>all</privateassets>
      <includeassets>runtime; build; native; contentfiles; analyzers; buildtransitive</includeassets>
    </packagereference>
  </packagereference>

  <itemgroup>
    <none update="MyExcel.xlsx">
      <copytooutputdirectory>PreserveNewest</copytooutputdirectory>
    </none>
  </itemgroup>

	<propertygroup>
		<emitcompilergeneratedfiles>true</emitcompilergeneratedfiles>
		<compilergeneratedfilesoutputpath>$(BaseIntermediateOutputPath)\GX</compilergeneratedfilesoutputpath>
	</propertygroup>
</packagereference>

The code that you will use is

using DemoExcel;

Console.WriteLine("Hello, World!");
var persons = MyExcelPersonFactory.Load("MyExcel.xlsx").ToArray();

foreach (var person in persons)
{
    Console.WriteLine(person.ID + person.FullName());
    
}

namespace DemoExcel;

public partial class MyExcelPerson
{
    public string FullName() => $"{FirstName} {LastName}";
}

using System;
using System.Collections.Generic;
using System.Text;

namespace DemoExcel;

[ExcelProvider(TemplatePath = @"MyExcel.xlsx")]
public partial class MyExcelPerson
{
}

   The code that is generated is

//----------------------
// <auto-generated>
//     Generated using Maestria.TypeProviders (https://github.com/MaestriaNet/TypeProviders)
// </auto-generated>
//----------------------

using System;
using System.Collections;
using System.Collections.Generic;
using System.Linq;
using System.IO;
using ClosedXML.Excel;
using Maestria.TypeProviders.Excel;

namespace DemoExcel
{
    public partial class MyExcelPerson
    {
        public int ID { get; set; }
        public string FirstName { get; set; }
        public string LastName { get; set; }
    }

    public static partial class MyExcelPersonFactory
    {
        public static IEnumerable<myexcelperson> Load(Stream input, int sheetPosition = 1)
        {
            using var workbook = new XLWorkbook(input);
            return Load(workbook, sheetPosition, null);
        }

        public static IEnumerable<myexcelperson> Load(Stream input, string sheetName)
        {
            using var workbook = new XLWorkbook(input);
            return Load(workbook, 0, sheetName);
        }

        public static IEnumerable<myexcelperson> Load(string filePath, int sheetPosition = 1)
        {
            using var workbook = ExcelExtensions.OpenWorkbook(filePath);
            return Load(workbook, sheetPosition, null);
        }

        public static IEnumerable<myexcelperson> Load(string filePath, string sheetName)
        {
            using var workbook = ExcelExtensions.OpenWorkbook(filePath);
            return Load(workbook, 0, sheetName);
        }

        public static IEnumerable<myexcelperson> Load(XLWorkbook workbook, int sheetPosition = 1, string sheetName = "")
        {
            var result = new List<myexcelperson>();
            var sheet = string.IsNullOrEmpty(sheetName) ? workbook.Worksheet(sheetPosition) : workbook.Worksheet(sheetName);
            var indexOfID = sheet.ColumnByName(@"ID");
            var indexOfFirstName = sheet.ColumnByName(@"FirstName");
            var indexOfLastName = sheet.ColumnByName(@"LastName");
            foreach (var row in sheet.Rows(2, sheet.RowUsedCount()))
            {
                var iDValue = row.Cell(indexOfID);
                var firstNameValue = row.Cell(indexOfFirstName);
                var lastNameValue = row.Cell(indexOfLastName);
                result.Add(new MyExcelPerson
                {
                    ID = iDValue.GetValue<int>(),
                    FirstName = firstNameValue.GetValue<string>(),
                    LastName = lastNameValue.GetValue<string>(),
                });
            }
            return result;
        }
    }
}

//----------------------
// <auto-generated>
//     Generated using Maestria.TypeProviders (https://github.com/MaestriaNet/TypeProviders)
// </auto-generated>
//----------------------

using System;
using System.IO;
using ClosedXML.Excel;

namespace Maestria.TypeProviders.Excel;

public static class ExcelExtensions
{
    /// <summary>
    /// Number of last user column
    /// </summary>
    /// <param name="sheet">
    /// <returns></returns>
    public static int ColumnUsedCount(this IXLWorksheet sheet) =&gt; sheet.LastColumnUsed().ColumnNumber();

    /// <summary>
    /// Number of last used row
    /// </summary>
    /// <param name="sheet">
    /// <returns></returns>
    public static int RowUsedCount(this IXLWorksheet sheet) =&gt; sheet.LastRowUsed().RowNumber();

    /// <summary>
    /// Retrieve column position by name in first row.
    /// </summary>
    /// <param name="sheet">Page to find column
    /// <param name="columnName">Name to find column in first row
    /// <returns>Position of column name or 0 when not found</returns>
    /// <exception cref="ArgumentNullException">When <paramref name="columnName"> is null o whitespace</paramref>
    public static int ColumnByName(this IXLWorksheet sheet, string columnName)
    {
        if (string.IsNullOrWhiteSpace(columnName)) throw new ExcelArgumentNullException(nameof(columnName), "Enter the column name to get position.");
        if (sheet.RowCount() &lt;= 0)
            return 0;
        for (var i = 1; i &lt;= sheet.ColumnUsedCount(); i++)
            if (sheet.Row(1).Cell(i).Value.ToString()?.ToUpper() == columnName.ToUpper()) return i;
        return 0;
    }

    /// <summary>
    /// Open file with treatment for file in use by another process
    /// </summary>
    /// <param name="filePath">
    /// <returns></returns>
    public static FileStream OpenFile(string filePath)
    {
        try
        {
            return new FileStream(filePath, FileMode.Open, FileAccess.Read);
        }
        catch
        {
            // Need more access permissions to the file already in use by the operating system
            return new FileStream(filePath, FileMode.Open, FileAccess.Read, FileShare.ReadWrite);
        }
    }

    /// <summary>
    /// Open file with treatment for file in use by another process and return XLWorkbook
    /// </summary>
    /// <param name="filePath">
    /// <returns></returns>
    public static XLWorkbook OpenWorkbook(string filePath)
    {
        using var fileStream = OpenFile(filePath);
        return new XLWorkbook(fileStream);
    }
}

using System;

namespace Maestria.TypeProviders.Excel;

internal class ExcelGeneratorException : Exception
{
    public ExcelGeneratorException()
    {
    }

    public ExcelGeneratorException(string message) : base(message)
    {
    }

    public ExcelGeneratorException(string message, Exception innerException) : base(message, innerException)
    {
    }
}

internal class ExcelArgumentOutOfRangeException : ArgumentOutOfRangeException
{
    public ExcelArgumentOutOfRangeException()
    {
    }
    
    public ExcelArgumentOutOfRangeException(string paramName) : base(paramName)
    {
    }

    public ExcelArgumentOutOfRangeException(string message, Exception innerException) : base(message, innerException)
    {
    }

    public ExcelArgumentOutOfRangeException(string paramName, object actualValue, string message) : base(paramName, actualValue, message)
    {
    }

    public ExcelArgumentOutOfRangeException(string paramName, string message) : base(paramName, message)
    {
    }
}

internal class ExcelArgumentNullException : ArgumentNullException
{
    public ExcelArgumentNullException()
    {
    }

    public ExcelArgumentNullException(string paramName) : base(paramName)
    {
    }

    public ExcelArgumentNullException(string message, Exception innerException) : base(message, innerException)
    {
    }

    public ExcelArgumentNullException(string paramName, string message) : base(paramName, message)
    {
    }
}

//----------------------
// <auto-generated>
//     Generated using Maestria.TypeProviders (https://github.com/MaestriaNet/TypeProviders)
// </auto-generated>
//----------------------

using System;

/// <summary>
/// Map excel file and auto generate DTO and Factory classes
/// </summary>
[AttributeUsage(AttributeTargets.Class)]
public class ExcelProviderAttribute : Attribute
{
    public const string TypeFullName = "ExcelProviderAttribute";

    /// <summary>
    ///     <para>
    ///         File path to load Excel template and generate source code.
    ///     </para>
    ///     <para>
    ///         It's supported relative path with format "..\..\folder\file.xlsx".
    ///         The relative path is based at the source code file location.
    ///     </para>
    /// </summary>
    public string TemplatePath { get; set; }

    /// <summary>
    /// Sheet position to build classes. Stated from 1.
    /// </summary>
    public int SheetPosition { get; set; } = 1;

    /// <summary>
    /// Sheet name to build classes.
    /// </summary>
    public string SheetName { get; set; }
}

Code and pdf at

https://ignatandrei.github.io/RSCG_Examples/v2/docs/Maestria.TypeProviders