gifts2017

Экспорт нескольких MXL таблиц в один XLS файл, на отдельные листы. Простой алгоритм

Опубликовал Dmitry Vidmanov (etmarket) в раздел Программирование - Практика программирования

Статья посвящена распространённому вопросу - как сохранить несколько таблиц (отчетов) в формате MXL, с которым работает 1С, на отдельные листы одного Excel файла. Освещается простой алгоритм решения проблемы штатными средствами, без использования внешних модулей и библиотек (не относящихся к 1С и Excel).

На просторах интернета есть целая масса различных алгоритмов, написанных для любой версии 1С. Я же искал самый простой способ, который не использует внешних модулей (не относящихся к встроенным функциям 1С и MS Excel).

Как оказалось, есть несколько проблем при сохранении файла XLS штатными средствами. Самая основная из них - быстродействие. При сохранении больших MXL отчетов в файле XLS, экспорт происходит достаточно долго. Но в моем случае это не очень важно, так как отчеты небольшие, а компьютер мощный. Даже если придется сохранять отчет, в котором больше 1000 строк, работа все равно будет выполнена менее, чем за 10 минут.

Основная идея предложенного решения заключается в следующем: для каждой новой MXL таблицы создаем временный файл, который используется как буфер для копирования таблицы на новый лист результирующего XLS файла. Временный XLS файл сохраняется штатными средствами. Результирующий файл создается через OLE технологию, с использованием функций объекта Excel.

Ниже приведен код, сохраняющий несколько MXL таблиц на отдельные листы XLS файла:

//******************************************************************************
//Функция генерирует имя временного XLS файла, используя имя файла приемника
//
Функция ВременныйXLSфайл(Знач ВремФайл)
	Возврат СтрЗаменить(ВремФайл,".xls","_temp.xls");
КонецФункции //ВременныйXLSфайл()

//******************************************************************************
//Процедура копирует лист из XLS файла источника в XLS файл приемника
// Excel - глобальная переменная, используемая для связи с EXCEL
//
Процедура СкопироватьСтраницуЭксель( ИмяФайлаИсточника, ИмяФайлаПриемника, ИмяСтраницы = "", ВсегоЛистов)
        //Создаем объект Excel.Application
	Попытка
		Excel = СоздатьОбъект("Excel.Application"); 
	Исключение
		Сообщить("ОШИБКА:"+ОписаниеОшибки());
		Возврат;
	КонецПопытки;
    РабочаяКнига = Excel.WorkBooks;
	//Открываем временный файл
	Попытка
		КнигаИсточник = РабочаяКнига.Open(ИмяФайлаИсточника);
	Исключение
		Сообщить("Не удалось открыть файл источник " + ИмяФайлаИсточника+". " + ОписаниеОшибки());
		Возврат;
	КонецПопытки;
	//Открываем рузультирующий файл
	Если ФС.СуществуетФайл(ИмяФайлаПриемника)=1 Тогда
		КнигаПриемник = РабочаяКнига.Open(ИмяФайлаПриемника);	
	Иначе
		КнигаПриемник = РабочаяКнига.Add();
	КонецЕсли;	
	//Приступаем к копированию листа с данными из временного файла
	Если ИмяСтраницы = "" Тогда
	    ИмяСтраницы = "Лист Х";
	КонецЕсли;
	Состояние("Копирование данных - "+ИмяСтраницы+". Ожидайте...");
	Для Инд = 1 По КнигаИсточник.Worksheets.Count Цикл
	    
		ЛистПриемник = КнигаПриемник.Sheets(1);	
		КнигаИсточник.Sheets().Copy(ЛистПриемник);
		
		Имя = КнигаПриемник.Sheets(1);
		Имя.Name = ИмяСтраницы;
	
	КонецЦикла;	
	//Сохранение изменений в результирующем файле
	Если ФС.СуществуетФайл(ИмяФайлаПриемника)=1 Тогда
		Попытка
			КнигаПриемник.Save();
		Исключение
			Сообщить("Не удалось сохранить файл приемник " + ИмяФайлаПриемника+". " + ОписаниеОшибки());
		КонецПопытки
	Иначе
		Попытка
			КнигаПриемник.SaveAs(ИмяФайлаПриемника);
		Исключение
			Сообщить("Не удалось сохранить файл приемник " + ИмяФайлаПриемника+". " + ОписаниеОшибки());
		КонецПопытки
	Конецесли;
	
	ВсегоЛистов = КнигаПриемник.Worksheets.Count;
	Excel.Quit();
	Excel = ПолучитьПустоеЗначение();

КонецПроцедуры //СкопироватьСтраницуЭксель()

//******************************************************************************
// СохранитьРезультирующийXLS() 
// Создаёт XLS-файл с несколькими листами
// Параметры
//      ИмяФайла - Полное имя выходного файла
//      ТабMXL   - Список значений, в котором:
//               Представление   - Имя листа
//               Значение        - Таблица MXL
//      ТекФайл  - Короткое имя результирующего файла
//
Процедура СохранитьРезультирующийXLS(ИмяФайла, ТабMXL, ТекФайл = "")
	Перем ВремФайл, мЛист, Ключ, СкопированоЛистов, ВсегоЛистов;
	Если (ПустоеЗначение(ТабMXL)=1)
         ИЛИ (ТипЗначения(ТабMXL)<>100) Тогда
	    Возврат;
	ИначеЕсли (ТабMXL.РазмерСписка()=0) Тогда
		Возврат;
	КонецЕсли;
	ВремФайл    = ВременныйXLSфайл(ИмяФайла); 
	Если ФС.СуществуетФайл(ВремФайл)=1 Тогда
		ФС.УдалитьФайл(ВремФайл);
	КонецЕсли;  
        Excel = ПолучитьПустоеЗначение();	
	Попытка
		Excel = СоздатьОбъект("Excel.Application"); 
	Исключение
		Сообщить("ОШИБКА: "+ОписаниеОшибки());
		Возврат;
	КонецПопытки;
        //Отключаем вывод предупреждений Excel и его появление на экране
	Excel.DisplayAlerts  = 0; 
	Excel.ScreenUpdating = 0;
        Excel.EnableEvents   = 0;
        Excel.Visible        = 0;
        //Выберем предпочтительный формат Excel файла
	Если Число(Excel.Application.Version) >= 12 Тогда
		ИмяФайла = СтрЗаменить(ИмяФайла, ".xls", ".xlsx");
		ТекФайл = СтрЗаменить(ТекФайл, ".xls", ".xlsx");
	КонецЕсли;
	Excel.Quit();
	Excel = ПолучитьПустоеЗначение();
	Если ФС.СуществуетФайл(ИмяФайла)=1 Тогда
		ФС.УдалитьФайл(ИмяФайла);
	КонецЕсли;
        //Приступаем к разбору списка значений с таблицами MXL
	ВсегоЛистов = 0; СкопированоЛистов = 0;
    Для мЛист = -ТабMXL.РазмерСписка() по -1 Цикл 
        Ключ = "Лист "+мЛист;
        // Сохраняем таблицу во временный файл
        ТабДок = ТабMXL.ПолучитьЗначение(-мЛист,Ключ);
		Состояние("Экспорт данных - "+Ключ+". Ожидайте..."); 
		Попытка
	    	ТабДок.Записать(ВремФайл,1); 
		Исключение
			Сообщить("ОШИБКА: "+ОписаниеОшибки());
			Возврат;
		КонецПопытки;
                //Копируем на новый лист файла приемника таблицу из временного файла
		Если ФС.СуществуетФайл(ВремФайл)=1 Тогда
        	СкопироватьСтраницуЭксель(ВремФайл, ИмяФайла, Ключ, ВсегоЛистов);
			ФС.УдалитьФайл(ВремФайл);
			СкопированоЛистов = СкопированоЛистов + 1;
		КонецЕсли;
	КонецЦикла;

        //Удаляем пустые листы, которые автоматически создает EXCEL для нового файла
	Если (СкопированоЛистов > 0)И(СкопированоЛистов < ВсегоЛистов)И(ФС.СуществуетФайл(ИмяФайла)=1) Тогда
	    Если (ПустоеЗначение(Excel)=0) Тогда
			Excel.Application.Quit();
			Excel.Quit();
		КонецЕсли;
		
		Попытка
			Excel = СоздатьОбъект("Excel.Application"); 
		Исключение
			Сообщить("ОШИБКА: "+ОписаниеОшибки());
			Возврат;
		КонецПопытки;
	    РабочаяКнига         = Excel.WorkBooks;
	
		Попытка
			КнигаПриемник = РабочаяКнига.Open(ИмяФайла);
		Исключение
			Сообщить("Не удалось открыть файл источник " + ИмяФайла+". " + ОписаниеОшибки());
			Возврат;
		КонецПопытки;
		
		УдаленоЛистов = 0;
		Пока (СкопированоЛистов < КнигаПриемник.Worksheets.Count) Цикл
			КнигаПриемник.Worksheets(КнигаПриемник.Worksheets.Count).Delete();
			УдаленоЛистов = УдаленоЛистов + 1;
		КонецЦикла;
		
		Если УдаленоЛистов > 0 Тогда
		    Попытка
				КнигаПриемник.Save();
			Исключение
				Сообщить("Не удалось сохранить файл приемник " + ИмяФайла+". " + ОписаниеОшибки());
			КонецПопытки
		КонецЕсли;
		Excel.Quit();
		Excel                = ПолучитьПустоеЗначение();
	КонецЕсли;

        //Возвращаем исходное состояние Excel после работы
	Попытка
		Excel = СоздатьОбъект("Excel.Application"); 
	Исключение
		Сообщить("ОШИБКА: "+ОписаниеОшибки());
		Возврат;
	КонецПопытки;
	Excel.DisplayAlerts  = 1; 
	Excel.ScreenUpdating = 1;
        Excel.EnableEvents   = 1;
        Excel.Visible        = 1;
	Excel.Quit();
	Excel = ПолучитьПустоеЗначение();
КонецПроцедуры // СохранитьРезультирующийXLS()

Таким образом, у нас всего 2 процедуры и 1 функция для решения задачи. Основная процедура, которая принимает имя будущего XLS файла и список значений с таблицами MXL:  

//******************************************************************************
// СохранитьРезультирующийXLS() 
// Создаёт XLS-файл с несколькими листами
// Параметры
//      ИмяФайла - Полное имя выходного файла
//      ТабMXL   - Список значений, в котором:
//               Представление   - Имя листа
//               Значение        - Таблица MXL
//      ТекФайл  - Короткое имя результирующего файла
//
Процедура СохранитьРезультирующийXLS(ИмяФайла, ТабMXL, ТекФайл = "")

См. также

Подписаться Добавить вознаграждение

Комментарии

1. Никита Уколов (zzerro) 25.11.15 16:06
Почему нет информации что это для 7.7?
2. A X (ditp) 25.11.15 16:17
При сохранении больших MXL отчетов в файле XLS, экспорт происходит достаточно долго.

см. http://kb.mista.ru/article.php?id=219
3. Dmitry Vidmanov (etmarket) 25.11.15 16:21
(2) ditp, при сохранении файла XLS штатными средствами. Без внешних модулей
Решение с библиотекой moxel неприемлемо в моем заказе. Но каждый сам вправе решать. Конечно можно и ускорить экспорт за счет дополнительных компонент.
4. Dmitry Vidmanov (etmarket) 25.11.15 16:22
(1) zzerro, какая разница, 7.7 или 8.3!? Я за 5 минут его перепишу в код другой версии. Главное идея.
5. Алексей Кожушко (Rie) 25.11.15 19:57
А зачем в файлы-то писать? Не лучше ли напрямую в лист?
(Если честно - то у объектов экселевских есть много разных методов и свойств. И если их изучить - то будет счастье без извратов).
6. Dmitry Vidmanov (etmarket) 25.11.15 22:08
(5) Rie, уважаемый, изучите предметную область. НЕВОЗМОЖНО выгрузить в отдельные листы штатными процедурами. Или вы собираетесь настраивать форматы и стили для каждой строки?! Или придется внешний модуль подключить, что априори неприемлимо. А предложен простейший вариант решения проблемы. Временный файл - универсальное решение, которое используют большинство, так как надежно. Поэтому не делайте поспешных выводов! Спасибо.
7. Вадим Латышев (pro1c@inbox.ru) 07.12.15 13:42
(3) etmarket,

можно через ADO сделать!
без внешних компонент.
8. Dmitry Vidmanov (etmarket) 07.12.15 22:41
(7) pro1c@inbox.ru, интересно. Расскажите как!
9. Вадим Латышев (pro1c@inbox.ru) 08.12.15 13:15
Например, на коленке набросал, не проверял....

Connection = СоздатьОбъект("ADODB.Connection");
    СтрокаПодключения="Provider=Microsoft.Jet.OLEDB.4.0; Data Source = "+ПутьКФайлу;
    СтрокаПодключения=СтрокаПодключения+"; Extended Properties = "+"""Excel 8.0"+";HDR=NO;IMEX=1"";";
    
    Попытка
        
        Connection.Open(СтрокаПодключения);
        
    Исключение
        
        Сообщить(ОписаниеОшибки());
        Прервать;
        
    КонецПопытки;
    
    Command = СоздатьОбъект("ADODB.Command");
    
    RecordSet =СоздатьОбъект("ADODB.RecordSet");
    Command.ActiveConnection = Connection;
      		
    Command.CommandText ="SEL ECT * FR OM [A1:BA16]"; //тут пишите что хотите (какую надо команду), для примера указал запрос, причем можно указать и ЛИСТ книги
		    
    Command.CommandType =1;

    Command.Execute();
...Показать Скрыть


как то так, писал с планшета, не ругайте сильно!


причем при таком подходе, даже Excel на компе не требуется!
etmarket; +1 Ответить
10. Dmitry Vidmanov (etmarket) 08.12.15 14:39
Все равно OLE, и в системе должен быть установлен "Microsoft.Jet.OLEDB.4.0". Чем эта схема лучше, чем делать через Excel, как в статье? Быстрее?
11. Вадим Латышев (pro1c@inbox.ru) 08.12.15 21:23
не нужен Office...
Ваше же решение решает проблему объединения!
и что?

зачем это нужно? просто вопрос? для "хомячков"?
12. Вадим Латышев (pro1c@inbox.ru) 08.12.15 21:25
13. Dmitry Vidmanov (etmarket) 08.12.15 22:31
(11) pro1c@inbox.ru, надо сравнивать что лучше. Хорошая идея на вечер :-)
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа