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

11.11.17

Разработка - Математика и алгоритмы

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

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

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

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

Ниже приведен код для платформы 1С 7.7, сохраняющий несколько 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, ТекФайл = "")

Excel MXL XLS экспорт объединение выгрузка листы XLS обмен данными

См. также

Загрузка банковской выписки для 1С 7.7 любой конфигурации

Банковские операции Загрузка и выгрузка в Excel Платформа 1С v7.7 Конфигурации 1cv7 Россия Бухгалтерский учет Платные (руб)

Решение предоставляет комплексную загрузку банковской выписки из CSV, XLS, TXT файла в 1С 7.7 любой конфигурации. Позволяет создавать документы выбранного вида, например "Строка выписки банка (приход/расход)", или многострочный табличный документ, например Выписка, для каждой операции из загружаемого файла банковской выписки. Загружает реквизиты документа, а также создаёт контрагентов и другие необходимые элементы справочников.

3588 руб.

29.07.2021    20594    13    22    

14

Загрузка документов в 1С (7.7) из табличных файлов Excel,OpenOffice,1C,DBF,TXT (обработка)

Файловый обмен (TXT, XML, DBF), FTP Загрузка и выгрузка в Excel Платформа 1С v7.7 Конфигурации 1cv7 Платные (руб)

Обработка решает поставленную задачу по вводу документов, а кроме того обладает важной функцией: настраивается на ассортимент конкретного поставщика, запоминая соответствие между его номенклатурой и «нашей». Т.е. фактически – является самообучающейся системой ввода накладных. У разных поставщиков могут быть накладные различного типа, с разным количеством полей, поэтому для каждого из них может быть сохранена своя собственная настройка диалоговой формы так, что любая поступающая накладная может быть обработана. По отношению к программе "1С:Предприятие 7.7" данное решение является внешними файлами. Для использования данного продукта не требуется вносить изменения в алгоритм существующих программ или используемых конфигураций.

2400 руб.

10.12.2009    76452    58    93    

68

Выгрузка товаров из 1С:Предприятия 7.7 "Торговля и Склад 9.2" в файл CSV для онлайн кассы МТС

Кассовые операции Оптовая торговля Розничная торговля Загрузка и выгрузка в Excel Оперативный учет 7.7 1С:Торговля и склад 7.7 Бухгалтерский учет Управленческий учет Абонемент ($m)

Данная обработка предназначена для выгрузки файла из программы 1С: Предприятие 7.7 Конфигурации «Торговля и Склад 9.2» в файл CSV для загрузки в кассу МТС. Тестирование проводилось на релизах 937 и 998. В теории должна работать на всех типовых релизах конфигурации «Торговля и Склад 9.2».

1 стартмани

07.02.2021    9929    5    Kuzya_brаtsk    1    

7

Загрузка адресов произвольного формата (КЛАДР и нестандартных "грязных" адресов) в ФИАС современных конфигураций из 1С 7.7

Загрузка и выгрузка в Excel Обмен между базами 1C Платформа 1С v7.7 Платформа 1С v8.3 Конфигурации 1cv8 Конфигурации 1cv7 1С:ERP Управление предприятием 2 Абонемент ($m)

Описание технологии загрузки любых адресов из 1С 7.7 с разложением по ФИАС в современные конфигурации 1C 8.3 на примере ERP. Предлагаемый способ просто чудо, он гарантирует результат, он очень простой и качественный! Моя обработка является синтаксическим анализатором, который подставляет в строку грязного адреса выражение "Дом №" и "Корпус", благодаря чему грязные адреса 7.7 сами очень хорошо раскладываются по значимым полям ФИАС - заполняется область, город, улица, дом, корпус.. все раскладывается само с помощью встроенного механизма современных конфигураций 1С 8.3, который написали сами сотрудники фирмы 1С!

1 стартмани

16.08.2019    19260    17    ksnik    12    

8

Пример переноса справочников, документов и движений через Эксель и "Конвертацию данных 2" из оптовой учетной системы 1С 7.7 Комплексной 4.2 и розничной учетной системы Рарус Торговый комплекс -> в 1С 8.3 ЕРП 2.4 (расширение и дополнительные реквизиты)

Загрузка и выгрузка в Excel Оптовая торговля Розничная торговля Обмен между базами 1C Платформа 1С v7.7 Платформа 1С v8.3 1С:Управление торговлей 10 1С:Комплексная 7.7 1С:ERP Управление предприятием 2 Управленческий учет Абонемент ($m)

Описание практического опыта переноса данных из двух устаревших учетных систем в одну современную с обобщением справочников (например номенклатуры, контрагентов, партнеров) и объединением учетных данных устаревших систем в единую новую УС применяя комбинацию стандартной загрузки из Эксель и КД2. Данная публикация является примером переноса данных из сильно устаревших и сильно переписанных конфигураций 1С:Предприятия 7.7 и 8 в современную 1С 8.3 своими силами при условии невозможности или нежелания пользоваться обновлением релизов и типовой функциональностью универсального обмена данными типовых мастеров переноса данных. Отличительной особенностью публикации является устранение зависимости от версий релиза конфигураций, отказ от обновлений устаревших конфигураций.

1 стартмани

29.07.2019    20947    16    ksnik    0    

6

Сохранение любой печатной формы в формате xlsx, xls, pdf, jpg, png, bmp

Файловый обмен (TXT, XML, DBF), FTP Загрузка и выгрузка в Excel Платформа 1С v7.7 Конфигурации 1cv7 Беларусь Абонемент ($m)

Внешняя обработка, позволяющая без труда сохранять в форматах xlsx, xls, pdf, jpg, png, bmp любую печатную форму.

1 стартмани

02.03.2018    18790    53    BotMan4    10    

6

ЭВОТОР: выгрузка и загрузка в онлайн кассу в соответствии с требованием 54-ФЗ (54ФЗ). 1С: 7.7 ТиС

Загрузка и выгрузка в Excel ККМ Платформа 1С v7.7 1С:Торговля и склад 7.7 Управленческий учет Платные (руб)

Выгрузка из 1C версии 7.7 ТиС (Торговля и Склад) в EXCEL для дальнейшей загрузки в кассы ЭВОТОР, с помощью обработки обмен через EXCEL ( которая выбирается на сайте ЭВОТОР в магазине приложений). Полная версия без ограничений. Демоверсия позволяет выгружать только 5 строк номенклатуры(группы товаров). Демоверсия обладает такими же характеристиками что и полная за исключением того, что выгружает 5 строк неважно чего группы или номенклатуры (текст обработки зашифрован). Если у вас одна позиция номенклатуры или 5 позиций без групп, тогда Демоверсия для Вас. Если вдруг выскакивает ошибка по запуску обработки попробуйте запустить 1с с правами администратора

3000 руб.

19.06.2017    49813    9    26    

14
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. zzerro 277 25.11.15 16:06 Сейчас в теме
Почему нет информации что это для 7.7?
4. etmarket 890 25.11.15 16:22 Сейчас в теме
(1) zzerro, какая разница, 7.7 или 8.3!? Я за 5 минут его перепишу в код другой версии. Главное идея.
2. ditp 91 25.11.15 16:17 Сейчас в теме
При сохранении больших MXL отчетов в файле XLS, экспорт происходит достаточно долго.

см. http://kb.mista.ru/article.php?id=219
3. etmarket 890 25.11.15 16:21 Сейчас в теме
(2) ditp, при сохранении файла XLS штатными средствами. Без внешних модулей
Решение с библиотекой moxel неприемлемо в моем заказе. Но каждый сам вправе решать. Конечно можно и ускорить экспорт за счет дополнительных компонент.
7. pro1c@inbox.ru 185 07.12.15 13:42 Сейчас в теме
(3)

можно через ADO сделать!
без внешних компонент.
8. etmarket 890 07.12.15 22:41 Сейчас в теме
(7) pro1c@inbox.ru, интересно. Расскажите как!
5. Rie 25.11.15 19:57 Сейчас в теме
А зачем в файлы-то писать? Не лучше ли напрямую в лист?
(Если честно - то у объектов экселевских есть много разных методов и свойств. И если их изучить - то будет счастье без извратов).
6. etmarket 890 25.11.15 22:08 Сейчас в теме
(5) Rie, уважаемый, изучите предметную область. НЕВОЗМОЖНО выгрузить в отдельные листы штатными процедурами. Или вы собираетесь настраивать форматы и стили для каждой строки?! Или придется внешний модуль подключить, что априори неприемлимо. А предложен простейший вариант решения проблемы. Временный файл - универсальное решение, которое используют большинство, так как надежно. Поэтому не делайте поспешных выводов! Спасибо.
9. pro1c@inbox.ru 185 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. etmarket 890 08.12.15 14:39 Сейчас в теме
Все равно OLE, и в системе должен быть установлен "Microsoft.Jet.OLEDB.4.0". Чем эта схема лучше, чем делать через Excel, как в статье? Быстрее?
11. pro1c@inbox.ru 185 08.12.15 21:23 Сейчас в теме
не нужен Office...
Ваше же решение решает проблему объединения!
и что?

зачем это нужно? просто вопрос? для "хомячков"?
13. etmarket 890 08.12.15 22:31 Сейчас в теме
(11) pro1c@inbox.ru, надо сравнивать что лучше. Хорошая идея на вечер :-)
12. pro1c@inbox.ru 185 08.12.15 21:25 Сейчас в теме
OLEDB.4.0 всегда уже есть...
Оставьте свое сообщение