Вход | Регистрация
 
1С:Предприятие :: 1С:Предприятие 8 общая

чтение ексель с помощью ADODB.Connection (продолжение)

чтение ексель с помощью ADODB.Connection (продолжение)
Я
   AntonU
 
19.04.19 - 14:48
В теме чтение ексель с помощью ADODB.Connection написано, что файл экселя можно быстро прочитать кодом
MXL=Новый ТабличныйДокумент;
MXL.Прочитать(АдресФайла);    
Построитель=Новый ПостроительОтчета;
Построитель.ИсточникДанных = Новый ОписаниеИсточникаДанных(MXL.Область(1, 1, MXL.ВысотаТаблицы, MXL.ШиринаТаблицы));
Построитель.Выполнить();
ТЗ = Построитель.Результат.Выгрузить();

Но у меня последняя команда выдает ошибку, Построитель.Результат - Пуст
MXL.ВысотаТаблицы и MXL.ШиринаТаблицы определяются верно.

Как выгрузить данные из эксель в ТЗ ?
 
 
   AntonU
 
1 - 19.04.19 - 14:58
Несколько лет назад работавшая функция Инфостарта не работает корректно - читает только 6424 строки экселя, а всего там больше 70000 строк
Функция ЗагрузитьМетодом_MSADODB(Знач ФайлEXCEL, Знач ИмяЛиста, Знач СтрокаЗаголовка = 1, НачСтрока = 0, КонСтрока = 0, КолвоСтрокExcel=0, 
    Знач ПодключениеADODB = "MicrosoftJetOLEDB40") Экспорт
    Перем СonnectionString, ADODBConnection, ADODBRecordset, ТекстЗапроса;
    Перем КолвоКолонокExcel, Поле, Колонка, ИмяКолонки;
    Перем НоваяСтрока, НомерСтроки;
    Перем ТаблицаРезультат;
    
    НачСтрока = 0;
    // Нумерация MS ADODB начинается с 1.

    
    // Переменная "СтрокаЗаголовка", не используется, т.к. HDR=YES, а не HDR=NO.

    // HDR=YES:

    // 1. Считывание заголовков колонок с 1-ой строки.

    // 2. Считываемые данные со 2-ой и последующих строк типизированы. Для варианта HDR=NO: считываемые данные - строка.

    
    // Строка соединения - определение драйвера, который будет использован для подключения к файлу 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) + ";";

        //СonnectionString = "Provider=Microsoft.Jet.OLEDB.12.0;Data Source= "  + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;""";

        
    КонецЕсли;
    
    Попытка
        // Инициализация основного объекта ADODB.Connection. Открытие соединения.

        ADODBConnection = Новый COMОбъект("ADODB.Connection");
        ADODBConnection.ConnectionString =  СonnectionString;
        ADODBConnection.Open();
        // Импирически определенный параметр для правильного определения количества строк листа.

        ADODBConnection.CursorLocation = 3;    // По-умолчанию 2.

    Исключение
        Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.

    КонецПопытки;
    ХCat=Новый COMОбъект ("ADOX.Catalog");
    ХCat.ActiveConnection = СonnectionString;
    этаХня=ХCat.Tables.Item(0).Name;
    ТекстЗапроса = "SELECT * FROM [" + этаХня + "]";
    
    // Создание Recordset. Дочерний объект ADODBConnection. Набор записей по запросу.

    Попытка
        ADODBRecordset = Новый COMОбъект("ADODB.Recordset");
        ADODBRecordset.Open(ТекстЗапроса, ADODBConnection);
        
        // Проверка заполненности листа.

        Если (ADODBRecordset.EOF ИЛИ ADODBRecordset.BOF) Тогда
            КолвоСтрокExcel = 0;
            Сообщить(НСтр("ru = '" + ИмяЛиста + ": не содержит данных.'"), СтатусСообщения.Внимание);
            
            // Завершение работы.

            // Закрытие Объектов.

            ADODBRecordset.Close();
            ADODBConnection.Close();
            ADODBRecordset   = Неопределено;
            ADODBConnection = Неопределено;
            
            Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.

        КонецЕсли;
        
        // Импирически определенные параметры для правильного определения количества строк листа.

        ADODBRecordset.AbsolutePage     = 1;
        ADODBRecordset.AbsolutePosition = 1;
    Исключение
        Сообщить(НСтр("ru = '"+ОписаниеОшибки()+"'"), СтатусСообщения.Внимание);
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.

    КонецПопытки;
    
    // Параметр, возвращаемый в вызывающую процедуру.

    КолвоСтрокExcel = ADODBRecordset.RecordCount + 1;    // (+1) - учет Строки-Заголовока, которая "съедается".

    КолвоКолонокExcel = ADODBRecordset.Fields.Count;
    
    // Проверка заполненности листа.

    Если КолвоСтрокExcel < 2 Тогда
        КолвоСтрокExcel = 0;
        Сообщить(НСтр("ru = '" + ИмяЛиста + ": не содержит данных.'"), СтатусСообщения.Внимание);
        
        // Завершение работы.

        // Закрытие Объектов.

        ADODBRecordset.Close();
        ADODBConnection.Close();
        ADODBRecordset   = Неопределено;
        ADODBConnection = Неопределено;
        
        Возврат Новый ТаблицаЗначений;    // В случае ошибки возвращаем пустую таблицу значений.

    КонецЕсли;
    
    // Создание результирующей таблицы, в которую будут записываться считанные из EXCEL данные.

    ТаблицаРезультат = Новый ТаблицаЗначений;
    
    // Формирование колонок результирующей таблицы.

    
    // "НомерСтроки" - для наглядности и удобства.

    // В зависимости от разрабатываемой обработки.

    // "Сопоставлено" - может быть другим.

    // Здесь же могут быть добавлены другие колонки, не формируемые из содержимого файла EXCEL.

    ТаблицаРезультат.Колонки.Добавить("НомерСтроки", Новый ОписаниеТипов("Число"), "№", 4);
    ТаблицаРезультат.Колонки.Добавить("Сопоставлено", Новый ОписаниеТипов("Булево"), "Сопоставлено", 1);
    
    Для ит = 1 ПО КолвоКолонокExcel Цикл
        
        Поле = ADODBRecordset.Fields.Item(ит - 1);
        ИмяКолонки = "К_" + ит;
        Колонка = ТаблицаРезультат.Колонки.Добавить(ИмяКолонки,Новый ОписаниеТипов("Число") , СокрЛП(СтрЗаменить(Поле.Name, "#", ".")));

        // Замена "#" на ".", т.к. при считывании ADODB "." в имени колонки заменяется на "#".

        
    КонецЦикла;
    
    // ТаблицаРезультат: 1-я строка - Строка-Заголовок.

    
    // Добавление этой строки обусловлено исключительно из соображений идентичности содержимого файла EXCEL и ТаблицыЗначений,

    // выводимой на форме Обработки, и дальнейшей обработки строки заголовка

    // с целью сопоставления колонок EXCEL и реквизитов 1С: для Справочников, ПВХ, Регистров, Документов.

    
    // Если в Вашей обработке в результирующей таблице в качестве 1-ой строки не нужна Строка-Заголовок, то

    // следует закомментировать следующий цикл:

    //НоваяСтрока = ТаблицаРезультат.Добавить();

    //НоваяСтрока.НомерСтроки = 1;

    //Для ит = 1 ПО КолвоКолонокExcel Цикл

    //    

    //    ИмяКолонки = "К_" + ит;

    //    Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);

    //    НоваяСтрока[ИмяКолонки] = Колонка.Заголовок;

    //    

    //КонецЦикла;

    
    // ТаблицаРезультат: Формирование строк по указанному диапазону: НачСтрока - КонСтрока.

    
    НомерСтроки = 0;
    Пока ADODBRecordset.EOF() = 0 Цикл
        
        НомерСтроки = НомерСтроки + 1;
        
        Если НомерСтроки < НачСтрока Тогда    // Номер строки вне диапазона считываемых строк.

            ADODBRecordset.MoveNext();            // Следующая строка.

            Продолжить;
        КонецЕсли;
        
        Если КонСтрока > 0 И НомерСтроки > КонСтрока Тогда    // Номер строки вне диапазона считываемых строк.

            Прервать;
        КонецЕсли;
        
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.НомерСтроки = НомерСтроки;
        
        Для ит = 1 ПО КолвоКолонокExcel Цикл
            
            Поле = ADODBRecordset.Fields.Item(ит - 1);
            
            Если Поле.ActualSize = 0 Тогда        // Пустое поле EXCEL.

                Продолжить;
            КонецЕсли;
            
            ЗначениеЯчейки = Поле.Value;        // Учитывая параметр HDR=YES в строке соединения, данные считываются в соответствии с их типом.

            
            ИмяКолонки = "К_" + ит;
            НоваяСтрока[ИмяКолонки] = ЗначениеЯчейки;
            
            // Используется при формировании таблицы на форме обработки.

            ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
            ДлинаСтроки      = СтрДлина(СокрЛП(ЗначениеЯчейки));
            ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);
            
        КонецЦикла;
        
        ADODBRecordset.MoveNext();  // Следующая строка.

        
    КонецЦикла;
    
    //УдалитьКолонкиСНулевойШириной(ТаблицаРезультат);

    
    // Завершение работы.

    // Закрытие Объектов.

    ADODBRecordset.Close();
    ADODBConnection.Close();
    ADODBRecordset   = Неопределено;
    ADODBConnection = Неопределено;
    
    Возврат ТаблицаРезультат;
    
КонецФункции
   AntonU
 
2 - 19.04.19 - 16:19
Версия 1С Предприятия 8.3.12
   shuhard
 
3 - 19.04.19 - 16:20
(0)[Как выгрузить данные из эксель в ТЗ ?]
дык через ADODB
   AntonU
 
4 - 22.04.19 - 10:35
Я и так пытаюсь через ADODB
(3) если нажать в (1) +code, то появится код.
Может тут надо что-то поменять?
СonnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source= "  + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 8.0;HDR=YES;IMEX=1;""";
   mikecool
 
5 - 22.04.19 - 10:38
(4) у провайдеров есть ограничения на размер файла, емнип
MXL.Прочитать(АдресФайла); - это работает, правда файлы нужно не ниже 97 офиса
разбирайся со своим построителем
   Filippov
 
6 - 22.04.19 - 11:17
(0) Если интересно, беру из xls без ADODB:
Процедура Прочитать(Команда)
    ПрочитатьНаСервере();
    ПреобразоватьВТЗНаСервере();
КонецПроцедуры

&НаСервере
Процедура ПрочитатьНаСервере()
    ТабДок = Новый ТабличныйДокумент;
    ТабДок.Прочитать(Объект.ИмяФайлаНадбавок);
КонецПроцедуры

&НаСервере
Процедура ПреобразоватьВТЗНаСервере()
    Объект.ДанныеДляЗагрузки.Очистить();
    ТабЗначений = ПреобразоватьТабличныйДокументВТаблицуЗначений(ЭтаФорма.ТабДок);
    Для Каждого Сотр ИЗ ТабЗначений Цикл
        Если ЗначениеЗаполнено(Сотр.Ф_И_О_)И ЗначениеЗаполнено(Сотр.Надбавка) ТОгда
            СотрТЗ = Объект.ДанныеДляЗагрузки.Добавить();
            СотрТЗ.ФамилияИО = Сотр.Ф_И_О_;
            СотрТЗ.Сумма = Сотр.Надбавка;
            СотрТЗ.Сотрудник = НайтиПоФамилияИО(СотрТЗ.ФамилияИО);
        КонецЕсли;
    КонецЦикла;
КонецПроцедуры

&НаСервере
Функция ПреобразоватьТабличныйДокументВТаблицуЗначений(ТабДокумент)    
    ОбластьФайла = ТабДокумент.ПолучитьОбласть(Объект.Организация.Префикс);
    ПоследняяСтрока = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоВертикали();
    ПоследняяКолонка = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоГоризонтали();    
    ОбластьЯчеек = ОбластьФайла.Область(1, 1, ПоследняяСтрока, ПоследняяКолонка);
    
    // Создаем описание источника данных на основании области ячеек табличного документа. 

    ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьЯчеек);
        
    // Создаем объект для интеллектуального построения отчетов,

    // указываем источник данных и выполняем построение отчета.

    ПостроительОтчета = Новый ПостроительОтчета;    
    ПостроительОтчета.ИсточникДанных = ИсточникДанных;
    ПостроительОтчета.Выполнить();
    
    // Результат выгружаем в таблицу значений.

    ТабЗначений = ПостроительОтчета.Результат.Выгрузить();
    
    Возврат ТабЗначений
    
КонецФункции
   ДенисЧ
 
7 - 22.04.19 - 11:19
(6) А теперь представь, что у тебя 8,2.
   Filippov
 
8 - 22.04.19 - 11:22
(7) Было написано Версия 1С Предприятия 8.3.12
   Filippov
 
9 - 22.04.19 - 11:24
Причем работает с xls, xlsx, таблицей Libre Office
   AntonU
 
10 - 22.04.19 - 11:58
Обнаружил, что программа написана на 8.1 и работает в режиме совместимости с 8.3. Наверное, поэтому Построитель отчета не работает.
А вот для ADODB нужна подходящая командная строка.
https://www.connectionstrings.com/excel/
Microsoft.ACE.OLEDB.12.0 - с ним не получается
  СonnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source= " + СокрЛП(ФайлEXCEL) + ";Extended Properties=""Excel 12.0;HDR=YES;IMEX=1;"""; - так не работает
А Microsoft.Jet.OLEDB.4.0 только Excel 2003 и ниже читает, у меня Excel 2016
 
 Рекламное место пустует
   Cyberhawk
 
11 - 22.04.19 - 11:59
хлсх всегда нативно читаю
   Cyberhawk
 
12 - 22.04.19 - 11:59
Только с датами может всплывать нюанс.
А 1Сное чтение вообще от релиза к релизу с ошибками.
   AntonU
 
13 - 22.04.19 - 12:00
(11) - очень долго получается, если построчно
   Cyberhawk
 
14 - 22.04.19 - 12:01
(13) Плата за надежность (полный контроль) и универсальность (кроссплатформенность)
   Cyberhawk
 
15 - 22.04.19 - 12:02
Да и зачем читать быстро - в фоне главное выполнять с отображением прогресса
   ДенисЧ
 
16 - 22.04.19 - 12:03
(15) Когда тебе нужно прочитать 5000 файлов за определенное время - тут скорость ещё как играет
   AntonU
 
17 - 22.04.19 - 12:05
Около 72 тыс. строк пришло в файле, читалось почти 20 минут. Перебор
   Cyberhawk
 
18 - 22.04.19 - 12:06
30 тыщ строк не более минуты у меня читается
   Cyberhawk
 
19 - 22.04.19 - 12:06
(16) Ну это не пользовательский сценарий всяко, там конечно же изворачиваться как угодно можно
   AntonU
 
20 - 22.04.19 - 12:14
Вот код, очень медленно работает:

    НомерЛистаExcel=1;    
    Попытка
        // Загрузка Microsoft Excel

        Состояние("Загрузка Microsoft Excel...");
        ExcelПриложение = Новый COMОбъект("Excel.Application");
    Исключение
        Сообщить("Ошибка при загрузке Microsoft Excel." + Символы.ПС + ОписаниеОшибки(), СтатусСообщения.Внимание);
        Возврат;
    КонецПопытки;
    
    ExcelПриложение.DisplayAlerts = 0;// не задавать вопросы


    Попытка
        // Открытие файла Microsoft Excel

        Состояние("Открытие файла Microsoft Excel...");
        ExcelФайл = ExcelПриложение.WorkBooks.Open(ПутьКФайлу);
    Исключение
        Сообщить("Ошибка открытия файла Microsoft Excel." + ПутьКФайлу + Символы.ПС + ОписаниеОшибки(), СтатусСообщения.Внимание);
    КонецПопытки;
    
    Попытка
        // Обработка файла Microsoft Excel

        Состояние("Обработка файла Microsoft Excel...");
        // Читаем данные первого листа книги

        ExcelЛист = ExcelФайл.Sheets(1);
        
        // Определить количество строк и колонок выбранного листа книги Excel

        xlCellTypeLastCell = 11;
        ExcelПоследняяСтрока = ExcelЛист.Cells.SpecialCells(xlCellTypeLastCell).Row;
    Исключение
        Сообщить("Ошибка открытия листа №1 Microsoft Excel." + Символы.ПС + ОписаниеОшибки(), СтатусСообщения.Внимание);
        // Закрыть COM соединение для экономии памяти

        ExcelПриложение.Quit();
    КонецПопытки;
        
        индекс=1;
        ТЗ1=Новый ТаблицаЗначений;
        ТЗ1.Колонки.Добавить("Сумма");
        ТЗ1.Колонки.Добавить("Наим");
        ТЗ1.Колонки.Добавить("Уд");
        
        ТЗ2=Новый ТаблицаЗначений;
        ТЗ2.Колонки.Добавить("Сумма");
        ТЗ2.Колонки.Добавить("Наим");
        ТЗ2.Колонки.Добавить("Уд");
        Для индекс = 2 По ExcelПоследняяСтрока Цикл 
            
            Попытка
                Сумма1=ExcelЛист.Cells(индекс,1).Value;
            Исключение    
                Сумма1=0;
            КонецПопытки;
            
            Наим1=ExcelЛист.Cells(индекс,2).Text;

            Если Сумма1<>0 И Наим1<>"" Тогда
                НовСтр=ТЗ1.Добавить();
                НовСтр.Сумма=Сумма1;
                НовСтр.Наим=Наим1;
                НовСтр.Уд=Ложь;
            КонецЕсли;    
            
            Попытка
                Сумма2= ExcelЛист.Cells(индекс,3).Value;
            Исключение    
                Сумма2=0;
            КонецПопытки;    
            
            Наим2=ExcelЛист.Cells(индекс,4).Text;

            Если Сумма2<>0 И Наим2<>"" Тогда
                НовСтр=ТЗ2.Добавить();
                НовСтр.Сумма=Сумма2;
                НовСтр.Наим=Наим2;
                НовСтр.Уд=Ложь;
            КонецЕсли;    
            
        КонецЦикла;
   ДенисЧ
 
21 - 22.04.19 - 12:17
(19) Я с ним столкнулся, значит, пользовательский
   Filippov
 
22 - 22.04.19 - 12:17
(20) А попробуйте открыть ваш файл через Файл/Открыть. За какое время он прочитается?
   AntonU
 
23 - 22.04.19 - 12:21
(22) - за несколько секунд.
ExcelЛист.Cells - очень долго работает
   Filippov
 
24 - 22.04.19 - 12:29
(23) Так при чтении в табличный документ и преобразовании его в ТЗ сторонних средств не используется. К сожалению, у меня файлы небольшие, оценить время на их обработку не могу.
   Cyberhawk
 
25 - 22.04.19 - 12:30
(20) Лол. Это не нативное чтение.
   NorthWind
 
26 - 22.04.19 - 12:41
(20) ну, читали сверки от федеральной торговой сети. Нормально все. Существенной разницы между ADO и OLE-объектом Excel не заметил - реализовал и такое и такое чтение, потому что не на всех машинах есть Excel. Файл под 100 тыс строк где-то минуту дербанит вместе с подбором накладных по базе. Как по мне - нормально.
   Сияющий в темноте
 
27 - 22.04.19 - 15:32
Из ексель сразу весь занятый данными лист в двумерный массив через Range.Value,а потом у себя в 1с массив разбираем,получается очень быстро,если памяти для массива хватает.


Список тем форума
Рекламное место пустует  Рекламное место пустует
Требовать и эффективности, и гибкости от одной и той же программы — все равно, что искать очаровательную и скромную жену... по-видимому, нам следует остановиться на чем-то одном из двух.
Фредерик Брукс-младший
ВНИМАНИЕ! Если вы потеряли окно ввода сообщения, нажмите Ctrl-F5 или Ctrl-R или кнопку "Обновить" в браузере.
Рекламное место пустует