Knowledgebase (2330)
Children categories
Textboxes in Excel are versatile tools that allow users to add annotations, labels, or any additional information to their spreadsheets. Whether you want to highlight important data, provide explanations, or create visually appealing reports, managing textboxes is essential.
In this article, you will learn how to add a textbox, extract content from an existing textbox, and remove a textbox in Excel using C# and Spire.XLS for .NET.
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Add a Textbox to Excel in C#
A textbox can be added to a worksheet using the Worksheet.TextBoxes.AddTextBox() method. This method returns an ITextBoxShape object, which contains properties such as Text, HAlignment, and Fill, for configuring the text and formatting of the textbox.
The steps to add a textbox with customized text and formatting to Excel are as follows:
- Create a Workbook object.
- Load an Excel file from the specified file path.
- Get a specific worksheet from the workbook.
- Add a textbox to the worksheet at the specified location using Worksheet.TextBoxes.AddTextBox() method.
- Set the text of the textbox using ITextBoxShape.Text property.
- Customize the appearance of the textbox using other properties of the ITextBoxShape object.
- Save the workbook to a different Excel file.
- C#
using Spire.Xls;
using Spire.Xls.Core;
using System.Drawing;
namespace AddTextbox
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel document
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Input.xlsx");
// Get a specific sheet
Worksheet sheet = workbook.Worksheets[0];
// Add a textbox to the specified location
ITextBoxShape textBoxShape = sheet.TextBoxes.AddTextBox(3, 3, 60, 200);
// Set text of the textbox
textBoxShape.Text = "This is a text box, with sample text.";
// Create a font
ExcelFont font = workbook.CreateFont();
font.FontName = "Calibri";
font.Size = 14;
font.Color = Color.Red;
// Apply font to the text
textBoxShape.RichText.SetFont(0, textBoxShape.Text.Length - 1, font);
// Set horizontal alignment
textBoxShape.HAlignment = CommentHAlignType.Left;
// Set the fill color of the shape
textBoxShape.Fill.FillType = ShapeFillType.SolidColor;
textBoxShape.Fill.ForeColor = Color.LightGreen;
// Save the Excel file
workbook.SaveToFile("output/AddTextBox.xlsx", ExcelVersion.Version2010);
// Dispose resources
workbook.Dispose();
}
}
}
Extract Text from a Textbox in Excel in C#
A specific textbox can be accessed using the Worksheet.TextBoxes[index] property. Once retrieved, the textbox's text can be accessed through the ITextBox.Text property.
The steps to extract text from a textbox in Excel are as follows:
- Create a Workbook object.
- Load an Excel file from the specified file path.
- Get a specific worksheet from the workbook.
- Get the text of a specific textbox using Worksheet.TextBoxes[index] property.
- Get the text of the textbox using ITextBox.Text property.
- C#
using Spire.Xls;
using Spire.Xls.Core;
namespace ExtractTextFromTextbox
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel file
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\TextBox.xlsx");
// Get a specific worksheet
Worksheet sheet = workbook.Worksheets[0];
// Get a specific textbox
ITextBox textBox = sheet.TextBoxes[0];
// Get text from the textbox
String text = textBox.Text;
// Print out result
Console.WriteLine(text);
}
}
}
Remove a Textbox from Excel in C#
To remove a specific textbox from a worksheet, use the Worksheet.TextBoxes[index].Remove() method. To clear all textboxes, retrieve the count with the Worksheet.TextBoxes.Count property and iterate through the collection, removing each textbox individually.
The steps to remove a textbox from Excel are as follows:
- Create a Workbook object.
- Load an Excel file from the specified file path.
- Get a specific worksheet from the workbook.
- Remove a specific textbox using Worksheet.TextBoxes[index].Remove() method.
- Save the workbook to a different Excel file.
- C#
using Spire.Xls;
namespace RemoveTextbox
{
class Program
{
static void Main(string[] args)
{
// Create a Workbook object
Workbook workbook = new Workbook();
// Load an Excel file
workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\TextBox.xlsx");
// Get a specific worksheet
Worksheet sheet = workbook.Worksheets[0];
// Remove a specific textbox
sheet.TextBoxes[0].Remove();
// Save the updated document to a different Excel file
workbook.SaveToFile("output/RemoveTextbox.xlsx", ExcelVersion.Version2016);
// Dispose resources
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
Hyperlinks are an essential element in Excel that allows users to reference external data sources, navigate between worksheets, or provide additional information about specific cells. When working with an Excel file, you may need to manipulate hyperlinks for various reasons. For example, you may need to extract all the hyperlinks from the file to perform an analysis, modify an outdated hyperlink to ensure accuracy or remove a broken hyperlink to improve the document's usability. In this article, we will explain how to extract, modify and remove hyperlinks in Excel in C# and VB.NET using Spire.XLS for .NET.
- Extract Hyperlinks from Excel in C# and VB.NET
- Modify Hyperlinks in Excel in C# and VB.NET
- Remove Hyperlinks from Excel in C# and VB.NET
Install Spire.XLS for .NET
To begin with, you need to add the DLL files included in the Spire.XLS for .NET package as references in your .NET project. The DLL files can be either downloaded from this link or installed via NuGet.
PM> Install-Package Spire.XLS
Extract Hyperlinks from Excel in C# and VB.NET
If you are migrating data from an Excel workbook to another system (such as a database) and need to preserve the hyperlinks associated with that data, extracting hyperlinks from the Excel file beforehand is necessary.
The following steps demonstrate how to extract hyperlinks from an Excel file in C# and VB.NET using Spire.XLS for .NET:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.Hyperlinks property.
- Initialize an instance of the StringBuilder class to store the extracted hyperlink information.
- Iterate through the hyperlinks in the hyperlinks collection.
- Get the address and type of each hyperlink using the XlsHyperlink.Address and XlsHyperlink.Type properties.
- Append the address and type to the StringBuilder instance.
- Write the content of the StringBuilder instance into a text file using the File.WriteAllText() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core.Spreadsheet;
using System.IO;
using System.Text;
namespace ExtractHyperlinks
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Hyperlinks1.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the collection of all hyperlinks in the worksheet
HyperLinksCollection hyperLinks = sheet.HyperLinks;
//Initialize an instance of the StringBuilder class
StringBuilder sb = new StringBuilder();
//Iterate through the hyperlinks in the collection
foreach (XlsHyperLink hyperlink in hyperLinks)
{
//Get the address of the hyperlink
string address = hyperlink.Address;
//Get the type of the hyperlink
HyperLinkType type = hyperlink.Type;
//Append the address and type of the hyperlink to the StringBuilder instance
sb.AppendLine("Link address: " + address);
sb.AppendLine("Link type: " + type.ToString());
sb.AppendLine();
}
//Write the content of the StringBuilder instance to a text file
File.WriteAllText("GetHyperlinks.txt", sb.ToString());
workbook.Dispose();
}
}
}
Modify Hyperlinks in Excel in C# and VB.NET
If you've accidentally linked to the wrong resource or entered an incorrect URL when creating a hyperlink, you may need to modify the hyperlink to correct the mistake.
The following steps demonstrate how to modify an existing hyperlink in an Excel file:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Get the collection of all hyperlinks in the worksheet using the Worksheet.Hyperlinks property.
- Get the first hyperlink in the collection.
- Modify the display text and address of the hyperlink using the XlsHyperlink.TextToDisplay and XlsHyperlink.Address properties.
- Save the result file using the Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
using Spire.Xls.Collections;
using Spire.Xls.Core.Spreadsheet;
namespace ModifyHyperlinks
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Hyperlinks2.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Get the collection of all hyperlinks in the worksheet
HyperLinksCollection links = sheet.HyperLinks;
//Get the first hyperlink in the collection
XlsHyperLink hyperLink = links[0];
//Modify the display text and the address of the hyperlink
hyperLink.TextToDisplay = "Spire.XLS for .NET";
hyperLink.Address = "http://www.e-iceblue.com/Introduce/excel-for-net-introduce.html";
//Save the result file
workbook.SaveToFile("ModifyHyperlink.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
Remove Hyperlinks from Excel in C# and VB.NET
Removing the irrelevant hyperlinks can help make your worksheet neater and more professional-looking.
The following steps demonstrate how to remove a specific hyperlink from an Excel file:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile() method.
- Get a specific worksheet using the Workbook.Worksheets[int index] property.
- Remove a specific hyperlink from the worksheet using the Worksheet.Hyperlinks.RemoveAt(int index) method.
- Save the result file using the Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace RemoveHyperlinks
{
internal class Program
{
static void Main(string[] args)
{
//Initialize an instance of the Workbook class
Workbook workbook = new Workbook();
//Load an Excel file
workbook.LoadFromFile("Hyperlinks2.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Remove the first hyperlink and keep its display text
sheet.HyperLinks.RemoveAt(0);
//Remove all content from the cell
//sheet.Range["B2"].ClearAll();
//Save the result file
workbook.SaveToFile("RemoveHyperlink.xlsx", ExcelVersion.Version2013);
workbook.Dispose();
}
}
}
Apply for a Temporary License
If you'd like to remove the evaluation message from the generated documents, or to get rid of the function limitations, please request a 30-day trial license for yourself.
The sample demonstrates how to Set PDF Text Format for Silverlight via Spire.PDF.
