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