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

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

Категория: Публикации 1С

Подробнее

Описание
Подробнее

Описание

Получить эти данные можно, используя схему данных (кстати, работает не только для файлов эксель, но и для всех подключений 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");
	
КонецФункции

Каталог решений - Получение списка листов и колонок из EXCEL через ADO