Microsoft Excel supports many advanced features, making it powerful and flexible. For example, Microsoft Excel can use formulas to enable dynamic data, which is one of the most interesting features regularly used by professional Excel developers. As an ETL developer, you might be asked to create an Excel file with a formula specified for some of the columns. One such example is working with a hyperlink column, where you need to provide both the text value and a link to the URL, enabling navigation to the web page when the link is clicked in Excel. Since this is a special case, your ETL software needs to provide you with such capability to enable this use case.
In our blog post today, we will show you how this can be achieved using our Premium Excel Destination Component to write Excel files with the specified hyperlink formula. You will find that this is a very straightforward process with the feature available in the software.
For this demonstration, we will be using the following components:
- Premium ADO.NET Source component (In our sample package, we have replaced it with a Data Spawner Component so that we generate some random sample data instead of relying on a db source)
- Premium Derived Column component
- Premium Excel Destination component
And we also have attached a sample package which you can download here.
Data flow configuration
Let's take the example below as the end result we would like to achieve. For our business case, column C in the Excel file has the 'Link' which would be the hyperlink that we are writing along with a label as "Click Here".
You can write links to an Excel file using the Premium Excel Destination and Excel's hyperlink function - HYPERLINK(link_location, [friendly_name]). The hyperlink function allows you to add your own jump text to be shown instead of displaying the URL, which could disturb the Excel file's formatting. To add the Hyperlink Function, you will be using a Premium Derived Column component between your Source and Destination, and the below function could be used.
Using the Premium Derived Column component, you can enter in the HYPERLINK function as a new column. Please note that our example is static (as www.kingswaysoft.com) to show the process, but if you have multiple links, you can set it as an expression to make it dynamic. To achieve this, you can drag and drop a column for the link_location portion in the function and specify the label you would like for the hyperlink, if that information is coming from an upstream component.
The Derived Column expression for this column is like something below.
"=HYPERLINK(\"https://www.kingswaysoft.com/\", \"(Click Here)\")"
As you can see, HYPERLINK is an Excel function that we will be passing to the column when we write to the Excel file in the Premium Excel destination component. This can be replaced to any other valid Excel functions. In our case, it is a static link to the same URL using the same text label, but you can easily change it to include values from upstream components or even SSIS variables to make it dynamic.
Afterwards you can map the column you created in the Excel Destination Component.. Make sure that you have set the InputFormat option to Formula so that the Excel component properly handle it as a formula instead of some static text.
If you wish to change the color of the link to blue to make it more apparent that it is a hyperlink, you can set that in the Excel Destination as well using the Data Cell Style. 
It's also worth noting that the Excel Destination component also supports many more functions and formulas that can be achieved by using the same technique as above.
Conclusion
By learning how to set formulas for your Excel sheets using our KingswaySoft Excel Destination component, we believe this can add more value to your integration solutions. It can also reduce the manual work required to format each hyperlink and input it using a different process, rather than dynamically setting it with a similar data flow design.
We hope this has helped!