Как выгрузиться в EXCEL со сводными таблицами

Пошаговый рабочий алгоритм выгрузки в EXCEL на примере выгрузки отчетов производства за смену(Автор статьи: Funny):

(1) Нарисовать форму обработки и к кнопке запуска прицепить процедуру:

Код 1C v 8.х

 Процедура КнопкаВыполнитьНажатие(Кнопка)
    
    Если ПроверкаДат() Тогда 
         Состояние("Запущена выгрузка данных");
         ПолучитьДанныеВТаблице();
         Если чОтчетов<>0 Тогда
                СортироватьТаблицу();
              ВыгрузитьТаблицуВФайл();
              ЗакрытьСистему();
              Состояние("Выгрузка завершена");
         Иначе
              //ЗакрытьСистемуАварийно();
 
         КонецЕсли;
    КонецЕсли;    
КонецПроцедуры

(2) Создадим функцию ПроверкаДат() корректного указания дат обработки
(3) Создадим процедуру ПолучитьДанныеВТаблице() выборки требуемых данных из документов в таблицу значений
(4) Создадим процедуру СортироватьТаблицу() для сортировки таблицы значений как нам нужно
(5) Создадим процедуру ВыгрузитьТаблицуВФайл() выгрузки ТЗ в Excel-файл:

Код 1C v 8.х

 Процедура ВыгрузитьТаблицуВФайл()
    
        //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").S_elect
 
            //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;
        
КонецФункции

Код 1C v 7.x

  РабочаяКнига = СоздатьОбъект("Excel.Application");
РабочаяКнига.SheetsInNewWorkbook = 1;
РабочаяКнига.Caption = "Отчет по договорам отгрузки";
NewBook = РабочаяКнига.Workbooks;
List = NewBook.Add;

СписокСтолбцов = СоздатьОбъект("СписокЗначений");
СписокСтолбцов.ДобавитьЗначение("Модель");
СписокСтолбцов.ДобавитьЗначение("Разнорядка");
СписокСтолбцов.ДобавитьЗначение("По договору");
СписокСтолбцов.ДобавитьЗначение("Отгрузили");
СписокСтолбцов.ДобавитьЗначение("Осталось");

//Заполним наименование столбцов: Модель, Разнорядка, Количество
Для Столбец = 1 По СписокСтолбцов.РазмерСписка() Цикл
	Ячейка = РабочаяКнига.Cells(1,Столбец);
	Ячейка.Value = СписокСтолбцов.ПолучитьЗначение(Столбец);
КонецЦикла; 

//Формируем запрос по регистру
//	//Создание объекта типа Запрос
Запрос = СоздатьОбъект("Запрос");
ТекстЗапроса =
"//{{ЗАПРОС(Сформировать)
|Период с ДатаНач по ДатаКон;
|Разнорядка = Регистр.УчетДоговоров.Разнорядка;
|Модель = Регистр.УчетДоговоров.Модель.Группа;
|Колво = Регистр.УчетДоговоров.Количество;
|Функция КолвоПриход = Приход(Колво);
|Функция КолвоРасход = Расход(Колво);
|Функция КолвоОст = КонОст(Колво);
|Группировка Разнорядка;
|Группировка Модель;
|"//}}ЗАПРОС
;

// Если ошибка в запросе, то выход из процедуры
Если Запрос.Выполнить(ТекстЗапроса) = 0 Тогда
	Возврат;
КонецЕсли;

НомерСтроки = 1;
Пока Запрос.Группировка("Разнорядка") = 1 Цикл
	Пока Запрос.Группировка("Модель") = 1 Цикл 
		Модель = Запрос.Модель;
		Разнорядка = Запрос.Разнорядка;
		КолПоДоговору = Запрос.КолвоПриход;
		КолОтгрузили = Запрос.КолвоРасход;
		КолОсталось = Запрос.КолвоОст;
		НомерСтроки = НомерСтроки + 1;
		//Организум заполнение ячеек Excel
		Для Ст = 1 По СписокСтолбцов.РазмерСписка() Цикл
			Ячейка = РабочаяКнига.Cells(НомерСтроки,Ст);
			Если Ст = 1 Тогда
				Ячейка.Value = Модель;
			ИначеЕсли Ст = 2 Тогда
				Ячейка.Value = Строка(Разнорядка);
			ИначеЕсли Ст = 3 Тогда
				Ячейка.Value = КолПоДоговору;
			ИначеЕсли Ст = 4 Тогда
				Ячейка.Value = КолОтгрузили;
			ИначеЕсли Ст = 5 Тогда
				Ячейка.Value = КолОсталось;
			КонецЕсли;
		КонецЦикла;	
	КонецЦикла;
КонецЦикла; 
СводнаяТаблица = РабочаяКнига.ActiveSheet.PivotTableWizard;
//Метод СводнаяТаблица.Name возвращает имя сводной таблицы
//Можно задать имя таблицы СводнаяТаблица.Name = "МояТаблица"
СводнаяТаблица.SmallGrid = 0;
//Разворачивает макет сводной таблицы 

//Обращение к ячейкам сводной таблицы возможно по номеру
//Например, СводнаяТаблица.PivotFields(1).Value 
//возвращает значение "Модель"

//Попробуем с другого конца
//1 - Row
//2 - Column
//3 - Page
//4 - Data
СводнаяТаблица.PivotFields(1).Orientation = 1;    //Модель 
СводнаяТаблица.PivotFields(2).Orientation = 3;    //Разнорядка
СводнаяТаблица.PivotFields(3).Orientation = 4;    //По договору
СводнаяТаблица.PivotFields(4).Orientation = 4;    //Отгрузили
СводнаяТаблица.PivotFields(5).Orientation = 4;    //Осталось

//Пройдемся по форме отчета
СводнаяТаблица.Format(1);

//Попробуем закрыть панель инструментов сводной таблицы
РабочаяКнига.CommandBars("PivotTable").Visible = 0;
РабочаяКнига.Visible = 1;

Как выгрузиться в EXCEL со сводными таблицами

Похожие FAQ

Еще в этой же категории

Ключевые слова и Изображения

Слова упорядочены по частоте использования в тексте

Изображения