Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования)

Вопрос Как осуществить чтение/запись данных из/в Excel на языке 1с (используя COM-объект)

Ответ
Здесь можно скачать шаблонную обработку, разработанную в среде «1С:Предприятие 8.1» для работы с файлами Excel: [download id=»5″] В обработке осуществляются все основные действия с файлом Excel. Даны подробные комментарии. Можно использовать в качестве шаблона для разработки собственных выгрузок/загрузок в/из Excel.

Основные методы, принципы и хитрости, используемые при работе с EXCEL через COM-объект

Чтение данных из Excel

Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:

 Попытка
		Эксель = Новый COMОбъект("Excel.Application"); // для v7 код будет: Эксель = СоздатьОбъект("Excel.Application");
 Исключение
		Сообщить(ОписаниеОшибки());
		Возврат;
	КонецПопытки;

Теперь используя переменную Эксель можно управлять приложением Excel.

  • Внимание! Microsoft Excel должен быть установлен на компьютере!

Следующая команда откроет книгу:

	Книга = Эксель.WorkBooks.Open(ПутьКФайлу);

Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные:

	Лист = Книга.WorkSheets(НомерЛиста);

Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду:

	КоличествоЛистов = Книга.Sheets.Count;

Лист можно выбрать по имени листа в книге:

	Лист = Книга.WorkSheets(ИмяЛиста);

Имя листа в книге можно получить по номеру:

	ИмяЛиста = Книга.Sheets(НомерЛиста).Name;

Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе:

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

Получим значения ячейки листа в строке НомерСтроки и в колонке НомерКолонки:

	Значение = Лист.Cells(НомерСтроки, НомерКолонки).Value;

Ниже приведен отрывок кода, запустив который мы прочитаем все данные с первой страницы:

	Эксель = СоздатьОбъект("Excel.Application");
	Книга = Эксель.WorkBooks.Open(ПутьКФайлу);
	Лист = Книга.WorkSheets(1);   

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

	Для Строка = 1 По ВсегоСтрок Цикл   

		Для Колонка = 1 По ВсегоКолонок Цикл
			Значение = СокрЛП(Лист.Cells(Строка,Колонка).Value);
		КонецЦикла;       

	КонецЦикла;

Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).

После выполнения действий необходимо закрыть книгу:

	Эксель.Application.Quit();

Выгрузка данных в Excel

Для вывода (выгрузки) данных в Excel необходимо либо открыть существующую книгу, либо создать новую, и выбрать рабочий лист для вывода данных.
Для создания новой книги можно использовать следующий код:

      Книга = Excel.WorkBooks.Add();

При создании книги автоматически создаются листы (по умолчанию 3). Нам остается только выбрать нужный:

      Лист = Книга.WorkSheets(НомерЛиста);

Или добавить в книгу новый лист:

      Лист = Книга.Sheets.Add();

Добавим в ячейку на листе значение:

      Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;

Запишем книгу:

	Попытка
		Книга.SaveAs(ПутьКФайлу);
	Исключение
		Сообщить(ОписаниеОшибки()+" Файл не сохранен!");
	КонецПопытки;

Где ПутьКФайлу — полный путь к файлу книги Excel (включая имя).

После выполнения действий необходимо закрыть книгу:

      	Эксель.Application.Quit();

Как программно сохранить файл Excel в формате 2003 года

Синтаксис команды «SaveAs» во втором параметре разрешает указать формат сохраняемого файла.
Числовое значение фрмата файла Excel 2003: FileFormatNum = -4143
Т.е. команду сохранения для этого можно написатьтак:

Книга.SaveAs(ПутьДляЗаписиФайла, -4143);

Часто используемые методы Excel

Эксель.Visible = Видимость; 0 — Excel не виден, 1 — виден.
Книга = Эксель.WorkBooks.Add(); Создание новой книги (файла) Excel.
Книга = Эксель.WorkBooks.Add(ИмяФайлаШаблона); Создание новой книги (файла) Excel по шаблону «ИмяФайлаШаблона»
Книга.SaveAs(ИмяФайла); Сохранение книги Excel.
Лист = Книга.WorkSheets.Add(); Добавление нового листа в книгу.
Книга = Эксель.WorkBooks.Open(ИмяФайла); Открытие существующей книги (файла) Excel.
Лист = Книга.WorkSheets(НомерЛиста); Установка листа в качестве рабочего с номером НомерЛиста.
Лист.Name = ИмяЛиста; Задание рабочему листу имени ИмяЛиста
Лист.PageSetup.Zoom = Масштаб; Задание параметра страницы «Масштаб» (от 10 до 400).
Лист.PageSetup.Orientation = Ориентация; Ориентация: 1 — книжная, 2 — альбомная.
Лист.PageSetup.LeftMargin = Эксель.CentimetersToPoints(Сантиметры); Задание левой границы (в сантиметрах).
Лист.PageSetup.TopMargin = Эксель.CentimetersToPoints(Сантиметры); Задание верхней границы (в сантиметрах).
Лист.PageSetup.RightMargin = Эксель.CentimetersToPoints(Сантиметры); Задание правой границы (в сантиметрах).
Лист.PageSetup.BottomMargin = Эксель.CentimetersToPoints(Сантиметры); Задание нижней границы (в сантиметрах).
Лист.Columns(НомерКолонки).ColumnWidth = Ширина; Задание ширины колонке.
Лист.Cells(НомерСтроки,НомерКолонки).ColumnWidth = 0; Скрыть всю колонку, в которой расположена ячейка
Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение; Ввод данных в ячейку.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Name = ИмяШрифта; Установка шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Color = ЦветШрифта; Установка цвета шрифта в ячейке. Тип переменной ЦветШрифта — число десятичное.
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Color = ЦветРамки; Установка цвета рамки в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Interior.Color = ЦветФона; Установка цвета фона в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Size = РазмерШрифта; Установка размера шрифта в ячейке.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Bold = Жирный; 1 — жирный шрифт, 0 — нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Italic = Курсив; 1 — наклонный шрифт, 0 — нормальный.
Лист.Cells(НомерСтроки,НомерКолонки).Font.Underline = Подчеркнутый; 2 — подчеркнутый, 1 — нет.
Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат; Установка формата данных ячейки.
Лист.Cells(НомерСтроки,НомерКолонки).Borders.Linestyle = ТипЛинии; Установка рамок ячейки. 1 — тонкая сплошная.
Лист.Cells(НомерСтроки,НомерКолонки).WrapText = Истина; Осуществлять перенос по словам в указанной ячейке
Лист.Protect(); Установка защиты на лист
Лист.UnProtect(); Снятие защиты с листа
Лист.Cells(Строка, Столбец).Locked=0; Ячейка будет доступной (и после установки защиты на лист)
ПолучитьCOMОбъект(<Имя файла>, <Имя класса COM>); Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу.

Хитрости Excel

Как выборочно разрешить / запретить редактирование ячеек листа

	//Создаем объект EXCEL
	Эксель = СоздатьОбъект("Excel.Application");
	Книга = Эксель.WorkBooks.Open(ФайлВыгрузки);
	Лист =Книга.Worksheets("Список сотрудников"); // Выбор листа

	Книга.ActiveSheet.UnProtect(); //делаем шаблон незащищенным

	// Заполняем лист

	// ...................................

	// Снимаем защиту с области ввода сумм 

	Для Перем = 1 По 10 Цикл
		// Прописываем, какие ячейки будут доступными
		Книга.ActiveSheet.Cells(Перем, 2).Locked=0;
	КонецЦикла;  

	Книга.ActiveSheet.Protect(); // ставим защиту на лист

Как запретить появление на экране всяких вопросов от Excel

Excel, чтоб вопрос не задавал:

	Excel.DisplayAlerts = False;

Как программно скрыть колонку файла Excel

	// ПРИМЕР как скрыть колонку программно - скроется колонка №2: 	ЛистОшибок.Cells(ПозицияШапкиФайла, 2).ColumnWidth  = 0; // скрыть колонку №2 

Как программно назначить ячейке файла Excel перенос по словам

	// ПРИМЕР как осуществлять перенос в ячейке по словам программно ячейка в строке ПозицияШапкиФайла, колонке №2: 	ЛистОшибок.Cells(ПозицияШапкиФайла, 2).WrapText = Истина; // осуществлять перенос в ячейке по словам

Как обработать файл xls, если Excel не установлен на компьютере

Для этого можно использовать метод

	СоздатьОбъект("ADODB.Connection");

Код для 7.7, решающий такую задачу, будет выглядеть примерно так:

	db = СоздатьОбъект("ADODB.Connection");
	ConectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ИмяФайла+";Extended Properties="+"Excel 8.0;";
	rs=CreateObject("ADODB.Recordset");
	db.Open(ConectionString);
	rs.ActiveConnection = db;
	rs.CursorType = 3;
	rs.LockType = 2;

	//Чтобы задать Область, надо выделить область в екселе и нажать Вставка-Имя-Присвоить...
	rs.Source = "Select * from [Лист$1]";
	rs.Open();
	КоличествоПолей = rs.Fields.Count;
	Сообщить(КоличествоПолей);
	КоличествоЗаписей = rs.RecordCount;
	Сообщить(КоличествоЗаписей);
	Если rs.Eof()=0 Тогда
		Сообщить(rs.Fields(0).Value);
		rs.MoveNext();
	Иначе ТЗ.УстановитьЗначение(1,1,rs.Fields(1).Value);
	КонецЕсли;
	rs.Close();
	db.Close();

Как указать цвет шрифта в ячейке, цвет рамки, цвет фона

Книга.Sheets(1).Cells(1,1).Borders.Color = 25525124; // цвет рамки Книга.Sheets(1).Cells(1,1).Font.Color = 255000000; // цвет шрифта Книга.Sheets(1).Cells(1,1).Interior.Color = 255045; // цвет фона

Организация автоматической обработки файлов xls из выбранной папки

// В 8.1 код обработки файлов выглядит примерно так:

// примеры задания пути к файлам:
ПримерПапкиВСети = "\Adsf01PublicЗАГРУЗКА ЗАКАЗОВ";
ПримерПапкиЛокал = "C:1сОбмен";

// задаем путь загрузки:
ПутьЗагрузки = ПримерПапкиЛокал;

// Файлы - Массив из значений типа Файл, содержащий найденные файлы:
Файлы = НайтиФайлы(ПутьЗагрузки,"*.xls*");

// организовываем перебор файлов:
Для Каждого Файл ИЗ Файлы Цикл

	// обрабатываем файлы....
	// ...

	// В конце можно удалить бработанный файл:
	Попытка
		УдалитьФайлы(Файл.ПолноеИмя);
	Исключение
		Сообщить("Не удалось удалить файл " + ОписаниеОшибки());
	КонецПопытки;

	// или в конце можно переместить обработанный файл в специально предназначенную подпапку исходной папки:
	Попытка
		ПереместитьФайл(Файл.ПолноеИмя, ПутьЗагрузки+"Arhiv" + Файл.Имя); // папка архива: "C:1сОбменArhiv"
	Исключение
		Сообщить("Не удалось переместить файл " + ОписаниеОшибки());
	КонецПопытки;

КонецЦикла;

// в 7.7 для аналогичных действий используются команды:
ФС.НайтиПервыйФайл()
ФС.НайтиСледующийФайл()
ФС.УдалитьФайл()
ФС.ПереименоватьФайл(,,);

Создание кнопки в Excel в 7.7

	ТекущийЛист.Shapes("CommandButton").Select
	ТекущийЛист.OLEObjects("CommandButton").Object.Caption = "Кнопуля";

Процедура открывает Эксель, втавляет на первый лист кнопку «Очистить» и назначает ей макрос,
устанавливающий автофильтр на колонку Е по не нулевым значениям. Текст макроса любой,
главное разделять Симв(13) строки

	Попытка
		Ex=CreateObject("Excel.Application");
	Исключение
		Сообщить(ОписаниеОшибки(),"!!!");
		Предупреждение("Не удалось запустить MS Excel!");
		Возврат;
	КонецПопытки;
	Состояние("Открытие файла...");
	Попытка
		Wb=Ex.WorkBooks.Add();
	Исключение
		Возврат;
	КонецПопытки;
	Ex.Visible=-1;
	Wb.Sheets(1).OLEObjects.Add("Forms.CommandButton.1",,,10, 99.75, 120.75,  "Очистить");//27.75
	st = "Private Sub CommandButton1_Click()" +  Chr(13) +
	" ThisWorkbook.Sheets(1).Columns(""E:E"").AutoFilter Field:=1, Criteria1:="">0"",
 Operator:=xlAnd" + Chr(13) + "End Sub";
	Ex.VBE.ActiveVBProject.VBComponents(Wb.Sheets(1).Name).CodeModule.AddFromString(st)

текст макроса пишется в переменную st

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

 Excel = ПолучитьCOMОбъект(, "Excel.Application"); 

При этом первый параметр нужно оставить пустым. В этом случае при этом подцепится тот файл экселя, который был открыт последним, даже если порядок переключения окон был таким:
Excel1, Excel2, IE, Проводник, 1С (т.е. что-то и было открытым между 1с и экселевским файлом) — все равно откроется Excel2, потому что он был активен последним.

Описание команды ПолучитьCOMОбъект

Глобальный контекст
ПолучитьCOMОбъект (GetCOMObject)
Синтаксис:
ПолучитьCOMОбъект(<Имя файла>, <Имя класса COM>)
Параметры:
<Имя файла> (необязательный)
Тип: Строка. Имя файла, включающее полный путь.
<Имя класса COM> (необязательный)
Тип: Строка. Имя класса COM, экземпляр которого должен быть создан или получен. Если расширение имени файла, указанное в первом параметре полностью идентифицирует класс объекта, то параметр может быть опущен.
Возвращаемое значение:
Тип: COMОбъект.
Описание:
Основное применение функции ПолучитьCOMОбъект — это получение COM-объекта, соответствующего файлу. Для этого следует в качестве первого параметра функции задать имя файла, который будет определять COM-объект. Например, фрагмент кода

Таб = ПолучитьCOMОбъект("C:DATADATA.XLS");

создает объект Excel.Application и открывает с его помощью файл документа «C:DATADATA.XLS». Если указанный файл во время выполнения данного фрагмента уже открыт с помощью MS Excel, то будет получена ссылка на уже существующий объект.
Для файлов, указываемых в качестве параметра данной функции, должно быть установлено соответствие расширения имени файла и класса COM.
Если в качестве имени файла указана пустая строка, то будет создан новый экземпляр объекта. В этом случае необходимо указать имя класса COM.
Например, фрагмент кода

Таб = ПолучитьCOMОбъект("", "Excel.Application");

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

П = ПолучитьCOMОбъект( , "Excel.Application");

Переменная П получит значение типа COMОбъект, соответствующее активному приложению MS Excel, если таковое имелось, или будет вызвано исключение, если активных экземпляров MS Excel не было.
Пример:

// Получение объекта COM, соответствующего файлу Таб = ПолучитьCOMОбъект("C:DATADATA.XLS"); // Создание нового экземпляра объекта Таб = ПолучитьCOMОбъект("", "Excel.Application"); // Получение активного объекта Таб = ПолучитьCOMОбъект( , "Excel.Application");

Ниже приведена сравнительная таблица команд — один и тот же код на 7.7 и 8.1 с небольшими дополнениями

Отличия:

  • команда создания самого объекта в 7.7 и 8.1 различна;
  • в 8.1 выводится запись в журнал регистрации (просто для примера, например, когда вывод сообщения на экран невозможен из-за выполнения кода в фоновом задании);
  • в 8.1 параллельно создается, заполняется и сохраняется копия исходного файла с комментариями об ошибках
1C 7.7 1C v8
Доступ из 1С к Excel производится посредством OLE. Создание COM-объекта:

Попытка
Эксель = СоздатьОбъект(«Excel.Application»);
Исключение
Сообщить(ОписаниеОшибки());
Возврат;
КонецПопытки;

Попытка
Эксель = Новый COMОбъект(«Excel.Application»);
Исключение
ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка подключения компоненты Excel. Возможно, программа Excel не установлена на данном компьютере!», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
Возврат Ложь;
КонецПопытки;

Теперь, используя переменную «Эксель», можно управлять приложением Excel.
* Внимание! Microsoft Excel должен быть установлен на компьютере!
Следующая команда откроет книгу:

Книга = Эксель.WorkBooks.Open(ПутьКФайлу);

Попытка
Книга = Эксель.WorkBooks.Open(ИмяФЗагрузки);
Исключение
ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка открытия файла Excel», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
Возврат;
КонецПопытки;

Перед тем, как начать считывание данных, укажем лист книги, с которого будем считывать данные:

Лист = Книга.WorkSheets(НомерЛиста)

Нумерация листов книги начинается с 1. Общее количество листов можно получить, используя следующую команду:

КоличествоЛистов = Книга.Sheets.Count;

КоличествоЛистов = Книга.Sheets.Count;

// создание файла для записи проблем

КнигаОшибок = Эксель.WorkBooks.Add();

КнигаОшибок = Эксель.WorkBooks.Add();

// читаем книгу по листам:

Для СчетчикПоЛистам = 1 По КоличествоЛистов Цикл //цикл по листам
// если листов больше 3, то потребуется добавить лист в книгу
Если СчетчикПоЛистам > 3 Тогда
ЛистОшибок = КнигаОшибок.Sheets.Add();
Иначе
ИмяЛистаОшибок = КнигаОшибок.Sheets(СчетчикПоЛистам).Name;
ЛистОшибок = КнигаОшибок.WorkSheets(ИмяЛистаОшибок);
КонецЕсли;

//Имя листа в книге можно получить по номеру:

ИмяЛиста = Книга.Sheets(НомерЛиста).Name;

ИмяЛиста = Книга.Sheets(СчетчикПоЛистам).Name;

//Лист можно выбрать по имени листа в книге:

Лист = Книга.WorkSheets(ИмяЛиста);

Лист = Книга.WorkSheets(ИмяЛиста);

//Итак, мы открыли книгу и выбрали лист, теперь посмотрим, сколько строк и колонок на выбранном листе:

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

Попытка
ВсегоКолонок = Лист.Cells(1,1).SpecialCells(11).Column;
ВсегоСтрок = Лист.Cells(1,1).SpecialCells(11).Row; // используем для перебора строк
Исключение
ЗаписьЖурналаРегистрации(«Excel: Неудачная попытка получения количества колонок и строк Excel», УровеньЖурналаРегистрации.Ошибка,,, ОписаниеОшибки());
Возврат;
КонецПопытки;

 

// получение значения из конкретной ячейки файла экселя:

Значение = Лист.Cells(НомерСтроки, НомерКолонки).Value;

Для счетчикПоКолонкам = 1 По ВсегоКолонок Цикл //цикл по колонкам ЗначениеВЯчейке=Книга.Sheets(СчетчикПоЛистам).Cells(ПозицияШапкиФайла,счетчикПоКолонкам).Value;

// установка нового значения ячейки экселя:

Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;

ЛистОшибок.Cells(ПозицияШапкиФайла, счетчикПоКолонкам).Value = ЗначениеВЯчейке;

// сохранение изменений в новом файле экселя:

// если такой файл уже был записан, удалим его, чтобы эксель не спросил интерактивно про перезапись
Попытка
ИмяФайлаОшибок = ВыбФайл.Путь + ВыбФайл.ИмяБезРасширения + «_bad.xls»;
ФайлОш = Новый Файл(ИмяФайлаОшибок);
Если
ФайлОш.Существует() Тогда
УдалитьФайлы(ИмяФайлаОшибок);
КонецЕсли;
КнигаОшибок.SaveAs(ИмяФайлаОшибок); // файл с ошибочными данными
Исключение
ДобавитьСообщениеВОшибки(«Не удалось записать в файл сообщения об ошибках!»+ ОписаниеОшибки(),,, «Важно»);
КонецПопытки;

 

// После выполнения действий закрываем книгу:

Эксель.Quit();

Эксель.Quit();

97 thoughts on “Excel — основные методы и конструкции языка, подключение через COM — соединение (1С: v8 и v7: Программисту: Язык программирования)

  1. Альберт, спасибо. Действительно, в перечне команд это различие не было учтено.
    Добавлена сравнительная табличка команд. Все остальное практически без изменений.

  2. Я думаю, не будет лишним 🙂

    Книга = Эксель.WorkBooks.Add(ИмяФайлаШаблона);

    если есть необходимость создания файла по шаблону

  3. Подскажите, пожалуйста, можно ли в 8.1 подцепить открытый Excel-евский файл? Чтобы в реальном времени изменить в Excel, и не загружая его в 1с, а лишь переключить окно, выгрузить данные из него? Заранее, спасибо.

  4. Ильдар, команда

    Excel.WorkBooks.Open(ИмяФЗагрузки)

    срабатывает и при открытом файле Excel. Важно, чтобы на момент исполнения этой команды открытия из 1С, файл содержал уже те данные, которые нужны, т.е. файл на этот момент уже должен быть изменен.

    Т.е. порядок такой: открыли файл из Windows, отредактировали, сохранили его (можно не закрывая), переключились на 1С, прочитали файл.

  5. Спасибо, но уже нашел проще вариант, может пригодится кому:

    Excel = ПолучитьCOMОбъект(, «Excel.Application»)

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

  6. Ильдар, есть сомнения, если будет запущено одновременно несколько файлов, какой подцепится?

  7. Подцепиться тот, что был открыт последним.
    Даже если порядок переключения окон был таким:

    Excel1, Excel2, IE, Проводник, 1С (т.е. что-то и было открытым между 1с и экселевским файлом), то все равно откроется Excel2, потому что он был активен последним.

  8. Здравствуйте, а как насчет загрузки в 7.7 из .xls файла без установленного экселя? Может у кого то есть подходящий пример? Спасибо.

    • Метод СоздатьОбъект(«Excel.Application») не поможет, если запускать исполнение на рабочей станции, на которой Эксель не установлен.

      Быть может, все же есть компютер, на котором он установлен? Можно попробовать вызывать исполнение модуля дистанционно, на том компьютере, где эксель установлен.

      Еще, как вариант, быть может сначала попробовать преобразовывать формат в txt или xml и работать уже с преобразованным файлом.

      Или можно использовать не СоздатьОбъект(«Excel.Application»), а СоздатьОбъект(«ADODB.Connection») — пример см. http://www.1c-h.ru/?p=1154

  9. Спасибо, я уже разобралась:

    	db = СоздатьОбъект("ADODB.Connection");  
    	ConectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source="+ИмяФайла+";Extended Properties="+"Excel 8.0;";
    	rs=CreateObject("ADODB.Recordset"); 
    	db.Open(ConectionString);
    	rs.ActiveConnection = db;
    	rs.CursorType = 3;
    	rs.LockType = 2;
    	//Чтобы задать Облсть, надо выделить область в екселе и нажать Вставка-Имя-Присвоить...	
    	rs.Source = "Select * from [Лист$1]"; 
    	rs.Open();
    	
    	КоличествоПолей = rs.Fields.Count;
    	Сообщить(КоличествоПолей); 
    	КоличествоЗаписей = rs.RecordCount;
    	Сообщить(КоличествоЗаписей);
    	Если rs.Eof()=0 Тогда
    		Сообщить(rs.Fields(0).Value);
    		rs.MoveNext();
    		Иначе ТЗ.УстановитьЗначение(1,1,rs.Fields(1).Value);
    	КонецЕсли;
    	rs.Close();
    	db.Close();
    
  10. Полезная инфа, спасибо!
    Подскажите, а как изменить цвет фона ячейки при выгрузке данных в Excel из 1С

    • Примерно вот так (сами цвета взяты «с потолка»);

      Книга.Sheets(1).Cells(1,1).Borders.Color = 25525124; // цвет рамки
      Книга.Sheets(1).Cells(1,1).Font.Color = 255000000; // цвет шрифта
      Книга.Sheets(1).Cells(1,1).Interior.Color = 255045; // цвет фона

      Книга.Sheets(1) — это первый лист, Cells(1,1) — это ячейка на пересечении первой строки, первой колонки соответственно.

  11. Спасибо. Нашел ещё такое решение:

     Excel.Range(Excel.Cells(Строчка,1), Excel.Cells(Строчка, 3)).Select; // выделение области первых трех ячеек текущей строки
    Excel.Selection.Interior.ColorIndex = 37; 

    Индекс цвета вроде бы, если больше 56 — выдает ошибку. Надо попробовать Color. Кстати, в чем принципиальная разница между Color и ColorIndex?

  12. Сорри, что-то плохо поместилось последнее сообщение… Наверное, плохо редактнул. 🙂

    • ColorIndex указывает на предопределенный цвет, Color настраивает цвет для текущего индекса.

  13. ColorIndex, если больше 56, выдает ошибку, потому что в Excel 2003 56 предопределенных цветов.
    И спасибо за интересные вопросы 🙂

  14. Полезная инфа, спасибо!
    Подскажите, а как выделить несколько колонок и скрыть

    • // ПРИМЕР как скрыть колонку программно - скроется колонка №2: ЛистОшибок.Cells(ПозицияШапкиФайла, 2).ColumnWidth  = 0; // скрыть колонку №2

    • // как осуществлять перенос в ячейке по словам программно, ячейка в строке ПозицияШапкиФайла, колонке №2: ЛистОшибок.Cells(ПозицияШапкиФайла, 2).WrapText = Истина; // осуществлять перенос в ячейке по словам

    • Постановка задачи не до конца ясна. Если в общем, то группироками можно управлять с помощью метода «Лист.Outline …»

  15. Добрый день!
    Очень полезная статья!
    Есть такая задача — выгрузить в Excel 40 000 строк. С помощью указанного примера это будет долго, потому что форматирование каждой ячейки выполняется не быстро.
    Есть мысль попробовать сначала выгрузить в текст, а потом этот текст «поднять» в Excel. Должно быть гораздо быстрее.
    Что посоветуете?

    • Спасибо!
      Да, возможно, так быстрее. Как вариант, можно попробовать сформировать табличный документ (в формате 1С — «mxl»), а его уже сохранить как «xls» через буфер обмена, т.е. вручную.

  16. А можно ли не устанавливать excel на сервер, а скопировать какие-либо его компоненты, а то сервак совсем загружен. Тем более сохранение будет на сетевой диск?

  17. Здравствуйте, подскажите, пожалуйста, как загрузить все файлы .xls в 1с 7.7 из выбранного каталога

    • // В 8.1 код обработки файлов выглядит примерно так:
      
      // примеры задания пути к файлам:
      ПримерПапкиВСети = «Adsf01PublicЗАГРУЗКА ЗАКАЗОВ»;
      ПримерПапкиЛокал = «C:1сОбмен»;
      
      // задаем путь загрузки:
      ПутьЗагрузки = ПримерПапкиЛокал;
      
      // Файлы — Массив из значений типа Файл, содержащий найденные файлы:
      Файлы = НайтиФайлы(ПутьЗагрузки,«*.xls*»);
      
      // организовываем перебор файлов:
      Для Каждого Файл ИЗ Файлы Цикл
      	
      	// обрабатываем файлы….
      	// …
      	
      	// В конце можно удалить бработанный файл:
      	Попытка
      		УдалитьФайлы(Файл.ПолноеИмя);
      	Исключение
      		Сообщить(«Не удалось удалить файл « + ОписаниеОшибки());
      	КонецПопытки;
      	
      	// или в конце можно переместить обработанный файл в специально предназначенную подпапку исходной папки:
      	Попытка
      		ПереместитьФайл(Файл.ПолноеИмя, ПутьЗагрузки+«Arhiv» + Файл.Имя); // папка архива: «C:1сОбменArhiv»
      	Исключение
      		Сообщить(«Не удалось переместить файл « + ОписаниеОшибки());
      	КонецПопытки;
      	
      КонецЦикла;
      	
      // в 7.7 для аналогичных действий используются команды:
      ФС.НайтиПервыйФайл()
      ФС.НайтиСледующийФайл()
      ФС.УдалитьФайл()
      ФС.ПереименоватьФайл(,,);
      
  18. Здравствуйте!
    Спасибо за статью, оказалась очень полезной!!!
    И подскажите, если сможете:
    Я передаю данные из 1С в Эксель, и сохраняю файл.
    Если использовать формат сохранения «*.xlsx», то этот файл открывается нормально, после сохранения при открытии.
    А если использовать формат офиса до 2003 года «*.xls», то после сохранения, при открытии этого файла Экселем, мне выдается предупреждение «Действительный формат открываемого файла (1.xls) отличается от указываемого его расширением имени файла…Открыть этот файл сейчас». Варианты — да, нет, справка. Если выбираю «да», то открывает его нормально.
    Мне нужен формат сохранения «*.xls», можно ли обойтись без этого предупреждения?

    Сохраняю: «Книга.SaveAs(ПутьДляЗаписиФайла);», где ПутьДляЗаписиФайла — переменная типа «Строка», со значением например «C:1.xls»

  19. Здравствуйте! не подскажите такую вещь: как запретить выделение заблокированных ячеек? пример моего кода:
    Книга = Эксель.WorkBooks.Open(ПутьКФайлу);
    Книга.ActiveSheet.Protect();
    //Книга.ActiveSheet.EnableSelection = xlUnlockedCells;
    Эксель.ActiveWorkBook.Save();
    Эксель.Quit();
    Платформа: 8.1

  20. Разбрался, всем спасибо. Вот так прописывать надо :

    Книга.ActiveSheet.EnableSelection = 1;
  21. хм. странное дело: в 2007 экселе открываю созданный документ, запрет на выделение заблокированных ячеек стоит после

    Книга.ActiveSheet.EnableSelection = 1;

    . Однако когда пользуюсь 2003 екселем Запрет исчезает, но защита остается… а нужно в 2003!

    • Ариан, спасибо за информацию. Напишите, когда удастся окончательно решить задачу.

  22. Здравствуйте! Передо мной стоит такая задача: обеспечить перенос информации о товаре из книги Excel в 1С v8.1 с созданием карточек в справочнике ТМЦ. При этом данные каждой записи на листе Excel должны помещаться в соответствующие поля карточки в 1С. Причём очень желательно, чтобы перед созданием новой карточки производилась бы проверка на наличие таковой в справочнике ТМЦ по какому-либо идентификационному коду (например, штрих-коду товара), и, если таковая уже существует, то запись игнорироуется (пропускается). Задача актуальна для множества пользователей, поэтому смею надеяться, что такая процедура существует в природе… Вы не в курсе, есть ли она? Очень хотелось бы её заполучить каким-либо способом… Заранее благодарю за ответ.

    • Добрый день, Анатолий. На диске ИТС есть универсальная обработка выгрузки/загрузки из Excel, поищите там. Можно попробовать приспособить ее.
      И к этой статье приложена обработка — заготовка. Можно попробовать использовать для своих доработок ее.
      «Опознание» номенклатуры по данным файла Excel в каждом случае свое, написать что-то более универсальное пока не довелось.

  23. Инфа очень полезная. Возник вопрос, можно ли полностью очистить рабочий лист v 7.7?

    • Можно разными способами. Например, трудоемкий: задать область R1C1:RxxxCyyy и всю ее очистить. Предварительно вычислив xxx и yyy как последние непустые ячейки (перебирая все ячейки).

      Другой способ — удалить лист и тут же создать с таким же именем.

      Третий (если нужно очистить только содержимое, сохранив формат):
      Лист.UsedRange.ClearContents();

      Если включая формат: Лист.UsedRange.Clear();

      Или так:
      Диапазон=Лист.Cells;
      Диапазон.Delete();

  24. А как заполнить этот самый лист Excel из Документа 1с?
    А то книга создаётся, но толку то от этого…

    • Алексей, вот этой командой:
      Лист.Cells(НомерСтроки, НомерКолонки).Value = Значение;
      где «Значение» — это то, что нужно записать в ячейку.

  25. Что-то
    «Excel.Range(Excel.Cells(Строчка,1), Excel.Cells(Строчка, 3)).Select; // выделение области первых трех ячеек текущей строки
    Excel.Selection.Interior.ColorIndex = 37;» — ошибку пишет: Неопознанный оператор
    ApplicationExcel.Range(ApplicationExcel.Cells(i,2), ApplicationExcel.Cells(i, 3)).Select<>;

    Неопознанный оператор
    ApplicationExcel.Range(ApplicationExcel.Cells(i,2), ApplicationExcel.Cells(i, 3)).Selection<>;
    Хотела выделить 2 ячейки и установить рамку ячейки

  26. Подскажите, почему может быть ошибка «Не установлен Excel» в 1с:8? все находится на сервере, и эксел тоже установлен!!!

    • Быть может, это связано с тем, что установлена не та версия. Например, сервер 64-разрядный, а установлена 32-битная версия.

      Вот сюда еще гляньте, может, окажется полезным http://www.1c-h.ru/?p=77

  27. Здравстуйте! Столкнулся с небольшой проблемкой. Буду признателен за помощь. Суть в следующем: загружаю в документ данные из Экселя используя таблицу значений. Как данные из одной ячейки, разделённые на строки, закинуть в разные ячейки тблицы значений?

    • Здравствуйте!
      Главное — выяснить, что за символ используется в качестве разделителя строк (можно смотреть в отладчике). Затем можно разделить содержимое ячейки на подстроки, используя команды 1С: СтрДлина(), Лев(), Прав(), Сред() по этому признаку и поместить их уже по отдельности в графы таблицы значений. Но количество граф при таком подходе заранее будет неизвестно, можно давать им наименования типа «Графа» + Сч, где Сч — номер очередной подстроки, выделенной по разделителю.

  28. А как вставить формулу ячейке, чтобы он не воспринимал её как строковое значение?

    • Попробуйте знак равенства поставить в начале строки. Обычно это признак следующей затем формулы.

  29. На выходе 1С получаем файл формата xls, но внутри цифры не суммируются, а суммируются только строки.
    Вносим в любые ячейки цифры — они суммируются нормально, а вот то что экспортировали — нет

  30. Всем привет! Есть задача из файла эксель загружать номенклатуру с картинками, вопрос в том — как загружать картинки? есть идея, сохранить файл как хтмл и в появившейся папке брать картинки…

    • Привет! Можно использовать внешнюю компоненту cClipBoardObject.dll, она ловит картинку в буфере обмена и сохраняет ее в файл формата *.wmf,*.bmp,*.jpg.

      Или можно попробовать с помощью команд, т.е. копировать можно и без этой дээлельки:

      ЭлементыФормы.Добавить(Тип(«ПолеHTMLДокумента»), «ПолеHTMLДокумента», Ложь);
      Окно = ЭлементыФормы.ПолеHTMLДокумента.Документ.ParentWindow;
      Окно.ClipboardData.SetData(«Text», ТекстПисьма);
      ЭлементыФормы.Удалить(ЭлементыФормы.Индекс(ЭлементыФормы.Найти(«ПолеHTMLДокумента»)));

      вставить из буфера

      ЭлементыФормы.Добавить(Тип(«ПолеHTMLДокумента»), «ПолеHTMLДокумента», Ложь);
      Окно = ЭлементыФормы.ПолеHTMLДокумента.Документ.ParentWindow;
      СодержимоеБуфера = Окно.ClipboardData.GetData(«Text»);
      ЭлементыФормы.Удалить(ЭлементыФормы.Индекс(ЭлементыФормы.Найти(«ПолеHTMLДокумента»)));
      ТекстПисьма = ТекстПисьма + СодержимоеБуфера;

  31. Доброго времени суток!
    У меня есть обработка с 2-мя макетами Excel. Эти макеты должны сохранятся в разные файлы. Подскажите, пожалуйста, как это реализовать.
    У меня только получается сохранить первый макет, а потом выходит ошибка: Произошла исключительная ситуация (0x800a03ec)

  32. Подскажите пожалуйста,как исправить данную ошибку {Обработка.ЗагрузкаExcel.Форма.Форма.Форма(90,3)}: Переменная не определена (ЭлементыФормы)
    <>ЭлементыФормы.ТаблицаТоваров.Значение = ТаблицаТоваров; (Проверка: Тонкий клиент)

  33. Здравствуйте, подскажите пожалуйста, как исправить данную ошибку:{Обработка.ЗагрузкаExcel.Форма.Форма.Форма(90,3)}: Переменная не определена (ЭлементыФормы)
    <>ЭлементыФормы.ТаблицаТоваров.Значение = ТаблицаТоваров; (Проверка: Тонкий клиент)

    • Видимо эта команда выполняется в модуле, где не доступен метод «ЭлементыФормы». Из какого модуля вызывается строка? Модуль чего? Объекта?

  34. Не могу понять как мне выделить ОБЛАСТЬ ячеек на одном листе, СКОПИРОВАТЬ и ВСТАВИТЬ ее. Как сделать откуда и куда я знаю.
    1. Какой командой ВЫДЕЛИТЬ ОБЛАСТЬ?
    2. Какаой командой СКОПИРОВАТЬ?
    3. Какой командой ВСТАВИТЬ ОБЛАСТЬ? ( ну тут примерно я сно это тоже что и скопировать только insert вместо copy в конце).

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

    • Добрый день, подскажите как работать с объединёнными ячейками Excel 2003 при программном обращении к ним выдаёт ошибку

  36. Здраствуйте. Столкнулся с такой неувязкой. У меня листы в книге первые имеют цифровое название, последний буквенное. Индексы листам эксель назначает: 1 для листа с буквенным обозначением, 2,3,4..для листов с цифровым обозначением по порядку их следования в книге. Является ли это общим принципом.

  37. Очень полезная инфа , а можно еще примерчик как работать с картинками в экселе , особенно интересует на примере 2003 екселя . в 2007 у меня вот такой вот код отрабатывался без проблем , а в 2003 не хотит работать
    код

    MyPath = ИмяФайлаЕксель;//ПутьКПрайсу - на форме.
    	myOlApp = Новый COMОбъект ("Excel.Application");
    	MyWo= myOlApp.Workbooks.Open(MyPath, , True);
    	Эксель = MyWo.Worksheets(1).UsedRange;
    	MySheet=MyWo.Worksheets(1);//Предполагаем, что картинки на первом листе.
    	Сч = 1;
    	Для Каждого MyPic Из MySheet.Pictures Цикл
    КонецЦикла;
    

    даже не заходит в цикл

    • У меня заходит, но дальше-то что делать? Как получить двоичные данные картинки?

  38. Может кому понадобиться.
    Как записать цифры в формат текста

    Перед присвоением значения «001», устанавливаем формат ячейки:

    Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = «@»;
    Лист.Cells(НомерСтроки, НомерКолонки).Value = «001»

    • Excel.Worksheets(1).Cells(1, 1).NumberFormat = ‘@’;
      …: Microsoft Office Excel: Нельзя установить свойство NumberFormat класса Range

  39. Здравствуйте1. Делаю загрузку данных в ексел и периодически 1с выпадает с ошибками времени выполнения на строчках

    App.Cells(Ch,Chkol).AddComment()

    или

    App.Cells(Ch,Chkol).Borders.LineStyle=1

    с чем это может быть связанно??

  40. Лист.Cells(НомерСтроки, НомерКолонки).NumberFormat = Формат;
    Каким должно быть значение Формат, чтобы установить Процентный формат ячейки?

  41. Ребята! Подскажите! Очень нужно! Как на форме Excel нажать кнопку программно из 1С7.7 ???

  42. Доступом к файлам Excel подобным способом надо пользоваться лишь в крайнем случае — Excel при запуске жрет кучу процессорной мощности, и может зависать. Доступ по ADO гораздо быстрее и надежнее.

  43. Очень полезная информация. Однако ни слова о переносе дат из Екселя в 1С. В 1С сегодняшняя дата будет выглядеть примерно 41256. Просто целое число… (это на самом деле и есть число, и неважно, как оно выглядит в самом Ексель).

  44. Спасибо большое за данную статью. Очень сильно помогла и я очень вам благодарен.

  45. У меня есть некая таблица в excel разделённая на области, в каждой области находится своя картинка. Я хочу загрузить каждую картинку в отдельный элемент справочника. Как найти нужную мне область я уже разобрался. Подскажите пожалуйста, а как получить картинку находящуюся именно в этой области?

  46. Добрый день.Подскажите,как при выгрузке из 1С 7.7 платежной ведомости для банка установитьв файле Excel разделитель для сумм точку вместо запятой?

  47. Огромная благодарность!
    Не столкнувшись ни разу с загрузкой/выгрузкой в Excel до этого момента, в ходе чтения статьи писался код и в течение 10 минут поставленная задача была решена.

  48. Не уверен, жива ли ветка форума. Но все же спрошу. Сам пишу на 7.7 и пока общение с моих программ с Экселем сводилось к банальному сохранению отчета в формате xls. Появилась необходимость выгружать данные из абон.отдела в ГИС ЖКХ. Посмотрел-почитал ихнюю процедуру обмена данными. Принцип заполнения ГИС сводится к загрузке/выгрузке файлов-шаблонов Экселя и заполнения своими данными. К примеру, на одном листе указывается информация по дому: адрес, этажность, площади и т.д. На другом листе в ячейке дома уже выбираю нужный дом, в соседней ячейке выбираю (из предлагаемых перечисляемых значений) нужный параметр, и рядышком указываю его значение (в формате строки, числа, даты и т.д.). Скажем, кол-во лифтов: 2, год ввода в эксплуатацию: 1980 и т.д. Может кто подскажет как работать со связанными ячейками на листах (там — указал, а там — выбрал), и как в ячейке с перечисляемым типом выбрать нужный?
    Кстати разработчики ГИС запрещают что-либо модифицировать в макросах, защитах и пр. в файле-шаблоне.

    • Михаил, Вы уже разобрались с этим?
      Места ячеек с информацией фиксированные, не меняется от файла к файлу? Если не меняется, то можно по месту ячейки обращаться, зная заранее, что именно там нужное значение.

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *