Добро пожаловать в форум, Guest  >>   Войти | Регистрация | Поиск | Правила | В избранное | Подписаться
Все форумы / Delphi Новый топик    Ответить
 Универсальный способ чтения гиперссылки из Excel  [new]
Jonnik
Member

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

Excel загружаю из Delphi через ComObj

Excel:=CreateOleObject('Excel.Application');                                 
Excel.Visible:=False;
Excel.WorkBooks.Open(PathFileName);
WorkSheet:=Excel.ActiveWorkbook.ActiveSheet;
// и потом уже пошел сам процесс обработки файла включая загрузку гиперссылок  
 


Варианты записи гиперссылок:

1. Гиперссылку можно добавить в ручную через меню добавления гиперссылки в ячейку и в этом случае считать гиперссылку можно так
Excel.Cells[i,j].Hyperlinks[1].Address


2. Иногда гиперссылку добавляют через формулу
=ГИПЕРССЫЛКА("http://www.Мой сайт.net/photo/Photo.jpg")

и тогда гиперссылку можно получить через чтение формулы, а затем из нее убрать все что не нужно
Excel.Cells[i,i].FormulaLocal


3. Частенько бывает, что гиперссылку задают
=ГИПЕРССЫЛКА("http://www.Мой сайт.net/photo/idphoto="&A1:A1".jpg")

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

4. И наконец гиперссылку могут задать так
=ЕСЛИ(A1=0;"";ГИПЕРССЫЛКА(СЦЕПИТЬ("http://Мой сайт.net/photo/";ПОДСТАВИТЬ(ЛЕВСИМВ(A1;ДЛСТР(A1)-3);".";"_");;"_1.jpg");"схема"))

Т.е. гиперссылка отображается в ячейки в зависимости от содержания другой ячейки, а сам адрес гиперссылки формируется выполнением различных функция Excel.

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

В приложении есть пример этих четырех вариантов записи гиперссылок

К сообщению приложен файл (Пример.xlsx - 10Kb) cкачать
10 апр 14, 19:28    [15864539]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
fd00ch
Member

Откуда: Нижний Новгород
Сообщений: 5913
обозначил бы, чем собираешься эксельный файл читать...
10 апр 14, 20:30    [15864827]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Jonnik
Member

Откуда:
Сообщений: 196
[quot fd00ch]обозначил бы, чем собираешься эксельный файл читать...[/quot

В начале темы все описано. -> "Excel загружаю из Delphi через ComObj"
Означает, что Excel файл открываю установленным экселем и читаю от туда данные.

Может для решения этой проблемы нужно какой то макрос придумать...
10 апр 14, 21:01    [15864951]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
user89
Member

Откуда:
Сообщений: 2083
Jonnik,

можно попробовать так:
- сохранить Excel-файл в HTML
- вытащить из HTML ссылку через a href
10 апр 14, 22:12    [15865160]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
rgreat
Member

Откуда:
Сообщений: 6723
Попробуй это:

http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel
11 апр 14, 00:29    [15865499]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Gator
Member

Откуда: Москва
Сообщений: 14980
user89
Jonnik,

можно попробовать так:
- сохранить Excel-файл в HTML
- вытащить из HTML ссылку через a href
Ну-ну.
Найти чёрную кошку в чёрной комнате. Особенно, если кошки там нет.

Представьте: есть 300 файлов или 300 000 блобов с xls.
Каждый надо сохранить (SaveAS) в HTML, распарсить, и ничего не найти.

Нравится сценарий?
11 апр 14, 00:51    [15865537]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Gator
Member

Откуда: Москва
Сообщений: 14980
Gator, приквел-сиквел про COM тоже в топку.

Возвращаемся к 15864827
11 апр 14, 00:54    [15865542]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Jonnik
Member

Откуда:
Сообщений: 196
rgreat
Попробуй это:

http://howtouseexcel.net/how-to-extract-a-url-from-a-hyperlink-on-excel


Это вариант вытащит только гиперссылки заданые 1 способом, а через формулу ничего не вернет
Function GetURL(cell As range, _
 Optional default_value As Variant)
 'Lists the Hyperlink Address for a Given Cell
 'If cell does not contain a hyperlink, return default_value
 If (cell.range("A1").Hyperlinks.Count <> 1) Then
 GetURL = default_value
 Else
 GetURL = cell.range("A1").Hyperlinks(1).Address & "#" & cell.range("A1").Hyperlinks(1).SubAddress
 End If
 End Function


Идея с Htm конечно интересная, но искать потом нужную ссылку крайне проблематично. Одно дело вытащить все гиперссылки, а другое только нужные и из нужных мест.

Файлов около 1000 и все разные.
11 апр 14, 02:50    [15865667]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
_Vasilisk_
Member

Откуда: Украина, Харьков
Сообщений: 13003
Jonnik,

Тынц?
11 апр 14, 03:26    [15865680]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Jonnik
Member

Откуда:
Сообщений: 196
_Vasilisk_
Jonnik,

Тынц?


EVALUATE() позволяет получить результат вычисления формулы, а для этого надо еще получить формулу самого текста гиперссылки.
Это предпологает написания парсера формул, что бы оставить только ту часть которая формирует текст гиперссылки.
Вариант интересный, но тоже не простой.
11 апр 14, 12:51    [15867595]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
_Vasilisk_
Member

Откуда: Украина, Харьков
Сообщений: 13003
Jonnik
а для этого надо еще получить формулу самого текста гиперссылки.
Зачем? Если
Jonnik
EVALUATE() позволяет получить результат вычисления формулы

Вам нужно выполнить EVALUATE() для всего содержимого ячейки. И все вложенные формулы вычеслятся сами
11 апр 14, 16:07    [15869087]     Ответить | Цитировать Сообщить модератору
Между сообщениями интервал более 1 года.
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
lks1965
Member

Откуда:
Сообщений: 15
Прошло много лет, но вопрос остался

Как же все таки получить ссылку из ячейки, где она формируется формулой ГИПЕРССЫЛКА?

Оставим за кадром вопросы зачем это надо и советы в духе как умнее строить листы, а сосредоточимся на существенном

имеется ячейки с заданными формулами гиперссылками

Формулы могут быть очень хитроумно вычисляемые с задействованием массы других листов

Это еще и простая - есть куда более хитроумные
=ЕСЛИОШИБКА(ЕСЛИ(ВПР($A2;ВсеМетодики!$A:$U;19;ЛОЖЬ) =0;""; ЕСЛИОШИБКА(ГИПЕРССЫЛКА(ВПР($A2;ВсеМетодики!$A:$U;19;ЛОЖЬ);ВПР($A2;ВсеМетодики!$A:$U;18;ЛОЖЬ));""));"")

Все конструкции встречающиеся в интернетах сводятся либо
1) к работе с объектом Hyperlinks - но в нем нет гиперссылок динамически формируемых функцией ГИПЕРССЫЛКА
2) к разбору самой формулы - это просто если формула такая ГИПЕРССЫЛКА(A1;B1) но нереалистично в случае сложных конструкций
3) Советы применить EVALUTE бесполезны т.к. при применении к содержимому ячейки она вернет, то что мы увидим в ячейке(но не ссылку!) т.е. это ничем не лучше скопировать - вставить значения, а разбор формулы в ячейке с целью выделения подформсулы формирующей гиперссылку см п.2

Вообще говоря ГИПЕРССЫЛКА функция двойственная - она с одной стороны генерит то что мы видим в ячейке а с другой стороны генерит саму гиперссылку (существующую в глубинах работающего экселя - заметьте когда мы подводим курсорк ячейке вполне видим правильную ссылку независимо от способа формирования)

По правде сказать единственно что действительно работает это совместный анализ xlsx файла и анализ сгенерированной htm странички (там сохраняются результирующие ссылки) - но это совсем уж тягомотно и как-то некрасиво

Итак вопрос остается - как же универсально узнать гиперссылку в ячейке
31 мар 21, 17:47    [22302646]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
Гаджимурадов Рустам
Member

Откуда:
Сообщений: 62986
lks1965
3) Советы применить EVALUTE бесполезны т.к. при применении к содержимому ячейки она вернет, то что мы увидим в ячейке(но не ссылку!)
Не очень понял.
31 мар 21, 19:01    [22302684]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
lks1965
Member

Откуда:
Сообщений: 15
Гаджимурадов Рустам
lks1965
3) Советы применить EVALUTE бесполезны т.к. при применении к содержимому ячейки она вернет, то что мы увидим в ячейке(но не ссылку!)
Не очень понял.


предположим у вас я ячейках
A1 - = ГИПЕРССЫЛКА(B1;C1)
B1 - HTTPS://MYSITE.RU
C1 - МОЙ САЙТ

Что мы увидим в ячейке A1 после вычисления формулы? = строку МОЙ САЙТ и к ячейке привяжется динамическая гиперссылка

А если в коде VBA вычислим - Application.Evaluate("A1")? Вернется строка МОЙ САЙТ А гиперссылка? А нет ее. В том то и вопрос

Формула в ячейке A1 может быть очень сложной но какой бы она не была вернется всего навсего значение помещаемое в ячейку но не гиперссылка

Сообщение было отредактировано: 31 мар 21, 19:20
31 мар 21, 19:26    [22302703]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
_Vasilisk_
Member

Откуда: Украина, Харьков
Сообщений: 13003
Какой VarType возвращает EVALUATE? Там не varDispatch?
31 мар 21, 20:30    [22302732]     Ответить | Цитировать Сообщить модератору
 Re: Универсальный способ чтения гиперссылки из Excel  [new]
lks1965
Member

Откуда:
Сообщений: 15
Я нашел способ такого копирования, а точнее получения адреса вот такой динамической ссылки. Заключается он в том, что ячейку надо скопировать в Word
(радость то какая - при такой операции Word вычисляет таки актуальный адрес ссылки и превращает ее в фиксированный), далее проверить коллекцию Hyperlinks уже объекта Word

Конечно в таком виде работает довольно таки медленно, но при желании можно усовершенствовать, например объект wdApp сделать статическим и не каждый раз создавать/разрушать, это весьма прилично ускорит работу, если надо много ячеек обработать
Проверено на Excel/Word 2019 (да и не забыть подключить Microsoft Object Library )
' Получаем адрес гиперссылки  
Function GetLink(r As Long, c As Long) As String  
       
    Dim wdApp As Word.Application            
    Dim wdDoc As Word.Document  
       
    If Cells(r, c) Is Nothing Then  
      GetLink = ""  
      Exit Function  
    End If  
       
    Cells(r, c).Copy  
       
    Set wdApp = CreateObject("Word.Application")  
    wdApp.Documents.Add  
    Set wdDoc = wdApp.Documents(1)  
       
    wdApp.Visible = False  
    wdDoc.Range.PasteExcelTable False, False, False  
       
    ' Теперь найдем ссылку в коллекции Hyperlinks Word-локумента word  
    If wdDoc.Hyperlinks.Count = 0 Then  
        GetLink = ""  
      Else  
        GetLink = wdDoc.Hyperlinks(1).Name  
     End If  
    wdDoc.Close (wdDoNotSaveChanges)  
    wdApp.Quit (wdDoNotSaveChanges)  
       
End Function  
4 апр 21, 17:56    [22304126]     Ответить | Цитировать Сообщить модератору
Все форумы / Delphi Ответить