gifts2017

Загрузка произвольных данных из Excel и вывод их на управляемую форму

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

Добрый день всем, много есть разных обработок по загрузке из Excel в 1с. Также много всего сказано про программный вывод элементов на управляемые формы. Хотелось бы все это объединить :). Может, кому-то будет полезно

И так, вот в принципе что нам надо, это обработка с реквизитом "Путь_к_Файлу" и командой для чтения Excel

 

А дальше собственно код обработки:

 

1. Пару процедур по выбору-открытию файла:

&НаКлиенте
Процедура Путь_к_ФайлуНачалоВыбора(Элемент, ДанныеВыбора, СтандартнаяОбработка)

СтандартнаяОбработка = Ложь;

Диалог = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
Диалог.Заголовок 	= "Выберите файл...";
Фильтр 			= "Файлы Excel (*.xls*)|*.xls*"; 
Диалог.Фильтр 		= Фильтр;   
Диалог.ПолноеИмяФайла 	= Объект.Путь_к_Файлу;

Если Диалог.Выбрать() Тогда
 Объект.Путь_к_Файлу = Диалог.ПолноеИмяФайла;
КонецЕсли;

КонецПроцедуры

&НаКлиенте
Процедура Путь_к_ФайлуОткрытие(Элемент, СтандартнаяОбработка)

СтандартнаяОбработка = Ложь;
ЗапуститьПриложение(Объект.Путь_к_Файлу);

КонецПроцедуры

2. а теперь чтение Excel и вывод

&НаКлиенте
Процедура ПрочитатьДанныеExcel(Команда)
	
	Если Не ЗначениеЗаполнено(Объект.Путь_к_Файлу) Тогда 
		Сообщить("Не указан файл для обработки!");
		Возврат
	КонецЕсли;
	
	ВыбФайл = Новый Файл(Объект.Путь_к_Файлу);
	Если Не ВыбФайл.Существует() Тогда
		Сообщить("Файл не существует:  " + Объект.Путь_к_Файлу);
		Возврат;
	КонецЕсли;
	
	Попытка
		xlLastCell 			= 11;
		Excel 				= Новый COMОбъект("Excel.Application");
		Excel.DisplayAlerts = Ложь;
		Книга 				= Excel.WorkBooks.Open(Объект.Путь_к_Файлу);					
		Лист 				= Книга.WorkSheets("Товары");
		RowCount 			= Лист.Cells(1,1).SpecialCells(xlLastCell).Row;
		ColumnCount 			= Лист.Cells(1,1).SpecialCells(xlLastCell).Column;
		
		СтруктураКолонок = Новый Структура;
		//получим имена всех колонок и их типы
		Для Column = 1 По ColumnCount Цикл
			ИмяКолонки 	= ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
			СтруктураКолонок.Вставить(ИмяКолонки, ТипЗнч(Лист.Cells(2,Column).Value));
		КонецЦикла;
		ДополнитьФормуНаСервере(СтруктураКолонок);
		Для Row = 2 По RowCount Цикл
			СтруктураКолонок.Очистить();
			//получим имена всех колонок и значение в данной строке Excel
			Для Column = 1 По ColumnCount Цикл
				ИмяКолонки 	= ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
				СтруктураКолонок.Вставить(ИмяКолонки, Лист.Cells(Row,Column).Value);
			КонецЦикла;
			
			Элементы.тзФормы.ДобавитьСтроку();
			Для Каждого Стр Из СтруктураКолонок Цикл
				Элементы.тзФормы.ТекущиеДанные[Стр.Ключ] = Стр.Значение;
			КонецЦикла;
			
			ПроцентОбработки = Окр(Row / RowCount * 100);
			Состояние("Обработка файла Microsoft Excel...", ПроцентОбработки, "Процент обработанных строк");				
		КонецЦикла;
		ПреобразоватьРеквизитФормы();
		
		Excel.WorkBooks.Close();
		Excel	= Неопределено;		
	Исключение
		Сообщить(ОписаниеОшибки());
		Excel.WorkBooks.Close();
		Excel 	= Неопределено;		
	КонецПопытки;

КонецПроцедуры
&НаКлиенте
Функция ПолучитьИмяКолонки(ИмяКолонкиПредставление)
	//имена колонок могут начинаться только с букв, а также исключим все специальные символы, которые не могут присутствовать в именах колонок
  ИмяКолонки = "";
  Для ш = 1 По СтрДлина(ИмяКолонкиПредставление) Цикл
   СимволИмяКолонки   = Сред(ИмяКолонкиПредставление, ш, 1);
  КодСимволаИмяКолонки  = КодСимвола(СимволИмяКолонки);
   Если КодСимволаИмяКолонки >= 1040 И КодСимволаИмяКолонки     ИмяКолонки = ИмяКолонки + СимволИмяКолонки;
   ИначеЕсли КодСимволаИмяКолонки >= 65 И КодСимволаИмяКолонки     ИмяКолонки = ИмяКолонки + СимволИмяКолонки;
   ИначеЕсли КодСимволаИмяКолонки >= 97 И КодСимволаИмяКолонки     ИмяКолонки = ИмяКолонки + СимволИмяКолонки;
   ИначеЕсли КодСимволаИмяКолонки >= 48 И КодСимволаИмяКолонки     Если ш = 1 Тогда
      ИмяКолонки = ИмяКолонки + "";
   Иначе
     ИмяКолонки = ИмяКолонки + СимволИмяКолонки;
    КонецЕсли;
   Иначе
    ИмяКолонки = ИмяКолонки + "";
   КонецЕсли;
  КонецЦикла;
 
  Возврат ИмяКолонки;
 
КонецФункции
 
&Насервере
Процедура ДополнитьФормуНаСервере(СтруктураКолонок)
	
	МассивРеквизитов 		= Новый Массив;
	МассивУдаляемыхРеквизитов 	= Новый Массив;
	МассивТипов      		= Новый Массив;
	МассивТипов.Добавить(Тип("ТаблицаЗначений"));
	
	Если Элементы.Найти("тзФормы") = Неопределено Тогда
		МассивРеквизитов.Добавить(Новый РеквизитФормы("тзФормы", Новый ОписаниеТипов(МассивТипов)));
	КонецЕсли;
	
	Для Каждого Стр Из СтруктураКолонок Цикл 
		МассивТипов.Очистить();
		МассивТипов.Добавить(Стр.Значение);
		МассивРеквизитов.Добавить(Новый РеквизитФормы(Стр.Ключ, Новый ОписаниеТипов(МассивТипов),"тзФормы"));
		Если Элементы.Найти(Стр.Ключ) <> Неопределено Тогда
			МассивУдаляемыхРеквизитов.Добавить("тзФормы." + Стр.Ключ);	
		КонецЕсли;
	КонецЦикла;
	
	Если Элементы.Найти("тзФормы") <> Неопределено Тогда
		ИзменитьРеквизиты(МассивРеквизитов, МассивУдаляемыхРеквизитов);
		Элементы.Удалить(Элементы.Найти("тзФормы"));
		ЭтаФорма.тзФормы.Очистить();
	Иначе 
		ИзменитьРеквизиты(МассивРеквизитов);
	КонецЕсли;
	
	тзФормы = Элементы.Добавить("тзФормы", Тип("ТаблицаФормы"), ЭтаФорма);
	тзФормы.ПутьКДанным = "тзФормы";
	тзФормы.Отображение = ОтображениеТаблицы.Список;			
	
	Для Каждого Стр Из СтруктураКолонок Цикл 
		Поле 		 = Элементы.Добавить(Стр.Ключ, Тип("ПолеФормы"), тзФормы);
		Поле.Вид	 = ВидПоляФормы.ПолеВвода;
		Поле.ПутьКДанным = "тзФормы." + Стр.Ключ;
	КонецЦикла;
	
	МассивРеквизитов.Очистить();
	МассивУдаляемыхРеквизитов.Очистить();
	МассивТипов.Очистить();
	
КонецПроцедуры
&Насервере
Процедура ПреобразоватьРеквизитФормы()
	
	тз = РеквизитФормыВЗначение(Элементы.тзФормы.Имя);
	//здесь можно у же непосредственно работать с таблицей значений
	тз.Очистить();
	
КонецПроцедуры

 

Скачать файлы

Наименование Файл Версия Размер
ЗагрузкаИзExcel_и_ДинамическийВывод 71
.epf 8,48Kb
03.12.14
71
.epf 8,48Kb Скачать

См. также

Подписаться Добавить вознаграждение
Комментарии
1. Дмитрий Елисеев (w-divin) 04.12.14 10:32
Прикольненько )))
Небольшой нюанс: как сформируется таблица, если в листе Excel такие колонки:
1я колонка, 2я Колонка, 3я колонка
ну, или например так:
1код, код, код2
или так:
Цена товара, ценатовара

думаю смысл вопроса понятен )))
2. Михаил Гусев (Идальго) 04.12.14 11:22
(1) w-divin, да вроде колонки в 1С будут также называться, как текст из первой строки соответствующей колонки экселя. (правда я не проверял, а так из кода предположил)
3. Дмитрий Елисеев (w-divin) 04.12.14 11:39
(2) Идальго, невнимательно смотрел код и читал комментарии )))
&НаКлиенте Функция ПолучитьИмяКолонки(ИмяКолонкиПредставление)
//имена колонок могут начинаться только с букв, а также исключим все специальные символы, которые не могут присутствовать в именах колонок
4. Дмитрий Елисеев (w-divin) 04.12.14 11:56
Менеджеры - народ своеобразный. У них свои понятия об именовании колонок. В связи с этим я бы первой строчкой в функции ПолучитьИмяКолонки добавил
ИмяКолонкиПредставление  СтрЗаменить(ИмяКолонкиПредставление, " ", "_")

для исключения влияния пробелов на имена колонок

Ну и добавил бы процедуру
Процедура ДобавитьКолонку(СтруктураКолонок, Знач ИмяКолонки, Счетчик=0)
   Если СтруктураКолонок.Свойство(ИмяКолонки) Тогда
      ИмяКолонки = ИмяКолонки+"_"+Строка(Счетчик);
      Счетчик=Счетчик+1;
      ДобавитьКолонку(СтруктураКолонок, ИмяКолонки, Счетчик);
   Иначе
      СтруктураКолонок.Вставить(ИмяКолонки);
   КонецЕсли;
КонецПроцедуры
...Показать Скрыть


как-то так )))
И сохранял бы их не в структуре, а в массиве по порядку, чтобы при заполнении строк для каждой ячейки в каждой строке не вычислять имя колонки )))
5. Алексей (kaging) 04.12.14 12:25
(1) w-divin, Согласен что есть косяк, с наименованием колонок, но решается просто, меняем вот это :
 //получим имена всех колонок и их типы
        Для Column = 1 По ColumnCount Цикл
            ИмяКолонки     = ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
            СтруктураКолонок.Вставить(ИмяКолонки, ТипЗнч(Лист.Cells(2,Column).Value));
        КонецЦикла;
...Показать Скрыть


на это
Для Column = 1 По ColumnCount Цикл
			ИмяКолонки 	= ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
			НумераторКолонок = "_" + СтрЗаменить(СокрЛП(Column), Символы.НПП, "") + "я";
			СтруктураКолонок.Вставить(ИмяКолонки + НумераторКолонок, ТипЗнч(Лист.Cells(2,Column).Value));
		КонецЦикла;
...Показать Скрыть


(4) ну и по поводу того чтобы не получать постоянно имена колонок можно сделать так, вот это:
 Для Row = 2 По RowCount Цикл
            СтруктураКолонок.Очистить();


            //получим имена всех колонок и значение в данной строке Excel
            Для Column = 1 По ColumnCount Цикл
                ИмяКолонки     = ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
                СтруктураКолонок.Вставить(ИмяКолонки, Лист.Cells(Row,Column).Value);
            КонецЦикла;


...Показать Скрыть

заменить на это :
Для Row = 2 По RowCount Цикл
//получим имена всех колонок и значение в данной строке Excel
Для Каждого Стр Из СтруктураКолонок Цикл
Подчеркивание = Найти(Стр.Ключ, "_");
НомерКолонкиСтр = Сред(Стр.Ключ, Подчеркивание + 1, СтрДлина(Стр.Ключ) - Подчеркивание - 1);
Номерколонки = Число(НомерКолонкиСтр);
СтруктураКолонок.Вставить(Стр.Ключ, Лист.Cells(Row,НомерКолонки).Value);
КонецЦикла;
6. Алексей (kaging) 04.12.14 12:30
заменить на это :
Для Row = 2 По RowCount Цикл 
//получим имена всех колонок и значение в данной строке Excel 
Для Каждого Стр Из СтруктураКолонок Цикл 
	Подчеркивание = Найти(Стр.Ключ, "_"); 
НомерКолонкиСтр = Сред(Стр.Ключ, Подчеркивание + 1, СтрДлина(Стр.Ключ) - Подчеркивание - 1); 
Номерколонки = Число(НомерКолонкиСтр); 
СтруктураКолонок.Вставить(Стр.Ключ, Лист.Cells(Row,НомерКолонки).Value); 
КонецЦикла;
...Показать Скрыть
7. Murad Karimov (k4rimov) 04.12.14 18:09
А .xlsx файлы будет читать? И что делать если неизвестно имя листа и он <> Товары?
Лист = Книга.WorkSheets("Товары");
8. Murad Karimov (k4rimov) 04.12.14 18:33
Как то делали такую реализацию чтения:
Функция ЗагрузитьДанныеИзФайла(ИмяФайла)
	
	Файл = Новый Файл(ИмяФайла);
	
	Если НЕ Файл.Существует() Тогда
		ВызватьИсключение("Файл не существует!");
	КонецЕсли;
	
	dbCon = Новый COMОбъект("ADODB.Connection");
	dbCon.Provider = "Microsoft.Jet.OLEDB.4.0";
	
	Если НРег(Файл.Расширение) = ".xls" Тогда
		dbCon.Properties("Data Source").Value = Файл.ПолноеИмя;
		dbCon.Properties("Extended Properties").Value = "Excel 8.0;HDR=Yes;IMEX=1";
	ИначеЕсли НРег(Файл.Расширение) = ".xlsx" Тогда	
		dbCon.Provider = "Microsoft.ACE.OLEDB.12.0";
		dbCon.Properties("Data Source").Value = Файл.ПолноеИмя;
		dbCon.Properties("Extended Properties").Value = "Excel 12.0;HDR=Yes;IMEX=1";
	ИначеЕсли НРег(Файл.Расширение) = ".csv" Тогда
		dbCon.Properties("Data Source").Value = Файл.Путь;
		dbCon.Properties("Extended Properties").Value = "text;HDR=Yes;FMT=Delimited";
	Иначе
		ВызватьИсключение("Тип файла """+Файл.Расширение+""" не поддерживается!");
	КонецЕсли;
	
	Попытка 
		dbCon.Open();
	Исключение
		ТекстОшибки = ИнформацияОбОшибке().Описание;
		dbCon = Неопределено;
		ВызватьИсключение("Не удалось открыть файл "+Файл.Имя+" : "+ТекстОшибки);
	КонецПопытки;
	
	//определим имя таблицы
	Если НРег(Файл.Расширение) = ".xls" ИЛИ НРег(Файл.Расширение) = ".xlsx" Тогда
		//получим имя первого листа файла Excel, остальные листы игнорируем
		rst = Новый COMОбъект("ADODB.Recordset");
		rst = dbCon.OpenSchema(20);
		Если rst.EOF Тогда
			rst.Close();
			ВызватьИсключение("Файл не содержит листов!");
		КонецЕсли;
		ИмяТаблицы = rst.Fields("TABLE_NAME").Value; 
		rst.Close();
		rst = Неопределено;
	ИначеЕсли НРег(Файл.Расширение) = ".csv" Тогда
		ИмяТаблицы = Файл.Имя;
	КонецЕсли;
	
	//прочитаем данные из файла
	rs = Новый COMОбъект("ADODB.Recordset");
	rs.CursorType = 0;
	rs.LockType = 1;
	
	Попытка 
		rs.Open("SEL ECT * FR OM ["+ИмяТаблицы+"]",dbCon);
	Исключение
		ТекстОшибки = ИнформацияОбОшибке().Описание;
		rs = Неопределено;
		dbCon.Close();
		dbCon = Неопределено;
		ВызватьИсключение("Не удалось прочитать данные из файла "+Файл.Имя+", таблица "+ИмяТаблицы+" : "+ТекстОшибки);
	КонецПопытки;
	
//получим имена колонок	
ТабФакта = Новый ТаблицаЗначений;
	к = 0;
	Для Каждого Field Из rs.Fields Цикл
		ТабФакта.Колонки.Добавить("Колонка"+к, , Field.Name);
		к = к + 1;
	КонецЦикла;
	
	Если НЕ (rs.EOF и rs.BOF) Тогда
		rs.MoveFirst();
	КонецЕсли;
	
	Пока НЕ rs.EOF Цикл
		//принудительный стоп по маркеру.. ))
                Если СокрЛП(rs.Fields(0).Value) = "END" Тогда
			Прервать;
		КонецЕсли;
		СтрокаТаб = ТабФакта.Добавить();
		к = 0;
		Для Каждого Field Из rs.Fields Цикл
			СтрокаТаб[к] = rs.Fields(Field.Name).Value;
			к = к + 1;
		КонецЦикла;
		//Для Каждого Колонка Из ТабФакта.Колонки Цикл
		//	СтрокаТаб[Колонка.Имя] = rs.Fields(Колонка.Заголовок).Value;
		//КонецЦикла;
		rs.MoveNext();
	КонецЦикла;
	
	rs.Close();
	rs = Неопределено;
	dbCon.Close();
	dbCon = Неопределено;
	
	Возврат(ТабФакта);
	
КонецФункции
...Показать Скрыть
9. Алексей (kaging) 05.12.14 09:52
(7)
Лист = Книга.WorkSheets("Товары");

Это был просто пример. Ну если это очень критично, то меняем это на
СписокЛистов 		= Новый СписокЗначений;
		Для List = 1 по Книга.WorkSheets().Count Цикл
			СписокЛистов.Добавить(Книга.Worksheets(List).Name);
		КонецЦикла;
		ЭлементСписка = ВыбратьИзМеню(СписокЛистов);
		Если ЭлементСписка = Неопределено Тогда 
			Excel.WorkBooks.Close();
			Excel 	= Неопределено;		
			Возврат;
		КонецЕсли;
		Лист 				= Книга.WorkSheets(ЭлементСписка.Значение);
...Показать Скрыть


(8)Ваша реализация естественно имеет место быть, и даже скорее всего это будет работать быстрее, но цель была не просто показать как прочитать Excel, но и динамический вывод таблицы на форму.
10. Марина Семёнова (SemenovaMarinaV) 05.12.14 11:26
11. Павел Алексеенко (qwinter) 10.12.14 19:58
На мой личный взгляд построителем получить данные гораздо проще и надежней и мне кажется даже быстрее, хотя по скорости надо протестировать, как будет в реальности.
12. Anatoliy (NOVOPRO) 11.12.14 07:32
А поможет ли эта процедура устранить неполадки с названиями столбцов...

Для Row = 2 По RowCount Цикл
//получим имена всех колонок и значение в данной строке Excel
Для Каждого Стр Из СтруктураКолонок Цикл
Подчеркивание = Найти(Стр.Ключ, "_");
НомерКолонкиСтр = Сред(Стр.Ключ, Подчеркивание + 1, СтрДлина(Стр.Ключ) - Подчеркивание - 1);
Номерколонки = Число(НомерКолонкиСтр);
СтруктураКолонок.Вставить(Стр.Ключ, Лист.Cells(Row,НомерКолонки).Value);
КонецЦикла;
13. Алексей (kaging) 11.12.14 09:44
(12) Проблема с наименованием столбцов устраняется вот здесь
Для Column = 1 По ColumnCount Цикл
            ИмяКолонки     = ПолучитьИмяКолонки(СокрЛП(Лист.Cells(1,Column).Text));
            НумераторКолонок = "_" + СтрЗаменить(СокрЛП(Column), Символы.НПП, "") + "я";
            СтруктураКолонок.Вставить(ИмяКолонки + НумераторКолонок, ТипЗнч(Лист.Cells(2,Column).Value));
        КонецЦикла;
...Показать Скрыть


в (5) я об этом писал

А в этом цикле мы вставляем в структуру значение по номеру колонки, для дальнейшего вывода.