C#/VB.NET: Protect Word Documents

If your Word document contains sensitive or confidential information that you don’t want anyone else to access, protect it with a password is a good idea. This article demonstrates how to protect a Word document with a password in C# and VB.NET. Moreover, it also covers how to make a Word document ready only along with how to allow certain types of editing in a Word document.

Installation

This article uses Free Spire.Doc for .NET library to protect Word documents. The DLL files of the library can be either downloaded from the official website or installed via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then execute the following command:

PM> Install-Package FreeSpire.Doc

Protect Word Document with Password

You can follow the steps below protect a Word document with password:

  • Create an instance of Document class.
  • Load the Word document using Document.LoadFromFile() method.
  • Protect the document with your password using Document.Encrypt() method.
  • Save the result document using Document.SaveToFile() method.

C#

using Spire.Doc;

namespace ProtectWordDocumentWithPassword
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document document = new Document();
            //Load a Word document
            document.LoadFromFile("Sample.docx");

            //Protect the document with your password
            document.Encrypt("password");

            //Save the result document
            document.SaveToFile("PasswordProtected.docx", FileFormat.Docx);
        }
    }
}

VB.NET

Imports Spire.Doc

Namespace ProtectWordDocumentWithPassword
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Document instance
            Dim document As Document = New Document()
            'Load a Word document
            document.LoadFromFile("Sample.docx")

            'Protect the document with your password
            document.Encrypt("password")

            'Save the result document
            document.SaveToFile("PasswordProtected.docx", FileFormat.Docx)
        End Sub
    End Class
End Namespace
Protect Word document with password in C# and VB.NET

Make a Word Document Read Only

If you do not want recipients to accidentally change your document, you can make the document read only by using the Document.Protect(ProtectionType.AllowOnlyReading, string) method.

The following are the steps to do so:

  • Create an instance of Document class.
  • Load the Word document using Document.LoadFromFile() method.
  • Make the document read only using Document.Protect(ProtectionType.AllowOnlyReading, string) method.
  • Save the result document using Document.SaveToFile() method.

C#

using Spire.Doc;

namespace MakeWordDocumentReadOnly
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document document = new Document();
            //Load a Word document
            document.LoadFromFile("Sample.docx");

            //Make the document read only
            document.Protect(ProtectionType.AllowOnlyReading, "password");

            //Save the result document
            document.SaveToFile("ReadOnly.docx");
        }
    }
}

VB.NET:

Imports Spire.Doc

Namespace MakeWordDocumentReadOnly
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Document instance
            Dim document As Document = New Document()
            'Load a Word document
            document.LoadFromFile("Sample.docx")

            'Make the document read only
            document.Protect(ProtectionType.AllowOnlyReading, "password")

            'Save the result document
            document.SaveToFile("ReadOnly.docx")
        End Sub
    End Class
End Namespace

Allow Certain Types of Editing in a Word Document

You can control the types of changes others can make to your document, such as filling in forms only or adding comments only, by using the Document.Protect(ProtectionType, string) method.

Here are the protection types you can set:

  • ProtectionType.AllowOnlyFormFields: allowing filling in forms only
  • ProtectionType.AllowOnlyComments: allowing adding comments only
  • ProtectionType.AllowOnlyRevisions: allowing revisions only

The following code example shows how to allow adding comments only in a Word document:

C#

using Spire.Doc;

namespace RestrictEditingType
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Document instance
            Document document = new Document();
            //Load a Word document
            document.LoadFromFile("Sample.docx");

            //Allow adding comments only
            document.Protect(ProtectionType.AllowOnlyComments, "password");

            //Save the result document
            document.SaveToFile("CommentsOnly.docx");
        }
    }
}

VB.NET

Imports Spire.Doc

Namespace RestrictEditingType
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Document instance
            Dim document As Document = New Document()
            'Load a Word document
            document.LoadFromFile("Sample.docx")

            'Allow adding comments only
            document.Protect(ProtectionType.AllowOnlyComments, "password")

            'Save the result document
            document.SaveToFile("CommentsOnly.docx")
        End Sub
    End Class
End Namespace

See More

Product Page | Documentation | Examples | Forum |

C#/VB.NET: Convert Excel XLS or XLSX to PDF

PDF files are ideal for document distribution. When sharing an Excel report, it would be better to convert it to PDF so recipients can open it on any device that does not have MS Excel installed. To automate this feature, this article will demonstrate how to convert Excel to PDF in C# and VB.NET.

The following topics will be covered:

  • Convert an Excel file to PDF
  • Convert a Worksheet to PDF
  • Convert a Cell Range to PDF
  • Convert Excel to PDF/A
  • Convert Excel to Password Protected PDF

Installation

In order to achieve the conversion, I will be using Spire.XLS for .NET, which is a feature-rich and easy-to-use API for creating, editing, converting and printing Excel files.

The DLL files of Spire.XLS for .NET can be either downloaded from the official website or installed via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then add the following code:

PM> Install-Package Spire.XLS

Convert an Excel file to PDF in C# and VB.NET

The following are the steps to convert an Excel file to PDF:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Access the convert settings using Workbook.ConvertSetting property.
  • Set ConvertSetting.SheetFitToPage property as true to fit all worksheets on 1 page.
  • Save the Excel file to PDF using Workbook.SaveToFile(string, FileFormat.PDF) method.

C#

using Spire.Xls;

namespace ConvertExcelToPDF
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

            //Fit all worksheets on one page
            ConverterSetting settings = workbook.ConverterSetting;
            settings.SheetFitToPage = true;

            //Save to PDF
            workbook.SaveToFile("ExcelToPdf.pdf", FileFormat.PDF);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace ConvertExcelToPDF
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Load a sample Excel file
            workbook.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")

            'Fit all worksheets on one page
            Dim settings As ConverterSetting = workbook.ConverterSetting
            settings.SheetFitToPage = True

            'Save to PDF
            workbook.SaveToFile("ExcelToPdf.pdf", FileFormat.PDF)
        End Sub
    End Class
End Namespace
Convert Excel to PDF in C# and VB.NET

Convert a Worksheet to PDF in C# and VB.NET

The following are the steps to convert a specific worksheet to PDF:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
  • Fit the worksheet on 1 page using Worksheet.PageSetup.IsFitToPage property.
  • Save the worksheet to PDF using XlsWorksheet.SaveToPdf() method.

C#

using Spire.Xls;

namespace ConvertWorksheetToPDF
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

            //Get the second worksheet
            Worksheet worksheet = workbook.Worksheets[1];
            //Fit the worksheet to one page
            worksheet.PageSetup.IsFitToPage = true;

            //Save to PDF
            worksheet.SaveToPdf("WorksheetToPdf.pdf");
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace ConvertWorksheetToPDF
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Load a sample Excel file
            workbook.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")

            'Get the second worksheet
            Dim worksheet As Worksheet = workbook.Worksheets(1)
            'Fit the worksheet to one page
            worksheet.PageSetup.IsFitToPage = True

            'Save to PDF
            worksheet.SaveToPdf("WorksheetToPdf.pdf")
        End Sub
    End Class
End Namespace
Convert an Excel Worksheet to PDF in C# and VB.NET

Convert a Cell Range to PDF in C# and VB.NET

The following are the steps to convert a particular cell range to PDF:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
  • Set the desired cell range as print area using Worksheet.PageSetup.PrintArea property.
  • Save the worksheet to PDF using XlsWorksheet.SaveToPdf() method.

C#

using Spire.Xls;

namespace ConvertCellRangeToPdf
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
            //Set the desired cell range as print area
            worksheet.PageSetup.PrintArea = "A1:B24";

            //Save to PDF
            worksheet.SaveToPdf("CellRangeToPdf.pdf");
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace ConvertCellRangeToPdf
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Load a sample Excel file
            workbook.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")

            'Get the first worksheet
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            'Set the desired cell range as print area
            worksheet.PageSetup.PrintArea = "A1:B24"

            'Save to PDF
            worksheet.SaveToPdf("CellRangeToPdf.pdf")
        End Sub
    End Class
End Namespace
Convert a Worksheet Range to PDF in C# and VB.NET

Convert Excel to PDF/A in C# and VB.NET

The following are the steps to convert Excel to PDF/A:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Access the convert settings using Workbook.ConvertSetting property.
  • Set ConvertSetting.SheetFitToPage property as true to fit all worksheets on 1 page.
  • Specify the conformance level of the PDF using ConvertSetting.PdfConformanceLevel property.
  • Save the Excel file to PDF/A using Workbook.SaveToFile(string, FileFormat.PDF) method.

C#

using Spire.Xls;

namespace ConvertExcelToPDFa
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

            //Set the conformance level of the PDF
            ConverterSetting settings = workbook.ConverterSetting;
            settings.SheetFitToPage = true;
            settings.PdfConformanceLevel = Spire.Pdf.PdfConformanceLevel.Pdf_A1A;

            //Save to PDF/A
            workbook.SaveToFile("ExcelToPdfA.pdf", FileFormat.PDF);
        }
    }
}

VB.NET:

Imports Spire.Xls

Namespace ConvertExcelToPDFa
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Load a sample Excel file
            workbook.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")

            'Set the conformance level of the PDF
            Dim settings As ConverterSetting = workbook.ConverterSetting
            settings.SheetFitToPage = True
            settings.PdfConformanceLevel = Spire.Pdf.PdfConformanceLevel.Pdf_A1A

            'Save to PDF/A
            workbook.SaveToFile("ExcelToPdfA.pdf", FileFormat.PDF)
        End Sub
    End Class
End Namespace
Convert Excel to PDF/A in C# and VB.NET

Convert Excel to Password Protected PDF in C# and VB.NET

The following are the steps to convert Excel to Password Protected PDF:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Access the convert settings using Workbook.ConvertSetting property.
  • Set ConvertSetting.SheetFitToPage property as true to fit all worksheets on 1 page.
  • Set the password to protect the PDF using ConvertSetting.PdfSecurity.Encrypt() method.
  • Save the Excel file to password protected PDF using Workbook.SaveToFile(string, FileFormat.PDF) method.

C#

using Spire.Xls;

namespace ConvertExcelToEncryptedPDF
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Load a sample Excel file
            workbook.LoadFromFile("C:\\Users\\Administrator\\Desktop\\Sample.xlsx");

            //Set password for PDF
            ConverterSetting settings = workbook.ConverterSetting;
            settings.SheetFitToPage = true;
            settings.PdfSecurity.Encrypt("123456");

            //Save to PDF
            workbook.SaveToFile("ExcelToEncryptedPdf.pdf", FileFormat.PDF);
        }
    }
}

VB.NET:

Imports Spire.Xls

Namespace ConvertExcelToEncryptedPDF
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Load a sample Excel file
            workbook.LoadFromFile("C:\Users\Administrator\Desktop\Sample.xlsx")

            'Set password for PDF
            Dim settings As ConverterSetting = workbook.ConverterSetting
            settings.SheetFitToPage = True
            settings.PdfSecurity.Encrypt("123456")

            'Save to PDF
            workbook.SaveToFile("ExcelToEncryptedPdf.pdf", FileFormat.PDF)
        End Sub
    End Class
End Namespace
Convert Excel to Encrypted PDF in C# and VB.NET

See More

Product Page | Documentation | Examples | Temporary License | Forum |

C#/VB.NET: Add Hyperlinks to Excel

An Excel hyperlink is a reference to a specific location, for instance, a webpage, an existing file, an e-mail address or another cell in the current workbook. By clicking on the hyperlink, users can quickly jump to the destination instead of spending time searching for it. A hyperlink can be added to text or an image. In this article, I will demonstrate how to add text hyperlinks and image hyperlinks to Excel in C# and VB.NET using Free Spire.XLS for .NET library.

Add References

At first, you need to add the DLL files of Free Spire.XLS for .NET to your project as a reference. The DLL files of Free Spire.XLS for .NET can be either downloaded from the official website or installed via NuGet by executing the following command in Package Manager Console:

PM> Install-Package FreeSpire.XLS

Add Text Hyperlinks to Excel in C# and VB.NET

The following are the steps to add a text hyperlink to Excel:

  • Create an instance of Workbook class.
  • Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
  • Access the specific cell that you want to add hyperlink to using Worksheet.Range[cellName] property.
  • Add a hyperlink to the cell using Worksheet.HyperLinks.Add() method.
  • Set the type, display text and address for the hyperlink using XlsHyperLink.Type, XlsHyperLink.TextToDisplay and XlsHyperLink.Address properties.
  • Autofit column width using XlsWorksheet.AutoFitColumn() method.
  • Save the result file using Workbook.SaveToFile() method.

C#

using Spire.Xls;

namespace AddTextHyperlinks
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();

            //Get the first worksheet
            Worksheet sheet = workbook.Worksheets[0];

            //Add a text hyperlink that leads to a webpage
            CellRange cell1 = sheet.Range["B3"];
            HyperLink urlLink = sheet.HyperLinks.Add(cell1);
            urlLink.Type = HyperLinkType.Url;
            urlLink.TextToDisplay = "Link to a website";
            urlLink.Address = "https://www.google.com/";

            //Add a text hyperlink that leads to an email address
            CellRange cell2 = sheet.Range["E3"];
            HyperLink mailLink = sheet.HyperLinks.Add(cell2);
            mailLink.Type = HyperLinkType.Url;
            mailLink.TextToDisplay = "Link to an email address";
            mailLink.Address = "mailto:abc@outlook.com";

            //Add a text hyperlink that leads to an external file
            CellRange cell3 = sheet.Range["B7"];
            HyperLink fileLink = sheet.HyperLinks.Add(cell3);
            fileLink.Type = HyperLinkType.File;
            fileLink.TextToDisplay = "Link to an external file";
            fileLink.Address = "C:\\Users\\Administrator\\Desktop\\Report.xlsx";

            //Add a text hyperlink that leads to a cell in another sheet
            CellRange cell4 = sheet.Range["E7"];
            HyperLink linkToSheet = sheet.HyperLinks.Add(cell4);
            linkToSheet.Type = HyperLinkType.Workbook;
            linkToSheet.TextToDisplay = "Link to a cell in sheet2";
            linkToSheet.Address = "Sheet2!B5";

            //Add a text hyperlink that leads to a UNC address
            CellRange cell5 = sheet.Range["B11"];
            HyperLink uncLink = sheet.HyperLinks.Add(cell5);
            uncLink.Type = HyperLinkType.Unc;
            uncLink.TextToDisplay = "Link to a UNC address";
            uncLink.Address = "\\\\192.168.0.121";

            //Autofit column width
            sheet.AutoFitColumn(2);
            sheet.AutoFitColumn(5);

            //Save the result file
            workbook.SaveToFile("AddTextHyperlinks.xlsx", ExcelVersion.Version2013);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace AddTextHyperlinks
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()

            'Get the first worksheet
            Dim sheet As Worksheet = workbook.Worksheets(0)

            'Add a text hyperlink that leads to a webpage
            Dim cell1 As CellRange = sheet.Range("B3")
            Dim urlLink As HyperLink = sheet.HyperLinks.Add(cell1)
            urlLink.Type = HyperLinkType.Url
            urlLink.TextToDisplay = "Link to a website"
            urlLink.Address = "https://www.google.com/"

            'Add a text hyperlink that leads to an email address
            Dim cell2 As CellRange = sheet.Range("E3")
            Dim mailLink As HyperLink = sheet.HyperLinks.Add(cell2)
            mailLink.Type = HyperLinkType.Url
            mailLink.TextToDisplay = "Link to an email address"
            mailLink.Address = "mailto:abc@outlook.com"

            'Add a text hyperlink that leads to an external file
            Dim cell3 As CellRange = sheet.Range("B7")
            Dim fileLink As HyperLink = sheet.HyperLinks.Add(cell3)
            fileLink.Type = HyperLinkType.File
            fileLink.TextToDisplay = "Link to an external file"
            fileLink.Address = "C:\\Users\\Administrator\\Desktop\\Report.xlsx"

            'Add a text hyperlink that leads to a cell in another sheet
            Dim cell4 As CellRange = sheet.Range("E7")
            Dim linkToSheet As HyperLink = sheet.HyperLinks.Add(cell4)
            linkToSheet.Type = HyperLinkType.Workbook
            linkToSheet.TextToDisplay = "Link to a cell in sheet2"
            linkToSheet.Address = "Sheet2!B5"

            'Add a text hyperlink that leads to a UNC address
            Dim cell5 As CellRange = sheet.Range("B11")
            Dim uncLink As HyperLink = sheet.HyperLinks.Add(cell5)
            uncLink.Type = HyperLinkType.Unc
            uncLink.TextToDisplay = "Link to a UNC address"
            uncLink.Address = "\\\\192.168.0.121"

            'Autofit column width
            sheet.AutoFitColumn(2)
            sheet.AutoFitColumn(5)

            'Save the result file
            workbook.SaveToFile("AddTextHyperlinks.xlsx", ExcelVersion.Version2013)
        End Sub
    End Class
End Namespace
Add text hyperlinks to Excel in C# and VB.NET

Add Image Hyperlinks to Excel in C# and VB.NET

The following are the steps to add an image hyperlink to Excel:

  • Create an instance of Workbook class.
  • Get the desired worksheet using Workbook.Worksheets[sheetIndex] property.
  • Insert an image into the worksheet using Worksheet.Pictures.Add() method and set column width and row height.
  • Add a hyperlink to the image using XlsBitmapShape.SetHyperLink() method.
  • Save the result file using Workbook.SaveToFile() method.

C#

using Spire.Xls;

namespace AddImageHyperlinks
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Get the first worksheet 
            Worksheet sheet = workbook.Worksheets[0];

            //Insert an image into the worksheet
            ExcelPicture picture = sheet.Pictures.Add(5, 3, "Logo.png");
            picture.LeftColumnOffset = 100;
            picture.TopRowOffset = 25;
            sheet.Columns[2].ColumnWidth = 13;
            sheet.Rows[4].RowHeight = 70;

            //Add a hyperlink to the image 
            picture.SetHyperLink("https://www.google.com/", true);

            //Save the document 
            workbook.SaveToFile("AddImageHyperlink.xlsx", ExcelVersion.Version2013);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace AddImageHyperlinks
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Get the first worksheet 
            Dim sheet As Worksheet = workbook.Worksheets(0)

            'Insert an image into the worksheet
            Dim picture As ExcelPicture = sheet.Pictures.Add(5, 3, "Logo.png")
            picture.LeftColumnOffset = 100
            picture.TopRowOffset = 25
            sheet.Columns(2).ColumnWidth = 13
            sheet.Rows(4).RowHeight = 70

            'Add a hyperlink to the image 
            picture.SetHyperLink("https://www.google.com/", True)

            'Save the document 
            workbook.SaveToFile("AddImageHyperlink.xlsx", ExcelVersion.Version2013)
        End Sub
    End Class
End Namespace

Add image hyperlink to Excel in C# and VB.NET

See More

Product Page | Documentation | Examples | Forum |

C#/VB.NET: Search and Highlight Text in Word Documents

Looking for a certain text in a lengthy Word document is a time-consuming task. To simplify this process, Microsoft Word provides a Find feature that lets users find and highlight all occurrences of a specific text in a Word document quickly and efficiently. In this article, I will demonstrate how to search and highlight text in a Word document programmatically in C# and VB.NET using Free Spire.Doc for .NET.

The following topics will be covered:

  • Search and Highlight the First Occurrences of a Text in Word
  • Search and Highlight All occurrences of a Text in Word

Installation

You can download the DLL file of Free Spire.Doc for .NET from this website or install it via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then execute the following command:

Install-Package FreeSpire.Doc

Search and Highlight the First Occurrences of a Text in Word using C# and VB.NET

The following are the steps to search and highlight the first occurrences of a specific text:

  • Create an instance of Document class.
  • Load a Word document using Document.LoadFromFile() method.
  • Find the first occurrence of a specific text using Document.FindString() method.
  • Highlight the found text with background color through TextSelection.GetAsOneRange().CharacterFormat.HighlightColor property.
  • Save the result document using Document.SaveToFile() method.

C#

using Spire.Doc;
using Spire.Doc.Documents;
using System.Drawing;

namespace SeachText
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load a Word document
            Document document = new Document();
            document.LoadFromFile("Input.docx");

            //Find and highlight the first occurrences of a specific text
            TextSelection text = document.FindString("Java", false, true);
            text.GetAsOneRange().CharacterFormat.HighlightColor = Color.Yellow;
            
            //Save the result document
            document.SaveToFile("HighlightFirstOccurrence.docx", FileFormat.Docx2013);
        }
    }
}

VB.NET

Imports Spire.Doc
Imports Spire.Doc.Documents
Imports System.Drawing

Namespace SeachText
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Load a Word document
            Dim document As Document = New Document()
            document.LoadFromFile("Input.docx")

            'Find and highlight the first occurrences of a specific text
            Dim text As TextSelection = document.FindString("Java", False, True)
            text.GetAsOneRange().CharacterFormat.HighlightColor = Color.Yellow

            'Save the result document
            document.SaveToFile("HighlightFirstOccurrence.docx", FileFormat.Docx2013)
        End Sub
    End Class
End Namespace

Output:

Search and highlight the first occurrence of a text in Word using C# and VB.NET

Search and Highlight All occurrences of a Text in Word

The following are the steps to search and highlight all the occurrences of a specific text:

  • Create an instance of Document class.
  • Load a Word document using Document.LoadFromFile() method.
  • Find all occurrences of a specific text using Document.FindAllString() method.
  • Loop through the occurrences, then highlight them with background color through TextSelection.GetAsOneRange().CharacterFormat.HighlightColor property.
  • Save the result document using Document.SaveToFile() method.

C#

using Spire.Doc;
using Spire.Doc.Documents;
using System.Drawing;

namespace SeachText
{
    class Program
    {
        static void Main(string[] args)
        {
            //Load a Word document
            Document document = new Document();
            document.LoadFromFile("Input.docx");

            //Find and highlight all occurrences of a specific text
            TextSelection[] text = document.FindAllString("Java", false, true);
            foreach (TextSelection seletion in text)
            {
                seletion.GetAsOneRange().CharacterFormat.HighlightColor = Color.Yellow;
            }

            //Save the result document
            document.SaveToFile("HighlightAllOccurrences.docx", FileFormat.Docx2013);
        }
    }
}

VB.NET

Imports Spire.Doc
Imports Spire.Doc.Documents
Imports System.Drawing

Namespace SeachText
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Load a Word document
            Dim document As Document = New Document()
            document.LoadFromFile("Input.docx")

            'Find and highlight all occurrences of a specific text
            Dim text As TextSelection() = document.FindAllString("Java", False, True)
            For Each seletion As TextSelection In text
                seletion.GetAsOneRange().CharacterFormat.HighlightColor = Color.Yellow
            Next

            'Save the result document
            document.SaveToFile("HighlightAllOccurrences.docx", FileFormat.Docx2013)
        End Sub
    End Class
End Namespace

Output:

Search and highlight all occurrences of a text in Word using C# and VB.NET

C#/VB.NET: Delete Hidden Rows and Columns in Excel

There may be times when we have Excel files that contain hidden rows or columns, but we just need to copy data from the visible rows or columns. In this article, I am going to demonstrate how to determine and delete hidden rows and columns in Excel using C# and VB.NET.

Installation

This article uses Free Spire.XLS for .NET library. The DLL files of Free Spire.XLS for .NET can be either downloaded from the official website or installed via NuGet by selecting Tools > NuGet Package Manager > Package Manager Console and then add the following code:

PM> Install-Package FreeSpire.XLS

Delete Hidden Rows from Excel in C# and VB.NET

The following are the steps to determine and delete the hidden rows in Excel:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Get the worksheet that you want to remove hidden rows from using Workbook.Worksheets[sheetIndex] property.
  • Get the row count using Worksheet.Rows.Count() method.
  • Loop through the rows, determine if the current row is hidden or not using Worksheet.GetRowIsHide(rowIndex) method, if it’s hidden, delete it using Worksheet.DeleteRow(rowIndex) method. Note the row index here starts from 1.
  • Save the result file using Workbook.SaveToFile() method.

C#

using Spire.Xls;
using System.Linq;

namespace DeleteHiddenRows
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile("Sample1.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];
            //Get row count
            int rowCount = worksheet.Rows.Count();

            //Loop through the rows in the worksheet
            for (int i = 1; i <= rowCount; i++)
            {
                //Determine if the current row is hidden
                if (worksheet.GetRowIsHide(i))
                {
                    //If yes, delete it
                    worksheet.DeleteRow(i);
                    i--;
                }
            }

            //Save the result file
            workbook.SaveToFile("DeleteHiddenRows.xlsx", ExcelVersion.Version2013);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace DeleteHiddenRows
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Load the Excel file
            workbook.LoadFromFile("Sample1.xlsx")

            'Get the first worksheet
            Dim worksheet As Worksheet = workbook.Worksheets(0)
            'Get row count
            Dim rowCount As Integer = worksheet.Rows.Count()

            'Loop through the rows in the worksheet
            For i = 1 To rowCount
                'Determine if the current row is hidden
                If worksheet.GetRowIsHide(i) Then
                    'If yes, delete it
                    worksheet.DeleteRow(i)
                    i -= 1
                End If
            Next

            'Save the result file
            workbook.SaveToFile("DeleteHiddenRows.xlsx", ExcelVersion.Version2013)
        End Sub
    End Class
End Namespace
Delete hidden rows from Excel in C# and VB.NET

Delete Hidden Columns from Excel in C# and VB.NET

The following are the steps to determine and delete the hidden columns in Excel:

  • Create an instance of Workbook class.
  • Load the Excel file using Workbook.LoadFromFile() method.
  • Get the worksheet that you want to remove hidden rows from using Workbook.Worksheets[sheetIndex] property.
  • Get the row count using Worksheet.Columns.Count() method.
  • Loop through the rows, determine if the current row is hidden or not using Worksheet.GetColumnIsHide(columnIndex) method, if it’s hidden, delete it using Worksheet.DeleteColumn(columnIndex) method. Note the column index here starts from 1.
  • Save the result file using Workbook.SaveToFile() method.

C#

using Spire.Xls;
using System.Linq;

namespace DeleteHiddenColumns
{
    class Program
    {
        static void Main(string[] args)
        {
            //Create a Workbook instance
            Workbook workbook = new Workbook();
            //Load the Excel file
            workbook.LoadFromFile("Sample2.xlsx");

            //Get the first worksheet
            Worksheet worksheet = workbook.Worksheets[0];

            //Get column count
            int colCount = worksheet.Columns.Count();

            //Loop through the columns in the worksheet
            for (int j = 1; j <= colCount; j++)
            {
                //Determine if the current column is hidden
                if (worksheet.GetColumnIsHide(j))
                {
                    //If yes, delete it
                    worksheet.DeleteColumn(j);
                    j--;
                }
            }

            //Save the result file
            workbook.SaveToFile("DeleteHiddenColumns.xlsx", ExcelVersion.Version2013);
        }
    }
}

VB.NET

Imports Spire.Xls

Namespace DeleteHiddenColumns
    Friend Class Program
        Private Shared Sub Main(ByVal args As String())
            'Create a Workbook instance
            Dim workbook As Workbook = New Workbook()
            'Load the Excel file
            workbook.LoadFromFile("Sample2.xlsx")

            'Get the first worksheet
            Dim worksheet As Worksheet = workbook.Worksheets(0)

            'Get column count
            Dim colCount As Integer = worksheet.Columns.Count()

            'Loop through the columns in the worksheet
            For j = 1 To colCount
                'Determine if the current column is hidden
                If worksheet.GetColumnIsHide(j) Then
                    'If yes, delete it
                    worksheet.DeleteColumn(j)
                    j -= 1
                End If
            Next

            'Save the result file
            workbook.SaveToFile("DeleteHiddenColumns.xlsx", ExcelVersion.Version2013)
        End Sub
    End Class
End Namespace
Delete hidden columns from Excel in C# and VB.NET

See More

Product Page | Documentation | Examples | Forum |

Design a site like this with WordPress.com
Get started