Hyperlinks
You can add any type of hyperlink to cell or range (row and col are ranges too), such as: hyperlink to file, network, website, email, another sheet and etc.
Xlsx2Go supports string version of hyperlinks, as well as custom version via special type. Read about types below.
Hyperlinks limits
Excel has built-in limit for total number of hyperlinks per sheet. Check Excel Limits
Tip
In some cases, adding same hyperlink to range (instead of cells directly) can help to exceed these limits.
Filepath
Xlsx2Go automatically detects windows, unix and network versions of filepath and validates it.
Hyperlinks and cells
//to add hyperlink to cell
sheet.CellByRef("N28").SetHyperlink("http://google.com")
//to add hyperlink to cell and value same time
sheet.CellByRef("N28").SetValueWithHyperlink(12345, "http://google.com")
//to remove hyperlink
sheet.CellByRef("N28").RemoveHyperlink()
//to retrieve hyperlink information
link := sheet.CellByRef("N28").Hyperlink()
Hyperlinks and ranges
//add hyperlink to range
sheet.RangeByRef("A1:A10").SetHyperlink("http://google.com")
//to remove hyperlink from range
sheet.RangeByRef("A1:A10").RemoveHyperlink()
//Row and Column are subtypes of Range, so inherit functionality
sheet.Row(0).SetHyperlink("http://google.com")
sheet.Col(0).SetHyperlink("http://google.com")
N.B.:
In case of ranges, only one hyperlink will be added to sheet, but for whole range!
String hyperlink
As was shown, the simplest way to add hyperlink is to use string version. At the same time, that version is quite limited - valid format should be used and you can't set tooltips, styles and etc.
Xlsx2Go supports same format as Excel's HYPERLINK function and will try to autodetect type of hyperlink (email, file, website and etc) to validate it later.
Check Excel Hyperlink for more information.
Same file, same sheet
#A1
Same file, other sheet
#SheetName!A1#'Sheet Name'!A1
External file
D:\Folder\File.docx[D:\Folder\File.docx]D:\Folder\File.docx#Bookmark[D:\Folder\File.docx]BookmarkD:\Folder\File.xlsx#SheetName!A1[D:\Folder\File.xlsx]SheetName!A1D:\Folder\File.xlsx#'Sheet Name'!A1[D:\Folder\File.xlsx]'Sheet Name'!A1
Network file
\\SERVER\Folder\File.doc\\SERVER\Folder\File.xlsx#SheetName!A1[\\SERVER\Folder\File.xlsx]SheetName!A1\\SERVER\Folder\File.xlsx#'Sheet Name'!A1[\\SERVER\Folder\File.xlsx]'Sheet Name'!A1
Url
https://www.spam.ithttps://www.spam.it/#bookmark[https://www.spam.it/]bookmark
mailto:spam@spam.itmailto:spam@spam.it?subject=topic
Custom hyperlink
While with string version of hyperlink you can add any kind of hyperlink, sometimes we need additional settings like tooltips or styles. For these cases you can use special type and configure hyperlink as you wish.
Example
package code
import (
"github.com/plandem/xlsx"
"github.com/plandem/xlsx/format/styles"
"github.com/plandem/xlsx/types/hyperlink"
)
func Example_hyperlinks() {
xl := xlsx.New()
sheet := xl.AddSheet("")
//hyperlink to other excel file with reference to C3 at Sheet1
link := hyperlink.New(
hyperlink.ToFile(`C:\example_simple.xlsx`),
hyperlink.ToRef("C3", "Sheet1"),
hyperlink.Tooltip("That's a tooltip"),
hyperlink.Display("Something to display"), //Cell still holds own value
hyperlink.Styles(styles.New(
styles.Font.Bold,
styles.Font.Color("#ff0000"),
)),
)
sheet.CellByRef("A2").SetHyperlink(link)
xl.SaveAs("./foo.xlsx")
}
As was shown in example, few relevant targets can be combined:
ToFile+ToBookmark, e.g. Word file with bookmark or Excel file with named regionToFile+ToRef, e.g. reference at other Excel fileToUrl+ToBookmark, e.g. bookmark at other site
Target types + examples
//sets target to external file
// can be unix, windows or network format
hyperlink.ToFile(`./example_simple.xlsx`)
//sets target to web site
hyperlink.ToUrl(`https://www.spam.it`)
//sets target to ref of sheet with sheetName
// omit sheetName to set location to ref of active sheet
hyperlink.ToRef(`C3`, "Sheet1")
//sets target to bookmark
// can be named region in xlsx, bookmark of remote file or even site
hyperlink.ToBookmark(`bookmark`)
//sets target to email
hyperlink.ToMail(`spam@spam.it`, "topic")
//sets target in same format as string version of hyperlink
hyperlink.ToTarget(`D:\Folder\File.docx#Bookmark`)