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

08.06.12

Разработка - Универсальные функции

Функция получения таблицы значений из файла 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С 8.3.24)

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Абонемент ($m)

Задача: вставить картинку из буфера обмена на форму средствами платформы 1С.

1 стартмани

18.03.2024    2670    0    John_d    8    

54

GUID в 1С 8.3 - как с ними быть

Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

Пришлось помучиться с GUID-ами немного, решил поделиться опытом, мало ли кому пригодится.

12.02.2024    4606    atdonya    22    

45

Переоткрытие внешних обработок

Универсальные функции Платформа 1С v8.3 Бесплатно (free)

На заключительных этапах, когда идет отладка или доработка интерфейса, необходимо много раз переоткрыть внешний объект. Вот один из способов автоматизации этого.

30.11.2023    3960    ke.92@mail.ru    16    

61

Валидация JSON через XDTO (включая массивы)

WEB-интеграция Универсальные функции Механизмы платформы 1С Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

При работе с интеграциями рано или поздно придется столкнуться с получением JSON файлов. И, конечно же, жизнь заставит проверять файлы перед тем, как записывать данные в БД.

28.08.2023    8818    YA_418728146    6    

141

Печать непроведенных документов для УТ, КА, ERP. Настройка печати по пользователям, документам и печатным формам

Пакетная печать Печатные формы Адаптация типовых решений Универсальные функции Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Россия Абонемент ($m)

Расширение для программ 1С:Управление торговлей, 1С:Комплексная автоматизация, 1С:ERP, которое позволяет распечатывать печатные формы для непроведенных документов. Можно настроить, каким пользователям, какие конкретные формы документов разрешено печатать без проведения документа.

2 стартмани

22.08.2023    2071    21    progmaster    7    

3

Расширение: Быстрые отборы через буфер [Alt+C] Копировать список, [Alt+V] Вставить список, [Ctrl+C] Копировать из файлов

Инструментарий разработчика Универсальные функции Платформа 1С v8.3 Конфигурации 1cv8 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Зарплата и Управление Персоналом 3.x Абонемент ($m)

Копирует в буфер значения из списков, из ячеек отчетов, таблиц, настроек списков, других отборов и вставляет в выбранную настройку отбора. Работает с Объект не найден. Работает как в одной так и между разными базами 1С. Использует комбинации [Alt+C] Копировать список, [Alt+V] Вставить список. Также для копирования данных используется стандартная [Ctrl+C] (например из открытого xls, mxl, doc и т.п. файла скопировать список наименований)

1 стартмани

13.10.2022    16143    133    sapervodichka    112    

129

Система контроля ведения учета [БСП]

Универсальные функции Механизмы типовых конфигураций БСП (Библиотека стандартных подсистем) Платформа 1С v8.3 Конфигурации 1cv8 Бесплатно (free)

В данном материале рассмотрим типовой алгоритм подсистемы контроля учета БСП в конфигурациях на примерах.

18.07.2022    7243    quazare    8    

109
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Alex_grem 256 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;	
Показать
moncat; user_pdd; script; +3 Ответить
6. 1yh1 13.06.12 12:46 Сейчас в теме
(1)
через ADODB быстрее, чем ч/з OLE
зависит от объема данных
Alexey_A; +1 Ответить
7. sbv2005 347 13.06.12 13:31 Сейчас в теме
(6) Действительно, скорость здесь не ставилась целью. Важнее было показать, что можно использовать язык запросов в среде Excel, кстати, используя все его многообразие функций, которых нет в стандартном TSQL.
2. sbv2005 347 06.06.12 12:35 Сейчас в теме
Да, согласен, по-своему неплохой вариант
3. CagoBHuK 32 06.06.12 20:18 Сейчас в теме
Люди порой получают плюсики за такую ахинею!
4. sbv2005 347 06.06.12 22:51 Сейчас в теме
5. mpei198 12.06.12 11:23 Сейчас в теме
Оставьте свое сообщение