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

Автоматизация заполнения и вывода файлов по шаблонам рутинных документов это одна из та областей в отрасли строительства по которой традиционно софт, кроме бухгалтерского, находится на уровне вылизанных поделок, на мой скромный взгляд. Поэтому, развивая тему, приглашаю обсудить те проблемы и возможности, с которыми пришлось столкнуться в процессе реализации на базе 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 способа вывода текста:

            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

       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 есть огромная пропасть, которая заключается в следующем:

Шаблон 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мес). Таким образом получается, что если:

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

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

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

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

Автор: LuchS-lynx

Источник