gifts2017

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

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