Формирование печатных форм в Excel из «1С Предприятие 7.7»

Любую печатную форму, сформированную с помощью встроенного табличного редактора «1С:Предприятия», при необходимости можно сохранить в формате Excel выбрав в меню «Файл» пункт «Сохранить как», а в появившемся окне сохранения файла — тип файла «Таблица Excel». Однако пользователи, заказывая внешнюю обработку, довольно часто просят предусмотреть возможность формирования некоторых печатных форм непосредственно в Microsoft Excel.

Контекст работы с приложением Microsoft Excel

Во всех программных модулях вызов процедур или функций внешнего приложения может выполняться только при помощи переменной со ссылкой на OLE-объект, который создаётся функцией СоздатьОбъект() (англоязычный синоним — CreateObject()). Чтобы вызвать метод внешнего приложения, имя метода (с указанием необходимых параметров) пишется через точку после идентификатора данной переменной.

При создании переменной со ссылкой на приложение Microsoft Excel, в качестве имени объекта в операторе СоздатьОбъект() должен выступать уникальный OLE-идентификатор Excel.Application. Например:

Процедура СформироватьВыходнуюФорму()

   Попытка
      // Пытаемся обратиться к MS Excel
      Excel = СоздатьОбъект("Exel.Application");
   Исключение
      // Если MS Excel не установлен на ПК
      Предупреждение(ОписаниеОшибки() + "; MS Excel не установлен на данном ПК!");
      Excel = 0;
      Возврат;
   КонецПопытки;

   // Формируем выходную форму в MS Excel
     . . .

КонецПроцедуры

При инициализации данного OLE-объекта с помощью функции СоздатьОбъект() мы получаем ссылку на главный элемент объектной модели приложения MS Excel — объект Application, а следовательно, можем использовать его собственные свойства и методы непосредственно. Чтобы обратиться к свойству или методу объекта Application достаточно записать имя свойства или метода через точку после имени нашей переменной, например:

// Проверим версию MS Excel
ВерсияExcel = Excel.Version;

Примечание: Иногда в исходных текстах программ можно встретить запись типа Excel.Application.Version. Данная запись аналогична записи приведённой в примере выше, но избыточна. Ссылка на объект Application здесь указана явно, что, в общем-то, является лишним.

Что нужно знать об объекте Application

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

О методах объекта Application, необходимых для работы с приложением, мы поговорим попозже, а вот свойства объекта, которые могут Вам потребоваться при написании процедур, приведены ниже.

ActiveSheet — доступное только для чтения свойство, возвращающее ссылку на объект, который представляет активный лист в активной книге.

ActiveWorkbook — доступное только для чтения свойство, возвращающее ссылку на объект Workbook, который представляет рабочую книгу в активном окне.

Calculation — свойство, позволяющее узнать или установить режим пересчёта рабочей книги. Возможные значения свойства:

−4105 — установить режим автоматического перерасчёта (значение по умолчанию);
−4135 — режим ручного перерасчёта по запросу пользователя;
2 — режим полуавтоматического перерасчёта, когда автоматически пересчитывается всё, кроме таблиц.

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

Для справки: В качестве значений различных свойств в VBA могут использоваться встроенные константы. В качестве значений свойства Calculation, например, могут использоваться константы xlCalculationAutomatic, xlCalculationManual и xlCalculationSemiautomatic. Однако при использовании кода VBA в сторонних приложениях встроенные константы VBA недоступны, а потому используются их числовые аналоги: −4105, −4135 и 2.

CalculationState — доступное только для чтения свойство, позволяющее узнать, занято ли приложение пересчётом данных или пересчёт уже завершен. Возможные значения:

2 — подготовка к пересчёту;
1 — идёт пересчёт;
0 — пересчёт завершён.

Cells — одно из самых важных свойств объекта Application. Оно возвращает объект Range, представляющий в данном случае все ячейки в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к ячейкам активного листа может выглядеть так:

Excel.Cells(1, 2).Font.Bold = -1;

В данном примере мы выделили полужирным шрифтом ячейку на пересечении первой строки и второго столбца.

Важно! Еще раз отмечу, что свойство Cells возвращает не коллекцию объектов Cell, как считают некоторые пользователи, а объект Range. На использовании объектов Range в Excel построена почти вся работа с ячейками и их значениями.

Columns — свойство, возвращающее объект Range, представляющий в данном случае все столбцы в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к столбцу активного листа может выглядеть так:

Excel.Columns(2).Font.Bold = -1;

В данном примере мы выделили полужирным шрифтом все ячейки второго столбца.

Важно! Еще раз отмечу, что свойство Columns возвращает не коллекцию объектов Column, как считают некоторые пользователи, а объект Range. На использовании объектов Range в Excel построена почти вся работа с ячейками и их значениями.

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

Пример использования:

// Подавляем вывод диалоговых окон и сообщений
Excel.DisplayAlerts = 0;

Для справки: При использовании кода VBA в сторонних приложениях встроенные константы false и true недоступны, а потому следует использовать их числовые аналоги: 0 и −1. Кстати, начинающие разработчики вместо единицы со знаком минус по незнанию часто пишут просто единицу и, надо заметить, приложения Microsoft Office вполне нормально это «проглатывают».

EnableEvents — свойство, позволяющее на время отключить события для объекта Application, чтобы они не срабатывали. На практике события часто отключают перед выполнением какого-то действия — перед открытием файла, сохранением и т.п. Для этого значение свойства устанавливают в false, а после выполнения действия возвращают в true.

Names — доступное только для чтения свойство, возвращающее коллекцию Names, представляющую все именованные диапазоны в активной рабочей книге. Работают такие диапазоны примерно так же, как закладки в Word — с их помощью очень удобно определять наборы данных в сложных таблицах Excel. На графическом экране в Excel назначить именованные диапазоны можно при помощи меню «Вставка -> Имя». Программно назначить именованные диапазоны в рабочей книге позволяет метод Add() коллекции Names.

OperatingSystem — доступное только для чтения свойство, возвращающее имя и номер версии текущей операционной системы, например, "Windows (32-bit) 4.00" или "Macintosh 7.00".

Range — свойство, возвращающее объект Range, который представляет собой указанную ячейку или указанный диапазон ячеек активного листа активной книги.

Пример использования:

// Выбираем диапазон A1:D10 в активном листе книги
ВыбранныйДиапазон = Excel.Range("A1:D10");

ReferenceStyle — свойство, позволяющее получить или установить режим отображения номеров ячеек («A1» или «R1C1»). Возможными значениями свойства могут быть:

1 — режим «A1»;
−4150 — режим «R1C1».

Rows — свойство, возвращающее объект Range, представляющий в данном случае все строки в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к строке активного листа может выглядеть так:

Excel.Rows(2).Font.Bold = -1;

В данном примере мы выделили полужирным шрифтом все ячейки второй строки.

Важно! Еще раз отмечу, что свойство Rows возвращает не коллекцию объектов Row, как считают многие пользователи, а объект Range. На использовании объектов Range в Excel построена почти вся работа с ячейками и их значениями.

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

Sheets — доступное только для чтения свойство, возвращающее коллекцию Sheets, представляющую все листы активной книги. Коллекция Sheets может содержать объекты Worksheet (рабочие листы) и Chart (диаграммы).

Version — доступное только для чтения свойство, возвращающее версию Microsoft Excel.

Visible — свойство, позволяющее сделать окно приложения видимым или невидимым. Для этого свойству необходимо задать значение true (в режиме OLE ему соответствует −1) или false (в режиме OLE ему соответствует 0).

По умолчанию свойство имеет значение false, поэтому при программном открытии или создании документа после отработки соответствующих команд (Add() или Open()) окно приложения на экране не появляется. Для вывода окна на экран необходимо принудительно задать свойству Visible значение true. Однако если на экране в момент создания или открытия окна программным способом присутствуют окна экселевских документов, открытых ранее «вручную» (с помощью команды меню или ярлыка), окно программно создаваемого или открываемого документа появляется на экране сразу же (то есть в момент отработки команды Add() или Open()).

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

Пример использования:

Excel = СоздатьОбъект("Excel.Application");
// Делаем окно приложения невидимым
Excel.Visible = 0;
// Создаем новую рабочую книгу
НашаКнига = Excel.WorkBooks.Add();

// Заполняем созданный документ содержимым
  . . .

// Возвращаем все окна MS Excel на экран
Excel.Visible = -1;

Стоит ли скрывать ли окно приложения на время формирования нового документа или нет — это решать Вам. Но при отладке процедуры окно приложения скрывать точно не следует — легче будет выявить допущенные в коде ошибки и недочёты.

Примечание: Если окна приложения сделать невидимыми, невидимыми становятся также и кнопки этих окон на Панели задач. Кнопка запуска приложения, закреплённая на Панели задач, при этом остаётся видимой.

Workbooks — доступное только для чтения свойство, возвращающее коллекцию Workbooks, представляющую все открытые в приложении книги.

Worksheets — доступное только для чтения свойство, возвращающее коллекцию Sheets, которая представляет все листы в активной книге.

Создание новой рабочей книги

Все операции с рабочими книгами (создание новой, открытие имеющейся и их закрытие) в Microsoft Excel осуществляются посредством коллекции Workbooks, которая создаётся в момент открытия приложения и содержит все созданные или открытые рабочие книги. Ссылку на коллекцию возвращает одноимённое свойство объекта Application.

Создать новый документ Excel (то есть новую рабочую книгу) позволяет метод Add() коллекции Workbooks.

Синтаксис метода:

Add([<Template>])

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

−4167 — создать новую книгу;
−4109 — создать диаграмму;
4 — создать макрос MS Excel версии 4;
3 — создать международный макрос Excel версии 4.

Примечание: При использовании в качестве параметра числового значения новая книга будет содержать один лист указанного типа. Будучи же вызванным без параметра, метод Add() создаёт новую рабочую книгу, количество листов в которой устанавливается свойством SheetsInNewWorkbook объекта Application.

Excel = СоздатьОбъект("Excel.Application");

ЛистовПоУмолчанию = Excel.SheetsInNewWorkbook;

// Создаём книгу с пятью листами
Excel.SheetsInNewWorkbook = 5;
НашаКнига = Excel.Workbooks.Add();
// Возвращаем значение свойства SheetsInNewWorkbook к значению по умолчанию
Excel.SheetsInNewWorkbook = ЛистовПоУмолчанию;
. . .

Создание печатной формы «с нуля» — дело слишком хлопотное. Чтобы программно привести все ячейки к нужному формату, а затем заполнить их данными потребуется очень большой объём программного кода. Поэтому на практике для формирования печатной формы обычно используются готовые шаблоны табличных документов, в которых все ячейки уже отформатированы и часть текстового содержимого набрана заранее.

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

Пример создания новой книги на основании шаблона:

Excel = СоздатьОбъект("Excel.Application");

// Создаём новую книгу, используя шаблон-заготовку
ФайлШаблона = КаталогИБ() + "ExtForms\PrnForms\price_template.xlsx";
НашаКнига = Excel.Workbooks.Add(ФайлШаблона);

// Приступаем к заполнению документа
. . .

Возвращаемым значением метода Add() будет ссылка на созданный документ — активный экземпляр коллекции Workbooks.

Вставка данных в шаблон-заготовку

Если лист в рабочей книге будет только один (как в шаблоне, показанном на рисунке выше), то можно сразу же приступать к заполнению ячеек данными, используя, например, свойство Cells объекта Application.

Данное свойство, как уже говорилось ранее, возвращает объект Range, представляющий в данном случае все ячейки в активном листе активной книги. Для обращения к конкретной ячейке необходимо указать её координаты: номер строки и номер столбца.

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

Excel.Cells(1, 1).Value = СокрЛП(Константа.НазваниеОрганизации);
Excel.Cells(7, 1).Value = "Телефон отдела продаж: +7(910)457-41-11";

Также для работы с ячейками можно использовать свойство Range, возвращающее одноимённый объект, который представляет собой указанную ячейку или указанный диапазон ячеек выбранного листа. Например:

Excel.Range("A1").Value = СокрЛП(Константа.НазваниеОрганизации);
Excel.Range("A7").Value = "Телефон отдела продаж: +7(910)457-41-11";

В результате работы вышеприведённого кода в формируемой печатной форме появятся название предприятия и контактный телефон:

После заполнения шапки и подвала переходим к формированию табличной части печатной формы. Для вставки строк в табличную часть воспользуемся методом Insert() объекта Range.

Синтаксис метода:

Insert([<Shift>], [<CopyOrigin>])

где

<Shift> — необязательный параметр, определяющий направление смещения ячеек, на место которых будут вставляться новые. Если данный параметр не указан, Excel принимает решение на основе формы диапазона. В параметре можно использовать одно из следующих числовых значений:
- −4121 — сместить ячейки вниз;
- −4161 — сместить ячейки вправо;
<CopyOrigin> — необязательный параметр, определяющий источник, который будет использоваться для копирования формата во вставляемые ячейки. В параметре можно использовать одно из следующих числовых значений:
- 0 — скопировать формат из ячеек выше и/или слева. Значение, используемое по умолчанию;
- 1 — скопировать формат из ячеек ниже и/или справа.

В нашем случае в качестве строки-образца будет использоваться строка под номером 5 (см. рис. выше). При вставке она будет сдвигаться вниз, и из неё будет копироваться формат ячеек. Объект же Range, методом которого мы воспользуемся, мы получим из свойства Rows объекта Application.

Свойство Rows, как уже говорилось ранее, возвращает объект Range, представляющий в данном случае все строки в активном листе активной книги. Для обращения к конкретной строке необходимо указать её порядковый номер. Например:

Excel.Rows(НомерСтрокиДляВставки).Insert(-4121, 1);

Цикл формирования табличной части печатной формы может выглядеть следующим образом:

// Формируем табличную часть документа
СпрНом = СоздатьОбъект("Справочник.Номенклатура");
ПорядковыйНомер = 1;
НомерСтрокиДляВставки = 5;
Если СпрНом.ВыбратьЭлементы(0)>0 Тогда
   Пока СпрНом.ПолучитьЭлемент()=1 Цикл
      Если (СпрНом.ЭтоГруппа()=1)ИЛИ(СпрНом.ПометкаУдаления()=1) Тогда
         // Пропускаем группы и помеченные на удаление
         Продолжить;
      КонецЕсли;

      // Вставляем новую строку
      Excel.Rows(НомерСтрокиДляВставки).Insert(-4121, 1);
      // Заполняем строку данными
      Excel.Cells(НомерСтрокиДляВставки, 1).Value = ПорядковыйНомер;
      Excel.Cells(НомерСтрокиДляВставки, 2).Value = СокрЛП(СпрНом.Наименование);
      Excel.Cells(НомерСтрокиДляВставки, 3).Value = СпрНом.Цена;
      ПорядковыйНомер = ПорядковыйНомер + 1;
      НомерСтрокиДляВставки = НомерСтрокиДляВставки + 1;
   КонецЦикла;
КонецЕсли;

// Удаляем строку-образец
Excel.Rows(НомерСтрокиДляВставки).Delete(-4162);

В результате работы данного кода произойдёт заполнение табличной части печатной формы:

Примечание: При формировании табличной части печатной формы с помощью цикла Для…По, когда количество строк в формируемой табличной части заранее известно, строку-образец можно использовать в качестве последней строки таблицы и в последней итерации заполнить данными. Если же табличная часть формируется с помощью цикла Пока, когда количество строк в формируемой табличной части заранее неизвестно, строку-образец после завершения цикла следует просто удалить, как это сделано в вышеприведённом примере. Для удаления строки можно воспользоваться методом Delete() объекта Range.

Синтаксис метода:

Delete([<Shift>])

где <Shift> — необязательный параметр, определяющий способ сдвига ячеек для замены удаляемых. Если данный параметр не указан, Excel принимает решение на основе формы диапазона. В параметре можно использовать одно из следующих числовых значений:

−4159 — сместить ячейки влево;
−4162 — сместить ячейки вверх.

Пример отчёта с группировкой

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

Шаблон для такого прайс-листа может выглядеть следующим образом:

Программный же код формирования табличной части будет таким:

// Формируем табличную часть документа
СпрНом = СоздатьОбъект("Справочник.Номенклатура");
ПорядковыйНомер = 1;
НомерСтрокиДляВставки = 5;
Если СпрНом.ВыбратьЭлементы()>0 Тогда
   Пока СпрНом.ПолучитьЭлемент()=1 Цикл
      Если СпрНом.ПометкаУдаления()=1 Тогда
         // Пропускаем помеченные на удаление
         Продолжить;
      КонецЕсли;

      // Вставляем новую строку в табличную часть
      Excel.Rows(НомерСтрокиДляВставки).Insert(-4121, 1);

      Если СпрНом.ЭтоГруппа()=1 Тогда
         // Если это группа,
         // копируем образец строки заголовка и
         // переносим её формат на вновь вставленную строку
         Excel.Rows(СтрокаДляВставки+2).Copy(Excel.Rows(СтрокаДляВставки));
         // Вставляем текст заголовка группы
         Excel.Cells(СтрокаДляВставки, 1).Value = СокрЛП(СпрНом.Наименование);
         // В группе начинаем нумерацию заново
         ПорядковыйНомер = 1;
      Иначе
         // Заполняем строку для элемента справочника
         Excel.Cells(СтрокаДляВставки, 1).Value = ПорядковыйНомер;
         Excel.Cells(СтрокаДляВставки, 2).Value = СокрЛП(СпрНом.Наименование);
         Excel.Cells(СтрокаДляВставки, 3).Value = СпрНом.Цена;
         ПорядковыйНомер = ПорядковыйНомер + 1;
      КонецЕсли;
			
      СтрокаДляВставки = СтрокаДляВставки + 1;
   КонецЦикла;
КонецЕсли;

// Удаляем строку-образец
Excel.Rows(НомерСтрокиДляВставки).Delete(-4162);
// Удаляем образец строки для заголовков групп
Excel.Rows(НомерСтрокиДляВставки).Delete(-4162);

Для переноса формата образца строки заголовка на вновь вставленную строку в данном примере используется метод Copy объекта Range.

Синтаксис метода:

Copy([<Destination>])

где <Destination> — необязательный параметр, определяющий диапазон ячеек, в который будет произведено копирование. Если данный параметр не указан, копирование производится в буфер обмена.

Особенности работы с рамками ячеек

У некоторых читателей при просмотре вышеприведённых примеров кода наверняка может возникнуть вопрос, почему новая строка в табличную часть печатной формы в этих примерах вставляется выше строки-образца, а не добавляется после неё. Чтобы получить ответ на этот вопрос, надо просто поэкспериментировать со вставкой строк в Excel.

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

Затем пробуем вставить новую строку между имеющимися группами ячеек. Сделать это можно «вручную» с помощью соответствующей команды меню или программно с помощью метода Insert() объекта Range. В любом случае результат получится следующим:

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

И опять попробуем вставить новую строку между имеющимися группами ячеек: «вручную» или программно с помощью метода Insert(). В любом случае результат будет следующим:

Эксперимент третий. Установим все рамки для двух групп ячеек (см. рис. ниже).

И опять пробуем вставить новую строку между имеющимися группами. Результат будет таким:

Эксперимент четвёртый. Установим все рамки для группы ячеек (см. рис. ниже) и пробуем вставить строки выше и ниже этой группы.

Результат вставки будет следующим:

Таким образом, копирование параметров рамок для ячеек вставляемой строки Excel производит по каким-то своим встроенным алгоритмам. И даже если строка вставляется программно с помощью метода Insert(), во втором параметре которого явно указан источник для копирования формата во вставляемые ячейки, алгоритм копирования рамок не меняется.

Процедура формирования печатной формы будет иметь самый простой код в случае, когда вставка строки производится между ячейками, имеющими одинаковые параметры рамок. В противном случае рамки во вставляемой строке придётся «дорисовывать» или «перерисовывать» программно.

Управлять рамками ячеек позволяют свойства коллекции Borders, ссылку на которую возвращает одноимённое свойство объекта Range. Чтобы рамки появились или исчезли, надо просто установить их цвет с помощью свойства ColorIndex коллекции Borders.

Пример установки всех линий (кроме диагональных) для заданного диапазона:

// Выбираем диапазон A5:D10
ВыбранныйДиапазон = Excel.Range("A5:D10");
// Устанавливаем цвет "Авто" для всех линий диапазона
Выбранный диапазон.Borders.ColorIndex = -4105;

Пример удаления всех линий (кроме диагональных) для заданного диапазона:

// Выбираем диапазон A5:D10
ВыбранныйДиапазон = Excel.Range("A5:D10");
// Делаем все линии границ бесцветными
Выбранный диапазон.Borders.ColorIndex = -4142;

Получить доступ к конкретному объекту Border (например, к верхней, нижней, правой или левой линии рамки) позволяет свойство Item коллекции Borders. В качестве параметра свойству необходимо передать индекс этой линии:

5 — линия по диагонали сверху вниз;
6 — линия по диагонали снизу вверх;
7 — линия, обрамляющая диапазон слева;
8 — линия, обрамляющая диапазон сверху;
9 — линия, обрамляющая диапазон снизу;
10 — линия, обрамляющая диапазон справа;
11 — все вертикальные линии внутри диапазона;
12 — все горизонтальные линии внутри диапазона.

Пример использования свойства Item:

// Выбираем диапазон A1:A10
ВыбранныйДиапазон = Excel.Range("A1:A10");
// Устанавливаем цвет для линии, обрамляющей диапазон справа 
Выбранный диапазон.Borders.Item(10).ColorIndex = 3;

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

−4105 — цвет «Авто» (обычно чёрный);
−4142 — бесцветный (значение по умолчанию).

Значения для установки других цветов приведены на рисунке ниже:

Получить или установить цвет рамок в формате RGB позволяет свойство Color коллекции Borders. Например:

// Выбираем диапазон A5:D10
ВыбранныйДиапазон = Excel.Range("A5:D10");
// Устанавливаем красный цвет для всех линий
Выбранный диапазон.Borders.Color = -16776961;

Получить или установить стиль линий рамок позволяет свойство LineStyle коллекции Borders. Данное свойство может принимать следующие значения:

−4142 — линии отсутствуют;
1 — непрерывная линия;
−4119 — двойная линия;
−4115 — пунктирная линия;
4 — линия из точек с пунктиром;
5 — линия из двойных точек с пунктиром;
−4118 — линия из точек;
13 — линия из наклоненных точек и пунктира.

Пример использования свойства:

// Устанавливаем стиль для всех линий
Выбранный диапазон.Borders.LineStyle = 4;

Установить толщину линий рамок позволяет свойство Weight коллекции Borders, которое может принимать следующие значения:

1 — сверхтонкая;
2 — тонкая;
−4138 — средняя;
4 — толстая.

Пример использования свойства:

// Устанавливаем толстые линии рамки
Выбранный диапазон.Borders.Weight = 4;

Работа с листами книги

Если в шаблоне будущей печатной формы содержится несколько листов, то прежде чем начать заполнение печатной формы, необходимо выбрать конкретный лист. Для выбора конкретного листа используется свойство Item коллекции Sheets, представляющей собой все листы рабочей книги. Получить доступ к данной коллекции можно через свойства Worksheets объектов Application или Workbook.

Свойство Item позволяет получить ссылку на указанный лист коллекции по номеру или имени листа. Например:

Excel = СоздатьОбъект("Excel.Application");

// Создаём новую книгу 
НашаКнига = Excel.Workbooks.Add();

// Делаем текущим лист с именем "Лист1"
ТекущийЛист = НашаКнига.Worksheets.Item("Лист1");
// Присваиваем листу имя "Январь"
ТекущийЛист.Name = "Январь";

Надо заметить, вышеприведённый код может быть использован только в локализованной версии MS Excel, где листам по умолчанию присваиваются имена «Лист1», «Лист2» и т.д. В англоязычной же версии приложения листы именуются «Sheet1», «Sheet2» и т.д. Поэтому, если Вы не знаете, с какой версией MS Excel Вам придётся работать, обязательно предусмотрите дополнительные проверки или просто используйте индексы (номера) листов вместо их имён, например, НашаКнига.Worksheets.Item(1), НашаКнига.Worksheets.Item(2) и т.д. Нумерация листов в книге идёт слева направо по расположению ярлыков.

Для справки: Переименовывая лист, необходимо помнить, что новое имя не должно совпадать с именем уже имеющегося листа и не должно включать символы «/», «\», «?», «:» и «*». Длина имени не может быть более 31-ого символа.

Поскольку свойство Item коллекции Sheets является свойством по умолчанию (то есть свойством, название которого можно опускать), то допустимой является и следующая запись:

ТекущийЛист = НашаКнига.Worksheets("Лист1");

Кроме свойства Item в процедурах может также пригодиться свойство Count коллекции Sheets, возвращающее количество листов в коллекции.

Пример использования свойства:

// Если листов в книге менее 12, добавляем недостающие
Пока НашаКнига.Worksheets.Count < 12 Цикл
   НашаКнига.Worksheets.Add();
КонецЦикла;

Когда конкретный лист выбран

Когда конкретный лист (то есть объект Worksheet) выбран, дальнейшая работа с ячейками и диапазонами происходит практически также, как и в случае с шаблоном, содержащим один лист. Используются те же свойства, только не объекта Application, а объекта Worksheet. Например, для доступа к конкретной ячейке используем свойство Cells, возвращающее объект Range, представляющий в данном случае все ячейки в выбранном листе книги. Для обращения к конкретной ячейке необходимо указать её координаты: номер строки и номер столбца.

Excel = СоздатьОбъект("Excel.Application");

// Создаём новую книгу, используя шаблон-заготовку
ФайлШаблона = КаталогИБ() + "ExtForms\PrnForms\price_template.xlsx";
НашаКнига = Excel.Workbooks.Add(ФайлШаблона);

// Заполняем шапку первого листа книги
ТекущийЛист = НашаКнига.Worksheets(1);
ТекущийЛист.Cells(1, 1).Value = "Продажи за Январь";

. . .


// Заполняем шапку второго листа книги
ТекущийЛист = НашаКнига.Worksheets(2);
ТекущийЛист.Cells(1, 1).Value = "Продажи за Февраль";

. . .


// Заполняем шапку третьего листа книги
ТекущийЛист = НашаКнига.Worksheets(3);
ТекущийЛист.Cells(1, 1).Value = "Продажи за Март";

. . .

// Заполняем шапку первого листа книги
ТекущийЛист = НашаКнига.Worksheets(1);
ТекущийЛист.Range("A1").Value = "Продажи за Январь";

При заполнении табличной части печатной формы для работы с колонками и строками листа используются соответственно свойства Columns и Rows объекта Worksheet. Свойство Columns возвращает объект Range, представляющий все столбцы в выбранном листе. Свойство Rows возвращает объект Range, представляющий все строки в выбранном листе. Для обращения к конкретному столбцу или строке необходимо указать их порядковый номер.

Пример формирования табличной части печатной формы на выбранном листе:

// Заполняем первый лист книги
ТекущийЛист = НашаКнига.Worksheets(1);
. . .

// Формируем табличную часть
СпрНом = СоздатьОбъект("Справочник.Номенклатура");
ПорядковыйНомер = 1;
НомерСтрокиДляВставки = 5;
Если СпрНом.ВыбратьЭлементы(0)>0 Тогда
   Пока СпрНом.ПолучитьЭлемент()=1 Цикл
      Если (СпрНом.ЭтоГруппа()=1)ИЛИ(СпрНом.ПометкаУдаления()=1) Тогда
         // Пропускаем группы и помеченные на удаление
         Продолжить;
      КонецЕсли;

      // Вставляем новую строку
      ТекущийЛист.Rows(НомерСтрокиДляВставки).Insert(-4121, 1);
      // Заполняем строку данными
      ТекущийЛист.Cells(НомерСтрокиДляВставки, 1).Value = ПорядковыйНомер;
      ТекущийЛист.Cells(НомерСтрокиДляВставки, 2).Value = СокрЛП(СпрНом.Наименование);
      ТекущийЛист.Cells(НомерСтрокиДляВставки, 3).Value = СпрНом.Цена;
      ПорядковыйНомер = ПорядковыйНомер + 1;
      НомерСтрокиДляВставки = НомерСтрокиДляВставки + 1;
   КонецЦикла;
КонецЕсли;

// Удаляем строку-образец
ТекущийЛист.Rows(НомерСтрокиДляВставки).Delete(-4162);

Сохранение рабочей книги

Сформированную печатную форму документа пользователь при необходимости может сам сохранить на диске и закрыть окно, но иногда это необходимо сделать программно, не запрашивая подтверждения у пользователя. Сохранить вновь созданный документ на диске под указанным именем позволяет метод SaveAs() объекта Workbook.

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

Синтаксис метода:

SaveAs(<FileName>, [<FileFormat>], [<Password>],
       [<WriteResPassword>], [<ReadOnlyRecommended>], [<CreateBackup>],
       [<AccessMode>], [<ConflictResolution>], [<AddToMru>],
       [<TextCodepage>], [<TextVisualLayout>], [<Local>])

где

<FileName> — строка, содержащая имя файла и полный путь к нему. Если путь не указать, Excel сохранит файл в текущей папке;
<FileFormat> — формат файла, который будет использоваться при сохранении. Для нового файла по умолчанию используется формат установленной версии Excel. В качестве значений можно указать следующие числовые константы:
- −4158 — текстовый формат, соответствующий текущей платформе;
- 7 — формат Dbase 2;
- 8 — формат Dbase 3;
- 11 — формат Dbase 4;
- 20 — текст Windows;
- 21 — текст MS DOS;
- 46 — таблица XML;
- 51 — рабочая книга по умолчанию (*.xlsx);
- 56 — формат Excel 97-2003 Workbook;
- −4143 — нормализованная рабочая книга (*.xls);
<Password> — строка, чувствительная к регистру (не более 15 символов), содержащая пароль защиты на открытие файла;
<WriteResPassword> — строка, содержащая пароль для защиты от изменений файла;
<ReadOnlyRecommended> — флаг «Рекомендован только для чтения». Если установить true, то при последующем открытии этого файла будет отображаться сообщение, рекомендующее открыть файл только для чтения. Значение по умолчанию — false;
<CreateBackup> — флаг создания Backup-файла;
<AccessMode> — режим доступа для книги. Возможные значения:
- 1 — не изменяет режим доступа (значение по умолчанию);
- 2 — множественный доступ;
- 3 — эксклюзивный режим;
<ConflictResolution> — параметр, определяющий способ разрешения конфликта при сохранении книги. Возможные значения:
- 1 — выводить диалоговое окно, запрашивающее у пользователя способ разрешения конфликта;
- 2 — изменения, внесённые локальным пользователем всегда принимаются;
- 3 — изменения, внесённые локальным пользователем всегда отклоняются;
<AddToMru> — флаг добавления файла в список недавно использованных файлов;
<TextCodepage> — кодировка текста. Параметр не используется в версии U.S. English Microsoft Excel;
<TextVisualLayout> — визуальная разметка. Параметр не используется в версии U.S. English Microsoft Excel;
<Local> — язык сохранения. Значение true соответствует языку, используемому в Microsoft Excel; значение false — языку, используемому в VBA (который обычно является английским).

Пример использования:

НашаКнига.SaveAs("D:\otchet.xlsx");

Закрытие окна документа

Закрыть документ позволяет метод Close() объекта Workbook.

Синтаксис метода:

Close([<SaveChanges>], [<Filename>], [<RouteWorkbook>])

где

<SaveChanges> — параметр, задающий возможные действия при закрытии документа. Если в рабочей книге есть изменения, а параметр установлен в true, изменения сохраняются в файл. Если ещё нет имени файла, связанного с рабочей книгой, используется имя из параметра <Filename>. Если же имя файла опущено, пользователю будет предложено указать имя файла;
<Filename> — имя файла для сохранения изменений;
<RouteWorkbook> — в данном параметре следует указать true для направления документа следующему получателю. Если в документе нет прикреплённого списка маршрутизации, этот параметр игнорируется.

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

Пример использования метода:

НашаКнига.Close();

Для справки: Когда в открытый документ вносятся изменения или когда документ создаётся на основании шаблона, свойство Saved объекта Workbook получает значение false. Именно по значению этого свойства приложение определяет, выводить окно сохранения документа перед закрытием приложения или нет.

Если необходимо закрыть документ, не сохраняя изменений, следует просто присвоить свойству Saved значение true. Тогда Excel не будет выводить никаких окон.

Закрытие окна приложения

Для закрытия окна приложения следует вызвать метод Quit() объекта Application. Однако прежде чем закрыть окно приложения, следует убедиться, нет ли на компьютере других открытых документов Excel. Ведь если таковые есть, они тоже будут закрыты. Выполнить проверку позволяет свойство Count коллекции Workbooks, возвращающее количество экземпляров коллекции.

Пример:

Если Excel.Workbooks.Count = 0 Тогда
   // Если нет открытых документов,
   // закрываем окно приложения
   Excel.Quit();
КонецЕсли;

// Удаляем OLE-объект из памяти
Excel = 0;

Другие статьи по схожей тематике