Поддерживаемые типы: *.xls,*.xlsx,*.ods,*.sxc.
В данной статье приведен функционал, с помощью которого в обработке
«Импорт из 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Каталог + «XLWorkBook.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.
С уважением к сообществу МА!
Related Posts
Получение логина и пароля техподдержки 1С из базы
Класс для вывода отчета в Excel
Счет-фактура для УПП
Библиотека классов для создания внешней компоненты 1С на C#- Акт об оказании услуг (со скидками) — внешняя печатная форма для Управление торговлей 11.1.10.86
Прайс-лист с артикулом в отдельной колонке
Плюс однозначно, но зашифрованные листы не читает
07.11.2013.
Добавлено: Получение списка листов файла типа XLSX средствами 1С (ЧтениеXML).
Согласно описанию метода 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.»
Источник:
Поэтому в приведенной конструкции «objFSO.MoveFile(ФайлEXCEL, ФайлEXCEL);» ошибка будет возникать всегда.
Блин, но не возникает же! В чем подвох?
(2)При загрузке через этот метод, почему то не видна первая колонка. Через загрузку просто через ексель апликейшен все читается. Но… Если открыть этот документ, тупо щелкнуть по первой колонке два раза не поменяв никакие данные и сохранить документ, то все читается хорошо. Че за прикол и как это побороть? 🙂
Спасибо, воспользовался вторым способом!
Вкупе с этим способом работы