How to generate lots of hyperlinks rather fast using ClosedXML library?
I am using ClosedXML library to generate an Excel containing thousands of hyperlinks using cell's Hyperlink property:
cell.SetValue(text).Hyperlink = new XLHyperlink(url);
Such a call takes about 3ms which seems very fast, but this leads to exports taking up to one minute for large exports (10K+ rows). Writing the URL as text in the cell is much faster, but it is not user-friendly (the user has to enter the cell, and press Enter to trigger hyperlink creation).
Is there a method to improve the performance and still get hyperlinks in the exported Excel?
1 answer
One faster alternative is to use a formula to create the hyperlink. An example is provided below:
public static IXLCell SetHyperLink(this IXLCell cell, string url, string text = "link")
{
if (string.IsNullOrWhiteSpace(url))
return cell;
// ensure a valid hyperlink text
string actualText = text?.Replace("\"", "\"\"");
actualText = !string.IsNullOrWhiteSpace(actualText) ? actualText : "link";
string actualUrl = url.Trim();
bool isValidUrl = Uri.IsWellFormedUriString(actualUrl, UriKind.Absolute);
if (isValidUrl)
{
cell.FormulaA1 = $"=HYPERLINK(\"{url}\", \"{actualText}\")";
cell.Style.Font.Underline = XLFontUnderlineValues.Single;
}
else
{
cell.SetValue(url);
}
return cell;
}
Actual hyperlink creation is done via cell.FormulaA1 = $"=HYPERLINK(\"{url}\", \"{actualText}\")";, but the rest of the code is required to check for various conditions that lead to invalid formulas (this creates a nasty popup when opening the Excel file that requires the user to confirm the Excel repairing).
0 comment threads
0 comment threads