.NET (1273)
Children categories
A table provides fast and efficient access to data displayed in rows and columns in a visually appealing manner. When presented in a table, data has a greater impact than when just used as words and enables readers to easily compare and understand relationships between them. In this article, you will learn how to create a table in PDF in C# and VB.NET using Spire.PDF for .NET.
Spire.PDF for .NET offers the PdfTable and the PdfGrid class to work with the tables in a PDF document. The PdfTable class is used to quickly create simple, regular tables without too much formatting, while the PdfGrid class is used to create more complex tables.
The table below lists the differences between these two classes.
| PdfTable | PdfGrid | |
| Formatting | ||
| Row | Can be set through events. No API support. | Can be set through API. |
| Column | Can be set through API (StringFormat). | Can be set through API (StringFormat). |
| Cell | Can be set through events. No API support. | Can be set through API. |
| Others | ||
| Column span | Not support. | Can be set through API. |
| Row span | Can be set through events. No API support. | Can be set through API. |
| Nested table | Can be set through events. No API support. | Can be set through API. |
| Events | BeginCellLayout, EndCellLayout, BeginRowLayout, EndRowLayout, BeginPageLayout, EndPageLayout. | BeginPageLayout, EndPageLayout. |
The following sections demonstrate how to create a table in PDF using the PdfTable class and the PdfGrid class, respectively.
Install Spire.PDF for .NET
To begin with, you need to add the DLL files included in the Spire.PDF 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.PDF
Create a Table Using PdfTable Class
The following are the steps to create a table using the PdfTable class.
- Create a PdfDocument object.
- Add a page to it using PdfDocument.Pages.Add() method.
- Create a Pdftable object.
- Set the table style through PdfTable.Style property.
- Insert data to table through PdfTable.DataSource property.
- Set row height and row color through BeginRowLayout event.
- Draw table on the PDF page using PdfTable.Draw() method.
- Save the document to a PDF file using PdfDocument.SaveToFile() method.
- C#
- VB.NET
using System;
using System.Data;
using System.Drawing;
using Spire.Pdf;
using Spire.Pdf.Graphics;
using Spire.Pdf.Tables;
namespace CreateTable
{
class Program
{
static void Main(string[] args)
{
//Create a PdfDocument object
PdfDocument doc = new PdfDocument();
//Add a page
PdfPageBase page = doc.Pages.Add(PdfPageSize.A4, new PdfMargins(40));
//Create a PdfTable object
PdfTable table = new PdfTable();
//Set font for header and the rest cells
table.Style.DefaultStyle.Font = new PdfTrueTypeFont(new Font("Times New Roman", 12f, FontStyle.Regular), true);
table.Style.HeaderStyle.Font = new PdfTrueTypeFont(new Font("Times New Roman", 12f, FontStyle.Bold), true);
//Crate a DataTable
DataTable dataTable = new DataTable();
dataTable.Columns.Add("ID");
dataTable.Columns.Add("Name");
dataTable.Columns.Add("Department");
dataTable.Columns.Add("Position");
dataTable.Columns.Add("Level");
dataTable.Rows.Add(new string[] { "1", "David", "IT", "Manager", "1" });
dataTable.Rows.Add(new string[] { "3", "Julia", "HR", "Manager", "1" });
dataTable.Rows.Add(new string[] { "4", "Sophie", "Marketing", "Manager", "1" });
dataTable.Rows.Add(new string[] { "7", "Wickey", "Marketing", "Sales Rep", "2" });
dataTable.Rows.Add(new string[] { "9", "Wayne", "HR", "HR Supervisor", "2" });
dataTable.Rows.Add(new string[] { "11", "Mia", "Dev", "Developer", "2" });
//Set the datatable as the data source of table
table.DataSource = dataTable;
//Show header(the header is hidden by default)
table.Style.ShowHeader = true;
//Set font color and backgroud color of header row
table.Style.HeaderStyle.BackgroundBrush = PdfBrushes.Gray;
table.Style.HeaderStyle.TextBrush = PdfBrushes.White;
//Set text alignment in header row
table.Style.HeaderStyle.StringFormat = new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
//Set text alignment in other cells
for (int i = 0; i < table.Columns.Count; i++)
{
table.Columns[i].StringFormat = new PdfStringFormat(PdfTextAlignment.Center, PdfVerticalAlignment.Middle);
}
//Register with BeginRowLayout event
table.BeginRowLayout += Table_BeginRowLayout;
//Draw table on the page
table.Draw(page, new PointF(0, 30));
//Save the document to a PDF file
doc.SaveToFile("PdfTable.pdf");
}
//Event handler
private static void Table_BeginRowLayout(object sender, BeginRowLayoutEventArgs args)
{
//Set row height
args.MinimalHeight = 20f;
//Alternate row color
if (args.RowIndex < 0)
{
return;
}
if (args.RowIndex % 2 == 1)
{
args.CellStyle.BackgroundBrush = PdfBrushes.LightGray;
}
else
{
args.CellStyle.BackgroundBrush = PdfBrushes.White;
}
}
}
}
Create a Table Using PdfGrid Class
Below are the steps to create a table using the PdfGrid class.
- Create a PdfDocument object.
- Add a page to it using PdfDocument.Pages.Add() method.
- Create a PdfGrid object.
- Set the table style through PdfGrid.Style property.
- Add rows to the table using PdfGrid.Rows.Add() method.
- Insert data to specific cells through PdfGridRow.Cells[index].Value property.
- Span cells across columns or rows through PdfGridRow.RowSpan or PdfGridRow.ColumnSpan property.
- Set the formatting of a specific cell through PdfGridRow.Cells[index].StringFormat and PdfGridRow.Cells[index].Style properties.
- Draw table on the PDF page using PdfGrid.Draw() method.
- Save the document to a PDF file using PdfDocument.SaveToFile() method.
- C#
- VB.NET
using Spire.Pdf;
using Spire.Pdf.Graphics;
using Spire.Pdf.Grid;
using System.Drawing;
namespace CreateGrid
{
class Program
{
static void Main(string[] args)
{
//Create a PdfDocument object
PdfDocument doc = new PdfDocument();
//Add a page
PdfPageBase page = doc.Pages.Add(PdfPageSize.A4,new PdfMargins(40));
//Create a PdfGrid
PdfGrid grid = new PdfGrid();
//Set cell padding
grid.Style.CellPadding = new PdfPaddings(1, 1, 1, 1);
//Set font
grid.Style.Font = new PdfTrueTypeFont(new Font("Times New Roman", 13f, FontStyle.Regular), true);
//Add rows
PdfGridRow row1 = grid.Rows.Add();
PdfGridRow row2 = grid.Rows.Add();
PdfGridRow row3 = grid.Rows.Add();
PdfGridRow row4 = grid.Rows.Add();
grid.Columns.Add(4);
//Set column width
foreach (PdfGridColumn col in grid.Columns)
{
col.Width = 110f;
}
//Write data into specific cells
row1.Cells[0].Value = "Order and Payment Status";
row2.Cells[0].Value = "Order number";
row2.Cells[1].Value = "Date";
row2.Cells[2].Value = "Customer";
row2.Cells[3].Value = "Paid or not";
row3.Cells[0].Value = "00223";
row3.Cells[1].Value = "2022/06/02";
row3.Cells[2].Value = "Brick Lane Realty";
row3.Cells[3].Value = "Yes";
row4.Cells[0].Value = "00224";
row4.Cells[1].Value = "2022/06/03";
row4.Cells[3].Value = "No";
//Span cell across columns
row1.Cells[0].ColumnSpan = 4;
//Span cell across rows
row3.Cells[2].RowSpan = 2;
//Set text alignment of specific cells
row1.Cells[0].StringFormat = new PdfStringFormat(PdfTextAlignment.Center);
row3.Cells[2].StringFormat = new PdfStringFormat(PdfTextAlignment.Left, PdfVerticalAlignment.Middle);
//Set background color of specific cells
row1.Cells[0].Style.BackgroundBrush = PdfBrushes.Orange;
row4.Cells[3].Style.BackgroundBrush = PdfBrushes.LightGray;
//Format cell border
PdfBorders borders = new PdfBorders();
borders.All = new PdfPen(Color.Orange, 0.8f);
foreach (PdfGridRow pgr in grid.Rows)
{
foreach (PdfGridCell pgc in pgr.Cells)
{
pgc.Style.Borders = borders;
}
}
//Draw table on the page
grid.Draw(page, new PointF(0, 30));
//Save the document to a PDF file
doc.SaveToFile("PdfGrid.pdf");
}
}
}
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.
When receiving or downloading a Word document from the Internet, you may sometimes need to extract content from the document for other purposes. In this article, you will learn how to programmatically extract text and images from a Word document using Spire.Doc for .NET.
Install Spire.Doc for .NET
To begin with, you need to add the DLL files included in the Spire.Doc 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.Doc
Extract Text from a Word Document
Below are detailed steps on how to extract text from a Word document and save in a TXT file.
- Create a Document instance.
- Load a sample Word document using Document.LoadFromFile() method.
- Create a StringBuilder instance.
- Get each paragraph of each section in the document.
- Get the text of a specified paragraph using Paragraph.Text property, and then append the extracted text to the StringBuilder instance using StringBuilder.AppendLine() method.
- Create a new txt file and write the extracted text to the file using File.WriteAllText() method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using System.Text;
using System.IO;
namespace ExtractTextfromWord
{
class ExtractText
{
static void Main(string[] args)
{
//Create a Document instance
Document document = new Document();
//Load a sample Word document
document.LoadFromFile("input.docx");
//Create a StringBuilder instance
StringBuilder sb = new StringBuilder();
//Extract text from Word and save to StringBuilder instance
foreach (Section section in document.Sections)
{
foreach (Paragraph paragraph in section.Paragraphs)
{
sb.AppendLine(paragraph.Text);
}
}
//Create a new txt file to save the extracted text
File.WriteAllText("Extract.txt", sb.ToString());
}
}
}
Extract Images from a Word Document
Below are detailed steps on how to extract all images from a Word document.
- Create a Document instance and load a sample Word document.
- Get each paragraph of each section in the document.
- Get each document object of a specific paragraph.
- Determine whether the document object type is picture. If yes, save the image out of the document using DocPicture.Image.Save(String, ImageFormat) method.
- C#
- VB.NET
using Spire.Doc;
using Spire.Doc.Documents;
using Spire.Doc.Fields;
using System;
namespace ExtractImage
{
class Program
{
static void Main(string[] args)
{
//Load a Word document
Document document = new Document("input.docx");
int index = 0;
//Get each section of document
foreach (Section section in document.Sections)
{
//Get each paragraph of section
foreach (Paragraph paragraph in section.Paragraphs)
{
//Get each document object of a specific paragraph
foreach (DocumentObject docObject in paragraph.ChildObjects)
{
//If the DocumentObjectType is picture, save it out of the document
if (docObject.DocumentObjectType == DocumentObjectType.Picture)
{
DocPicture picture = docObject as DocPicture;
picture.Image.Save(string.Format("image_{0}.png", index), System.Drawing.Imaging.ImageFormat.Png);
index++;
}
}
}
}
}
}
}
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.
In Excel, a page break is a separator that divides a worksheet into several different pages/segments for the purpose of better printing. By inserting page breaks where necessary, you can avoid misalignment of data and ensure a desired print result, which is especially useful when working with a large data set. This article will demonstrate how to programmatically insert horizontal or vertical page breaks in Excel using Spire.XLS for .NET.
- Insert Horizontal Page Breaks in an Excel Worksheet
- Insert Vertical Page Breaks in an Excel Worksheet
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
Insert Horizontal Page Breaks in an Excel Worksheet
A horizontal page break is inserted between a selected row and the row above it. After insertion, the selected row will become the top row of the new page. With Spire.XLS for .NET, developers are allowed to use the Worksheet.HPageBreaks.Add(CellRange) method to insert horizontal page breaks. The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Add horizontal page break to a specified cell range using Worksheet.HPageBreaks.Add(CellRange) method.
- Set view mode to Preview mode using Worksheet.ViewMode property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace EditExcelComment
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile("input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Set Excel page break horizontally
sheet.HPageBreaks.Add(sheet.Range["A7"]);
sheet.HPageBreaks.Add(sheet.Range["A18"]);
//Set view mode to Preview mode
sheet.ViewMode = ViewMode.Preview;
//Save the result document
workbook.SaveToFile("SetHorizontalPageBreak.xlsx");
}
}
}
Insert Vertical Page Breaks in an Excel Worksheet
A vertical page break is inserted between a selected column and the column to its left. After insertion, the selected column will become the left most column of the new page. To insert vertical page breaks, developers can use the Worksheet.VPageBreaks.Add(CellRange) method offered by Spire.XLS for .NET offers. The detailed steps are as follows.
- Create a Workbook instance.
- Load a sample Excel file using Workbook.LoadFromFile() method.
- Get a specified worksheet using Workbook.Worksheets[sheetIndex] property.
- Add vertical page break to a specified cell range using Worksheet.VPageBreaks.Add(CellRange) method.
- Set view mode to Preview mode using Worksheet.ViewMode property.
- Save the result file using Workbook.SaveToFile() method.
- C#
- VB.NET
using Spire.Xls;
namespace EditExcelComment
{
class Program
{
static void Main(string[] args)
{
//Create a Workbook instance
Workbook workbook = new Workbook();
//Load a sample Excel document
workbook.LoadFromFile("input.xlsx");
//Get the first worksheet
Worksheet sheet = workbook.Worksheets[0];
//Set Excel page break vertically
sheet.VPageBreaks.Add(sheet.Range["B1"]);
//Set view mode to Preview mode
sheet.ViewMode = ViewMode.Preview;
//Save the result document
workbook.SaveToFile("SetVerticalPageBreak.xlsx");
}
}
}
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.
A common use of HTML is to display data and information on websites, web applications, and a variety of platforms. It is sometimes necessary to convert HTML to images like JPG, PNG, TIFF, BMP etc. since images are difficult to modify and can be accessed by virtually anyone. This article will show you how to perform the HTML to images conversion programmatically in C# and VB.NET using Spire.Doc for .NET.
Install Spire.Doc for .NET
To begin with, you need to add the DLL files included in the Spire.Doc 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.Doc
Convert HTML to Images
Spire.Doc for .NET offers the Document.SaveToImages() method to convert HTML to Images. Here are detailed steps.
- Create a Document instance.
- Load an HTML sample document using Document.LoadFromFile() method.
- Save the document as an image using Document.SaveToImages() method.
- C#
- VB.NET
using System.Drawing;
using Spire.Doc;
using Spire.Doc.Documents;
using System.Drawing.Imaging;
namespace HTMLToImage
{
class Program
{
static void Main(string[] args)
{
//Create a Document instance
Document mydoc = new Document();
//Load an HTML sample document
mydoc.LoadFromFile(@"sample.html", FileFormat.Html, XHTMLValidationType.None);
//Save to image. You can convert HTML to BMP, JPEG, PNG, GIF, Tiff etc
Image image = mydoc.SaveToImages(0, ImageType.Bitmap);
image.Save("HTMLToImage.png", ImageFormat.Png);
}
}
}
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.
Excel's Find and Replace feature is an indispensable tool for users when editing large Excel spreadsheets. It allows users to search for specific values within a worksheet or cell range and update them with new values quickly and accurately. With this feature, users don't need to perform manual searches, which significantly improves their working efficiency. In this article, we will introduce how to programmatically find and replace data in Excel in C# and VB.NET using Spire.XLS for .NET library.
- Find and Replace Data in a Worksheet in Excel
- Find and Replace Data in a Specific Cell Range in Excel
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
Find and Replace Data in a Worksheet in Excel in C# and VB.NET
Spire.XLS for .NET offers the Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method which enables you to find the cells containing specific data values in an Excel worksheet. Once the cells are found, you can use the CellRange.Text property to update their values with new values. The detailed steps are as follows:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile(string fileName) method.
- Get a specific worksheet of the file using the Workbook.Worksheets[int index] property.
- Find the cells containing a specific value in the worksheet using the Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method.
- Iterate through the found cells.
- Update the value of each cell with another value using the CellRange.Text property.
- Set a background for the cell so you can easily find the updated cells using the CellRange.Style.Color property.
- Save the result file to a specific location using the Workbook.SaveToFile(string fileName, ExcelVersion version) method.
- C#
- VB.NET
using Spire.Xls;
using System.Drawing;
namespace ReplaceDataInWorksheet
{
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(@"Sample.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Find the cells with the specific string value “Total” in the worksheet
CellRange[] cells = worksheet.FindAllString("Total", true, true);
//Iterate through the found cells
foreach (CellRange cell in cells)
{
//Replace the value of the cell with another value
cell.Text = "Sum";
//Set a background color for the cell
cell.Style.Color = Color.Yellow;
}
//Save the result file to a specific location
workbook.SaveToFile("ReplaceDataInWorksheet.xlsx", ExcelVersion.Version2016);
workbook.Dispose();
}
}
}
Find and Replace Data in a Specific Cell Range in Excel in C# and VB.NET
You can find the cells containing a specific value in a cell range using the CellRange.FindAllString(string stringValue, bool formula, bool formulaValue) method. Then you can update the value of each found cell with another value using the CellRange.Text property. The detailed steps are as follows:
- Initialize an instance of the Workbook class.
- Load an Excel file using the Workbook.LoadFromFile(string fileName) method.
- Get a specific worksheet of the file using the Workbook.Worksheets[int index] property.
- Get a specific cell range of the worksheet using the Worksheet.Range[string rangeName] property.
- Find the cells with a specific value in the cell range using the CellRange.FindAllString(string stringValue, bool formula, bool formulaValue) method.
- Iterate through the found cells.
- Update the value of each found cell to another value using the CellRange.Text property.
- Set a background for the cell so you can easily find the updated cells using the CellRange.Style.Color property.
- Save the result file to a specific location using the Workbook.SaveToFile(string fileName, ExcelVersion version) method.
- C#
- VB.NET
using Spire.Xls;
using System.Drawing;
namespace ReplaceDataInCellRange
{
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(@"Sample.xlsx");
//Get the first worksheet
Worksheet worksheet = workbook.Worksheets[0];
//Get a specific cell range
CellRange range = worksheet.Range["A1:C9"];
//Find the cells with the specific value "Total" in the cell range
CellRange[] cells = range.FindAllString("Total", true, true);
//Iterate through the found cells
foreach (CellRange cell in cells)
{
//Replace the value of the cell with another value
cell.Text = "Sum";
//Set a background color for the cell
cell.Style.Color = Color.Yellow;
}
//Save the result file to a specific location
workbook.SaveToFile("ReplaceDataInCellRange.xlsx", ExcelVersion.Version2016);
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.
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.
C#/VB.NET: Extract, Modify or Remove Hyperlinks in Excel
2023-06-16 06:48:00 Written by support iceblueHyperlinks 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.
The sample demonstrates how to Create Table in Word for Silverlight via Spire.Doc.
The sample demonstrates how to Edit Excel in Silverlight via Spire.XLS.
