SQL

Загрузка из Excel в 1С

Загрузка из Excel в 1С, довольно частая задача, которую приходится решать программистам 1С. В данной заметке будет показано, как произвести загрузку данных из файла Excel в 1С, наиболее быстрым и оптимальным способом. Перед тем как показать примеры кода, объяснюю как производить отладку и проверку выполнения SQL запросов к файлу Excel, для последующей загрузки данных в программу 1С.

Для примера будет использоваться файл формата Excel со следующей структурой и данными:

Чтобы выполнить SQL запрос к файлу Excel и проверить результат, необходимо запустить программу Microsoft Excel, перейти в главном меню программы на вкладку «Данные», далее нажать кнопку «Из других источников» и в появившемся меню выбрать пункт «Из Microsoft Query»:

После появится окно «Выбор источника данных», в нем необходимо выбрать пункт «Excel Files*» и нажать «ОК»:

Далее выбрать непосредственно ваш файл Excel для выборки данных. Может появится предупреждение, что «Данный источник данных не содержит видимых таблиц», игнорируем его и нажимаем «ОК». После необходимо в параметрах установить пункт отображать системные таблицы:

После включения данной опции, появится возможность выбора листов из книги Excel и столбцов для запроса:

Дальше необходимо несколько раз нажать «Далее» и на последнем шаге, выбрать пункт «Просмотр или изменение данных в Microsoft Query» и «Готово»:

Откроется окно «Microsoft Query», в котором мы видим выборку данных из нашего файл Excel. Для тестирования и редактирования SQL запросов к файлу Excel, используется кнопка «SQL», при помощи которой вызывается окно редактирования запроса:

Теперь, когда вы знаете, как тестировать ваши SQL запросы к файлу Excel, попробуем загрузить данные в программу 1С, для этого создадим внешнюю обработку, добавим основную форму, для удобства вывода информации добавим реквизит формы с типом «Табличный документ» и отобразим его на форме. В модуле формы добавим процедуру «ПриОткрытии».
Далее приведен код с поясняющими комментариями:

&НаКлиенте
Процедура ПриОткрытии(Отказ)
	
	//Указываем путь к файлу Excel
	ФайлExcel = "C:\Users\Константин\Desktop\Данные.xls";
	
	//Создаем объект Файл
	ФайлОстатков = Новый Файл(ФайлExcel);
	
	//Проверка на существование файла
	Если ФайлОстатков.Существует() Тогда
		
		//Формируем строку соединения
		СтрокаСоединения = "Driver={Microsoft Excel Driver (*.xls)};
		|DriverId=790;
		|Dbq="        + ФайлОстатков.ПолноеИмя + ";
		|DefaultDir=" + ФайлОстатков.Путь + ";"; 
		
		//Создаем СОМ объект ADODB.Connection
		Соединение = Новый COMОбъект("ADODB.Connection"); 
		Попытка
			//Пробуем подключится к файлу Excel
			Соединение.Open(СтрокаСоединения);           
		Исключение
			Сообщить("Ошибка соединения ADODB.Connection: " + ОписаниеОшибки(), СтатусСообщения.Внимание);
			Возврат;	
		КонецПопытки;
		
		//Пробуем получить имена колонок из файла Excel
		//В случае возникновении ошибки: Попробуйте написать: "SELECT * FROM "[Лист1$A1:IV]" или "SELECT * FROM `Sheet1$A1:IV`"
		ТекстЗапросаSQL = "SELECT * FROM `Лист1$A1:IV`";
		Попытка
			НаборЗаписей = Соединение.Execute(ТекстЗапросаSQL);
		Исключение
			Сообщить("Ошибка соединения ADODB.Recordset: " + ОписаниеОшибки(), СтатусСообщения.Внимание);
			Возврат;	
		КонецПопытки;
		
		//Проверяем количество полученных полей
		Если НаборЗаписей.Fields.Count > 0 Тогда
			
			//Выводим полученные имена колонок
			СтрокДляВывода = "";
			Для каждого СтрокаИзНабора Из НаборЗаписей.Fields Цикл
				СтрокДляВывода = СтрокДляВывода + СтрокаИзНабора.Name + Символы.Таб;
			КонецЦикла;
			
			ВыводСообщений.ДобавитьСтроку(СтрокДляВывода);
			
			//Пробуем получить данные из файла Excel
			ТекстЗапросаSQL = "SELECT * FROM `Лист1$A1:BB`";
			Попытка
				НаборЗаписей = Соединение.Execute(ТекстЗапросаSQL);
			Исключение
				Сообщить("Ошибка соединения ADODB.Recordset: " + ОписаниеОшибки(), СтатусСообщения.Внимание);
				Возврат;	
			КонецПопытки;
			
			//Выводим данные на экран
			Пока НЕ НаборЗаписей.EOF Цикл
				
				СтрокДляВывода = "";
				Для каждого СтрокаИзНабора Из НаборЗаписей.Fields Цикл
					СтрокДляВывода = СтрокДляВывода + СтрокаИзНабора.Value + Символы.Таб;
				КонецЦикла;
				
				ВыводСообщений.ДобавитьСтроку(СтрокДляВывода);
				
				//Перемещяем курсор дальше
				НаборЗаписей.MoveNext();
				
			КонецЦикла;
			
		Иначе
			Сообщить("Не найдены наименования колонок!");
		КонецЕсли;
		
		//Закрываем соединение
		Соединение.Close();
		
	Иначе
		Сообщить("Файл: " + ФайлExcel + " не найден!");
	КонецЕсли;
	
КонецПроцедуры

Результат выполнения внешней обработки по загрузке данных из файла Excel в 1С с помощью запроса SQL, показан на изображении:

Скачать файлы внешней обработки и данных Excel.