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