/// <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>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)
{
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)
{
return(ExcelDataConverter.IsEmptyCell(data));
}
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 ((rowIndex < m_RowCount) && (rowIndex >= 0) && (columnIndex < m_ColumnCount) && (columnIndex >= 0))
{
m_SetOfUsedPropertyIndices.Add(rowIndex);
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>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))
{
string valueDropDownListAsString = EnumString <TEnum> .GetValues(enumStringRepresentationUsage).AsExcelDropDownListString();
m_Range.CreateDropdownList(0, 0, valueDropDownListAsString);
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 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);
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>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);
}
}
}
/// <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>Gets the value of a specific Excel cell.
/// </summary>
/// <typeparam name="T">The type of the value.</typeparam>
/// <param name="excelCell">The Excel cell.</param>
/// <param name="value">The value of the <paramref name="excelCell"/> (output).</param>
/// <returns>A value indicating whether <paramref name="value"/> contains valid data.</returns>
/// <exception cref="ArgumentException">Thrown, if <typeparamref name="T"/> represents a enumeration or <paramref name="excelCell"/> can not converted to an object of type <typeparamref name="T"/>.</exception>
public static bool TryGetCellValue <T>(object excelCell, out T value)
{
if (typeof(T).IsEnum) // which enumStringRepresentationUsage? --> call an other method
{
throw new ArgumentException("Do not use these method [TryGetCellValue] for enumerations.");
}
if ((excelCell == null) || (excelCell is ExcelEmpty) || (excelCell is ExcelMissing))
{
value = default(T);
return(false);
}
Type typeofT = typeof(T);
if (typeofT == typeof(string))
{
if (excelCell is String)
{
value = (T)(object)(string)excelCell;
return(true);
}
else
{
value = (T)(object)excelCell.ToString();
return(true);
}
}
else if (typeofT == typeof(Double))
{
double doubleValue;
if (ExcelDataConverter.TryGetDouble(excelCell, out doubleValue) == true)
{
value = (T)(object)doubleValue;
return(true);
}
}
else if (typeofT == typeof(int))
{
int intValue;
if (TryGetInteger(excelCell, out intValue) == true)
{
value = (T)(object)(intValue);
return(true);
}
}
else if (typeofT == typeof(DateTime))
{
DateTime dateTime;
if (TryGetDateTime(excelCell, out dateTime) == true)
{
value = (T)((object)dateTime);
return(true);
}
}
else if (typeofT == typeof(bool))
{
if (excelCell is bool)
{
value = (T)(object)(bool)excelCell;
return(true);
}
if (excelCell is string) // a fallback solution
{
IdentifierString stringRepresentation = new IdentifierString((string)excelCell);
if (IsTrueExcelCell(stringRepresentation))
{
value = (T)(object)(true);
return(true);
}
else if (IsFalseExcelCell(stringRepresentation))
{
value = (T)(object)(false);
return(true);
}
}
value = default(T);
return(false);
}
else if (typeofT == typeof(IdentifierString))
{
if (excelCell is String)
{
value = (T)(object)new IdentifierString((string)excelCell);
return(true);
}
else
{
value = (T)(object)new IdentifierString(excelCell.ToString());
return(true);
}
}
else
{
TryGetExcelCellValue tryGetExcelCellValue;
if (sm_ExcelCellConverter.TryGetValue(typeofT, out tryGetExcelCellValue) == true)
{
object tempValue;
if (tryGetExcelCellValue(excelCell, out tempValue) == true)
{
value = (T)tempValue;
return(true);
}
}
}
if (excelCell is T) // a fallback solution
{
value = (T)excelCell;
return(true);
}
value = default(T);
return(false);
}
/// <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()));
}
