Полезные приемы при работе с Excel из 1С (Версия 3.1)

19.11.19

Интеграция - Загрузка и выгрузка в Excel

Программисту 1С часто приходится работать с таблицами Excel из 1С. Я постарался собрать небольшой FAQ и набор функций для работы с файлами Excel. Надеюсь, кому-то будет полезна данная статья.

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

Наименование Файл Версия Размер
РаботаСExcel(Управляемые формы).zip
.zip 14,28Kb
287
.zip 14,28Kb 287 Скачать
Демонстрация работы с Excel через ADODB
.zip 13,44Kb
151
.zip 13,44Kb 151 Скачать
Демонстрация работы с Excel через Табличный документ 1С (УФ)
.zip 13,87Kb
175
.zip 13,87Kb 175 Скачать

Работать с файлами Excel можно тремя способами:

  1. Через OLE  объект;
  2. Через Табличный Документ 1С.
  3. Через ADODB

Разберем первый способ.

1. Подключаемся к  OLE и октрываем файл  Excel:

//Поключимся к Excel через OLE, откроем файл и вернем список страниц
	//ИмяФайла = Полный путь к файлу Excel
	Соотв = Новый Соответствие;
	Попытка 
		Листы = Новый Массив;
		Excel = Новый COMОбъект("Excel.Application");
		Соотв.Вставить("Соединение",Excel);
	    ExcelФайл = Excel.WorkBooks.Open (ИмяФайла);
		Соотв.Вставить("ExcelФайл",ExcelФайл);
		Для Сч = 1 По ExcelФайл.Sheets.Count Цикл
			Листы.Добавить(ExcelФайл.Sheets(Сч));
		КонецЦикла;
		Соотв.Вставить("Листы",Листы);
		лРезультат = Соотв;
	Исключение
		Сообщить("Ошибка создания обьекта Microsoft Excel" + ОписаниеОшибки());
		лРезультат = Неопределено;
	КонецПопытки;
	
	Возврат лРезультат;

 

Отсоединяемся от Excel и закрываем файл:

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

Процедура  ОтключатьсяОтExcel(Соответстие)
	Попытка
		Соответстие["Соединение"].DisplayAlerts = 0;
		Соответстие["ExcelФайл"].Close();
		Соответстие["Соединение"].DisplayAlerts = 1;
		Соответстие["Соединение"].Quit(); 
		Соответстие["Соединение"] = Неопределено;		
	Исключение
		Сообщить("Не удалось отключиться от Excel - "+ОписаниеОшибки());
	КонецПопытки;
КонецПроцедуры

Клиент-Серверный вариант в управляемых формах.

При работе в клиент-серверном варианте основная трудность заключается в том, что обычно файл мы загружаем на стороне клиента, а данные, как правило, обрабатываем на стороне сервера. Причем, таблицу значений нельзя передать от клиента на сервер (точнее, передать таблицу значений можно, но только в толстом клиенте. На тонком - 1С выдаст вам сообщение об ошибке). Есть два пути решения данной задачи:

1. Считываем данные из файла в массив из структур и затем этот массив передаем на сервер для дальнейших манипуляций с данными.

&НаКлиенте
Процедура Загрузить1(Команда)
	Если Объект.ИмяФайла = "" Тогда
		Сообщение = Новый СообщениеПользователю;
		Сообщение.Текст = "Не указан файл для загрузки";
		Сообщение.Поле = "Объект.ИмяФайла";
		Сообщение.Сообщить();
		Возврат;
	КонецЕсли;
	
	Соединение = ПоключитьсяКExcel(Объект.ИмяФайла);
	Если Соединение = Неопределено Тогда
		Возврат;
	КонецЕсли;
	
	Лист = Соединение["Листы"][0];
	//Создаем Массив для строк
	МассивДанных = Новый Массив;
		
	ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
	ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;
	
	Для Сч = 1 по ВсегоСтрок Цикл
		
		//Создаем структуру для текущей строки
		Строка = Новый Структура;
		Для Сч2 = 1 по ВсегоКолонок Цикл
			 Строка.Вставить("Колонка"+Строка(Сч2),Лист.Cells(Сч,Сч2).Value);
		КонецЦикла;
		
		МассивДанных.Добавить(Строка);
	КонецЦикла;
	
	Загрузить1НаСервере(МассивДанных);
КонецПроцедуры

&НаСервере
Процедура Загрузить1НаСервере(МассивДанных)
	//Создадим колонки
	Если МассивДанных.Количество() = 0 Тогда
		Возврат;
	КонецЕсли;
	
	Таб = РеквизитФормыВЗначение("Результат");
	
	МассивРеквизитов = Новый Массив;	
	
	Для Каждого Кл Из МассивДанных[0] Цикл
		Мас = Новый Массив;
		Мас.Добавить(ТипЗнч(Кл.Значение));
		
		Таб.Колонки.Добавить(Кл.Ключ,Новый ОписаниеТипов(Мас),Кл.Значение);
	КонецЦикла;
	
	Первая = Истина;
	Для Каждого Ст ИЗ МассивДанных Цикл
		Если Первая Тогда
			Первая = Ложь;
			Продолжить;
		КонецЕсли;
		ТБ = Таб.Добавить();
		Для Каждого Кл из СТ Цикл
			ТБ[Кл.Ключ] = Кл.Значение;
		КонецЦикла;
		
	КонецЦикла;
	
	ОтобразитьТабНаФорме(Таб);
	
КонецПроцедуры

2-ой способ - это передать файл Excel на сервер через хранилище данных и далее работать с ним уже на сервере.

&НаСервере
Процедура Загрузить2НаСервере(пФайл)
	лФайл = ПолучитьИмяВременногоФайла("xlsx");
	лДвоичДанные = ПолучитьИзВременногоХранилища(пФайл);
	лДвоичДанные.Записать(лФайл);
	
	Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
	Если Соединение = Неопределено Тогда
		Возврат;
	КонецЕсли;
	/// Обработка Excel
	ОтключатьсяОтExcelСервер(Соединение);	
КонецПроцедуры

&НаКлиенте
Процедура Загрузить2(Команда)
	лДвоичДанные = Новый ДвоичныеДанные(Объект.ИмяФайла);
	лФайл = ПоместитьВоВременноеХранилище(лДвоичДанные);	
	Загрузить2НаСервере(лФайл);
КонецПроцедуры

Очень часто приходится работать с очень большими  файлами Excel, и его обработка путем перебора строк занимает огромное количество времени. 

В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с  массивом.

Лист = Соединение["Листы"][0];
	
	ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
	ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

	Область = Лист.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
	Данные = Область.Value.Выгрузить();

На выходе получаем двумерный массив, который содержит все данные указанного листа Excel

Полезные функции при работе с Excel:

 Устанавливает видимость Excel при работе
//0 - Excel не виден, 1 - виден.
 Соединение.Visible = Видимость;
 Добавление новой книги в файл Excel
Книга = Соединение.WorkBooks.Add();
 Сохранение книги Excel
Книга.SaveAs(ИмяФайла);
 Добавление нового листа к книге
Лист = Книга.WorkSheets.Add();
 Переименование листа
Лист.Name = ИмяЛиста;
 Изменение маштаба листа
//"Масштаб" (от 10 до 400).
Лист.PageSetup.Zoom = Масштаб;
 Изменение ориентации листа
//1 - книжная, 2 - альбомная.
Лист.PageSetup.Orientation = Ориентация;
 Отступы листа
//Левый отступ
Лист.PageSetup.LeftMargin = Соединение.CentimetersToPoints(Сантиметры);
//Верхний отступ
Лист.PageSetup.TopMargin = Соединение.CentimetersToPoints(Сантиметры);
//Правый отступ
Лист.PageSetup.RightMargin = Соединение.CentimetersToPoints(Сантиметры);
//Нижний отступ
Лист.PageSetup.BottomMargin = Соединение.CentimetersToPoints(Сантиметры);
Обращение к ячейки
чтение/запись 
//Прочитать значение ячейки
//Сч = Номер строки
//Сч2 = Номер колонки
Данные = Лист.Cells(Сч,Сч2).Value

//Записать значение в ячейку
Лист.Cells(Сч,Сч2).Value =  Данные
Обращение к области ячеек
//В качестве параметров передаем ячейки по диогонали
Лист.Range(ЛистЭксель.Cells(1,1),ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок))
Очень часто при чтении или записи значений в Excel ставятся лишние пробелы в числе, например, вместо 1502 он читает как 1 502 и в дальнейшем это значение не приводится к числу. Эту проблему можно решить заменой.
ЗначениеЯчейки = Лист.Cells(1,3).Value;
СтрЗаменить(Строка(ЗначениеЯчейки),Символы.НПП,"");
Объединение ячеек
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Merge();	

 
Работа с именованными ячейками в Excel
Обл = Лист.Range("Имя_Ячейки_В_Excel").Select();	
Обл.Value = "Присваиваем значение";

 
Удаление ячейки 
Лист.Cells(1,3).Delete();
Удаление области ячеек
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Delete();
 Обращение к строке
//Сч = Номер строки
Лист.Rows(Сч)
 Изменение ширины колонки
Лист.Columns(НомерКолонки).ColumnWidth = Ширина;
 Обращение к колонке
//Сч = Номер Колонки
Лист.Cols(Сч)
 Удаление Строки
Лист.Rows(Сч).Delete()
 Фон ячейки / области / Строки /  
//Фон Ячейки
Лист.Cells(1,1).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);

//Фон Области
Лист.Range(Лист.Cells(1,1),Лист.Cells(10,1)).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);

//Фон строки
Лист.Rows(Сч).Interior.Color = ПолучитьЦветExcelRGB(10,10,10);

 
 Функция переводит цвет из формата RGB в формат Excel
Функция ПолучитьЦветExcelRGB(R,G,B)
	Возврат ((B*256) + G) * 256 + R;
КонецФункции
 Управление шрифтом в ячейки/строке/области
//Изменение шрифта
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта;

//Изменение размера шрифтв
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта;

//Управление жирностью шрифта
//1-жирный шрифт (bold)
//0-нормальный шрифт (normal)
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный;

//Управление курсивом шрифта
//1-Курсив
//0-Нормальный
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив;

//2 - Подчеркнутый шрифт
//1- нет
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый;
 Разрешает переносить по словам в ячейке
//1-Переносить
Лист.Cells(1, 1).WrapText = 1;
 Управление рамкой ячейки
//1 - Тонкая сплошная линия
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии;
 Устанавливаем формат ячейки
//"@" - текстовый
//"0.00" - числовой
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;
 Формула в ячейки 
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
 Формула в ячейки 
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
 Формула в ячейки 
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
 Формула в ячейки 
Лист.Cells(Сч,Сч2).FormulaLocal = "=ОКРУГЛ(135,46456;0)";
 Функция для получения ширины колонки Excel 
Спасибо пользователю goodwill
&НаКлиенте
Функция ПолучитьШиринуКолнкиЭксель(ПараметрШиринаВПикселях)
    
    Если ПараметрШиринаВПикселях > 9 Тогда 
        ШиринаВСимволах = (ПараметрШиринаВПикселях/0.75-5)/7;
    Иначе
        ШиринаВСимволах = ПараметрШиринаВПикселях/9;
    КонецЕсли;

    Возврат ШиринаВСимволах;
    
КонецФункции

Лист.Columns("A").ColumnWidth = ПолучитьШиринуКолнкиЭксель(РазмерШиринаВПикселях);
 Разрешить перенос слов в ячейке  
Спасибо пользователю roofless
Лист.Cells(1, 1).WrapText = 1;
 Группировки данных на листе  
Спасибо пользователю dr-wit, ignor
1. Развернуть все группы (строки и колонки):
Excel.ActiveSheet.Outline.ShowLevels(3, 3);
2. Сернуть все группы (строки и колонки) до первого уровня:
Excel.ActiveSheet.Outline.ShowLevels(1, 1); 
3. Вернуть глубину дерева
Лист.Rows(Инд).OutlineLevel 

 

При работе с Excel мы оперируем столбцами как числом (Например, 1 столбец), а у Excel адресация столбцов производится с помощью символов. И когда нам нужно отредактировать формулу, то нам нужно номер столбца преобразовать в символ. В таких случаях вам пригодится эта функция.

Функция ПреобразоватьНомерСтолбцаВФорматExcel(Столбец)
	стСтолбец = "";
	А = Окр(Столбец/27,0);
	В = Столбец - (А*26);
	Если А>0 Тогда
		стСтолбец = Символ(А+64);
	КонецЕсли;
	Если В>0 Тогда
		стСтолбец = стСтолбец + Символ(В+64);
	КонецЕсли;
	
	Возврат стСтолбец;
КонецФункции

2. Работа с Excel через ТабличныйДокумент 1С

С помощью данного метода можно и загружать из Excel и выгружать в Excel. Но на мой взгляд этот метод идепально подходит когда вам необходимо посто сохранить информацию в Excel без дальнейшей манипуляции.

Итак, приступим: загрузка из Excel:

1. Загружаем файл Excel в табличный документ

//Файл - это файл Excel
	ТабДок = Новый ТабличныйДокумент;
	Попытка
		ТабДок.Прочитать(Файл, СпособЧтенияЗначенийТабличногоДокумента.Значение);
	Исключение
		Сообщение = Новый СообщениеПользователю;
		Сообщение.Текст = ОписаниеОшибки();
		Сообщение.Сообщить();
		Возврат Неопределено;
	КонецПопытки;

 2. Производим манипуляции уже с ТабличнымДокументом

//Создадим ТЗ куда будем собирать инфу
	Таб = Новый ТаблицаЗначений;
	Таб.Колонки.Добавить("Номенклатура",Новый ОписаниеТипов("Строка"));
	Таб.Колонки.Добавить("Количество",Новый ОписаниеТипов("Число"));
	Таб.Колонки.Добавить("Цена",Новый ОписаниеТипов("Число"));
	
//Определяем количество строк
	КолСтр = ТабДок.ВысотаТаблицы;
	Для Сч = 2 по КолСтр Цикл
		Попытка
			ТБ = Таб.Добавить();
//Обращаемся к ячейки и забираем данные
			ТБ.Номенклатура = Строка(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 1).ТекущаяОбласть.Текст);		
			ТБ.Количество = Число(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 2).ТекущаяОбласть.Текст);				
			ТБ.Цена = Число(ТабДок.ПолучитьОбласть("R" + Формат(Сч, "ЧГ=0") + "C" + 3).ТекущаяОбласть.Текст);		
		Исключение
			Сообщение = Новый СообщениеПользователю;
			Сообщение.Текст = "Не удалось загрузить строку "+Строка(Сч);
			Сообщение.Сообщить();
		КонецПопытки;
	КонецЦикла;

Давайте теперь разберем сохранение в Excel с помощью данного метода:

Тут все очень просто сначала мы формируем обычный Табличный документ и затем записываем его в Excel

//ТабДок - Табличный документ
	ТабДок.Записать(Объект.ИмяФайла,ТипФайлаТабличногоДокумента.XLSX);

2. Работа с Excel ADODB

Выражаю особую благодарность коллеги Fragster за хороший комментарий

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

  1. Не требует установки самой Excel, необходима лишь установить ODBC.  Но как правило  он уже установлен. Это особенность позволяет работать на стороне сервера без дополнительных установок Excel.
  2. Позволяет работать с таблицой Excel как с БД и строить к ней запросы на T-SQL. Таким образом мы можем делать отборы еще на этапе чтения данных и другие преимущества что дает  Т-SQL. Что на мой взгляд огромный плюс.

Подключение к ADO

Функция СоединитьсяСADO(ИмяФайла)
	//Поз = СтрНайти(ИмяФайла,"\",НаправлениеПоиска.СКонца);
	Поз = 0;
	Найден = 1;
	ТмпСтр = ИмяФайла;
	Пока Найден <> 0 Цикл
		Найден = Найти(ТмпСтр,"\");
		ТмпСтр = Прав(ТмпСтр,СтрДлина(ТмпСтр) - Найден);
		Если Найден <> 0 Тогда
			Поз = Найден;
		КонецЕсли;
	КонецЦикла;
	Путь = Лев(ИмяФайла,Поз-1);                 	
	
	Данные = Новый Структура;
	СтрокаСоединения = "
	| Provider=Microsoft.ACE.OLEDB.12.0;Data Source="+СокрЛП(ИмяФайла)+";
	| Extended Properties=""Excel 12.0 Xml;HDR=YES""";
	Соединение = Новый COMОбъект("ADODB.Connection");	
	Попытка		
		Соединение.Open(СтрокаСоединения);           		
	Исключение		
		Сообщить ("Не возможно подключится к Microsoft Excel Driver!!!		
		|Возможно файл ["+ИмяФайла+"] открыт другим пользователем.");		
		Возврат Неопределено;
	КонецПопытки;
	Данные.Вставить("Соединение",Соединение);
		
	Возврат Данные;
КонецФункции

Строка подключения зависит от версии ODBC. И вызывает наибольшие трудности при подключение поэтомя я рекомендую ее сгенерировать на сайте http://www.connectionstrings.com

Отключение от ADO

Процедура ОтключитьсяОтADO(Подключение)
	Попытка
		Подключение["Соединение"].Close();
		Подключение = Неопределено;
	Исключение
	КонецПопытки;
КонецПроцедуры


Выполнение запроса

Функция ВыполнитьЗапросADO(ТекстЗапроса,Подключение)
	Попытка		
		Записи = Новый COMОбъект("ADODB.Recordset");           		
	Исключение		
		Сообщить ("Не удалось Создать к Microsoft Excel Driver!!!");		
		Возврат Неопределено;
	КонецПопытки;
    Попытка
        Записи.Open(ТекстЗапроса, Подключение["Соединение"]);
		Возврат Записи;
	Исключение
	    Сообщить ("Проблемы с выполнением запроса");
	КонецПопытки;	
КонецФункции

Пример запроса:

//Сразу отбираем только не пустые номенклатуры
ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE `Номенклатура`<>""""";

Запись в  Excel тоже производится в виде запроса:

ТекстЗапроса = "    
		|INSERT INTO [Лист1$] (`Номенклатура`,`Количество`,`Цена`,`Сумма`) VALUES ('"
	+ТБ.Номенклатура+"','"+ТБ.Количество+"','"+ТБ.Цена+"','"+ТБ.Сумма+"')";

Хочу отметить что наименование полей производится по первой строке в таблице

 

ADODB предоставляет ряд объектов, с которыми мы работаем

ADODB.Connection
предназначен для соединения с ADO
Соединение = Новый COMОбъект("ADODB.Connection");

 

  ADOX.Table
для работы с таблицей
// Создаем таблицу и добавляем в неё два столбца
Table = Новый COMОбъект("ADOX.Table");
Table.Name = "Table";
Table.Columns.Append("Code");
Table.Columns.Append("Description");

 

ADODB.Command Для выполнения комманд на языке T-SQL
Комманды = Новый COMОбъект("ADODB.Command");
	Комманды.ActiveConnection = Подключение["Соединение"];
	Комманды.CommandType = 1;
	Комманды.CommandText = ТекстЗапроса;
	Комманды.Execute();

 

 ADODB.Recordset

Похож на ADODB.Command предназначен для выполнения запросов и обработки результата 

Записи = Новый COMОбъект("ADODB.Recordset"); 
Записи.Open(ТекстЗапроса, Подключение["Соединение"]);

 

 

В файле продемонстрированны оба варианта работы с запросами.

 

На этом пока все. По возможности буду дополнять статью :)

 

В архиве находится обработка, которая демонстрирует все описанные и другие возможности при работе с Excel.

И тренировочный файл Excel.

Excel работа с

См. также

Загрузка номенклатуры из Excel в УТ11, КА 2, ERP 2, Розница 2. Дополнительные реквизиты и сведения, характеристики, картинки, цены, остатки

Загрузка и выгрузка в Excel Розничная торговля Логистика, склад и ТМЦ Ценообразование, анализ цен Прайсы Платформа 1С v8.3 1С:Комплексная автоматизация 1.х 1С:Розница 2 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Загрузка из файлов xls, xlsx, ods, csv, mxl в УТ11, КА 2, ERP 2, Розница 2. Задействованы все возможности конфигурации - заполнение реквизитов номенклатуры, дополнительных реквизитов и сведений, характеристики, доп.реквизиты и сведения характеристик. Дополнительные обработки для расширения возможностей.

10560 руб.

29.10.2014    209834    620    524    

438

Загрузка номенклатуры c картинками (несколько потоков одновременно) и сопутствующими данными в базу и любые документы из yml, xls, xlsx, xlsm, ods, ots, csv для УТ 10.3, УТ 11 (все), БП 3, КА 2, ERP 2, УНФ 1.6/3.0, Розница 2

Загрузка и выгрузка в Excel Логистика, склад и ТМЦ Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Платформа 1С v8.3 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Платные (руб)

Эволюция не стоит на месте - новая удобная версия функциональной обработки для Вашего бизнеса! Что же Вы получаете? Удобный и интуитивно понятный интерфейс с 3-мя этапами работы. 2 режима - автоматический и ручной. Чтение XLSX, XLSM, CSV, XML/YML форматов без офиса, на любом сервере! Визуальное связывание колонок файла и реквизитов простым перетаскиванием колонок. Создание или обновление номенклатуры с иерархией, характеристик, доп. реквизитов, упаковок, загрузка практически неограниченного количества картинок на одну номенклатуру (с возможностью загрузки в несколько потоков одновременно), с хранением в томах или в базе. Загрузка номенклатуры поставщиков или поиск по их данным номенклатуры. Загрузка доп. реквизитов в характеристики. Загрузка штрихкодов с генерацией новых. Создание элементов справочников и ПВХ "на лету" для выбранных реквизитов. (Обновление от 11.12.2023, версия 9.5 - 9.9)

13200 руб.

20.11.2015    150390    365    375    

499

Распознавание и загрузка сканов в 1С "одним нажатием": УПД, ТОРГ-12, накладные, счета, номенклатура, заказы и т.д.

Загрузка и выгрузка в Excel Документооборот и делопроизводство (СЭД) Учет документов Распознавание документов и образов Управляемые формы 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление холдингом 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная программа для распознавания ЛЮБЫХ СКАНОВ ИЛИ ФОТО ТОВАРНЫХ ДОКУМЕНТОВ в 1С. Не требует указания параметров и предварительной настройки. Просто выбираете файл (PDF, JPG, DOC, XLS, HTML и т.д.) выбираете документ 1С и нажимаете кнопку "Распознать и загрузить".

5400 руб.

04.06.2019    101141    296    173    

312

Загрузка данных отчета о реализации товаров из Excel файла СберМегаМаркет

Загрузка и выгрузка в Excel Маркетплейсы Платформа 1С v8.3 1С:Бухгалтерия 3.0 1С:Управление торговлей 11 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Загрузка данных отчета о реализации товаров из сервиса СберМегаМаркет для конфигурации: Бухгалтерия предприятия, редакция 3.0; Управление торговлей, редакция 11 и Управление нашей фирмой, редакция 3.0 в документ "Отчет комиссионера (агента) о продажах".

3600 руб.

07.06.2022    14956    78    0    

59

Загрузка документов и номенклатуры из Excel в 1С "одним нажатием": УПД, ТОРГ-12, отчеты маркетплейсов, заказы, счета, прайсы

Загрузка и выгрузка в Excel Оптовая торговля Платформа 1С v8.3 Управляемые формы Платформа 1C v8.2 Конфигурации 1cv8 1С:Комплексная автоматизация 1.х 1С:Бухгалтерия 2.0 1С:Управление торговлей 10 1С:Розница 2 1С:Управление производственным предприятием 1С:Управление нашей фирмой 1.6 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х 1С:Управление нашей фирмой 3.0 Бухгалтерский учет Управленческий учет Платные (руб)

Универсальная обработка для загрузки документов из Excel в 1С. Не требует указания параметров (номера колонок, номер первой строки таблицы и т.д.) и предварительной настройки. Просто выбираете файл Excel, документ 1С и нажимаете кнопку "Загрузить". Обработка сама находит таблицу в файле Excel, необходимые для загрузки данные в ней (номенклатура, количество, НДС, цена, сумма) и загружает ее в 1С. Вместе с номенклатурой может найти контрагента, номер и дату документа, штрих-коды, серии ГТД, страну и т.д. Распознает документы ЛЮБОЙ ФОРМЫ (УПД, ТОРГ-12, заказ, отчет комиссионера и т.д.). Не требует MS Office. Для поиска таблиц используются методы эвристического поиска. Загружает только то, что нужно, т.е. пропускает повторы шапки таблицы, заголовки, промежуточные итоги, подписи и т.д. Содержит модуль работы с электронной почтой и api-загрузчик отчетов о продажах маркетплейсов.

5000 руб.

09.11.2016    214619    921    886    

938

Универсальная загрузка остатков и цен поставщиков в 1С (УТ 11, КА 2, ERP 2.0) из файлов Excel, CSV, XML (YML и CML)

Прайсы Оптовая торговля Розничная торговля Ценообразование, анализ цен Файловый обмен (TXT, XML, DBF), FTP Загрузка и выгрузка в Excel Платформа 1С v8.3 1С:ERP Управление предприятием 2 1С:Управление торговлей 11 1С:Комплексная автоматизация 2.х Управленческий учет Платные (руб)

Обработка позволяет легко настроить загрузку прайс-листов и остатков поставщиков. Форматы файлов задаются для каждого поставщика отдельно. В настройках предусмотрена возможность гибкой обработки входящих данных (убрать лишние символы, пересчитать цены в другую валюту, сделать произвольную наценку и т.д.).

12000 руб.

23.01.2017    58798    59    48    

67
Вознаграждение за ответ
Показать полностью
Комментарии
В избранное Подписаться на ответы Сортировка: Древо развёрнутое
Свернуть все
1. Krasnyj 1282 23.09.15 09:13 Сейчас в теме
Дельная подборка. Полезная. Спасибо.
vkozak; Gendelf; Spakus; Мах; sergio199; +5 Ответить
2. Fragster 1137 23.09.15 10:24 Сейчас в теме
Забыто про UsedRange, а также про то, что передача через ComSafeArray работает в обе стороны.
SCHNEIDER GROUP; sergpogo; kovgard; psih12; +4 Ответить
3. Fragster 1137 23.09.15 10:32 Сейчас в теме
Ну и про вариант через ADO с плюшками SQL запросов типа фильтрации, группировок и джоинов данных с разных листов
KostyaLavr; _also; Sib4p; Йожкин Кот; +4 Ответить
9. Sib4p 697 30.09.15 10:05 Сейчас в теме
(3) Fragster, Добавил небольшое описание ADO
Fragster; +1 Ответить
4. Sib4p 697 23.09.15 11:58 Сейчас в теме
Спасибо за очень полезные комментарии. В ближайшее время добавлю по работе с Excel через Табличный документ и потом постараюсь осветить ваши темы.
5. roofless 23 23.09.15 15:48 Сейчас в теме
отлично, добавьте
Лист.Cells(1, 1).WrapText = 1;

разрешает переносить по словам в ячейке
arakelyan; +1 Ответить
6. Sib4p 697 23.09.15 16:52 Сейчас в теме
(5) roofless, Спасибо большое! Добавил
7. spy-83 198 24.09.15 06:10 Сейчас в теме
помоему конструкция
Excel = Новый COMОбъект("Excel.Application");

на сервере не взлетит. не?
8. roofless 23 24.09.15 07:51 Сейчас в теме
(7) spy-83, если апликейшен будет на сервере, то взлетит
гаврюша; Sib4p; +2 Ответить
24. spy-83 198 15.03.16 10:40 Сейчас в теме
(8) roofless, а если сервер на линуксе?
27. Sib4p 697 16.03.16 20:18 Сейчас в теме
(24) spy-83, На линуксе я не пробовал, но он там где то же временные файлы хранит
57. dobrynin.i.s 92 05.04.19 07:41 Сейчас в теме
(24) на линуксе не взлетит , так как нет ком объектов там
10. malets 2 30.09.15 12:07 Сейчас в теме
Спасибо большое, очень полезная статья!
11. volit 01.10.15 13:57 Сейчас в теме
Спасибо все очень понятно и подробно!
62. kotov2000 5 25.11.19 09:13 Сейчас в теме
Коллеги, подскажите.
Раз есть команда Область.Value.Выгрузить(), то, наверное, есть что-то наподобие загрузить в лист Excel?

Очень часто приходится работать с очень большими файлами Excel, и его обработка путем перебора строк занимает огромное количество времени.

В таких случаех удобно в одно действие загрузить всю таблицу в массив и в потом работать уже непосредственно с массивом.

Лист = Соединение["Листы"][0];

ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

Область = Лист.Range(ЛистЭксель.Cells(1,1), ЛистЭксель.Cells(ВсегоСтрок,ВсегоКолонок));
Данные = Область.Value.Выгрузить();
12. rasswet 82 01.10.15 14:58 Сейчас в теме
опыта работы с именованными ячейками у вас нет?
если есть может есть смысл добавить сюда в статью?
13. Sib4p 697 01.10.15 15:13 Сейчас в теме
(12) rasswet, Добавил. Смотрите табличку
14. Shokei 19.11.15 13:25 Сейчас в теме
Спасибо автору ,попробуем
15. skif47 350 19.11.15 13:40 Сейчас в теме
Плюсую, для больших файлов ADO дает просто огромную скорость обработки, только им и пользуюсь.
16. dwarkin 05.02.16 14:47 Сейчас в теме
Очень хорошая статья, сильно помогла, особенно ссылка на connectionstrings.com. Спасибо большое.
Для ADO, если система 64 бит, а 1Ска - 32, то надо ставить Microsoft Access Database Engine 2010 Redistributable (16/12/2010) 32 и 64 - разрядные версии.
В моем случае помогла 32бит версия (система 64бит (Вин 7, Вин2008Ент), 1Ска - 32 бит).
На Вин7 стоял Офис 2010, но видать какой-то резанный. Все равно пришлось так же ставить Access Database Engine 32 бит.
kulak1974; +1 Ответить
17. tormozit 7133 06.02.16 15:20 Сейчас в теме
Для редактирования и отладки запросов к ADO можно использовать консоль запросов из подсистемы Инструменты разработчика на обычных формах 8.2.13+. Нужно выбрать тип запроса ADO, для которого будет доступен конструктор запросов (непохожий на штатный), дерево запроса, контекстная подсказка и 1с-ная подсветка синтаксиса.
AlexiyI; speshuric; TeMochkiN; teflon; +4 Ответить
18. Чародей 17 29.02.16 08:51 Сейчас в теме
Скачал "Демонстрация работы с Excel через Табличный документ 1С" а он тоже только для управляемых форм ((
Может дополните описание?
19. Sib4p 697 29.02.16 11:21 Сейчас в теме
(18) Чародей, Так на алгоритм работы через табличный документ не влияет.
Я учту и дополню описание. Спасибо.
20. igormiro 714 29.02.16 13:39 Сейчас в теме
Спасибо за статью.
Единственное, что я хотел уточнить, для тех кто не знает, где можно скачать ODBC ODBC .
21. babys 90 06.03.16 15:33 Сейчас в теме
Автор:
Вот это что за шняга:
//Сразу отбираем только не пустые номенклатуры
ТекстЗапроса = "SELECT * FROM [Лист1$] WHERE `Номенклатура`<>""""";

Вы когда что-то пишите, пытайтесь думать не как 80-ти летний профессор и член-корр АН.
22. Sib4p 697 09.03.16 22:54 Сейчас в теме
(21) babys, Не пойму суть вашей претензии. Это SQL запрос к Excel он вроде как по Русски не понимает
25. babys 90 15.03.16 12:17 Сейчас в теме
(22) слово "Номенклатура" не смущает, в предложении where? Меня очень. Я привык писать
[F2] <> ""НДФЛ""
26. Sib4p 697 16.03.16 20:17 Сейчас в теме
он берет(25) babys, Он берет название из первой строки. Вариант в поле рабочий. Может конечно не привычный но это просто пример :)
23. Yashazz 4707 12.03.16 20:59 Сейчас в теме
На самом деле обзорчик не полный. С экселем можно работать ещё множеством разных способов. Можно, если современная версия, xml парсить, а можно SafeArray юзать. Ну и так далее.
28. Sirruf 192 27.07.16 17:41 Сейчас в теме
Подскажите, кто знает - как быстро вставить гиперссылки в экселевский файл?
Через COMОбъект("Excel.Application") при большом количестве строк, в которые нужно добавить Hyperlinks очень долго работает...
29. Sirruf 192 27.07.16 17:49 Сейчас в теме
Есть вообще какие-нибудь хорошие библиотеки для работы с файлами в формате xlsx помимо COMОбъект("Excel.Application"), который использовать вроде как не рекомендует Майкрософт?
30. dr-wit 5 30.08.16 08:58 Сейчас в теме
Спасибо, отличная статья!

Еще по группировкам данных на листе, если требуется заполнить подготовленный шаблон листа:
1. Развернуть все группы (строки и колонки):
Excel.ActiveSheet.Outline.ShowLevels(3, 3);

2. Сернуть все группы (строки и колонки) до первого уровня:
Excel.ActiveSheet.Outline.ShowLevels(1, 1);

svetanik; +1 Ответить
31. Rustam1984 12.09.16 22:58 Сейчас в теме
Спасибо,
Взял на вооружение!
32. skyvixen 32 13.09.16 10:33 Сейчас в теме
Коллеги, кто может подсказать, как сохранить в Excel гиперссылку (например, в расшифровке ячейки табличного документа 1C у меня указан WEB-адрес)
33. goodwill 20 29.11.16 17:20 Сейчас в теме
Ширина в Excel задается в символах, что не очень удобно при выводе картинок. Вот функция для перевода ширины в пикселя в ширину в символах.

&НаКлиенте
Функция ПолучитьШиринуКолнкиЭксель(ПараметрШиринаВПикселях)
	
	Если ПараметрШиринаВПикселях > 9 Тогда 
		ШиринаВСимволах = (ПараметрШиринаВПикселях/0.75-5)/7;
	Иначе
		ШиринаВСимволах = ПараметрШиринаВПикселях/9;
	КонецЕсли;

	Возврат ШиринаВСимволах;
	
КонецФункции

Лист.Columns("A").ColumnWidth = ПолучитьШиринуКолнкиЭксель(РазмерШиринаВПикселях);
Показать
69. Greek26rusa 2 03.10.20 23:14 Сейчас в теме
(33)Добрый вечер. А нету куска кода который картику вставляет? в Эксель
34. panovisp 29.11.16 17:25 Сейчас в теме
Спасибо за полезную информацю!
35. Gasilin 45 09.12.16 15:43 Сейчас в теме
Хорошая статья. Но вот пытаюсь на сервере рисовать диаграммы в Excel и при попытке соединения ПодключитьсяКExcelСервер(ИмяВременногоФайла) ругается: ...Open. Произошла исключительная ситуация (MS Excel). К сожалению, нам не удалось найти файл ... . Возможно, он был перемещен, переименован или удален?
Это проблема с отсутствием папки C:\Windows\SysWOW64\config\systemprofile\Desktop?
36. ImHunter 312 09.12.16 17:03 Сейчас в теме
Еще есть способ чтения Excel 2007-выше (*.xlsx) через XML. Ведь это зазипованный XML. В БСП это подсмотрел в обработке ЗагрузкаДанныхИзФайла.
42. su_mai 90 25.03.17 07:49 Сейчас в теме
(36) Структура файлов docx, xlsx и пр., соответствует спецификации Microsoft Open Packaging Convention, сокращенно OPC: https://en.wikipedia.org/wiki/Open_Packaging_Conventions.

По такому же принципу построены файлы комплектов бюджетной бухгалтерской отчетности 1С, для конфигураций БГУ и Свод отчетов (repx).
46. XelOla 17 30.03.17 15:54 Сейчас в теме
(42) Скажите, а можно с Эксель проработать как в статье http://infostart.blog-buh.ru/public/237032/
37. Gasilin 45 26.12.16 15:00 Сейчас в теме
Во втором случае на сервере в строке разве в качестве параметре должно выступать Объект.ИмяФайла?
Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
38. roofless 23 09.01.17 10:42 Сейчас в теме
(37) код этой процедуры тут не приведен, так что вполне может быть и имя файла, если параметры подключения жестко заданы в коде
39. Gasilin 45 09.01.17 14:18 Сейчас в теме
Код процедуры есть в файле публикации.
Даю фрагмент:
&НаСервере
Процедура Загрузить2НаСервере(пФайл)

лФайл = ПолучитьИмяВременногоФайла("xlsx");
лДвоичДанные = ПолучитьИзВременногоХранилища(пФайл);
лДвоичДанные.Записать(лФайл);

Соединение = ПоключитьсяКExcelСервер(Объект.ИмяФайла);
Если Соединение = Неопределено Тогда
Возврат;
КонецЕсли;
47. =Kollega= 3 30.03.17 18:10 Сейчас в теме
(39) Сразу скажу - полный бред. Смотрите вы получаете имя временого файла в лФайл, куда и сохраняется сам файл. А потом пытаетесь открыть файл, который задан в реквизите объекта ИмяФайла. Я так полагаю это файл который пользователь выбрал на КЛИЕНТЕ. Соответственно открывать нужно именно лФайл, который сохранен во временном каталоге сервера.
Да и после окончания работы с ним, неплохо бы почистить за собой - удалить временный файл с сервера.
40. KazanKokos 10 21.03.17 09:36 Сейчас в теме
Народ. А как в Запросе T-SQL вытащить уровень группировки? При строчном переборе я уже научился и пользуюсь, но на больших файлах сами понимаете тормоза. Буду переходить на ODBC и хотелосьбы раскидывать по папкам
41. su_mai 90 25.03.17 07:45 Сейчас в теме
>Подключаемся к OLE и октрываем файл Excel:
Есть более удобный метод получения Com объекта: "ПолучитьCOMОбъект". Из СП: Основное применение функции ПолучитьCOMОбъект - это получение COM-объекта, соответствующего файлу. Доступность: Тонкий клиент, веб-клиент, сервер, толстый клиент, внешнее соединение.
43. BigBoss 2 26.03.17 10:51 Сейчас в теме
Полезно достаточно. Спасибо
44. spezc 781 26.03.17 10:56 Сейчас в теме
45. XelOla 17 30.03.17 13:55 Сейчас в теме
Скажите, а можно с Эксель проработать как в статье http://infostart.ru/public/237032/
48. su_mai 90 31.03.17 20:43 Сейчас в теме
(45) Можно только с форматом xslx.
49. pekus 25.04.17 09:12 Сейчас в теме
Можете еще добавить про установку сквозных строк:
ActiveSheet.PageSetup.PrintTitleRows = "$1:$4"

где $1 - номер первой строки, $4 - номер последней строки.

Была задача, чтобы шапка таблицы excel при выводе на печать отображалась на каждой странице. Пробовал использовать шаблон файла excel с настроенными сквозными строками, но после обработки в 1С и сохранения файла эта настройка пропадала. Долго искал как сделать эту настройку на стороне 1С - и вот нашел) Задача решена.
svetanik; +1 Ответить
50. Key-Zed 108 14.09.17 11:09 Сейчас в теме
Подскажите, не могу найти метод как выделить несколько листов в книге, и сохранить к примеру в PDF.
51. creatermc 27 26.02.18 09:24 Сейчас в теме
Большой плюс автору и goodwill за материал, многое собрано в одном месте, все очень доступно и грамотно прописано
52. Bene_Valete 188 17.07.18 06:24 Сейчас в теме
Спасибо за публикацию, очень наглядно и содержательно
53. rrustam11983 01.08.18 07:00 Сейчас в теме
54. strelec13 20 24.10.18 17:46 Сейчас в теме
Может пропустил. Непонятно, неужели ни кто не сталкивался с проблемой перезаписи файла Эксель после его изменения. Вместо записи и закрытия файла, выводится системное сообщение о предложении перезаписи файла, вместо того, чтобы записаться и закрыться.
55. user790109 9 31.10.18 10:07 Сейчас в теме
Как определить какие строки ексель заполнены? Мне требуется зачищать ексель кроме 1 строки(т.к там наименования столбцов) и загружать туда новые данные
56. user790109 9 31.10.18 11:12 Сейчас в теме
Попытка
		Excel = Новый COMОбъект("Excel.Application");
		Excel.Application.DisplayAlerts = False;
		Excel.WorkBooks.Open(ИмяФайла);
		Excel.visible = Истина;

		Excel.Sheets("LRU_LET").Select();
		НС = 1;
		НК = 1;
		Excel.Range(Excel.Cells(2, 1), Excel.Cells(100, 3)).Delete();
		Для каждого стр Из ТЗ Цикл
			НС = НС + 1;
			Excel.Cells(НС, НК).Value = стр.ITEM;  
			Excel.Cells(НС, НК+1).Value = стр.DSCA;
			Excel.Cells(НС, НК+2).Value = стр.QHND;
		КонецЦикла;
		Excel.ActiveWorkBook.Save();

		Excel.DisplayAlerts = 0; 
		Excel.ActiveWorkbook.Close();
		Excel.DisplayAlerts = 1;
		Excel.Application.Quit();
		Excel = Неопределено;
	Исключение
		Сообщить("Ошибка при открытии файла с помощью Excel! Загрузка не будет произведена!");
		Сообщить(ОписаниеОшибки());
	КонецПопытки;
Показать

После выполнения, файл доступен только для чтения. В процессе заметил что visible остается Ложь, и процесс екселя остается активным(не удает удалить файл, пока не грохнешь процесс). Что я делаю не так?
58. Alex_1066 04.06.19 17:29 Сейчас в теме
Вопрос, а в примере клиент-серверного варианта строчка "ОтобразитьТабНаФорме(Таб);" что обозначает? Или другими словами - что там и как реализовано в этой процедуре/функции дальше?
59. Sib4p 697 04.06.19 21:47 Сейчас в теме
(58) Эта процедура отображает произвольную таблицу значений на форме
60. semx800 71 27.06.19 11:28 Сейчас в теме
Долго боролся с проблемой, не читался эксель в серверном варианте и проблема оказалась не в коде, а в экселе.

Решение тут https://infostart.ru/public/196323/ спасибо этому челу, и тебе конечно за статью.
Divedition; +1 Ответить
61. ignor 235 16.11.19 23:47 Сейчас в теме
Лист.Rows(Инд).OutlineLevel

Возвращает глубину для деревьев, то есть для сворачиваемых и разворачиваемых строк
63. pavel_pss 289 15.01.20 15:48 Сейчас в теме
Пример удаление листов в файле эксель. по списку из справочника Подарок :)





ДокExcel = ПолучитьCOMОбъект("", "Excel.Application");
ExcelФайл = ДокExcel.WorkBooks.Open(ИмяВременногоФайла);

Для Сч = 1 По ExcelФайл.Sheets.Count Цикл

Если Сч > ExcelФайл.Sheets.Count Тогда
Прервать;
КонецЕсли;

ИмяЛиста = ExcelФайл.Sheets(Сч).Name;

Запрос = Новый Запрос;
Запрос.Текст = "ВЫБРАТЬ
| СписокЛистовВФайлеЭксельДляУдаления.Ссылка КАК Ссылка,
| СписокЛистовВФайлеЭксельДляУдаления.Наименование КАК Наименование
|ИЗ
| Справочник.СписокЛистовВФайлеЭксельДляУдаления КАК СписокЛистовВФайлеЭксельДляУдаления
|ГДЕ
| СписокЛистовВФайлеЭксельДляУдаления.Используется = ИСТИНА
| И СписокЛистовВФайлеЭксельДляУдаления.Наименование = &Наименование";

Запрос.УстановитьПараметр("Наименование", ИмяЛиста);

Результат = Запрос.Выполнить();
Если НЕ Результат.Пустой() Тогда
ExcelФайл.Sheets(ИмяЛиста).Select();
ExcelФайл.ActiveSheet.Delete();

Сч = Сч - 1;
КонецЕсли;
КонецЦикла;

// начинаем закрывать файл
ДокExcel.DisplayAlerts = 0;

// сохраняем его в новый временный, для помещения во врем.хранилище на сервер
ИмяВременногоФайлаНовый = ПолучитьИмяВременногоФайла(ТипВыбранногоФайла); //"xlsx"
ExcelФайл.SaveAs(ИмяВременногоФайлаНовый);
ExcelФайл.Close();


ДокExcel.DisplayAlerts = 1;
ДокExcel.Quit();
ДокExcel = Неопределено;
64. snurk 9 14.02.20 03:48 Сейчас в теме
Несколько дополнений.

Работа с Excel-файлами через ТабличныйДокумент удобна, довольно быстра и кроссплатформенна, я предпочитаю именно её, но, к сожалению, тут есть ограничение - метод Прочитать() недоступен в тонком клиенте. Это кажется довольно нелогичным, т.к. тонкий клиент без проблем может открывать файлы Excel просто через меню Файл/Открыть, но это так.

Второе - по поводу работы с файлами, содержащими несколько листов, через ТабличныйДокумент. При загрузке листы файла преобразуются в именованные области. К сожалению, при этом 1С делает некоторые изменения имён листов - например, пробел заменяется на символ подчёркивания. Способа получить оригинальное имя листа при загрузке файла только средствами 1С, похоже, нет.

Наконец, последнее - есть малоизвестный объект ПакетОтображаемыхДокументов. Он очень удобен, когда надо сохранить файл Excel с несколькими листами и заданием их имён. Также этот объект хорош для печати, через него можно задать имя задания печати. К сожалению, через него нельзя прочитать файл.
65. 5H1 25.02.20 09:58 Сейчас в теме
ADODB.Command Для выполнения комманд на языке T-SQL

Как оказалось, это не совсем так. Используется язык Microsoft Access SQL. В нем нет множества полезных функций и методов, которые есть в T-SQL.

Например, я пытался добавить колонку ID (SEL ECT IDENTITY (int, 0, 1) AS ID), которая отсутствует в исходном excel-файле для того, чтобы отобрать нужные мне записи по ID, так как файл имеет достатоно много лишних полей. Как оказалось, напрямую это сделать нельзя.

Был составлен примерно такой запрос:

SEL ECT
	IDENTITY (int, 0, 1) AS ID
	, *
INTO #EXCEL
FR OM [EXCEL3]...[Page1$] AS EXCEL

SELECT * FR OM #EXCEL
WH ERE ID > 9
ORDER BY ID 

DR OP   TABLE #EXCEL
Показать


Который отлично отрабатывает в Microsoft SQL Server Management Studio 18, но не в 1С.

Если есть доступ к SQL серверу, можно открыть excel-файл запросом:

SELECT *
FR OM OPENROWSET(
    'Microsoft.ACE.OLEDB.12.0',
    'Excel 12.0;HDR=NO;Database=T:\temp\Test.xlsx',
    'select * fr om [sheet1$]')


И произвести дальнейшие манипуляции на стороне SQL сервера используя синатксис T-SQL, а не Microsoft Access SQL. Но теперь стоит вопрос целеобразности всего этого.

Если я не прав, поправьте меня.
66. noon 11 18.09.20 10:37 Сейчас в теме
Не нашел тут вроде, копирование диапазона ячеек:
исходныйДиапазон.Copy( диапазонВставки );
Может кому полезно будет.
user1189839; +1 Ответить
67. lev6975 29.09.20 12:36 Сейчас в теме
Через табдок лучший способ, пожалуй...
Кроме 1С ничего не требуется
Ни экселя, ни ADODB - драйверов, которые тоже капризные
И с комом не возиться, который любит слетать при обновлении платформы
А для облак это вообще находка, там никакие Com не работают принципиально, например во фрешах...
68. lev6975 29.09.20 12:41 Сейчас в теме
(67) Единственный минус, не работает на тонком клиенте, нужно на сервер все гнать...
То есть, всяки эффекты во внешних обработках типа полос прогрессов чтения сделать будет проблематично, придется пользовать фоновые задания
70. Азбука Морзе 104 23.10.20 16:34 Сейчас в теме
Метод получения последней строки в эксельной таблице зависит от установленных фильтров:

ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row;

Если в эксельной таблице установлен фильтр, то последней строкой является строка из удовлетворяющих фильтру.

Может кто знает как получить последнюю строку в таблице несмотря на наложенные фильтры?
Или как сбросить фильтр программно?
72. user852481 03.01.21 22:16 Сейчас в теме
(70)
Или как сбросить фильтр программно?

Если висят "головы" фильтров по столбцам, то:
Excel.Sheets(1).ShowAllData

с условием:
If Excel.Sheets(1).FilterMode = True Then Excel.Sheets(1).ShowAllData
71. user852481 03.01.21 21:54 Сейчас в теме
Sh = Excel.Sheets(1); //наш COM объект
Sh.Range("K4:N" + Sh.Cells(Sh.Rows.Count, "N").End(xlUp).Row);//вычисляем последнюю заполненную ячейку в столбце "N".

В 1С не проверял, в VBA внутри самого экселя это работает.
73. user852481 04.01.21 22:07 Сейчас в теме
xlUp - одна из множества констант в Excel. -4162
a = Sh.Cells(Sh.Rows.Count, "A").End(-4162).Row

в переменную попадет последняя заполненная ячейка, т.е. в которой есть хоть какие-то данные, в столбце "A". Столбец можно указывать числом.
74. kaliuzhnyi 135 25.02.21 16:43 Сейчас в теме
Обход табличного документа, как по мне вообще дело не благодарное.
Используйте построитель запроса.

	// Чтение данных из временного файла в табличный документ.
	ТабличныйДокумент = Новый ТабличныйДокумент;
	ТабличныйДокумент.Прочитать(ИмяВременногоФайла);
	
	// Обход данных табличного документа и загрузка их(данных) в таблицу значений.
	ПостроительЗапроса = Новый ПостроительЗапроса;
	ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
	ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
	ПостроительЗапроса.ЗаполнитьНастройки();
	ПостроительЗапроса.Выполнить();
	
	// Получение таблицы значений
	ТаблицаЗначений = ПостроительЗапроса.Результат.Выгрузить();
Показать
75. kaliuzhnyi 135 25.02.21 16:52 Сейчас в теме
(74) Вот полный и готовый шаблон загрузки и табличного документа, в данном случае из экселя.

&НаКлиенте
Процедура ЗагрузкаДанныхИзТабличногоДокумента(Команда)
	
	ДиалогВыбораФайла = Новый ДиалогВыбораФайла(РежимДиалогаВыбораФайла.Открытие);
	ДиалогВыбораФайла.Заголовок = "Выберите табличный документ";
	ДиалогВыбораФайла.Фильтр = НСтр("ru='Текстовый документ(*.xls;*.xlsx)|*.xls;*.xlsx'");
	ДиалогВыбораФайла.МножественныйВыбор = Ложь;
	
	НачатьПомещениеФайла(Новый ОписаниеОповещения("ЗагрузкаДанныхИзТабличногоДокументаНаСервере", ЭтотОбъект),,
		ДиалогВыбораФайла);

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

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

	// Сохранение двоичных данных на сервере.
	РасширениеВременногоФайла = Сред(ВыбранноеИмяФайла, СтрНайти(ВыбранноеИмяФайла, ".", НаправлениеПоиска.СКонца));
	ИмяВременногоФайла = ПолучитьИмяВременногоФайла(РасширениеВременногоФайла);
	ДвоичныеДанные.Записать(ИмяВременногоФайла);
	
	// Чтение данных из временного файла в табличный документ.
	ТабличныйДокумент = Новый ТабличныйДокумент;
	ТабличныйДокумент.Прочитать(ИмяВременногоФайла);
	
	// Обход данных табличного документа и загрузка их(данных) в таблицу значений.
	ПостроительЗапроса = Новый ПостроительЗапроса;
	ПостроительЗапроса.ИсточникДанных = Новый ОписаниеИсточникаДанных(ТабличныйДокумент.Область());
	ПостроительЗапроса.ДобавлениеПредставлений = ТипДобавленияПредставлений.НеДобавлять;
	ПостроительЗапроса.ЗаполнитьНастройки();
	ПостроительЗапроса.Выполнить();
	
	// Получение таблицы значений с данными.
	ТаблицаЗначений = ПостроительЗапроса.Результат.Выгрузить();
	
	// Обработка таблицы значений.
	
	
КонецПроцедуры
Показать
svetanik; eeeio; aguriev; +3 Ответить
78. nikolav 47 16.06.21 07:51 Сейчас в теме
(75) для получения первого листа книги Excel после чтения табличного документа нужно добавить
// полученине первого листа
Попытка
	ТабличныйДокумент = ТабличныйДокумент.ПолучитьОбласть(ТабличныйДокумент.Области[0].Имя);
Исключение
	Сообщить("Ошибка получения области листа");
	Возврат;
КонецПопытки;

иначе запрос будет выполнен по всем листам.

Также если на листе есть столбцы с одинаковыми именами, то после запроса их данные будут утеряны, и результаты будут кривые. Чтобы это обойти нужно переименовать заголовки в табличном документе.
ПерваяСтрока = 1; //строка заголовков (последняя перед данными)
ПерваяКолонка = 1;
ПоследняяСтрока = ТабличныйДокумент.ВысотаТаблицы;
ПоследняяКолонка = ТабличныйДокумент.ШиринаТаблицы;

// переименование столбцов
Для НомерКолонки = 1 По ПоследняяКолонка Цикл
	ТабличныйДокумент.Область(ПерваяСтрока, НомерКолонки).Текст = ("к" + НомерКолонки);
КонецЦикла;
Показать


Дальше уже в таблице значений можно задать удобные имена колонок.
ТаблицаЗначений.Колонки[0].Имя = "ИмяКолонки";
zagsa; eeeio; +2 Ответить
76. scarabey2006 26 09.04.21 17:44 Сейчас в теме
Как загрузить из листа если стоит фильтр, только те строки которые соответствуют фильтру?
77. KSM 04.05.21 13:19 Сейчас в теме
очень помогло, спасибо!
79. dadel 19 28.06.21 15:36 Сейчас в теме
Хороший труд!
Но может ещё кто расширит? Требуется выгрузить в эксель результат отчета. И пользователи его потом редактируют и он обратно загружается изменяя откорректированные пользователями данные.
Так вот, некоторые поля нужно пользователям выбирать строго из выпадающего списка, что бы вольности и многообразия с опечатками исключить.
Как этот список делается просто в эксель, понятно. Но вот как выгрузить программно результат запроса и при выгрузке проставить там возможность выбирать значения из списка - вопрос?
82. djek88 1 17.01.22 15:06 Сейчас в теме
(79)
Это решается путем программного открытия файла ecxel и добавления признака Validation в ячейку

Вот пример выпадающего списка, например, материалов, список которых прописан в листе "ЛистДиапазономДанныхВыбираемыхИзСписка" :

xlValidateList = 3;
xlValidAlertInformation = 3;
xlBetween = 1;
Формула = "="+ЛистДиапазономДанныхВыбираемыхИзСписка.Name+"!R2C1:R"+Формат((мКолвоСтрокЛиста_ЛистДиапазономДанныхВыбираемыхИзСписка),"ЧГ=0")+"C1";
Sheet.Cells(НомерСтроки, НомерКолонки).Validation.Add(
				xlValidateList,
				xlValidAlertInformation,
				xlBetween,
				Формула);
Sheet.Cells(НомерСтроки, НомерКолонки).Validation.InCellDropdown=1;
Показать


более подробно можно посмотреть в онлайн справке Excel
84. dadel 19 17.01.22 20:24 Сейчас в теме
(82) Спасибо!
Сейчас уже не помню как тогда выкрутился, но выпадающего списка не осилил точно я тогда.
80. sponchebob 20 03.08.21 07:42 Сейчас в теме
Спасибо за статью, очень полезная! По ней сделал выгрузку файла эксель!
81. ssn5810 79 14.10.21 10:52 Сейчас в теме
у меня в документе таблица (колонки 1,2,3...), как в вашем случае загрузить в колонки ? Спасибо.
83. djek88 1 17.01.22 15:08 Сейчас в теме
А кто подскажет как зафиксировать первых n-срок в листе (по-аналогии, как табличном документе)?
85. bohdan-k 20.01.22 16:21 Сейчас в теме
(83). В Excel включаете запись макроса, интерактивно выполняете необходимые действия (фиксируете первые n строк, или любое другое действие), и выключаете запись макроса. Затем заходите в записанный макрос на VBA и анализируете ).
86. SlavaKron 05.04.22 10:36 Сейчас в теме
Если файл excel открыт пользователем, то открыть его программно в 1С через табличный документ не получится – выскочит:
Ошибка совместного доступа к файлу 'ИмяФайла'. 32(0x00000020): Процесс не может получить доступ к файлу, так как этот файл занят другим процессом.
Для обхода придумал костыль, суть которого заключается в том, чтобы через ком получить открытый файл excel и с помощью метода SaveCopyAs сохранить его копию во временный файл, из которого уже грузить данные в табличный документ. При чем в копию сохраняются изменения, еще не сохраненные в оригинальном файле:
&НаКлиенте
Процедура ПослеВыбораФайла(ВыбранныеФайлы, ДополнительныеПараметры) Экспорт
	
	Если ВыбранныеФайлы <> Неопределено Тогда
		ОП = Новый ОписаниеОповещения("ПослеПомещенияФайлаНаСервер", ЭтотОбъект);
		ПутьКФайлу = ВыбранныеФайлы[0];
		РезультатЧтения.Очистить();
		Попытка
			НачатьПомещениеФайлаНаСервер(ОП, , , , ПутьКФайлу, УникальныйИдентификатор);
		Исключение
			КомЭксель = ПолучитьCOMОбъект(ПутьКФайлу, "Excel.Application");
			Расширение = Сред(ПутьКФайлу, СтрНайти(ПутьКФайлу, ".", НаправлениеПоиска.СКонца) + 1);
			ИмяВременногоФайла = ПолучитьИмяВременногоФайла(Расширение);
			КомЭксель.SaveCopyAs(ИмяВременногоФайла);
			КомЭксель = Неопределено;
			НачатьПомещениеФайлаНаСервер(ОП, , , , ИмяВременногоФайла, УникальныйИдентификатор);
		КонецПопытки;
		
	КонецЕсли;
	
КонецПроцедуры
Показать
87. alexander-lubich 26 10.06.22 12:13 Сейчас в теме
Привет ,
Метод выгрузки файла в Табдок ограничен, я получаю не все возможности как если при работе через COM (тот который требует установки Ексель)

через com я могу получить путь гиперссылки на какой либо файл используя код Лист.Cells(n,3).Hyperlinks.Item(1).Address

при работе через Табдок часть свойств ячеек ексель не доступна.

при использовании ADODB возможность вынуть гиперссылку из ячейки сохраняется или вся его "модность" в том что не надо ексель ставить и отборы в него кинуть сразу по Т-SQL ?
88. JaSergey 10.08.22 10:23 Сейчас в теме
Всем привет!
Есть такая проблема, с 1С я на вы, а с Excel тем более, но потребовалось создать внешний отчет для 1С с выгрузкой в Excel для руководства.
Условия: создать файл на нескольких листах. В них выгружаются данные из 1С с указанием даты операций в 1С. Основное условие, чтобы при добавлении информации, оставалось видно, только информация по свежим датам.
Может, я плохо умею пользоваться поисковиком, но я не смог найти метод создания автофильтра по условию с типом дата, а тем более когда нужно отфильтровать по нескольким датам.

Прочитал справочник VBA для Office (https://docs.microsoft.com/ru-ru/office/vba/api/excel.xlautofilteroperator) написал такой код:
УсловиеФильтраВычерков = Новый Массив;
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
УсловиеФильтраВычерков.Добавить(2);
УсловиеФильтраВычерков.Добавить(Формат(Дата(2022,03,03),"ДФ=М/д/гггг"));
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока-1,ВсегоКолонок)).AutoFilter(1,,7,УсловиеФильтраВычерков);

Не работает. Помогите решить проблему!
P.S. Руководство хочет только распечатать(без манипуляций с фильтрами, т.к. совсем никак с работой на компьютере(странно, но такие еще остались)) и идти к своему руководству.
89. JaSergey 10.08.22 11:15 Сейчас в теме
(88) Забыл уточнить. Платформа 8.2.13... Обычные формы.

Текст ошибки:
{Форма.Форма.Форма(226)}: Ошибка при вызове метода контекста (AutoFilter)
ТекЛист.Range(ТекЛист.Cells(1,1),ТекЛист.Cells(АктивнаяСтрока,ВсегоКолонок)).AutoFilter();
по причине:
Неизвестная ошибка

В Excel запуск Макроса делал. Оттуда формат даты взял такой.
90. user1428658 16.08.22 12:39 Сейчас в теме
спасибо очень помогло
91. TariK 18.10.22 17:03 Сейчас в теме
(1) Подскажите:
1) как с помощью OLE найти конкретное слово в произвольной ячейки экселе?
2) пустую строку экселя?
92. flyDrag 290 31.10.22 16:25 Сейчас в теме
оч. полезная статья, помогло
93. user635629_exter 37 18.12.22 05:53 Сейчас в теме
Добавьте, пожалуйста, отключение пересчета книги xlCalculation из https://forum.mista.ru/topic.php?id=263635

это для семерки, но думаю достаточно для понимания.
[1C]
//*******************************************
Процедура Сформировать()
   xlCalculationAutomatic = -4105;
   xlCalculationManual = -4135;
   Попытка
       scr = СоздатьОбъект("MSScriptControl.ScriptControl");
       scr.language = "vbscript";
       ExcelApp = scr.eval("CreateObject(""Excel.Application"")");
       ExcelApp.WorkBooks.Add();
       //Вот это, при таком обращении к ехелю нет книг в контексте
   Исключение
       Попытка
           ExcelApp = СоздатьОбъект("Excel.Application");
       Исключение
           Сообщить("Не удалось открыть Excel. " + ОписаниеОшибки());
           Возврат;
       КонецПопытки;
   КонецПопытки;
   ExcelApp.Calculation = xlCalculationManual;
КонецПроцедуры
[/1C]
Показать
Оставьте свое сообщение