Link

Link (6)

Spire.XLS supports to insert hyperlinks in Excel, and modify or remove hyperlinks in an existing Excel document. This article presents how to retrieve hyperlink information from an Excel worksheet in C# and VB.NET.

Step 1: Create a Workbook object and load a sample Excel file.

Workbook wb = new Workbook();
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Hyperlinks.xlsx");

Step 2: Get the first worksheet.

Worksheet sheet = wb.Worksheets[0];

Step 3: Get the hyperlink information from the worksheet.

foreach (var item in sheet.HyperLinks)
{
    string address = item.Address;
    CellRange range = item.Range;       
    Console.WriteLine(string.Format("Cell[{0},{1}] contains URL: {2}", range.Row, range.Column, address));
}

Output:

Retrieve Hyperlinks from an Excel Sheet in C#, VB.NET

Full Code:

[C#]
Workbook wb = new Workbook();
wb.LoadFromFile(@"C:\Users\Administrator\Desktop\Hyperlinks.xlsx");

Worksheet sheet = wb.Worksheets[0];
foreach (var item in sheet.HyperLinks)
{
    string address = item.Address;
    CellRange range = item.Range;
    Console.WriteLine(string.Format("Cell[{0},{1}] contains URL: {2}", range.Row, range.Column, address));
}
[VB.NET]
Dim wb As Workbook =  New Workbook() 
wb.LoadFromFile("C:\Users\Administrator\Desktop\Hyperlinks.xlsx")
 
Dim sheet As Worksheet =  wb.Worksheets(0) 
Dim item As var
For Each item In sheet.HyperLinks
    Dim address As String =  item.Address 
    Dim range As CellRange =  item.Range 
    Console.WriteLine(String.Format("Cell[{0},{1}] contains URL: {2}", range.Row, range.Column, address))
Next
Thursday, 01 September 2016 08:02

How to Add Image Hyperlink in Excel in C#, VB.NET

Written by support iceblue

Hyperlinks can direct readers from one file to a web address, an email address or another file. A Hyperlink can be added to a text or an image. In our previous articles, we have demonstrated how to add text hyperlink to Excel worksheet. This article will represent how hyperlink is created based on an image in C# and VB.NET.

Code Snippet:

Step 1: Create an object of Workbook and get the first worksheet.

Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

Step 2: Insert an image to a specific cell.

string picPath = @"C:\Users\Administrator\Desktop\logo.png";
ExcelPicture picture = sheet.Pictures.Add(1, 1, picPath);

Step 3: Add a hyperlink to the image using SetHyperLink(string linkString, bool isExternal) method.

picture.SetHyperLink("Http://www.e-iceblue.com", true);

Step 4: Set the 1st column width and 1st row height.

sheet.Columns[0].ColumnWidth = 22;
sheet.Rows[0].RowHeight = 140;

Step 5: Save the file.

wb.SaveToFile("ImageHyperlink.xlsx", ExcelVersion.Version2013);

Output:

How to Add Image Hyperlink in Excel in C#, VB.NET

Full Code:

[C#]
Workbook wb = new Workbook();
Worksheet sheet = wb.Worksheets[0];

sheet.Range["A1"].Text = "Image Hyperlink";
sheet.Range["A1"].Style.VerticalAlignment = VerticalAlignType.Top;

string picPath = @"C:\Users\Administrator\Desktop\logo.png";
ExcelPicture picture = sheet.Pictures.Add(1, 1, picPath);
picture.SetHyperLink("Http://www.e-iceblue.com", true);

sheet.Columns[0].ColumnWidth = 22;
sheet.Rows[0].RowHeight = 140;
picture.TopRowOffset = 25;

wb.SaveToFile("ImageHyperlink.xlsx", ExcelVersion.Version2013);
[VB.NET]
Dim wb As New Workbook()
Dim sheet As Worksheet = wb.Worksheets(0)

sheet.Range("A1").Text = "Image Hyperlink"
sheet.Range("A1").Style.VerticalAlignment = VerticalAlignType.Top

Dim picPath As String = "C:\Users\Administrator\Desktop\logo.png"
Dim picture As ExcelPicture = sheet.Pictures.Add(1, 1, picPath)
picture.SetHyperLink("Http://www.e-iceblue.com", True)

sheet.Columns(0).ColumnWidth = 22
sheet.Rows(0).RowHeight = 140
picture.TopRowOffset = 25

wb.SaveToFile("ImageHyperlink.xlsx", ExcelVersion.Version2013)
Wednesday, 28 October 2015 08:31

How to Modify Hyperlinks in Excel in C#, VB.NET

Written by support iceblue

A hyperlink that directs readers from one file to another file or an address is commonly used in our electronic documents. With the help of Spire.XLS, programmers can easily insert or remove hyperlinks in Excel sheet. In this article, you'll learn how to modify a hyperlink including the address and hyperlink text in Excel via Spire.XLS in C#, VB.NET.

Screenshot of sample file:

How to Modify Hyperlinks in Excel in C#, VB.NET

Code Snippet:

Step 1: Initialize a new instance of workbook class, load the sample file from disk.

Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];

Step 2: Get the collection of all hyperlinks in the worksheet.

HyperLinksCollection links = sheet.HyperLinks;

Step 3: Change the values of TextToDisplay and Address property of HyperLinksCollection class to reset the hyperlink text and address.

links[0].TextToDisplay = "Spire.XLS for .NET";
links[0].Address = "http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html";

Step 4: Save and launch the file.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");

Output:

How to Modify Hyperlinks in Excel in C#, VB.NET

Full Code:

[C#]
Workbook workbook = new Workbook();
workbook.LoadFromFile("sample.xlsx");
Worksheet sheet = workbook.Worksheets[0];    

HyperLinksCollection links = sheet.HyperLinks;
links[0].TextToDisplay = "Spire.XLS for .NET";
links[0].Address = "http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html";

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
System.Diagnostics.Process.Start("result.xlsx");
[VB.NET]
Dim workbook As New Workbook()
workbook.LoadFromFile("sample.xlsx")
Dim sheet As Worksheet = workbook.Worksheets(0)

Dim links As HyperLinksCollection = sheet.HyperLinks
links(0).TextToDisplay = "Spire.XLS for .NET"
links(0).Address = "http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html"

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010)
System.Diagnostics.Process.Start("result.xlsx")

There are two types of hyperlinks: one is URL type, which linked to any web protocol; the other is file path type, which usually point to a file in a folder. Before we have discussed how to manage URL hyperlinks in Excel via C#/VB.NET, thus this section will demonstrate how to manage Excel hyperlinks, which target at a file path.

Spire.Xls for .NET provides Hyperlink type property in its class design, which makes the operation related to Excel hyperlinks in C#/VB.NET quite simple and smooth for developers and programmers.

Spire.XLS for .NET is always welcome to any kind of trial and evaluation. So now please feel free to download Spire.Xls for .NET. And then follow our guide to add Excel hyperlinks in C#/VB.NET or try other function of Spire.Xls for .NET.

Step 1: Create new project and add reference

In this step, we create a new project and add spire.xls dll as its reference. The following operations relate to Excel hyperlinks are all preceded within this project.

Step 2: Import namespace

Spire.Xls is a namespace which must be used during our procedure of adding Excel hyperlinks via C#/VB.NET, so please use the code below to import it.

[C#]
using Spire.Xls;
[VB.NET]
Imports Spire.Xls

Step 3: Add an Excel hyperlink to a cell

In this step, we will load an Excel file from a folder and then add a hyperlink to a cell in C#/VB.NET. This hyperlink will link to a Excel file by setting its type. Have a check of the code sample.

[C#]
//load Excel file
Workbook workbook = new Workbook();
workbook.LoadFromFile(@"..\..\hyperlink.xlsx", ExcelVersion.Version2010);
// add hyperlink and make setting 
Worksheet sheet1 = workbook.Worksheets[0];
CellRange range = sheet1.Range["C3"];
HyperLink hyperlink1 = sheet1.HyperLinks.Add(range);
hyperlink1.Type = HyperLinkType.File;
hyperlink1.TextToDisplay = "This is a hyperlink to a target file”
hyperlink1.Address = "employee2.xlsx";
[VB.NET]
'load Excel file
Dim workbook As New Workbook()
workbook.LoadFromFile("..\..\hyperlink.xlsx", ExcelVersion.Version2010)
' add hyperlink
Dim sheet1 As Worksheet = workbook.Worksheets(0)
Dim range As CellRange = sheet1.Range("C3")
Dim hyperlink1 As HyperLink = sheet1.HyperLinks.Add(range)
hyperlink1.Type = HyperLinkType.File
hyperlink1.TextToDisplay = "This is a hyperlink"
hyperlink1.Address = "employee2.xlsx"

Finally, we can save our work and run it to preview the effect. The effect before we add Excel hyperlink in C#/VB.NET:

Add Excel Hyperlink to a File

The effect after we add Excel hyperlink in C#/VB.NET:

Add Excel Hyperlink to a File

You can see a hyperlink has been added in Excel, and if you click on the hyperlink, it will lead you to a new Excel file named employee2.xlsx.

Friday, 13 April 2012 06:48

Remove Excel Hyperlinks in C#, VB.NET

Written by support iceblue

Hyperlink is very convenient for helping people easily reach the webpage in a few seconds. While sometimes, it disturbs people since it may be clicked by accident and the page will automatically open. Especially, when there are many hyperlinks in excel, it is really a trouble that many web pages opened one by one just because your light clicks. In such a case, it is a good choice to remove hyperlinks. Now let us look at how to remove Excel hyperlink with C#/VB.NET by using Spire.XLS.

Spire.XLS Offers 2 solutions to remove Excel hyperlink via C#/VB.NET. The image below shows an Excel sample with different links. Now, we will use Spire.XLS to remove some of them.

Remove Excel Hyperlink

Step 1: Create Project

Create a C#/VB.NET project in your visual studio. Add Spire.XLS.dll as reference. The default setting of Spire.Xls.dll is placed under "C:\Program Files\e-iceblue\Spire.Xls\Bin". Select assembly Spire.Xls.dll and click OK to add it to the project.

[C#]
using Spire.Xls;

namespace remove_hyperlink
{
    class Program
    {
        static void Main(string[] args)
        {
        }
    }
}
[VB.NET]
Imports Spire.Xls

Namespace remove_hyperlink
	Class Program
		Private Shared Sub Main(args As String())
          End Sub
    End Class
End Namespace

Step 2: Load Excel File

Use the code below to load Excel file which we will remove links.

[C#]
            //Create a new workbook;
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\test1.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
[VB.NET]
           ‘Create a new workbook
          Dim workbook As New Workbook()

 

Step 3: Remove Excel Hyperlink

Remove All Link Content

Select the cell range which we want to remove all link content. Use the code below to remove them.

[C#]
            sheet.Range["B5"].ClearAll();
            sheet.Range["B11"].ClearAll();
[VB.NET]
sheet.Range("B5").ClearAll()
sheet.Range("B11").ClearAll()

Remove Hyperlink Effect and Keep Link Text

Sometimes we just need remove the link effect and keep the link text content. Spire.XLS offers sheet.HyperLinks.RemoveAt(0); method for users to remove only link effect and keep the link content. The default setting of this method means removing links one by one. If we want to remove 3 links, we can use 3 sentences of the code. If we want to remove 5 links, then just write 5 sentences.

[C#]
             sheet.HyperLinks.RemoveAt(0);
              sheet.HyperLinks.RemoveAt(0);
              sheet.HyperLinks.RemoveAt(0);
              sheet.HyperLinks.RemoveAt(0);
              sheet.HyperLinks.RemoveAt(0);
[VB.NET]
             sheet.HyperLinks.RemoveAt(0)
              sheet.HyperLinks.RemoveAt(0)
              sheet.HyperLinks.RemoveAt(0)
              sheet.HyperLinks.RemoveAt(0)
              sheet.HyperLinks.RemoveAt(0)

Step 4: Preview

Use the code below to preview output Excel file.

 

[C#]
            // Save the project
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
            System.Diagnostics.Process.Start(workbook.FileName);
[VB.NET]
            // Save the project
            workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
            System.Diagnostics.Process.Start(workbook.FileName)

Effective Screeshot:

Remove Excel Hyperlink

Wednesday, 05 January 2011 06:15

Insert Hyperlink in Excel

Written by Administrator

Excel Hyperlink guides users to get more information about data in one cell. For example, the hyperlink links text (city name) in cell to a city history webpage and can help readers learn more about a city. Besides website address, the hyperlink can be an Email address or a file. Generally speaking, if data in on cell is website address like www.e-iceblue.com or Email address, the hyperlink will be generated automatically.

Spire.XLS for .NET, a professional .NET Excel component to generate, open and write Excel files, enables users to insert hyperlink in Excel by using C#, VB.NET. This guide illustrates how to realize this function quickly via Spire.XLS for .NET.

Now, I will insert hyperlink for Text “Chicago” and the Email address besides “Chicago” cell. Invoke sheet.Hyperlinks.Add(cellRange) method to insert hyperlink for specified cell. Then, set TextToDisplay, Type, Address properties for this hyperlink.Download and install Spire.XLS for .NET. Use the following code to insert hyperlink in Excel with C#, VB.NET.

Insert Excel Hyperlink

[C#]
using Spire.Xls;

namespace ExcelHyperlink
{
    class XLSLink
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"E:\Work\Documents\ExcelFiles\Student Info.xlsx");
            Worksheet sheet = workbook.Worksheets[0];

            HyperLink UrlLink= sheet.HyperLinks.Add(sheet.Range["E8"]);
            UrlLink.TextToDisplay = sheet.Range["E8"].Text;
            UrlLink.Type=HyperLinkType.Url; 
            UrlLink.Address="http://en.wikipedia.org/wiki/Chicago";

            HyperLink MailLink = sheet.HyperLinks.Add(sheet.Range["F8"]);       
            MailLink.TextToDisplay = sheet.Range["F8"].Text;
            MailLink.Type = HyperLinkType.Url;
            MailLink.Address = "mailto:ka.Lee220@hotmail.com";

            workbook.SaveToFile("ExcelHyperlink.xlsx", ExcelVersion.Version2010);
            System.Diagnostics.Process.Start("ExcelHyperlink.xlsx");
        }
    }
}

[VB.NET]
Imports Spire.Xls

Namespace ExcelHyperlink
    Friend Class XLSLink
        Shared Sub Main(ByVal args() As String)
            Dim workbook As New Workbook()
            workbook.LoadFromFile("E:\Work\Documents\ExcelFiles\Student Info.xlsx")
            Dim sheet As Worksheet = workbook.Worksheets(0)

            Dim UrlLink As HyperLink = sheet.HyperLinks.Add(sheet.Range("E8"))
            UrlLink.TextToDisplay = sheet.Range("E8").Text
            UrlLink.Type = HyperLinkType.Url
            UrlLink.Address = "http://en.wikipedia.org/wiki/Chicago"

            Dim MailLink As HyperLink = sheet.HyperLinks.Add(sheet.Range("F8"))
            MailLink.TextToDisplay = sheet.Range("F8").Text
            MailLink.Type = HyperLinkType.Url
            MailLink.Address = "mailto:ka.Lee220@hotmail.com"

            workbook.SaveToFile("ExcelHyperlink.xlsx", ExcelVersion.Version2010)
            System.Diagnostics.Process.Start("ExcelHyperlink.xlsx")
        End Sub
    End Class
End Namespace