gifts2017

Функция получения таблицы значений из файла Excel

Опубликовал sbv2005 (sbv2005) в раздел Программирование - Практика программирования

Функция получения таблицы значений из файла Excel. Использует запрос в среде самого Excel.
Параметры функции:
ПутьКФайлу (строка)  - полный путь к файлу Excel
ИмяНомерСтраницы     - имя (строка) или порядковый номер (число) страницы файла Excel
Заголовок (булево)   - является ли первая строка строкой заголовка. Если Истина, то колонки будущей ТЗ проименуются из строки заголовка Excel, если Ложь, то колонки проименуются системно "F1", "F2" и т.д
ПерваяСтрока (число) - номер первой строки считывания с листа Excel

ВАЖНО!!! Типы колонок будущей ТЗ будут определяться типом первой строки данных Excel (именно данных, не заголовка). Поэтому важно соблюдать в Excel тип колонок данных

Функцию достаточно разместить в общем модуле своей конфигурации.


// Функция возвращает таблицу значений ТЗ из данных файла Excel
//
// ПутьКФайлу (строка)  - полный путь к файлу Excel
// ИмяНомерСтраницы     - имя (строка) или порядковый номер (число) страницы файла Excel
// Заголовок (булево)   - является ли первая строка строкой заголовка. Если Истина, то колонки будущей ТЗ проименуются из строки заголовка Excel, если Ложь, то колонки проименуются системно "F1", "F2" и т.д
// ПерваяСтрока (число) - номер первой строки считывания с листа Excel

// ВАЖНО!!! Типы колонок будущей ТЗ будут определяться типом первой строки данных Excel (именно данных, не заголовка). Поэтому важно соблюдать в Excel тип колонок данных

Функция ПолучитьТЗИзEXCEL(ПутьКФайлу, ИмяНомерСтраницы, Заголовок = Истина, ПерваяСтрока = 0) Экспорт

   
ТЗ  = Новый ТаблицаЗначений;

   
#Если Клиент Тогда
       
Состояние("Подключение к файлу Excel...");
   
#КонецЕсли
   
//
   
Попытка
       
Эксель = Новый COMОбъект("Excel.Application");
       
Версия = СтрПолучитьСтроку(СтрЗаменить(Эксель.Version, ".", Символы.ПС), 1);
    Исключение
       
СообщениеТекста("Ошибка подключения к "+ПутьКФайлу+" : "+ОписаниеОшибки(),,СтатусСообщения.Важное);
        Возврат
ТЗ;
    КонецПопытки;
   
//
   
Книга = Эксель.WorkBooks.Open(ПутьКФайлу);
   
// Выбираем данные запросом из таблицы файла по имени страницы
   
Если ТипЗнч(ИмяНомерСтраницы) = Тип("Число")  Тогда
       
Лист        = Книга.WorkSheets(ИмяНомерСтраницы);
       
ИмяСтраницы = Лист.Name;
    Иначе
       
ИмяСтраницы = ИмяНомерСтраницы;
    КонецЕсли;
   
//
    // Определим диапазон данных Excel
   
ПослЯчейка = СтрЗаменить(Книга.WorkSheets(ИмяСтраницы).Cells(1,1).SpecialCells(11).Address, "$", "");
   
//
   
Эксель.Application.Quit();
   
//
   
Эксель  = неопределено;
   
Книга   = неопределено;
   
Лист    = неопределено;

   
// Подлючаемся

   
Connection          = Новый COMОбъект("ADODB.Connection");
    Если
Версия = "12" ИЛИ Версия = "14" Тогда
       
СтрокаПодключения   ="Provider =Microsoft.ACE.OLEDB.12.0;Data Source="+ПутьКФайлу+";Extended Properties=""Excel 12.0 Xml;HDR="+?(Заголовок,"YES","NO")+";IMEX=1""";
    Иначе
       
СтрокаПодключения   ="Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ПутьКФайлу+";Extended Properties=""Excel 8.0;HDR="+?(Заголовок,"YES","NO")+";IMEX=1""";
    КонецЕсли;

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

   
Command=Новый COMОбъект("ADODB.Command");
   
Command.ActiveConnection=Connection;
   
Command.CommandText = ?(ПерваяСтрока = 0, "Select * From [" + ИмяСтраницы + "$]", "Select * From [" + ИмяСтраницы + "$A" + ПерваяСтрока + ":" + ПослЯчейка + "]");

    Попытка
       
Записи=Command.Execute();
    Исключение
       
СообщениеТекста("Ошибка при выполнении запроса!"+ОписаниеОшибки(), ,СтатусСообщения.ОченьВажное);
        Возврат
ТЗ;
    КонецПопытки;

    Для
НомерПоля = 0 По Записи.Fields.Count-1 Цикл
       
СтрКолонка = Строка(Записи.Fields.Item(НомерПоля).Name);
       
СтрКолонка = СтрЗаменить(СтрКолонка,"№","Num");
       
НедопустимыеСимволы = " ~`!@""#№$;%^:&?*()-+=/\|.,";
        Для
i = 1 По СтрДлина(НедопустимыеСимволы) Цикл
           
СтрКолонка = СтрЗаменить(СтрКолонка, Mid(НедопустимыеСимволы, i, 1), "_")
        КонецЦикла;
       
ИмяКолонки = СокрЛП(СтрКолонка);
       
ТЗ.Колонки.Добавить(ИмяКолонки, , СтрКолонка);
    КонецЦикла;

    Пока НЕ
Записи.EOF() Цикл
       
НоваяСтрока = ТЗ.Добавить();
        Для
НомерПоля = 0 По Записи.Fields.Count-1 Цикл
           
НоваяСтрока[НомерПоля] = Записи.Fields(Записи.Fields.Item(НомерПоля).Name).Value;
        КонецЦикла;
       
Записи.MoveNext();
    КонецЦикла;

   
Command = Неопределено;
   
Записи  = Неопределено;

    Возврат
ТЗ;

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





См. также

Подписаться Добавить вознаграждение

Комментарии

1. Алексей Гафуров (Alex_grem) 06.06.12 08:53
Как-то все сложно - 2 com-объекта, запросы...я делаю так:

	Док = ПолучитьCOMОбъект(ПутьКФайлу);
	
	ИмяКниги = ПутьКФайлу;
	поз = Найти(ИмяКниги, "\");
	Пока поз > 0 Цикл
		ИмяКниги = Прав(ИмяКниги, СтрДлина(ИмяКниги) - поз );
		поз = Найти(ИмяКниги, "\");
	КонецЦикла;	
	
	ТекЛист = Док.Application.Workbooks(ИмяКниги).Worksheets(1);
	КолСтрок = ТекЛист.UsedRange.Rows.Count;
	КолСтолбцов = ТекЛист.UsedRange.Columns.Count;
	
	СомОбластьДанных = ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(КолСтрок,КолСтолбцов));
	Данные = СомОбластьДанных.Value.Выгрузить();
	
	ТабДанных = Новый ТаблицаЗначений;
	
	Для Сч=0 По КолСтолбцов-1 Цикл
	
		ТабДанных.Колонки.Добавить("Колонка" + Сч);
	
	КонецЦикла;
	
	Для Сч=1 По КолСтрок Цикл
	
		ТабДанных.Добавить();
	
	КонецЦикла;
	
	Для Сч=0 По КолСтолбцов-1 Цикл
	
		ТабДанных.ЗагрузитьКолонку(Данные[Сч], "Колонка"+Сч);
	
	КонецЦикла;
	
	Док = 0;	
...Показать Скрыть
2. sbv2005 (sbv2005) 06.06.12 12:35
Да, согласен, по-своему неплохой вариант
3. Данила Елистратов (CagoBHuK) 06.06.12 20:18
Люди порой получают плюсики за такую ахинею!
4. sbv2005 (sbv2005) 06.06.12 22:51
5. Александр Коновалов (mpei198) 12.06.12 11:23
6. Вик (1yh1) 13.06.12 12:46
(1)
через ADODB быстрее, чем ч/з OLE
зависит от объема данных
7. sbv2005 (sbv2005) 13.06.12 13:31
(6) Действительно, скорость здесь не ставилась целью. Важнее было показать, что можно использовать язык запросов в среде Excel, кстати, используя все его многообразие функций, которых нет в стандартном TSQL.
Для написания сообщения необходимо авторизоваться
Прикрепить файл
Дополнительные параметры ответа