Периодически возникает необходимость заполнять шаблоны Excel, т.к. там пользователи могут настроить для себя не необходимые формулы и разнообразные проверки данных (например, по общероссийским классификаторам) и затем заполнить этот файл данными из 1С, чтобы всё «само посчиталось».
В приложенной обработке есть пример, как можно сделать такое заполнение. Используется шаблон Excel (загружен в обработку в «Макеты») с двумя листами (заполняются оба листа), на одном листе заполняется таблица с номенклатурой закупки из справочника «Лоты», на втором – таблица сопоставления предложений поставщиков по данному лоту из документов «Предложение поставщика».
В обработке есть следующие примеры:
- Заполнение значения для конкретной ячейки;
- Добавление и заполнение строк в таблицу (список номенклатуры);
- Добавление и заполнение колонок и строк в таблицу (колонки по поставщикам, строки по номенклатуре);
- Форматирование (цвет фона в ячейке, цвет текста, выделение жирным шрифтом);
- Замена текста через «Найти и заменить».
Для начала необходимо разработать сам макет Excel.
Из 1С к ячейкам можно обращаться по номеру строки и колонки, а можно задать для области имя и обращаться к ячейкам по имени. В обработке встречаются оба варианта.
В большинстве случаев я предпочитаю второй вариант, т.к. при редактировании шаблонов (добавлении / удалении строк или колонок), если изменения не касались именованных ячеек / таблиц, то редактирование кода может и не понадобиться.
Т.е., если в макет добавили новые строки (например, гриф утверждения), то все нижестоящие строки будут смещены вниз. При обращении к ячейкам по номеру строки, придётся скорректировать код и изменить номер, по которому выполнялось обращение. При обращении к ячейке по имени, изменения вносить не придётся.
Для того, чтобы задать имя для ячейки, необходимо выделить ячейку ПКМ и выбрать пункт «Присвоить имя». На одном листе имена повторяться не могут.
Такие же имена использую для заполнения таблиц – для определения первой (и последней, при необходимости) строки, колонок, конкретных ячеек и т.п.
В случае, если в таблице есть суммирующие формулы, то создаем две строки – начало и окончание, настраиваем между ними формулы, а новые строки будем добавлять между ними, чтобы формула при растягивании их подхватила. Можно задать формулы и из 1С, но на мой взгляд в шаблоне это сделать намного проще.
Все имена можно посмотреть на вкладке «Формулы» -> Диспетчер имен.
Когда макет готов, загружаем его в 1С и делаем выгрузку.
Для этого сначала получаем данные, которые необходимо выгрузить в Excel. Все данные, которые пойдут на печать, должны иметь примитивный тип. В случае попытки присвоить ячейке Excel значение ссылочного типа, будет получена ошибка.
Затем полученными данными заполняем наш документ.
Для заполнения ячейки получаем ячейку по имени (или номеру строки и колонки) и присваиваем ей нужное значение:
Либо, если в поле нужно подставить только часть текста, можно в шаблоне сначала задать заменяемый текст в виде какого-нибудь шаблона (например, «/Поставщик/»), а потом заменить на нужный текст:
Для заполнения таблиц находим в Excel нужную строку, копируем её и заполняем:
В случае, если в таблицу нужно добавить колонки, то в шаблоне имя задаём для всей области, которая будет копироваться (в данном примере для каждого поставщика будет копироваться выделенная область).
Мне удобнее сначала копировать и вставлять области для добавления колонок, а затем уже добавлять и заполнять строки.
Послу того, как все строки и колонки будут вставлены, необходимо удалить лишние области, которые останутся после вставки, например:
Также, при заполнении можно установить форматирование для ячейки, например, выделить красным цветом цены с превышением и зелёным фоном минимальное предложение:
После завершения заполнения я всегда позиционируюсь на первой ячейке листа, в противном случае, если в документе много текста / таблиц, он может открыться где-то с середины или в самом конце листа, что не очень удобно.
И открываем заполненный шаблон пользователю:
Проверено на следующих конфигурациях и релизах:
- 1С:Управление холдингом 3.2 (русский и английский интерфейсы), релизы 3.2.11.20
Вступайте в нашу телеграмм-группу Инфостарт