Problem

Sometimes your application requires creating a column of hyperlinks from URLs stored as strings. When ExcelTemplate populates a data marker with these strings, they remain static.

Solution

There are two ways to turn a column of strings into a column of hyperlinks:

1. Fill a hidden column in the template with the URLs and use Excel’s HYPERLINK function to build the hyperlinks.
2. Open the populated spreadsheet with the ExcelApplication object and use Cell.CreateHyperlink to turn the cell values into links.

Using the HYPERLINK Function

In this approach you can create a formula in a column in your worksheet that will reference another column that holds the URLs. To do this, place the datamarker for your URLs in an unused column on the same row in the same worksheet as the column that will contain the hyperlinks. For example, if you want the hyperlinks to begin in cell C5, place the data marker in row five of some other column, for instance AA5.

Now use Excel’s HYPERLINK() formula in C5 to reference the datamarker. Set C5’s formula to “=HYPERLINK(AA5)”, and as the datamarker in AA5 gets filled with URLs and stretches downward, the formula in C5 that references it will also stretch. If you don’t want the readers of the spreadsheet to be able to see the column of raw URLs, you can set it to hidden. In Excel, just right-click on a column header (the letter at the top) and choose ‘Hide’ from the drop-down menu. Now the column will not be visible, though it can be unhidden.

This method can be faster than using the ExcelApplication method (below), since it doesn’t require opening the file with ExcelApplication, but it has the disadvantage of leaving a column full of raw data in the worksheet.

Using ExcelApplication

After filling a column with URLs, you can open the workbook with the ExcelApplication object and convert the strings to hyperlinks. To iterate over a column of URLs and turn them all to hyperlinks, do something like this:


ExcelTemplate xlt = new ExcelTemplate();
xlt.Open("C:\\path_to_template\\template.xls");

// ... Data binding code ...

// Populate the spreadsheet and open it with ExcelApplication
xlt.Process();
ExcelApplication xla = new ExcelApplication();
Workbook wb = xla.Open(xlt);
Worksheet ws = wb["Sheet With URLS"];

// Define the area to convert to hyperlinks
Area URLArea = ws.CreateArea("C3:C90");
for (int i = 0; i < URLArea.RowCount; i++)
{
// Convert each cell to a hyperlink pointing to its URL
Cell c = URLArea[0, i];
c.CreateHyperlink(c.Value);
}

This will create hyperlinks in every cell in that Area using the value of the cell as the URL. Each cell will display the URL as the link text. If you want the cells to show some other text, change c.Value after the call to CreateHyperlink.

Keep in mind when using this technique that opening large files with the ExcelApplication object can be memory-intensive as it needs to create objects for each filled cell.

Follow

Related posts: