/// <summary>Gets the null-based row index of a specific property.
/// </summary>
/// <param name="propertyName">The name of the property to search (in the first column).</param>
/// <param name="rowIndex">The null-based index of the row which contains the property (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="rowIndex"/> contains valid data.</returns>
public bool TryGetRowIndexOfPropertyName(string propertyName, out int rowIndex, IExcelDataAdvice dataAdvice = null)
{
if (propertyName == null)
{
throw new ArgumentNullException("propertyName");
}
string idPropertyName = propertyName.ToIDString();
for (int j = 0; j < m_RowCount; j++)
{
if (m_Data[j, 0] is String)
{
string cellName = (string)m_Data[j, 0];
if (cellName.ToIDString() == idPropertyName)
{
rowIndex = j;
if (dataAdvice != null)
{
ExcelLowLevel.CreateDropdownList(m_Range, rowIndex, 0, dataAdvice.AsExcelDropDownListString());
}
m_SetOfUsedPropertyIndices.Add(rowIndex);
m_GuidedExcelDataQuery.SetData(rowIndex, 0, cellName);
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, 0, dataAdvice);
return(true);
}
}
}
m_UnusedOptionalPropertyNames.Add(propertyName);
m_GuidedExcelDataQuery.AddUnusedPropertyName(propertyName);
rowIndex = -1;
return(false);
}
/// <summary>Finalize the current <see cref="IExcelDataQuery"/> instance.
/// </summary>
/// <param name="throwExceptionIfDataIsNotUsed">A value indicating whether an exception will be thrown, if some of the data are are not queried by the user.</param>
/// <exception cref="ArgumentException">Thrown, if <paramref name="throwExceptionIfDataIsNotUsed"/> is <c>true</c> and a property or table entry has been detected which
/// is not queried, i.e. not used.</exception>
/// <remarks>Call this method before calling <see cref="IExcelDataQuery.AsCustomizeData()"/> to check the user input; perhaps the user has enter properties or values
/// which are not used by the program and an exception will be shown to indicate wrong user input.</remarks>
public void QueryCompleted(bool throwExceptionIfDataIsNotUsed = true)
{
if (throwExceptionIfDataIsNotUsed)
{
string unusedOptionalPropertyNameDropDownList = null;
if (m_UnusedOptionalPropertyNames.Count > 0)
{
unusedOptionalPropertyNameDropDownList = m_UnusedOptionalPropertyNames.AsExcelDropDownListString();
}
for (int j = 0; j < m_RowCount; j++)
{
if (m_SetOfUsedPropertyIndices.Contains(j) == false)
{
for (int k = 0; k < m_ColumnCount - 1; k++)
{
if (ExcelDataConverter.IsEmptyCell(m_PropertyNameArray[0, j]) == false)
{
throw new ArgumentException("Invalid property '" + m_PropertyNameArray[0, j] + "'.");
}
else if (ExcelDataConverter.IsEmptyCell(m_PropertyValueArray[k, j]) == false)
{
throw new ArgumentException("Invalid value '" + m_PropertyValueArray[k, j] + "'.");
}
else
{
ExcelLowLevel.CreateDropdownList(m_PropertyNames, 0, j, unusedOptionalPropertyNameDropDownList);
}
}
}
}
}
}
/// <summary>Store the configuration, i.e. write a specific configuration file.
/// </summary>
public void StoreConfiguration()
{
var useDataAdvise = comboBoxAddDropDownList.SelectedItem as EnumString <ExcelLowLevel.DropDownListCreationType>;
if (useDataAdvise != null)
{
ExcelLowLevel.StoreUseDataAdvice(useDataAdvise.Value);
}
ExcelLowLevel.StoreUseVBADataAdvice(cBoxAddVBADropDownList.Checked);
ExcelDataAdvice.DropDownRepresentation.StoreDropDownSeparator(comboBoxSeparator.Text);
ExcelDataAdvice.Pool.m_BooleanAdvice.SetTrueString(comboBoxTRUE.Text);
ExcelDataAdvice.Pool.m_BooleanAdvice.SetFalseString(comboBoxFALSE.Text);
ExcelAddIn.Configuration.Save();
m_ConfigurationChanged = false;
}
/// <summary>Restores the configuration, i.e. reads the original configuration from some
/// configuration file or take some default values and show these configuration.
/// </summary>
public void RestoreConfiguration()
{
ExcelLowLevel.DropDownListCreationType useDataAdvice = ExcelLowLevel.GetUseDataAdviceFromConfigFile();
for (int j = 0; j < comboBoxAddDropDownList.Items.Count; j++)
{
var comboBoxUseDataAdvice = comboBoxAddDropDownList.Items[j] as EnumString <ExcelLowLevel.DropDownListCreationType>;
if ((comboBoxUseDataAdvice != null) && (comboBoxUseDataAdvice.Value == useDataAdvice))
{
comboBoxAddDropDownList.SelectedIndex = j;
break;
}
}
cBoxAddVBADropDownList.Checked = ExcelLowLevel.GetUseVBADataAdviceFromConfigFile();
comboBoxSeparator.Text = ExcelDataAdvice.DropDownRepresentation.GetDropDownSeparatorFromConfigFile();
comboBoxTRUE.Text = ExcelDataAdvice.Pool.m_BooleanAdvice.RestoreTrueString();
comboBoxFALSE.Text = ExcelDataAdvice.Pool.m_BooleanAdvice.RestoreFalseString();
m_ConfigurationChanged = false;
}
/// <summary>Gets a specific excel range message, i.e. convert a <see cref="System.String"/> into the Excel Range selected by the user as output of the user defined function.
/// </summary>
/// <param name="message">The message.</param>
/// <returns>The <paramref name="message"/> as some Excel range output.</returns>
public static object GetExcelRangeMessage(string message)
{
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount);
if ((excelRangeRowCount == 1) && (excelRangeColumnCount == 1))
{
return(message);
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
returnValue[0, 0] = message;
return(returnValue);
}
/// <summary>Gets the Excel range output for a specific array.
/// </summary>
/// <typeparam name="T1">The type of the first item.</typeparam>
/// <typeparam name="T2">The type of the second item.</typeparam>
/// <param name="values">The collection of values.</param>
/// <param name="header1">An optional header for the first item.</param>
/// <param name="header2">An optional header for the second item.</param>
/// <returns>The Excel range which contains the <paramref name="values"/>.</returns>
public static object GetExcelRangeOutput <T1, T2>(IEnumerable <Tuple <T1, T2> > values, string header1 = null, string header2 = null, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
bool containsHeader = (header1 != null) || (header2 != null);
int valueRowCount = values.Count() + (containsHeader == true ? 1 : 0);
if (valueRowCount > excelRangeRowCount)
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x 2 Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least 2 x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
int k = 0 + (containsHeader == true ? 1 : 0);
foreach (var value in values)
{
returnValue[k, 0] = value.Item1;
returnValue[k++, 1] = value.Item2;
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
int k = 0;
foreach (var value in values)
{
var firstCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex);
firstCellReference.SetValue(value.Item1);
var secondCellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex + 1);
secondCellReference.SetValue(value.Item2);
k++;
}
});
var returnValue = new object[1, 2];
if (header1 != null)
{
returnValue[0, 0] = header1;
}
if (header2 != null)
{
returnValue[0, 1] = header2;
}
return(returnValue);
}
}
/// <summary>Gets the Excel range output for a specific array.
/// </summary>
/// <typeparam name="T">The type of the values.</typeparam>
/// <param name="values">The collection of values.</param>
/// <param name="header">An optional header.</param>
/// <param name="rangeOutputType">A value indicating how the values should be presented in Excel.</param>
/// <returns>The Excel range which contains the <paramref name="values"/>.</returns>
public static object GetExcelRangeOutput <T>(IEnumerable <T> values, string header = null, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
int valueRowCount = values.Count() + ((header != null) ? 1 : 0);
if (valueRowCount > excelRangeRowCount)
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x 1 Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least 1 x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
int k = 0;
if (header != null)
{
returnValue[k++, 0] = header;
}
foreach (T value in values)
{
returnValue[k++, 0] = value;
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
int k = 0;
foreach (T value in values)
{
var cellReference = new ExcelReference(firstRowIndex + k + 1, firstColumnIndex);
cellReference.SetValue(value);
k++;
}
});
return((header != null) ? header : "<unknown>");
}
}
/// <summary>Gets the excel range output, i.e. converts the output such that no '#NV' values are
/// shown and print an error message, if the given range is to small for the output.
/// </summary>
/// <typeparam name="T">The type of the values.</typeparam>
/// <param name="values">The values.</param>
/// <param name="rangeOutputType">A value indicating how the values should be presented in Excel.</param>
/// <returns>The range which contains <paramref name="values"/> or some error message.</returns>
public static object GetExcelRangeOutput <T>(T[][] values, RangeOutputType rangeOutputType = RangeOutputType.Standard)
{
int valueRowCount = values.GetLength(0);
int valueColumnCount = values[0].GetLength(0);
if (rangeOutputType == RangeOutputType.Standard)
{
ExcelLowLevel.OutputRangeOrientation rangeOrientation;
int excelRangeRowCount, excelRangeColumnCount;
ExcelLowLevel.GetRangeSize(out excelRangeRowCount, out excelRangeColumnCount, out rangeOrientation);
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Transposed) // swap the Excel Range size given by the user
{
int temp = excelRangeRowCount;
excelRangeRowCount = excelRangeColumnCount;
excelRangeColumnCount = temp;
}
object[,] returnValue = new object[excelRangeRowCount, excelRangeColumnCount];
for (int i = 0; i < excelRangeRowCount; i++)
{
for (int j = 0; j < excelRangeColumnCount; j++)
{
returnValue[i, j] = String.Empty;
}
}
if ((valueRowCount > excelRangeRowCount) || (valueColumnCount > excelRangeColumnCount))
{
if (rangeOrientation == ExcelLowLevel.OutputRangeOrientation.Regular)
{
returnValue[0, 0] = "ERROR: At least " + valueRowCount + " x " + valueColumnCount + " Range needed.";
}
else
{
returnValue[0, 0] = "ERROR: At least " + valueColumnCount + " x " + valueRowCount + " Range needed.";
}
return(returnValue);
}
for (int i = 0; i < valueRowCount; i++)
{
for (int j = 0; j < valueColumnCount; j++)
{
returnValue[i, j] = values[i][j];
}
}
return(returnValue);
}
else // write the result below the cell where the user has called the specified UDF
{
int firstRowIndex, firstColumnIndex;
var returnValue = ExcelLowLevel.GetCurrentRangePosition(out firstRowIndex, out firstColumnIndex);
ExcelAsyncUtil.QueueAsMacro(() =>
{
for (int i = 0; i < valueRowCount; i++)
{
for (int j = 0; j < valueColumnCount; j++)
{
var cellReference = new ExcelReference(firstRowIndex + i + 1, firstColumnIndex + j);
cellReference.SetValue(values[i][j]);
}
}
});
return(String.Format("<Below result of> {0}", returnValue).ToTimeStampString());
}
}
