Профессия — 1С

Рукопашный бой Карташ

Категории

Чтение данных из файлов Excel с помощью ADO

рубрики: ADO | Дата: 28 Январь, 2014

Несмотря на кажущуюся сложность, читать информацию из файлов Excel с помощью интерфейса ADO очень удобно. И по скорости это гораздо быстрее чем через COM соединение.

Прежде всего сформируем строку подключения



СтрокаСоединения = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=ПутьКФайлу;Extended Properties=""Excel 12.0;HDR=YES"";Persist Security Info=False";




Несколько слов о параметрах строки подключения

Provider=Microsoft.ACE.OLEDB.12.0 — здесь мы выбираем провайдера подключения. Зависит от версии Excel. в данном случае используется провайдер для версии MS Excel 2010;

Data Source=ПутьКФайлу — путь к файлу Excel вида «С:\musor\MyFile.xlsx»;

Extended Properties=»Excel 12.0;HDR=YES» — дополнительные свойства. Первый параметр (Excel 12.0) зависит от версии Excel. HDR=YES означает что первая строка на листе не будет восприниматься как данные. И в этом случае в момент построения запроса данные находящиеся в первой строке воспринимаются как имена полей, что существенно облегчает жизнь при написании запроса, да и в момент обхода выборки обращаться к значениям выборки через имена гораздо удобней. Причем в этом случае абсолютно неважно в каком порядке расположены колонки в файле. Конечно это накладывает определенные ограничения на содержимое ячеек первой строки. Если в ячейке будет «Фамилия, имя, отчество», мы не сможем выбрать в запросе это поле. А вот если «ФИО» или «ФамилияИмяОтчество», то все отработает корректно.

Подключаемся к файлу



Connection = Новый COMОбъект("ADODB.Connection");
Connection.ConnectionString = СтрокаСоединения;

Попытка
	
	Connection.Open();
	
Исключение
	
	Сообщить ("Не удалось подключиться к файлу Excel" );
	Возврат;
	
КонецПопытки;

Сформируем текст запроса.




Допустим что в нашем файле на листе «Лист1» есть колонки «ФИО» и «ДатаРождения». Текст запроса будет выглядеть следующим образом (обратите внимание на знак $ после имени листа):



ТекстЗапроса =
"SELECT
|    Лист.ФИО				AS ФИО,
|    Лист.ДатаРождения 		AS ДатаРождения
|FROM
|    [Лист1$] as Лист"
;

Выполняем запрос.



Выборка = Новый COMОбъект("ADODB.Recordset");

Попытка
	
	Выборка.Open(ТекстЗапроса, Connection);
	
Исключение
	
	Сообщить ("Проблемы с выполнением запроса");
	Возврат;
	
КонецПопытки;

Делаем обход выборки и создаем таблицу значений с прочитанными данными.



Таблица = Новый ТаблицаЗначений;

Таблица.Колонки.Добавить("ФИО",	Новый ОписаниеТипов("Строка"));
Таблица.Колонки.Добавить("ДатаРождения", Новый ОписаниеТипов("Строка"));

Пока НЕ Выборка.EOF() Цикл

	НоваяСтрока = Таблица.Добавить();
	
	НоваяСтрока.ФИО = СокрЛП(Выборка.Fields.Item("ФИО").Value);
	ДатаСтрока = СокрЛП(Выборка.Fields.Item("ДатаРождения").Value);
	
	Выборка.MoveNext();

КонецЦикла;

Выборка.Close();

10 комментариев на «“Чтение данных из файлов Excel с помощью ADO”»

  1. необычный подарок:

    Данный пост реально помог мне принять очень важное для себя решение. За что автору отдельное спасибо. Жду от Вас новых постов!

  2. radiopodarok.ru:

    Добавил в закладки. Теперь буду почаще читать!

  3. необычный подарок:

    Восхитительно

  4. Михаил:

    А что делать дальше с таблицей значений? Как ее передать на клиент?

  5. Owner:

    Во-первых не обязательно считывать данные в таблицу значений. А во-вторых COMОбъект можно и на клиенте создать.

  6. svetas:

    Не работает
    Код:
    СтрокаПодключения=»Provider=Microsoft.ACE.OLEDB.16.0;Data Source=G:\11\Кадорр, Б.Арнаутская 26-30.xlsx;Extended Properties=Excel 16.0;»;
    // создание соединения
    Connection=Новый ComObject(«ADODB.Connection»);
    // Подключение
    Попытка
    Connection.Open(СтрокаПодключения);
    Исключение
    // Возврат

    Сообщить(«Ошибка подключения к EXCEL»);
    Возврат;
    КонецПопытки;
    сообщить(«УСПЕШНО EXCEL»);
    кто знает почему?
    Excel 2016, 1c 8.3.9.2033

  7. svetas:

    Добрый День!
    есть
    Код:
    СтрокаПодключения=»Provider=Microsoft.ACE.OLEDB.12.0;Data Source=»+ ЭлементМассива.ПолноеИмя +»;Extended Properties=Excel 12.0;»;
    // создание соединения
    Connection=Новый ComObject(«ADODB.Connection»);
    // Подключение
    Попытка
    Connection.Open(СтрокаПодключения);
    Исключение
    // Возврат
    Сообщить(«Ошибка подключения к EXCEL»);
    Возврат;
    КонецПопытки;
    сообщить(«УСПЕШНО EXCEL»);

    // после подключения загружаем содержимое файла
    ТекстЗапроса =
    «SELECT
    | Лист.Категория1 AS Категория1,
    | Лист.Категория2 AS Категория2,
    | Лист.Категория3 AS Категория3,
    | Лист.Наименование AS Наименование,
    | Лист.Кол AS Кол,
    | Лист.Цена as Цена,
    | Лист.Сумма AS Сумма
    |FROM
    | [Лист1$] as Лист
    |Where Лист.Категория1 like &Категория1
    |»;

    Выборка = Новый COMОбъект(«ADODB.Recordset»);

    Попытка
    Выборка.Open(ТекстЗапроса, Connection);
    Исключение
    Сообщить («Проблемы с выполнением запроса»);
    Возврат;
    КонецПопытки;
    Вопрос в запросе есть строка
    Код:
    Where Лист.Категория1 like &Категория1
    но как значение параметра » &Категория1″ установить в строке
    Код:
    Выборка.Open(ТекстЗапроса, Connection);

    ЛЮДИ ДОБРЫЕ,
    ПОМОГИТЕ, ПОЖАЛУЙСТА

    • Owner:

      Первое, что приходит в голову это запрос делать без условия и отсечь ненужное на этапе обхода выборки.
      Во-вторых можно попытаться сформировать текст запроса динамически чтобы строка с условием получилась следующего вида:
      |Where Лист.Категория1 like ‘%Наименование категории%’

    • Евгений:

      В приведенном запросе нельзя устанавливать параметры, как в запросах 1С. Необходимо динамически формировать текст запроса. Вместо «&Категория1» необходимо сразу указывать текстовое представление значения категории 1.

  8. Ольга:

    Добрый день, а как можно отсечь данные в скрытых строках? Спасибо.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

   

2018г. Профессия — 1С. Обмен опытом по программированию в 1С