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

← На главную

При формировании прайс-листов и каталогов товаров в 1С часто возникает необходимость добавить в выгружаемый файл .xls кликабельные ссылки (например, на фото номенклатуры или карточку товара на сайте). Однако при стандартном выводе формулы вида =ГИПЕРССЫЛКА("URL", "Текст") или =HYPERLINK("URL", "Текст") через внешние компоненты (такие как Йоксель) Excel и LibreOffice Calc часто отображают её как обычный текст.

Формула становится активной ссылкой только после того, как пользователь кликнет по ячейке и нажмет клавишу Enter. Для прайсов, содержащих тысячи позиций, ручной пересчет не подходит. Рассмотрим причины возникновения этой проблемы и разберем универсальное программное решение.

Почему формула гиперссылки отображается как текст?

Выясним главные причины, по которым электронные таблицы не распознают формулу автоматически:

Текстовый формат ячеек: При сохранении табличного документа из 1С ячейкам присваивается текстовый формат (символ формата @). В таком режиме любые формулы воспринимаются офисом как обычная текстовая строка.
Отсутствие кэшированного значения: Форматы XLS и XLSX требуют записи не только текста формулы, но и предвычисленного результата. Сторонние библиотеки выгрузки часто не заполняют это поле, поэтому Excel показывает формулу текстом до принудительного пересчета.

Алгоритм решения проблемы

Для создания полноценных гиперссылок применим следующий подход:

Сохраняем сформированный отчет или прайс-лист из 1С в файл формата XLS.
Программно подключаемся к офисному пакету, установленному на компьютере (Microsoft Excel или LibreOffice Calc), используя механизмы COM / OLE.
Находим колонку со ссылками и заменяем текстовое значение ячеек на полноценные объекты гиперссылок.
Сохраняем и закрываем файл.

Разберем реализацию этого алгоритма на языке 1С. Данный код автоматически определяет, какой офисный пакет установлен в системе (Excel или LibreOffice), и выполняет необходимую обработку.

Пример кода на 1С для работы с Excel и LibreOffice


Состояние("Идет формирование гиперссылок на товар на сайте фирмы.");
Сообщить("Идет формирование гиперссылок на товар на сайте фирмы.");

флЕстьЕксель = 0;
флЕстьЛибре = 0;

Попытка
    Ексель = СоздатьОбъект("Excel.Application");
    флЕстьЕксель = 1;
Исключение
    флЕстьЕксель = 0;
    Попытка
        ServiceManager = СоздатьОбъект("com.sun.star.ServiceManager");
        флЕстьЛибре = 1;
    Исключение
        флЕстьЛибре = 0;
    КонецПопытки;
КонецПопытки;

Если (флЕстьЛибре = 0) И (флЕстьЕксель = 0) Тогда
    Сообщить("Не установлен Excel или LibreOffice! Гиперссылки могут отображаться некорректно.");
КонецЕсли;

// Преобразование текста в гиперссылки через Excel
Если флЕстьЕксель = 1 Тогда
    Попытка
        Wsh = СоздатьОбъект("WScript.Shell");
        Wsh.RegWrite("HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\10.0\Excel\Security\AccessVBOM", 1, "REG_DWORD");
        Wsh.RegWrite("HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\11.0\Excel\Security\AccessVBOM", 1, "REG_DWORD");
        Wsh.RegWrite("HKEY_CURRENT_USER\SOFTWARE\Microsoft\Office\12.0\Excel\Security\AccessVBOM", 1, "REG_DWORD");
        Wsh = "";
    Исключение
    КонецПопытки;

    Книга = Ексель.WorkBooks.Open(ИмяФайлаXLS);
    Ексель.Visible = 0;
    Лист = Книга.WorkSheets(1);

    ЕстьЛинк = 0;
    СтартКол = 1;

    Для Кол = СтартКол По 30 Цикл
        Если СокрЛП(Лист.Cells(10, Кол).Value) = "Link" Тогда
            ЕстьЛинк = ЕстьЛинк + 1;
            СтолбецЛинка1 = Кол;
            Прервать;
        КонецЕсли;
    КонецЦикла;

    Если ЕстьЛинк > 0 Тогда
        vbc = Ексель.vbe.ActiveVBProject.VBComponents;
        last = vbc.Count;
        vbc.Add(1);
        module = vbc.Item(last + 1).CodeModule;
        module.InsertLines(1, "Sub SetLynk(ActCell, Adr, txtDisp)");
        module.InsertLines(2, "ActiveSheet.Hyperlinks.Add Anchor:=Range(ActCell), Address:=Adr, TextToDisplay:=txtDisp");
        module.InsertLines(3, "End Sub");

        Для Стр = 11 По КоличествоСтрокПрайса - 1 Цикл
            Колонка = СтолбецЛинка1;
            СсылкаСтрока = СокрЛП(Лист.Cells(Стр, Колонка).Value);
            Если ПустоеЗначение(СсылкаСтрока) = 0 Тогда
                Если ВРег(СсылкаСтрока) <> "LINK" Тогда
                    АдресЯчейки = Лист.Range(Лист.Cells(Стр, Колонка), Лист.Cells(Стр, Колонка)).Address;
                    Ексель.run("SetLynk", АдресЯчейки, СсылкаСтрока, "Сайт");
                КонецЕсли;
            КонецЕсли;
        КонецЦикла;

        Ексель.DisplayAlerts = 0;
        vbc.Remove(vbc.Item(last + 1));
        Книга.Save;
        Ексель.Quit;
        Ексель.DisplayAlerts = 1;
    КонецЕсли;

// Преобразование текста в гиперссылки через LibreOffice Calc
ИначеЕсли флЕстьЛибре = 1 Тогда
    Скрипт = СоздатьОбъект("MSScriptControl.ScriptControl");
    Скрипт.language = "javascript";
    СервисМанагер = СоздатьОбъект("com.sun.star.ServiceManager");
    Скрипт.AddObject("СервисМанагер", СервисМанагер);

    СписПарам = Скрипт.eval("new Array");
    Парам = Скрипт.Eval("СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
    Парам.Name = "Hidden";
    СписПарам.push(Парам);

    Десктоп = СервисМанагер.createInstance("com.sun.star.frame.Desktop");
    Диспатчер = СервисМанагер.CreateInstance("com.sun.star.frame.DispatchHelper");
    Док = Десктоп.LoadComponentFromURL(ПреобразоватьВУРЛ(ИмяФайлаXLS), "_blank", 0, СписПарам);

    Док.lockControllers;
    Док.addActionLock;
    Листы = Док.getSheets;
    Лист = Листы.getByIndex(0);

    ЕстьЛинк = 0;
    СтартКол = 0; // В LibreOffice отсчет строк и столбцов идет с 0

    Для Кол = СтартКол По 30 Цикл
        Если СокрЛП(Лист.GetCellbyPosition(Кол, 9).getString) = "Link" Тогда
            ЕстьЛинк = ЕстьЛинк + 1;
            СтолбецЛинка1 = Кол;
            Прервать;
        КонецЕсли;
    КонецЦикла;

    Если ЕстьЛинк > 0 Тогда
        Для Стр = 10 По КоличествоСтрокПрайса - 2 Цикл
            Колонка = СтолбецЛинка1;
            СсылкаСтрока = СокрЛП(Лист.getCellByPosition(Колонка, Стр).getString);
            Лист.getCellByPosition(Колонка, Стр).setString("");

            Если ПустоеЗначение(СсылкаСтрока) = 0 Тогда
                Если ВРег(СсылкаСтрока) <> "LINK" Тогда
                    КолонкаСтр = "$" + Симв(65 + (Колонка - Цел(Колонка / 26) * 26));
                    АдресЯчейки = КолонкаСтр + "$" + (Стр + 1);

                    СписПарам = Скрипт.eval("new Array");
                    Парам = Скрипт.Eval("СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Парам.Name = "ToPoint";
                    Парам.Value = АдресЯчейки;
                    СписПарам.push(Парам);
                    Диспатчер.executeDispatch(Док.CurrentController.Frame, ".uno:GoToCell", "", 0, СписПарам);

                    Скрипт.eval("Massiv=new Array");
                    СписПарам = Скрипт.eval("Massiv");
                    Фото = "Фото";

                    Скрипт.eval("Massiv[0]=СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Скрипт.eval("Massiv[0].Name='Hyperlink.Text'");
                    Скрипт.eval("Massiv[0].Value = '" + Фото + "'";
                    Скрипт.eval("Massiv[1]=СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Скрипт.eval("Massiv[1].Name = 'Hyperlink.URL'");
                    Скрипт.eval("Massiv[1].Value = '" + СсылкаСтрока + "'";
                    Скрипт.eval("Massiv[2]=СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Скрипт.eval("Massiv[2].Name = 'Hyperlink.Target'");
                    Скрипт.eval("Massiv[2].Value = ''");
                    Скрипт.eval("Massiv[3]=СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Скрипт.eval("Massiv[3].Name = 'Hyperlink.Name'");
                    Скрипт.eval("Massiv[3].Value = ''");
                    Скрипт.eval("Massiv[4]=СервисМанагер.Bridge_GetStruct('com.sun.star.beans.PropertyValue')");
                    Скрипт.eval("Massiv[4].Name = 'Hyperlink.Type'");
                    Скрипт.eval("Massiv[4].Value = 1");

                    Диспатчер.executeDispatch(Док.CurrentController.Frame, ".uno:SetHyperlink", "", 0, СписПарам);
                КонецЕсли;
            КонецЕсли;
        КонецЦикла;

        Попытка
            Док.Store;
            Док.unLockControllers;
            Док.removeActionLock;
            Док.Close(-1);
        Исключение
            Сообщить("Ошибка сохранения: " + ОписаниеОшибки());
        КонецПопытки;
    КонецЕсли;
КонецЕсли;

Альтернативные варианты без использования COM

Если на сервере или рабочем компьютере пользователя не установлены MS Excel и LibreOffice, можно воспользоваться альтернативными решениями:

Экспорт в HTML / MHT: Сформируйте прайс-лист в формате HTML. Для создания ссылок используйте стандартные теги <a href="http://...">Текст</a>. Если сохранить такой файл с расширением .xls или .html, Excel и LibreOffice Calc открывают его корректно и автоматически отображают теги <a> как интерактивные гиперссылки.
Быстрый способ оживления формул для пользователей: Если файл с формулами =HYPERLINK(...) уже открыт в Excel, можно выделить колонку, изменить формат ячеек с «Текстового» на «Общий», а затем вызвать окно замены (клавиши Ctrl + H) и заменить знак = на =. Excel принудительно пересчитает формулы и активирует ссылки.