Hyperlink is very convenient for helping people easily reach the webpage in a few seconds. While sometimes, it disturbs people since it maybe clicked by accident and the page will automatically open. Especially, when there are many hyperlinks in excel, it is really a trouble that many webpages 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 hyperlink in excel with C#/VB.NET.
I am happy to use an excel component Spire.XLS to help me realize the task in that it is really quickly to get what I want. Also Visual Studio is necessary.
Freely Download Spire.XLS
I am happy to use an excel component Spire.XLS to help me realize the task in that it is really quickly to get what I want. Also Visual Studio is necessary.
Freely Download Spire.XLS
Procedure
Step1.Create a new Project
1.Create a new project in Console Application in Visual Studio.
2.Change the Target framework to be .NET Framework 4.
Step2. Add reference.
1.Add System. Drawing and Spire.XLS as references in Project.
2.Add using at the top of the method.
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using Spire.Xls;
namespace remove_hyperlink
{
class Program
{
static void Main(string[] args)
{
…
}
VB.NET
Imports System;
Imports System.Collections.Generic;
Imports System.Linq;
Imports System.Text;
Imports System.Drawing;
Imports Spire.Xls;
namespace remove_hyperlink
Friend Class Program
Shared Sub Main(ByVal args() As String)
…
End Sub
End Class
Step1.Create a new Project
1.Create a new project in Console Application in Visual Studio.
2.Change the Target framework to be .NET Framework 4.
Step2. Add reference.
1.Add System. Drawing and Spire.XLS as references in Project.
2.Add using at the top of the method.
C#
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Drawing;
using Spire.Xls;
namespace remove_hyperlink
{
class Program
{
static void Main(string[] args)
{
…
}
VB.NET
Imports System;
Imports System.Collections.Generic;
Imports System.Linq;
Imports System.Text;
Imports System.Drawing;
Imports Spire.Xls;
namespace remove_hyperlink
Friend Class Program
Shared Sub Main(ByVal args() As String)
…
End Sub
End Class
Step3. Remove hyperlinks in excel document.
1.Create a new workbook.
C# Code:
//Create a new workbook;
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
VB.NET Code:
'create a workbook
Workbook workbook = new Workbook();
Worksheet sheet = workbook.Worksheets[0];
2.Insert text and hyperlinks in excel worksheet. This step includes set the text font, size and color.
C# Code:
//insert hyperlink
HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range["B2"]);
hylink1.Type = HyperLinkType.Unc;
hylink1.TextToDisplay = "e-iceblue";
hylink1.Address = @"http://www.e-iceblue.com";
sheet.Range["B2"].Style.Font.Color = Color.OrangeRed;
sheet.Range["B2"].Style.Font.IsBold = true;
sheet.Range["B2"].Style.Font.Size = 20;
sheet.Range["B4"].Text = "Forum";
sheet.Range["B4"].Style.Font.Color = Color.SeaGreen;
sheet.Range["B4"].Style.Font.IsBold = true;
sheet.Range["B4"].Style.Font.Size = 20;
HyperLink hylink2 = sheet.HyperLinks.Add(sheet.Range["B5"]);
hylink2.Type = HyperLinkType.Unc;
hylink2.Address = @"http://www.e-iceblue.com/forum/";
sheet.Range["B6"].Text = "Weebly";
HyperLink hylink3 = sheet.HyperLinks.Add(sheet.Range["B7"]);
hylink3.Type = HyperLinkType.Url;
hylink3.Address = @"http://www.everlating129.weebly.com";
sheet.Range["B6"].Style.Font.Color = Color.Salmon;
sheet.Range["B6"].Style.Font.Size = 20;
C# Code:
//insert hyperlink
HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range["B2"]);
hylink1.Type = HyperLinkType.Unc;
hylink1.TextToDisplay = "e-iceblue";
hylink1.Address = @"http://www.e-iceblue.com";
sheet.Range["B2"].Style.Font.Color = Color.OrangeRed;
sheet.Range["B2"].Style.Font.IsBold = true;
sheet.Range["B2"].Style.Font.Size = 20;
sheet.Range["B4"].Text = "Forum";
sheet.Range["B4"].Style.Font.Color = Color.SeaGreen;
sheet.Range["B4"].Style.Font.IsBold = true;
sheet.Range["B4"].Style.Font.Size = 20;
HyperLink hylink2 = sheet.HyperLinks.Add(sheet.Range["B5"]);
hylink2.Type = HyperLinkType.Unc;
hylink2.Address = @"http://www.e-iceblue.com/forum/";
sheet.Range["B6"].Text = "Weebly";
HyperLink hylink3 = sheet.HyperLinks.Add(sheet.Range["B7"]);
hylink3.Type = HyperLinkType.Url;
hylink3.Address = @"http://www.everlating129.weebly.com";
sheet.Range["B6"].Style.Font.Color = Color.Salmon;
sheet.Range["B6"].Style.Font.Size = 20;
VB.NET Code:
‘insert text and hyperlinks
HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range["B2"]);
hylink1.Type = HyperLinkType.Unc;
hylink1.TextToDisplay = "e-iceblue";
hylink1.Address = @"http://www.e-iceblue.com";
sheet.Range["B2"].Style.Font.Color = Color.OrangeRed;
sheet.Range["B2"].Style.Font.IsBold = true;
sheet.Range["B2"].Style.Font.Size = 20;
sheet.Range["B4"].Text = "Forum";
sheet.Range["B4"].Style.Font.Color = Color.SeaGreen;
sheet.Range["B4"].Style.Font.IsBold = true;
sheet.Range["B4"].Style.Font.Size = 20;
HyperLink hylink2 = sheet.HyperLinks.Add(sheet.Range["B5"]);
hylink2.Type = HyperLinkType.Unc;
hylink2.Address = @"http://www.e-iceblue.com/forum/";
sheet.Range["B6"].Text = "Weebly";
HyperLink hylink3 = sheet.HyperLinks.Add(sheet.Range["B7"]);
hylink3.Type = HyperLinkType.Url;
hylink3.Address = @"http://www.everlating129.weebly.com";
sheet.Range["B6"].Style.Font.Color = Color.Salmon;
sheet.Range["B6"].Style.Font.Size = 20;
Until this step, you can see the screenshot below:
‘insert text and hyperlinks
HyperLink hylink1 = sheet.HyperLinks.Add(sheet.Range["B2"]);
hylink1.Type = HyperLinkType.Unc;
hylink1.TextToDisplay = "e-iceblue";
hylink1.Address = @"http://www.e-iceblue.com";
sheet.Range["B2"].Style.Font.Color = Color.OrangeRed;
sheet.Range["B2"].Style.Font.IsBold = true;
sheet.Range["B2"].Style.Font.Size = 20;
sheet.Range["B4"].Text = "Forum";
sheet.Range["B4"].Style.Font.Color = Color.SeaGreen;
sheet.Range["B4"].Style.Font.IsBold = true;
sheet.Range["B4"].Style.Font.Size = 20;
HyperLink hylink2 = sheet.HyperLinks.Add(sheet.Range["B5"]);
hylink2.Type = HyperLinkType.Unc;
hylink2.Address = @"http://www.e-iceblue.com/forum/";
sheet.Range["B6"].Text = "Weebly";
HyperLink hylink3 = sheet.HyperLinks.Add(sheet.Range["B7"]);
hylink3.Type = HyperLinkType.Url;
hylink3.Address = @"http://www.everlating129.weebly.com";
sheet.Range["B6"].Style.Font.Color = Color.Salmon;
sheet.Range["B6"].Style.Font.Size = 20;
Until this step, you can see the screenshot below:
It is obviously that there are three links. They are in B2, B5 and B7. All them can open a certain page by clicking them. Now it’s time to remove them.
3.Remove hyperlinks. Remove hyperlinks do not mean clear hyperlinks. If they are important for you, you do not want to clear them, that is OK. Please see the code:
C# Code:
//remove hyperlinks
sheet.HyperLinks.RemoveAt(0);
sheet.HyperLinks.RemoveAt(0);
sheet.Range["B7"].ClearAll();
VB.NET Code:
‘remove hyperlinks
sheet.HyperLinks.RemoveAt(0);
sheet.HyperLinks.RemoveAt(0);
sheet.Range["B7"].ClearAll();
3.Remove hyperlinks. Remove hyperlinks do not mean clear hyperlinks. If they are important for you, you do not want to clear them, that is OK. Please see the code:
C# Code:
//remove hyperlinks
sheet.HyperLinks.RemoveAt(0);
sheet.HyperLinks.RemoveAt(0);
sheet.Range["B7"].ClearAll();
VB.NET Code:
‘remove hyperlinks
sheet.HyperLinks.RemoveAt(0);
sheet.HyperLinks.RemoveAt(0);
sheet.Range["B7"].ClearAll();
Step4. Save the project.
C# Code:
// Save the project;
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start(workbook.FileName);
VB.NET Code:
‘Save the project;
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start(workbook.FileName)
C# Code:
// Save the project;
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003);
System.Diagnostics.Process.Start(workbook.FileName);
VB.NET Code:
‘Save the project;
workbook.SaveToFile("sample.xls", ExcelVersion.Version97to2003)
System.Diagnostics.Process.Start(workbook.FileName)
Preview
From the above picture, you can see one hyperlink left, but when you click the link, there is no webpage opened. Thus, you can keep the link and it will not disturb you by your accidental click. If you have other questions about excel, please visit the excel forum, there professionals are ready to help you.
Excel Forum
Source: