Хабрахабр

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

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

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

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

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

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

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

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

Есть и минусы такого решения, в частности всплывающие подсказки могут раздражать, но в ситуации, когда на объекте 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 требует перед использованием настроить отображение под конкретный принтер, т.к. В последнем случае не будте автораздвигания строки, в случае переноса текста. Так же перенос строки текста возможен, но только в пределах ячейки/объединенных ячеек. Вам вручную придется заранее определит границы области, которые будут содержать текст, который в свою очередь в них еще должен убраться. Т.е. Зато Вы точно задали границы печати и выводимого текста и уверены, что не съедет информация (но не содержание) с одного листа на другой.

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

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

Для первой строки:

Для последующих:

Теги
Показать больше

Похожие статьи

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Кнопка «Наверх»
Закрыть