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