Опубликовано: 04.09.2024

Загрузка данных из Excel в «1С Предприятие 7.7»

При работе с «1С:Предприятие» нередко возникает необходимость в загрузке каких-то данных из файла Excel в соответствующие справочники. Пожалуй, самый распространенный случай — это загрузка номенклатуры в справочник из прайса.

Контекст работы с приложением Microsoft Excel

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

При создании переменной со ссылкой на приложение Microsoft Excel, в качестве имени объекта в операторе СоздатьОбъект() должен выступать уникальный OLE-идентификатор Excel.Application. Например:

Процедура ЗагрузитьДанные()

   Попытка
      // Пытаемся обратиться к MS Excel
      Excel = СоздатьОбъект("Exel.Application");
   Исключение
      // Если MS Excel не установлен на ПК
      Предупреждение(ОписаниеОшибки() + "; MS Excel не установлен на данном ПК!");
      Excel = 0;
      Возврат;
   КонецПопытки;

   // Начинаем работать с Excel
     . . .

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

При инициализации данного OLE-объекта с помощью функции СоздатьОбъект() мы получаем ссылку на главный элемент объектной модели приложения MS Excel — объект Application, а следовательно, можем использовать его собственные свойства и методы непосредственно. Чтобы обратиться к свойству или методу объекта Application достаточно записать имя свойства или метода через точку после имени нашей переменной, например:

// Проверим версию MS Excel
ВерсияExcel = Excel.Version;

Примечание: Иногда в исходных текстах программ можно встретить запись типа Excel.Application.Version. Данная запись аналогична записи приведённой в примере выше, но избыточна. Ссылка на объект Application здесь указана явно, что, в общем-то, является лишним.

Что нужно знать об объекте Application

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

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

ActiveSheet — доступное только для чтения свойство, возвращающее ссылку на объект, который представляет активный лист в активной книге.

ActiveWorkbook — доступное только для чтения свойство, возвращающее ссылку на объект Workbook, который представляет рабочую книгу в активном окне.

Cells — одно из самых важных свойств объекта Application. Оно возвращает объект Range, представляющий в данном случае все ячейки в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к ячейкам активного листа может выглядеть так:

Excel.Cells(2, 3).Value = 1984;

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

Важно! Еще раз отмечу, что свойство Cells возвращает не коллекцию объектов Cell, как считают некоторые пользователи, а объект Range. На использовании объектов Range в Excel построена почти вся работа с ячейками и их значениями.

Columns — свойство, возвращающее объект Range, представляющий в данном случае все столбцы в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к столбцу активного листа может выглядеть так:

Excel.Columns(4).Font.Italic = -1;

В данном примере мы выделили курсивом все ячейки четвёртого столбца.

Важно! Еще раз отмечу, что свойство Columns возвращает не коллекцию объектов Column, как считают некоторые пользователи, а объект Range.

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

Пример использования:

// Подавляем вывод диалоговых окон и сообщений
Excel.DisplayAlerts = 0;

Для справки: При использовании кода VBA в сторонних приложениях встроенные константы false и true недоступны, а потому для установки значений свойств объектов следует использовать их числовые аналоги: 0 и −1. Кстати, начинающие разработчики вместо единицы со знаком минус по незнанию часто пишут просто единицу и, надо заметить, приложения Microsoft Office вполне нормально это «проглатывают».

EnableEvents — свойство, позволяющее на время отключить события для объекта Application, чтобы они не срабатывали. На практике события часто отключают перед выполнением какого-то действия — перед открытием файла, сохранением и т.п. Для этого значение свойства устанавливают в false, а после выполнения действия возвращают в true.

OperatingSystem — доступное только для чтения свойство, возвращающее имя и номер версии текущей операционной системы, например, "Windows (32-bit) 4.00" или "Macintosh 7.00".

Range — свойство, возвращающее объект Range, который представляет собой указанную ячейку или указанный диапазон яче¬ек активного листа активной книги.

Пример использования:

// Выбираем диапазон A1:D10 в активном листе книги
ВыбранныйДиапазон = Excel.Range("A1:D10");

ReferenceStyle — свойство, позволяющее получить или установить режим отображения номеров ячеек («A1» или «R1C1»). Возможными значениями свойства могут быть:

  • 1 — режим «A1»;
  • −4150 — режим «R1C1».

Rows — свойство, возвращающее объект Range, представляющий в данном случае все строки в активном листе активной книги. Поскольку свойством по умолчанию (то есть свойством, название которого можно опускать) для объекта Range является свойство Item, то обращение к строке активного листа может выглядеть так:

Excel.Rows(2).Font.Name = 'Times New Roman';

В данном примере мы установили семейство шрифта для всех ячеек второй строки.

Важно! Еще раз отмечу, что свойство Rows возвращает не коллекцию объектов Row, как считают некоторые пользователи, а объект Range.

Sheets — доступное только для чтения свойство, возвращающее коллекцию Sheets, представляющую все листы активной книги. Коллекция Sheets может содержать объекты Worksheet (рабочие листы) и Chart (диаграммы).

Version — доступное только для чтения свойство, возвращающее версию Microsoft Excel.

Visible — свойство, позволяющее сделать окно приложения видимым или невидимым. Для этого свойству необходимо задать значение true (в режиме OLE ему соответствует −1) или false (в режиме OLE ему соответствует 0). По умолчанию свойство имеет значение false, поэтому при программном открытии или создании документа после отработки соответствующих команд (Add() или Open()) окно приложения на экране не появляется. Для вывода окна на экран необходимо принудительно задать свойству Visible значение true. Однако если на экране в момент создания или открытия окна программным способом присутствуют окна экселевских документов, открытых ранее «вручную» (с помощью команды меню или ярлыка), окно программно создаваемого или открываемого документа появляется на экране сразу же (то есть в момент отработки команды Add() или Open()).

На практике многие разработчики предпочитают принудительно присваивать свойству Visible значение false в самом начале процедуры формирования нового или обработки имеющего документа (см. пример ниже). Это делается для того, чтобы пользователь не видел то, что ему видеть и не надо, а главное, чтобы не мог случайно вмешаться в процесс. Лишь после завершения всех действий по обработке документа свойству Visible возвращается значение true, и все принудительно скрытые ранее экселевские окна появляются на экране.

Пример использования свойства:

Excel = СоздатьОбъект("Excel.Application");
// Делаем окна приложения невидимыми
Excel.Visible = 0;
// Открываем наш файл Excel
НашаКнига = Excel.Workbooks.Open("price.xls");

// Производим загрузку данных

  . . .

// Закрываем наш файл
НашаКнига.Close();

Если Excel.Workbooks.Count = 0 Тогда
   // Если нет других открытых документов,
   // закрываем окно приложения
   Excel.Quit();
Иначе
   // Возвращаем окна других документов Excel на экран
   Excel.Visible = -1;
КонецЕсли;

// Удаляем OLE-объект из памяти
Excel = 0;

Стоит ли скрывать ли окно приложения на время работы с документом или нет — это решать Вам. Но при отладке процедуры окно приложения скрывать точно не следует — легче будет выявить допущенные в коде ошибки и недочёты.

Примечание: Если окна приложения сделать невидимыми, невидимыми становятся также и кнопки этих окон на Панели задач. Кнопка запуска приложения, закреплённая на Панели задач, при этом остаётся видимой.

Workbooks — доступное только для чтения свойство, возвращающее коллекцию Workbooks, представляющую все открытые в приложении книги.

Worksheets — доступное только для чтения свойство, возвращающее коллекцию Sheets, которая представляет все листы в активной книге.

Открытие книги Excel

Все операции с рабочими книгами (создание новой, открытие имеющейся и их закрытие) в Microsoft Excel осуществляются посредством коллекции Workbooks, которая создаётся в момент открытия приложения и содержит все созданные или открытые рабочие книги. Ссылку на коллекцию возвращает одноимённое свойство объекта Application.

Открыть документ Excel позволяет метод Open() коллекции Workbooks. Данный метод добавляет открытый документ в коллекцию Workbooks и возвращает ссылку на него.

Синтаксис метода:

Open([<FileName>], [<UpdateLinks>], [<ReadOnly>],
     [<Format>], [<Password>], [<WriteResPassword>],
     [<IgnoreRORecommended>], [<Origin>], [<Delimiter>],
     [<Editable>], [<Notify>], [<Converter>],
     [<AddToMru>],  [<Local>], [<CorruptLoad>])

где

  • <FileName> — необязательный параметр, указывающий имя файла (полный путь к файлу), который необходимо открыть;
  • <UpdateLinks> — необязательный параметр, указывающий, как будут обновляться ссылки в документе. Если этот параметр опущен, пользователю будет предложено выбрать вариант обновления в диалоге. Возможные значения параметра:
    • 0 — внешние ссылки не будут обновляться при открытии книги;
    • 3 — внешние ссылки будут обновлены при открытии книги;
  • <ReadOnly> — флаг «Только для чтения». Если указать значение true, то документ будет открыт только для чтения;
  • <Format> — необязательный параметр, задающий символ разделителя, если приложением Excel открывается текстовый файл. Возможные значения параметра:
    • 1 — символ табуляции;
    • 2 — запятая;
    • 3 — пробел;
    • 4 — точка с запятой;
    • 5 — разделитель отсутствует;
    • 6 — пользовательский символ, указанный в параметре <Delimiter>;
  • <Password> — необязательный параметр, в котором может быть указан пароль, необходимый для открытия защищённой книги. Если параметр опущен, а рабочая книга требует пароль, пользователю будет предложено ввести этот пароль в диалоге;
  • <WriteResPassword> — необязательный параметр, в котором может быть указан пароль, необходимый для записи в книгу. Если параметр опущен, а рабочая книга требует пароль, пользователю будет предложено ввести этот пароль в диалоге;
  • <IgnoreRORecommended> — необязательный параметр, позволяющий путём установки в true указать Excel не отображать сообщение, рекомендующее только чтение (если книга была сохранена с параметром «Рекомендован только для чтения»);
  • <Origin> — необязательный параметр, использующийся при открытии текстовых файлов и позволяющий указать источник, где был создан файл, для правильного трактования используемых символов. Если параметр опущен, подразумевается текущая операционная система. Возможные значения параметра:
    • 1 — операционная система Macintosh;
    • 2 — операционная система MS-DOS;
    • 3 — операционная система Microsoft Windows;
  • <Delimiter> — необязательный параметр, задающий пользовательский символ, который будет использоваться в качестве разделителя при открытии текстовых файлов;
  • <Editable> — необязательный параметр, который следует установить в true, если необходимо открыть для редактирования шаблон Excel или файл надстройки Microsoft Excel 4.0. Значение по умолчанию — false;
  • <Notify> — флаг режима уведомления о полной доступности файла. Если установить в true, приложение уведомит пользователя, когда файл станет доступен для записи (если ранее он был открыт в многопользовательском режиме только для чтения);
  • <Converter> — необязательный параметр, позволяющий указать индекс первого конвертера файлов, который нужно попробовать при открытии файла. Если параметр указан, то сначала будет проверяться указанный конвертер файлов; если этот преобразователь не распознает файл, будут проверены все остальные конвертеры. Индекс конвертера состоит из номеров строк конвертеров, возвращаемых свойством FileConverters объекта Application;
  • <AddToMru> — необязательный параметр, позволяющий добавить этот файл в список недавно открывавшихся файлов. Для добавления файла в список параметр следует установить в true. Значение по умолчанию — false;
  • <Local> — флаг установки локальных (региональных) настроек. Значение true позволяет сохранить файл на языке, используемом в приложении Microsoft Excel (включая параметры панели управления). Значение false (по умолчанию) позволяет сохранить файл на языке, используемом в VBA (обычно английский США, если проект VBA, из которого выполняется метод, не является старым проектом VBA с международной версией XL5/95);
  • <CorruptLoad> — необязательный параметр, позволяющий установить режим при открытии повреждённых файлов. Возможные значения:
    • 0 — книга открывается как обычно и Excel не пытается выполнить восстановление (значение по умолчанию);
    • 1 — книга открывается в режиме восстановления;
    • 2 — книга открывается в режиме извлечения данных.

Для справки: При использовании кода VBA в сторонних приложениях встроенные константы false и true недоступны, а потому для установки значений параметров метода следует использовать их числовые аналоги: 0 и −1.

Пример использования метода:

Excel = СоздатьОбъект("Excel.Application");

// Открываем наш файл Excel
Файл = КаталогИБ() + "ExtForms\price.xls";
НашаКнига = Excel.Workbooks.Open(Файл);

Примечание: Для открытия файлов, форматы которых отличаются от формата MS Excel, у коллекции Workbooks имеются также дополнительные методы OpenDatabase(), OpenText() и OpenXML().

Работа с ячейками листа

Если в книге только один лист

Если в открытой книге имеется только один единственный лист, для обращения к конкретной ячейке этого листа можно воспользоваться свойством Cells объекта Application. Данное свойство, как уже было сказано ранее, возвращает объект Range, представляющий в данном случае все ячейки в активном листе активной книги. Открытый же нами документ является активным, поскольку был открыт последним, и в нём всего один лист.

Указать номер конкретной ячейки, к которой необходимо получить доступ, можно с помощью свойства Item объекта Range. Например, нижеприведённая запись позволяет получить значение ячейки, находящейся на пересечении второй строки и третьего столбца:

ПолученноеЗначение = Excel.Cells.Item(2, 3).Value;

Поскольку для объекта Range свойство Item является свойством по умолчанию (то есть свойством, название которого можно опускать), то обращение к ячейке может выглядеть и так:

ПолученноеЗначение = Excel.Cells(2, 3).Value;

Для доступа к значению ячейки в вышеприведённых примерах используется свойство Value объекта Range, позволяющее получить или установить это значение. Также для получения доступа к значению ячейки в некоторых случаях можно использовать доступное только для чтения свойство Text, которое в отличие от Value возвращает строковое представление этого значения в соответствии с установленным для ячейки форматом.

Увидеть, чем отличаются хранимые в свойствах Text и Value значения, позволяет следующий код:

// Получаем доступ к ячейке
НашаЯчейка = Excel.Cells(1, 1);
// Устанавливаем значение
НашаЯчейка.Value = 1198.3;
// Устанавливаем формат ячейки
НашаЯчейка.NumberFormat = "$#,##0_);($#,##0)";

// Смотрим результат
Сообщить("" + НашаЯчейка.Text);     // выведет "$1198,30 "
Сообщить("" + НашаЯчейка.Value);    // выведет "1198.3"

Процедура же выборки значений из таблицы Excel и загрузка этих значений в соответствующий справочник «1С:Предприятия» в простейшем случае может выглядеть следующим образом:

Excel = СоздатьОбъект("Excel.Application");
// Делаем окна приложения невидимыми
Excel.Visible = 0;
// Открываем наш файл 
Файл = КаталогИБ() + "ExtForms\price.xls";
НашаКнига = Excel.Workbooks.Open(Файл);

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

СпрНом = СоздатьОбъект("Справочник.Номенклатура");
СтрокаНачалаВыборки = 1;
СтрокаКонцаВыборки = 40;
СтолбецДляВыборки = 2;

Для ТекущаяСтрока=СтрокаНачалаВыборки По СтрокаКонцаВыборки Цикл
   ПолученноеЗначение = СокрЛП(Excel.Cells(ТекущаяСтрока, СтолбецДляВыборки).Value);
   Если ПустаяСтрока(ПолученноеЗначение)=1 Тогда
      // Пропускаем пустые ячейки
      Продолжить;
   КонецЕсли;

   СпрНом.Новый();
   СпрНом.Наименование = ПолученноеЗначение;
   СпрНом.Записать();
КонецЦикла;

// Закрываем наш файл
НашаКнига.Close();

Если Excel.Workbooks.Count = 0 Тогда
   // Если нет других открытых документов,
   // закрываем окно приложения
   Excel.Quit();
Иначе
   // Возвращаем окна других документов Excel на экран
   Excel.Visible = -1;
КонецЕсли;

// Удаляем OLE-объект из памяти
Excel = 0;

Если в книге несколько листов

Если в книге, из которой будет производиться загрузка, содержится несколько листов, то прежде чем начать загрузку, необходимо выбрать конкретный лист. Для выбора конкретного листа используется свойство Item коллекции Sheets, представляющей собой все листы рабочей книги. Получить доступ к данной коллекции можно через свойства Worksheets объектов Application или Workbook.

Пример:

Excel = СоздатьОбъект("Excel.Application");

// Открываем наш файл Excel
НашаКнига = Excel.Workbooks.Open("price.xls");
// Делаем текущим лист с именем "Лист1"
ТекущийЛист = НашаКнига.Worksheets.Item("Лист1");

Надо заметить, вышеприведённый код может быть использован только в локализованной версии MS Excel, где листам по умолчанию присваиваются имена «Лист1», «Лист2» и т.д. В англоязычной же версии приложения листы именуются «Sheet1», «Sheet2» и т.д. Поэтому, если Вы не знаете, с какой версией MS Excel Вам придётся работать, обязательно предусмотрите дополнительные проверки или просто используйте индексы (номера) листов вместо их имён, например, НашаКнига.Worksheets.Item(1), НашаКнига.Worksheets.Item(2) и т.д. Нумерация листов в книге идёт слева направо по расположению ярлыков.

Поскольку свойство Item коллекции Sheets является свойством по умолчанию (то есть свойством, название которого можно опускать), то допустимой является и следующая запись:

ТекущийЛист = НашаКнига.Worksheets("Лист1");

Когда конкретный лист (то есть объект Worksheet) выбран, дальнейшая работа с ячейками осуществляется практически также, как и в случае с одним листом. Для доступа к конкретной ячейке используется свойство Cells, только уже не объекта Application, а объекта Worksheet. Например:

Excel = СоздатьОбъект("Excel.Application");
// Делаем окна приложения невидимыми
Excel.Visible = 0;
// Открываем наш файл 
Файл = КаталогИБ() + "ExtForms\price.xls";
НашаКнига = Excel.Workbooks.Open(Файл);
// Делаем текущим первый лист книги
ТекущийЛист = НашаКнига.Worksheets(1);

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

СпрНом = СоздатьОбъект("Справочник.Номенклатура");
СтрокаНачалаВыборки = 1;
СтрокаКонцаВыборки = 40;
СтолбецДляВыборки = 2;

Для ТекущаяСтрока=СтрокаНачалаВыборки По СтрокаКонцаВыборки Цикл
   ПолученноеЗначение = СокрЛП(ТекущийЛист.Cells(ТекущаяСтрока, СтолбецДляВыборки).Value);
   Если ПустаяСтрока(ПолученноеЗначение)=1 Тогда
      // Пропускаем пустые ячейки
      Продолжить;
   КонецЕсли;

   СпрНом.Новый();
   СпрНом.Наименование = ПолученноеЗначение;
   СпрНом.Записать();
КонецЦикла;

// Закрываем наш файл
НашаКнига.Close();

Если Excel.Workbooks.Count = 0 Тогда
   // Если нет других открытых документов,
   // закрываем окно приложения
   Excel.Quit();
Иначе
   // Возвращаем окна других документов Excel на экран
   Excel.Visible = -1;
КонецЕсли;

// Удаляем OLE-объект из памяти
Excel = 0;

Получить количество листов в книге позволяет свойство Count коллекции Sheets. Если необходимо произвести загрузку данных со всех листов книги, можно организовать, например, следующий цикл:

. . .

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

Для НомерЛиста=1 По КоличествоЛистов Цикл
   ТекущийЛист = НашаКнига.Worksheets(НомерЛиста);

   . . .

КонецЦикла;

. . .

Закрытие документа

Закрыть открытый ранее документ позволяет метод Close() объекта Workbook.

Синтаксис метода:

Close([<SaveChanges>], [<Filename>], [<RouteWorkbook>])

где

  • <SaveChanges> — параметр, задающий возможные действия при закрытии документа. Если в рабочей книге есть изменения, а параметр установлен в true, изменения сохраняются в файл. Если ещё нет имени файла, связанного с рабочей книгой, используется имя из параметра <Filename>. Если же имя файла опущено, пользователю будет предложено указать имя файла;
  • <Filename> — имя файла для сохранения изменений;
  • <RouteWorkbook> — в данном параметре следует указать true для направления документа следующему получателю. Если в документе нет прикреплённого списка маршрутизации, этот параметр игнорируется.

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

Пример использования метода:

НашаКнига.Close();

Для справки: Когда в открытый документ вносятся изменения или когда документ создаётся на основании шаблона, свойство Saved объекта Workbook получает значение false. Именно по значению этого свойства приложение определяет, выводить окно сохранения документа перед закрытием приложения или нет.

Если необходимо закрыть документ, не сохраняя изменений, следует просто присвоить свойству Saved значение true. Тогда Excel не будет выводить никаких окон.

Закрытие окна приложения

Для закрытия окна приложения следует вызвать метод Quit() объекта Application. Однако прежде чем закрыть окно приложения, следует убедиться, нет ли на компьютере других открытых документов Excel. Ведь если таковые есть, они тоже будут закрыты. Выполнить проверку позволяет свойство Count коллекции Workbooks, возвращающее количество экземпляров коллекции.

Пример:

Если Excel.Workbooks.Count = 0 Тогда
   // Если нет открытых документов,
   // закрываем окно приложения
   Excel.Quit();
КонецЕсли;

// Удаляем OLE-объект из памяти
Excel = 0;

Другие статьи по схожей тематике