/// <summary>Gets the value of a specific Excel cell with respect to specific row and column index.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="value">The value (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <T>(out T value, int rowIndex, int columnIndex, IExcelDataAdvice dataAdvice = null)
{
if ((rowIndex < m_RowCount) && (rowIndex >= 0) && (columnIndex < m_ColumnCount) && (columnIndex >= 0))
{
m_SetOfUsedPropertyIndices.Add(rowIndex);
if (dataAdvice != null)
{
m_Range.CreateDropdownList(rowIndex, columnIndex, dataAdvice.AsExcelDropDownListString());
}
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
if (ExcelDataConverter.IsEmptyCell(m_Data[rowIndex, columnIndex]) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <T>(m_Data[rowIndex, columnIndex], out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
/// <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>Gets the value of a specific property with respect to a null-based (row or column) index.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="value">The value (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <TEnum>(EnumStringRepresentationUsage enumStringRepresentationUsage, out TEnum value, int rowIndex, int columnIndex)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if ((rowIndex < m_RowCount) && (rowIndex >= 0) && (columnIndex < m_ColumnCount) && (columnIndex >= 0))
{
string valueDropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
m_SetOfUsedPropertyIndices.Add(rowIndex);
m_Range.CreateDropdownList(rowIndex, columnIndex, valueDropDownListAsString);
if (ExcelDataConverter.IsEmptyCell(m_Data[rowIndex, columnIndex]) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <TEnum>(m_Data[rowIndex, columnIndex], enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Returns a <see cref="System.String"/> representation of a specific entry.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A <see cref="System.String"/> that represents the entry with the desired position.
/// </returns>
/// <remarks>This method is used for error reports etc. only, thus do not use this method to get the value of a specific Excel cell.</remarks>
public string ToString(int rowIndex, int columnIndex)
{
object data = null;
bool validPosition = false;
if ((columnIndex == 0) && (rowIndex >= 0) && (rowIndex < m_RowCount))
{
data = m_PropertyNameArray[0, rowIndex];
validPosition = true;
}
else if ((columnIndex >= 1) && (columnIndex < m_ColumnCount) && (rowIndex >= 0) && (rowIndex < m_RowCount))
{
data = m_PropertyValueArray[columnIndex - 1, rowIndex];
validPosition = true;
}
if (validPosition == true)
{
if (ExcelDataConverter.IsEmptyCell(data) == true)
{
return("<empty>");
}
return(data.ToString());
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <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="inputCell">The input Excel cell.</param>
/// <param name="value">The value of the Excel cell (output).</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</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>
private ExcelCellValueState TryGetValue <TEnum>(object inputCell, out TEnum value, EnumStringRepresentationUsage enumStringRepresentationUsage)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
object excelCellValue;
if (inputCell == null)
{
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
else if (inputCell is ExcelReference)
{
ExcelReference excelRange = (ExcelReference)inputCell;
string valueDropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
excelRange.CreateDropdownList(0, 0, valueDropDownListAsString);
excelCellValue = excelRange.GetValue();
}
else
{
excelCellValue = inputCell;
}
if (ExcelDataConverter.IsEmptyCell(excelCellValue) == true)
{
value = default(TEnum);
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
return((ExcelDataConverter.TryGetCellValue <TEnum>(excelCellValue, enumStringRepresentationUsage, out value) == true) ? ExcelCellValueState.ProperValue : ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific Excel cell.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="inputCell">The input Excel cell.</param>
/// <param name="value">The value of the Excel cell (output).</param>
/// <param name="valueDropDownListAsString">The optional semicolon separated <see cref="System.String"/> representation of the
/// possible outcomes with respect to <paramref name="value"/>, i.e. if not <c>null</c> a drop down list will be added to the corresponding Excel cell.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
private ExcelCellValueState TryGetValue <T>(object inputCell, out T value, string valueDropDownListAsString)
{
object excelCellValue;
if (inputCell == null)
{
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
else if (inputCell is ExcelReference)
{
ExcelReference excelRange = (ExcelReference)inputCell;
excelRange.CreateDropdownList(0, 0, valueDropDownListAsString);
excelCellValue = excelRange.GetValue();
}
else
{
excelCellValue = inputCell;
}
if (ExcelDataConverter.IsEmptyCell(excelCellValue) == true)
{
value = default(T);
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
return((ExcelDataConverter.TryGetCellValue <T>(excelCellValue, out value) == true) ? ExcelCellValueState.ProperValue : ExcelCellValueState.NoValidValue);
}
/// <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)
{
if ((m_Data == null) && (ExcelDataConverter.IsEmptyCell(m_Data[j, 0]) == false))
{
throw new ArgumentException("Invalid property '" + ToString(j, 0) + "' .");
}
else
{
m_Range.CreateDropdownList(j, 0, unusedOptionalPropertyNameDropDownList);
}
}
}
}
}
/// <summary>Returns a <see cref="System.String"/> representation of a specific entry.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A <see cref="System.String"/> that represents the entry with the desired position.
/// </returns>
/// <remarks>This method is used for error reports etc. only, thus do not use this method to get the value of a specific Excel cell.</remarks>
public string ToString(int rowIndex, int columnIndex)
{
if ((rowIndex == 0) && (columnIndex == 0))
{
return(ExcelDataConverter.IsEmptyCell(m_ExcelCellValue) ? "<empty>" : m_ExcelCellValue.ToString());
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Determines whether a specific Excel cell is empty.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns><c>true</c> if the Excel cell at the specific position is empty; otherwise, <c>false</c>.</returns>
/// <exception cref="ArgumentException">Thrown, if the row/column position is not valid.</exception>
public bool IsEmptyExcelCell(int rowIndex, int columnIndex)
{
if ((rowIndex < m_Count) && (rowIndex >= 0) && (columnIndex == 0))
{
return(ExcelDataConverter.IsEmptyCell(m_Data[0, rowIndex]));
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Returns a <see cref="System.String"/> representation of a specific entry.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A <see cref="System.String"/> that represents the entry with the desired position.</returns>
/// <remarks>This method is used for error reports etc. only, thus do not use this method to get the value of a specific Excel cell.</remarks>
public string ToString(int rowIndex, int columnIndex)
{
if ((rowIndex < m_RowCount) && (rowIndex >= 0) && (columnIndex < m_ColumnCount) && (columnIndex >= 0))
{
return(ExcelDataConverter.IsEmptyCell(m_Data[rowIndex, columnIndex]) ? "<empty>" : m_Data[rowIndex, columnIndex].ToString());
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Gets the value of a specific Excel cell with respect to specific row and column index.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="value">The value (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <T>(out T value, int rowIndex, int columnIndex, IExcelDataAdvice dataAdvice = null)
{
if (typeof(T).IsEnum)
{
throw new ArgumentException("The type " + typeof(T).ToString() + " represents a enumeration which is not allowed.", "T");
}
if ((rowIndex >= 0) && (rowIndex < m_RowCount))
{
if (columnIndex == 0)
{
if (dataAdvice != null)
{
m_PropertyNames.CreateDropdownList(0, rowIndex, dataAdvice.AsExcelDropDownListString());
}
m_SetOfUsedPropertyIndices.Add(rowIndex);
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
if (ExcelDataConverter.IsEmptyCell(m_PropertyNameArray[0, rowIndex]) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <T>(m_PropertyNameArray[0, rowIndex], out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
else if ((columnIndex >= 1) && (columnIndex < m_ColumnCount))
{
if (dataAdvice != null)
{
m_PropertyValues.CreateDropdownList(columnIndex - 1, rowIndex, dataAdvice.AsExcelDropDownListString());
}
m_SetOfUsedPropertyIndices.Add(rowIndex);
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
if (ExcelDataConverter.IsEmptyCell(m_PropertyValueArray[columnIndex - 1, rowIndex]) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <T>(m_PropertyValueArray[columnIndex - 1, rowIndex], out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
}
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific property with respect to a null-based (row or column) index.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="value">The value (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <TEnum>(EnumStringRepresentationUsage enumStringRepresentationUsage, out TEnum value, int rowIndex, int columnIndex)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if (typeof(TEnum).IsEnum == false)
{
throw new ArgumentException("The type " + typeof(TEnum).ToString() + " does not represents a enumeration.", "TEnum");
}
if ((rowIndex >= 0) && (rowIndex < m_RowCount))
{
string dropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
if (columnIndex == 0)
{
m_PropertyNames.CreateDropdownList(0, rowIndex, dropDownListAsString);
m_SetOfUsedPropertyIndices.Add(rowIndex);
if (ExcelDataConverter.IsEmptyCell(m_PropertyNameArray[0, rowIndex]) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
if (ExcelDataConverter.TryGetCellValue <TEnum>(m_PropertyNameArray[0, rowIndex], enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
else if ((columnIndex >= 1) && (columnIndex < m_ColumnCount))
{
m_PropertyValues.CreateDropdownList(columnIndex - 1, rowIndex, dropDownListAsString);
m_SetOfUsedPropertyIndices.Add(rowIndex);
if (ExcelDataConverter.IsEmptyCell(m_PropertyValueArray[columnIndex - 1, rowIndex]) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <TEnum>(m_PropertyValueArray[columnIndex - 1, rowIndex], enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
}
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Returns a <see cref="System.String"/> representation of a specific entry.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A <see cref="System.String"/> that represents the entry with the desired position.</returns>
/// <remarks>This method is used for error reports etc. only, thus do not use this method to
/// get the value of a specific Excel cell.</remarks>
public string ToString(int rowIndex, int columnIndex)
{
if (rowIndex == 0)
{
if (columnIndex == 0)
{
return(ExcelDataConverter.IsEmptyCell(m_PropertyName) ? "<empty>" : m_PropertyName.ToString());
}
else if (columnIndex == 1)
{
return(ExcelDataConverter.IsEmptyCell(m_PropertyValue) ? "<empty>" : m_PropertyValue.ToString());
}
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Determines whether a specific Excel cell is empty.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns><c>true</c> if the Excel cell at the specific position is empty; otherwise, <c>false</c>.</returns>
/// <exception cref="ArgumentException">Thrown, if the row/column position is not valid.</exception>
public bool IsEmptyExcelCell(int rowIndex, int columnIndex)
{
if ((rowIndex < RowCount) && (rowIndex >= 0) && (columnIndex < ColumnCount) && (columnIndex >= 0))
{
if (rowIndex < m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1) // query the header
{
return(ExcelDataConverter.IsEmptyCell(m_HeaderData[rowIndex, columnIndex]));
}
else
{
return(ExcelDataConverter.IsEmptyCell(m_BelowHeaderData[rowIndex - (m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1), columnIndex]));
}
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Returns a <see cref="System.String"/> representation of a specific entry.
/// </summary>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <returns>A <see cref="System.String"/> that represents the entry with the desired position.</returns>
/// <remarks>This method is used for error reports etc. only, thus do not use this method to get the value of a specific Excel cell.</remarks>
public string ToString(int rowIndex, int columnIndex)
{
if ((rowIndex < RowCount) && (rowIndex >= 0) && (columnIndex < ColumnCount) && (columnIndex >= 0))
{
if (rowIndex < m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1) // query the header
{
return(ExcelDataConverter.IsEmptyCell(m_HeaderData[rowIndex, columnIndex]) ? "<empty>" : m_HeaderData[rowIndex, columnIndex].ToString());
}
else
{
int adjRowIndex = rowIndex - (m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1);
return(ExcelDataConverter.IsEmptyCell(m_BelowHeaderData[adjRowIndex, columnIndex]) ? "<empty>" : m_BelowHeaderData[adjRowIndex, columnIndex].ToString());
}
}
throw new ArgumentException("Invalid Excel cell position, row = " + rowIndex + " column = " + columnIndex + ".");
}
/// <summary>Gets the value of a specific property with respect to a null-based (row or column) index.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="value">The value (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <TEnum>(EnumStringRepresentationUsage enumStringRepresentationUsage, out TEnum value, int rowIndex, int columnIndex)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if ((rowIndex < RowCount) && (rowIndex >= 0) && (columnIndex < ColumnCount) && (columnIndex >= 0))
{
string valueDropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
if (rowIndex < m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1) // query the header
{
m_HeaderRange.CreateDropdownList(rowIndex, columnIndex, valueDropDownListAsString);
if (ExcelDataConverter.IsEmptyCell(m_HeaderData[rowIndex, columnIndex]) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <TEnum>(m_HeaderData[rowIndex, columnIndex], enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
else // query below the header
{
int adjRowIndex = rowIndex - (m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1);
m_BelowHeaderRange.CreateDropdownList(adjRowIndex, columnIndex, valueDropDownListAsString);
if (ExcelDataConverter.IsEmptyCell(m_BelowHeaderData[adjRowIndex, columnIndex]) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <TEnum>(m_BelowHeaderData[adjRowIndex, columnIndex], enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
}
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific Excel cell with respect to specific row and column index.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="value">The value (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <T>(out T value, int rowIndex, int columnIndex, IExcelDataAdvice dataAdvice = null)
{
if ((rowIndex < RowCount) && (rowIndex >= 0) && (columnIndex < ColumnCount) && (columnIndex >= 0))
{
if (dataAdvice != null)
{
m_HeaderRange.CreateDropdownList(rowIndex, columnIndex, dataAdvice.AsExcelDropDownListString());
}
m_GuidedExcelDataQuery.SetDataAdvice(rowIndex, columnIndex, dataAdvice);
if (rowIndex < m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1) // query the header
{
if (ExcelDataConverter.IsEmptyCell(m_HeaderData[rowIndex, columnIndex]) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <T>(m_HeaderData[rowIndex, columnIndex], out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
else // query below the header
{
int adjRowIndex = rowIndex - (m_HeaderRange.RowLast - m_HeaderRange.RowFirst + 1);
if (ExcelDataConverter.IsEmptyCell(m_BelowHeaderData[adjRowIndex, columnIndex]) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
else if (ExcelDataConverter.TryGetCellValue <T>(m_BelowHeaderData[adjRowIndex, columnIndex], out value) == true)
{
m_GuidedExcelDataQuery.SetData(rowIndex, columnIndex, value);
return(ExcelCellValueState.ProperValue);
}
}
}
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific property with respect to a null-based (row or column) index.
/// </summary>
/// <typeparam name="TEnum">The type of the output which is assumed to be a enumeration.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="enumStringRepresentationUsage">The method how to compute the <see cref="System.String"/> representation.</param>
/// <param name="value">The value (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.
/// </returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="TEnum"/> does not represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <TEnum>(EnumStringRepresentationUsage enumStringRepresentationUsage, out TEnum value, int rowIndex, int columnIndex)
where TEnum : struct, IComparable, IConvertible, IFormattable
{
if ((rowIndex == 0) && (columnIndex == 0))
{
if (ExcelDataConverter.IsEmptyCell(m_ExcelCellValue) == true)
{
value = default(TEnum);
m_GuidedExcelDataQuery.SetData(0, 0, typeof(TEnum));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
if (ExcelDataConverter.TryGetCellValue <TEnum>(m_ExcelCellValue, enumStringRepresentationUsage, out value) == true)
{
m_GuidedExcelDataQuery.SetData(0, 0, value);
return(ExcelCellValueState.ProperValue);
}
}
value = default(TEnum);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Gets the value of a specific Excel cell with respect to specific row and column index.
/// </summary>
/// <typeparam name="T">The type of the output.</typeparam>
/// <param name="rowIndex">The null-based index of the row.</param>
/// <param name="columnIndex">The null-based index of the column.</param>
/// <param name="value">The value (output).</param>
/// <param name="dataAdvice">Data advice, i.e. a list of possible outcome, perhaps <c>null</c>.</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.
/// </returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration.</exception>
public ExcelCellValueState TryGetValue <T>(out T value, int rowIndex, int columnIndex, IExcelDataAdvice dataAdvice = null)
{
if ((rowIndex == 0) && (columnIndex == 0))
{
m_GuidedExcelDataQuery.SetDataAdvice(0, 0, dataAdvice);
if (ExcelDataConverter.IsEmptyCell(m_ExcelCellValue) == true)
{
value = default(T);
m_GuidedExcelDataQuery.SetData(0, 0, typeof(T));
return(ExcelCellValueState.EmptyOrMissingExcelCell);
}
if (ExcelDataConverter.TryGetCellValue <T>(m_ExcelCellValue, out value) == true)
{
m_GuidedExcelDataQuery.SetData(0, 0, value);
return(ExcelCellValueState.ProperValue);
}
}
value = default(T);
return(ExcelCellValueState.NoValidValue);
}
/// <summary>Returns a <see cref="System.String" /> that represents this instance.
/// </summary>
/// <returns>A <see cref="System.String" /> that represents this instance.
/// </returns>
public override string ToString()
{
return(String.Format("{0} : {1}", ExcelDataConverter.IsEmptyCell(m_PropertyName) ? "<empty>" : m_PropertyName.ToString(), ExcelDataConverter.IsEmptyCell(m_PropertyValue) ? "<empty>" : m_PropertyValue.ToString()));
}
/// <summary>Returns a <see cref="System.String" /> that represents this instance.
/// </summary>
/// <returns>A <see cref="System.String" /> that represents this instance.
/// </returns>
public override string ToString()
{
return(ExcelDataConverter.IsEmptyCell(m_ExcelCellValue) ? "<empty>" : m_ExcelCellValue.ToString());
}
/// <summary>Creates a dropdown list at a specific Excel cell.
/// </summary>
/// <param name="excelRange">The Excel range given as a single row or single column containing values of some properties.</param>
/// <param name="rowIndex">The null-based row index of the <paramref name="excelRange"/> where to add a dropdown list.</param>
/// <param name="columnIndex">The null-based column index of the <paramref name="excelRange"/> where to add a dropdown list.</param>
/// <param name="dropDownListAsString">The semicolon separated <see cref="System.String"/> representation of the dropdown list to add.</param>
/// <remarks>This method adds a specific (Excel range) data validation with respect to a specific Excel position of <paramref name="excelRange"/>
/// and the dropdown list will contains the elements of the <paramref name="dropDownListAsString"/>.</remarks>
public static void CreateDropdownList(this ExcelDna.Integration.ExcelReference excelRange, int rowIndex, int columnIndex, string dropDownListAsString)
{
if ((sm_UseDataAdvice != DropDownListCreationType.None) && (dropDownListAsString != null) && (dropDownListAsString.Length > 0))
{
try
{
if (ExcelDna.Integration.ExcelDnaUtil.IsInFunctionWizard() == false)
{
dynamic sheet = ExcelAddIn.ExcelApplication.ActiveSheet;
dynamic cell = sheet.Cells[excelRange.RowFirst + rowIndex + 1, excelRange.ColumnFirst + columnIndex + 1]; // in Excel rows/columns are one-based
if (((sm_UseDataAdvice == DropDownListCreationType.EmptyExcelCells) && (ExcelDataConverter.IsEmptyCell(cell.Value) == true)) ||
((sm_UseDataAdvice == DropDownListCreationType.ExcelCellsWithoutFormula) && (cell.HasFormula == false)) ||
(sm_UseDataAdvice == DropDownListCreationType.Always))
{
cell.Validation.Delete();
cell.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertInformation, XlFormatConditionOperator.xlBetween, dropDownListAsString, Type.Missing); // todo: Excel has a restriction
cell.Validation.ShowError = false;
}
}
}
catch (Exception e)
{
// Logger.Stream.Add_Info_ExcelCellDropdownListFails(exception: e);
}
}
}
