Работа с Excel из 1С (создание, наполнение,формирование формул)

В данной статье, на простом примере будет показано создание файла Excel с формированием формул.

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

//Создаем COM-объект
Excel = Новый COMОбъект("Excel.Application"); 

//Добавление книги
Excel.WorkBooks.Add();

//снимаем защиту листа
Excel.ActiveSheet.UnProtect();

//Формируем заголовки колонок (текст,ширину и формат)======
Excel.ActiveSheet.Cells(1,1).Value="Код";
Excel.ActiveSheet.Cells(1,2).Value="Наименование";
Excel.ActiveSheet.Cells(1,3).Value="Значение";
	
Excel.ActiveSheet.Columns(1).ColumnWidth = 15;
Excel.ActiveSheet.Columns(2).ColumnWidth = 50;
Excel.ActiveSheet.Columns(3).ColumnWidth = 10;

//указываем явно текстовый формат колонки
Excel.ActiveSheet.Columns(1).NumberFormat = "@";
//========================================================

//Показательная выборка данных============================
запрос=новый запрос(
"ВЫБРАТЬ ПЕРВЫЕ 20
|	Номенклатура.Код,
|	Номенклатура.Наименование
|ИЗ
|	Справочник.Номенклатура КАК Номенклатура
|ГДЕ НЕ ЭтоГруппа
|УПОРЯДОЧИТЬ ПО Код" );
//========================================================

выборка=запрос.Выполнить().Выбрать();
Если выборка.Количество()=0 тогда возврат; конецесли;

ТекущийНомерСтроки=2;
СтрокаФормулы="=0";
Пока Выборка.Следующий() Цикл
	//Заполняем данными таблицу
	Excel.ActiveSheet.Cells(ТекущийНомерСтроки,1).Value = Выборка.Код;
	Excel.ActiveSheet.Cells(ТекущийНомерСтроки,2).Value = Выборка.Наименование;
	Excel.ActiveSheet.Cells(ТекущийНомерСтроки,3).Value = ТекущийНомерСтроки;

	//Снимаем защиту с ячейки значения
	Excel.ActiveSheet.Cells(ТекущийНомерСтроки,3).Locked = 0;

	//Формируем строку формулы итога
	СтрокаФормулы=СтрокаФормулы+"+"+Excel.ActiveSheet.Cells(ТекущийНомерСтроки,3).Address;

	ТекущийНомерСтроки=ТекущийНомерСтроки+1;

КонецЦикла;

//Настройка строки итога================================================
Excel.ActiveSheet.Cells(ТекущийНомерСтроки,2).Value		= "ИТОГО";//заголовок итога
Excel.ActiveSheet.Cells(ТекущийНомерСтроки,3).Formula	= СтрокаФормулы;//устанавливаем текст формулы
Excel.ActiveSheet.Rows(ТекущийНомерСтроки).Interior.ColorIndex	= 38;//выделяем цветом строку итога
Excel.ActiveSheet.Cells(ТекущийНомерСтроки,3).Locked	= 0;		//снимаем защиту с ячейки значения итога
//======================================================================

//Устанавливаем защиту на лист 
//(защита будет действовать на все ячейки, кроме тех, у которых мы явно сняли защиту через свойство Locked)
Excel.ActiveSheet.Protect();

//Визуализируем объект Excel
Excel.Visible=1;

//Освобождаем память от объекта
Excel=null;

Разберем код подробнее:

В первых строчках создается COM-объект, по средством методов и свойств которого мы и сформируем файл Excel.

Строка кода

Excel.ActiveSheet.Columns(1).NumberFormat = "@";
заслуживает особого внимания: здесь назначается формат колонки, где будет содержаться код ( например «0000001»). Если не указать явно формат, то значение в ячейке будет числовое (и отображаться будет так «1»).

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

Ниже в блоке «Настройка строки итога» производятся различные действия (смысл каждой разъяснен в комментариях), в том числе и заполнение свойства «Formula» ячейки, которое и служит для размещения строки формулы.

Ну и в заключении, устанавливаем защиту на лист, визуализируем Excel и освобождаем память от переменной.


Автор: Андрей Баринов

Дата публикации: 27.09.2010