/// <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)); }