/// <summary>Gets the value of a specific property.
/// </summary>
/// <typeparam name="TEnum">The type of the value which is assumed to be a enumeration.</typeparam>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="propertyName">The name of the property to search.</param>
/// <param name="value">The value of the property (output).</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation of the enumeration <typeparamref name="TEnum"/>.</param>
/// <param name="propertyValueColumnIndex">The null-based index of the column which contains the value, the second column is standard.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a specific enumeration.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static ExcelPropertyValueState TryGetPropertyValue <TEnum>(this IExcelDataQuery dataQuery, string propertyName, out TEnum value, EnumStringRepresentationUsage enumStringRepresentationUsage, int propertyValueColumnIndex = 1)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
int rowIndex;
if (dataQuery.TryGetRowIndexOfPropertyName(propertyName, out rowIndex) == false)
{
value = default(TEnum);
return(ExcelPropertyValueState.NoPropertyFound);
}
ExcelCellValueState state = dataQuery.TryGetValue <TEnum>(enumStringRepresentationUsage, out value, rowIndex, propertyValueColumnIndex);
if (state == ExcelCellValueState.ProperValue)
{
return(ExcelPropertyValueState.ProperProperty);
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
return(ExcelPropertyValueState.ValueIsEmptyExcelCell);
}
else
{
return(ExcelPropertyValueState.NoValidValue);
}
}
/// <summary>Gets the value of a specific property.
/// </summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="propertyName">The name of the property to search.</param>
/// <param name="value">The value of the property (output).</param>
/// <param name="dataAdvice">A data advice for a the property value, i.e. possible outcome to improve the useability.</param>
/// <param name="propertyValueColumnIndex">The null-based index of the column which contains the value.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents the type of a specific enumeration.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static ExcelPropertyValueState TryGetPropertyValue <T>(this IExcelDataQuery dataQuery, string propertyName, out T value, IExcelDataAdvice dataAdvice = null, int propertyValueColumnIndex = 1)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
int rowIndex;
if (dataQuery.TryGetRowIndexOfPropertyName(propertyName, out rowIndex) == false)
{
value = default(T);
return(ExcelPropertyValueState.NoPropertyFound);
}
ExcelCellValueState state = dataQuery.TryGetValue <T>(out value, rowIndex, propertyValueColumnIndex, dataAdvice);
if (state == ExcelCellValueState.ProperValue)
{
return(ExcelPropertyValueState.ProperProperty);
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
return(ExcelPropertyValueState.ValueIsEmptyExcelCell);
}
else
{
return(ExcelPropertyValueState.NoValidValue);
}
}
/// <summary>Gets the value of a specific pool with respect to a specific position of a <see cref="IExcelDataQuery"/> object.
/// </summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="dataQuery">The data query, i.e. property name/value pairs.</param>
/// <param name="tryGetPoolElement">A method to pick a specific object.</param>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome to improve the useability, perhaps <c>null</c>.</param>
/// <returns>The value.</returns>
/// <exception cref="ArgumentException">Thrown, if the user input is invalid.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static T GetRequiredPoolValue <T>(this IExcelDataQuery dataQuery, ExcelDataQuery.tTryGetPoolElement <T> tryGetPoolElement, int rowIndex = 0, int columnIndex = 0, IExcelDataAdvice dataAdvice = null)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
string objectName;
ExcelCellValueState state = dataQuery.TryGetValue <String>(out objectName, rowIndex, columnIndex, dataAdvice);
T value;
if (state == ExcelCellValueState.ProperValue)
{
if (tryGetPoolElement(objectName, out value) == true)
{
return(value);
}
else
{
throw new ArgumentException(dataQuery.ToString(rowIndex, columnIndex) + " is no valid name" + GetFormatedDataQueryName(dataQuery) + ".");
}
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
throw new ArgumentException("Valid pool element name required" + GetFormatedDataQueryName(dataQuery) + ".");
}
throw new ArgumentException(dataQuery.ToString(rowIndex, columnIndex) + " is no valid input" + GetFormatedDataQueryName(dataQuery) + ".");
}
/// <summary>Gets the [optional] value of a specific pool with respect to a specific position of a <see cref="IExcelDataQuery"/> object.
/// </summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="dataQuery">The data query, i.e. property name/value pairs.</param>
/// <param name="tryGetPoolElement">A method to pick a specific object.</param>
/// <param name="value">On input this is a standard value; on exist this argument will be changed if and only if <paramref name="dataQuery"/> contains valid data at the desired position.</param>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome to improve the useability, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data; or the Excel cell is empty.</returns>
/// <exception cref="ArgumentException">Thrown, if the user input is invalid, i.e. the name of the pool item is not given its <see cref="System.String"/> representation.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static bool TryGetOptionalPoolValue <T>(this IExcelDataQuery dataQuery, ExcelDataQuery.tTryGetPoolElement <T> tryGetPoolElement, ref T value, int rowIndex = 0, int columnIndex = 0, IExcelDataAdvice dataAdvice = null)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
string objectName;
ExcelCellValueState state = dataQuery.TryGetValue <String>(out objectName, rowIndex, columnIndex, dataAdvice);
if (state == ExcelCellValueState.ProperValue)
{
T tempValue;
if (tryGetPoolElement(objectName, out tempValue) == true)
{
value = tempValue;
return(true);
}
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
return(false);
}
throw new ArgumentException(dataQuery.ToString(rowIndex, columnIndex) + " is no valid input" + GetFormatedDataQueryName(dataQuery) + ".");
}
/// <summary>Gets the [optional] value of a specific Excel cell.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="value">On input this is a standard value; on exist this argument will be changed if and only if <paramref name="dataQuery"/> contains valid data at the desired position.</param>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome to improve the useability, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> has been changed and set to some user input.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents an enumeration or if the user input at the desired position can not converted to <typeparamref name="T"/>.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static bool TryGetOptionalValue <T>(this IExcelDataQuery dataQuery, ref T value, int rowIndex = 0, int columnIndex = 0, IExcelDataAdvice dataAdvice = null)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
T tempValue;
ExcelCellValueState state = dataQuery.TryGetValue <T>(out tempValue, rowIndex, columnIndex, dataAdvice);
if (state == ExcelCellValueState.NoValidValue)
{
throw new ArgumentException("No valid input '" + dataQuery.ToString(rowIndex, columnIndex) + "'" + GetFormatedDataQueryName(dataQuery) + ".");
}
else if (state == ExcelCellValueState.ProperValue)
{
value = tempValue;
return(true);
}
return(false);
}
/// <summary>Gets the value of a specific Excel cell with respect to specific row and column index.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="value">The value (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <T>(out T value, int rowIndex, int columnIndex, IExcelDataAdvice dataAdvice = null)
{
if (typeof(T).IsEnum)
{
throw new ArgumentException("The type " + typeof(T).ToString() + " represents a enumeration which is not allowed.", "T");
}
if (rowIndex == 0)
{
ExcelCellValueState state = ExcelCellValueState.NoValidValue;
if (columnIndex == 0)
{
state = TryGetValue <T>(m_PropertyName, out value, (dataAdvice != null) ? dataAdvice.AsExcelDropDownListString() : null);
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
}
else if (columnIndex == 1)
{
state = TryGetValue <T>(m_PropertyValue, out value, (dataAdvice != null) ? dataAdvice.AsExcelDropDownListString() : null);
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
}
else
{
value = default(T);
state = ExcelCellValueState.NoValidValue;
}
if (state == ExcelCellValueState.ProperValue)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
}
return(state);
}
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the [optional] value of a specific Excel cell.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="value">On input this is a standard value; on exist this argument will be changed if and only if <paramref name="dataQuery"/> contains valid data at the desired position.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A value indicating whether <paramref name="value"/> has been changed and set to some user input.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents an enumeration or if the user input at the desired position can not converted to <typeparamref name="TEnum"/>.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static bool TryGetOptionalValue <TEnum>(this IExcelDataQuery dataQuery, ref TEnum value, EnumStringRepresentationUsage enumStringRepresentationUsage, int rowIndex = 0, int columnIndex = 0)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
TEnum tempValue;
ExcelCellValueState state = dataQuery.TryGetValue <TEnum>(enumStringRepresentationUsage, out tempValue, rowIndex, columnIndex);
if (state == ExcelCellValueState.NoValidValue)
{
throw new ArgumentException("No valid input '" + dataQuery.ToString(rowIndex, columnIndex) + "'" + GetFormatedDataQueryName(dataQuery) + ".");
}
else if (state == ExcelCellValueState.ProperValue)
{
value = tempValue;
return(true);
}
return(false);
}
/// <summary>Gets values of a specific row with respect to a specific type, empty elements will be ignored.
/// </summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="startColumnIndex">The first column to take into account in its null-based index representation.</param>
/// <returns>The values of the <paramref name="dataQuery"/> in the specific row of the desired type, empty cells will be ignored.</returns>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
/// <exception cref="ArgumentException">Thrown, if <paramref name="rowIndex"/> is invalid or the column contains
/// a value which is non-empty but can not converted into <typeparamref name="T"/>.</exception>
public static T[] GetRowVector <T>(this IExcelDataQuery dataQuery, int rowIndex = 0, int startColumnIndex = 0)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
List <T> vector = new List <T>();
for (int j = startColumnIndex; j < dataQuery.ColumnCount; j++)
{
T value;
ExcelCellValueState state = dataQuery.TryGetValue <T>(out value, rowIndex, j);
if (state == ExcelCellValueState.ProperValue)
{
vector.Add(value);
}
else if (state == ExcelCellValueState.NoValidValue)
{
throw new ArgumentException("No valid input '" + dataQuery.ToString(rowIndex, j) + "' at row: " + rowIndex + ", column: " + j + " found" + GetFormatedDataQueryName(dataQuery) + ".");
}
}
return(vector.ToArray());
}
/// <summary>Gets the value of a specific property with respect to a null-based (row or column) index.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="value">The value (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <TEnum>(EnumStringRepresentationUsage enumStringRepresentationUsage, out TEnum value, int rowIndex, int columnIndex)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (typeof(TEnum).IsEnum == false)
{
throw new ArgumentException("The type " + typeof(TEnum).ToString() + " does not represents a enumeration.", "TEnum");
}
if (rowIndex == 0)
{
ExcelCellValueState state = ExcelCellValueState.NoValidValue;
if (columnIndex == 0)
{
state = TryGetValue <TEnum>(m_PropertyName, out value, enumStringRepresentationUsage);
}
else if (columnIndex == 1)
{
state = TryGetValue <TEnum>(m_PropertyValue, out value, enumStringRepresentationUsage);
}
else
{
value = default(TEnum);
state = ExcelCellValueState.NoValidValue;
}
if (state == ExcelCellValueState.ProperValue)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
}
else if (state == ExcelCellValueState.EmptyOrMissingExcelCell)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
}
return(state);
}
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
public static object GetInputPropertyValue(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName,
[ExcelArgument(Name = "propertyName", Description = "The name of the property", AllowReference = true)]
object xlPropertyName,
[ExcelArgument(Name = "propertyValueColumnIndex", Description = "[optional] The null-based column index of the column which contains the value of the property, '1' is standard", AllowReference = true)]
object xlPropertyValueColumnIndex,
[ExcelArgument(Name = "propertyTableName", Description = "[optional] The name of the table which represents the properties ('general properties' is standard)", AllowReference = true)]
object xlPropertyTableName = null)
{
try
{
if (ExcelDnaUtil.IsInFunctionWizard() == true)
{
return(String.Empty);
}
IExcelDataQuery objectNameQuery = ExcelDataQuery.Create(xlObjectName);
string objectName;
ExcelPoolItem poolItem;
if ((objectNameQuery.TryGetValue <string>(out objectName, dataAdvice: ExcelDataAdvice.Create(ExcelPool.GetObjectNames())) != ExcelCellValueState.ProperValue) || (ExcelPool.TryGetItem(objectName, out poolItem) == false))
{
throw new ArgumentException("No object with name '" + objectNameQuery.ToString(0, 0).GetRelevantSubstring() + "' available.");
}
string propertyTableName = "General properties";
IExcelDataQuery propertyTableQuery = ExcelDataQuery.Create(xlPropertyTableName);
if (propertyTableQuery.IsEmpty == false)
{
propertyTableName = propertyTableQuery.GetValue <string>(dataAdvice: ExcelDataAdvice.Create(poolItem.GetDataQueryNames()));
}
GuidedExcelDataQuery propertyDataQuery;
if (poolItem.TryGetDataQuery(propertyTableName, out propertyDataQuery) == false)
{
throw new ArgumentException("The property table name' " + propertyTableName + "' is invalid.");
}
IExcelDataQuery propertyNameQuery = ExcelDataQuery.Create(xlPropertyName);
string propertyName;
if (propertyNameQuery.TryGetValue <string>(out propertyName, dataAdvice: ExcelDataAdvice.Create(propertyNameQuery.GetColumnVector <string>())) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("The property name '" + propertyNameQuery.ToString(0, 0) + "' is invalid.");
}
/* now get the property value, but use a Excel conform output: */
int propertyRowIndex;
if (propertyDataQuery.TryGetRowIndexOfPropertyName(propertyName, out propertyRowIndex) == false)
{
throw new ArgumentException("No property with name '" + propertyName + "' found.");
}
int propertyValueColumnIndex;
ExcelCellValueState propertyValueColumnIndexState = ExcelDataQuery.Create(xlPropertyValueColumnIndex).TryGetValue <int>(out propertyValueColumnIndex);
if (propertyValueColumnIndexState == ExcelCellValueState.EmptyOrMissingExcelCell)
{
propertyValueColumnIndex = 1;
}
else if (propertyValueColumnIndexState == ExcelCellValueState.NoValidValue)
{
throw new ArgumentException("Invalid 'Property value column index', a positive integer expected.");
}
return(propertyDataQuery.GetExcelData(propertyRowIndex, propertyValueColumnIndex));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
