Как сделать шаблон для печати в экселе

Обновлено: 06.07.2024

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

Шаблоны печати

В нашей CRM программе вы можете создавать любые шаблоны печати. Например, вы можете выписывать счета, акты о выполненных работах, заполнять типовые договора, пользуясь данными из таблиц. В шаблоне печати задается общий вид документа и отмечается, куда в этом документе нужно вставить значения полей таблицы. Шаблоны печати в системе "Клиентская база" позволяют не только распечатывать документы, но и сохранять их в форматах rtf, odt (OpenOffice), docx (Word 2007), xls, pdf. На этом уроке мы научимся создавать шаблоны в формате xls (при создании шаблонов xls для нашей программы рекомендуем использовать Excel 2007).

Важно: На данный момент значения из подтаблиц могут некорректно отображаться в версии Microsoft Office 2013. Если Вы столкнулись с такой проблемой, попробуйте открыть сформированный файл в другом текстовом редакторе (например, Libre office или Open office).

Создание шаблонов печати на основе документа Excel

1. Сначала необходимо подготовить документ в программе Excel. Разместите в нем необходимый текст и таблицы. Места, куда должны будут подставляться значения из таблиц CRM "Клиентской базы", оставьте пока пустыми. В документе также можно использовать изображения, их необходимо вставить в сам файл Excel, из поля программы изображения подтягиваться не будут.

Важно: В шаблонах печати Excel можно использовать только изображения в формате "png", в противном случае изображения при печати выводиться не будут!

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

2. Теперь в программе "Клиентская база" перейдем в "Настройки" — "Конфигурация".

3. Выберите категорию "Документы", затем перейдите в таблицу "Счет-фактура". Перед вами отобразится окно с настройками, здесь нажмите на кнопку "Шаблоны печати".

4. Вы увидите список уже имеющихся шаблонов. Нажмите на кнопку "Добавить шаблон".

5. В окне "Создание шаблона" сначала нужно ввести имя шаблона. Затем выбрать формат шаблона. Выбираем "xls".

6. Далее необходимо выбрать файл, с которым мы будем работать. Нажимаем "Загрузить".

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

Теперь нужно в нашем файле Excel разместить шаблоны полей в тех местах, куда должны подставляться значения из базы. Для этого в окне "Создание шаблона" есть список всех полей, которые можно разместить в шаблоне. Копируем из списка название поля целиком, включая фигурные скобки, и вставляем в Excel файл в нужное место.

Обратите внимание: вы можете использовать поля не только из текущей таблицы, например , но и любые поля из связанных таблиц - они пишутся через точку, например, .

Задайте ячейкам нужный формат, чтобы Excel корректно отображал данные. Например, ячейкам, в которых должен выводиться ИНН или КПП, лучше задать текстовый формат, т.к. Excel может воспринять номер ИНН как слишком большое число и выводить его некорректно.

8. После того, как мы разместили в xls файл все нужные поля, сохраняем его. Шаблон готов. Теперь нужно подгрузить заново готовый шаблон. Опять нажимаем "Выбрать файл", выбираем наш xls-файл, нажимаем "Загрузить". Наш шаблон с уже вставленными в него переменными загружен.

9. Теперь мы можем настроить ориентацию страниц шаблона: альбомную или книжную.

После этого задаем размер страницы нашего шаблона, выбрав его из предустановленных и нажимаем на кнопку "Сохранить".

Примечание. В целях безопасности редактировать шаблоны печати с расширением xls могут только пользователи с правами администратора.

10. Распечатать в программе можно несколькими способами. Например, переходим в таблицу, выбираем запись. В режиме просмотра нажимаем кнопку "Дополнительно", открывается кнопка "Распечатать". Или находясь в таблице отмечаем галочками одну или несколько нужных записей, а затем нажимаем на кнопку "Распечатать" на верхней панели.

Выбираем наш шаблон печати.

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

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

Функции для использования в xls-шаблонах

Шаблоны полей, условия, функции в ячейки xls-файла нужно вставлять в фигурных скобках, например, . Внутри фигурных скобок можно использовать круглые скобки, например, <(Сумма*(1+Наша компания. Ставка НДС/100))>

ПрописьюДата(Дата) - предназначена для вывода даты прописью. Например: Если поле "Дата" имеет значение "21.11.12", функция Прописью(Дата) будет возвращать значение "21 ноября 2010г".

ПрописьюМесяц(Дата) - предназначена для вывода месяца прописью. Например: Если поле "Дата" имеет значение "21.11.12", функция ПрописьюМесяц(Дата) будет возвращать значение "ноября".

ДатаЧисло(Дата) - возвращает значение числа. Например: Если поле "Дата" имеет значение "21.11.12", функция ДатаЧисло(Дата) будет возвращать значение "21".

ДатаМесяц(Дата) - возвращает значение месяца. Например: Если поле "Дата" имеет значение "21.11.12", функция ДатаМесяц(Дата) будет
возвращать значение "11".

ДатаГод(Дата) - возвращает значение года. Например: Если поле "Дата" имеет значение "21.11.12", функция ДатаМесяц(Дата) будет возвращать значение "2012".

ЧислоФормат(Шаблон поля) - возвращает число с двумя знаками после запятой с округлением. Например, ЧислоФормат(3.068) возвращает 3,07.

ЧислоФормат(Шаблон поля,n) - возвращает число с n-знаками после запятой с округлением. Например, ЧислоФормат(3.068,1) возвращает 3,1.

ЧислоФормат(Шаблон поля,n,1) - возвращает число с n-знаками после запятой с округлением и с разделением разрядов. Например, ЧислоФормат(35200.068,2,1) возвращает 35 200,07.

ПрописьюРублей(Шаблон поля) - возвращает значение числа прописью в рублях и копейках. Например, ПрописьюРублей(3.068) возвращает "три рубля 07 копеек".

ПрописьюРублей(Шаблон поля, '', 'none' ) - возвращает значение числа прописью в рублях без копеек. Например, ПрописьюРублей(3.23, '', 'none') возвращает "три рубля".

Имя_Подтаблицы.НомерСтроки - возвращает значение номера строки в подтаблице. Например, если нужно вывести в табличном виде все строки подтаблицы "Позиции Счета", то в ячейке, в которой должен выводиться номер строки, нужно прописать .

КоличествоСтрок('Имя_подтаблицы') - возвращает значение количества строк в подтаблице. Например, если после распечатанных в табличном виде позиций счета необходимо вывести общее количество строк, то в ячейке нужно прописать: Всего наименований

Кроме того, там где это уместно, можно использовать функции Excel и задавать формат ячеек.

Задание условий

Условия в ячейках xls-файла можно добавить с помощью краткой записи условного оператора if php.

Cинтаксис краткой записи условного оператора if:

expression ? true_value : false_value

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

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

Начиная с версии 2.0.1 в программе "Клиентская база" появилась возможность печати многостраничных документов. Для создания такого шаблона нужно проделать те же действия, которые описаны выше в пункте "Создание шаблонов печати на основе документа Excel". Однако документ, который необходимо будет подготовить перед созданием шаблона будет иметь другой вид:

Нужно добавить в шаблон четыре новых листа, чтобы общее количество листов в документе составило 5.

Лист №1 - это лист, где будет содержаться тело шаблона. Назовем его, например, "Body";
Лист №2 - это лист, где будет указываться общий заголовок всего документа. Назовем его "Header";
Лист №3 - это лист, где будет указывается общий подвал шаблона. Назовем его "Footer";
Лист №4 - это лист, где будет указываться заголовок отдельно взятой страницы. Назовем его "Table header";
Лист №5 - это лист, где будет указываться подвал отдельно взятой страницы. Назовем его "Table footer".

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

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

Лист №2
Лист №4
Лист №1
Лист №5
Лист №4
Лист №1
Лист №5
Лист №3

Header;
Table header;
Body;
Table footer;
Table header;
Body;
Table footer;
Footer.

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

Давайте разберем создание сложного многостраничного документа на примере шаблона ТОРГ-12. Мы хотим, чтобы при печати накладной она выглядела следующим образом:

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

Итак, в качестве заголовка документа мы отмечаем то, что будет в начале первой страницы документа и больше нигде повторяться не будет. Шаблон этой части должен быть расположен на листе №2 — Header.

В качестве подвала документа мы выделяем то, что будет в самом конце последней страницы документа. Шаблон этой части должен быть расположен на листе №3 — Footer.

Далее выделяем заголовок таблицы — это та часть, которая должна быть напечатана на каждой странице документа, содержащей данные. То есть, если у нас будет много позиций по накладной, то часть из них будут перенесены на вторую страницу, в начале которой тоже должен стоять заголовок таблицы с названиями столбцов. Шаблон этой части должен быть расположен на листе №4 - Table header.

Теперь давайте посмотрим на окончание (подвал) таблицы (на рисунке выделен фиолетовый цветом), которая должна быть также на каждой странице, но в отличие от заголовка располагаться не до, а сразу же после основной части. В примере с шаблоном печати по форме ТОРГ-12 это одна строка итога. Шаблон этой части должен быть расположен на листе №5 - Table footer.

Затем обратим внимание на ту часть, которая является основной. В нашем примере - это непосредственно сами позиции накладной. На каждой странице эти данные будут обрамлены сверху той частью, которая помещена в лист "Table header", а снизу той частью, которая размещена в листе "Table footer". Шаблон этой части должен быть расположен на листе №1 - Body. Данные из этой части дублироваться не будут.

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

Функции для использования в многостраничных xls-шаблонах

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

Примечание: Функции, которые считают суммы по странице нельзя помещать в какой-либо другой лист, кроме Листа № 5 — Table footer.

Давайте разберем их применение также на примере ТОРГ-12:

Примечание: Если необходимо добавить шаблон печати, в котором на каждой странице документа будут повторяться верхняя (заголовок) и нижняя (подвал) части, то в файле шаблона достаточно оставить три листа: Лист №1 — Body, Лист №2 — Header и Лист №3 — Footer. Однако, в таком шаблоне нельзя использовать функции расчета по странице. Теперь если включить параметр "Разрыв строк", то в случае многостраничного документа шапка и подвал будут напечатаны на каждой странице. Если параметр "Разрыв строк" не включить, то шапка будет только на первом листе, а подвал только на последнем.

Шаблон формата Excel может быть как библиотекой наборов стилей форматирования, так и готовым документом с таблицами, данными, форматами и графиками. В шаблоны-документы достаточно только ввести свои данные, чтобы получить готовый, тематически стильно оформленный, презентабельный отчет. Например, «Семейный бюджет» – очень полезный встроенный шаблон в Excel.

Создание шаблона в Excel

Создадим свой новый шаблон, в котором следует использовать стили форматирования. Как форматировать с помощью стилей мы знаем из предыдущего урока: стили ячеек и средства управления ими. Чтобы сделать шаблон в Excel, выполним определенный ряд действий, которые послужат практическим примером для решения данной задачи:

Сначала нужно сделать шаблон таблицы Excel и график, как показано ниже на рисунке. И присвойте ей пользовательский стиль форматирования.
Сохраняем документ как шаблон: «Файл»-«Сохранить как». В окне сохранения можно указать любую папку, так как Excel потом автоматически перенесет этот файл в специальную директорию для хранения нестандартных шаблонов.
В поле тип файла следует указать «Шаблоны формата Microsoft Office Excel *.xltx». Заметьте, как только мы выбираем этот тип файла, автоматически открывается папка «Шаблоны» в окне сохранения документа.
В поле: «Имя файла» введите любое название шаблона. Например, «мои расходы». И жмите СОХРАНИТЬ.
После сохранения закройте документ.

Теперь чтобы использовать наш шаблон достаточно выбрать меню: «Файл»-«Создать»-«Мои шаблоны». Выберите свой шаблон «мои расходы».

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

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

Создание файла по шаблону Excel

Можно установить свой пользовательский шаблон, по которому будет оформлен и отформатирован документ при каждом запуске программы Excel. Для этого следует сохранить файл с расширением *.xlsx в папке XLSTART. Данная папка создается при установке программы Excel. Чтобы легко найти путь к ней следует перейти в режим VisualBasic (Alt+F11) и открыть окно: «View»-«Immediate». А там следует набрать код: ? application.StartupPath и нажать Enter. Ниже отобразиться путь:

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

Теперь при каждой загрузке программы будет открыт файл который лежит в этой папке. Более того если вы сохраните там несколько документов, то все они будут открыты при каждой запуске программы.

Полезный совет! Если в папке XLSTART сохранить отформатированный шаблон с именем Лист.xltx. То каждый новый созданный лист будет иметь такой же формат как Лист.xltx. При том не зависимо, в какой способ создан новый лист, через меню или комбинацией горячих клавиш SHIFT+F11. Формат будет один и тот же. А чтобы отменить такую функцию нужно просто удалить этот шаблон из XLSTART.

Форматирование документов – это важный, но очень утомительный процесс. Для его автоматизации существует несколько полезных инструментов такие как: темы, стили и шаблоны. Каждый из них обладает своими преимуществами и недостатками. Например, самый простой способ автоматизации форматирования – это стили. Они облегчаю пользователю процесс форматирования, но могут быть сохранены только в рамках одной книги. Да созданные или измененные пользователем стили можно скопировать в другую книгу. Или создать отдельную, специальную книгу с целым набором свих пользовательских стилей, чтобы копировать их в другие документы, но это крайне неудобно. Для решения такой задачи лучше использовать шаблоны.

Создание темы документа для автоматического форматирования

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

Для примера возьмем простой не форматированный документ и будем присваивать ему разные темы пока не выберем подходящую к данным. Заодно посмотрим, что произойдет выбрать другую тему Excel:

Создайте новую книгу и заполните ее данными (начиная со столбца H) так как показано на рисунке:
Выберите инструмент: «Разметка страницы»-«Темы».
В выпадающем списке наводите курсор мышки на разные темы и обратите внимание на то, как меняется стиль оформления данных документа.
Щелкните по подходящей теме, которая на Ваш взгляд наиболее подходит к данному документу.

Внимание! Темы присваиваются к целому документу. Поэтому нельзя для разных листов задавать разные темы форматирования.

Пользователь имеет возможность самостоятельно создавать и редактировать любую тему. Для этого доступны 3 инструмента, которые находятся рядом возле тем:

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

Как только вы сохраните новый набор шрифтов и/или цветов под своим названием, тогда она будет доступна в разделе тем как для Excel, так и для других программ из пакета MS Office (Word, PowerPoint и др.).

Предположим, что у нас есть таблица Excel такого вида:

а также эскиз желаемой этикетки в виде фото:

Наша задача — по данным из таблицы Excel сформировать этикетки примерно такого же вида, как на этом фото.

ПОРЯДОК НАСТРОЙКИ:

1. Создадим новый шаблон этикетки.

Для этого в меню Шаблоны выберем пункт Создать новый шаблон

В появившемся диалоговом окне, где будет предложено ввести название шаблона, введём произвольный текст, — например, Этикетка.

Будет создан и открыт новый файл Excel. В выпадающем писке шаблонов появится новый пункт, для нового шаблона:

2. Подготавливаемся к настройке шаблона.

Внимательно изучаем эскиз (фото) этикетки, прикидывая, сколько столбцов и строк нам нужно использовать для размещения данных как на образце:

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

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

Выделим штук 20 столбцов, и уменьшим их ширину:

Кроме того, вставим фото (эскиз) этикетки на лист шаблона, чтобы по этому образцу ориентироваться, какие размеры задавать ячейкам и строкам шаблона.

3. Приступаем к настройке шаблона.

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

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

Сохраним промежуточные изменения в шаблоне, нажав Ctrl + S (необязательно)

ВНИМАНИЕ: оставим первые и последние строки / столбцы шаблона пустыми, чтобы потом регулировать ими отступы между этикетками.
Если забыли оставить пустые строки / столбцы выше и левее шаблона, их потом всегда можно будет добавить.

4. Настраиваем подстановку данных в шаблон.

Настройка заключается в замене данных в шаблоне на подстановочные коды вида , где 2 — номер столбца исходной таблицы.

В результате получим что-то типа такого:

Я немного изменил шаблон на этом этапе, ввиду того что в наших исходных данных оказались другие столбцы.

Обратите внимание, что для ячейки со штрихкодом задан шрифт Code EAN13, и в ячейке находится подстановочный код вместо обычного — это требуется программе для перекодировки числового значения штрихкода в другой формат, понятный шрифту Code EAN13.

5. Сохраним изменения в шаблоне

ВАЖНО: Для этого мы в меню Шаблоны нажмём кнопку Сохранить изменения в шаблоне

(именно так надо сохранять шаблон, хотя бы при первом сохранении, чтобы программа могла дажее работать с ним)

Появится диалоговое окно выбора диапазона ячеек с шаблоном:

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

После выбора диапазона в диалоговом окне, настройка шаблона завершена, и он автоматически закрывается.

6. Тестируем формирование этикеток

Нажимаем в меню кнопку Настройки, и задаём нужные параметры:

Теперь открываем таблицу с исходными данными, выделяем в ней несколько строк, и нажимаем на панели инструментов кнопку Сформировать этикетки.

Если мы всё настроили правильно, будет сформирована таблица Excel примерно такого вида:

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

2. Если рисовать таблицы средствами Word, то к ним можно обращаться с адресацией в ячейку
Здесь нужно обратить внимание, что у каждой таблицы в 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мес). Таким образом получается, что если:

Читайте также: