Excel application свойства и методы 1с

Обновлено: 04.07.2024

Использование COM-соединения, выгрузка в Excel через COM-объект Excel.Application

Вообще COM-объекты используют для соединения информационной базы 1С с файлом Word, Excel, Outlook или любой другой программой, поддерживающей данный интерфейс обмена данными. В этой статье рассмотрим задачу выгрузки/загрузки данных из/в MS Excel. Чтобы это осуществить воспользуемся COM-соединением и объектом Excel.Application. Для примера возьмём задачу выгрузки/загрузки данных о номенклатуре. Пример рассмотрим ниже.

COM-соединение

Что же такое COM-соединение? Component Object Model (или COM) – это технология (фирмы Microsoft) взаимодействующих компонентов, которые одновременно могут быть использованы в разных приложениях. При этом весь функционал соответствующего компонента наследуется внутрь разрабатываемого приложения. В нашем случае COM-объект Excel.Application используется внутри кода 1С для операций с файлом книги MS Excel.

Объект Excel.Application

У объекта Excel.Application существует ряд методов, которые нам могут пригодиться для реализации нижепоставленной задачи:

ОбъектExcel.WorkBooks.Open(ИмяФайла) – Открытие книги MS Excel
ОбъектExcel.ActiveWorkbook.Close() – Закрытие текущей книги
ОбъектExcel.Quit() – Закрытие COM-объекта
ОбъектExcel.Sheets(ИмяЛиста) – Получает лист книги
ЛистExcel.Cells(НачалоСтрока, НачалоСтолбец) – Ячейка таблицы на данном листе
ЛистExcel.Range(Ячейка1, Ячейка2) – Выделенная область
ЯчейкаExcel.Value – Значение ячейки таблицы
ЯчейкаExcel.Text – Текст ячейки таблицы

Постановка задачи

Итак, предположим, что в обработке 1С у нас имеется табличная часть, состоящая из следующих колонок:

Необходимо реализовать 2 функционала (сделать на форме 2 основные кнопки):

Выгрузка табличной части в подготовленный файл MS Excel
Загрузка табличной части из файла.

Алгоритм выгрузки/загрузки в MS Excel

Алгоритм выгрузки следующий:

Выгружаем табличную часть в таблицу значений
Создаём новый COM-объект Excel.Application
Выбираем файл, открываем файл книги MS Excel
Переходим на заданный лист книги
Выгружаем данные в файл
Закрываем книгу, выходим из COM-объекта.

Алгоритм загрузки следующий:

Создаём новый COM-объект Excel.Application
Выбираем файл, открываем файл книги MS Excel
Переходим на заданный лист книги
Загружаем данные из файла в таблицу значений
Закрываем книгу, выходим из COM-объекта
Таблицу значений выгружаем в табличную часть.

Операция выгрузки и загрузки данных о номенклатуре происходит в заранее подготовленный шаблон MS Excel.

Пример кода 1С

Код 1С я постарался разделить на отдельные функции, чтобы, скопировав, с ними можно было работать где угодно. На форме обработки 1С были созданы 3 кнопки:

В итоге в реализации алгоритма получились следующие основные процедуры и функции 1С:

ПолучитьExcel – Получает COM-объект MS Excel;
ЗакрытьExcel – Закрывает использование COM-объекта MS Excel;
ПолучитьЛистExcel – Получает лист книги Excel;
ДобавитьТабличныйДокументВExcel – Добавляет табличный документ на лист Excel (нужно для выгрузки данных);
ПрочитатьОбластьИзExcel – Читает область ячеек с листа Excel (нужно для загрузки данных);
ШиринаЛистаExcel – Ширина листа Excel;
ВысотаЛистаExcel – Высота листа Excel;
ПреобразоватьТДвТЗ – Преобразует табличный документ в таблицу значений;
ПреобразоватьТЗвТД – Преобразует таблицу значений в табличный документ;

Для начала приведу вспомогательную функцию для открытия/сохранения файла на диске:

Также в реализации алгоритма были задействованы следующие вспомогательные функции:

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

// Разбирает строку в массив подстрок по разделителю. // При этом пробелы между подстроками не учитываются. // // Параметры: // Стр - исходная строка; // СтрРазделитель - разделитель, по умолчанию ","; // ИгнорироватьПустые - игнорировать ли пустые места между разделителями. // // Возвращаемое значение: // Массив строк // Функция РазобратьСтрокуВМассивПоРазделителю(Знач Стр, СтрРазделитель = ",", ИгнорироватьПустые = Ложь) Экспорт Результат = Новый Массив; ВхождениеРазделителя = Найти(Стр, СтрРазделитель); Пока ВхождениеРазделителя <> 0 Цикл ЧастьДоРазделителя = СокрЛП(Лев(Стр, ВхождениеРазделителя - 1)); Если НЕ (ИгнорироватьПустые И ПустаяСтрока(ЧастьДоРазделителя)) Тогда Результат.Добавить(ЧастьДоРазделителя); КонецЕсли; Стр = СокрЛП(Сред(Стр, ВхождениеРазделителя + 1)); ВхождениеРазделителя = Найти(Стр, СтрРазделитель); КонецЦикла; Если НЕ (ИгнорироватьПустые И ПустаяСтрока(Стр)) Тогда Результат.Добавить(СокрЛП(Стр)); КонецЕсли; Возврат Результат; КонецФункции;

// Создаёт новую таблицу значений с заданными колонками. // // Параметры: // пКолонки - <Строка>, <Структура>, <Массив>, <СписокЗначений>, <ТаблицаЗначений> - // Набор колонок для таблицы значений. // // Возвращаемое значение: // <ТаблицаЗначений> - Созданная таблица. // Функция ПолучитьТаблицуВывода(пКолонки) Экспорт ТЗ = Новый ТаблицаЗначений; Если пКолонки <> Неопределено Тогда Если ТипЗнч(пКолонки) = Тип("Строка") Тогда пКолонки = РазобратьСтрокуВМассивПоРазделителю(пКолонки); КонецЕсли; Если ТипЗнч(пКолонки) = Тип("Структура") Тогда Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(Поле.Ключ, Поле.Значение); КонецЦикла; ИначеЕсли ТипЗнч(пКолонки) = Тип("СписокЗначений") Тогда Для Каждого Поле Из пКолонки Цикл Если Поле.Пометка Тогда СтрокаТабл = ТЗ.Колонки.Добавить(Поле.Значение, пКолонки.ТипЗначения, Поле.Представление); КонецЕсли; КонецЦикла; ИначеЕсли ТипЗнч(пКолонки) = Тип("ТаблицаЗначений") Тогда ЕстьНаименование = (пКолонки.Колонки.Найти("Наименование") <> Неопределено); ЕстьТипЗначения = (пКолонки.Колонки.Найти("ТипЗначения") <> Неопределено); ЕстьЗаголовок = (пКолонки.Колонки.Найти("Заголовок") <> Неопределено); ЕстьШирина = (пКолонки.Колонки.Найти("Ширина") <> Неопределено); Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(?(ЕстьНаименование, Поле.Наименование, ""), ?(ЕстьТипЗначения, Поле.ТипЗначения, Новый ОписаниеТипов), ?(ЕстьЗаголовок, Поле.Заголовок, ""), ?(ЕстьШирина, Поле.Ширина, 0)); КонецЦикла; Иначе Для Каждого Поле Из пКолонки Цикл СтрокаТабл = ТЗ.Колонки.Добавить(Поле); КонецЦикла; КонецЕсли; КонецЕсли; Возврат ТЗ; КонецФункции;

// Создаёт структуру колонок из таблицы значений. // // Параметры: // ТабЗначений - <ТаблицаЗначений> - Любая таблица. // // Возвращаемое значение: // <Структура> - Созданная таблица. // Функция ПолучитьСтруктуруКолонокИзТаблицы(ТабЗначений) Экспорт СтруктураКолонок = Новый Структура; Для Каждого ЭлементКолонка Из ТабЗначений.Колонки Цикл СтруктураКолонок.Вставить(ЭлементКолонка.Имя, ЭлементКолонка.ТипЗначения); КонецЦикла; Возврат СтруктураКолонок; КонецФункции;

Основные функции обработки алгоритма следующие:

// Записывает данные из табличного документа в файл MS Excel. // // Параметры: // ЛистExcel - <COMОбъект> - Лист из файла MS Excel; // Таблица - <ТабличныйДокумент> - Документ, по порядку следования колонок и строк // соответствующий листу файла MS Excel; // Шапка - <Число> - Количество первых строк для шапки в файле MS Excel; // СписокСтолбцов - <Строка>, <Массив> - Список номеров столбцов, которые будут выгружены // в файл MS Excel; // СписокСтрокЗапрета - <Строка>, <Массив> - Список номеров строк, которые не должны // выгружаться в файл MS Excel. Если этот параметр не задан, то выгружаются все строки; // ПроверятьЗначения - <Булево> - Определяет будут ли проверяться ячейки табличного // документа на содержание в них значения, а не текстовое представление этого значения. // По умолчанию этот параметр "Ложь". // Процедура ДобавитьТабличныйДокументВExcel(ЛистExcel, Таблица, Шапка, СписокСтолбцов, СписокСтрокЗапрета = Неопределено, ПроверятьЗначения = Ложь) Экспорт Если ТипЗнч(СписокСтолбцов) = Тип("Строка") Тогда СписокСтолбцов = РазобратьСтрокуВМассивПоРазделителю(СписокСтолбцов); КонецЕсли; Если ТипЗнч(СписокСтрокЗапрета) = Тип("Строка") Тогда СписокСтрокЗапрета = РазобратьСтрокуВМассивПоРазделителю(СписокСтрокЗапрета); КонецЕсли; ЕстьМассив = (СписокСтрокЗапрета = Неопределено); Если ЕстьМассив Тогда Попытка МассивCOM = Новый COMSafeArray("VT_VARIANT", 1, Таблица.ВысотаТаблицы - Шапка); Исключение ЕстьМассив = Ложь; КонецПопытки; КонецЕсли; Для Каждого НомерСтолбца Из СписокСтолбцов Цикл Для НомерСтроки = Шапка+1 По Таблица.ВысотаТаблицы Цикл Если СписокСтрокЗапрета = Неопределено ИЛИ (СписокСтрокЗапрета.Найти(Строка(НомерСтроки)) = Неопределено И СписокСтрокЗапрета.Найти(Число(НомерСтроки)) = Неопределено) Тогда Область = Таблица.Область(НомерСтроки, Число(НомерСтолбца)); Значение = ?(ПроверятьЗначения И Область.СодержитЗначение, Область.Значение, Область.Текст); Если ЕстьМассив Тогда МассивCOM.SetValue(0, НомерСтроки-Шапка-1, Значение); Иначе ЛистExcel.Cells(НомерСтроки, Число(НомерСтолбца)).Value = Значение; КонецЕсли; КонецЕсли; КонецЦикла; Если ЕстьМассив Тогда ЛистExcel.Range(ЛистExcel.Cells(Шапка+1, Число(НомерСтолбца)), ЛистExcel.Cells(Таблица.ВысотаТаблицы, Число(НомерСтолбца))).Value = МассивCOM; КонецЕсли; КонецЦикла; КонецПроцедуры;

// Читает область ячеек из MS Excel в табличный документ. // // Параметры: // ЛистExcel - <COMОбъект> - Лист из файла MS Excel; // ТабДок - <ТабличныйДокумент> - Исходный табличный документ, поле табличного // документа или таблица значений. Если этот параметр не задан, то создаётся // новый табличный документ; // НачалоСтрока - <Число> - Начальная строка в файле MS Excel; // НачалоСтолбец - <Число> - Начальный столбец в файле MS Excel; // КонецСтрока - <Число> - Конечная строка в файле MS Excel; // КонецСтолбец - <Число> - Конечный столбец в файле MS Excel. // // Возвращаемое значение: // <ТабличныйДокумент> - Возвращает прочитанный из области в MS Excel табличный // документ, который передавался в эту функцию параметром "ТабДок". // Функция ПрочитатьОбластьИзExcel(ЛистExcel, ТабДок = Неопределено, НачалоСтрока, НачалоСтолбец, КонецСтрока, КонецСтолбец) Экспорт Если ТабДок = Неопределено Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; Попытка МассивCOM = Новый COMSafeArray("VT_VARIANT", КонецСтолбец - НачалоСтолбец + 1, КонецСтрока - НачалоСтрока + 1); ЕстьМассив = Истина; Исключение ЕстьМассив = Ложь; КонецПопытки; Если ЕстьМассив Тогда МассивCOM = ЛистExcel.Range(ЛистExcel.Cells(НачалоСтрока, НачалоСтолбец), ЛистExcel.Cells(КонецСтрока, КонецСтолбец)).Value; Для ИндексКолонка = НачалоСтолбец По КонецСтолбец Цикл Для ИндексСтрока = НачалоСтрока По КонецСтрока Цикл Значение = МассивCOM.GetValue(ИндексКолонка - НачалоСтолбец + 1, ИндексСтрока - НачалоСтрока + 1); ТабДок.Область(ИндексСтрока, ИндексКолонка).СодержитЗначение = Истина; ТабДок.Область(ИндексСтрока, ИндексКолонка).Значение = Значение; КонецЦикла; КонецЦикла; Иначе Для ИндексКолонка = НачалоСтолбец По КонецСтолбец Цикл Для ИндексСтрока = НачалоСтрока По КонецСтрока Цикл Значение = ЛистExcel.Cells(ИндексСтрока, ИндексКолонка).Value; ТабДок.Область(ИндексСтрока, ИндексКолонка).СодержитЗначение = Истина; ТабДок.Область(ИндексСтрока, ИндексКолонка).Значение = Значение; КонецЦикла; КонецЦикла; КонецЕсли; Возврат ТабДок; КонецФункции;

// Преобразовать табличный документ в таблицу значений. // // Параметры: // ТабДок - <ТабличныйДокумент> - Исходный табличный документ; // СтруктураКолонок - <Структура>, <ТаблицаЗначений> - Структура колонок; // НачалоСтрока - <Число> - Строка начала области; // НачалоСтолбец - <Число> - Столбец начала области; // КонецСтрока - <Число> - Строка конца области; // КонецСтолбец - <Число> - Столбец конца области. // // Возвращаемое значение: // <ТаблицаЗначений> - Полученная таблица значений. // Функция ПреобразоватьТДвТЗ(ТабДок, СтруктураКолонок, Знач НачалоСтрока = Неопределено, Знач НачалоСтолбец = Неопределено, Знач КонецСтрока = Неопределено, Знач КонецСтолбец = Неопределено) Экспорт // Определение габаритов таблицы Если НачалоСтрока = Неопределено И НачалоСтолбец = Неопределено Тогда НачалоСтрока = 1; НачалоСтолбец = 1; КонецЕсли; Если НачалоСтрока = Неопределено Тогда НачалоСтрока = 1; Пока НЕ ТабДок.Область(НачалоСтрока, НачалоСтолбец).СодержитЗначение И НачалоСтрока < ТабДок.ВысотаТаблицы Цикл НачалоСтрока = НачалоСтрока + 1; КонецЦикла; ИначеЕсли НачалоСтолбец = Неопределено Тогда НачалоСтолбец = 1; Пока НЕ ТабДок.Область(НачалоСтрока, НачалоСтолбец).СодержитЗначение И НачалоСтолбец < ТабДок.ШиринаТаблицы Цикл НачалоСтолбец = НачалоСтолбец + 1; КонецЦикла; КонецЕсли; КонецСтрока = ?(КонецСтрока = Неопределено, ТабДок.ВысотаТаблицы, КонецСтрока); КонецСтолбец = ?(КонецСтолбец = Неопределено, ТабДок.ШиринаТаблицы, КонецСтолбец); // Преобразование ЭтоТаблица = (ТипЗнч(СтруктураКолонок) = Тип("ТаблицаЗначений")); ТабЗначений = ПолучитьТаблицуВывода(СтруктураКолонок); Для ИндексСтроки = НачалоСтрока По КонецСтрока Цикл СтрокаТЗ = ТабЗначений.Добавить(); ИндексКолонки = НачалоСтолбец; Для Каждого Колонка Из СтруктураКолонок Цикл НаименованиеКолонки = ?(ЭтоТаблица, Колонка.Наименование, Колонка.Ключ); пИндексКолонки = ?(ЭтоТаблица, Колонка.СтолбецОтчёт, ИндексКолонки); Если ТабДок.Область(ИндексСтроки, пИндексКолонки).СодержитЗначение Тогда СтрокаТЗ[НаименованиеКолонки] = ТабДок.Область(ИндексСтроки, пИндексКолонки).Значение; Иначе СтрокаТЗ[НаименованиеКолонки] = ТабДок.Область(ИндексСтроки, пИндексКолонки).Текст; КонецЕсли; ИндексКолонки = ИндексКолонки + 1; КонецЦикла; КонецЦикла; Возврат ТабЗначений; КонецФункции;

// Преобразовать таблицу значений в табличный документ. // // Параметры: // ТабЗначений - <ТаблицаЗначений> - Исходная таблица значений; // ТабДок - <ТабличныйДокумент> - Полученный табличный документ. Если параметр не задан, // то документ создаётся заново и возвращается функцией; // НачалоСтрока - <Число> - Строка начала области; // НачалоСтолбец - <Число> - Столбец начала области; // ВыводитьЗаголовки - <Булево> - Определяет выводить ли имена колонок или нет. // // Возвращаемое значение: // <ТабличныйДокумент> - Полученный табличный документ (возвращает параметр "ТабДок"). // Функция ПреобразоватьТЗвТД(ТабЗначений, ТабДок = Неопределено, Знач НачалоСтрока = Неопределено, Знач НачалоСтолбец = Неопределено, ВыводитьЗаголовки = Ложь) Экспорт Если ТабДок = Неопределено Тогда ТабДок = Новый ТабличныйДокумент; КонецЕсли; // Определение габаритов таблицы НачалоСтрока = ?(НачалоСтрока = Неопределено, 1, НачалоСтрока); НачалоСтолбец = ?(НачалоСтолбец = Неопределено, 1, НачалоСтолбец); // Преобразование ИндексСтроки = НачалоСтрока; Если ВыводитьЗаголовки Тогда ИндексКолонки = НачалоСтолбец; Для Каждого Колонка Из ТабЗначений.Колонки Цикл ТабДок.Область(ИндексСтроки, ИндексКолонки).Текст = ?(ПустаяСтрока(Колонка.Заголовок), Колонка.Имя, Колонка.Заголовок); ИндексКолонки = ИндексКолонки + 1; КонецЦикла; ИндексСтроки = ИндексСтроки + 1; КонецЕсли; Для Каждого Элемент Из ТабЗначений Цикл ИндексКолонки = НачалоСтолбец; Для Каждого Колонка Из ТабЗначений.Колонки Цикл ТабДок.Область(ИндексСтроки, ИндексКолонки).СодержитЗначение = Истина; ТабДок.Область(ИндексСтроки, ИндексКолонки).ТипЗначения = Новый ОписаниеТипов(Колонка.ТипЗначения); ТабДок.Область(ИндексСтроки, ИндексКолонки).Значение = Элемент[Колонка.Имя]; ИндексКолонки = ИндексКолонки + 1; КонецЦикла; ИндексСтроки = ИндексСтроки + 1; КонецЦикла; Возврат ТабДок; КонецФункции;

В реализации алгоритма был дополнительно использован объект COMSafeArray с типом VT_VARIANT. Этот COM-объект выступает в качестве двумерного массива, в котором удобно хранить значения ячеек. Также использование данного объекта повышает быстродействие операций чтения/записи для ячеек таблицы.

Целиком получившуюся обработку 1С, а также шаблон для загрузки/выгрузки можно скачать по ссылке ниже.

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

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

Обмен через табличный документ

Данный способ простой. Его суть заключается в том, что объект ТабличныйДокумент имеет методы:

Записать (< ИмяФайла >, < ТипФайлаТаблицы >) для выгрузки данных в файл;
Прочитать (< ИмяФайла >, < СпособЧтенияЗначений >) для загрузки данных из файла.

Внимание!

Метод Записать () доступен как на клиенте, так и на сервере. Метод Прочитать () доступен только на стороне сервера. Необходимо помнить об этом
при планировании клиент-серверного взаимодействия.

Рассмотрим пример сохранения табличного документа в файл. Необходимо любым способом создать и заполнить объект ТабличныйДокумент, а выгрузка в файл осуществляется всего лишь одной строкой:

ТабДок . Записать ( ПутьКФайлу , ТипФайлаТабличногоДокумента . XLSX );

Загрузка из файла осуществляется также достаточно просто:

ТабДок = Новый ТабличныйДокумент ;
ТабДок . Прочитать ( ПутьКФайлу , СпособЧтенияЗначенийТабличногоДокумента.Значение );

Обмен через OLE

Обмен через технологию OLE automation, пожалуй, самый распространенный вариант программной работы с файлами Excel. Он позволяет использовать весь функционал, предоставляемый Excel, но отличается медленной скоростью работы по сравнению с другими способами. Для обмена через OLE требуется установка MS Excel:

На компьютере конечного пользователя, если обмен происходит на стороне клиента;
На компьютере сервера 1С:Предприятие, если обмен происходит на стороне сервера.

Пример выгрузки:

Примеры чтения:

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

Вместо последовательного обхода всех строк листа можно выгрузить все данные в массив и работать с ним. Такой подход будет быстрее при чтении большого объема данных:

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

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

В таблице ниже приведены наиболее востребованные свойства и методы для работы с Excel через OLE:

Для того, чтобы узнать какое свойство нужно менять или какой метод вызвать можно воспользоваться макросами Excel. Если записать макрос с требуемыми действиями, то после можно посмотреть программный код на VBA записанного макроса.

Использование COMSafeArray

Обмен через ADO

Файл Excel при обмене через ADO представляет собой базу данных, к которой можно обращаться при помощи SQL-запросов. Установка MS Excel не требуется, но обязательно наличие драйвера ODBC, при помощи которого будет осуществляться доступ. Используемый драйвер ODBC определяется при указании строки соединения к файлу. Обычно требуемый драйвер уже установлен на компьютере.

Обмен через ADO заметно быстрее обмена через OLE, но при выгрузке нет возможности использовать функционал Excel для оформления ячеек, разметки страниц, задания формул и т.д.

Пример выгрузки:

Для создания нового листа и формирования его структуры можно воспользоваться объектами ADOX.Catalog и ADOX.Table. В этом случае код примет вид:

В приведенном примере в методе

во втором параметре указывается тип колонки. Параметр необязательный, вот некоторые значения типа колонки:

Пример чтения:

В строке соединения параметр HDR определяет как будет восприниматься первая строка на листе. Возможны варианты:

В приведенных примерах рассмотрено лишь несколько объектов ADO. Объектная модель ADO состоит из следующих объектов:

Connection;
Command;
Recordset;
Record;
Fields;
Stream;
Errors;
Parameters;
Properties.

Выгрузка без программирования

Для сохранения данных из 1С в Excel не всегда целесообразно прибегать к программированию. Если в режиме Предприятия пользователь может отобразить требуемые для выгрузки данные, то, их возможно сохранить в Excel без программирования.

В открывшемся окне требуется выбрать каталог, имя и формат сохраняемого файла.

Для сохранения данных динамических списков (например, списка номенклатуры) необходимо:

Остались вопросы?
Спросите в комментариях к статье.

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

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

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

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

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

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

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

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

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

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

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

Точно так же можно задать имя листа:

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

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

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

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

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

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

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

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

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

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

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

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

Хитрости Excel

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

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

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

Как добавить лист Excel в конец списка листов книги или после конкретного листа (а не в начало книги)

Метод работает для платформ 1С v8.

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

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

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

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

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

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

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

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

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

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

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

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

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

&НаКлиенте
Процедура ФайлНачалоВыбора ( Элемент , ДанныеВыбора , СтандартнаяОбработка ) //необходимо в событии "НачалоВыбора" поля ввода вызвать ДиалогВыбораФайла

Диалог = Новый ДиалогВыбораФайла ( РежимДиалогаВыбораФайла . Открытие );
Диалог . Заголовок = "Выберите файл Excel" ;
Диалог . ПредварительныйПросмотр = Ложь;
Диалог . Фильтр = "(*.xls,*.xlsx)|*.xls;*.xlsx;|Microsoft Excel 97/2000/XP/2003 (*.xls)|*.xls|Microsoft Excel 2007/2010 (*.xlsx)|*.xlsx" ;

Если ЗначениеЗаполнено ( Объект . Файл ) Тогда
Диалог . ПолноеИмяФайла = Объект . Файл ;
КонецЕсли;

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

&НаСервере
Процедура ЗагрузитьИзXLS () // в документ Поступление материалов

// Попытка открытия файла Excel
Попытка
Excel_App = Новый COMОбъект ( "Excel.Application" );
Excel_App . WorkBooks . Open ( Объект . Файл );
Исключение
Сообщить ( "Произошла ошибка при открытии файла " + СокрЛП ( ОписаниеОшибки ())+ "! Операция прервана!" );
Возврат;
КонецПопытки;

// Попытка выбрать первый лист (можно указать другой)
Попытка
Excel_App . Sheets ( 1 ). Select ();
Исключение
// Если первый лист не найден - закрываем файл
Excel_App . ActiveWorkbook . Close ();
Excel_App = 0 ;
Сообщить ( "Первый лист не найден!" );
ОтменитьТранзакцию ();
Возврат;
КонецПопытки;

// Вычисление количества строк и колонок в зависимости от версии Excel
version_Ex = Лев ( Excel_App . Version , Найти ( Excel_App . Version , "." )- 1 );
Если version_Ex = "8" тогда
Колич_Строк = Excel_App . Cells . CurrentRegion . Rows . Count ;
Колич_Колонок = Макс ( Excel_App . Cells . CurrentRegion . Columns . Count , 13 );
Иначе
Колич_Строк = Excel_App . Cells ( 1 , 1 ). SpecialCells ( 11 ). Row ;
Колич_Колонок = Excel_App . Cells ( 1 , 1 ). SpecialCells ( 11 ). Column ;
Конецесли;

// Имена колонок в файле должны совпадать с именами реквизитов табличной части, в которую загружаются данные
// Переменная ТЗ_Колонки содержит список номеров колонок, которые будут перегружаться
ТЗ_Колонки = Новый ТаблицаЗначений ;
ТЗ_Колонки . Колонки . Добавить ( "НомерКолонки" );
ТЗ_Колонки . Колонки . Добавить ( "НазваниеКолонки" );

Для Каждая_Колонка = 1 по Колич_Колонок Цикл

ИмяКолонки = Excel_App . Cells ( 1 , Каждая_Колонка ). Text ;
ИмяБезПробелов = СтрЗаменить ( ИмяКолонки , " " , "" ); // Удаление лишних пробелов из имен колонок

// Проверка наличия реквизитов табличной части "Материалы" в документе "ПоступлениеМатериалов"
Если НЕ ПроверкаРеквизитаТЧ ( ИмяБезПробелов , Метаданные . Документы . ПоступлениеМатериалов , "Материалы" ) Тогда
Сообщить ( "Не найден реквизит с именем " + ИмяБезПробелов + "! Колонка не будет загружена!" );
Иначе
Новая_Строка = ТЗ_Колонки . Добавить ();
Новая_Строка . НомерКолонки = Каждая_Колонка ;
Новая_Строка . НазваниеКолонки = ИмяБезПробелов ;
КонецЕсли;

// Если есть колонки для загрузки и есть колонка "Материалы" (обязательная к заполнению)
Если ТЗ_Колонки . Количество () <> 0 и
ТЗ_Колонки . НайтиСтроки (Новый Структура ( "НазваниеКолонки" , "Материалы" )). Количество () <> 0 Тогда

// Создание документа и заполнение реквизитов шапки
Тек_Документ = Документы . ПоступлениеМатериалов . СоздатьДокумент ();
Тек_Документ . Комментарий = "Загружено из файла " + Объект . Файл ;
Тек_Документ . Дата = ТекущаяДата ();
Тек_Документ . Ответственный = ПараметрыСеанса . ТекущийПользователь ;

Для Тек_Строка = 1 по Колич_Строк Цикл // Заполнение табличной части "Материалы"

Строка_Док = Тек_Документ . Материалы . Добавить ();
Строка_Док . Валюта = Тек_Документ . ТипЦен . ВалютаЦены ;

Для каждого Тек_ТЗ из ТЗ_Колонки Цикл

Тек_Значение = Excel_App . Cells ( Тек_Строка , Тек_ТЗ . НомерКолонки ). Text ;

// Получение имени колонки
ИмяКолонкиДокумента = Excel_App . Cells ( 1 , Тек_ТЗ . НомерКолонки ). Text ;

// Заполнение строки данными
Если ИмяКолонкиДокумента = "Материалы" Тогда
Строка_Док . Номенклатура = Справочники . Материалы . НайтиПоНаименованию ( Тек_Значение , Истина);
ИначеЕсли ИмяКолонкиДокумента = "Цена" Тогда
Строка_Док . Цена = Тек_Значение ;
ИначеЕсли ИмяКолонкиДокумента = "Ставка_НДС" Тогда
Строка_Док . Ставка_НДС = Тек_Значение ;
КонецЕсли;

КонецЦикла;
Тек_Документ . Записать ( РежимЗаписиДокумента . Проведение ); // Запись и проведение документа
Сообщить ( "Записан документ " + СокрЛП ( Тек_Документ ));
Иначе
Сообщить ( "В файле " + СокрЛП ( Объект . Файл )+ " не достаточно данных для заполнения документа!" );
КонецЕсли;

Excel_App . DisplayAlerts = 0 ;
Excel_App . Quit ();
Excel_App . DisplayAlerts = 1 ;

&НаСервере
Функция ПроверкаРеквизитаТЧ ( ИмяРекв , МетаданныеДок , ИмяТЧ )

// Проверка наличия ТЧ
ТаблЧасть = МетаданныеДок . ТабличныеЧасти . Найти ( ИмяТЧ );
Если ТаблЧасть = Неопределено Тогда // Нет такой таб. части в документе
Возврат Ложь; //реквизит не найден
Иначе
Возврат НЕ ( ТаблЧасть . Реквизиты . Найти ( ИмяРекв ) = Неопределено); //реквизит найден
КонецЕсли;

&НаСервере
Процедура ВыгрузитьВXLS ( Выб_Таблица )

Если Выб_Таблица . Количество () = 0 Тогда
Сообщить ( "Пустая таблица! Операция прервана!" );
Возврат;
КонецЕсли;

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

Попытка
Book_Excel = Excel_App . WorkBooks . Add ();

Sheet_Excel = Book_Excel . WorkSheets ( 1 );
Sheet_Excel . Name = "Test Sheet" ; // Присваиваем имя первому листу

Excel_App . ActiveWindow . View = 2 ; // Режим страничного просмотра
Excel_App . ActiveWindow . Zoom = 100 ; // Масштаб
Sheet_Excel . PageSetup . Orientation = 2 ; // Альбомная ориентация
Sheet_Excel . Columns ( 1 ). ColumnWidth = 20 ; // Ширина первой колонки
Sheet_Excel . Columns ( 2 ). ColumnWidth = 40 ; // Ширина второй колонки
Sheet_Excel . Columns ( 10 ). ColumnWidth = 15 ;
Sheet_Excel . Columns ( 11 ). ColumnWidth = 15 ;

Sheet_Excel . Cells ( 1 , 1 ). Value = "№ ПП" ; //Создаем шапку 1
Sheet_Excel . Cells ( 1 , 2 ). Value = "Текст" ;
Sheet_Excel . Cells ( 1 , 3 ). Value = "Числа" ;
Sheet_Excel . Cells ( 1 , 10 ). Value = "Дата" ;
Sheet_Excel . Cells ( 1 , 11 ). Value = "Формула" ;

Sheet_Excel . Cells ( 2 , 3 ). Value = "Число 1" ; //Создаем шапку 2
Sheet_Excel . Cells ( 2 , 4 ). Value = "Число 2" ;
Sheet_Excel . Cells ( 2 , 5 ). Value = "Число 3" ;
Sheet_Excel . Cells ( 2 , 6 ). Value = "Число 4" ;
Sheet_Excel . Cells ( 2 , 7 ). Value = "Число 5" ;
Sheet_Excel . Cells ( 2 , 8 ). Value = "Число 6" ;
Sheet_Excel . Cells ( 2 , 9 ). Value = "Число 7" ;

Sheet_Excel . Range ( "A1:A2" ). MergeCells = Истина; Sheet_Excel . Range ( "A1:A2" ). WrapText = Истина;
Sheet_Excel . Range ( "B1:B2" ). MergeCells = Истина; Sheet_Excel . Range ( "B1:B2" ). WrapText = Истина;
Sheet_Excel . Range ( "J1:J2" ). MergeCells = Истина; Sheet_Excel . Range ( "J1:J2" ). WrapText = Истина;
Sheet_Excel . Range ( "K1:K2" ). MergeCells = Истина; Sheet_Excel . Range ( "K1:K2" ). WrapText = Истина;
Sheet_Excel . Range ( "C1:I1" ). MergeCells = Истина; Sheet_Excel . Range ( "C1:I1" ). WrapText = Истина;

Sheet_Excel . Range ( "A1:K2" ). Borders . Linestyle = 1 ; //Линия границы
Sheet_Excel . Range ( "A1:K2" ). HorizontalAlignment = 3 ; //Выравнивание по горизонтали
Sheet_Excel . Range ( "A1:K2" ). VerticalAlignment = 2 ; //Выравнивание по вертикали
Sheet_Excel . Range ( "A1:K2" ). Font . Bold = 1 ; //Установим жирный шрифт в шапке

// Выгружаем данные в таблицу
Количество_Строк = 3 ; //Заполнение ТЧ начинаем с третьей строки
Для Каждого ТекСтрока Из Выб_Таблица Цикл
Sheet_Excel . Cells ( Количество_Строк , 1 ). Value = ТекСтрока . НомерПоПорядку ;
Sheet_Excel . Cells ( Количество_Строк , 2 ). Value = ТекСтрока . Табл_Текст ;
Sheet_Excel . Cells ( Количество_Строк , 3 ). Value = ТекСтрока . Табл_Число1 ;
Sheet_Excel . Cells ( Количество_Строк , 4 ). Value = ТекСтрока . Табл_Число2 ;
Sheet_Excel . Cells ( Количество_Строк , 5 ). Value = ТекСтрока . Табл_Число3 ;
Sheet_Excel . Cells ( Количество_Строк , 6 ). Value = ТекСтрока . Табл_Число4 ;
Sheet_Excel . Cells ( Количество_Строк , 7 ). Value = ТекСтрока . Табл_Число5 ;
Sheet_Excel . Cells ( Количество_Строк , 8 ). Value = ТекСтрока . Табл_Число6 ;
Sheet_Excel . Cells ( Количество_Строк , 9 ). Value = ТекСтрока . Табл_Число7 ;
Sheet_Excel . Cells ( Количество_Строк , 10 ). Value = ТекСтрока . Табл_Дата ;
Sheet_Excel . Cells ( Количество_Строк , 11 ). Formula = "=F" + Строка ( Количество_Строк )
+ "+G" + Строка ( Количество_Строк );

Количество_Строк = Количество_Строк + 1 ;
КонецЦикла;

Количество_Строк = Количество_Строк - 1 ; //Последняя строка для форматирования

Sheet_Excel . Range ( "A3:K" + Строка ( Количество_Строк )). Borders . Linestyle = 1 ; //Линия границы
Sheet_Excel . Range ( "A3:K" + Строка ( Количество_Строк )). VerticalAlignment = 2 ; //Выравнивание по вертикали

Для НомерСтроки = 3 По Количество_Строк Цикл // Установка числового формата
Для Столбец = 4 По 9 Цикл
Sheet_Excel . Cells ( НомерСтроки , Столбец ). NumberFormat = "0.00" ;
КонецЦикла;
Sheet_Excel . Cells ( НомерСтроки , 11 ). NumberFormat = "0.00" ;
КонецЦикла;

Исключение
Excel_App . Quit ();
Сообщить ( ОписаниеОшибки ());
Возврат;
КонецПопытки;

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

Попытка
Excel_App . Quit ();
Исключение
Сообщить ( ОписаниеОшибки ());
Возврат;
КонецПопытки;

Что такое DCOM

Настройка DCOM для Excel.Application

И так у меня есть виртуальная машина под управлением Windows Server 2012 R2, на ней установлены серверные компоненты 1С, есть задача для Excel.Application модуля, настроить DCOM компоненты.

Для выполнения нижеописанных действий, вам будут необходимы права локального администратора, на том сервере, где все будет выполняться

Нам необходимо открыть в панели управления Windows, компонент "Службы компонентов".

Полный путь "Панель управления\Все элементы панели управления\Администрирование\Службы компонентов"

Либо можете, через окно выполнить ввести DCOMCNFG, откроется, тоже самое.

В службах компонентов (Component Services) откройте ветку:

Компоненты - Мой компьютер - Настройка DCOM - Microsoft Excel Application (Component Services - Computers - My computer - DCOM Config)

Если по каким-то причинам у вас нет пункта Microsoft Excel Application, откройте реестр Windows, найдите ветку: HKEY_CLASSES_ROOT\AppID\EXCEL.EXE, если ее нет, то создаем ее. В ней должен быть ключ REG_SZ со значением .

КОМУ ЛЕНЬ самому создавать, то можете скачать данный ключ реестра у меня. Просто запустите потом и подтвердите, что согласны добавить. Затем откройте командную строку от имени администратора и выполните команду:

У вас откроется mmc оснастка "Службы компонентов", которая делает то же что и DCOMCONFIG, но позволяет видеть 32 битные компоненты. В результате этих манипуляций компонент Microsoft Excel Application у вас должен появиться. Открываем свойства Microsoft Excel Application.

Переходим на вкладку "Безопасность (Secutity)". В пункте "Разрешения на запуск и активацию (Launch and Activation Permissions)", выберите "Настроить (Customize )" и изменить. Вам нужно добавить сюда учетную запись, от имени которой запускается служба 1С и предоставить ей права:

Локальный запуск - Local Launch
Локальная активация - Local Activation

Далее переходим к пункту "Разрешения на доступ (Access Permissions)" и так же меняем их. Добавьте там ту же учетную запись, от имени которой запускается сервис 1С, и дайте ей права "Локальный доступ (Local Access)". Сохраните настройки.

Закрываем окно служб компонентов, теперь для того, чтобы закончит ьнастройку DCOM для 1С и Microsoft Excel Application, вам необходимо дать права на две папки.

Читайте также: