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