Загрузка из EXCEL в 1С. MS EXCEL.Application

---

20.08.2014. Новая редакция с возможностью загрузки изображений из файла.

В данной статье приведен функционал, с помощью которого в обработке
«Импорт из EXCEL и др.источников (xls,xlsx,ods,sxc,dbf,mxl,csv,sql) в 1С»: //infostart.ru/public/120961/
производится считывание данных из файлов табличного типа *xls, *.xlsx.

Методы загрузки из внешнего источника:
— Метод «MS ADO» (Чтение файлов xls, xlsx средствами Microsoft ADO): //infostart.ru/public/163640/
— Метод «MS EXCEL» (Чтение файлов xls, xlsx с картинками средствами Microsoft Office): //infostart.ru/public/163641/
— Метод «LO CALC» (Чтение файлов xls, xlsx, ods, sxc с картинками средствами LibreOffice): //infostart.ru/public/163642/
— Метод «NativeXLSX» (Чтение файлов xlsx с картинками средствами 1С. ПостроительDOM): //infostart.ru/public/300092/
— Метод «NativeXLSX». Предыдущий вариант (Чтение файлов xlsx средствами 1С. ЧтениеXML)://infostart.ru/public/225624/
— Метод «Excel1C» (Загрузка на платформе 8.3.6 с картинками. Чтение файлов xls, xlsx, ods): //infostart.ru/public/341855/
— Список листов файла: //infostart.ru/public/163724/

---

Публикаций на тему загрузки из EXCEL — множество, но
«
— Вам билетёр нужен?
— Был нужен, да уже взяли.
— Может и я на что сгожусь?
— Может и сгодишься, если скалиться не будешь …
«
«НЕУЛОВИМЫЕ МСТИТЕЛИ» (1966).

---

&НаКлиенте
Процедура ПриОткрытии(Отказ)
    ФайлEXCEL= «D:Товар.xls»;
    ИмяЛиста = Новый Структура («ИмяЛиста, НомерЛиста»,«СФКартинок (2)», 16);
    СтрокаЗаголовка = 1;
    НачСтрока = 3;
    КонСтрока = 4;
    КолвоСтрокExcel = 0;
    ЗагрузитьМетодом_MSEXCEL_НаСервере(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel);
КонецПроцедуры

&НаСервере
Процедура ЗагрузитьМетодом_MSEXCEL_НаСервере(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel)

    ТаблицаРезультат = ЗагрузитьМетодом_MSEXCEL(ФайлEXCEL, ИмяЛиста, СтрокаЗаголовка, НачСтрока, КонСтрока, КолвоСтрокExcel);

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

// Метод «Microsoft EXCEL.Application».
//
// Параметры:
//      ФайлEXCEL — Полное имя файла (путь к файлу с именем файла и расширением)
//      ИмяНомерЛиста — Имя/Номер выбранного листа файла EXCEL.
//      СтрокаЗаголовка (по умолчанию = 1) — Номер строки EXCEL, в которой расположены заголовки колонок.
//          Не используется.
//      В обработке 1-я строка анализируется для сопоставления колонок EXCEL с реквизитами 1С (справочники, докуметны, регистры).
//      НачСтрока (по-умолчанию = 0) — Номер начальной строки, начиная с которой считываются данные из EXCEL.
//      КонСтрока (по-умолчанию = 0) — Номер конечной строки, которой заканчиваются считываемые данные из EXCEL.
//          Если НачСтрока=0 и КонСтрока=0, то считывается вся таблица, находящаяся на листе EXCEL.
//      КолвоСтрокEXCEL — Количество строк на листе «ИмяЛиста» EXCEL. Возвращается в вызываемую процедуру.
//
// Возвращаемые значения:
//      ТаблицаРезультат — Результат считывания с листа «ИмяЛиста» EXCEL.
//
&НаСервере
Функция ЗагрузитьМетодом_MSEXCEL(Знач ФайлEXCEL, Знач ИмяНомерЛиста, Знач СтрокаЗаголовка = 1, НачСтрока = 2, КонСтрока = 0, КолвоСтрокEXCEL)
    Перем ИмяФайлаEXCEL, ИмяЛиста, НомерЛиста;
    Перем EXCELApplication, Book, Sheet;
    Перем ВсегоЛистов, НачальнаяКолонка, НачальнаяСтрока, КонечнаяКолонка, КонечнаяСтрока;
    Перем ДиапазонДанных, СтрокДиапазона, ДиапазонЗаголовка, МассивЗаголовка, МассивЗначений;
    Перем ТекИмя, ПозДоллар, Версия;
    Перем ТаблицаРезультат, МассивИменКолонок, ИмяКолонки, Колонка, Заголовок, НоваяСтрока, нСтроки;

    ИмяФайлаEXCEL = ПолучитьИмяФайлаБезРасширения(ФайлEXCEL);

    // Нумерация MS EXCEL начинается с 1.
    ИмяЛиста    = ИмяНомерЛиста.ИмяЛиста;
    НомерЛиста  = ИмяНомерЛиста.НомерЛиста;

    Попытка
        // Инициализация основного объекта EXCEL.Application: Открытие соединения.
        EXCELApplication = Новый COMОбъект(«EXCEL.Application»);
        EXCELApplication.Visible       = Ложь;
        EXCELApplication.DisplayAlerts = Ложь;
    Исключение
        Попытка
            EXCELApplication.Application.Quit();
        Исключение
        КонецПопытки;
        EXCELApplication = Неопределено;
        Сообщить(НСтр(«ru = ‘»+ОписаниеОшибки()+«‘»), СтатусСообщения.Внимание);
        Возврат Новый ТаблицаЗначений;  // В случае ошибки возвращаем пустую таблицу значений.
    КонецПопытки;

    // Определение версии EXCEL.
    Версия = Лев(EXCELApplication.Version,Найти(EXCELApplication.Version,«.»)-1);

    // Инициализация дочернего объекта типа Книга EXCEL.
    Book = EXCELApplication.WorkBooks.Open(ФайлEXCEL);
    ВсегоЛистов = Book.Sheets.Count;

    // Инициализация дочернего объекта Книги типа Лист EXCEL.
    Если Лев(ИмяЛиста, 1) = «‘» И Прав(ИмяЛиста, 1) = «‘» Тогда
        ИмяЛиста = УдалитьКавычки(ИмяЛиста, «‘»);
    КонецЕсли;
    Если Прав(ИмяЛиста, 1) = «$» Тогда
        УдалитьПоследнийСимволВСтроке(ИмяЛиста, 1);
    КонецЕсли;
    Sheet = Book.WorkSheets(ИмяЛиста);

    // Определение диапазона данных таблицы EXCEL.
    НачальнаяКолонка    = 1;
    НачальнаяСтрока     = 1;
    Если Версия = «8» Тогда
        КонечнаяКолонка = Sheet.Cells.CurrentRegion.Columns.Count;
        КонечнаяСтрока  = Sheet.Cells.CurrentRegion.Rows.Count;
    Иначе
        КонечнаяКолонка = Sheet.Cells.SpecialCells(11).Column;
        КонечнаяСтрока  = Sheet.Cells.SpecialCells(11).Row;
    Конецесли;

    // Параметр возвращаемый в вызывающую процедуру.
    КолвоСтрокEXCEL     = КонечнаяСтрока;

    // Проверка заполненности листа.
    Если КолвоСтрокEXCEL <= 1 И КонечнаяКолонка <= 1 Тогда
        КолвоСтрокEXCEL = 0;
        Сообщить(НСтр(«ru = ‘» + ИмяЛиста + «: Лист, содержащий 1 строку считаем пустым.'»), СтатусСообщения.Внимание);

        // Завершение работы.
        // Закрытие Объектов.
        Попытка
            Book.Close();
        Исключение
        КонецПопытки;
        Попытка
            EXCELApplication.Application.Quit();
        Исключение
        КонецПопытки;
        EXCELApplication = Неопределено;

        Возврат Новый ТаблицаЗначений;  // В случае ошибки возвращаем пустую таблицу значений.
    КонецЕсли;

    НачальнаяСтрока     = ?(НачСтрока = 0   , НачальнаяСтрока   , НачСтрока);
    КонечнаяСтрока      = ?(КонСтрока = 0   , КонечнаяСтрока    , КонСтрока);

    КонечнаяСтрока      = ?(КонечнаяСтрока > КолвоСтрокEXCEL, КолвоСтрокEXCEL, КонечнаяСтрока);
    НачальнаяСтрока     = ?(НачальнаяСтрока > КолвоСтрокEXCEL, КолвоСтрокEXCEL, НачальнаяСтрока);

    // Диапазон: Считываемые данные.
    ДиапазонДанных = Sheet.Range(Sheet.Cells(НачальнаяСтрока, НачальнаяКолонка), Sheet.Cells(КонечнаяСтрока, КонечнаяКолонка));
    СтрокДиапазона = ДиапазонДанных.Rows.Count;

    // Создание результирующей таблицы, в которую будут записываться считанные из EXCEL данные.
    ТаблицаРезультат = Новый ТаблицаЗначений;

    // Формирование колонок результирующей таблицы.

    // «НомерСтроки» — для наглядности и удобства.
    // В зависимости от разрабатываемой обработки.
    // «Сопоставлено» — может быть другим.
    // Здесь же могут быть добавлены другие колонки, не формируемые из содержимого файла EXCEL.
    ТаблицаРезультат.Колонки.Добавить(«НомерСтроки», Новый ОписаниеТипов(«Число»), «№», 4);
    ТаблицаРезультат.Колонки.Добавить(«Сопоставлено», Новый ОписаниеТипов(«Булево»), «Сопоставлено», 1);

    МассивИменКолонок = Новый Массив;
    Для ит = 1 ПО КонечнаяКолонка Цикл

        ТекИмя = Строка(ит);
        МассивИменКолонок.Добавить(ТекИмя);

        Если ДиапазонДанных.Columns(ит).Hidden Тогда    // Скрытые колонки EXCEL пропустить.
            Продолжить;
        КонецЕсли;

        ИмяКолонки = «N» + ТекИмя;
        ТаблицаРезультат.Колонки.Добавить(ИмяКолонки);

    КонецЦикла;

    // ТаблицаРезультат: 1-я Строка-Заголовок.
    // Если в Вашей обработке в результирующей таблице в качестве 1-ой строки не нужна Строка-Заголовок, то
    // следует закомментировать следующий цикл:
    ДиапазонЗаголовка = Sheet.Range(Sheet.Cells(1, НачальнаяКолонка), Sheet.Cells(1, КонечнаяКолонка));
    МассивЗаголовка = ДиапазонЗаголовка.Value.Выгрузить();

    НоваяСтрока = ТаблицаРезультат.Добавить();
    НоваяСтрока.НомерСтроки = 1;

    ит = —1;
    Для Каждого МассивЗначений ИЗ МассивЗаголовка Цикл

        Для Каждого ЭлементМассива ИЗ МассивЗначений Цикл
            ит = ит + 1;

            ИмяКолонки = «N» + МассивИменКолонок[ит];
            Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);
            Если Колонка = Неопределено Тогда   // Имена скрытых колонок отсутствуют в массиве.
                Продолжить;
            КонецЕсли;

            НоваяСтрока[ИмяКолонки] = СокрЛП(ЭлементМассива);
        КонецЦикла;

    КонецЦикла;

    // ТаблицаРезультат: формирование строк по указанному диапазону: НачСтрока — КонСтрока.

    ДиапазонДанных = ДиапазонДанных.Value;

    нСтроки = НачальнаяСтрока—1;
    Для НомерСтроки = 1 ПО СтрокДиапазона Цикл

        нСтроки = нСтроки + 1;
        НоваяСтрока = ТаблицаРезультат.Добавить();
        НоваяСтрока.НомерСтроки = нСтроки;

        Для НомерКолонки = 1 ПО КонечнаяКолонка Цикл

            ИмяКолонки = «N» + МассивИменКолонок[НомерКолонки—1];
            Колонка = ТаблицаРезультат.Колонки.Найти(ИмяКолонки);
            Если Колонка = Неопределено Тогда   // Имена скрытых колонок отсутствуют в массиве.
                Продолжить;
            КонецЕсли;

            ЗначениеЯчейки = ДиапазонДанных.GetValue(НомерКолонки, НомерСтроки);            // Считывание данных в соответствии с их типом.

            Если ЗначениеЯчейки = Неопределено Тогда
                ЗначениеЯчейки = ПрочитатьКартинку_MSEXCEL(EXCELApplication, Sheet, НомерКолонки, нСтроки, ИмяФайлаEXCEL, НомерЛиста, «УИД»);
            КонецЕсли;

            Если ЗначениеЗаполнено(ЗначениеЯчейки) Тогда

                // Ширина колонки используется при формировании таблицы на форме обработки.
                ШиринаКолонки = ТаблицаРезультат.Колонки[ИмяКолонки].Ширина;
                ДлинаСтроки = СтрДлина(СокрЛП(ЗначениеЯчейки));
                ТаблицаРезультат.Колонки[ИмяКолонки].Ширина = ?(ШиринаКолонки < ДлинаСтроки, ДлинаСтроки, ШиринаКолонки);

                НоваяСтрока[ИмяКолонки] = ЗначениеЯчейки;

            КонецЕсли;

        КонецЦикла;

    КонецЦикла;

    УдалитьКолонкиСНулевойШириной(ТаблицаРезультат);

    // Завершение работы.
    // Закрытие Объектов.
    Попытка
        Book.Close();
    Исключение
    КонецПопытки;
    Попытка
        EXCELApplication.Application.Quit();
    Исключение
    КонецПопытки;
    EXCELApplication = Неопределено;

    Возврат ТаблицаРезультат;

КонецФункции

// Функция осуществляет экспорт изображения во внешний графический файл.
//
// Параметры:
//      EXCELApplication — Объект типа «EXCEL.Application».
//      Sheet — Обект типа «Лист книги EXCEL».
//      НомерКолонки — Номер колонки листа.
//      НомерСтроки — Номер строки листа.
//      ИмяФайлаEXCEL — Короткое имя файла без расширения, из которого производится импорт.
//      НомерЛиста — Номер листа книги EXCEL.
//      ПравилоИмяФайлаКартинки — правило формирования имени выходного графического файла.
//      — «УИД» (по умолчанию).
//      — Иначе на основании имени исходного файла EXCEL, Номера листа, Номера строки, Номера колонки.
//
// Возвращаемые значения:
//      ПолноеИмяФК — Полное имя графического файла.
//
&НаСервере
Функция ПрочитатьКартинку_MSEXCEL(Знач EXCELApplication, Знач Sheet, Знач НомерКолонки, Знач НомерСтроки, Знач ИмяФайлаEXCEL, Знач НомерЛиста, Знач ПравилоИмяФайлаКартинки = «УИД»)
    Перем GraphicObject, Chart;
    Перем ПолноеИмяФК, ФайлКартинки;

    Если Sheet.Shapes.Count() = 0 Тогда
        Возврат Неопределено;
    КонецЕсли;

    Для Каждого GraphicObject ИЗ Sheet.Shapes Цикл
        Если GraphicObject.Type = 13 И GraphicObject.TopLeftCell.Column = НомерКолонки И GraphicObject.TopLeftCell.Row = НомерСтроки Тогда

            // GraphicObject.Name.
            Если ПравилоИмяФайлаКартинки = «УИД» Тогда
                ПолноеИмяФК = КаталогВременныхФайлов() + Новый УникальныйИдентификатор() + «.jpg»;
            Иначе
                ПолноеИмяФК = КаталогВременныхФайлов() + ИмяФайлаEXCEL + «Л» + НомерЛиста + «С» + НомерСтроки + «К» + НомерКолонки + «.jpg»;
            КонецЕсли;

            Попытка
                GraphicObject.ScaleHeight(1,1);     // Истинный размер по высоте.
                GraphicObject.ScaleWidth(1,1);      // Истинный размер по ширине.
                GraphicObject.Copy();

                Chart = EXCELApplication.ActiveSheet.ChartObjects().Add(0, 0, GraphicObject.Width, GraphicObject.Height).Chart();
                Chart.Paste();
                Chart.Export(ПолноеИмяФК);

                Chart = Неопределено;

                ФайлКартинки = Новый Файл(ПолноеИмяФК);
                Если ФайлКартинки.Существует() Тогда
                    Возврат ПолноеИмяФК;
                Иначе
                    Сообщить(«Не удалось экспортировать картинку из строки « + НомерСтроки + » колонки « + НомерКолонки + » в « + ПолноеИмяФК);
                    Возврат Неопределено;
                КонецЕсли;
            Исключение
                Сообщить(ОписаниеОшибки());
                Сообщить(«Не удалось экспортировать картинку из строки « + НомерСтроки + » колонки « + НомерКолонки + » в « + ПолноеИмяФК);
                Возврат Неопределено;
            КонецПопытки;

        КонецЕсли;
    КонецЦикла;

    Возврат Неопределено;

КонецФункции

&НаСервере
Процедура УдалитьКолонкиСНулевойШириной(ТаблицаРезультат)
    Перем МассивПустыхКолонок;

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

    // Удалим пустые колонки.
    Для Каждого ПустаяКолонка ИЗ МассивПустыхКолонок Цикл
        ТаблицаРезультат.Колонки.Удалить(ПустаяКолонка);
    КонецЦикла;

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

// Выделяет из полного имени файла имя файла без расширения.
//
// Параметры
//  ПолноеИмяФайла     – Строка, содержащая имя файла, неважно с именем каталога или без.
//
// Возвращаемое значение:
//   ИмяФайлаБезРасширения – короткое имя файла.
//
&НаСервере
Функция ПолучитьИмяФайлаБезРасширения(ПолноеИмяФайла)
    Перем ФайлТМП, РасширениеФайла, ИмяФайлаБезРасширения;

    ФайлТМП = РазложитьСтрокуВМассивПодстрок(ПолноеИмяФайла, «»);
    ФайлТМП = ФайлТМП[ФайлТМП.Количество()-1];
    РасширениеФайла  = «.» + ПолучитьРасширениеИмениФайла(ФайлТМП);
    ИмяФайлаБезРасширения = СтрЗаменить(ФайлТМП, РасширениеФайла, «»);

    Возврат ИмяФайлаБезРасширения;

КонецФункции

// Выделяет из имени файла его расширение (набор символов после последней точки).
//
// Параметры
//  ИмяФайла     – Строка, содержащая имя файла, неважно с именем каталога или без.
//
// Возвращаемое значение:
//   Строка – расширение файла.
//
&НаСервере
Функция ПолучитьРасширениеИмениФайла(Знач ИмяФайла)
    Перем Расширение;

    Расширение = ПолучитьСтрокуОтделеннойСимволом(ИмяФайла, «.»);
    Возврат Расширение;

КонецФункции

// Удаляет кавычки с начала и конца строки, если они есть.
//
// Параметры:
//  Строка — входная строка;
//
// Возвращаемое значение:
//  Строка — строка без двойных кавычек.
//
&НаСервере
Функция УдалитьКавычки(Знач Строка, Кавычка = «»»»)

    Пока Лев(Строка, 1) = Кавычка Цикл
        Строка = Сред(Строка, 2);
    КонецЦикла;

    Пока Прав(Строка, 1) = Кавычка Цикл
        Строка = Лев(Строка, СтрДлина(Строка) — 1);
    КонецЦикла;

    Возврат Строка;

КонецФункции

// Удаляет из строки указанное количество символов справа.
//
// Параметры:
//  Текст         — Строка — строка, в которой необходимо удалить последние символы;
//  ЧислоСимволов — Число  — количество удаляемых символов.
//
&НаСервере
Процедура УдалитьПоследнийСимволВСтроке(Текст, ЧислоСимволов)

    Текст = Лев(Текст, СтрДлина(Текст) — ЧислоСимволов);

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

// Функция «расщепляет» строку на подстроки, используя заданный
//      разделитель. Разделитель может иметь любую длину.
//      Если в качестве разделителя задан пробел, рядом стоящие пробелы
//      считаются одним разделителем, а ведущие и хвостовые пробелы параметра Стр
//      игнорируются.
//      Например,
//      РазложитьСтрокуВМассивПодстрок(«,один,,,два», «,») возвратит массив значений из пяти элементов,
//      три из которых — пустые строки, а
//      РазложитьСтрокуВМассивПодстрок(» один   два», » «) возвратит массив значений из двух элементов
//
//  Параметры:
//      Стр —           строка, которую необходимо разложить на подстроки.
//                      Параметр передается по значению.
//      Разделитель —   строка-разделитель, по умолчанию — запятая.
//
//  Возвращаемое значение:
//      массив значений, элементы которого — подстроки
//
&НаСервере
Функция РазложитьСтрокуВМассивПодстрок(Знач Стр, Разделитель = «,»)

    МассивСтрок = Новый Массив();
    Если Разделитель = » « Тогда
        Стр = СокрЛП(Стр);
        Пока 1 = 1 Цикл
            Поз = Найти(Стр, Разделитель);
            Если Поз = 0 Тогда
                МассивСтрок.Добавить(СокрЛП(Стр));
                Возврат МассивСтрок;
            КонецЕсли;
            МассивСтрок.Добавить(СокрЛП(Лев(Стр, Поз — 1)));
            Стр = СокрЛ(Сред(Стр, Поз));
        КонецЦикла;
    Иначе
        ДлинаРазделителя = СтрДлина(Разделитель);
        Пока 1 = 1 Цикл
            Поз = Найти(Стр, Разделитель);
            Если Поз = 0 Тогда
                Если (СокрЛП(Стр) <> «») Тогда
                    МассивСтрок.Добавить(СокрЛП(Стр));
                КонецЕсли;
                Возврат МассивСтрок;
            КонецЕсли;
            МассивСтрок.Добавить(СокрЛП(Лев(Стр,Поз — 1)));
            Стр = Сред(Стр, Поз + ДлинаРазделителя);
        КонецЦикла;
    КонецЕсли;

КонецФункции

// Функция возвращает часть строки после последнего встреченного символа в строке
&НаСервере
Функция ПолучитьСтрокуОтделеннойСимволом(Знач ИсходнаяСтрока, Знач СимволПоиска)

    ПозицияСимвола = СтрДлина(ИсходнаяСтрока);
    Пока ПозицияСимвола >= 1 Цикл
        Если Сред(ИсходнаяСтрока, ПозицияСимвола, 1) = СимволПоиска Тогда
            Возврат Сред(ИсходнаяСтрока, ПозицияСимвола + 1);
        КонецЕсли;
        ПозицияСимвола = ПозицияСимвола — 1;
    КонецЦикла;

    Возврат «»;

КонецФункции

---

Преимущества:

1.Метод «Microsoft EXCEL» наиболее прост в применении (кодировании).
2. По скорости считывания находится на 2-ом месте после «Microsoft ADODB».

Особенности и Ограничения:

1. Для функционирования метода «Microsoft EXCEL» необходим установленный Microsoft Office. Коммерческий продукт.
2. Метод может не работать в клиент-серверном варианте, если у SQL-пользователя недостаточно прав в Windows.
3. 1-я строка файла EXCEL — строка, содержащая заголовки колонок.

---

С уважением к сообществу МА!