public static object GetFromExcel <T>(ExcelWorksheet worksheet, string var, int?row)
{
if (worksheet == null)
{
return(null);
}
int? col;
object value = null;
var type = typeof(T);
if (string.IsNullOrWhiteSpace(var))
{
return(null);
}
object val;
if (row.HasValue)
{
col = worksheet.GetColumnByName(var);
if (col == null)
{
return(null);
}
val = worksheet.Cells[row.Value, col.Value]?.Value;
if (val == null)
{
return(null);
}
}
else
{
val = worksheet.Cells[var]?.Value;
}
if (type == typeof(int) || type == typeof(int?))
{
if (val != null)
{
var num = Regex.Replace(Regex.Replace(val.ToString(), @"(-.*|\.+)$", ""), @"[^0-9]", "");
if (!string.IsNullOrWhiteSpace(num))
{
value = Convert.ToInt32(num, CultureInfo.InvariantCulture);
}
}
}
else if (type == typeof(double) || type == typeof(double?))
{
if (val != null)
{
var num = Regex.Replace(Regex.Replace(val.ToString(), @"(?<=^.+)(-.*|\.+)$", ""), @"[^0-9\.,-]", "");
if (!string.IsNullOrWhiteSpace(num))
{
var culture = CultureInfo.CreateSpecificCulture(num.Contains(',', StringComparison.InvariantCultureIgnoreCase) ? "es-CL" : "en-GB");
value = Convert.ToDouble(num, culture);
}
}
}
else if (type == typeof(string))
{
value = val != null?
val.ToString() : "";
}
else if (type == typeof(bool))
{
value = val != null &&
val.ToString() == "Si" || val.ToString() == "Yes";
}
else if (type == typeof(DateTime))
{
var sDate = val.ToString();
var formats = new string[] { "yyyyMMdd", "yyyy-MM-dd", "dd-MM-yyyy HH:mm", "dd-MM-yyyy", "dd-MM-yyyy HH:mm' '" };
DateTime.TryParseExact(sDate, formats, new CultureInfo("es-CL"), DateTimeStyles.None, out DateTime date);
//DateTime.TryParse(sDate, out DateTime date);
value = date;
}
else if (type == typeof(ProductionType?) || type == typeof(ProductionType))
{
foreach (var tipo in Enum.GetNames(typeof(ProductionType)))
{
if (val.ToString().ToUpperInvariant()
.Contains(tipo.ToString(CultureInfo.InvariantCulture)
.ToUpperInvariant(),
StringComparison.InvariantCultureIgnoreCase))
{
var parsed = Enum.TryParse(tipo, out ProductionType production);
if (parsed)
{
value = production;
break;
}
}
}
if (value == null)
{
value = ProductionType.Desconocido;
}
}
else if (type == typeof(Item?) || type == typeof(Item))
{
foreach (var tipo in Enum.GetNames(typeof(Item)))
{
if (val.ToString().ToUpperInvariant()[0] ==
tipo.ToString(CultureInfo.InvariantCulture).ToUpperInvariant()[0])
{
var parsed = Enum.TryParse(tipo, out Item production);
if (parsed)
{
value = production;
break;
}
}
}
}
else if (type == typeof(Tipo))
{
value = val.ToString().ToUpperInvariant()
.Contains("M", StringComparison.InvariantCultureIgnoreCase) ? Tipo.MateriaPrima : Tipo.Producción;
}
else if (type == typeof(Enum))
{
value = Enum.Parse(DataTableExtensions
.GetEnumType("BiblioMit.Models." + typeof(T).ToString()), val.ToString());
}
if (value is T)
{
return((T)value);
}
else
{
return(null);
}
}