В данной статье приведен функционал, с помощью которого в обработке
"Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С": //infostart.ru/public/120961
производится считывание данных из файлов табличного типа *xls, *.xlsx, *.ods, *.sxc.
Методы загрузки из внешнего источника:
- Метод "MS ADO" (Чтение файлов xls, xlsx средствами Microsoft ADO): //infostart.ru/public/163640/
- Метод "MS EXCEL" (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): //infostart.ru/public/163641/
- Метод "LO CALC" (Чтение файлов xls, xlsx, ods, sxc с картинками средствами LibreOffice): //infostart.ru/public/163642/
- Метод "NativeXLSX" (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): //infostart.ru/public/300092/
- Метод "NativeXLSX". Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML)://infostart.ru/public/225624/
- Метод "Excel1C" (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): //infostart.ru/public/341855/
- Список листов файла: //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.
С уважением к сообществу МА!
