Категории
Excel (2)
- SQL (4)
- Web-Сервисы (3)
- Word (1)
- XML (3)
- Документы (1)
- Запросы (11)
- ЗУП 2.5 (5)
- ЗУП 3.1 (2)
- Иерархические структуры (2)
- Конструктор запросов (7)
- Метаданные (1)
- Начинающим (8)
Обмен данными (7)
- Отчеты и обработки (3)
- Платформа 8.3 (9)
- Работа с формами (6)
- Разное (5)
- Регистры (5)
- Регламентные задания (3)
- Сервер 1С:Предприятия (1)
- Скачать (1)
- СКД (8)
- УРБД (2)
- Философия разработки (1)
- Фишки (8)
- Язык программирования 1С (7)
Чтение данных из файлов 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();
13 комментариев на «“Чтение данных из файлов Excel с помощью ADO”»
Добавить комментарий
Данный пост реально помог мне принять очень важное для себя решение. За что автору отдельное спасибо. Жду от Вас новых постов!
Добавил в закладки. Теперь буду почаще читать!
Восхитительно
А что делать дальше с таблицей значений? Как ее передать на клиент?
Во-первых не обязательно считывать данные в таблицу значений. А во-вторых COMОбъект можно и на клиенте создать.
Не работает
Код:
СтрокаПодключения=»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
Добрый День!
есть
Код:
СтрокаПодключения=»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);
ЛЮДИ ДОБРЫЕ,
ПОМОГИТЕ, ПОЖАЛУЙСТА
Первое, что приходит в голову это запрос делать без условия и отсечь ненужное на этапе обхода выборки.
Во-вторых можно попытаться сформировать текст запроса динамически чтобы строка с условием получилась следующего вида:
|Where Лист.Категория1 like ‘%Наименование категории%’
В приведенном запросе нельзя устанавливать параметры, как в запросах 1С. Необходимо динамически формировать текст запроса. Вместо «&Категория1» необходимо сразу указывать текстовое представление значения категории 1.
Добрый день, а как можно отсечь данные в скрытых строках? Спасибо.
good! super!
Годнота
Я чуял, что отыщу нужную мне инфу тут на сайте. Просто хотел поблагодарить