/// <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 name of the file.
/// </summary>
/// <param name="xlPath">The path.</param>
/// <param name="xlFileName">The name of the file (without path).</param>
/// <param name="fileExtensionSearchPattern">A collection of possible file extensions.</param>
/// <returns>The file name including path.</returns>
private static string GetFileName(object xlPath, object xlFileName, IEnumerable <IdentifierString> fileExtensionSearchPattern)
{
IExcelDataQuery pathDataQuery = ExcelDataQuery.Create(xlPath);
IExcelDataQuery fileNameDataQuery = ExcelDataQuery.Create(xlFileName);
string path;
if (pathDataQuery.TryGetValue <string>(out path) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("No valid path!");
}
List <string> possibleFileNameList = new List <string>();
foreach (var fileExtension in fileExtensionSearchPattern)
{
string[] possibleFileNames = Directory.GetFiles(path, "*." + fileExtension.String, SearchOption.TopDirectoryOnly);
if ((possibleFileNames != null) && (possibleFileNames.Length > 0))
{
possibleFileNameList.AddRange(possibleFileNames);
}
}
string fileName;
if (fileNameDataQuery.TryGetValue <string>(out fileName, dataAdvice: ExcelDataAdvice.Create(possibleFileNameList, possibleFileName => Path.GetFileName(possibleFileName))) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("No valid file name!");
}
return(Path.Combine(path, fileName));
}
public static object TryEvaluateUDF(
[ExcelArgument(Name = "dodoni UDF result", Description = "The Excel cell to check whether the result of a Dodoni.net UDF is meaningful")]
object xlDodoniUDFResult)
{
IExcelDataQuery excelDataQuery = ExcelDataQuery.Create(xlDodoniUDFResult);
string value;
if (excelDataQuery.TryGetValue <string>(out value) == ExcelCellValueState.ProperValue)
{
if (value.StartsWith("Error!", true, CultureInfo.InvariantCulture) == true) // here, we just check whether some Error message starts with "Error!"
{
if (excelDataQuery.IsSingleCell == true)
{
return(String.Empty);
}
object[,] emptyOutput = new object[excelDataQuery.RowCount, excelDataQuery.ColumnCount];
for (int j = 0; j < excelDataQuery.RowCount; j++)
{
for (int k = 0; k < excelDataQuery.ColumnCount; k++)
{
emptyOutput[j, k] = String.Empty;
}
}
return(emptyOutput);
}
}
return(xlDodoniUDFResult);
}
/// <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 [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 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) + ".");
}
public static object GetExcelPoolItemLogging(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName)
{
try
{
IExcelDataQuery objectNameQuery = ExcelDataQuery.Create(xlObjectName);
string objectName;
if (objectNameQuery.TryGetValue <string>(out objectName, dataAdvice: ExcelDataAdvice.Create(ExcelPool.GetObjectNames())) != ExcelCellValueState.ProperValue)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage("Invalid object name '" + objectNameQuery.ToString(0, 0) + "'."));
}
objectNameQuery.QueryCompleted();
ILoggedObject loggingObject;
if (ExcelPool.TryGetObject <ILoggedObject>(objectName, out loggingObject) == false)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage("No object found of name '" + objectName + "' which supports logging functionality."));
}
var excelLogFile = loggingObject.Logging as ExcelObjectLogger;
if (excelLogFile == null)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage("No log file available."));
}
// return ExcelDataConverter.GetExcelRangeOutput<string>(excelLogFile.GetAsStringArray());
throw new NotImplementedException();
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
/// <summary>Gets the 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">The value (output).</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"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static ExcelCellValueState TryGetValue <TEnum>(this IExcelDataQuery dataQuery, out TEnum value, EnumStringRepresentationUsage enumStringRepresentationUsage, int rowIndex = 0, int columnIndex = 0)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
return(dataQuery.TryGetValue <TEnum>(enumStringRepresentationUsage, out value, rowIndex, columnIndex));
}
public static object IsError(
[ExcelArgument(Name = "dodoni UDF result", Description = "The Excel cell to check whether the result of a Dodoni.net UDF is meaningful")]
object xlDodoniUDFResult)
{
IExcelDataQuery excelDataQuery = ExcelDataQuery.Create(xlDodoniUDFResult);
string value;
if (excelDataQuery.TryGetValue <string>(out value) == ExcelCellValueState.ProperValue)
{
return(value.StartsWith("Error!", true, CultureInfo.InvariantCulture)); // here, we just check whether some Error message starts with "Error!"
}
return(false);
}
/// <summary>Gets the value of a specific cell.
/// </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="columnIndex">The null-based index of the column.</param>
/// <param name="dataAdvice">A data advice for a the property value, i.e. possible outcome to improve the useability.</param>
/// <returns>The value of the cell at the desired position.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration or no valid input is given by the user.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static T GetValue <T>(this IExcelDataQuery dataQuery, int rowIndex = 0, int columnIndex = 0, IExcelDataAdvice dataAdvice = null)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
T value;
if (dataQuery.TryGetValue <T>(out value, rowIndex, columnIndex, dataAdvice) == ExcelCellValueState.ProperValue)
{
return(value);
}
throw new ArgumentException("No valid input '" + dataQuery.ToString(rowIndex, columnIndex) + "' at row: " + rowIndex + ", column: " + columnIndex + " found" + GetFormatedDataQueryName(dataQuery) + ".");
}
/// <summary>Gets the value of a specific 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="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>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration or no valid input is given by the user.</exception>
/// <exception cref="ArgumentNullException">Thrown, if <paramref name="dataQuery"/> is <c>null</c>.</exception>
public static TEnum GetValue <TEnum>(this IExcelDataQuery dataQuery, EnumStringRepresentationUsage enumStringRepresentationUsage, int rowIndex = 0, int columnIndex = 0)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
TEnum value;
if (dataQuery.TryGetValue <TEnum>(enumStringRepresentationUsage, out value, rowIndex, columnIndex) == ExcelCellValueState.ProperValue)
{
return(value);
}
throw new ArgumentException("No valid input '" + dataQuery.ToString(rowIndex, columnIndex) + "' at row: " + rowIndex + ", column: " + columnIndex + " found" + GetFormatedDataQueryName(dataQuery) + ".");
}
/// <summary>Gets the null-based column index of a specific header name.
/// </summary>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="headerName">The name of the header to search.</param>
/// <param name="columnIndex">The null-based index of the column which contains in the first row <paramref name="headerName"/> (output).</param>
/// <returns>A value indicating whether <paramref name="columnIndex"/> contains valid data.</returns>
public static bool TryGetHeaderNameColumnIndex(this IExcelDataQuery dataQuery, string headerName, out int columnIndex)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
if (headerName == null)
{
throw new ArgumentNullException("headerName");
}
if (headerName.Length == 0)
{
throw new ArgumentException(String.Format(ExceptionMessages.ArgumentIsInvalid, "<empty>"), "headerName");
}
string idHeaderName = headerName.ToIDString();
for (int j = 0; j < dataQuery.ColumnCount; j++)
{
string value;
switch (dataQuery.TryGetValue <string>(out value, rowIndex: 0, columnIndex: j))
{
case ExcelCellValueState.EmptyOrMissingExcelCell:
break;
case ExcelCellValueState.NoValidValue:
throw new ArgumentException("Invalid header, ' " + dataQuery.ToString(0, j) + "' does not represent a string" + GetFormatedDataQueryName(dataQuery) + ".");
case ExcelCellValueState.ProperValue:
if (value != null)
{
if (idHeaderName == value.ToIDString())
{
columnIndex = j;
return(true);
}
}
break;
default:
throw new NotImplementedException();
}
}
columnIndex = -1;
return(false);
}
/// <summary>Gets the set of null-based (excluding header) row indices of a specified <see cref="DataTable"/> object.
/// </summary>
/// <param name="rowDataQuery">The row data query, which contains the null-based (excluding header) row indices.</param>
/// <param name="dataTable">The data table.</param>
/// <param name="rowCount">The number of row indices, i.e. of the return value (output).</param>
/// <returns>The set of null-based row indices; <c>null</c> if the set of row indices is empty.</returns>
private static IEnumerable <int> GetRowIndices(IExcelDataQuery rowDataQuery, DataTable dataTable, out int rowCount)
{
if (rowDataQuery.IsEmpty == true)
{
rowCount = dataTable.Rows.Count;
return(GetNullbasedIndices(rowCount));
}
if (rowDataQuery.ColumnCount > 1)
{
throw new ArgumentException("The Excel Range that contains the row indices must be a single row or column.");
}
SortedSet <int> rowIndices = new SortedSet <int>();
for (int j = 0; j < rowDataQuery.RowCount; j++)
{
int rowIndex;
switch (rowDataQuery.TryGetValue <int>(out rowIndex, j))
{
case ExcelCellValueState.ProperValue:
if ((rowIndex < 0) || (rowIndex > dataTable.Rows.Count))
{
throw new ArgumentException("Invalid row index '" + rowDataQuery.ToString(j, 0) + "'.");
}
rowIndices.Add(rowIndex);
break;
case ExcelCellValueState.NoValidValue:
throw new ArgumentException("Invalid row index '" + rowDataQuery.ToString(j, 0) + "'.");
case ExcelCellValueState.EmptyOrMissingExcelCell:
break; // nothing to do here
default:
throw new NotImplementedException();
}
}
if (rowIndices.Count == 0)
{
rowCount = dataTable.Rows.Count;
return(GetNullbasedIndices(rowCount));
}
rowCount = rowIndices.Count;
return(rowIndices);
}
/// <summary>Gets the value of a specific required 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="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>The value of the property.</returns>
/// <exception cref="ArgumentException">Thrown, if no property or valid value is given by the user or if
/// <typeparamref name="T"/> represents the type of a specific enumeration.</exception>
public static T GetRequiredPropertyValue <T>(this IExcelDataQuery dataQuery, string propertyName, IExcelDataAdvice dataAdvice = null, int propertyValueColumnIndex = 1)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
T value;
int rowIndex;
if (dataQuery.TryGetRowIndexOfPropertyName(propertyName, out rowIndex) == false)
{
throw new ArgumentException("No property with name '" + propertyName + "' found" + GetFormatedDataQueryName(dataQuery) + ".");
}
if (dataQuery.TryGetValue <T>(out value, rowIndex, propertyValueColumnIndex, dataAdvice) == ExcelCellValueState.ProperValue)
{
return(value);
}
throw new ArgumentException("No valid input for property '" + propertyName + "' found" + GetFormatedDataQueryName(dataQuery) + ".");
}
/// <summary>Gets the value of a specific required 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="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>The value of the property.</returns>
/// <exception cref="ArgumentException">Thrown, if no property or valid value is given by the user or 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 TEnum GetRequiredPropertyValue <TEnum>(this IExcelDataQuery dataQuery, string propertyName, EnumStringRepresentationUsage enumStringRepresentationUsage, int propertyValueColumnIndex = 1)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
TEnum value;
int rowIndex;
if (dataQuery.TryGetRowIndexOfPropertyName(propertyName, out rowIndex) == false)
{
throw new ArgumentException("No property with name '" + propertyName + "' found" + GetFormatedDataQueryName(dataQuery) + ".");
}
if (dataQuery.TryGetValue <TEnum>(enumStringRepresentationUsage, out value, rowIndex, propertyValueColumnIndex) == ExcelCellValueState.ProperValue)
{
return(value);
}
throw new ArgumentException("No valid input for property '" + propertyName + "' found" + 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 name of the file.
/// </summary>
/// <param name="xlPath">The path.</param>
/// <param name="xlFileName">The name of the file (without path).</param>
/// <param name="fileExtensionSearchPattern">The file extension to search.</param>
/// <returns>The file name including path.</returns>
private static string GetFileName(object xlPath, object xlFileName, IdentifierString fileExtensionSearchPattern)
{
IExcelDataQuery pathDataQuery = ExcelDataQuery.Create(xlPath);
IExcelDataQuery fileNameDataQuery = ExcelDataQuery.Create(xlFileName);
string path;
if (pathDataQuery.TryGetValue <string>(out path) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("No valid path!");
}
string[] possibleFileNames = Directory.GetFiles(path, "*." + fileExtensionSearchPattern.String, SearchOption.TopDirectoryOnly);
string fileName;
if (fileNameDataQuery.TryGetValue <string>(out fileName, dataAdvice: ExcelDataAdvice.Create(possibleFileNames, possibleFileName => Path.GetFileName(possibleFileName))) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("No valid file name!");
}
return(Path.Combine(path, fileName));
}
/// <summary>Gets the null-based column index of a specific header name.
/// </summary>
/// <param name="dataQuery">The <see cref="IExcelDataQuery"/> object.</param>
/// <param name="isHeaderName">A delegate that determines the name of the header to search; the argument is in the original representation, i.e. including whitespaces etc.</param>
/// <param name="columnIndex">The null-based index of the column which contains in the first row the desired header name (output).</param>
/// <returns>A value indicating whether <paramref name="columnIndex"/> contains valid data.</returns>
public static bool TryGetHeaderNameColumnIndex(this IExcelDataQuery dataQuery, Func <string, bool> isHeaderName, out int columnIndex)
{
if (dataQuery == null)
{
throw new ArgumentNullException("dataQuery");
}
if (isHeaderName == null)
{
throw new ArgumentNullException("isHeaderName");
}
for (int j = 0; j < dataQuery.ColumnCount; j++)
{
string value;
switch (dataQuery.TryGetValue <string>(out value, rowIndex: 0, columnIndex: j))
{
case ExcelCellValueState.EmptyOrMissingExcelCell:
break;
case ExcelCellValueState.NoValidValue:
throw new ArgumentException("Invalid header, ' " + dataQuery.ToString(0, j) + "' does not represent a string" + GetFormatedDataQueryName(dataQuery) + ".");
case ExcelCellValueState.ProperValue:
if (value != null)
{
if (isHeaderName(value) == true)
{
columnIndex = j;
return(true);
}
}
break;
default:
throw new NotImplementedException();
}
}
columnIndex = -1;
return(false);
}
/// <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());
}
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));
}
}
public static object GetOutputNames(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName,
[ExcelArgument(Name = "outputType", Description = "Contains the type of the output, i.e. 'tables' or 'properties'", AllowReference = true)]
object xlOutputType,
[ExcelArgument(Name = "propertyGroupName", Description = "[optional] If 'inputType = \"properties\" this argument represents the name of the table which represents the properties", AllowReference = true)]
object xlPropertyTableName,
[ExcelArgument(Name = "category", Description = "[optional] The category of the output, i.e. 'general' etc.", AllowReference = true)]
object xlCategoryName)
{
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.");
}
IExcelDataQuery outputTypeQuery = ExcelDataQuery.Create(xlOutputType);
eQueryType outputType;
if (outputTypeQuery.TryGetValue <eQueryType>(out outputType, EnumStringRepresentationUsage.StringAttribute) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("Invalid output type '" + outputTypeQuery.ToString(0, 0) + "'.");
}
/* get the output of the pool object: */
InfoOutput itemOutput = new InfoOutput();
poolItem.Value.FillInfoOutput(itemOutput);
InfoOutputPackage itemOutputCollection = null;
IExcelDataQuery categoryNameQuery = ExcelDataQuery.Create(xlCategoryName);
if (categoryNameQuery.IsEmpty == true)
{
itemOutputCollection = itemOutput.GetGeneralPackage();
}
else
{
string categoryName = categoryNameQuery.GetValue <string>(0, 0, ExcelDataAdvice.Create(itemOutput.CategoryNames));
itemOutputCollection = itemOutput.GetPackage(categoryName);
}
IExcelDataQuery propertyTableQuery = ExcelDataQuery.Create(xlPropertyTableName);
if (outputType == eQueryType.PropertyNames)
{
string propertyTableName = InfoOutputPackage.GeneralPropertyGroupName.String;
if (propertyTableQuery.IsEmpty == false)
{
propertyTableName = propertyTableQuery.GetValue <string>(0, 0, ExcelDataAdvice.Create(itemOutputCollection.PropertyGroupNames));
}
IIdentifierStringDictionary <InfoOutputProperty> propertyCollection;
if (itemOutputCollection.TryGetProperties(propertyTableName, out propertyCollection) == false)
{
throw new ArgumentException("The property table with name' " + propertyTableName + "' is invalid.");
}
object[,] outputRange = new object[propertyCollection.Count() + 1, 1];
outputRange[0, 0] = "Property names (output)";
int j = 1;
foreach (var property in propertyCollection)
{
outputRange[j, 0] = (string)property.Name;
j++;
}
return(ExcelDataConverter.GetExcelRangeOutput(outputRange));
}
else
{
if (propertyTableQuery.IsEmpty == false)
{
throw new ArgumentException("The property table with name' " + propertyTableQuery.ToString(0, 0) + "' is invalid.");
}
List <object> outputRange = new List <object>();
outputRange.Add("Table names");
foreach (var table in itemOutputCollection.GetDataTables(InfoOutputPackage.DataTableType.Single | InfoOutputPackage.DataTableType.Parent))
{
outputRange.Add(table.Item1.String);
}
return(ExcelDataConverter.GetExcelRangeOutput(outputRange));
}
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
public static object GetOutputPropertyValue(
[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 = "propertyGroupName", Description = "[optional] The name of the property group ('general properties' is standard)", AllowReference = true)]
object xlPropertyGroupName,
[ExcelArgument(Name = "category", Description = "The category of the output, i.e. 'general' etc.", AllowReference = true)]
object xlCategoryName)
{
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.");
}
/* get the output of the pool object: */
InfoOutput itemOutput = new InfoOutput();
poolItem.Value.FillInfoOutput(itemOutput);
InfoOutputPackage itemOutputCollection = null;
IExcelDataQuery categoryNameQuery = ExcelDataQuery.Create(xlCategoryName);
if (categoryNameQuery.IsEmpty == true)
{
itemOutputCollection = itemOutput.GetGeneralPackage();
}
else
{
string categoryName = categoryNameQuery.GetValue <string>(dataAdvice: ExcelDataAdvice.Create(itemOutput.CategoryNames));
itemOutputCollection = itemOutput.GetPackage(categoryName);
}
string propertyGroupName = InfoOutputPackage.GeneralPropertyGroupName.String;
IExcelDataQuery propertyGroupNameQuery = ExcelDataQuery.Create(xlPropertyGroupName);
if (propertyGroupNameQuery.IsEmpty == false)
{
propertyGroupName = propertyGroupNameQuery.GetValue <string>(dataAdvice: ExcelDataAdvice.Create(itemOutputCollection.PropertyGroupNames));
}
IIdentifierStringDictionary <InfoOutputProperty> properties;
if (itemOutputCollection.TryGetProperties(propertyGroupName, out properties) == false)
{
throw new ArgumentException("The property group name' " + propertyGroupName + "' is invalid.");
}
IExcelDataQuery propertyNameQuery = ExcelDataQuery.Create(xlPropertyName);
string propertyName;
if (propertyNameQuery.TryGetValue <string>(out propertyName, dataAdvice: ExcelDataAdvice.Create(properties)) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("The property name '" + propertyNameQuery.ToString(0, 0) + "' is invalid.");
}
InfoOutputProperty property;
if (properties.TryGetValue(propertyName, out property) == false)
{
throw new ArgumentException("No property with name '" + propertyName + "' found.");
}
return(ExcelDataConverter.GetExcelCellRepresentation(property.Value));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
public static object GetOutputRange(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName,
[ExcelArgument(Name = "tableName", Description = "The name of the table", AllowReference = true)]
object xlTableName,
[ExcelArgument(Name = "rowIndices", Description = "An [optional] Excel Range vector that contains null-based (excluding header) row indices of the table", AllowReference = true)]
object xlRowIndices,
[ExcelArgument(Name = "columnIndicesOrNames", Description = "An [optional] Excel Range vector which contains column names or null-based column indices of the table", AllowReference = true)]
object xlColumnIndicesOrNames,
[ExcelArgument(Name = "category", Description = "The category of the output, i.e. 'general' etc.", AllowReference = true)]
object xlCategoryName)
{
try
{
if (ExcelDnaUtil.IsInFunctionWizard() == true)
{
return(String.Empty);
}
string objectName;
ExcelPoolItem poolItem;
IExcelDataQuery objectNameQuery = ExcelDataQuery.Create(xlObjectName);
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.");
}
InfoOutput infoOutput = new InfoOutput();
poolItem.Value.FillInfoOutput(infoOutput);
InfoOutputPackage itemOutputCollection = null;
IExcelDataQuery categoryNameQuery = ExcelDataQuery.Create(xlCategoryName);
if (categoryNameQuery.IsEmpty == true)
{
itemOutputCollection = infoOutput.GetGeneralPackage();
}
else
{
string categoryName = categoryNameQuery.GetValue <string>(dataAdvice: ExcelDataAdvice.Create(infoOutput.CategoryNames));
itemOutputCollection = infoOutput.GetPackage(categoryName);
}
IExcelDataQuery tableNameDataQuery = ExcelDataQuery.Create(xlTableName);
DataTable tableOutput;
string tableName;
if ((tableNameDataQuery.TryGetValue <string>(out tableName, dataAdvice: ExcelDataAdvice.Create(itemOutputCollection.GetDataTableNames())) != ExcelCellValueState.ProperValue) || (itemOutputCollection.TryGetDataTable(tableName, out tableOutput) == false))
{
throw new ArgumentException("Table with name '" + tableNameDataQuery.ToString(0, 0) + "' is invalid.");
}
int rowCount, columnCount;
IEnumerable <int> rowIndices = GetRowIndices(ExcelDataQuery.Create(xlRowIndices), tableOutput, out rowCount);
IEnumerable <int> columnIndices = GetColumnIndices(ExcelDataQuery.Create(xlColumnIndicesOrNames), tableOutput, out columnCount);
return(ExcelDataConverter.GetExcelRangeOutput(GetSubTable(rowIndices, rowCount, columnIndices, columnCount, tableOutput)));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
/// <summary>Gets the set of null-based column indices of a specified <see cref="DataTable"/> object.
/// </summary>
/// <param name="columnDataQuery">The column data query, which contains the null-based column indices or column names.</param>
/// <param name="dataTable">The data table.</param>
/// <param name="columnCount">The number of column indices, i.e. of the return value (output).</param>
/// <returns>The set of null-based column indices; <c>null</c> if the set of column indices is empty.</returns>
private static IEnumerable <int> GetColumnIndices(IExcelDataQuery columnDataQuery, DataTable dataTable, out int columnCount)
{
if (columnDataQuery.IsEmpty == true)
{
columnCount = dataTable.Columns.Count;
return(GetNullbasedIndices(dataTable.Columns.Count));
}
if (columnDataQuery.ColumnCount > 1)
{
throw new ArgumentException("The Excel Range that contains the column names/indices must be a single row or column.");
}
HashSet <int> columnIndices = new HashSet <int>();
for (int j = 0; j < columnDataQuery.RowCount; j++)
{
int columnIndex;
if ((columnDataQuery.TryGetValue <int>(out columnIndex, j, dataAdvice: ExcelDataAdvice.Create(dataTable.Columns.Count)) == ExcelCellValueState.ProperValue) && (columnIndex < 0) && (columnIndex < dataTable.Columns.Count))
{
columnIndices.Add(columnIndex);
}
else // perhaps a column name is given by the user:
{
string columnName;
switch (columnDataQuery.TryGetValue <string>(out columnName, j, dataAdvice: ExcelDataAdvice.Create(GetColumnNames(dataTable))))
{
case ExcelCellValueState.ProperValue:
string idColumnName = columnName.ToIDString();
bool foundColumn = false;
int k = 0;
while ((k < dataTable.Columns.Count) && (foundColumn == false))
{
if ((dataTable.Columns[k].ColumnName != null) && (dataTable.Columns[k].ColumnName.ToIDString() == idColumnName))
{
columnIndices.Add(k);
foundColumn = true;
}
k++;
}
if (foundColumn == false)
{
throw new ArgumentException("Invalid column name '" + columnDataQuery.ToString(j, 0) + "'.");
}
break;
case ExcelCellValueState.NoValidValue:
throw new ArgumentException("Invalid column index/name '" + columnDataQuery.ToString(j, 0) + "'.");
case ExcelCellValueState.EmptyOrMissingExcelCell:
break;
// default: // todo: Funktioniert nicht - warum?
// new NotImplementedException();
}
}
}
if (columnIndices.Count == 0)
{
columnCount = dataTable.Columns.Count;
return(GetNullbasedIndices(dataTable.Columns.Count));
}
columnCount = columnIndices.Count;
return(columnIndices);
}
public static object GetInputNames(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName,
[ExcelArgument(Name = "inputType", Description = "Contains the type of the input, i.e. 'tables' or 'properties'", AllowReference = true)]
object xlInputType,
[ExcelArgument(Name = "propertyTableName", Description = "[optional] If 'inputType = \"properties\" this argument represents the name of the table which represents the properties", AllowReference = true)]
object xlPropertyTableName)
{
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.");
}
IExcelDataQuery inputTypeQuery = ExcelDataQuery.Create(xlInputType);
eQueryType inputType;
if (inputTypeQuery.TryGetValue <eQueryType>(out inputType, EnumStringRepresentationUsage.StringAttribute) != ExcelCellValueState.ProperValue)
{
throw new ArgumentException("Need valid input type; 'tables' and 'properties' are allowed.");
}
IExcelDataQuery propertyTableQuery = ExcelDataQuery.Create(xlPropertyTableName);
if (inputType == eQueryType.PropertyNames)
{
string propertyTableName = "General properties";
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("No property table with name' " + propertyTableQuery.ToString(0, 0) + "' available.");
}
object[,] outputRange = new object[propertyDataQuery.GetNonEmptyRowCount(m_EmptyExcelCellMode) + 1, 1];
outputRange[0, 0] = "Property names (input)";
int k = 1;
for (int j = 0; j < propertyDataQuery.RowCount; j++)
{
if (propertyDataQuery.IsEmptyExcelCell(j, 0, m_EmptyExcelCellMode) == false)
{
outputRange[k, 0] = propertyDataQuery.GetExcelData(j, 0);
k++;
}
}
return(ExcelDataConverter.GetExcelRangeOutput(outputRange));
}
else
{
if (propertyTableQuery.IsEmpty == false)
{
throw new ArgumentException("The property table with name' " + propertyTableQuery.ToString(0, 0) + "' is invalid.");
}
List <object> outputRange = new List <object>();
outputRange.Add("Table names");
foreach (string tableName in poolItem.GetDataQueryNames())
{
outputRange.Add(tableName);
}
return(ExcelDataConverter.GetExcelRangeOutput(outputRange));
}
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}
public static object GetInputRange(
[ExcelArgument(Name = "objectName", Description = "The name of the object", AllowReference = true)]
object xlObjectName,
[ExcelArgument(Name = "tableName", Description = "The name of the table", AllowReference = true)]
object xlTableName,
[ExcelArgument(Name = "rowIndex", Description = "The [optional] null-based row index of the table")]
object xlRowIndex,
[ExcelArgument(Name = "columnIndex", Description = "The [optional] null-based column index of the table")]
object xlColumnIndex)
{
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.");
}
IExcelDataQuery tableNameDataQuery = ExcelDataQuery.Create(xlTableName);
GuidedExcelDataQuery tableDataQuery;
string tableName;
if ((tableNameDataQuery.TryGetValue <string>(out tableName, dataAdvice: ExcelDataAdvice.Create(poolItem.GetDataQueryNames())) != ExcelCellValueState.ProperValue) || (poolItem.TryGetDataQuery(tableName, out tableDataQuery) == false))
{
throw new ArgumentException("Table name '" + tableNameDataQuery.ToString(0, 0) + "' is invalid.");
}
IExcelDataQuery rowDataQuery = ExcelDataQuery.Create(xlRowIndex);
IExcelDataQuery columnDataQuery = ExcelDataQuery.Create(xlColumnIndex);
object[,] outputRange = null;
if (!rowDataQuery.IsEmpty)
{
int rowIndex;
if ((rowDataQuery.TryGetValue <int>(out rowIndex) != ExcelCellValueState.ProperValue) || (rowIndex < 0) || (rowIndex > tableDataQuery.RowCount))
{
throw new ArgumentException("Invalid row index '" + rowDataQuery.ToString(0, 0) + "'.");
}
if (!columnDataQuery.IsEmpty)
{
int columnIndex;
if ((columnDataQuery.TryGetValue <int>(out columnIndex) != ExcelCellValueState.ProperValue) || (columnIndex < 0) || (columnIndex > tableDataQuery.ColumnCount))
{
throw new ArgumentException("Invalid column index '" + columnDataQuery.ToString(0, 0) + "'.");
}
outputRange = new object[1, 1];
outputRange[0, 0] = tableDataQuery.GetExcelData(rowIndex, columnIndex);
}
else
{
outputRange = new object[1, tableDataQuery.ColumnCount];
for (int j = 0; j < tableDataQuery.ColumnCount; j++)
{
outputRange[0, j] = tableDataQuery.GetExcelData(rowIndex, j);
}
}
}
else
{
if (!columnDataQuery.IsEmpty)
{
int columnIndex;
if ((columnDataQuery.TryGetValue <int>(out columnIndex) != ExcelCellValueState.ProperValue) || (columnIndex < 0) || (columnIndex > tableDataQuery.ColumnCount))
{
throw new ArgumentException("Invalid column index '" + columnDataQuery.ToString(0, 0) + "'.");
}
outputRange = new object[tableDataQuery.GetNonEmptyRowCount(m_EmptyExcelCellMode), 1];
int k = 0;
for (int j = 0; j < tableDataQuery.RowCount; j++)
{
if (tableDataQuery.IsEmptyExcelCell(j, columnIndex, m_EmptyExcelCellMode) == false)
{
outputRange[k, 0] = tableDataQuery.GetExcelData(j, columnIndex);
k++;
}
}
}
else
{
outputRange = new object[tableDataQuery.GetNonEmptyRowCount(m_EmptyExcelCellMode), tableDataQuery.ColumnCount];
int rowIndex = 0;
for (int j = 0; j < tableDataQuery.RowCount; j++)
{
if (tableDataQuery.IsEmptyRow(j, m_EmptyExcelCellMode) == false)
{
for (int k = 0; k < tableDataQuery.ColumnCount; k++)
{
outputRange[rowIndex, k] = tableDataQuery.GetExcelData(j, k);
}
rowIndex++;
}
}
}
}
return(ExcelDataConverter.GetExcelRangeOutput(outputRange));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e.Message));
}
}