В продолжении темы автоматизации вывода файлов по шаблону. Excel +8



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

Со времени предыдущей статьи прошло уже пол года. За это время при помощи этой заготовки была разработана текстовая часть Исполнительной документации и сдана Заказчику. По итогам работы и отзывам редких участников в файл были внесены следующие правки, о которых я бы хотел поговорить и это 3 большие темы:

  1. Эстетика и юзабилити
  2. Оптимизация кода + нововведения
  3. Структура и связи

Итак — вперед!!!

1. Эстетика и юзабилити


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

1. Примечание
2. Вкладка «Данные» -> пункт меню «Проверка данных» -> вкладка «Сообщение для ввода»

Как выглядят всплывающие подсказки


Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 15" мониторы на ноутбуках с разрешением 1366?768 это разумный компромисс, что бы рабочая область была как можно больше.

Если внимательно проанализировать данные, то окажется что в таблице будут ячейки 3х типов:

  1. ячейки в которые непосредственно необходимо вводить новую текстовую информацию;
  2. ячейки, значение которых может принимать значение из ограниченного диапазона, введенного заранее, например: ФИО и должность подписантов;
  3. ячейки в которых прописаны формулы, например есть часть данных которая будет повторяться из акта в акт и такую информацию достаточно ввести один раз, например: наименование объекта, участок, организация и т.п.; либо формулы призванные реализовать технические возможности, например: переноса строки, подтягивание объемов работ, регалий по ФИО и т.п.

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

Private Sub Worksheet_Activate()
    Worksheets("Ваш Лист").EnableOutlining = True
    Worksheets("Ваш Лист").Protect Password:="111"
End Sub
Private Sub Worksheet_SelectionChange(ByVal Target As Range)
  If Application.CutCopyMode = xlCut Then
    Application.CutCopyMode = False
  End If
End Sub

Здесь первая процедура постоянно будет защищать лист при помощи пароля 111, вторая будет блокировать функционал вырезать-вставить. Надо ли говорить, что это все работает только при включенных макросах, но с другой стороны без них и файл на 100% функционировать не будет.
Для случаев же п.2 разумно завести лист где столбцы будут содержать меняющиеся значения, прописать в них ссылки на диапазоны, присвоить им имена, т.е. на вкладке «Формулы» -> «Диспетчер имен» каждому диапазону присвоить имена и через вкладку «Данные» -> пункт меню «Проверка данных» -> вкладка «Параметры» -> условие проверки — «Список» реализовать выпадающее меню.

И, конечно, не забывайте ставить условия форматирования цветом, например для случаев, когда заполнены все необходимые строки в столбце через «Условное форматирование», например формула условного форматирования закрашивает ячейку, если следующие ячейки под ней содержат текст: =И(ДЛСТР(E5)>0; ДЛСТР(E6)>0)

2. Оптимизация кода + нововведения


Начать придется издалека, а именно вернуться к вопросу о реализации механизма заполнения шаблона. Если Вы решите заполнить шаблон в формате Excel и в формате Word, то это будут совершенно 2 разных механизма. В основе своей в файл Excel пишутся значения в конкретные ячейки файла или диапазоны ячеек и имеют привязку вида (у, х) (не спрашивайте почему у них строка идет впереди столбца при адресации — не знаю), например: Worksheet.Cells(y, x) = k. Отсюда же и первая мысль, что заполнять Excel-шаблон можно либо явным образом, т.е. непосредственно весь макрос будет содержать что откуда берется и куда закладывается, но что если придется вносить изменения в таблицы данных или выйдет новая форма шаблона? Отсюда вторая идея реализации, код которой описан в первой статье — это парсинг некоторых символов, которыми сперва заполняется массив, а так же в свою очередь содержит файл шаблона в нужных местах. Затем в каждой строке шаблона ищется совпадение с элементами массива поочередно, если совпадение есть, то порядковый номер массива привязан к строке таблицы откуда берутся данные, а столбец берется с листа в котором мы указываем какие именно акты мы хотим вывести. Итого несколько вложенных циклов, что накладывает ограничения на форматирование шаблона Excel, чем проще — тем лучше, потому что чем больше ячеек парсить — тем дольше будет происходить заполнение шаблона данными.

По многочисленным просьбам мною была интегрирована возможность вывода в шаблон формата Word, и здесь на самом деле есть 2 способа вывода текста:

1. Это через функционал закладок,
когда мы так же считываем массив управляющих кодов, вручную прописываем их в шаблоне через «Вставка» -> «Закладки» и дальше просто прогоняем макросом присваивая закладке данные из соответствующей ей ячейке в файле Excel.

            Rem -= Открываем файл скопированного шаблона по новому пути и заполняем его=-
            Set Wapp = CreateObject("word.Application"): Wapp.Visible = False
            Set Wd = Wapp.Documents.Open(ИмяФайла)
            
            NameOfBookmark = arrСсылкиДанных(1)
            ContentOfBookmark = Worksheets("Данные для проекта").Cells(3, 3)
            On Error Resume Next
            UpdateBookmarks Wd, NameOfBookmark, ContentOfBookmark
            Dim ContentString As String
            For i = 4 To Кол_воЭл_овМассиваДанных Step 1
                If Len(arrСсылкиДанных(i)) > 1 Then
                   NameOfBookmark = arrСсылкиДанных(i)
                   ContentString = CStr(Worksheets("БД для АОСР (2)").Cells(i, НомерСтолбца))
                   If ContentString = "-" Or ContentString = "0" Then ContentString = ""
                   ContentOfBookmark = ContentString
                   On Error Resume Next
                   UpdateBookmarks Wd, NameOfBookmark, ContentOfBookmark
                End If
            Next i
             
            Rem -= Обновляем поля, что бы ссылки в документе Word так же обновились и приняли значение закладок, на которые ссылаются =-
            Wd.Fields.Update
             
            Rem -= Сохраняем и закрываем файл =-
            Wd.SaveAs Filename:=ИмяФайла, FileFormat:=wdFormatXMLDocument
            Wd.Close False: Set Wd = Nothing

Sub UpdateBookmarks(ByRef Wd, ByVal NameOfBookmark As String, ByVal ContentOfBookmark As Variant)
    On Error Resume Next
    Dim oRng As Variant
    Dim oBm
    Set oBm = Wd.Bookmarks
    Set oRng = oBm(NameOfBookmark).Range

    oRng.Text = ContentOfBookmark
    oBm.Add NameOfBookmark, oRng
End Sub

Здесь вынесена в отдельную процедуру обращение к закладке и arrСсылкиДанных(i) — это массив который содержит управляющие символы. Издержки метода, если Вам потребуется сослаться на значение Закладки в другом месте, например дату нужно использовать в заголовке и напротив фамилии каждого подписанта, то необходимо использовать в шаблоне Меню «Вставка» -> пункт меню «Перекрестная ссылка» -> Тип ссылки: «Закладка», Вставить ссылку на: «Текст закладки» и снять галочку «Вставить как гиперссылку». Что бы это отобрадзилось корректно не забудте обновить в конце макроса перед выводом поля Wd.Fields.Update

2. Если рисовать таблицы средствами Word, то к ним можно обращаться с адресацией в ячейку
       Rem -= Заполняем данными таблицы ЖВК =-
       Dim y, k As Integer
       Let k = 1
       For y = Worksheets("Титул").Cells(4, 4) To Worksheets("Титул").Cells(4, 5)
           Wd.Tables(3).cell(k, 1).Range.Text = Worksheets("БД для входного контроля (2)").Cells(6, 4 + y)
           Let k = k + 1
       Next y
       End With       

Здесь нужно обратить внимание, что у каждой таблицы в Word есть свой внутренний номер, методом нехитрого перебора Вы найдете нужный, а дальше принцип тот же, что и в Excel.

Между выводами в файлы форматов Word и Excel есть огромная пропасть, которая заключается в следующем:

Шаблон Excel требует перед использованием настроить отображение под конкретный принтер, т.к. фактическая область печати разнится от модели к модели. Так же перенос строки текста возможен, но только в пределах ячейки/объединенных ячеек. В последнем случае не будте автораздвигания строки, в случае переноса текста. Т.е. Вам вручную придется заранее определит границы области, которые будут содержать текст, который в свою очередь в них еще должен убраться. Зато Вы точно задали границы печати и выводимого текста и уверены, что не съедет информация (но не содержание) с одного листа на другой.

Шаблон Word при настройке автоматически переносит текст на последующую строку, если он не убрался по ширине ячейки/строки, однако этим самым он вызывает непрогнозируемый сдвиг текста по вертикали. Учитывая тот факт, что по требованиям к Исполнительной документации в строительстве ЗАПРЕЩЕНО один акт печатать на 2х и более листах, то это в свою очередь так же рождает проблемы.

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

Для первой строки:
{=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ЕСЛИ(ДЛСТР('Данные для проекта'!$C$3)<106;'Данные для проекта'!$C$3; ПСТР('Данные для проекта'!$C$3;1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3;1;105); СТРОКА($1:$10));))));"-")}

Для последующих:
{=ЕСЛИОШИБКА(ЕСЛИ($F$20<>"-"; ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105-ПОИСКПОЗ(" *"; ПРАВСИМВ(ПСТР('Данные для проекта'!$C$3; СУММ(ДЛСТР(F$1:F1))+1;105); СТРОКА($1:$10));)));"-")}


Здесь используется принцип массивов, т.е. вводится такой текст по Ctrl + Shift + Enter, а не обычному Enter. Сами формулы располагаются в ячейках F1 и F2. 'Данные для проекта'!$C$3 — ссылка на наименования объекта, длина текста которого более 105 символов. Перенос организуется в случае превышения длины текста в 105 символов.

Еще одним нововведением стал общий реестр, а так же контроль списания материалов по актам АОСР, но здесь ничего нового, просто парсинг соответствующих строк в свяске ИНДЕКС + ПОИСКПОЗ, которые расписаны во многих мануалах.

3. Структура и связи


Но мой пост так бы и остался рядовым постом с очередной игрой в изобретание велосипеда инструментами, которые рассчитаны на совершенно другое, если бы ни одно НО(!) Месячно-суточный график.



Идея о том, что можно именно на него много чего повесить, например заполнение Общего журнала работ в части Раздела 3 — наименование работ по датам, очередность и необходимость Актов освидетельствования скрытых работ и не только — завладела моими мыслями. Обычно в Excel закрашивают даты, в зависимости от диапазонов дат — начало и конец, но не на стройке!!! На стройке в календарном графике пишут объемы, а в зависимости от того с какой даты напротив наименования работ стоят объемы и по которую — получаются диапазоны дат отчетных периодов. На скриншоте серым помечены объемы попадающие в систематизированные отчетные периоды (1мес). Таким образом получается, что если:

  • детализируем и составим очередность работ по разделу проекта и пропишем их в порядке очереди, то мы получим очередность работ;
  • на календарном графике обозначим отчетные периоды (серым) и организуем суммирование объемов по отчетным периодам по каждой строке — мы получим объемы работ для АОСР и иных актов;
  • субдиапазоны работ для каждого отчетного периода можно забрать макросом или формулой.

Таким образом получается, что при помощи МСГ можно составлять документацию… А это упрощается и визуальное восприятие и визуальный контроль объемов работ.

Надеюсь, что Вам было интересно. Попробовать программу можно по ссылке

Спасибо за внимание.

Вы можете помочь и перевести немного средств на развитие сайта



Комментарии (15):

  1. BasiC2k
    /#11371086

    Для внесения данных в шаблоны Word удобно использовать закладки.
    Для внесения данных в шаблон Excel использую именованные диапазоны.
    Если перед внесением данных проверять наличие закладки/именованного диапазона, то документ будет заполняться без ошибок. Изменение формата шаблона при этом ни на что не влияет. Закладку/именованный диапазон можно перенести в нужную часть нового шаблона.

    • LuchS-lynx
      /#11371944

      Можно посмотреть Ваш пример реализации, если конечно это не является секретом? ;)

      • BasiC2k
        /#11371960

        В Word создании шаблона создаются закладки. Выделяется, например заголовок в тексте, далее: «Вставка — Закладки». Указывается название закладки, например: «Title»
        В проекте VB.NET после открытия шаблона указывается:
        If wdDoc.Bookmarks.Exists(«Title») Then wdDoc.Bookmarks(«Title»).Range.Text = sValue
        После этого, на месте месте закладки будет значение sValue

        В Excel при создании шаблона указывается именованный диапазон. Выделяется нужная ячейка, ПКМ — присвоить имя. Указывается, например: Title.
        В проекте VB.NET после открытия шаблона указывается:
        xlBook.Range(«Title»).Value = sValue
        После этого, в ячейке будет значение sValue

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

        • LuchS-lynx
          /#11371996

          Правильно ли я понимаю, что в случае именованных диапазонов Один именованный диапазон (sValue) — одна ячейка данных? Есть ли ограничения у метода, т.е. как можно собирать в шаблоне строки из нескольких значений? Возможно ли использовать значение диапазонов в существующем тексте? Например как организовать итоговый вывод фразы содержащейся в одной ячейке: «Акт АОСР составлен в sValue экземплярах»?

          • BasiC2k
            /#11372026

            Если не ошибаюсь, при присвоении имени диапазону ячеек, значение будет присваиваться только левой верхней ячейке. Если в Excel нужно вставить массив данных, то тут нужно смотреть — откуда берутся данные. Если, например, используется ADO, то у Recordset есть замечательный метод Range.CopyFromRecordset который вставляет результат выборки в нужное место листа.
            Для присвоения строки из нескольких значений разумно в коде собрать саму строку, а потом — присвоить её ячейке.
            По сути, эти вопросы не являются такими сложными как Вам кажется )

            • LuchS-lynx
              /#11372038

              я просто пытаюсь понять ограничения метода =) потому что когда составлял первоначальное ТЗ, то планировалась потенциальная возможность вставки текста куда угодно, отсюда у меня в выводе в формате Эксель перебор текстовых строк (в предыдущей статье приложен макрос с описаниями). С Вордом такой проблемы нет, потому что там достаточно расположить в нужном месте закладку и… все хорошо.
              В принципе это не всегда актуально, а Ваш метод достаточно изящен. Если Вы не возражаете, что я опробую Ваш вариант для вывода в Эксель файл )

          • StrangerInTheKy
            /#11372034

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

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

            P. P. S. Правда, вам с большой вероятностью очень скоро захочется большего, вы освоите какой-нибудь «взрослый» мейнстримный язык (а то и два-три), станете полноценным разработчиком, а офисную автоматизацию будете вспоминать субботними вечерами в кругу семьи и друзей.

            P. P. P. S. Ах, да. Именованным диапазоном может быть любой диапазон. А «диапазон» — это, если грубо, любая комбинация ячеек, которую можно выделить одновременно (мышью с шифтом и контролом).

            • LuchS-lynx
              /#11372052

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

  2. air_squirrel
    /#11371658

    Добрый, старый, актуальный VB:)
    Спасибо за статью, возьму на заметку.

  3. DaMaNic
    /#11371798

    Богата земля русская самородками ))
    Есть такая же VB-шная штука, Blank-RZ, программист тоже из Нижнего Новгорода.

    • LuchS-lynx
      /#11371928

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

  4. DaMaNic
    /#11371826

    И да ) в свое время для исполнительной документации проекта IRKA, Нижегородская область, г.Кстово, я тоже делал систему исполнительной документации ) куча VB. Но там был немного другой принцип — база данных по всему проекту, от входа до выхода, из которого на каждую линию «выдергивалась» информация в виде уже исполниловки.
    Каждый элемент на скриншоте — это выбираемый из базы или вычисляемый на ее основании текст или число.
    image
    image
    image
    image
    image

    • LuchS-lynx
      /#11371936

      Лепо )
      Здесь тонкий момент, дело в том что можно решать задачу «влоб» и дальше, вставив в книгу листы «БД для ...», можно закрывать по 100500 форм актов, однако меня сейчас занимает мысль — как бы сгруппировать это все, что бы составив рутинную методику кол-во ввода информации уменьшить, ведь чем больше вводить информации — тем больше вероятность ошибиться.

      • DaMaNic
        /#11371966

        Так и было сгруппировано. Была единая база исходных данных, для удобства разбитая на несколько таблиц Excel (как это принято в реляционных базах данных), редактирование производилось в 90% случаев копипастом с последующим редактированием важных данных (например, стык угловой или прямой — это только человек может определить), и собственно все.
        image
        Потом вводится номер линии, нажимается одна кнопка и вжух — готова исполниловка так как это выше выглядит на эту линию.

  5. xdenser
    /#11373102

    хм… а я уже на следующем уровне… от макросов отказался,
    Apache POI — безопасно, быстро, ни Word ни Excel не нужен для генерации документов
    но посколько переделывалось из генератора на макросах и надо было совместимость, то синтаксис шаблонов так же основан на именованных диапазонах в Excel и закладках в Word