Welcome to Software Development on Codidact!
Will you help us build our independent community of developers helping developers? We're small and trying to grow. We welcome questions about all aspects of software development, from design to code to QA and more. Got questions? Got answers? Got code you'd like someone to review? Please join us.
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