Searching data is a powerful data processing function of Microsoft excel, but it doesn't allow users to extract the selected data to a new excel file directly. It's almost impossible for us to copy data row by row manually from one excel file to another, so it cannot entirely meet our requirements especially when we want to retrieve and extract the interesting data from a large excel file.

This article will demonstrate how to retrieve data from one excel worksheet and extract to a new excel file with Spire.XLS in C#.

Note: Before start, please download and install Spire.XLS correctly. Then add Spire.XLS.dll file as reference of your project.

Below is the screenshot of the original excel worksheet:

In this sample, all of the data related to teacher were extracted to a new excel file.

Detail steps overview:

Step 1: Create a new workbook instance and get the first worksheet.

Workbook newBook = new Workbook();
Worksheet newSheet = newBook.Worksheets[0];

Step 2: Create a new workbook instance and load the sample excel file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Information.xlsx");

Step 3: Get the worksheet where you want to retrieve and extract data from. In this sample, it's the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 4: Retrieve data and extract to the first worksheet of the new excel workbook.

int i = 1;
int columnCount = sheet.Columns.Count();
foreach (CellRange range in sheet.Columns[0])
{
    if (range.Text == "teacher")
        {
            CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount];
            CellRange destRange = newSheet.Range[i, 1, i, columnCount];
            sheet.Copy(sourceRange, destRange, true);
            i++;
        }
}

Step 5: Save the target file as NewForm.xlsx.

newBook.SaveToFile("NewForm.xlsx", ExcelVersion.Version2010);

Effective screenshot:

Full codes:

using System.Linq;
using Spire.Xls;

namespace Retrieve_and_extract_data
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook newBook = new Workbook();
            Worksheet newSheet = newBook.Worksheets[0];
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Information.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            int i = 1;
            int columnCount = sheet.Columns.Count();
            foreach (CellRange range in sheet.Columns[0])
            {
                if (range.Text == "teacher")
                {
                    CellRange sourceRange = sheet.Range[range.Row, 1, range.Row, columnCount];
                    CellRange destRange = newSheet.Range[i, 1, i, columnCount];
                    sheet.Copy(sourceRange, destRange, true);
                    i++;
                }
            }
            newBook.SaveToFile("NewForm.xlsx", ExcelVersion.Version2010);

        }
    }
}

Spire.XLS can help developers to write rich text into the spreadsheet easily, such as set the text in bold, italic, and set the colour and font for them. We have already shown you how to set Subscript and Superscript in Excel files by using Spire.XLS. This article will focus on demonstrate how to formatting text in Excel sheet in C#.

Spire.Xls offers ExcelFont class and developers can format the text of cells easily. By using RichText, we can add the text into cells and set font for it. Here comes to the steps of how to write rich text into excel cells.

Step 1: Create an excel document and get its first worksheet.

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

Step 2: Create the fonts and sent the format for each font.

//create a font and set the format to bold.
ExcelFont fontBold = workbook.CreateFont();
fontBold.IsBold = true;

//create a font and set the format to underline.              
ExcelFont fontUnderline = workbook.CreateFont();
fontUnderline.Underline = FontUnderlineType.Single;

//create a font and set the format to italic.            
ExcelFont fontItalic = workbook.CreateFont();
fontItalic.IsItalic = true;

//create a font and set the color to green.            
ExcelFont fontColor = workbook.CreateFont();
fontColor.KnownColor = ExcelColors.Green;

Step 3: Set the font for specified cell range.

RichText richText = sheet.Range["A1"].RichText;
richText.Text="It is in Bold";
richText.SetFont(0, richText.Text.Length-1, fontBold);
  
richText = sheet.Range["A2"].RichText;
richText.Text = "It is underlined";
richText.SetFont(0, richText.Text.Length-1, fontUnderline);

richText = sheet.Range["A3"].RichText;
richText.Text = "It is Italic";
richText.SetFont(0, richText.Text.Length - 1, fontItalic);

richText = sheet.Range["A4"].RichText;
richText.Text = "It is Green";
richText.SetFont(0, richText.Text.Length - 1, fontColor);

Step 4: Save the document to file.

workbook.SaveToFile("Sample.xls",ExcelVersion.Version97to2003);

Effective screenshot of writing rich text into excel worksheet:

Full codes:

using Spire.Xls;
namespace WriteRichtextinExcel
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            Worksheet sheet = workbook.Worksheets[0];

            ExcelFont fontBold = workbook.CreateFont();
            fontBold.IsBold = true;

            ExcelFont fontUnderline = workbook.CreateFont();
            fontUnderline.Underline = FontUnderlineType.Single;

            ExcelFont fontItalic = workbook.CreateFont();
            fontItalic.IsItalic = true;

            ExcelFont fontColor = workbook.CreateFont();
            fontColor.KnownColor = ExcelColors.Green;

            RichText richText = sheet.Range["A1"].RichText;
            richText.Text = "It is in Bold";
            richText.SetFont(0, richText.Text.Length - 1, fontBold);

            richText = sheet.Range["A2"].RichText;
            richText.Text = "It is underlined";
            richText.SetFont(0, richText.Text.Length - 1, fontUnderline);

            richText = sheet.Range["A3"].RichText;
            richText.Text = "It is Italic";
            richText.SetFont(0, richText.Text.Length - 1, fontItalic);

            richText = sheet.Range["A4"].RichText;
            richText.Text = "It is Green";
            richText.SetFont(0, richText.Text.Length - 1, fontColor);

            workbook.SaveToFile("Sample.xls", ExcelVersion.Version97to2003);

        }
    }
}

We have already shown you how to use the method ListObjects to filter the data and format it as a table. This article will focus on demonstrate how to remove the auto filters with the method AutoFilters offered by Spire.XLS. Developer can also use the method sheet.AutoFilters.Range to create the auto filters.

Step 1: Create an excel document and load the Excel with auto filers from file.

Workbook workbook = new Workbook();
workbook.LoadFromFile("Filter.xlsx ");

Step 2: Gets the first worksheet in the Excel file.

Worksheet sheet = workbook.Worksheets[0];

Step 3: Remove the auto filters.

sheet.AutoFilters.Clear();

Step 4: Save the document to file.

workbook.SaveToFile("Result.xlsx", ExcelVersion.Version2010);

Screenshot for the Excel files with auto filters:

The effective screenshot of removing the auto filters:

Full codes:

using Spire.Xls;
namespace RemoveAutoFilter
{
    class Program
    {

      static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("Filter.xlsx");

            Worksheet sheet = workbook.Worksheets[0];
            sheet.AutoFilters.Clear();

            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
        }


        }
    }

Auto Filter is the most convenient way to select the data we want from a large amount of the data in an excel data table. With the help of Spire.XLS for .NET, developers can use the method ListObjects to filter the data and format it as a table. This article will focus on show you how to add table with filter in Excel.

Step 1: Create an excel document and load the Excel from file:

Workbook workbook = new Workbook();
workbook.LoadFromFile("DataTable.xlsx");

Step 2: Gets the first worksheet in the Excel file

Worksheet sheet = workbook.Worksheets[0];

Step 3: Create a List Object named in Table

sheet.ListObjects.Create("Table", sheet.Range[1, 1, sheet.LastRow, sheet.LastColumn]);

Step 4: Set the BuiltInTableStyle for List object.

sheet.ListObjects[0].BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;

Step 5: Save the document to file.

workbook.SaveToFile("Filter.xlsx", ExcelVersion.Version2010);

Effective screenshot after the date has been auto filtered:

Full codes:

namespace Excelfilter
{
    class Program
    {
        static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("DataTable.xlsx");

            Worksheet sheet = workbook.Worksheets[0];

            sheet.ListObjects.Create("Table", sheet.Range[1, 1, sheet.LastRow, sheet.LastColumn]);

            sheet.ListObjects[0].BuiltInTableStyle = TableBuiltInStyles.TableStyleLight9;

            workbook.SaveToFile("Filter.xlsx", ExcelVersion.Version2010);
        }
    }
}

Spire.XLS for .NET is a professional Excel component which enables developers/programmers to fast generate, read, write and modify Excel document for .NET. Spire.XLS for .NET doesn't need Microsoft Office Excel Automation. It allows user to operate Excel document directly such as save to stream, save as web response, copy, lock/unlock worksheet, set up workbook properties, etc. As a professional .NET Excel component, it also includes many useful features, for example, functionalities of importing data from Excel to dataTable and exporting dataTable to Excel from Database.

In this article introduces a method of updating excel data by dataTable via using sheet.ExportDataTable() method and sheet.InsertDataTable() method to import data from excel to dataTable and export dataTable to excel from Database.

The main steps of method are:

Step 1: Load the excel document and use sheet.ExportDataTable() method extract data to dataTable and show by dataGridView control.

private void Form1_Load(object sender, EventArgs e)
        {
            //load excel document to workbook
            workbook.LoadFromFile(@"DatatableSample.xls");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Original table";
            //extract data to dataTable from sheet 
            DataTable dataTable = sheet.ExportDataTable();
            //show the data to dataGridView
            this.dataGridView.DataSource = dataTable;
        }

The effect screenshot:

Step 2: Create a new sheet to save the updata data and use sheet.InsertDataTable() method to insert dataTable to the sheet.

            //create a new sheet to save Updata data.
            Worksheet sheet = workbook.CreateEmptySheet("Updata Table");
            //extract data from dataGridView
            DataTable dataTable = this.dataGridView.DataSource as DataTable;
            // insert dataTable to sheet
            sheet.InsertDataTable(dataTable, true, 1, 1);

Step 3: Save the result excel document.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);

The effect screenshot:

Download and install Spire.XLS for .NET and use below code to experience this method to update excel data by dataTable.

The full code:

using System;
using System.Data;
using System.Windows.Forms;
using Spire.Xls;
namespace UpdataExcelDataByDataTable
{

    public partial class UpdataExcelData : Form
    {
        private Workbook workbook = new Workbook();
        private void Form1_Load(object sender, EventArgs e)
        {
            workbook.LoadFromFile(@"DatatableSample.xls");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.Name = "Original table";
            DataTable dataTable = sheet.ExportDataTable();
            this.dataGridView.DataSource = dataTable;
        }
        private void Updata_Click(object sender, EventArgs e)
        {
            Worksheet sheet = workbook.CreateEmptySheet("Updata Table");
            DataTable dataTable = this.dataGridView.DataSource as DataTable;
            sheet.InsertDataTable(dataTable, true, 1, 1);
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2007);
            System.Diagnostics.Process.Start("result.xlsx");
        }
    }
}

Imports System.Data
Imports System.Windows.Forms
Imports Spire.Xls
Public Class Form1
    Private workbook As New Workbook()
    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        'load excel document to workbook
        workbook.LoadFromFile("DatatableSample.xls")
        Dim sheet As Worksheet = workbook.Worksheets(0)
        sheet.Name = "Original table"
        'extract data to dataTable from sheet 
        Dim dataTable As DataTable = sheet.ExportDataTable()
        'show the data to dataGridView
        Me.DataGridView.DataSource = dataTable
    End Sub

If you couldn't successfully use the Spire.Xls, please refer Spire.XLS Quick Start which can guide you quickly use the Spire.Xls.

Superscript and subscript are formatting styles that allow you to display characters or numerals above or below the regular text baseline respectively. By utilizing these formatting styles, you can emphasize certain elements, denote exponents, powers, chemical formulas, or mathematical equations, and present data in a more visually appealing and informative manner. In this article, we will demonstrate how to apply superscript and subscript styles in Excel in C# and VB.NET using 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

Apply Superscript and Subscript in Excel in C# and VB.NET

To apply the superscript or subscript style to specific characters in a cell, you need to create a custom font, enable the superscript or subscript property of the font, and then assign the custom font to the specific characters within the cell. The detailed steps are as follows:

• Create a Workbook object.
• Get a specific worksheet using Workbook.Worksheets[int index] property.
• Get a specific cell using Worksheet.Range[string name] property and add rich text to the cell using CellRange.RichText.Text property.
• Create a custom font using Workbook.CreateFont() method.
• Enable the subscript property of the font by setting ExcelFont.IsSubscript property to true.
• Assign the custom font to specific characters of the rich text in the cell using CellRange.RichText.SetFont() method.
• Get a specific cell using Worksheet.Range[string name] property and add rich text to the cell using CellRange.RichText.Text property.
• Create a custom font using Workbook.CreateFont() method.
• Enable the superscript property of the font by setting ExcelFont.IsSuperscript property to true.
• Assign the custom font to specific characters of the rich text in the cell using CellRange.RichText.SetFont() method.
• Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;
using System.Drawing;

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

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

            //Add text to specific cells
            sheet.Range["B2"].Text = "This is an example of subscript:";
            sheet.Range["D2"].Text = "This is an example of superscript:";

            //Add rich text to a specific cell
            CellRange range = sheet.Range["B3"];
            range.RichText.Text = "an = Sn - Sn-1";

            //Create a custom font
            ExcelFont font = workbook.CreateFont();
            //Enable the subscript property of the font
            font.IsSubscript = true;
            //Set font color
            font.Color = Color.Green;
            //Assign the font to specific characters of the rich text in the cell
            range.RichText.SetFont(6, 6, font);
            range.RichText.SetFont(11, 13, font);

            //Add rich text to a specific cell
            range = sheet.Range["D3"];
            range.RichText.Text = "a2 + b2 = c2";

            //Create a custom font
            font = workbook.CreateFont();
            //Enable the superscript property of the font
            font.IsSuperscript = true;
            //Assign the font to specific characters of the rich text in the cell
            range.RichText.SetFont(1, 1, font);
            range.RichText.SetFont(6, 6, font);
            range.RichText.SetFont(11, 11, font);

            //Auto-fit column widths
            sheet.AllocatedRange.AutoFitColumns();

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

Imports Spire.Xls
Imports System.Drawing

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

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

            'Add text to specific cells
            sheet.Range("B2").Text = "This is an example of subscript:"
            sheet.Range("D2").Text = "This is an example of superscript:"

            'Add rich text to a specific cell
            Dim range As CellRange = sheet.Range("B3")
            range.RichText.Text = "an = Sn - Sn-1"

            'Create a custom font
            Dim font As ExcelFont = workbook.CreateFont()
            'Enable the subscript property of the font
            font.IsSubscript = True
            'Set font color
            font.Color = Color.Green
            'Assign the font to specific characters of the rich text in the cell
            range.RichText.SetFont(6, 6, font)
            range.RichText.SetFont(11, 13, font)

            'Add rich text to a specific cell
            range = sheet.Range("D3")
            range.RichText.Text = "a2 + b2 = c2"

            'Create a custom font
            font = workbook.CreateFont()
            'Enable the superscript property of the font
            font.IsSuperscript = True
            'Assign the font to specific characters of the rich text in the cell
            range.RichText.SetFont(1, 1, font)
            range.RichText.SetFont(6, 6, font)
            range.RichText.SetFont(11, 11, font)

            'Auto-fit column widths
            sheet.AllocatedRange.AutoFitColumns()

            'Save the result file
            workbook.SaveToFile("ApplySubscriptAndSuperscript.xlsx", ExcelVersion.Version2013)
            workbook.Dispose()
        End Sub
    End Class
End Namespace

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.

You can hide the Excel row or column by using the c# code, but a row or column also becomes hidden when you want to show the full Excel worksheet. You can unhide the Excel row and column by using the c# code. This article aims at introducing the method sheet.ShowRow and sheet.ShowColumn in the Excel .NET component Spire.Xls to show the hidden row and column.

First, let’s preview the hidden row and column.

Here comes to the steps of the process.

Step 1: Create an instance of Spire.XLS.Workbook.

Workbook workbook = new Workbook();

Step 2: Load the existing Excel file that hidden the row and column in the specified path.

workbook.LoadFromFile("hide.xlsx");

Step 3: Get the first worksheet of the Excel file.

Worksheet sheet = workbook.Worksheets[0];

Step 4: Unhide the hidden row and column.

sheet.ShowRow(7);
sheet.ShowColumn(3);

Step 5: Generate the new Excel file.

workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);

Now let's preview the effect screenshot.

Here is the full code.

using Spire.Xls;
namespace UnhideExcelRow
{
    class Program
    {

      static void Main(string[] args)
        {
            Workbook workbook = new Workbook();
            workbook.LoadFromFile("hide.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            // unhide the hidden row and column of the worksheet.
            sheet.ShowRow(7);
            sheet.ShowColumn(3);
            workbook.SaveToFile("result.xlsx", ExcelVersion.Version2010);
        }


        }
    }

Text alignment and orientation are essential formatting features in Excel that allow you to position and orient text within cells according to your specific needs. By adjusting text alignment and orientation, you can enhance the readability and aesthetics of your spreadsheet. In this article, we will explain how to set text alignment and orientation in Excel in C# and VB.NET using 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

Set Text Alignment and Orientation in Excel in C# and VB.NET

You can set the horizontal or vertical alignment of text in individual cells or a range of cells using the CellRange.Style.HorizontalAlignment or CellRange.Style.VerticalAlignment properties. In addition, you are also able to change the orientation of text by assigning a rotation value to the corresponding cell or cells using the CellRange.Style.Rotation property. The detailed steps are as follows:

• Create a Workbook object.
• Load an Excel file using Workbook.LoadFromFile() method.
• Get a specific worksheet using Workbook.Worksheets[int index] property.
• Access specific cells in the worksheet using Worksheet.Range[string name] property and then set the horizontal alignment of text in them using CellRange.Style.HorizontalAlignment property.
• Access specific cells in the worksheet using Worksheet.Range[string name] property and then set the vertical alignment of text in them using CellRange.Style.VerticalAlignment property.
• Access specific cells in the worksheet using Worksheet.Range[string name] property and then change their text orientation using CellRange.Style.Rotation property.
• Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;

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

            //Load an Excel file
            workbook.LoadFromFile(@"Sample.xlsx");

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

            //Set the horizontal alignment for text in a specific cell to Left
            sheet.Range["B1"].Style.HorizontalAlignment = HorizontalAlignType.Left;

            //Set the horizontal alignment for text in a specific cell to Center
            sheet.Range["B2"].Style.HorizontalAlignment = HorizontalAlignType.Center;

            //Set the horizontal alignment for text in a specific cell to Right
            sheet.Range["B3"].Style.HorizontalAlignment = HorizontalAlignType.Right;

            //Set the horizontal alignment for text in a specific cell to General
            sheet.Range["B4"].Style.HorizontalAlignment = HorizontalAlignType.General;

            //Set the vertical alignment for text in a specific cell to Top
            sheet.Range["B5"].Style.VerticalAlignment = VerticalAlignType.Top;

            //Set the vertical alignment for text in a specific cell to Center
            sheet.Range["B6"].Style.VerticalAlignment = VerticalAlignType.Center;

            //Set the vertical alignment for text in a specific cell to Bottom
            sheet.Range["B7"].Style.VerticalAlignment = VerticalAlignType.Bottom;          

            //Change the text orientation in specific cells by assigning a rotation value
            sheet.Range["B8"].Style.Rotation = 45;
            sheet.Range["B9"].Style.Rotation = 90;

            //Set the row height for specific cells
            sheet.Range["B8:C9"].RowHeight = 70;

            //Save the result file
            workbook.SaveToFile("TextAlignmentAndOrientation.xlsx", ExcelVersion.Version2016);
            workbook.Dispose();
        }
    }
}

Imports Spire.Xls

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

            'Load an Excel file
            workbook.LoadFromFile("Sample.xlsx")

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

            'Set the horizontal alignment for text in a specific cell to Left
            sheet.Range("B1").Style.HorizontalAlignment = HorizontalAlignType.Left

            'Set the horizontal alignment for text in a specific cell to Center
            sheet.Range("B2").Style.HorizontalAlignment = HorizontalAlignType.Center

            'Set the horizontal alignment for text in a specific cell to Right
            sheet.Range("B3").Style.HorizontalAlignment = HorizontalAlignType.Right

            'Set the horizontal alignment for text in a specific cell to General
            sheet.Range("B4").Style.HorizontalAlignment = HorizontalAlignType.General

            'Set the vertical alignment for text in a specific cell to Top
            sheet.Range("B5").Style.VerticalAlignment = VerticalAlignType.Top

            'Set the vertical alignment for text in a specific cell to Center
            sheet.Range("B6").Style.VerticalAlignment = VerticalAlignType.Center

            'Set the vertical alignment for text in a specific cell to Bottom
            sheet.Range("B7").Style.VerticalAlignment = VerticalAlignType.Bottom

            'Change the text orientation in specific cells by assigning a rotation value
            sheet.Range("B8").Style.Rotation = 45
            sheet.Range("B9").Style.Rotation = 90

            'Set the row height for specific cells
            sheet.Range("B8:C9").RowHeight = 70

            'Save the result file
            workbook.SaveToFile("TextAlignmentAndOrientation.xlsx", ExcelVersion.Version2016)
            workbook.Dispose()
        End Sub
    End Class
End Namespace

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.

Group the Excel cells is to tie a range of cells together so that they can be collapsed or expanded. But usually, we also need to ungroup the Excel cells. Consequently, the articles aims at introducing how to ungroup Excel cells in C#, through a professional Excel .NET Component Spire.Xls.

Just as its name implies, ungroup Excel cells is to ungroup a range of cells that were previously grouped. Before ungroup Excel cells, we should complete the preparatory work:

• Download the Spire.XLS and install it on your machine.
• Add the Spire.XLS.dll files as reference.
• Open bin folder and select the three dll files under .NET 4.0.
• Right click property and select properties in its menu.
• Set the target framework as .NET 4.
• Add Spire.XLS as namespace.

Then here comes to the explanation of the code:

Step 1: Create an instance of Spire.XLS.Workbook.

Workbook workbook = new Workbook();

Step 2: Load the file base on a specified file path.

workbook.LoadFromFile(@"group.xlsx");

Step 3: Get the first worksheet.

Worksheet sheet = workbook.Worksheets[0];

Step 4: Ungroup the first 5 row cells.

sheet.UngroupByRows(1, 5);

Step 5: Save as the generated file.

workbook.SaveToFile(@"result.xlsx", ExcelVersion.Version2010);

Full code:

using Spire.Xls;
namespace UngroupCell
{
    class Program
    {

        static void Main(string[] args)
        {

            Workbook workbook = new Workbook();
            workbook.LoadFromFile(@"group.xlsx");
            Worksheet sheet = workbook.Worksheets[0];
            sheet.UngroupByRows(1, 5);
            workbook.SaveToFile(@"..\..\result.xlsx", ExcelVersion.Version2010);

        }
    }
}

Please preview the original group effect screenshot:

And the generated ungroup effect screenshot:

The Find function in Excel is one of the most commonly used functions for quickly locating specified data, and users can also highlight the data to make it more obvious. In this article, you will learn how to programmatically find and highlight cells with a specific value using 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

Find and Highlight Data in Excel

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.
• Find all cells with matching text using Worksheet.FindAllString(string stringValue, bool formula, bool formulaValue) method.
• Set color to highlight the cells using CellRange.Style.Color property.
• Save the result file using Workbook.SaveToFile() method.

using Spire.Xls;
using System.Drawing;

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

            //Load a sample Excel file
            workbook.LoadFromFile("Test.xlsx");

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

            //Find all cells with the text "Regulator System"
            foreach (CellRange range in sheet.FindAllString("Regulator System", true, true))
            {
                //Set color to highlight the cells
                range.Style.Color = Color.Yellow;
            }

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

Imports Spire.Xls

Namespace FindHighlight
    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("Test.xlsx")

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

            'Find all cells with the text "Regulator System"
            For Each range As CellRange In sheet.FindAllString("Regulator System", True, True)

                'Set color to highlight the cells
                range.Style.Color = Color.Yellow
            Next

            'Save the result file
            workbook.SaveToFile("FindHighlight.xlsx", ExcelVersion.Version2016)
        End Sub
    End Class
End Namespace

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.