Введение.
На мой взгляд ПК появился как инструмент для решение принципа автоматизации рутинных процессов и в этом он чертовски хорош. Кроме того развитие функциональных программ есть история создания инструментов для реализации перевода авральных задач в рутинные действия, как следствие углубление разделения труда. Однако есть области, в т.ч. и документооборота, где на сегодняшний день ПК используется именно как печатная машинка в т.ч. и потому, что серьезных разработок не велось в силу того, что оклады людей сидят в накладных расходах и, как следствие, не велики, что не скажешь о возрастающих объемах документооборота.
Итак, поговорим про строительство, конкретно про составление Исполнительной документации (далее ИД).
Большая часть работ закрывается по форме Акта освидетельствования скрытых работ (утвержденным приказом Федеральной службы по экологическому, технологическому и атомному надзору от 26 декабря 2006 года N 1128 (В редакции, введенной в действие с 6 марта 2016 года приказом Ростехнадзора от 26 октября 2015 года №42. Далее АОСР).
Исходные данные для автоматизации.
По-этому возьмем форму АОСР за основу. Итак, у нас есть шаблон документа, в который заносится следующая информация:
— постфикс номера акта;
— наименование объекта капитального строительства;
— юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
— перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
— наименование выполненных работ;
— сроки выполнения работ;
— перечень работ, входящих в выполненные работы;
— ссылки на НТД и разделы проекта/тех.документации;
— ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
— перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)
Мысли о принципиальных методах решения задачи
Итак, в первом приближении, можно просто создать наглядную таблицу, в которой назначив соответствующие однотипные поля каждому акту мы получим наглядную портянку схемы выполнения работ на объекте. И в этом нет ничего нового. Итак, нам необходимо связать форму с ячейками в таблицах данных и здесь есть 2 варианта:
1. Слияние с файлом Word
2. Заполнение при помощи макросов шаблона на базе Excel.
Каждый из этих способов имеет свои плюсы и минусы, но т.к. слияние производит замену в реальном времени, то мною было принято решение выбрать второй пункт, который в реальном времени слияние не обеспечивает и каждый раз необходимо будет осуществлять вывод актов, в случае корректировки данных, заново. Это связано с тем, что мне зачастую необходима история моих действий.
Итак, теперь мы упираемся в 2 задачи:
1. Заполнение шаблона на основе табличных данных
2. Какие поля достаточно ввести один раз, какие будет меняться время от времени и какие поля будут отличаться в каждом акте.
Решая задачу №2 мы вынесем на отдельный лист те данные, которые будут едины в рамках объекта/раздела проекта строительства — это:
— юридические данные об участниках строительства (Застройщик или Заказчик; Лицо, осуществляющее строительство; Лицо, осуществляющее подготовку проектной документации;
Лицо, осуществляющее строительство, выполнившее работы, подлежащие освидетельствованию; иные лица.)
— перечень лиц с наименованием организаций, должностей и приказов подтверждающих их полномочия;
На текущем листе заполним лишь один раз, а для остальных актов просто проставим ссылки на эти значения:
— Постфикс к номеру акта;
И поля, которые будут меняться в каждом акте:
— сроки выполнения работ;
— перечень работ, входящих в выполненные работы;
— ссылки на исполнительные схемы, акты испытаний (если таковые требуются);
— перечень используемых материалов с сылками на документы подтверждающими их соответствие (паспорта, сертификаты и т.п.)
Теперь об удобствах, если записать ФИО ответственных лиц, их организацию, приказ о назначении с датой, то при помощи инструмента «проверка данных» можно занести их фамилии в спойлер, а формулой подтягивать их регалии.
=ЕСЛИОШИБКА(ДВССЫЛ(СЦЕПИТЬ("'Данные для проекта'!";АДРЕС((ПОИСКПОЗ(E30;'Данные для проекта'!$G$15:$G$34;0))+14;6)));"-")Т.е. на листе 'Данные для проекта' в диапазоне $G$15:$G$34, в 6м столбце ищем значение в ячейке Е30, и как только находим нехитрым методом преобразуем в адрес, который будет преобразован в ссылку формулами.
Новая проблема уже упирается в длину строки, если использовать шрифт Times New Roman №10, то длина текста, которая будет выводится на печать, не превышает 105 символов. Т.е. мы упираемся в необходимость создавать костыль для переносов. Итак код функции на VBA:
Function PatrOfString(StringOfTable As String, Nnumber As Byte) As String
Dim МассивБлоков(1 To 10) As String
Dim i As Integer '
Dim j As Integer '
Dim k As Integer '
Dim p As Integer '
For i = 1 To 10
Let МассивБлоков(i) = " "
Next i
Let k = 1
Let p = Len(StringOfTable)
Let p1 = Len(StringOfTable)
For i = 1 To Round(Len(StringOfTable) / 105) + 1 Step 1
If p > 0 And p < 105 Then
If k <= p1 Then Let МассивБлоков(i) = Mid$(StringOfTable, k, p)
Else
If Mid(StringOfTable, k, 1) = " " Then
If k <= p1 Then Let МассивБлоков(i) = Mid$(StringOfTable, k, 105)
Let p = p - 105
k = k + 105
Else
j = 105 * i
If j - k >= 105 Then
j = k + 105
End If
Do
j = j - 1
Loop While Mid$(StringOfTable, j, 1) <> " "
Let МассивБлоков(i) = Mid$(StringOfTable, k, j - k + 1)
Let p = p - (j - k + 1)
Let k = j + 1
End If
End If
Next i
If Nnumber - 1 > 0 Then
If МассивБлоков(Nnumber) = МассивБлоков(Nnumber - 1) Then МассивБлоков(Nnumber) = " "
End If
PatrOfString = МассивБлоков(Nnumber)Т.е. мы сперва забираем текст, потом вырезаем 105 символов, ищем с конца первый символ пробел, как его находим, то закидываем в первую строку массива текст длиной с первого символа, по номер найденного пробела. Затем продолжаем операцию до тех пор пока текст не закончится или не забьется выводной массив. На данном этапе он ограничен памятью в 10 строк. Затем мы выводим по ссылке содержание нужной строки из 1-10. Из минусов решения — забивается память, и для каждого нового запроса осуществляется пересчет заново. Но костыль работает.
Теперь вывод в типовой шаблон АОСР. Есть опять 2 варианта, либо вручную прописывать сопоставлению столбцу(/строки в случае горизонтально расположенных данных под каждый акт), то это будет долго и ресурсоемко подгонять каждый новый шаблон или изменения под таблицы данных. Потому делаем оптимизацию. Данные на каждый один акт будут располагаться вертикально, а сопоставление управляющей комбинации символов (на латинице, т.к. сами акты сплошь на кириллице) будут строки с информацией в этих актах, таким образом в двойном вложенном цикле поиском в тексте управляющих символов мы сопоставим нужное значение из колонки.
Do
wb.Worksheets("Пример акта входного контроля").Copy after:=Worksheets(Worksheets.Count)
Set новыйЛист = wb.Worksheets(Worksheets.Count)
For x = 1 To 15 Step 1 ' Перебираем столбцы в листе "Пример акта входного контроля"
For y = 1 To 71 Step 1 ' Перебираем строки в листе "Пример акта входного контроля"
If Sheets(новыйЛист.Name).Cells(y, 20) = 1 Then
Let k = CStr(Sheets(новыйЛист.Name).Cells(y, x)) ' Ищем только если в ячейке что-то есть
If k <> "" Then
For i = 1 To Кол_воЭл_овМассиваДанных Step 1
Let k = Replace(k, arrСсылкиДанных(i), Worksheets("БД для входного контроля (2)").Cells(i, НомерСтолбца))
Next i
новыйЛист.Cells(y, x) = k
End If
End If
Next y
Next x
' Если Вы захотите добавить новые данные для автозаполнения, продолжайте этот список.
' Ячейки имеют координаты формата Cells(3, 2), где 3 - пример номера строки, 2 - пример номера столбца
' Чтобы легко узнавать номер столбца, можно включить стиль ссылок R1C1
' (Файл -> Параметры -> Формулы -> галочка на поле "Стиль ссылок R1C1")
' либо задавать координаты в формате Cells(1, "A"), где 1 - пример номера строки, "А" - пример буквенного обозначения столбца
Rem -= Уточняем имя файла и текущий путь к папке откуда запущен макрос =-
Let ИмяФайла = ИмяФайла + CStr(Worksheets("БД для входного контроля (2)").Cells("1", НомерСтолбца)) + "-"
Let ИмяФайла = ИмяФайла + CStr(Worksheets("БД для входного контроля (2)").Cells("2", НомерСтолбца)) + ".xlsx"
НовыйПуть = Replace(ThisWorkbook.FullName, ThisWorkbook.Name, ИмяФайла)
Application.DisplayAlerts = False ' выключаем вывод предупреждений
Sheets(новыйЛист.Name).Copy ' Копируем текущий лист в новую книгу
ActiveWorkbook.SaveAs Filename:=НовыйПуть, _
FileFormat:=51
ActiveWindow.Close
Sheets(новыйЛист.Name).Delete ' Удаляем созданный лист
Application.DisplayAlerts = True ' Включаем вывод предупреждений обратно
Let НомерСтолбца = НомерСтолбца + 1
Loop While НомерСтолбца <= КонечныйНомерСтолбца
End SubНу и последний момент на данном этапе — мы упираемся в производительность системы и при большом кол-ве актов их вывод будет занимать часы. Дабы ускорить процесс я использую следующий вариант: Копирую содержимое листа с данными в новый лист макросом, он получает в своем имени циферку (2), затем запускается еще один макрос, ускоряющий Excel, но отключающий ряд функционала:
'Ускоряем Excel путём отключения всего "тормозящего"
Public Sub AccelerateExcel()
'Больше не обновляем страницы после каждого действия
Application.ScreenUpdating = False
'Расчёты переводим в ручной режим
Application.Calculation = xlCalculationManual
'Отключаем события
Application.EnableEvents = False
'Не отображаем границы ячеек
If Workbooks.Count Then
ActiveWorkbook.ActiveSheet.DisplayPageBreaks = False
End If
'Отключаем статусную строку
Application.DisplayStatusBar = False
'Отключаем сообщения Excel
Application.DisplayAlerts = False
End SubА после вывода всех данных из форм я прогоняю аналогичный макрос где тем же самым переменным присваиваю значение true и удаляю дублирующий лист, что бы не мешался.
Комментарии (18)
ivanius
18.12.2017 15:49Вспоминаю те времена когда я на VBA писал хорошие вещи, обработчики со всеми связями, даже добавлял формы красивые, для добавления информации и отображения подробностей. А также использовал WinAPI чтобы это все выглядело как нужно. Это было конечно до тесного знакомства с SQL базами.
Bronto3
18.12.2017 16:18+1И теперь спустя ваш опыт работы со скуэлем, положа руку на сердце, стоит ли базы делать на нём для домашне-офисного пользования? Или всё таки пытаться делать базы на нескольких файликах экселя? Что быстрее работает в простых поисках, что проще изучать, что проще обслуживать?
lmike
18.12.2017 17:31изучать Excel для программирования в VB…
может тогда гуглдокс?
как пример xakep.ru/2015/01/08/google-apps-script
QDeathNick
19.12.2017 09:37Гуглдокс незаменим в таких кейсах. +Масса удобств из-за облака.
Но я стараюсь все же избавиться от бумаги.
Anatolevich
18.12.2017 22:08У меня несколько проще все сделано — на листе «исходники» собираются все данные, а потом раскидываются по десятку остальных актов. Печать из excel в принципе норм получается.
fukkit
18.12.2017 23:40Купите себе кусочек
фокспро1С (технологическую платформу) и жуйте те же кактусы, но элегантно и со всеми встроенными удобствами.LuchS-lynx Автор
19.12.2017 07:44В данном случае выбор обусловлен лишь тем, в чем я умею работать и в чем чаще всего работаю как инженер-строитель
saege5b
19.12.2017 00:14Раньше (в 2к) просто отключалась отрисовка.
И потом, нужно использовать массив типа — ячейка!
Из прошлого опыта типа того что тут 2-5 стрпниц А4 готовились с минуту, потом, с отключенной отрисовкой и оформлением массива ячеек в памчти с послежкющей вставкой всего масмива — 2-3 сек.
Т.е. не надо городить на странице. Всё делается в оперативной памчти и потом вставляется чохом.
Alexprintme
20.12.2017 09:45А чем ваше решение лучше того что предлагает MS?
Для заполнения актов на скрытые пользуюсь связкой word + excel.
В ворде есть такая вкладка — рассылки. Там можно связать документ с данными из excel (и не только). И для каждого вхождения данных будет создан документ по шаблону (вкладка найти и объединить -> объединить все. Если подробнее то:
1. создаем в excel таблицу — в столбцах данные которые будут импортированы в ворд, первая строка — заголовки полей слияния.
2. в ворде создаем шаблон акта (или чего вам там надо) и на вкладке рассылки:
— выбрать получателей -> выбрать существующий список -> указываем файл с данными
— вставить поле слияния -> раскрывается список полей (названия столбцов в таблице ексель) -> форматируем как нам надо
— найти и объединить -> изменить отдельные документы -> все
-> ворд создает новый документ по шаблону с данными по таблице из ексель
3. Лайфхаки:
— поля с датами могут содержать условия, например если данные пустые можно вставить значения по умолчанию (если не заполнена дата вставляем '__'______20__ и тд)
— даты передаются как числа, потому правой кнопкой мыши по ним и в код/значениеполей добавляем \@ «dd MMMM yyyy г.» или как вам надо отформатировать)
— если в таблице нужно передать длинные строки, то есть одна тонкость. Если первая строка данных в екселе содержит короткую строку (короче 250 символов или около того) то последующие строки этого столбца ворд тоже обрежет. Потому первую строку с данными я заполняю просто длинной строкой типа 12345… и так 1000 символов.
— в таблице ексель я создаю поле «печать». Потом в ворде во вкладке рассылки -> изменить список получателей выбираю фильтр по полю «печать»=… и отфильтровываются документы которые нужны сейчас, а не вся таблица. Как правило я создаю в екселе сразу реестр работ по всему объекту, и по мере готовности печатаю.
— связать документ ворда можно не только с екселем, но и со многими источниками данных вроде бд и тд
— полезно и даже очень изучать инструменты в которых работаешь, особенно когда есть неплохая такая справка в офисе.
LuchS-lynx Автор
20.12.2017 10:27По выводным формам:
1. Мне удобнее и привычнее работать с Excel, чем с Word.
2. Когда кол-во пунктов для вывода становится очень много, то привязками в офисе сложнеее отслеживать такие вещи для меня визуально, в то же время я прописываю доп колонку из управляющих символов на латинице, загоняю из в массив, а в шаблоне акта расставляю как мне заблагорассудится, т.е. я могу поместить значение как в отдельную ячейку, так и набрать текст навроде «b6 b7 — b9c0c1c2(b8)» или «a2, a3», моя функция корректно отрабатывает вывод такой вывод. Т.е. Набрать можно абсолютно любой текст на кириллице и, вставив управляющую латиницу, на выходе получить любые сочетания текста с сохранением кириллицы.
Я знаю, что в сети есть 2 альтернативных варианта решения проблемы, один работает со слиянием, второй так же выводит в эксель, НО(!), я не собираюсь изначально ограничиваться только выводной формой таблицы. На текущий момент я реализовал следующий функционал:
— Заполнение данные для входного контроля и вывод акты входного контроля;
— Вывод журнала входного контроля;
— Заполнение форм для вывода АОСР с подтягиванием данных из листа с данными по материалам и годовым Месячно-суточным графиком, т.е. даты парсятся с графика согласно отчетных периодов (по умолчанию месяц) и расстановка следующих друг за другом актов в рамках каждого из отчетных периодов для АОСР, пока, берется последовательно с МСГ.
— Есть визуальный (пока визуальный) контроль за кол-вом списываемых по актам АОСР материалов.
На профильных сайтах и в одной социальной сети мною уже выложен часть работающего функционала в открытый доступ для тестирования. Так что я не делаю из этого тайну. Если будет интересно потрогать ручками — прошу в приват.
По-этому с одной стороны, данная статья является напоминанием о том, что Excel может несколько больше, чем в среднем от него требуется среднестатистическому офисному сотруднику, в т.ч. можно автоматизировать рутины, с другой стороны надеюсь, что мои выкладки кому-то помогут решать именно его прикладные задачи.
В планах:
— подключить ВСН'овские формы
— реестр документов на основании выводимых актов
— ОЖР — разделы 3 и 6
— доработать ввод данных для АОСР, с тем что бы система отслеживала вывод работ по участкам, возможно делала несколько актов в зависимости от этого.
telhin
Магические константы в 105 символов выглядят совсем уж плохо (как минимум потому что символы в Times New Roman не фиксированной ширины).
Если Вам нужен только качественный печатный формат, то можете посмотреть в сторону latex: более-менее удобная поддержка макросов и счетчиков, перекрестных ссылок.
Из минусов: тяжелый порог входа, нет качественного экспорта в MS word, другие люди не захотят пользоваться латехом.
UPD. К функциональному программированию эта статья мало относится.
LuchS-lynx Автор
Меня не только печатный формат интересует, в конце-концов я думаю что так или иначе можно подвесить на вывод или преобразование в картинку/pdf. Дело в том, что фактически затем планирую добавить месячно-суточный график на основании которого будут заполняться другие типовые формы, например Общий журнал работ (Раздел 3), Акт готовности электромонтажных работ и другие. Можно ли в latex реализовать и парсить информацию с календарных графиков, заполняемых вручную?
iilin
Все зависит от того в каком формате будет представлен график, теоретически, если он он будет plain text (csv) то можно и замарочиться
LuchS-lynx Автор
На текущий момент видится мне следующим образом — МСГ в формате Excel, оттуда берутся работы, объемы работ согласно отчетным интервалам (например с 26го текущего по 25е число следующего месяца), в отдельном фильтре указывается какие работы будут упоминаться в каких типах актов, затем данные будут заноситься частично автоматически, частично вручную по нескольким таблицам и уже обработка информации с них, по принципу указанному в статье, будет осуществляться вывод комплекта документации в виде файлов.
На текущий момент у мя только одна проблема — под каждый новый принтер в системе приходится пересохранять файлы-шаблонов форм актов, т.к. не смотря на первоначальные настройки на разных принтерах даже на одной системе слетают границы. Если latex может решить эту проблему, то можно попробовать использовать его
telhin
Есть еще кое-что добавить:
iilin
Можно ж на VBA дергать скрипт компиляции latex, перед этим сгенерировав шаблон
third112
Из другой области, но может будет полезно: для сложных работ (статьи с математикой, таблицами, графиками и алгоритмами в бумажные журналы) использую LyX. Графики делаю, в том числе, и в Excel, первожу в картинки (нпр., JPEG). На мой взгляд LyX проще ворда. На выходе легко получаю pdf.