Сводная таблица Excel из 1С

Неожиданно столкнулся с задачей выкинуть из 1С 7.7 данные в Excel и сделать из них сводную таблицу. В целом, все это можно использовать и под 1С 8, но это уже сами, если что )))

И вдруг оказалось, что задача не настолько тривиальна, как казалось издалека. Поэтому, ткнувшись носом в ряд граблей, решил написать, вдруг, кому-то еще понадобится.

Итак, первое, с чем я столкнулся - это константы Excel, всякие xlYes, xlNo и т.д. Я вдруг обнаружил, что выковыривать их значения из документации Microsoft довольно муторно, а обратиться к ним по имени через внешнее соединение нельзя.

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

Конкретно тут используются константы:

xlSrcRange = 1
xlYes = 1
xlNo = 2
xlDatabase = 1

Вторая грабля - это создание нового листа, дело в том, что надо использовать Null в качестве передаваемого параметра, а из 1С 7.7 это ну никак не получится, поэтому пришлось использовать скрипты.

Треьтья грабля, на которую я убил более всего времени - это коллекция PivotCaches. В отличии от остальных коллекций листа Excel,это не объект коллекции, а метод, возвращающий коллекцию (внезапно, правда), то есть обращаться к нему надо так:

ОбъектЛистExcel.PivotCaches().Create( ну и далее уж параметры

Далее уже код:

//============================================================
Функция ПолучитьЛистExcel(Книга, ТекущийЛист, Заголовок="")
	//__ как, по вашему передать null из 1С 7.7 ?????
	// в общем, создаем скрипт
	врСкрипт = СоздатьОбъект("MSScriptControl.ScriptControl");
	врСкрипт.Language = "javascript";
	// кладем туда параметры
	врСтраницы = Книга.WorkSheets();
	врСкрипт.AddObject("Worksheets", врСтраницы);
	врСкрипт.AddObject("LastWorksheet", ТекущийЛист);
	// и возвращаем вычисленное там значение уже в переменную 1С
	врЛист = врСкрипт.eval("Worksheets.add(null,LastWorksheet,null,null);");
	Если СокрЛП(Заголовок)="" Тогда
		// ну и переименовываем по необходимости
		врЛист.Name = СокрЛП(Заголовок);
	КонецЕсли;
	Возврат врЛист;
КонецФункции	// ПолучитьЛистExcel

//__ тут у нас есть
//  глКонстантыExcel - это список значений с константами Excel и их числовыми значениями
//  ИмяФайла - это существующий, сохраненный ранее файл с отчетом, в книге на 1 листе лежат данные
//  Габариты - это список значений с границами данных в отчете (Лево, Верх, Право, Низ)
//============================================================
Процедура УмнаяТаблицаExcel(ИмяФайла, Габариты)
	Попытка
		врExcel = СоздатьОбъект("Excel.Application");     
		врExcel.DisplayAlerts = 0;  // без лишних вопросов
	Исключение
		Сообщить("Excel на компьютере не установлен..","!");
		Возврат;
	КонецПопытки;
	врКнигаТемп = врExcel.WorkBooks.Open(ИмяФайла); //открываем файл
	врExcel.Visible = 1; // пусть будет видимый для пользователя
	врЛистТемп = врКнигаТемп.WorkSheets(1); // получаем 1 лист
	врЛистТемп.Name = "Данные"; // переименовываем его
	// получаем диапазон ячеек данными
	врДиапазон = врЛистТемп.Range(врЛистТемп.Cells(Габариты.Получить("Верх"), Габариты.Получить("Лево")),врЛистТемп.Cells(Габариты.Получить("Низ"), Габариты.Получить("Право")));
	// создаем умную таблицу
	врУмнаяТаблица = врЛистТемп.ListObjects.Add(глКонстантыExcel.Получить("xlSrcRange"), врДиапазон, глКонстантыExcel.Получить("False"), глКонстантыExcel.Получить("xlYes"));
	// задаем ей имя
	врУмнаяТаблица.Name = "SmTable1";
	// получаем PivotCahce - вот тут я особенно долго мучался и так и не нагуглил ответа
	// даже немного повезло, дело в том, что я вызывал PivotCaches.Create(...,
	// а оказалось, что PivotCaches - это не коллекция, а метод, возвращающий коллекцию,
	// и обращаться к нему надо PivotCahces().Create(...
	врКэшДляСвода = врКнигаТемп.PivotCaches().Create(глКонстантыExcel.Получить("xlDatabase"), "SmTable1");
	// создаем новый лист, куда помести уже сводную таблицу
	врНовЛист = ПолучитьЛистExcel(врКнигаТемп, врЛистТемп, "Свод");
	// ну и, наконец, создаем саму сводную таблицу
	// в качестве последнего параметра я передаю имя умной таблицы, так как у Microsoft
	// в описании сказано, что следует передавать имя, потому что при передаче объекта 
	// может "неожиданно возникнуть ошибка" (с) Microsoft
	врСвод = врКэшДляСвода.CreatePivotTable(врНовЛист.Cells(2,2), "PvTable1");
	
КонецПроцедуры

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

Вот такой вот пример. По правилам надо указать точную версию, где тестировалось - это 1С 7.70.027 (правда, 7.7 уже давно не обновляется).