Выгрузка данных в Excel с добавлением формул (нюансы формирования формул)

Ошибки, которые поймал при написании формул:

Ошибка при установке значения атрибута контекста (FormulaR1C1)

Ошибка при установке значения атрибута контекста (FormulaR1C1Local)

О том, как избежать появления, смотрите в конце статьи.

1. Формируем обычный отчет в табличный документ при помощи СКД
2. Далее, имея сформированный табличный документ на экране, начинаем выгрузку
3. Выбираем каталог

&НаКлиенте
Процедура ВыгрузитьВЁксель(Команда)
    
    //вызываем диалог выбора каталога сохранения файла (с возможностью изменения и самого имени файла при необходимости)
    лОписаниеОповещения = Новый ОписаниеОповещения("ВыборФайлаДляСохранения",ЭтаФорма);
    лДиалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Сохранение);
    лДиалог.Фильтр="Файлы Excel(2007-2010)|*.xlsx";
    лДиалог.ПолноеИмяФайла = СтрШаблон(("Report_for_example_%1",Формат(ТекущаяДата(),"ДФ=yyyy_MM_dd_HH_mm_ss"));                
    лДиалог.Показать(лОписаниеОповещения);
    
КонецПроцедуры

4. Выполняем выгрузку в excel

&НаКлиенте
Процедура ВыборФайлаДляСохранения(аВыбраныйФайл,аДопПараметры) Экспорт      
	
	//не выбрали куда сохранять делать дальше нечего
	Если аВыбраныйФайл = Неопределено Тогда
		Возврат;	
	КонецЕсли; 
		
    //сюда мы получим excel файл шаблона то есть файл с раскрашенными колонками и т. и т. п.
	лДвоичныеДанныеШаблона = Неопределено; 
	//тут убиваем двух зайцев получаем шаблон excel и собственно данные для помещения в excel.
	лСрРаРезультат = СложитьДанныеВМассив(лДвоичныеДанныеШаблона);
	
	//получаем количество колонок и строк будущего файла excel
	лКолонок = лСрРаРезультат.Количество();
	лСтрок = лСрРаРезультат["column_0"].Количество();
	Если лСтрок = 0 Тогда
		Возврат;
	КонецЕсли;
	
	//проверяем что шаблон файла excel получен 
	Если лДвоичныеДанныеШаблона = Неопределено Тогда
		Возврат;
	КонецЕсли;	

	лИмяШаблона = аВыбраныйФайл[0];
	лДвоичныеДанныеШаблона.Записать(лИмяШаблона);
	
	//Создаем объект excel для выгрузки данных
	Попытка
		лЕксель = Новый COMОбъект("Excel.Application");
		лЕксель.DisplayAlerts=0;    
		лЕксельВерсия=Число(лЕксель.Version);		
	Исключение
		Возврат
	КонецПопытки;
	
	лЕксельКнига = лЕксель.WorkBooks.Open(лИмяШаблона);
	лЕксельЛист = лЕксельКнига.Worksheets("Report_for_example");
	
	лчТекНомерКолонки = 1;  
	лбПервыйРаз = Истина;
	лТекПроцент=0;
	Для Каждого лЗначение из лСрРаРезультат цикл
		//демонстрируем прогресс)))
		Если Цел(лчТекНомерКолонки/лКолонок*10)>=лТекПроцент тогда
			лТекПроцент=Цел(лчТекНомерКолонки/лКолонок*10);
		КонецЕсли;	
		Состояние("Writing data..."+(лТекПроцент*10)+"%");
		//помещаем данные в excel массивами содержащими колонки отчета
		лЕксельОбласть = лЕксельЛист.Range(лЕксельЛист.Cells(2,лчТекНомерКолонки),
                             лЕксельЛист.Cells(лСтрок+1,лчТекНомерКолонки));
		лЕксельОбластьФормата = лЕксельЛист.Range(лЕксельЛист.Cells(2,лчТекНомерКолонки), 
                                    лЕксельЛист.Cells(2,лчТекНомерКолонки));
		лЕксельОбласть.NumberFormat = лЕксельОбластьФормата.NumberFormat;
		Если ТипЗнч(лЗначение.Значение) = Тип("Массив") Тогда
			лМассивДляЗаливки = Новый Массив();
			лМассивДляЗаливки.Добавить(лЗначение.Значение);
			лМассКСА = Новый COMSafeArray(лМассивДляЗаливки,"VT_VARIANT");
			лЕксельОбласть.Value = лМассКСА;
		Иначе
			//заполняем формулы
			Попытка
				лЕксельОбласть.FormulaR1C1Local = лЗначение.Значение;
			Исключение
			    лЕксельОбласть.FormulaR1C1 = лЗначение.Значение;
			КонецПопытки;
		КонецЕсли; 
		лчТекНомерКолонки = лчТекНомерКолонки + 1;
	КонецЦикла;	 
	Попытка
		//сохраняем файл после выгрузки данных
		лЕксельКнига.SaveAs(лИмяШаблона,51); 
	Исключение
		лЕксель.Quit();
		Возврат;
	КонецПопытки;
	лЕксель.Quit();

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

5. Готовим данные для выгрузки на базе табличного документа

&НаСервере
Функция СложитьДанныеВМассив(аДвоичныеДанныеШаблона)
	
	лСрРаРезультат = Новый Структура;
	ПоследняяСтрока = Результат.ВысотаТаблицы;
    ПоследняяКолонка = Результат.ШиринаТаблицы;
    лОбластьЯчеек = Результат.Область(1, 1, ПоследняяСтрока, ПоследняяКолонка);
    лИсточникДанных = Новый ОписаниеИсточникаДанных(лОбластьЯчеек);
    лПостроительОтчета = Новый ПостроительОтчета;
    лПостроительОтчета.ИсточникДанных = лИсточникДанных;
    лПостроительОтчета.Выполнить();
    лТзРезультат = лПостроительОтчета.Результат.Выгрузить();
	Для лчНомерКолонки = 0 По лТзРезультат.Колонки.Количество() - 1 Цикл
		лСрРаРезультат.Вставить(лТзРезультат.Колонки[лчНомерКолонки].Имя,лТзРезультат.ВыгрузитьКолонку(лчНомерКолонки));
	КонецЦикла;
	//далее добавляем две пустых колонки их заполнит пользователь при работе в Excel
	лСрРаРезультат.Вставить("column_1","");
	лСрРаРезультат.Вставить("column_2","");
	//добавляем колонки с формулами
	лСрРаРезультат.Вставить("column_3","=RC[-1]- IF(NOT(ISBLANK(RC[-10])),RC[-10],RC[-11])");
    лСрРаРезультат.Вставить("column_4","=IF(RC[-1]<=3,0,IF(AND(RC[-1]>3,RC[-1]<14),RC[-1],IF(RC[-1]>=14,13)))");
	лСрРаРезультат.Вставить("column_5","=IF(RC[-2]>13,RC[-2]-13,IF(RC[-2]<=13,0))");
	лСрРаРезультат.Вставить("column_6","=ROUND(RC[-2]*(RC[-24]*0.005),2)");
	лСрРаРезультат.Вставить("column_7","=ROUND(RC[-2]*(RC[-25]*0.01),2)");
	лСрРаРезультат.Вставить("column_8","=RC[-1]+RC[-2]");
	аДвоичныеДанныеШаблона = Отчеты.НашОТчет.ПолучитьМакет("ШаблонВыгрузки");
	Возврат лСрРаРезультат;
	
КонецФункции

6. Особенности формирования формул

Если про это забыть, то получим:

Ошибка при установке значения атрибута контекста (FormulaR1C1)

Ошибка при установке значения атрибута контекста (FormulaR1C1Local)

Пример:

Формула на английском:

"=IF(RC[-1]<=3,0,IF(AND(RC[-1]>3,RC[-1]<14),RC[-1],IF(RC[-1]>=14,13)))"

"=ROUND(RC[-2]*(RC[-25]*0.01),2)"

Формула на русском:

"=ЕСЛИ(RC[-1]<=3;0;ЕСЛИ(И(RC[-1]>3;RC[-1]<14);RC[-1];ЕСЛИ(RC[-1]>=14;13)))"

"=ОКРУГЛ(RC[-2]*(RC[-25]*0,01);2)"

RC[-1] – предыдущая ячейка

RC[+1] – следующая ячейка

Заполнять формулу можно вот так:

лЕксельОбласть.FormulaR1C1Local = Формула;

Или так:

лЕксельОбласть.FormulaR1C1 Формула;

Надеюсь, было полезно! Если нет, не кидайтесь тухлыми помидорами.))) Просто проходите мимо.