DD.WellWorkover.Cloud/AsbCloudInfrastructure/XLExtentions.cs

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

namespace AsbCloudInfrastructure;

public static class XLExtentions
{
   public const string ProblemDetailsTemplate = "Лист: {0}, Строка: {1}, Столбец: {2}. {3}";
   public const string NotFoundSheetTemplate = "Книга excel не содержит листа {0}";
   public const string InvalidValueTemplate = "Лист: {0}, Строка: {1}, Столбец: {2}. Содержит некорректное значение";
   
   public static IXLWorksheet GetWorksheet(this IXLWorkbook workbook, string sheetName) =>
      workbook.Worksheets.FirstOrDefault(ws => string.Equals(ws.Name.Trim(), sheetName.Trim(), StringComparison.CurrentCultureIgnoreCase))
      ?? throw new FileFormatException(string.Format(NotFoundSheetTemplate, sheetName));

    public static IXLCell SetCellValue<T>(this IXLCell cell, T? value, string? format = null)
    {
	    if (value == null)
		    return cell;

	    if (value is DateTime || value is DateTimeOffset)
	    {
		    cell.Style.DateFormat.Format = format ?? "DD.MM.YYYY HH:MM:SS";

		    if (value is DateTimeOffset dateTimeOffset)
		    {
			    cell.Value = XLCellValue.FromObject(dateTimeOffset.DateTime);
			    return cell;
		    }
	    }

	    cell.Value = XLCellValue.FromObject(value);

	    return cell;
    }

    public static IXLCell SetHyperlink(this IXLCell cell, string link)
    {
        cell.SetHyperlink(new XLHyperlink(link));

        return cell;
    }

    public static T? GetCellValue<T>(this IXLCell cell)
    {
        try
        {
            if (cell.IsEmpty() && default(T) == null)
                return default;

         return cell.GetValue<T>();
      }
      catch
      {
         var message = string.Format(InvalidValueTemplate, cell.Worksheet.Name, cell.Address.RowNumber, cell.Address.ColumnNumber);
         throw new FileFormatException(message);
      }
   }
}