/// <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) + ".");
}
public static object ListObjectsFromFile(
[ExcelArgument(Name = "path", Description = "The path", AllowReference = true)]
object xlPath,
[ExcelArgument(Name = "fileName", Description = "The file name", AllowReference = true)]
object xlFileName,
[ExcelArgument(Name = "fileFormat", Description = "[Optional] The file format; the file extension will be used by default", AllowReference = true)]
object xlFileFormat)
{
try
{
IExcelDataQuery fileFormatDataQuery = ExcelDataQuery.Create(xlFileFormat, "File format");
string fileName;
IObjectStreamer objectStreamer;
if (fileFormatDataQuery.IsEmpty == true)
{
fileName = GetFileName(xlPath, xlFileName, ObjectStreamer.GetFileExtensions());
if (ObjectStreamer.TryGetObjectStreamerByFileExtension(ExtendedPath.GetExtension(fileName), out objectStreamer) == false)
{
throw new ArgumentException("Invalid file extension '" + ExtendedPath.GetExtension(fileName) + "', used default file extensions or specify the file format.");
}
}
else
{
if (fileFormatDataQuery.TryGetPoolValue <IObjectStreamer>(ObjectStreamer.TryGetObjectStreamer, out objectStreamer, dataAdvice: ExcelDataAdvice.Create(ObjectStreamer.GetNames())) == false)
{
throw new ArgumentException("Invalid file format " + fileFormatDataQuery.ToString(0, 0) + ".");
}
fileName = GetFileName(xlPath, xlFileName, objectStreamer.FileExtension);
fileFormatDataQuery.QueryCompleted();
}
StreamReader streamReader = new StreamReader(fileName);
IObjectStreamReader objectStreamReader = objectStreamer.GetStreamReader(streamReader);
var objectNames = ExcelPool.GetObjectNames(objectStreamReader);
int n = objectNames.Count();
object[,] output = new object[n + 1, 2];
output[0, 0] = "Name";
output[0, 1] = "Type";
int j = 1;
foreach (var obj in objectNames)
{
output[j, 0] = obj.Item2;
output[j, 1] = obj.Item1.Name.String;
j++;
}
objectStreamReader.Close();
return(ExcelDataConverter.GetExcelRangeOutput(output));
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e));
}
}
/// <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);
}
public static object LoadObjectsFromFile(
[ExcelArgument(Name = "path", Description = "The path", AllowReference = true)]
object xlPath,
[ExcelArgument(Name = "fileName", Description = "The file name", AllowReference = true)]
object xlFileName,
[ExcelArgument(Name = "objectNames", Description = "[Optional] A list of object names to load; all objects will be loaded by default", AllowReference = true)]
object xlObjectNames,
[ExcelArgument(Name = "fileFormat", Description = "[Optional] The file format; the file extension will be used by default", AllowReference = true)]
object xlFileFormat)
{
try
{
IExcelDataQuery fileFormatDataQuery = ExcelDataQuery.Create(xlFileFormat, "File format");
string fileName;
IObjectStreamer objectStreamer;
if (fileFormatDataQuery.IsEmpty == true)
{
fileName = GetFileName(xlPath, xlFileName, ObjectStreamer.GetFileExtensions());
if (ObjectStreamer.TryGetObjectStreamerByFileExtension(ExtendedPath.GetExtension(fileName), out objectStreamer) == false)
{
throw new ArgumentException("Invalid file extension '" + Path.GetExtension(fileName) + "', used default file extensions or specify the file format.");
}
}
else
{
if (fileFormatDataQuery.TryGetPoolValue <IObjectStreamer>(ObjectStreamer.TryGetObjectStreamer, out objectStreamer, dataAdvice: ExcelDataAdvice.Create(ObjectStreamer.GetNames())) == false)
{
throw new ArgumentException("Invalid file format " + fileFormatDataQuery.ToString(0, 0) + ".");
}
fileName = GetFileName(xlPath, xlFileName, objectStreamer.FileExtension);
fileFormatDataQuery.QueryCompleted();
}
IExcelDataQuery objectNamesDataQuery = ExcelDataQuery.Create(xlObjectNames, "Object names");
IEnumerable <string> objectNames = objectNamesDataQuery.GetColumnVector <string>();
objectNamesDataQuery.QueryCompleted();
StreamReader streamReader = new StreamReader(fileName);
IObjectStreamReader objectStreamReader = objectStreamer.GetStreamReader(streamReader);
string infoMessage;
IEnumerable <ExcelPoolItem> excelPoolItems;
ExcelPool.TryLoadObjectsByName(objectStreamReader, objectNames, out infoMessage, out excelPoolItems);
objectStreamReader.Close();
return(infoMessage.ToTimeStampString());
}
catch (Exception e)
{
return(ExcelDataConverter.GetExcelRangeErrorMessage(e));
}
}
/// <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 [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 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 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));
}
}
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 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));
}
}
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 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);
}