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

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

Как выгрузить данные из эксель в ТЗ ?
1 AntonU
 
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 = Неопределено;
    
    Возврат ТаблицаРезультат;
    
КонецФункции
2 AntonU
 
19.04.19
16:19
Версия 1С Предприятия 8.3.12
3 shuhard
 
19.04.19
16:20
(0)[Как выгрузить данные из эксель в ТЗ ?]
дык через ADODB
4 AntonU
 
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;""";
5 mikecool
 
22.04.19
10:38
(4) у провайдеров есть ограничения на размер файла, емнип
MXL.Прочитать(АдресФайла); - это работает, правда файлы нужно не ниже 97 офиса
разбирайся со своим построителем
6 Filippov
 
22.04.19
11:17
(0) Если интересно, беру из xls без ADODB:
Процедура Прочитать(Команда)
    ПрочитатьНаСервере();
    ПреобразоватьВТЗНаСервере();
КонецПроцедуры

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

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

&НаСервере
Функция ПреобразоватьТабличныйДокументВТаблицуЗначений(ТабДокумент)    
    ОбластьФайла = ТабДокумент.ПолучитьОбласть(Объект.Организация.Префикс);
    ПоследняяСтрока = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоВертикали();
    ПоследняяКолонка = ОбластьФайла.ПолучитьРазмерОбластиДанныхПоГоризонтали();    
    ОбластьЯчеек = ОбластьФайла.Область(1, 1, ПоследняяСтрока, ПоследняяКолонка);
    
    // Создаем описание источника данных на основании области ячеек табличного документа.
    ИсточникДанных = Новый ОписаниеИсточникаДанных(ОбластьЯчеек);
        
    // Создаем объект для интеллектуального построения отчетов,
    // указываем источник данных и выполняем построение отчета.
    ПостроительОтчета = Новый ПостроительОтчета;    
    ПостроительОтчета.ИсточникДанных = ИсточникДанных;
    ПостроительОтчета.Выполнить();
    
    // Результат выгружаем в таблицу значений.
    ТабЗначений = ПостроительОтчета.Результат.Выгрузить();
    
    Возврат ТабЗначений
    
КонецФункции
7 ДенисЧ
 
22.04.19
11:19
(6) А теперь представь, что у тебя 8,2.
8 Filippov
 
22.04.19
11:22
(7) Было написано Версия 1С Предприятия 8.3.12
9 Filippov
 
22.04.19
11:24
Причем работает с xls, xlsx, таблицей Libre Office
10 AntonU
 
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
11 Cyberhawk
 
22.04.19
11:59
хлсх всегда нативно читаю
12 Cyberhawk
 
22.04.19
11:59
Только с датами может всплывать нюанс.
А 1Сное чтение вообще от релиза к релизу с ошибками.
13 AntonU
 
22.04.19
12:00
(11) - очень долго получается, если построчно
14 Cyberhawk
 
22.04.19
12:01
(13) Плата за надежность (полный контроль) и универсальность (кроссплатформенность)
15 Cyberhawk
 
22.04.19
12:02
Да и зачем читать быстро - в фоне главное выполнять с отображением прогресса
16 ДенисЧ
 
22.04.19
12:03
(15) Когда тебе нужно прочитать 5000 файлов за определенное время - тут скорость ещё как играет
17 AntonU
 
22.04.19
12:05
Около 72 тыс. строк пришло в файле, читалось почти 20 минут. Перебор
18 Cyberhawk
 
22.04.19
12:06
30 тыщ строк не более минуты у меня читается
19 Cyberhawk
 
22.04.19
12:06
(16) Ну это не пользовательский сценарий всяко, там конечно же изворачиваться как угодно можно
20 AntonU
 
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) Я с ним столкнулся, значит, пользовательский
22 Filippov
 
22.04.19
12:17
(20) А попробуйте открыть ваш файл через Файл/Открыть. За какое время он прочитается?
23 AntonU
 
22.04.19
12:21
(22) - за несколько секунд.
ExcelЛист.Cells - очень долго работает
24 Filippov
 
22.04.19
12:29
(23) Так при чтении в табличный документ и преобразовании его в ТЗ сторонних средств не используется. К сожалению, у меня файлы небольшие, оценить время на их обработку не могу.
25 Cyberhawk
 
22.04.19
12:30
(20) Лол. Это не нативное чтение.
26 NorthWind
 
22.04.19
12:41
(20) ну, читали сверки от федеральной торговой сети. Нормально все. Существенной разницы между ADO и OLE-объектом Excel не заметил - реализовал и такое и такое чтение, потому что не на всех машинах есть Excel. Файл под 100 тыс строк где-то минуту дербанит вместе с подбором накладных по базе. Как по мне - нормально.
27 Сияющий в темноте
 
22.04.19
15:32
Из ексель сразу весь занятый данными лист в двумерный массив через Range.Value,а потом у себя в 1с массив разбираем,получается очень быстро,если памяти для массива хватает.
Проблемы невозможно решaть нa том же уровне компетентности, нa котором они возникaют. Альберт Эйнштейн