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

Опубликовал Антонио Антонио (Fragster) в раздел Обмен - Загрузка и выгрузка в Excel

При работе с экселем через 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");
	
КонецФункции

См. также

Комментарии
1. Артем Бардюг (Йожкин Кот) 1027 10.07.13 13:45 Сейчас в теме
У программистов, часто работающих с источниками данных давно уже должна быть в закладках ссылка http://msdn.microsoft.com/en-us/library/ms675532(VS.85).aspx
2. Антонио Антонио (Fragster) 659 10.07.13 14:13 Сейчас в теме
(1) Йожкин Кот, согласен, на msdn'е есть эта информация, но найти ее там проблематично, особенно, когда с английским не очень.
3. Артем Бардюг (Йожкин Кот) 1027 12.07.13 17:07 Сейчас в теме
(2) я наоборот статьи с MSDN читаю только на английском, т.к. из-за корявого русского перевода невозможно понять что имелось ввиду в оригинале.
4. Владимир Чаклин (vec435) 14 17.07.13 09:53 Сейчас в теме
технический английский-архинужен
5. Sergey A. (spaminfostart) 13 11.02.14 17:06 Сейчас в теме
Познавательно. Попробую развить, чтобы получать-таки лист книги по его номеру (не индексу!).
6. Демин Денис (Dionisy_nb) 14.02.14 12:06 Сейчас в теме
(1) Йожкин Кот, Ссылка удалена, а что там по ней было?
7. Дмитрий Мануйлов (Натц) 14.07.14 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;
    КонецЦикла;
...Показать Скрыть

NN2P; slaxxals; Shida; YuriFm; +4 Ответить 1
8. Юрий Водич (YuriFm) 14.07.14 18:53 Сейчас в теме
(7) Натц, плюсую, полезная штука, взял на вооружение.
9. Денис Аграновский (de0nis) 148 25.01.16 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();
...Показать Скрыть
NN2P; KapasMordorov; Fragster; +3 Ответить