v8: Как выгрузиться в EXCEL со сводными таблицами
Ключевые слова: Excel, pivottable, pivot, table, 1C, OLE, automation, exchange, сводная, таблица, обмен
Пошаговый рабочий алгоритм выгрузки в EXCEL на примере выгрузки отчетов производства за смену:
(1) Нарисовать форму обработки и к кнопке запуска прицепить процедуру:
Процедура КнопкаВыполнитьНажатие(Кнопка)
Если ПроверкаДат() Тогда
Состояние("Запущена выгрузка данных");
ПолучитьДанныеВТаблице();
Если чОтчетов<>0 Тогда
СортироватьТаблицу();
ВыгрузитьТаблицуВФайл();
ЗакрытьСистему();
Состояние("Выгрузка завершена");
Иначе
//ЗакрытьСистемуАварийно();
КонецЕсли;
КонецЕсли;
КонецПроцедуры
(2) Создадим функцию ПроверкаДат() корректного указания дат обработки
(3) Создадим процедуру ПолучитьДанныеВТаблице() выборки требуемых данных из документов в таблицу значений
(4) Создадим процедуру СортироватьТаблицу() для сортировки таблицы значений как нам нужно
(5) Создадим процедуру ВыгрузитьТаблицуВФайл() выгрузки ТЗ в Excel-файл:
Процедура ВыгрузитьТаблицуВФайл()
//1. Open Excel-file
Если FileOfReports = Неопределено Тогда
Попытка
FileOfReports = Новый COMОбъект("Excel.Application");
MyBook=FileOfReports.WorkBooks.Add(истина); //вместо истина можно вставить имя шаблона
FirstSheet=MyBook.Worksheets(1);
FirstSheet.Name = ОпределитьДату(НачПериода)+"-"+ОпределитьДату(КонПериода);
FirstSheet.Activate();
MyBook.SaveAs(КаталогDBF+"ОтчетыПроизводства.xls");
Исключение
Сообщить("ОШИБКА ЗАПИСИ В EXCEL-ФАЙЛ:"+ОписаниеОшибки());
Сообщить("Совет: Возможно файл, в который должна быть произведена запись, Вами открыт для просмотра");
Сообщить("Если это так, то закройте файл и запустите данную обработку повторно!");
КонецПопытки;
FileOfReports.Visible = 0;
Иначе
FileOfReports.Visible = 0;
//FileOfReports.Visible = 1;
КонецЕсли;
//2.==============================================================================================================
//Состояние("Анализ структуры файла...");
чОтчетов = ТаблицаОтчетов.Количество();
ЭлементыФормы.ИндикаторЗагрузки.МинимальноеЗначение = 0;
ЭлементыФормы.ИндикаторЗагрузки.МаксимальноеЗначение = чОтчетов;
ЭлементыФормы.Logo.Заголовок = "Выгружаю документы";
лСчетчикИндикатора = 0;
ВыгрузитьЗаголовокТаблицы(26);
//ВЫГРУЖАЮ ТАБЛИЦУ В ФАЙЛ ПО СТРОКАМ ~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
фСтарт=истина; //флаг первого прохода
чОт=2; //номер первой строки с данными
Пока чОтчетов >= 1 Цикл //Цикл по строкам Таблицы отчетов
чОтчетов = чОтчетов -1;
СтрокаОтчетов = ТаблицаОтчетов.Получить(чОтчетов);
Если (СтрокаОтчетов.ДатаВыпуска=ДатаВыпускаПред)
И (СтрокаОтчетов.Принадлежность=ПринадлежностьПред)
И (СтрокаОтчетов.Типоразмер=ТипоразмерПред)
И (СтрокаОтчетов.ТипОсновы=ТипОсновыПред)
Тогда
Иначе
ДатаВыпускаПред=СтрокаОтчетов.ДатаВыпуска;
ПринадлежностьПред=СтрокаОтчетов.Принадлежность;
ТипоразмерПред=СтрокаОтчетов.Типоразмер;
ТипОсновыПред=СтрокаОтчетов.ТипОсновы;
Если фСтарт Тогда
фСтарт = ложь; //Уже не первый проход
Иначе
чДо=глСчетчикСтрок;
ВыгрузитьСтрокуИтог(чОт,чДо,26);
КонецЕсли;
чОт=глСчетчикСтрок;
КонецЕсли;
ВыгрузитьСтрокуОтчетовВФайл(СтрокаОтчетов,26);
лСчетчикИндикатора = лСчетчикИндикатора + 1;
ЭлементыФормы.ИндикаторЗагрузки.Значение = лСчетчикИндикатора;
ОбработкаПрерыванияПользователя();
КонецЦикла;
чДо=глСчетчикСтрок;
ВыгрузитьСтрокуИтог(чОт,чДо,26);
//КОНЕЦ ВЫГРУЗКИ СТРОК В ФАЙЛ
Состояние("Данные файла выгружены");
//~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~
libСообщитьНК1("ИНФО: ШАГ 1 ВЫПОЛНЕН. Данные файла Отчеты.DBF успешно считаны",1);
//Состояние("Создание сводных таблиц");
ЭлементыФормы.Logo.Заголовок = "Создание сводных таблиц";
//РазделитьОбласти();
//Range("A2").Select
//ActiveWindow.FreezePanes = True
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"наим","Дата","Наим","","К");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"ЮВ","Дата","Наим,Каркас,Вид","","К");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"В пр(2)","Дата","Наим,Каркас,Основа","","К");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"В пр","Дата","Каркас,Основа","","К");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"Основа","Дата","Основа","","кг основы");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"Напыление","","Напылен","","кг напылен");
СоздатьТаблицуЛист(MyBook.Worksheets(1).Name,"Клей","","Дата","","кг клея");
КонецПроцедуры
Процедура СоздатьТаблицуЛист(стрДанные,стрСводнаяТаблица,стрПоле1,стрПоле2,стрПоле3,стрПоле4);
//Создает сводную таблицу стрСводнаяТаблица с полями (стрПоле4 - поле реквизита)
//и размещает ее на одноименном листе
//Заполняет сводную таблицу данными, взятыми с листа стрДанные
//Поле стрПоле2 может быть полем списка через запятую
//0. Вспомогательный разбор списка значений в стрПоле2 и определение числа значений
лПоля.Очистить(); //сбросим все значения, если таковые весели в памяти
стрПоля=стрПоле2;
i=1;//число значений в списке
Пока Найти(стрПоля,",")<>0 Цикл
n=Найти(стрПоля,",");
лПоля.Добавить(Сред(стрПоля,1,n-1));
стрПоля=Сред(стрПоля,n+1);
i=i+1;
КонецЦикла;
лПоля.Добавить(стрПоля);
//1. Установить диапазон выборки данных
FirstSheet.Activate();
дДиапазонДанных=FirstSheet.UsedRange.Address;
ДиапазонДанных=FirstSheet.Name+"!"+Строка(дДиапазонДанных);
//2. Создать кэш под сводную таблицу
// аргументы не переменные, а текст с именем переменных (crazy)
SourceType = xlDatabase;
SourceData = ДиапазонДанных;
Cache = MyBook.PivotCaches().Add(SourceType, SourceData);
//для отладки: FileOfReports.Visible = 1;
//3. Породить из кэша сводную таблицу с именем стрСводнаяТаблица
TableDestination="";
TableName=стрСводнаяТаблица;
Cache.CreatePivotTable(TableDestination,TableName);
//4. Определить представление данных в сводной таблице
//стрПоле1
Если СокрЛП(стрПоле1)<>"" Тогда
MyBook.ActiveSheet.PivotTables(TableName).PivotFields(стрПоле1).Orientation = 3; //xlRowField=1 то, что указывается справа у таблицы
КонецЕсли;
//стрПоле2
чНомер=0;
Пока чНомер<i Цикл
Если СокрЛП(лПоля[чНомер].Значение)<>"" Тогда
MyBook.ActiveSheet.PivotTables(TableName).PivotFields(лПоля[чНомер].Значение).Orientation = 1;
КонецЕсли;
чНомер = чНомер + 1;
КонецЦикла;
//стрПоле3
Если СокрЛП(стрПоле3)<>"" Тогда
MyBook.ActiveSheet.PivotTables(TableName).PivotFields(стрПоле3).Orientation = 2; //xlColumnField=2 то, что указывается в вепрхней части таблицы
КонецЕсли;
//стрПоле4
Если СокрЛП(стрПоле4)<>"" Тогда
MyBook.ActiveSheet.PivotTables(TableName).PivotFields(стрПоле4).Orientation = 4; //xlCount=4 data
КонецЕсли;
//5. Скрыть пустые строки в стрПоле2, предварительно проверив, что таковые СУЩЕСТВУЮТ!!!!
//стрПоле2
чНомер=0;
Пока чНомер<i Цикл
Если СокрЛП(лПоля[чНомер].Значение)<>"" Тогда
Если IfPivotItemExists(TableName,лПоля[чНомер].Значение,"(пусто)")Тогда
MyBook.ActiveSheet.PivotTables(TableName).PivotFields(лПоля[чНомер].Значение).PivotItems("(пусто)").Visible = False;
КонецЕсли;
КонецЕсли;
чНомер = чНомер + 1;
КонецЦикла;
//6. Закрыть вспомогательные панели
MyBook.ShowPivotTableFieldList = False;
FileOfReports.CommandBars("PivotTable").Visible = False;
//7. Присвоить активному листу имя, заданное в параметрах процедуры
MyBook.ActiveSheet.Name = стрСводнаяТаблица;
//8. Сохранить файл
MyBook.Save();
КонецПроцедуры
Функция IfPivotItemExists(strTableName,strFieldName,strItemName)
//Проверяет существование позиции поля сводной таблицы со значением strItemName
nItems = MyBook.ActiveSheet.PivotTables(strTableName).PivotFields(strFieldName).PivotItems().Count;
i = 1;
Пока i <= nItems Цикл
Если strItemName=MyBook.ActiveSheet.PivotTables(strTableName).PivotFields(strFieldName).PivotItems(i).Name Тогда
//Искомая позиция strItemName существует:
//Сообщить("Позиция "+strItemName+" найдена");
Возврат True;
КонецЕсли;
i=i+1;
КонецЦикла;
//Сообщить("Позиция "+strItemName+" не найдена");
Возврат False;
КонецФункции |