Optimized way of adding multiple hyperlinks in excel file with C#

Question

I wanted to ask if there is some practical way of adding multiple hyperlinks in excel worksheet with C# ..? I want to generate a list of websites and anchor hyperlinks to them, so the user could click such hyperlink and get to that website. So far I have come with simple nested for statement, which loops through every cell in a given excel range and adds hyperlink to that cell:

for (int i = 0; i < _range.Rows.Count; i++)
        {
            Microsoft.Office.Interop.Excel.Range row = _range.Rows[i];
            for (int j = 0; j < row.Cells.Count; j++)
            {
                Microsoft.Office.Interop.Excel.Range cell = row.Cells[j];
                cell.Hyperlinks.Add(cell, adresses[i, j], _optionalValue, _optionalValue, _optionalValue);
            }
        }

The code is working as intended, but it is Extremely slow due to thousands of calls of the Hyperlinks.Add method. One thing that intrigues me is that the method set_Value from Office.Interop.Excel can add thousands of strings with one simple call, but there is no similar method for adding hyperlinks (Hyperlinks.Add can add just one hyperlink).

So my question is, is there some way to optimize adding hyperlinks to excel file in C# when you need to add a large number of hyperlinks...?

Any help would be apreciated.

have you tried just using set_Value with http:// preceding the link ? When you just type in a url into a blank excel worksheet, I found that it keys off of "http://" and "www." to infer that it is a hyperlink and does an auto link. — Ta01
@kd7 : Yes, I have tried using set_Value with protocol preceding the link, but it wont create the hyperlink itself, just normal string. — Jan
@CodeBlend : Excel file because the customer wants it. Besides I really dont know of any better alternative... — Jan
Surely an HTML file your pretty much guaranteed they can open and it gives you a way to display the links? — Coops

Martin Meeser Martin Meeser · Accepted Answer · 2012-02-15T13:03:48

I am using VS2010 and MS Excel 2010. I have the very same problems (adding 300 hyperlinks via Range.Hyperlinks.Add takes approx. 2 min).

The runtime issue is because of the many Range-Instances.
Solution:
Use a single range instance and add Hyperlinks with the "=HYPERLINK(target, [friendlyName])" Excel-Formula.

Example:

List<string> urlsList = new List<string>();
urlsList.Add("http://www.gin.de");
// ^^ n times ...

// create shaped array with content 
object[,] content = new object [urlsList.Count, 1];
foreach(string url in urlsList)
{
    content[i, 1] = string.Format("=HYPERLINK(\"{0}\")", url);
}

// get Range
string rangeDescription = string.Format("A1:A{0}", urlsList.Count+1) // excel indexes start by 1
Xl.Range xlRange = worksheet.Range[rangeDescription, XlTools.missing];

// set value finally
xlRange.Value2 = content;

... takes just 1 sec ...

Optimized way of adding multiple hyperlinks in excel file with C#

1 Answers