Excel - проверить существование Range

[JBOS]

- если в шаблоне Excel есть range с именем "zzz", записать туда значение.
- если такого range в шаблоне нет - не записывать туда ничего, и при этом не падать (!)

Пробую решать :

public void insertValueSafeMode(Bookmark _bookmark, anytype _anyVal, int _workSheet = 1, NoYes _diagnostics = NoYes::No)
  {
      COM         comApplication;
      COM         comWorkSheet;
      COM         comRange;
   
      int         curInfoLine;
   
      if (! m_comDocument)
          throw error(strfmt("@GEE6401", this.getApplicationName()));
   
      try
      {
          curInfoLine = infolog.line();
   
          comWorkSheet   = this.getWorkSheet(_workSheet);
          comApplication = m_comDocument.application();
          comWorkSheet.activate();
   
          if (comWorkSheet && comApplication)
          {
              comRange = comApplication.range(_bookMark);
          }
   
          if (comRange)
          {
              this.insertValueInRange(comRange, _anyVal);
          }
      }
   
      catch
      {
          infolog.clear(curInfoLine);
   
          if (_diagnostics)
          {
              setPrefix('Отсутствует поле в шаблоне Excel');
              warning(strfmt('insertValueSafeMode(%1, %2, %3) failed', _bookmark, _anyVal, _workSheet));
          }
      }
  }

Все очень здорово до тех пор, пока все это не попадает внутрь другого try() - catch(). Тогда, естественно, отрабатывает внешний catch(), и отчет работать перестает..

Any idea?

[ice]

скорее всего во внешнем try catch вы используете транзакцию. не используйте работу с екселем внутри транзакции

[RVS]

Цитата:

Сообщение от ice

скорее всего во внешнем try catch вы используете транзакцию. не используйте работу с екселем внутри транзакции

Уточню вопрос : собственно, убежать от "работы внутри транзакции" скорее всего не получится. Пример - печать Предварительных документов из Заказа на продажу.

Ищется волшебный метод узнать у Excel-я, есть ли в нем такое имя (поле, Range) или его нет. Так, чтобы Excel при этом не упал.

Пока я такого метода не нашел.

[Владимир Максимов]

Способы есть. Причем не один

static void Job_Excel_ExistsRange(Args _args)
{
    COMExcelDocument_RU     excel;
    MSOfficeBookMark_RU     bookMark = 'test3'; // Имя, которое ищем
    COM     comWorkSheet;
    COM     comNames;
    COM     comName;
    int     totalI;
    int     nextI;
    str     strName;
    str     strSearh;

    COM     comApplication;
    COM     comRange;
    boolean existsRange = false;
    str     strRange;
    ;

    excel = new ComExcelDocument_RU();
    excel.newFile('',true);
    excel.visible(true);

    comWorkSheet    = excel.getWorkSheet(1);
    comNames        = comWorkSheet.Names();

    // Для тестирования создаю именованные диапазоны
    comNames.add('test1','=$A$1');
    comNames.add('test2','=$A$2');
//    comNames.add('test3','=$A$3');

    // Вариант 1 - перебор существующих имен для поиска совпадений
    /*
      Коллекция Names есть на всех 3 уровнях: Application, WorkBook, WorkSheet
      Можно использовать перебор по любой коллекции
      Возвращаемое из коллекции имя предваряется именем листа, поэтому надо 
      дополнить искомое имя диапазона именем листа с восклицательным знаком
    */
    totalI = comNames.Count();
    strSearh = comWorkSheet.Name() + '!' + bookMark;
    for (nextI = 1; nextI <= totalI; nextI++)
    {
        comName = comNames.Item(nextI);
        strName = comName.Name();
        if (strName == strSearh)
        {
            existsRange = true;
            break;
        }   // if (strName == strSearh)
    }   // for (nextI = 1; nextI <= totalI; nextI++)

    if (existsRange)
    {
        info('Вариант 1: ' + bookMark + ' - найден');
    }


    // Вариант 2 - "макроподстановка"
    existsRange = false;
    comApplication = comWorkSheet.application();    // m_comDocument.application();
    // Ошибка в Range генерит сообщение об ошибке, которое не подавляется Try...Catch
    // comRange = comApplication.range(bookMark);
    // Поэтому использую "макроподстановку" Evaluate
    comRange = comApplication.evaluate(bookMark);
    // В объекте comRange нет метода Error(), поэтому штатно прочитать ошибку не получается
    // Конвертирую объект в строку и ищу в нем ключевое слово "ERROR"
    strRange = comRange.toString();
    if (strScan(strRange,'ERROR',1,strLen(strRange)) == 0)
    {
        existsRange = true;
    }

    if (existsRange)
    {
        info('Вариант 2: ' + bookMark + ' - найден');
    }


    if (! existsRange)
    {
        info(bookMark + ' - не существует');
    }

}

Только вот, сам факт использования именованных диапазонов особого смысла не имеет. Больно уж специфические способы модификации файла Excel требуются, чтобы использование именованных диапазонов было оправдано. В общем случае - это просто бессмысленно.


PS: Иногда возвращаемое имя диапазона из коллекции Names() может и не предваряться именем листа. В каких случаях имя листа указывается, а в каких - нет лично я не понял

[RVS]

Цитата:

Сообщение от Владимир Максимов

сам факт использования именованных диапазонов особого смысла не имеет. Больно уж специфические способы модификации файла Excel требуются, чтобы использование именованных диапазонов было оправдано. В общем случае - это просто бессмысленно.

Ну, даже и не знаю.. )

Пример : есть восемь дизайнов одного и того же документа. Накладной, СФ. Именованные диапазоны - простейшее решение для того, чтобы одним и тем же кодом распихать данные по любому из этих дизайнов (ИМХО).

Кстати, желание проверить, есть имя в данном шаблоне или нет - из той же оперы, не все клиенты хотят видеть все данные, которые распихиваем..

Спасибо, завтра попробую )

[Владимир Максимов]

Цитата:

Сообщение от RVS

Ну, даже и не знаю.. )

Пример : есть восемь дизайнов одного и того же документа. Накладной, СФ. Именованные диапазоны - простейшее решение для того, чтобы одним и тем же кодом распихать данные по любому из этих дизайнов (ИМХО).

Кстати, желание проверить, есть имя в данном шаблоне или нет - из той же оперы, не все клиенты хотят видеть все данные, которые распихиваем..

Откуда взялось желание - понятно. Сам через это прошел Только вот, описанный пример - это всего-лишь ЧАСТЬ постановки задачи.

Если имеем несколько дизайнов, то, как правило, дело вовсе не ограничивается банальным изменением положения той или иной надписи на листе. Обычно все-таки добавляются некие дополнительные объекты дизайна. Ну, и стоит ли из-за 2..3 общих объектов "городить огород" с именованными диапазонами?

Кроме того, а как это все в коде оформляется-то? Очевидно, через макросы (#define). Ну и в чем проблема для каждого дизайна сделать свой набор макросов, только вместо имен диапазонов так прямо и писать адрес верхней левой ячейки?

#define.BookMark_Title('A1')

comRange = comApplication.range(#BookMark_Title);

[ice]

2Владимир Максимов: с таким подходом редактировать шаблоны становится трудоемко, тк надо всякий раз перебивать макросы, чтоб ячейки не поехали

[Владимир Максимов]

Цитата:

Сообщение от ice

2Владимир Максимов: с таким подходом редактировать шаблоны становится трудоемко, тк надо всякий раз перебивать макросы, чтоб ячейки не поехали

Мой опыт показывает обратное. Факт наличия именованных ячеек усложняет сопровождение подобных отчетов. В лучшем случае - без разницы есть или нет именованные ячейки. Крайне редко, в очень специфических случаях, наличие именованных ячеек упрощает модификацию.

Вы опять забываете тот факт, что модификация отчетов крайне редко заключается всего-лишь в добавлении/удалении одной строчки/столбца. Обычно все гораздо сложнее. И модификация макроса - это крайне незначительная часть всех модификаций.

[Gustav]

Цитата:

Сообщение от ice

2Владимир Максимов: с таким подходом редактировать шаблоны становится трудоемко, тк надо всякий раз перебивать макросы, чтоб ячейки не поехали

А надо вообще попробовать кардинально перестроить работу, примерно так.

В любом шаблоне делаем два листа: один - собственно шаблон (раскрашенный и расфуфыренный), второй - служебный, обычный лист без всякого форматирования. На второй выводится одна единственная строка - например, при помощи CopyFromRecordset. Без всяких поисков именованных или неименованных диапазонов, обычно хаотично разбросанных по парадному шаблону.

Вот. А на первом прописываете формулы на ячейки этой единственной строки второго листа. Перед самым показом пользователю для первого листа делаете копию всего используемого диапазона первого листа и специальную вставку "Только значения", чтобы зафиксировать значения формул. Второй лист больше не нужен, удаляем его. Всё это происходит втёмную, до visible = true.

Соответственно, если вы позднее вносите изменения в дизайн парадного шаблона, добавляя/удаляя колонки/строки, то формулы в ячейках сами перенастраиваются (ну, собственно как и имена, созданные по Ctrl+F3).

Кстати, передачу данных для не очень больших табличных частей тоже можно включать в эту единственную строку, выгружаемую из Аксапты.

На эту тему уже бывали беседы на форуме: Копирование в Excel

[RVS]

Цитата:

Сообщение от Владимир Максимов

Если имеем несколько дизайнов, то, как правило, дело вовсе не ограничивается банальным изменением положения той или иной надписи на листе. Обычно все-таки добавляются некие дополнительные объекты дизайна. Ну, и стоит ли из-за 2..3 общих объектов "городить огород" с именованными диапазонами?

Задача была чуть уже - дать возможность добавлять в шаблоны (которых 8) поля. Шаблоны эти сделал Микрософт (DAX2009), это накладная и фактура. И "общего" в них сильно больше, чем "2-3 общих объекта". И диапазонов там и без меня хватало.

Цитата:

Сообщение от Gustav

А надо вообще попробовать кардинально перестроить работу, примерно так.

В любом шаблоне делаем два листа: один - собственно шаблон (раскрашенный и расфуфыренный), второй - служебный, обычный лист без всякого форматирования.

Замечательная техника. Есть такой Отчет о контролируемых сделках, который - о ужас - захотели в Аксапте. Поскольку делаем сами, именно эту технику и использовали.

А переписывать отчеты за MS я как-то не готов

[Alexanderis.ua]

Цитата:

Сообщение от Владимир Максимов

Мой опыт показывает обратное. Факт наличия именованных ячеек усложняет сопровождение подобных отчетов.

Это вы наверно не работали с украинским НДСом.
Скорость (а главное внезапность!) изменения шаблонов для печати налоговых и той же месячной декларации просто поражает воображение.
И в основном это как раз добавление/перемещение одной ячейки/строчки. Только именованными ячейками и спасаемся.
Собственно процентов 90 последних изменений даже без девелопера решались - просто перетягиванием имеющихся ячеек местами.

Так что, как говорил Ржевский, - "случаи бывают разные".

[Владимир Максимов]

Alexanderis.ua

А Вас не удивлят сама постановка вопроса в данной теме? Если с именованными ячейками все так замечательно, то зачем вообще проверять факт их существования?

[grishin]

Мы около трех лет назад написали машинку :
Опрашиваем шаблон Excel какие именованные ячейки есть.
Потом алгорим выводит в данные ячейки нужные значения
Имеем один универсальный алгоритм на любое количество отчетов.
Это дало возможность не программерам каждый раз следить за отчетами,
а самим пользователям корректировать шаблоны как им надо.
Они просто ставят оговоренные метки в нужное место шаблона.
Есть определенный алгоритм разделения меток шапки и табличной части.

Для тех кто будет это использовать хочу предупредить, что есть однако одна неприятность при удаление именованных ячеек в шаблоне.
Они не удаляются из списка ActiveWorkbook.Names и при опросе передаются в Axapta, но при выводе данных Axapta вываливается с ошибкой. На данный момент решили данную проблему созданием VBA макроса, обеспечивающего удаление "неправильных" именнованных ячеек из ActiveWorkbook.Names

[sukhanchik]

Цитата:

Сообщение от grishin

Имеем один универсальный алгоритм на любое количество отчетов.
Это дало возможность не программерам каждый раз следить за отчетами,
а самим пользователям корректировать шаблоны как им надо.
Они просто ставят оговоренные метки в нужное место шаблона.
Есть определенный алгоритм разделения меток шапки и табличной части.

Любая универсальность усложняет поддержку. Я тоже такую машинку писал - для Word/Excel. Также с разделением шапки и табличной части. Правда не заморачивался выковыриванием именованных ячеек, а просто полагался на корректность настроек машинки, ибо сложность решения ошибки с лихвой компенсировалась ее некритичностью.

Из плюсов такого подхода:

• Кажущаяся универсальность на этапе проектирования (=можно выбить бюджет)
• Возможность заполнения шаблона по принципу 80/20, т.е. легкое автоматическое заполнение 80% полей в шаблоне
• Возможность корректировки шаблона Word/Excel пользователем

Из минусов:

• Универсальности нет. Вот нет и все. Обработкой каждого отчета все равно занимается свой класс, несмотря на настройки. Настройки могут помочь в выводе информации, но не помогут в ее формировании. Конечно классы являются наследниками, иерархия, почти как наследники RunBase, но ... все же каждый класс индивидуален. Особенно это заметно, когда используется табличная часть, т.к. там используется цикл, уникальный для каждого отчета.
• Сложность настройки. В качестве источника данных для именованных диапазонов могут быть поля таблиц и дисплей-методы (у меня было так). Даже если не брать в расчет дисплей-методы - то какими же знаниями должны обладать пользователи, чтобы уметь выбрать правильное поле при создании настройки? Особенно учитывая тот факт, что даже в стандартном функционале поля именуются так - что пока не узнаешь, как они называются в АОТе - не поймешь, какое поле требуется выбирать. При этом пользователь обычно не видит, какое поле в терминах АОТа используется в обычной форме, хотя может и знать - что ему надо именно это поле. Про дисплей-методы вообще можно забыть - тут ориентация нулевая. Кстати - при настройке - каким образом облегчается жизнь настройщика? Или он должен знать все поля и методы на таблицах?
• Неуниверсальность настройки. Представьте себе - вам нужно вывести в одном случае фразу из БД (например, название региона) в именительном падеже, а в другом - в родительном. Это будет 2 источника данных? А настройщик не запутается? А если нужно вывести 3 суммы - без НДС, с НДС и НДС - тоже в именительном и родительном падеже? Без программирования (даже минимального) не обойтись. А если программировать - то зачем тогда себе усложнять жизнь настройками?
• Переносимость настройки. Программный код переносится легко через XPO. Легко - означает в один клик по проекту. А при импорте - можно сравнить - что поменялось. Плюс есть система контроля версий кода. Обладает ли этим утилита переноса данных? (Группа определений или еще какая?). А можно ли "оптом" переносить настройки ? Например, также легко, как модели в АХ 2012 или приложение в АХ 2009.
• Отладка настроек. В состоянии ли настройщик адекватно проанализировать - что у него не получается в настройке? Или должен постоянно звать программиста? Если должен звать - то см. пункт выше - зачем тогда себе усложнять жизнь настройками?
• При подготовке шаблона нужно уделять тщательное внимание именованию ячеек/закладок и следить за тем, чтобы их названия были понятны настройщику. Иначе все опять скатится до программиста.

Так что тут палка как говорится о двух концах...

[Alexanderis.ua]

Цитата:

Сообщение от Владимир Максимов

Alexanderis.ua

А Вас не удивлят сама постановка вопроса в данной теме? Если с именованными ячейками все так замечательно, то зачем вообще проверять факт их существования?

Зачем проверять наличие записи в таблице SQL если он такой замечательный?

Я не говорил, что все замечательно. Моя мысль проста - иногда использование именованых ячеек оправдано. Просто небольшая контра к Вашей категоричности с примером из жизни.

Вот еще один пример - оттуда же. С 16 декабря 2011 вид НН изменился. Добавились новые ячейки и перетасовался порядок. Старые НН нужно печатать в прошлом варианте.
Сделали 2 шаблона и на всякий случай проверяем наличие ячеек - чтобы не плодить кучу условий на печати - если ячейка есть, то напечатается.
Это к вопросу о "зачем вообще проверять".

[Владимир Максимов]

Цитата:

Сообщение от Alexanderis.ua

Я не говорил, что все замечательно. Моя мысль проста - иногда использование именованых ячеек оправдано. Просто небольшая контра к Вашей категоричности с примером из жизни.

Лично я писал, что выгоды от использования именованных ячеек значительно меньше, чем кажется на первый взгляд. Крайне редки случаи, когда их использование действительно оправдано. Это одна из тех вещей, которая хороша для презетаций, но не очень-то удобна в реальной практике. Вроде древовидных справочников.

Цитата:

Сообщение от Alexanderis.ua

Вот еще один пример - оттуда же. С 16 декабря 2011 вид НН изменился. Добавились новые ячейки и перетасовался порядок. Старые НН нужно печатать в прошлом варианте.
Сделали 2 шаблона и на всякий случай проверяем наличие ячеек - чтобы не плодить кучу условий на печати - если ячейка есть, то напечатается.
Это к вопросу о "зачем вообще проверять".

Т.е. вместо того, чтобы сделать банальное

if ( <= ...)
{
   val1 = ...;
   Range1.Value(val1);
}
else
{
   val2 = ...;
   Range2.Value(val2);
}

Вы сделали

val1 = ...;
Range1.Value(val1);
val2 = ...;
Range2.Value(val2);

Ну, пока значение, записываемое в ячейку - простое и общее количество ячеек - небольшое, это еще работает. Но с увеличением количества версий Вы сильно задумаетесь хотя бы об отдельных методах для заполнения каждого шаблона. В перспективе - иерархии классов. Нечто вроде

if ( <= ...)
{
   this.Method1();
}
else
{
   this.Method2();
}

Если при модификации нового шаблона Вы вынуждены постоянно "оглядываться" на заполнение старого шаблона, то такую конструкцию слишком тяжело сопровождать.

[Alexanderis.ua]

Цитата:

Сообщение от Владимир Максимов

Но с увеличением количества версий Вы сильно задумаетесь хотя бы об отдельных методах для заполнения каждого шаблона. В перспективе - иерархии классов.

Лучшее - враг хорошего.
Изменения самой НН не радикальны. Только расположение в основном, форматирование какое-то. И старая версия (которая до сих пор одна) не меняется уже.
В таких условиях городить иерархию классов или отдельные методы - просто не рационально.

И я опять повторюсь - я с Вами не спорю. В общем случае нужно очень хорошо подумать, стоит ли применять именованные ячейки.
Но категорически отказываться от них я считаю тоже не правильно.

DIXI