/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
if (excelValue is string text)
{
var numberStyle = options.NumberStyle ?? NumberStyles.Float;
if (decimal.TryParse(text, numberStyle, options.CultureInfo, out var d))
{
return(d);
}
}
try {
return(Convert.ToDecimal(excelValue));
} catch (Exception e) {
throw new ExcelTypeConverterException(ValueIsNotANumber, e);
}
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
if (excelValue is string text)
{
var numberStyle = options.NumberStyle ?? NumberStyles.Integer;
if (sbyte.TryParse(text, numberStyle, options.CultureInfo, out var sb))
{
return(sb);
}
}
try {
return(Convert.ToSByte(excelValue));
} catch (Exception e) {
throw new ExcelTypeConverterException(ValueIsNotANumber, e);
}
}
/// <summary>
/// Get the <see cref="TypeConverterOptions"/> for the given <see cref="Type"/>.
/// </summary>
/// <param name="type">The type the options are for.</param>
/// <param name="defaultCulture">Default culture to use if not specified</param>
/// <returns>The options for the given type.</returns>
public static TypeConverterOptions GetOptions(
Type type,
CultureInfo defaultCulture)
{
if (type == null)
{
throw new ArgumentNullException();
}
lock (_locker) {
if (!_typeConverterOptions.TryGetValue(type, out var options))
{
options = new TypeConverterOptions();
}
if (options.CultureInfo == null)
{
options.CultureInfo = defaultCulture;
}
return(options);
}
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
var text = excelValue as string;
if (text != null)
{
var numberStyle = options.NumberStyle ?? NumberStyles.Integer;
uint ui;
if (uint.TryParse(text, numberStyle, options.CultureInfo, out ui))
{
return(ui);
}
}
try {
return(Convert.ToUInt32(excelValue));
} catch (Exception e) {
throw new ExcelTypeConverterException(InvalidConversionMessage, e);
}
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
var text = excelValue as string;
if (text != null)
{
var numberStyle = options.NumberStyle ?? NumberStyles.Float;
double d;
if (double.TryParse(text, numberStyle, options.CultureInfo, out d))
{
return(d);
}
}
try {
return(Convert.ToDouble(excelValue));
} catch (Exception e) {
throw new ExcelTypeConverterException(InvalidConversionMessage, e);
}
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
if (excelValue != null)
{
// Excel stores dates natively as doubles in the OLE Automation format for older formats
// but it also comes in as native DateTime values when using XLSX files.
if (excelValue.GetType() == typeof(double))
{
return(DateTime.FromOADate((double)excelValue));
}
if (excelValue.GetType() == typeof(DateTime))
{
return(excelValue);
}
// Try to parse the date as a string if it comes in that way
var text = excelValue as string;
if (text != null)
{
if (string.IsNullOrWhiteSpace(text))
{
return(DateTime.MinValue);
}
var formatProvider = (IFormatProvider)options.CultureInfo.GetFormat(typeof(DateTimeFormatInfo)) ?? options.CultureInfo;
var dateTimeStyle = options.DateTimeStyle ?? DateTimeStyles.None;
return(DateTime.Parse(text, formatProvider, dateTimeStyle));
}
// Fail if we cannot parse the value
return(base.ConvertFromExcel(options, excelValue));
}
// Return DateTime.MinValue if the entry is null
return(DateTime.MinValue);
}
/// <summary>
/// Converts the object to an Excel value. This is not called if Excel supports the type natively.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="value">The object to convert to an Excel value.</param>
/// <returns>The Excel value representation of the object.</returns>
public override object ConvertToExcel(
TypeConverterOptions options,
object value)
{
if ((bool)value)
{
var result = options.BooleanTrueValues.First();
if (result == "1")
{
return(1);
}
return(result);
}
else
{
var result = options.BooleanFalseValues.First();
if (result == "0")
{
return(0);
}
return(result);
}
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public virtual string ExcelFormatString(
TypeConverterOptions options)
{
if (AcceptsNativeType)
{
if (options.NumberFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.NumberFormat, _convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
else if (options.DateFormat != null)
{
var format = XLStyle.FormatDotNetToXL(options.DateFormat, _convertedType, options.CultureInfo);
if (!string.IsNullOrEmpty(format))
{
return(format);
}
}
}
return(null);
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
// Return the value directly if it is already a boolean
if (excelValue != null)
{
if (excelValue.GetType() == typeof(bool))
{
return(excelValue);
}
if (excelValue.GetType() == typeof(double))
{
return((double)excelValue != 0.0);
}
if (excelValue is string text)
{
// Try parsing the strings true/false
if (bool.TryParse(text, out var b))
{
return(b);
}
// Try parsing as 0 or 1
if (short.TryParse(text, out var sh))
{
if (sh == 0)
{
return(false);
}
if (sh == 1)
{
return(true);
}
}
// Try parsing true values from the options (usually yes/y)
var t = (text ?? string.Empty).Trim();
foreach (var trueValue in options.BooleanTrueValues)
{
if (options.CultureInfo.CompareInfo.Compare(trueValue, t, CompareOptions.IgnoreCase) == 0)
{
return(true);
}
}
// Try parsing false values from the options (usually no/n)
foreach (var falseValue in options.BooleanFalseValues)
{
if (options.CultureInfo.CompareInfo.Compare(falseValue, t, CompareOptions.IgnoreCase) == 0)
{
return(false);
}
}
}
}
else if (options.BooleanFalseValues.Contains(""))
{
// If we support blank to mean false, also convert null to false as well
return(false);
}
return(base.ConvertFromExcel(options, excelValue));
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public override string ExcelFormatString(
TypeConverterOptions options)
{
return(UnderlyingTypeConverter.ExcelFormatString(options));
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public virtual object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
throw new ExcelTypeConverterException(InvalidConversionMessage);
}
/// <summary>
/// Converts the object to an Excel value. This is not called if Excel supports the type natively.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="value">The object to convert to an Excel value.</param>
/// <returns>The Excel value representation of the object.</returns>
public virtual object ConvertToExcel(
TypeConverterOptions options,
object value)
{
return(value?.ToString());
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public override string ExcelFormatString(
TypeConverterOptions options)
{
return(ExcelFormatting.DateTimeFormatString(options));
}
/// <summary>
/// Converts an Excel value to an object.
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <param name="excelValue">The Excel value to convert to an object.</param>
/// <returns>The object created from the Excel value.</returns>
public override object ConvertFromExcel(
TypeConverterOptions options,
object excelValue)
{
throw new ExcelTypeConverterException(Message);
}
/// <summary>
/// Return the Excel type formatting string for the current options (null if not defined)
/// </summary>
/// <param name="options">The options to use when converting.</param>
/// <returns>The Excel formatting string for the object, null to use default formatting.</returns>
public override string ExcelFormatString(
TypeConverterOptions options)
{
// Always use the general date format for storing dates in Excel, so they don't look like doubles to the user
return(XLStyle.FormatDotNetToXL(options.DateFormat ?? "G", typeof(DateTime), options.CultureInfo));
}
