Получение списка листов и колонок из EXCEL через ADO

При работе с экселем через ADO при запросе данных нужно указать, с какого листа нужно брать данные. Под катом я покажу, как получить имя листа, не прибегая к "хардкоду" его имени и не открывая excel через OLE.

Получить эти данные можно, используя схему данных (кстати, работает не только для файлов эксель, но и для всех подключений ADO).

Для получения данных — используются магические числа:

adSchemaTables = 20;
adSchemaColumns = 4;

Итак, Connection — это наше подключение.

 Recordset = Connection.OpenSchema(adSchemaColumns);

ТЗ = Новый ТаблицаЗначений();
Для Сч = 0 по Recordset.Fields.Count-1 Цикл
ТЗ.Колонки.Добавить(Recordset.Fields(Сч).Name, Новый ОписаниеТипов("Строка",,Новый КвалификаторыСтроки(150)));
КонецЦикла;

Пока Не Recordset.EoF() Цикл
Стр = ТЗ.Добавить();
Для каждого Колонка Из ТЗ.Колонки Цикл
Стр[Колонка.Имя] = Recordset.Fields(Колонка.Имя).Value;
КонецЦикла;
Recordset.MoveNext();
КонецЦикла;

Теперь в нашей таблице данные о том, какие листы таблицы есть в нашем файле экселя источнике данных.

Соответственно, если указать вместо adSchemaTables adSchemaColumns, то информация будет уже о колонках (включая листы, на которых эти колонки уже находятся). Используя эту информацию, я написал функцию, которая возвращает имена листов, на которых находятся нужные нам колонки (так как иногда пользователи лепят данные сразу на несколько листов, например, по периодам и т.п.):

На входе — соединение с источником данных и массив имен колонок, которые ищем на листах в файле

Функция ПолучитьСписокЛистов(Connection, МассивКолонок)

adSchemaColumns = 4;

Recordset = Connection.OpenSchema(adSchemaColumns);

ТЗ = Новый ТаблицаЗначений();
Для Сч = 0 по Recordset.Fields.Count-1 Цикл
ТЗ.Колонки.Добавить(Recordset.Fields(Сч).Name, Новый ОписаниеТипов("Строка",,Новый КвалификаторыСтроки(150)));
КонецЦикла;

Пока Не Recordset.EoF() Цикл
Стр = ТЗ.Добавить();
Для каждого Колонка Из ТЗ.Колонки Цикл
Стр[Колонка.Имя] = Recordset.Fields(Колонка.Имя).Value;
КонецЦикла;
Recordset.MoveNext();
КонецЦикла;

Запрос = Новый Запрос();
Запрос.Текст =
"ВЫБРАТЬ
| СтруктураФайла.TABLE_NAME,
| СтруктураФайла.COLUMN_NAME
|ПОМЕСТИТЬ СтруктураФайла
|ИЗ
| &СтруктураФайла КАК СтруктураФайла
|;
|
|////////////////////////////////////////////////////////////////////////////////
|ВЫБРАТЬ
| СтруктураФайла.TABLE_NAME,
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ СтруктураФайла.COLUMN_NAME) КАК COLUMN_NAME
|ИЗ
| СтруктураФайла КАК СтруктураФайла
|ГДЕ
| СтруктураФайла.COLUMN_NAME В (&МассивКолонок)
|
|СГРУППИРОВАТЬ ПО
| СтруктураФайла.TABLE_NAME
|
|ИМЕЮЩИЕ
| КОЛИЧЕСТВО(РАЗЛИЧНЫЕ СтруктураФайла.COLUMN_NAME) = &КоличествоКолонок";
Запрос.УстановитьПараметр("СтруктураФайла", ТЗ);
Запрос.УстановитьПараметр("МассивКолонок", МассивКолонок);
Запрос.УстановитьПараметр("КоличествоКолонок", МассивКолонок.Количество());

Возврат Запрос.Выполнить().Выгрузить().ВыгрузитьКолонку("TABLE_NAME");

КонецФункции

10 Comments

Йожкин Кот 10.07.2013 at 13:45

У программистов, часто работающих с источниками данных давно уже должна быть в закладках ссылка http://msdn.microsoft.com/en-us/library/ms675532(VS.85).aspx

Reply ↓
Fragster 10.07.2013 at 14:13

(1) Йожкин Кот, согласен, на msdn’е есть эта информация, но найти ее там проблематично, особенно, когда с английским не очень.

Reply ↓
Йожкин Кот 12.07.2013 at 17:07

(2) я наоборот статьи с MSDN читаю только на английском, т.к. из-за корявого русского перевода невозможно понять что имелось ввиду в оригинале.

Reply ↓
vec435 17.07.2013 at 09:53

технический английский-архинужен

Reply ↓
spaminfostart 11.02.2014 at 17:06

Познавательно. Попробую развить, чтобы получать-таки лист книги по его номеру (не индексу!).

Reply ↓
Dionisy_nb 14.02.2014 at 12:06

(1) Йожкин Кот, Ссылка удалена, а что там по ней было?

Reply ↓

Натц 14.07.2014 at 17:15

Честно говоря не особо понял махинации автора и решил поискать по проще в итоге наткнулся на вот такой код

СтрокаПодключения = «Provider=Microsoft.Jet.OLEDB.4.0; Data Source = «+СокрЛП(ФайлДанных);
СтрокаПодключения = СтрокаПодключения + «; Extended Properties = «+»»»Excel 8.0″+»;HDR=NO;IMEX=1″»;»;

//подключение к источнику данных
Попытка
Connection.Open(СтрокаПодключения);
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;

//создание объекта выполнения команды
Command = Новый COMОбъект(«ADODB.Command»);

//создание объекта подключения к файлу
axCatalog = Новый COMОбъект(«ADOX.Catalog»);
axCatalog.ActiveConnection = Connection;

//получим первый лист документа
НомерЛиста=1;
Для каждого Лист ИЗ axCatalog.Tables Цикл
Если НомерЛиста = 1 Тогда
//получим имя таблицы,затем будем использовать в запросе
ИмяТаблицы = Лист.Name;
Прервать;
КонецЕсли;

НомерЛиста = НомерЛиста+1;
КонецЦикла;

Показать

Reply ↓

YuriFm 14.07.2014 at 18:53

(7) Натц, плюсую, полезная штука, взял на вооружение.

Reply ↓

de0nis 25.01.2016 at 13:42

Еще в обоих предложенных вариантах получения имени листа, если в книге есть автофильтры на листе, то за таблицей листа на котором установлен фильтр добавляется таблица для фильтра с именем ИмяЛиста_xlnm#_FilterDatabase. Ее надо отфильтровывать, т.к. она не является листом.

rst = Новый COMОбъект(«ADODB.Recordset»);
rst = АДОСоединение.OpenSchema(20);
СписокТаблиц = Новый Массив;
ТекНомерЛиста = 0;
Пока НЕ rst.EOF Цикл
ИмяЛистаExcel = СокрЛП(СтрЗаменить(rst.Fields(«TABLE_NAME»).Value,»$»,»»));
Если Найти(ИмяЛистаExcel,»_xlnm#_FilterDatabase») > 0 Тогда
// Таблица для автофильтра. Пропускаем ее.
rst.MoveNext();
Продолжить;
КонецЕсли;

ТекНомерЛиста = ТекНомерЛиста + 1;
Если НомерЛиста = ТекНомерЛиста Тогда
ИмяЛистаExcel = СтрЗаменить(ИмяЛистаExcel,»‘»,»»);
Прервать;
КонецЕсли;
rst.MoveNext();
КонецЦикла;
rst.Close();

Показать

Reply ↓

Tolpinski 22.12.2018 at 09:24

И в случае OpenSchema(20) и в случае ADOX.Catalog порядок имён листов отличается от файла, в случае если листы в файле перетаскивались, как быть?

Reply ↓

Получение списка листов и колонок из EXCEL через ADO

Related Posts

10 Comments

Leave a Comment Отменить ответ