Ask Your Question
0

LibreOffice Calc 6 - "batch" hyperlink to files in folder?

asked 2019-05-05 19:14:30 +0100

LOcalcQ gravatar image

Hei!

Question regarding LibreOffice Calc (Version: 6.1.5.2).

I have one folder with the spreadsheet and a bunch of image and pdf files.

In the sheet every line starts with a hyperlink, which I usually put in using Ctrl+K, then target the file, and then rename it to not show the full link to file but just the file name.

Since I have 36 file to put in, I've been wondering if there is a faster way to do this, instead of manually putting them in one by one?

(I'm on Fedora 29 if that makes any difference).

Thanks!

edit retag flag offensive close merge delete

1 Answer

Sort by » oldest newest most voted
0

answered 2019-05-05 23:51:17 +0100

karolus gravatar image

Called from a Calcfile stored in target_folder the following code puts HYPERLINK(…)-formulas into Column A of active Sheet.

from os import path
from pathlib import Path
import uno



def link_my_folder(*_):
    doc = XSCRIPTCONTEXT.getDocument()
    sheet = doc.CurrentSelection.Spreadsheet

    p = Path(path.dirname(uno.fileUrlToSystemPath(doc.URL)))
    filenames = [(x.name, x.as_uri()) for x in p.iterdir()
                if x.is_file() and not x.as_uri()==doc.URL]
    filenames.sort()
    formulas = tuple(('=HYPERLINK("{}";"{}"'.format(b, a),) for a, b in filenames)

    targetrange = sheet.getCellRangeByPosition(0,0,0,len(formulas)-1)
    targetrange.setFormulaArray(formulas)
edit flag offensive delete link more

Comments

Thank you for the answer! I don't know how to use this script. Where should I put it? How can I call it?

LOcalcQ gravatar imageLOcalcQ ( 2019-05-06 07:26:59 +0100 )edit

open your favourite Texteditor (no it isnt writer in this case) c&p the souce exactly into a new file and store it into path:

/home/$( you )/.config/libreoffice/4/user/Scripts/python/$(some_sensefull_name).py

run link_my_folder via >Tools>Makros>execute Makros>…

if the Script and the Funktion is not visible in the GUI you have probably to install libreoffice-scriptprovider-python with your Packet-Manager or apt-get or yum or whatever is used in Fedora.

karolus gravatar imagekarolus ( 2019-05-06 09:10:54 +0100 )edit

Ps.:
look also in this thread about APSO a very usefull Extionsion for managing python-Scripts in Libreoffice

karolus gravatar imagekarolus ( 2019-05-06 09:28:39 +0100 )edit

Thank you, this answered my question! Worked like a charm.

LOcalcQ gravatar imageLOcalcQ ( 2019-05-12 12:00:09 +0100 )edit

If so please tick the -mark left near my answer.

karolus gravatar imagekarolus ( 2019-05-12 15:09:22 +0100 )edit
Login/Signup to Answer

Question Tools

1 follower

Stats

Asked: 2019-05-05 19:14:30 +0100

Seen: 68 times

Last updated: May 05