C # Excel Dependent Picklist с Validation & Indirect

Ниже приведена общая попытка создания зависимых picklists динамически с C #. Когда значение «A» выбрано из pick1, pick2 должен отображать значения из SecondaryRangeA.

Этот код почти работает, но вместо отображения SecondaryRangeA он отображает буквальное значение «A».

pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(\"A5\")"); 

Когда я открываю excel после его экспорта и изменения валидации данных, он показывает формулу.

 =INDIRECT("A5") 

Если я изменю формулу вручную в Excel, чтобы исключить кавычки, она работает должным образом.

 =INDIRECT(A5) 

Когда я изменяю код на следующий, я получаю исключение. Есть идеи?

 pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A5)"); 

Исключение:

 System.Runtime.InteropServices.COMException was unhandled ErrorCode=-2146827284 Message=Exception from HRESULT: 0x800A03EC Source="" StackTrace: at System.RuntimeType.ForwardCallToInvokeMember(String memberName, BindingFlags flags, Object target, Int32[] aWrapperTypes, MessageData& msgData) at Microsoft.Office.Interop.Excel.Validation.Add(XlDVType Type, Object AlertStyle, Object Operator, Object Formula1, Object Formula2) at TestExcelValidation.Program.Main(String[] args) in C:\TFS\ExcelInterop\TestExcelValidation\Program.cs:line 44 at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args) at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly() at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state) at System.Threading.ThreadHelper.ThreadStart() InnerException: 

Полный пример:

 using System.Collections.Generic; using System.IO; using System.Linq; using Microsoft.Office.Interop.Excel; namespace TestExcelValidation { class Program { static void Main(string[] args) { string temporaryPath = Path.GetTempPath(); string temporaryFile = Path.GetTempFileName(); Application appl = new Application(); appl.Visible = true; Workbook workbook = appl.Workbooks.Open(temporaryFile, 0, true, 5, "", "", true, XlPlatform.xlWindows, "\t", false, false, 0, true, 1, 0); Worksheet worksheet = (Worksheet)workbook.Worksheets.Add(); List<string> primaryList = new List<string>(); primaryList.Add("A"); primaryList.Add("B"); List<string> secondaryListA = new List<string>(); secondaryListA.Add("A1"); secondaryListA.Add("A2"); secondaryListA.Add("A3"); List<string> secondaryListB = new List<string>(); secondaryListB.Add("B1"); secondaryListB.Add("B2"); secondaryListB.Add("B3"); Range primaryRange = AddToExcelNamedRange(worksheet, primaryList, 'A', 1, "PrimaryRange"); Range secondaryRangeA = AddToExcelNamedRange(worksheet, secondaryListA, 'B', 1, "A"); Range secondaryRangeB = AddToExcelNamedRange(worksheet, secondaryListB, 'C', 1, "B"); Range pick1 = worksheet.Range["A5"]; pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange"); Range pick2 = worksheet.Range["A6"]; pick2.Validation.Delete(); pick2.NumberFormat = "Text"; pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(\"A5\")"); pick2.Validation.InCellDropdown = true; pick2.Validation.IgnoreBlank = true; } private static Range AddToExcelNamedRange(Worksheet worksheet, List<string> primaryList, char col, int row, string rangeName) { Range range = worksheet.Range[col.ToString() + row.ToString(), col.ToString() + primaryList.Count().ToString()]; range.Name = rangeName; foreach (string item in primaryList) { worksheet.Cells[row, col - 64] = item; row++; } return range; } } } 

У меня есть обходной путь, но я хочу знать, почему это не работает. Я уверен, что снова столкнусь с этим.

Вот ответ на английском, бог знает только то, что говорят два других.

проблема

Добавление каскадного выпадающего списка с использованием проверки в Excel через C # (или VBA) завершается с ошибкой COMException 0x800A03EC.

причина

Причина, по которой он не работает, заключается в том, что источник фактически пуст.

Позвольте мне показать вам, как я это сделал. Я ввел макрос в Excel и запустил его:

 Range pick1 = worksheet.Range["A5"]; pick1.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=PrimaryRange"); Range pick2 = worksheet.Range["A6"]; StringBuilder sb = new StringBuilder(); sb.Append("Sub InsertCascadingDropDown()" + Environment.NewLine); sb.Append(" Range(\"A6\").Select" + Environment.NewLine); sb.Append(" With Selection.Validation" + Environment.NewLine); sb.Append(" .Delete" + Environment.NewLine); sb.Append(" .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:= xlBetween, Formula1:=\"=INDIRECT(A5)\"" + Environment.NewLine); sb.Append(" .IgnoreBlank = True" + Environment.NewLine); sb.Append(" .InCellDropdown = True" + Environment.NewLine); sb.Append(" .ShowInput = True" + Environment.NewLine); sb.Append(" .ShowError = True" + Environment.NewLine); sb.Append(" End With" + Environment.NewLine); sb.Append("End Sub" + Environment.NewLine); //You need to add a COM reference to Microsoft Visual Basic for Applications Extensibility for this to work var xlmodule = workbook.VBProject.VBComponents.Add(Microsoft.Vbe.Interop.vbext_ComponentType.vbext_ct_StdModule); xlmodule.CodeModule.AddFromString(sb.ToString()); appl.Run("InsertCascadingDropDown"); 

Это вызывает ошибку времени выполнения «1004», когда макрос выполняет строку для добавления проверки:

 .Add Type:=xlValidateList, AlertStyle:=xlValidAlertStop, Operator:=xlBetween, Formula1:="=INDIRECT(A5)" 

введите описание изображения здесь

Таким образом, это привело меня к тому, что объект выбора не был определен (или более того, что он был пуст, в зависимости от того, как вы интерпретируете слово « Выбор» ).

Решение

Я играл с этим и в конечном итоге остановил контроль над кодом и добавил проверку вручную, когда обнаружил это:

введите описание изображения здесь

Источник в настоящее время оценивает ошибку .

Это было дымящееся пистолет, в котором объект выбора был не нулевым, это было выпадающее меню A5, значение / значение было пустым!

Для добавления каскадного выпадающего списка требуется, чтобы его родитель имел значение!

Так что это все, что вам нужно сделать:

 pick1.Value2 = "A"; //< set the parent to have a value pick2.Validation.Delete(); //<- this is not really needed, unless you run this again pick2.Validation.Add(XlDVType.xlValidateList, XlDVAlertStyle.xlValidAlertStop, XlFormatConditionOperator.xlBetween, "=INDIRECT(A5)"); 

Если, например, значение в A5 равно B то =INDIRECT(A5) совпадает с =INDIRECT("B") который является таким же, как =B который не является действительной формулой или ссылкой на ячейку.

=INDIRECT("A5") совпадает с =A5

По крайней мере, с Excel 2007 и 2010, =Indirect(A5) без кавычек оценивается до #REF при использовании в ячейке. Я думаю, как этот код передается из C # имеет какое-то отношение к исключению (поскольку он будет оценивать ошибку). Кроме того, использование той же функции при ручном создании проверки также дает сообщение, которое оно оценивает с ошибкой в ​​excel. Редактировать: Хотя я исправляюсь с некоторыми пунктами здесь с большим количеством исследований.

Ожидаемый вывод из Indirect () ожидает строковое значение в формате A1 или R1C1 вместо фактической ссылки на ячейку. Если целевой диапазон не является ссылкой на ячейку, то: A5 был A1.

Согласно MSDN, косвенный () будет вычислять только при открытии файла и в памяти MSDN 151323 . Открытие книги и изменение списка валидации и правильное ее оценивание не означает, что ошибка не существует, когда выполняется код в C #.

  • Как писать данные в excel или csv с разными цветами, размерами, шрифтами, полужирным шрифтом и курсивом, используя c #
  • как переместить весь существующий диапазон excel в одну строку вниз, используя interop в C #
  • Переименование листа Excel не будет работать
  • Могу ли я сохранить форматирование шаблона Excel?
  • Как я могу получить каждое значение внутри анонимного объекта массива в c #
  • Как вернуть массив из Excel udf?
  • Закрытие открытой книги Excel в C #
  • Ошибка Внешняя таблица не находится в ожидаемом формате
  • Метод Looping Findtext из таблицы Gembox
  • Какой самый быстрый способ загрузить лишний лист с более чем 100 000 строк кода на C #?
  • Office Interop считывает неверные значения ячейки
  • Interesting Posts

    Обработка большого количества столбцов в Excel с использованием SAS

    Импорт Excel в Mysql с использованием Spring MVC

    Excel – сравнить два столбца, если совместить вычитание значений из двух других столбцов из совпадающих строк

    Я ищу информацию о внедрении Excel в WinForm. (под C # 4.0)

    Формула Express массива в виде формулы без массива в Excel

    Разница между листами и таблицами в Excel interop

    Найдите последнюю использованную ячейку строки в листе Excel

    Excel cubevalue memberexpression

    apache poi showInPane – параметр в коротком типе данных

    Гибкая гиперссылка Excel не работает в немецком языке

    как перемещать сводную диаграмму с помощью vba

    Можно ли игнорировать предупреждения Excel при создании электронных таблиц с использованием EPPlus?

    Excel VBA Loop с Excel Solver с копированием значения ячейки в зависимости от определенного значения ячейки

    Поиск VBA Goal устанавливает ячейки в 0 без изменения аргумента

    Excel VBA: как найти максимальный / минимальный диапазон при игнорировании ячеек ошибок

    Давайте будем гением компьютера.