IE 2016

Загрузка из EXCEL в 1С. Список листов EXCEL

Опубликовал StepByStep в раздел Обработки - Универсальные обработки

ПРАКТИЧЕСКОЕ ПОСОБИЕ РАЗРАБОТЧИКА: Методы получения списка листов файлов типа EXCEL.
Поддерживаемые типы: *.xls,*.xlsx,*.ods,*.sxc.

В данной статье приведен функционал, с помощью которого в обработке
"Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С": http://infostart.ru/public/120961
производится считывание данных из файлов табличного типа *xls, *.xlsx, *.ods, *.sxc.

Методы загрузки из внешнего источника:
- Метод "MS ADO" (Чтение файлов xls, xlsx средствами Microsoft ADO): http://infostart.ru/public/163640/
- Метод "MS EXCEL" (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): http://infostart.ru/public/163641/
- Метод "LO CALC" (Чтение файлов xls, xlsx, ods, sxc с картинками средствами LibreOffice): http://infostart.ru/public/163642/
- Метод "NativeXLSX" (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): http://infostart.ru/public/300092/
- Метод "NativeXLSX". Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML):
http://infostart.ru/public/225624/
- Метод "Excel1C" (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): http://infostart.ru/public/341855/
- Список листов файла: http://infostart.ru/public/163724/


Публикаций на тему загрузки из EXCEL - множество, но
"
— Вам билетёр нужен?
— Был нужен, да уже взяли.
— Может и я на что сгожусь?
— Может и сгодишься, если скалиться не будешь ...
"
"НЕУЛОВИМЫЕ МСТИТЕЛИ" (1966).


// Функция возвращает список значений, содержащий имена листов книги EXCEL или Пустой СписокЗначений.
//
// Параметры:
//     ФайлEXCEL - Полное имя файла (путь к файлу с именем файла и расширением).

//    
ПодключениеADODB - Тип драйвера ADODB для подключения к EXCEL.
//
// Возвращаемые значения:

//     СписокЛистов - Список имен листов в файле, например: Лист1, Лист2, Лист3.
//
Функция ФайлExcelПолучитьСписокЛистов(Знач ФайлEXCEL, Знач ПодключениеADODB = "MicrosoftJetOLEDB40")
    Перем СписокЛистов, ВсегоЛистов, ИмяЛиста, МассивЛистов;
    Перем ServiceManager, Desktop, Properties1, Properties2, Arguments, Book, Sheets;
    Перем СonnectionString, ADODBConnection, ADOXCatalog;
 
    СписокЛистов = Новый СписокЗначений;
    
    Файл = Новый Файл(ФайлEXCEL);
    
    Если ВРег(Файл.Расширение) = ".ODS" ИЛИ ВРег(Файл.Расширение) = ".SXC" Тогда
        
        // Для файлов типа .ODS,*.SXC используем ServiceManager LibreOffice.
        
        Попытка
            // Инициализация основного СОМОбъекта типа com.sun.star.ServiceManager (LibreOffice/OpenOffice).            
            ServiceManager = Новый COMОбъект("com.sun.star.ServiceManager");
            
            // Инициализация дочернего объекта Desktop.
            Desktop = ServiceManager.CreateInstance("com.sun.star.frame.Desktop");
            
            // Объявление свойств и аргументов для создания объекта типа Книга EXCEL.
            Properties1 = ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue");
            Properties1.Name = "AsFile";            // Свойство "КАК ФАЙЛ", альтернатива "AsTemplate" - "КАК ШАБЛОН".
            Properties1.Value = Истина;
            
            Properties2 = ServiceManager.Bridge_GetStruct("com.sun.star.beans.PropertyValue");
            Properties2.Name = "Hidden";            // Запускать скрытно. Реализовано в LibreOffice 3.6.
            Properties2.Value = Истина;
            
            Arguments = Новый COMSafeArray("VT_VARIANT", 2);
            Arguments.SetValue(0, Properties1);
            Arguments.SetValue(1, Properties2);
            
            // Дочерний объект Desktop-а: Книга EXCEL.
            Book = Desktop.LoadComponentFromURL(ConvertToURL(ФайлEXCEL), "_blank", 0, Arguments);
            
            // Дочерний объект Book-а: Листы EXCEL.
            Sheets= Book.getSheets();
            ВсегоЛистов = Sheets.getCount();
            
            // Формирование списка листов файла.
            Для ит = 0 ПО ВсегоЛистов -1 Цикл
                ИмяЛиста = Sheets.getByIndex(ит).Name;
                СписокЛистов.Добавить(ИмяЛиста);    // Добавляем в список имя листа без знака.
            КонецЦикла;
            
            // Завершение работы.
            // Закрытие Объектов.
            Book.Close(Истина);
            Desktop.Terminate();
            Desktop = Неопределено;
            ServiceManager = Неопределено;
        Исключение
            Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
            Возврат Новый СписокЗначений;    // В случае ошибки возвращаем пустой список значений.
        КонецПопытки;
        
    Иначе
        // Для файлов типа .XLS .XLSX используем MS ADODB.
        
        // Строка соединения - определение драйвера, который будет использован для подключения к файлу EXCEL.
        Если ПодключениеADODB = "MicrosoftACEOLEDB12" Тогда
            
            // ACE.OLEDB.12.0 - Для использования данного подключения необходимо дополнительное ПО:
            // Microsoft Access Database Engine 2010 Redistributable 32/64 bit.
            СonnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";
            
            // Еще один вариант.
            //СтрокаСоединения = "Driver={Microsoft Excel Driver (*.xls, *.xlsx, *.xlsm, *.xlsb)};Dbq=" + СокрЛП(ФайлEXCEL) + ";";
            
        Иначе
            
            // Jet.OLEDB.4.0 - Стандартное подключение, как правило, не требующее установки дополнительного ПО.
            // Рекомендуется установить последний Service Pack Windows.
            СonnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "  + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";
            
            // Еще один вариант.
            //СтрокаСоединения = "Driver={Microsoft Excel Driver (*.xls)};Dbq=" + СокрЛП(ФайлEXCEL) + ";";
            
        КонецЕсли;
        
        Попытка
            ADODBConnection = Новый COMОбъект("ADODB.Connection");       // Инициализация ADODB.
            ADODBConnection.Open(СonnectionString);                                      // Открытие соединения.
            
            // 1-ый способ Формирования списка листов файла EXCEL.
            ADOXCatalog = Новый COMОбъект ("ADOX.Catalog");
            ADOXCatalog.ActiveConnection = ADODBConnection;
            МассивЛистов = ADOXCatalog.Tables;
            
            Для Каждого ЛистМассива ИЗ МассивЛистов Цикл
                ИмяЛиста = ЛистМассива.Name;
                Если ИмяЛиста = "Excel_BuiltIn_Database" Тогда                    // Исключаем лист "по-умолчанию".
                    Продолжить;
                КонецЕсли;
                // Добавляем в список имя листа без знака $. Исключительно для удобвства восписятия при выборе листа из списка.
                СписокЛистов.Вставить(0, Лев(ИмяЛиста, СтрДлина(ИмяЛиста)-1));
            КонецЦикла;
            //// 2-ой способ Формирования списка листов файла EXCEL.
            //ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
            //ADODBRecordset = ADODBConnection.OpenSchema(20);
            //Пока НЕ ADODBRecordset.EOF Цикл
            //    ИмяЛиста = ADODBRecordset.Fields("TABLE_NAME").Value;
            //    Если ИмяЛиста = "Excel_BuiltIn_Database" Тогда                    // Исключаем лист "по-умолчанию".
            //        ADODBRecordset.MoveNext();
            //        Продолжить;
            //    КонецЕсли;
            //// Добавляем в список имя листа без знака $. Исключительно для удобвства восписятия при выборе листа из списка.
            //    СписокЛистов.Добавить(Лев(ИмяЛиста, СтрДлина(ИмяЛиста)-1));

            //    ADODBRecordset.MoveNext();
            //КонецЦикла;
            //ADODBRecordset.Close();
           
            // Завершение работы.
            // Закрытие Объектов.
            ADOXCatalog = Неопределено;
            ADODBConnection.Close();
            ADODBConnection = Неопределено;
        Исключение
            Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
            Возврат Новый СписокЗначений;    // В случае ошибки возвращаем пустой список значений.
        КонецПопытки;
        
    КонецЕсли;
    
    Возврат СписокЛистов;
    
КонецФункции


// Конвертировать имя файла для метода "LibreOffice CALC".

//
Функция ConvertToURL(ФайлEXCEL)
    
    ФайлEXCEL = СтрЗаменить(ФайлEXCEL," ","%20");
    ФайлEXCEL = СтрЗаменить(ФайлEXCEL,"\","/");
    
    Возврат "file:/" + "/localhost/" + ФайлEXCEL;
    
КонецФункции


// Получение списка листов файла типа XLSX средствами 1С (ЧтениеXML).
//
Функция ФайлExcelПолучитьСписокЛистов_1CXML_XLSX(ФайлEXCEL)
    Перем
ZIPКаталог, WorkBook;
    Перем
СписокЛистов;
   
ZIPКаталог = КаталогВременныхФайлов() + "XLSX\";
   
СписокЛистов = Новый СписокЗначений;
   
Файл = ПолучитьОбъектФайл(ФайлEXCEL, Истина);
    Если
Файл = Неопределено Тогда
       
Сообщить("Невозможно получить список листов, т.к. невозможно открыть для чтения файл:
        |"
+ ФайлEXCEL);
        Возврат Новый
СписокЗначений;
    КонецЕсли;
    Если НЕ
ВРег(Файл.Расширение) = ".XLSX" Тогда
        Возврат Новый
СписокЗначений;
    КонецЕсли;
   
WorkBook = Новый ЧтениеXML;
   
WorkBook.ОткрытьФайл(ZIPКаталог + "XL\WorkBook.xml");
   
// Считать очередной узел XML.
   
Пока WorkBook.Прочитать() Цикл
        Если НЕ
ВРег(WorkBook.Имя) = ВРег("sheet") Тогда
            Продолжить;
        КонецЕсли;
       
// Тип текущего узла XML.
       
Если WorkBook.ТипУзла = ТипУзлаXML.НачалоЭлемента Тогда
           
// Считать очередной атрибут элемента XML.
           
Пока WorkBook.ПрочитатьАтрибут() Цикл
                Если НЕ
ВРег(WorkBook.Имя) = "NAME" Тогда
                    Продолжить;
                КонецЕсли;
               
СписокЛистов.Добавить(WorkBook.Значение);
            КонецЦикла;
        КонецЕсли;
    КонецЦикла;
   
// Завершение работы.
    // Закрытие Объектов.
   
WorkBook.Закрыть();
    Возврат
СписокЛистов;
КонецФункции

Функция
ПолучитьОбъектФайл(Знач ФайлEXCEL, РаспаковатьXLSX = Ложь)
    Перем
Файл, objFSO, objFile, XLSXРаспакован;
    Если НЕ
ЗначениеЗаполнено(ФайлEXCEL) Тогда
        Возврат Неопределено;
    КонецЕсли;
   
Файл = Новый Файл(ФайлEXCEL);
    Если НЕ
Файл.Существует() Тогда
       
Сообщить("Файл не существует:
        |"
+ ФайлEXCEL);
        Возврат Неопределено;
    КонецЕсли;
   
// Проверка: Занят ли файл другим процессом?
   
objFSO = Новый COMОбъект("Scripting.FileSystemObject");
    Попытка
       
objFSO.MoveFile(ФайлEXCEL, ФайлEXCEL);
       
objFile = objFSO.GetFile(ФайлEXCEL);
    Исключение
       
objFile = Неопределено;
       
objFSO = Неопределено;
       
Сообщить("Ошибка открытия файла/Файл занят другой программой:
        |"
+ ФайлEXCEL);
        Возврат Неопределено;
    КонецПопытки;
   
objFSO = Неопределено;
    Если
ВРег(Файл.Расширение) = ".XLSX" И РаспаковатьXLSX Тогда
       
// Распаковка файла XLSX во временный каталог.
       
XLSXРаспакован = РаспаковатьXLSXвКаталогВременныхФайлов(ФайлEXCEL);
        Если НЕ
XLSXРаспакован Тогда
            Возврат Неопределено;
        КонецЕсли;
    КонецЕсли;
    Возврат
Файл;
КонецФункции

Функция
РаспаковатьXLSXвКаталогВременныхФайлов(ФайлEXCEL)
    Перем
ZIPФайл, ZIPКаталог;
   
ZIPКаталог = КаталогВременныхФайлов() + "XLSX\";
    Попытка
       
УдалитьФайлы(ZIPКаталог);
       
ZIPФайл = Новый ЧтениеZipФайла;
       
ZIPФайл.Открыть(ФайлEXCEL);
       
ZIPФайл.ИзвлечьВсе(ZIPКаталог, РежимВосстановленияПутейФайловZIP.Восстанавливать);
        Возврат Истина;
    Исключение
        Возврат Ложь;
    КонецПопытки;
    Возврат Истина;
КонецФункции


Особенности и Ограничения:

1. Для функционирования метода «Microsoft ADODB» необходимо:

Драйвер подключения Provider=Microsoft.Jet.OLEDB.4.0:
-
Установленный Microsoft MDAC, как правило специальная установка не требуется, достаточно последнего Service Pack-а операционной системы..

  Microsoft MDAC 2.8 SP1 10.05.2005: http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=5793
Драйвер подключения Provider=Microsoft.ACE.OLEDB.12.0:
- Установленный Microsoft Access Database Engine 2010 Redistributable (16/12/2010) 32 и 64 - разрядные версии:
  Microsoft ADE 2010 16/12/2010:  http://www.microsoft.com/en-us/download/details.aspx?id=13255

2. Для функционирования метода «LibreOffice CALC» необходим установленный LibreOffice.

МА! С уважением к сообществу МА!


См. также

PowerTools от 1 000

Комментарии

1. echo77 01.10.2013 14:09
Плюс однозначно, но зашифрованные листы не читает
# Ответить
2. StepByStep 08.11.2013 00:03
07.11.2013.
Добавлено: Получение списка листов файла типа XLSX средствами 1С (ЧтениеXML).
# Ответить
3. Sardukar 09.10.2014 03:33
Согласно описанию метода MoveFile, при попытке переместить файл в уже существующий, возникает ошибка:
"...
object.MoveFile ( source, destination );
...
________________________________________
If source contains wildcards or destination ends with a path separator (\), it is assumed that destination specifies an existing folder in which to move the matching files. Otherwise, destination is assumed to be the name of a destination file to create. In either case, three things can happen when an individual file is moved:
• If destination does not exist, the file gets moved. This is the usual case.
• If destination is an existing file, an error occurs.
• If destination is a directory, an error occurs."

Источник:
http://msdn.microsoft.com/en-us/library/2wcf3ba6(v=vs.84).aspx


Поэтому в приведенной конструкции "objFSO.MoveFile(ФайлEXCEL, ФайлEXCEL);" ошибка будет возникать всегда.
Блин, но не возникает же! В чем подвох?
# Ответить
Внимание! За постинг в данном форуме $m не начисляются.
Внимание! Для написания сообщения необходимо авторизоваться
Текст сообщения*
Прикрепить файл






IE 2016