Worksheet Cells Manipulation in File Formats XlsIO

1 Jun 202324 minutes to read

The IRange interface represents a single cell or a group of cells in a worksheet. XlsIO has several useful methods for accessing, manipulating and formatting the content in the cells.

Accessing a Cell or a Range

The following code shows the different ways of accessing a single cell or group of cells

NOTE

Here row and column indexes in the range are “one-based”.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access a range by specifying cell address
  sheet.Range["A7"].Text = "Accessing a Range by specify cell address ";

  //Access a range by specifying cell row and column index
  sheet.Range[9, 1].Text = "Accessing a Range by specify cell row and column index ";

  //Access a Range by specifying using defined name
  IName name = workbook.Names.Add("Name");
  name.RefersToRange = sheet.Range["A11"];
  sheet.Range["Name"].Text = "Accessing a Range by specifying using defined name";

  //Accessing a Range of cells by specifying cells address
  sheet.Range["A13:C13"].Text = "Accessing a Range of Cells (Method 1)";

  //Accessing a Range of cells specifying cell row and column index
  sheet.Range[15, 1, 15, 3].Text = "Accessing a Range of Cells (Method 2)";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Range.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Access a range by specifying cell address
  sheet.Range["A7"].Text = "Accessing a Range by specify cell address ";

  //Access a range by specifying cell row and column index
  sheet.Range[9, 1].Text = "Accessing a Range by specify cell row and column index ";

  //Access a Range by specifying using defined name
  IName name = workbook.Names.Add("Name");
  name.RefersToRange = sheet.Range["A11"];
  sheet.Range["Name"].Text = "Accessing a Range by specifying using defined name.";

  //Accessing a Range of cells by specifying cells address
  sheet.Range["A13:C13"].Text = "Accessing a Range of Cells (Method 1)";

  //Accessing a Range of cells specifying cell row and column index
  sheet.Range[15, 1, 15, 3].Text = "Accessing a Range of Cells (Method 2)";

  workbook.SaveAs("Range.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Access a range by specify cell address
  sheet.Range("A7").Text = "Accessing a Range by specify cell address "

  'Access a range by specify cell row and column index
  sheet.Range(9, 1).Text = "Accessing a Range by specify cell row and column index "

  'Access a Range by specifying using defined name
  Dim name As IName = workbook.Names.Add("Name")
  name.RefersToRange = sheet.Range("A11")
  sheet.Range("Name").Text = "Accessing a Range by specifying using defined name"

  'Accessing a Range of cells by specify cells address
  sheet.Range("A13:C13").Text = "Accessing a Range of Cells (Method 1)"

  'Accessing a Range of cells specify cell row and column index
  sheet.Range(15, 1, 15, 3).Text = "Accessing a Range of Cells (Method 2)"

  workbook.SaveAs("Range.xlsx")
End Using

A complete working example to access a cell or range in an Excel worksheet in C# is present on this GitHub page.

TIPS

You can make use of GetText, SetText, GetNumber and SetNumber methods from worksheet object that enable users to get/set values without range object.

Accessing Relative Range

By default, accessing a range by index will return the cell or range from worksheet level. To get a relative range for the indexes provided, it is recommended to set the ExcelRangeIndexerMode option. Here, the RowIndex and ColumnIndex arguments are relative offsets, where specifying a RowIndex of 1 returns cells in the first row of the range not the first row of the worksheet.

For example, if a range is mentioned as “B3:D5”, then accessing a range with the index [1,1] will return the cell “A1” from worksheet. If the ExcelRangeIndexerMode is set to Relative then it returns “B3”.

Following code example illustrates how to access the range relatively to the existing range object in XlsIO.

NOTE

Here row and column indexes in the range are “one-based”.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  //Setting range index mode to relative
  application.RangeIndexerMode = ExcelRangeIndexerMode.Relative;

  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating a range by specifying cells address
  IRange range1 = sheet.Range["B3:D5"];

  //Accessing a range relatively to the existing range by specifying cell row and column index
  range1[2, 2].Text = "Returns C4 cell";
  range1[0, 0].Text = "Returns A2 cell";

  //Creating a Range of cells specifying cell row and column index
  IRange range2 = sheet.Range[5, 1, 10, 3];

  //Accessing a range relatively to the existing range of cells by specifying cell row and column index
  range2[2, 2, 3, 3].Text = "Returns range of cells B6 to C7";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Range.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  //Setting range index mode to relative
  application.RangeIndexerMode = ExcelRangeIndexerMode.Relative;

  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating a range by specifying cells address
  IRange range1 = sheet.Range["B3:D5"];

  //Accessing a range relatively to the existing range by specifying cell row and column index
  range1[2, 2].Text = "Returns C4 cell";
  range1[0, 0].Text = "Returns A2 cell";

  //Creating a Range of cells specifying cell row and column index
  IRange range2 = sheet.Range[5, 1, 10, 3];

  //Accessing a range relatively to the existing range of cells by specifying cell row and column index
  range2[2, 2, 3, 3].Text = "Returns range of cells B6 to C7";

  workbook.SaveAs("Range.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx

  'Setting range index mode to relative
  application.RangeIndexerMode = ExcelRangeIndexerMode.Relative

  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating a range by specifying cells address
  Dim range1 As IRange = sheet.Range("B3:D5")

  'Accessing a range relatively to the existing range by specifying cell row and column index
  range1(2, 2).Text = "Returns B4 cell"
  range1(0, 0).Text = "Returns A2 cell"

  Dim range2 As IRange = sheet.Range(5, 1, 10, 3)
  'Accessing a range relatively to the existing range of cells by specifying cell row and column index
  range2(2, 2, 3, 3).Text = "Returns range of cells B6 to C7"

  workbook.SaveAs("Range.xlsx")
End Using

A complete working example to access relative range in an Excel worksheet in C# is present on this GitHub page.

Accessing Discontinuous Ranges

It is possible to modify the contents or apply formatting to discontinuous range by accessing and adding them to the RangesCollection.

Following code snippet illustrates how to access discontinuous range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //range1 and range2 are discontinuous ranges
  IRange range1 = sheet.Range["A1:A2"];
  IRange range2 = sheet.Range["C1:C2"];
  IRanges ranges = sheet.CreateRangesCollection();

  //range1 and range2 are considered as a single range
  ranges.Add(range1);
  ranges.Add(range2);
  ranges.Text = "Test";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Range.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //range1 and range2 are discontinuous ranges
  IRange range1 = sheet.Range["A1:A2"];
  IRange range2 = sheet.Range["C1:C2"];
  IRanges ranges = sheet.CreateRangesCollection();

  //range1 and range2 are considered as a single range
  ranges.Add(range1);
  ranges.Add(range2);
  ranges.Text = "Test";

  workbook.SaveAs("Range.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'range1 and range2 are discontinuous ranges
  Dim range1 As IRange = sheet.Range("A1:A2")
  Dim range2 As IRange = sheet.Range("C1:C2")
  Dim ranges As IRanges = sheet.CreateRangesCollection()

  'range1 and range2 are considered as a single range
  ranges.Add(range1)
  ranges.Add(range2)
  ranges.Text = "Test"

  workbook.SaveAs("Range.xlsx")
End Using

A complete working example to access discontinuous range in an Excel worksheet in C# is present on this GitHub page.

Accessing a Cell or Range using IMigrantRange

The IMigrantRange interface can also be used to access a single cell or group of cells and manipulate it. It is recommended to prefer IMigrantRange instead of IRange for writing large amount of data in an optimal way.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  IMigrantRange migrantRange = sheet.MigrantRange;

  //Writing Data
  for (int row = 1; row <= migrantRange.LastRow; row++)
  {
    for (int column = 1; column <= migrantRange.LastColumn; column++)
    {
      //Writing values
      migrantRange.ResetRowColumn(row, column);
      migrantRange.Text = "Test";
    }
  }

  //Saving the workbook as stream
  FileStream stream = new FileStream("Range.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];
  IMigrantRange migrantRange = sheet.MigrantRange;

  //Writing Data
  for (int row = 1; row <= migrantRange.LastRow; row++)
  {
    for (int column = 1; column <= migrantRange.LastColumn; column++)
    {
      //Writing values
      migrantRange.ResetRowColumn(row, column);
      migrantRange.Text = "Test";
    }
  }
  
  workbook.SaveAs("Range.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  Dim migrantRange As IMigrantRange = sheet.MigrantRange

  'Writing Data
  Dim row As Integer
  For row = 1 To migrantRange.LastRow Step row + 1
    Dim column As Integer
    For column = 1 To migrantRange.LastColumn Step column + 1
      'Writing values
      migrantRange.ResetRowColumn(row, column)
      migrantRange.Text = "Test"
    Next
  Next

  workbook.SaveAs("Range.xlsx")
End Using

A complete working example to access migrant range in an Excel worksheet in C# is present on this GitHub page.

Accessing used range of a Worksheet

The following code snippet shows how to get the range of used cells in a given Excel worksheet.

NOTE

By default, XlsIO considers a cell as used, even if there exists some formatting alone. This behavior can be disabled, and make XlsIO consider a cell as used, only when there exists data, through the UsedRangeIncludesFormatting property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //UsedRange excludes the blank cell which has formatting
  worksheet.UsedRangeIncludesFormatting = false;

  //Modifying the column width and row height of the used range
  worksheet.UsedRange.ColumnWidth = 20;
  worksheet.UsedRange.RowHeight = 20;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Range.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //UsedRange excludes the blank cell which has formatting
  sheet.UsedRangeIncludesFormatting = false;

  //Modifying the column width and row height of the used range
  sheet.UsedRange.ColumnWidth = 20;
  sheet.UsedRange.RowHeight = 20;

  workbook.SaveAs("Range.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'UsedRange excludes the blank cell which has formatting
  sheet.UsedRangeIncludesFormatting = False

  'Modifying only the Used Ranges
  sheet.UsedRange.ColumnWidth = 20
  sheet.UsedRange.RowHeight = 20

  workbook.SaveAs("Range.xlsx")
End Using

A complete working example to access used range in an Excel worksheet in C# is present on this GitHub page.

Get Precedent and Dependent Cells or Range

Precedent cells are cells that are referred to by a formula in another cell. Dependent cells contain formulas that refer to other cells. XlsIO allows to trace the relationship between cells and formulas in Excel workbooks and returns the list of cells or range that are precedent and dependent.

Accessing list of precedent and dependent cells can be obtained:

  • from a worksheet
  • from a workbook

Following code example illustrates how to get precedent cells from a worksheet and entire workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("FormulaExcel.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting precedent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetPrecedents();

  //Getting precedent cells from the workbook
  IRange[] results2 = sheet["A1"].GetPrecedents(true);

  //Saving the workbook as stream
  FileStream file = new FileStream("Precedents.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting precedent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetPrecedents();

  //Getting precedent cells from the workbook
  IRange[] results2 = sheet["A1"].GetPrecedents(true);

  string fileName = "Precedents.xlsx";
  workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("FormulaExcel.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Getting precedent cells from the worksheet
  Dim results1() As IRange = sheet("A1").GetPrecedents()

  'Getting precedent cells from the workbook
  Dim results2() As IRange = sheet("A1").GetPrecedents(True)

  Dim fileName As String = "Precedents.xlsx"
  workbook.SaveAs(fileName)
End Using

Following code example illustrates how to get dependent cells from a worksheet and entire workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("FormulaExcel.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting dependent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDependents();

  //Getting dependent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDependents(true);

  //Saving the workbook as stream
  FileStream file = new FileStream("Dependents.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting dependent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDependents();

  //Getting dependent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDependents(true);

  string fileName = "Dependents.xlsx";
  workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("FormulaExcel.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Getting dependent cells from the worksheet
  Dim results1() As IRange = sheet("A1").GetDependents()

  'Getting dependent cells from the workbook
  Dim results2() As IRange = sheet("A1").GetDependents(True)

  Dim fileName As String = "Dependents.xlsx"
  workbook.SaveAs(fileName)
End Using

Get Direct Precedent and Dependent Cells

GetDirectDependents and GetDirectPrecedents methods are used to get direct dependent/precedent cells for source range, excluding inner dependent/precedent cells.

Following code example illustrates how to get direct precedent cells from a worksheet and entire workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("FormulaExcel.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting precedent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDirectPrecedents();

  //Getting precedent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDirectPrecedents(true);

  //Saving the workbook as stream
  FileStream file = new FileStream("DirectPrecedents.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting precedent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDirectPrecedents();

  //Getting precedent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDirectPrecedents(true);

  string fileName = "DirectPrecedents.xlsx";
  workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("FormulaExcel.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Getting precedent cells from the worksheet
  Dim results1() As IRange = sheet("A1").GetDirectPrecedents()

  'Getting precedent cells from the workbook
  Dim results2() As IRange = sheet("A1").GetDirectPrecedents(True)

  Dim fileName As String = "DirectPrecedents.xlsx"
  workbook.SaveAs(fileName)
End Using

Following code example illustrates how to get direct dependent cells from a worksheet and entire workbook.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("FormulaExcel.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting dependent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDirectDependents();

  //Getting dependent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDirectDependents(true);

  //Saving the workbook as stream
  FileStream file = new FileStream("DirectDependents.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("FormulaExcel.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Getting dependent cells from the worksheet
  IRange[] results1 = sheet["A1"].GetDirectDependents();

  //Getting dependent cells from the workbook
  IRange[] results2 = sheet["A1"].GetDirectDependents(true);

  string fileName = "DirectDependents.xlsx";
  workbook.SaveAs(fileName);
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("FormulaExcel.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Getting dependent cells from the worksheet
  Dim results1() As IRange = sheet("A1").GetDirectDependents()

  'Getting dependent cells from the workbook
  Dim results2() As IRange = sheet("A1").GetDirectDependents(True)

  Dim fileName As String = "DirectDependents.xlsx"
  workbook.SaveAs(fileName)
End Using

A complete working example to get the precedent and dependent cells in an Excel worksheet in C# is present on this GitHub page.

Clearing a Cell Content

Either entire cell content, or just formatting, contents, or comments can be removed from Excel cell. The following code example illustrates how to clear a range along with its formatting.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;

  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Clearing a Range “A4” and its formatting
  worksheet.Range["A4"].Clear(true);

  //Saving the workbook as stream
  FileStream stream = new FileStream("ClearRange.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Clearing a Range “A4” and its formatting
  sheet.Range["A4"].Clear(true);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("ClearRange.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Clearing a Range “A4” and its formatting
  sheet.Range("A4").Clear(True)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("ClearRange.xlsx")
End Using

A complete working example to clear cell content in an Excel worksheet in C# is present on this GitHub page.

Copy or Move a Range

A single cell or a range of cells can be copied to another range using CopyTo method. It is also possible to copy all the formats or only specific formats using ExcelCopyRangeOptions options.

Following code example illustrates how to copy a cell from the source to destination.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Copying a Range “A1” to “A5”
  IRange source = worksheet.Range["A1"];
  IRange destination = worksheet.Range["A5"];
  source.CopyTo(destination, ExcelCopyRangeOptions.All);

  //Saving the workbook as stream
  FileStream stream = new FileStream("CopyRange.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Copying a Range “A1” to “A5”
  IRange source = sheet.Range["A1"];
  IRange destination = sheet.Range["A5"];
  source.CopyTo(destination, ExcelCopyRangeOptions.All);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("CopyRange.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Copying a Range “A1” to “A5”
  Dim source As IRange = sheet.Range("A1")
  Dim destination As IRange = sheet.Range("A5")
  source.CopyTo(destination, ExcelCopyRangeOptions.All)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("CopyRange.xlsx")
End Using

A complete working example to copy range in an Excel worksheet in C# is present on this GitHub page.

MoveTo method is used for moving a range of cells to another range as shown below.

NOTE

MoveTo method does not update formulas.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Moving a Range “A1” to “A5”
  IRange source = worksheet.Range["A1"];
  IRange destination = worksheet.Range["A5"];
  source.MoveTo(destination);

  //Saving the workbook as stream
  FileStream stream = new FileStream("MoveRange.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Moving a Range “A1” to “A5”
  IRange source = sheet.Range["A1"];
  IRange destination = sheet.Range["A5"];
  source.MoveTo(destination);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("MoveRange.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Moving a Range “A1” to “A5”
  Dim source As IRange = sheet.Range("A1")
  Dim destination As IRange = sheet.Range("A5")
  source.MoveTo(destination)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("MoveRange.xlsx")
End Using

A complete working example to move range in an Excel worksheet in C# is present on this GitHub page.

A range can be copied and paste the range as link to another range using a bool parameter in CopyTo method.

Following code example illustrates how to paste a range of cells as link.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Copy range as link from Range “A1” to “A5”
  IRange source = worksheet.Range["A1"];
  IRange destination = worksheet.Range["A5"];
  source.CopyTo(destination, true);
  
  //Saving the workbook as stream
  FileStream stream = new FileStream("PasteLink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Copy range as link from Range “A1” to “A5”
  IRange source = sheet.Range["A1"];
  IRange destination = sheet.Range["D5"];
  source.CopyTo(destination, true);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("PasteLink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Copy range as link from Range “A1” to “A5”
  Dim source As IRange = sheet.Range("A1")
  Dim destination As IRange = sheet.Range("A5")
  source.CopyTo(destination, True)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("PasteLink.xlsx")
End Using

A complete working example to copy and paste as link in an Excel worksheet in C# is present on this GitHub page.

Skip Blanks While Copying

Blank cells can be skipped while copying from source to destination range by setting the parameter skipBlanks to TRUE in CopyTo method.

The following code illustrates how to skip blank cells while copying.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream sampleFile = new FileStream("Sample.xlsx", FileMode.Open);
  IWorkbook workbook = application.Workbooks.Open(sampleFile);
  IWorksheet sheet = workbook.Worksheets[0];             
  
  // Copy range as link from Range “A1” to “A5”.
  IRange source = sheet.Range["A1:A7"];
  IRange destination = sheet.Range["C3"];
  
  //Skip blanks while copying
  source.CopyTo(destination, ExcelCopyRangeOptions.All, true);

  //Saving the workbook to stream in XLSX format
  FileStream stream = new FileStream("SkipBlank.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];
  
  // Copy range as link from Range “A1” to “A5”.
  IRange source = sheet.Range["A1:A7"];
  IRange destination = sheet.Range["C3"];
  
  //Skip blanks while copying
  source.CopyTo(destination, ExcelCopyRangeOptions.All, true);
  
  //Save workbook
  workbook.SaveAs("SkipBlank.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx;
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorkbook = workbook.Worksheets(0)
  
  ' Copy range as link from Range “A1” to “A5”.
  Dim source As IRange = sheet.Range("A1:A7")
  Dim destination As IRange = sheet.Range("C3")
  
  ' Skip blanks while copying
  source.CopyTo(destination, ExcelCopyRangeOptions.All, true)
  
  ' Save workbook
  workbook.SaveAs("SkipBlank.xlsx")
End Using

Find and Replace

XlsIO provides following options to perform find and replace for text and numbers in Excel workbook or worksheet:

  • Search for data in formulas, values or comments.
  • Search for case-sensitive data and to match entire cell contents of the cell.

To know more about these options, please refer the ExcelFindType, ExcelFindOptions in the API documentation section.

All the occurrences of a text in Excel worksheet can be found through FindAll method.

The following code illustrates how to find all the occurrences of text in a worksheet with different find options.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Searches for the given string within the text of worksheet
  IRange[] result1 = worksheet.FindAll("FindValue", ExcelFindType.Text);

  //Searches for the given string in formulas
  IRange[] result2 = worksheet.FindAll("FindValue", ExcelFindType.Formula);

  //Searches for the given string in calculated value, number and text
  IRange[] result3 = worksheet.FindAll("FindValue", ExcelFindType.Values);

  //Searches for the given string in comments
  IRange[] result4 = worksheet.FindAll("FindValue", ExcelFindType.Comments);

  //Searches for the given string within the text of worksheet and case matched
  IRange[] result5 = worksheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchCase);

  //Searches for the given string within the text of worksheet and the entire cell content matching to search text
  IRange[] result6 = worksheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchEntireCellContent);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Find.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Searches for the given string within the text of worksheet
  IRange[] result1 = sheet.FindAll("FindValue", ExcelFindType.Text);

  //Searches for the given string in formulas
  IRange[] result2 = sheet.FindAll("FindValue", ExcelFindType.Formula);

  //Searches for the given string in calculated value, number and text
  IRange[] result3 = sheet.FindAll("FindValue", ExcelFindType.Values);

  //Searches for the given string in comments
  IRange[] result4 = sheet.FindAll("FindValue", ExcelFindType.Comments);

  //Searches for the given string within the text of worksheet and case matched
  IRange[] result5 = sheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchCase);

  //Searches for the given string within the text of worksheet and the entire cell content matching to search text
  IRange[] result6 = sheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchEntireCellContent);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Find.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Searches for the given string within the text of worksheet
  Dim result1() As IRange = sheet.FindAll("FindValue", ExcelFindType.Text)

  'Searches for the given string in formulas
  Dim result2() As IRange = sheet.FindAll("FindValue", ExcelFindType.Formula)

  'Searches for the given string in calculated value, number and text
  Dim result3() As IRange = sheet.FindAll("FindValue", ExcelFindType.Values)

  'Searches for the given string in comments
  Dim result4() As IRange = sheet.FindAll("FindValue", ExcelFindType.Comments)

  'Searches for the given string within the text of worksheet and case matched
  Dim result5() As IRange = sheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchCase)

  'Searches for the given string within the text of worksheet and the entire cell content matching to search text
  Dim result6() As IRange = sheet.FindAll("FindValue", ExcelFindType.Text, ExcelFindOptions.MatchEntireCellContent)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Find.xlsx")
End Using

It is possible to replace a text with another text with the help of Replace method which searches for text which should be changed. A string can be replaced, with the data of various data types and data sources, such as data table, data column and array.

To know more about replace overloads, please refer Replace in the API documentation section.

The following code example illustrates how to replace all occurrences of given string with various data.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Replaces the given string with another string
  worksheet.Replace("FindValue", "NewValue");

  //Replaces the given string with another string on match case
  worksheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchCase);

  //Replaces the given string with another string matching entire cell content to the search word
  worksheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchEntireCellContent);

  //Replaces the given string with DateTime value
  worksheet.Replace("DateValue", DateTime.Now);

  //Replaces the given string with Array
  worksheet.Replace("ArrayValue", new string[] { "ArrayValue1", "ArrayValue2", "ArrayValue3" }, true);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Replace.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Replaces the given string with another string
  sheet.Replace("FindValue", "NewValue");

  //Replaces the given string with another string on match case
  sheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchCase);

  //Replaces the given string with another string matching entire cell content to the search word
  sheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchEntireCellContent);

  //Replaces the given string with DateTime value
  sheet.Replace("DateValue", DateTime.Now);

  //Replaces the given string with Array
  sheet.Replace("ArrayValue", new string[] { "ArrayValue1", "ArrayValue2", "ArrayValue3" }, true);

  //Replaces the given string with DataTable
  DataTable table = SampleDataTable();
  sheet.Replace("DataTable", table, true);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Replace.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Replaces the given string with another string
  sheet.Replace("FindValue", "NewValue")

  'Replaces the given string with another string on match case
  sheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchCase)

  'Replaces the given string with another string matching entire cell content to the search word
  sheet.Replace("FindValue", "NewValue", ExcelFindOptions.MatchEntireCellContent)

  'Replaces the given string with DateTime value
  sheet.Replace("DateValue", DateTime.Now)

  'Replaces the given string with Array
  sheet.Replace("ArrayValue", New String() {"ArrayValue1", "ArrayValue2", "ArrayValue3"}, True)

  'Replaces the given string with DataTable
  Dim table As DataTable = SampleDataTable()
  sheet.Replace("DataTable", table, True)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Replace.xlsx")
End Using

Data Sorting

A range of cells in Excel worksheet can be sorted based on data in one or more columns. Following types of sorting is supported in XlsIO:

  • Based on Cell Values
  • Based on Font Color
  • Based on Cell Color

NOTE

Currently XlsIO don’t support sorting based on cell icon, parsing and serialization of its sorting details.

Based on Cell Values

The following code snippet explains how to sort a range of cells by values

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("SortingData.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = worksheet.Range["D3:D16"];

  //Adds the sort field with the column index, sort based on and order by attribute
  ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

  //Adds another sort field
  ISortField sortField2 = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);

  //Sort based on the sort Field attribute
  sorter.Sort();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = sheet.Range["D3:D16"];

  //Adds the sort field with the column index, sort based on and order by attribute
  ISortField sortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending);

  //Adds another sort field
  ISortField sortField2 = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending);

  //Sort based on the sort Field attribute
  sorter.Sort();

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creates the Data sorter
  Dim sorter As IDataSort = workbook.CreateDataSorter()

  'Specifies the sort range
  sorter.SortRange = sheet.Range("D3:D16")

  'Adds the sort field with column index, sort based on and order by attribute
  Dim sortField As ISortField = sorter.SortFields.Add(0, SortOn.Values, OrderBy.Ascending)

  'Adds the second sort field
  Dim sortField2 As ISortField = sorter.SortFields.Add(1, SortOn.Values, OrderBy.Ascending)

  'Sorts the data with the sort field attribute
  sorter.Sort()

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Sort.xlsx")
End Using

A complete working example to sort Excel data based on cell values in C# is present on this GitHub page.

Based on Font Color

The following code snippet explains how to move a range of cells with the specified font color to either top or bottom of the sorting range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = worksheet.Range["A2:D16"];

  //Creates the sort field with the column index, sort based on and order by attribute
  ISortField sortField1 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField1.Color = Color.Red;

  //Creates another sort field with the column index, sort based on and order by attribute
  ISortField sortField2 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField2.Color = Color.Green;

  //Sort based on the sort Field attribute
  sorter.Sort();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = sheet.Range["A2:D16"];

  //Creates the sort field with the column index, sort based on and order by attribute
  ISortField sortField1 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField1.Color = Color.Red;

  //Creates another sort field with the column index, sort based on and order by attribute
  ISortField sortField2 = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField2.Color = Color.Green;

  //Sort based on the sort Field attribute
  sorter.Sort();

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creates the Data sorter
  Dim sorter As IDataSort = workbook.CreateDataSorter()

  'Specifies the sort range
  sorter.SortRange = sheet.Range("A2:D16")

  'Adds the sort field with column index, sort based on and order by attribute
  Dim field1 As ISortField = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop)

  'Sorts the data based on this color
  field1.Color = Color.Red

  'Adds another sort field with column index, sort based on and order by attribute
  Dim field2 As ISortField = sorter.SortFields.Add(2, SortOn.FontColor, OrderBy.OnTop)

  'Sorts the data based on this color
  field2.Color = Color.Green

  'Sorts the data with the sort field attribute
  sorter.Sort()

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Sort.xlsx")
End Using

A complete working example to sort Excel data based on font color in C# is present on this GitHub page.

Based on Cell Color

The following code snippet explains how to move a range of cells with the specified cell background color to either top or bottom of the sorting range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = worksheet.Range["A2:D16"];

  //Creates the sort field with the column index, sort based on and order by attribute
  ISortField sortField1 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField1.Color = Color.Red;

  //Creates another sort field with the column index, sort based on and order by attribute
  ISortField sortField2 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField2.Color = Color.Green;

  //Sort based on the sort Field attribute
  sorter.Sort();

  //Saving the workbook as stream
  FileStream stream = new FileStream("Sort.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creates the data sorter
  IDataSort sorter = workbook.CreateDataSorter();

  //Range to sort
  sorter.SortRange = sheet.Range["A2:D16"];

  //Creates the sort field with the column index, sort based on and order by attribute
  ISortField sortField1 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField1.Color = Color.Red;

  //Creates the sort field with the column index, sort based on and order by attribute
  ISortField sortField2 = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop);

  //Specifies the color to sort the data
  sortField2.Color = Color.Green;

  //Sort based on the sort field attribute
  sorter.Sort();

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Sort.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  Dim sorter As IDataSort = workbook.CreateDataSorter()

  'Specifies the sort range.
  sorter.SortRange = sheet.Range("A2:D16")

  'Adds the sort field with column index, sort based on and order by attribute
  Dim field1 As ISortField = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop)

  'Sorts the data based on this color
  field1.Color = Color.Red

  'Adds the sort field with column index, sort based on and order by attribute
  Dim field2 As ISortField = sorter.SortFields.Add(2, SortOn.CellColor, OrderBy.OnTop)

  'Sorts the data based on this color
  field2.Color = Color.Green

  'Sorts the data with the sort field attribute
  sorter.Sort()

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Sort.xlsx")
End Using

A complete working example to sort Excel data based on cell color in C# is present on this GitHub page.

Data Filtering

Using AutoFilters, data can be filtered to enable quick and easy way to find and work with a subset of data in a range of cells. When the data is filtered, entire rows are hidden if values in one or more columns does not meet the filtering criteria. The following are the types of filters that can be used in XlsIO.

  • Custom Filter (Conditional)
  • Combination Filter (Text and DateTime filter)
  • Dynamic Filter
  • Color Filter
  • Icon Filter
  • Advanced Filter

Applying Filter

The following code illustrates how to apply simple auto filters.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied
  IAutoFilter filter = worksheet.AutoFilters[0];

  //To apply Top10Number filter, IsTop and IsTop10 must be enabled
  filter.IsTop = true;
  filter.IsTop10 = true;

  //Setting Top10 filter with number of cell to be filtered from top
  filter.Top10Number = 5;

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range. 
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied
  IAutoFilter filter = sheet.AutoFilters[0];

  //To apply Top10Number filter, IsTop and IsTop10 must be enabled
  filter.IsTop = true;
  filter.IsTop10 = true;

  //Setting Top10 filter with number of cell to be filtered from top
  filter.Top10Number = 5;

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range. 
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied.
  Dim filter As IAutoFilter = sheet.AutoFilters(0)

  'To apply Top10Number filter, IsTop and IsTop10 must be enabled.
  filter.IsTop = True
  filter.IsTop10 = True

  'Setting Top10 filter with number of cell to be filtered from top
  filter.Top10Number = 5

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply filter on Excel data in C# is present on this GitHub page.

Custom Filter

Following code snippet illustrates how to apply custom filter, based on first and second condition.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
  sheet.AutoFilters.FilterRange = sheet.Range["A1:B323"];
  IAutoFilter filter = sheet.AutoFilters[1];

  //Specifying first condition
  IAutoFilterCondition firstCondition = filter.FirstCondition;
  firstCondition.ConditionOperator = ExcelFilterCondition.Greater;
  firstCondition.Double = 100;

  //Specifying second condition
  IAutoFilterCondition secondCondition = filter.SecondCondition;
  secondCondition.ConditionOperator = ExcelFilterCondition.Less;
  secondCondition.Double = 200;

  //Saving the workbook as stream
  FileStream file = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
  sheet.AutoFilters.FilterRange = sheet.Range["A1:B323"];
  IAutoFilter filter = sheet.AutoFilters[1];

  //Specifying first condition
  IAutoFilterCondition firstCondition = filter.FirstCondition;
  firstCondition.ConditionOperator = ExcelFilterCondition.Greater;
  firstCondition.Double = 100;

  //Specifying second condition
  IAutoFilterCondition secondCondition = filter.SecondCondition;
  secondCondition.ConditionOperator = ExcelFilterCondition.Less;
  secondCondition.Double = 200;

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range("A1:B323")
  Dim filter As IAutoFilter = sheet.AutoFilters(1)

  'Specifying first condition.
  Dim firstCondition As IAutoFilterCondition = filter.FirstCondition
  firstCondition.ConditionOperator = ExcelFilterCondition.Greater
  firstCondition.Double = 100

  'Specifying second condition.
  Dim secondCondition As IAutoFilterCondition = filter.SecondCondition
  secondCondition.ConditionOperator = ExcelFilterCondition.Less
  secondCondition.Double = 200

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply custom filter on Excel data in C# is present on this GitHub page.

Combination Filter

This filter contains both Text filter and DateTime filter. It filters the data based on multiple criteria. Following code snippet illustrates how to apply combination filter with multiple of Text filter and DateTime filter.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range. 
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = worksheet.AutoFilters[2];

  //Applying Text filter to filter multiple text to get filter.
  filter.AddTextFilter(new string[] { "London", "Paris", "New York City" });

  //Applying DateTime filter to filter the date based on DateTimeGroupingType.
  filter.AddDateFilter(new DateTime(2013, 1, 29, 0, 0, 0), DateTimeGroupingType.day);
  filter.AddDateFilter(2014, 12, 2, 10, 30, 0, DateTimeGroupingType.minute);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied
  IAutoFilter filter = sheet.AutoFilters[2];

  //Applying Text filter to filter multiple text to get filter
  filter.AddTextFilter(new string[] { "London", "Paris", "New York City" });

  //Applying DateTime filter to filter the date based on DateTimeGroupingType
  filter.AddDateFilter(new DateTime(2013, 1, 29, 0, 0, 0), DateTimeGroupingType.day);
  filter.AddDateFilter(2014, 12, 2, 10, 30, 0, DateTimeGroupingType.minute);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied
  Dim filter As IAutoFilter = sheet.AutoFilters(2)

  'Applying Text filter to filter multiple text to get filter
  filter.AddTextFilter(New String() {"London", "Paris", "New York City"})

  'Applying DateTime filter to filter the date based on DateTimeGroupingType
  filter.AddDateFilter(New DateTime(2013, 1, 29, 0, 0, 0), DateTimeGroupingType.day)
  filter.AddDateFilter(2014, 12, 2, 10, 30, 0, DateTimeGroupingType.minute)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply combination filter on Excel data in C# is present on this GitHub page.

Dynamic Filter

Dynamic filter is a relative date filter, which filters data based on DynamicFilterType enumeration. Following code snippet illustrates how to apply Dynamic filter.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = worksheet.AutoFilters[3];

  //Applying dynamic filter to filter the date based on DynamicFilterType.
  filter.AddDynamicFilter(DynamicFilterType.NextQuarter);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = sheet.AutoFilters[3];

  //Applying dynamic filter to filter the date based on DynamicFilterType.
  filter.AddDynamicFilter(DynamicFilterType.NextQuarter);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied.
  Dim filter As IAutoFilter = sheet.AutoFilters(3)

  'Applying dynamic filter to filter the date based on DynamicFilterType.
  filter.AddDynamicFilter(DynamicFilterType.NextQuarter)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply dynamic filter on Excel data in C# is present on this GitHub page.

Color Filter

Color Filter can be used to filter data based on the color applied to the cell or the color applied to the text in the cell. The following code snippet show how to apply Color Filter based on Cell Color (fill color applied to the cell).

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = worksheet.AutoFilters[3];

  //Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.CellColor);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = sheet.AutoFilters[3];

  //Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.CellColor);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied.
  Dim filter As IAutoFilter = sheet.AutoFilters(3)

  'Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.CellColor)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply color filter on Excel data based on cell color in C# is present on this GitHub page.

To filter cells based on Font color of the text inside cells just change the ExcelColorFilterType to Font Color. The following snippet show how to filter the cells based on font color.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = worksheet.AutoFilters[3];

  //Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.FontColor);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = sheet.AutoFilters[3];

  //Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.FontColor);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied.
  Dim filter As IAutoFilter = sheet.AutoFilters(3)

  'Applying color filter to filter based on Cell Color.
  filter.AddColorFilter(Color.Red, ExcelColorFilterType.FontColor)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply color filter on Excel data based on font color in C# is present on this GitHub page.

Icon Filter

Icon filter can be used to filter data that has conditional formatting with Icon Sets applied. Applying Icon Sets for numeric data adds icons to each cell based on the value present in that cell. Using Icon Filter, the data that has only a specific Icon in it can be filtered easily.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  worksheet.AutoFilters.FilterRange = worksheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = worksheet.AutoFilters[3];

  //Applying Icon filter to filter based on applied icon set.
  filter.AddIconFilter(ExcelIconSetType.ThreeFlags, 2);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Filter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range["A1:K180"];

  //Column index to which AutoFilter must be applied.
  IAutoFilter filter = sheet.AutoFilters[3];

  //Applying Icon filter to filter based on applied icon set.
  filter.AddIconFilter(ExcelIconSetType.ThreeFlags, 2);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Filter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating an AutoFilter in the first worksheet. Specifying the AutoFilter range.
  sheet.AutoFilters.FilterRange = sheet.Range("A1:K180")

  'Column index to which AutoFilter must be applied.
  Dim filter As IAutoFilter = sheet.AutoFilters(3)

  'Applying Icon filter to filter based on applied icon set.
  filter.AddIconFilter(ExcelIconSetType.ThreeFlags, 2)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Filter.xlsx")
End Using

A complete working example to apply icon filter on Excel data in C# is present on this GitHub page.

Advanced Filter

Advanced Filter can be used to perform more complex filtering other than basic filters. Data can be filtered with custom defined criteria range.

Advanced Filter support two types of filter action.

  1. Filter In Place
  2. Filter Copy

Filter In Place: Filter data in same location.
Filter Copy: Filter and copy data into new location within a worksheet.

Advanced Filter also provides an option to filter the unique records. This will remove the duplicate record from filtered data.

The following code illustrates how to apply Advanced Filter in worksheet.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("InputData.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  IRange filterRange = worksheet.Range["A1:C6"];
  IRange criteriaRange = worksheet.Range["A10:C12"];
  IRange copyToRange = worksheet.Range["K5:N5"];

  //Apply the Advanced Filter with enable of unique value and copy to another place.
  worksheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyToRange, true);

  //Saving the workbook as stream
  FileStream stream = new FileStream("AdvancedFilter.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("InputData.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  IRange filterRange = sheet.Range["A1:C6"];
  IRange criteriaRange = sheet.Range["A10:C12"];
  IRange copyToRange = sheet.Range["K5:N5"];

  //Apply the Advanced Filter with enable of unique value and copy to another place.
  sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyToRange, true);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("AdvancedFilter.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("InputData.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  Dim filterRange As IRange = sheet.Range("A1:C6")
  Dim criteriaRange As IRange = sheet.Range("A10:C12")
  Dim copyToRange As IRange = sheet.Range("K5:N5")

  'Apply the Advanced filter with enable of unique value and copy to another place.
  sheet.AdvancedFilter(ExcelFilterAction.FilterCopy, filterRange, criteriaRange, copyToRange, True)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("AdvancedFilter.xlsx")
End Using

A complete working example to apply advanced filter on Excel data in C# is present on this GitHub page.

Accessing Filter

A filter and its criteria can be accessed based on its column index. Following code snippet illustrates how to access different types of filters using XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream inputStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet sheet = workbook.Worksheets[0];

  //selecting the filter by column index
  IAutoFilter filter = sheet.AutoFilters[0];

  switch (filter.FilterType)
  {
    case ExcelFilterType.CombinationFilter:
      CombinationFilter filterItems = (filter.FilteredItems as CombinationFilter);
      for (int index = 0; index < filterItems.Count; index++)
      {
        if (filterItems[index].CombinationFilterType == ExcelCombinationFilterType.TextFilter)
        {
          string textValue = (filterItems[index] as TextFilter).Text;
        }
        else
        {
          DateTimeGroupingType groupType = (filterItems[index] as DateTimeFilter).GroupingType;
        }
      }
      break;

    case ExcelFilterType.DynamicFilter:
      DynamicFilter dateFilter = (filter.FilteredItems as DynamicFilter);
      DynamicFilterType dynamicFilterType = dateFilter.DateFilterType;
      break;

    case ExcelFilterType.CustomFilter:
      IAutoFilterCondition firstCondition = filter.FirstCondition;
      ExcelFilterDataType types = firstCondition.DataType;
      break;

    case ExcelFilterType.ColorFilter:
      ColorFilter colorFilter = (filter.FilteredItems as ColorFilter);
      Color color = colorFilter.Color;
      ExcelColorFilterType filterType = colorFilter.ColorFilterType;
      break;

    case ExcelFilterType.IconFilter:
      IconFilter iconFilter = (filter.FilteredItems as IconFilter);
      int iconId = iconFilter.IconId;
      ExcelIconSetType iconSetType = iconFilter.IconSetType;
      break;
  }

  //Saving the workbook as stream
  FileStream file = new FileStream("Output.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic);
  IWorksheet worksheet = workbook.Worksheets[0];

  //selecting the filter by column index
  IAutoFilter filter = worksheet.AutoFilters[0];

  switch (filter.FilterType)
  {
    case ExcelFilterType.CombinationFilter:
      CombinationFilter filterItems = (filter.FilteredItems as CombinationFilter);
      for (int index = 0; index < filterItems.Count; index++)
      {
        if (filterItems[index].CombinationFilterType == ExcelCombinationFilterType.TextFilter)
        {
          string textValue = (filterItems[index] as TextFilter).Text;
        }
        else
        {
          DateTimeGroupingType groupType = (filterItems[index] as DateTimeFilter).GroupingType;
        }
      }
      break;

    case ExcelFilterType.DynamicFilter:
      DynamicFilter dateFilter = (filter.FilteredItems as DynamicFilter);
      DynamicFilterType dynamicFilterType = dateFilter.DateFilterType;
      break;

    case ExcelFilterType.CustomFilter:
      IAutoFilterCondition firstCondition = filter.FirstCondition;
      ExcelFilterDataType types = firstCondition.DataType;
      break;

    case ExcelFilterType.ColorFilter:
      ColorFilter colorFilter = (filter.FilteredItems as ColorFilter);
      Color color = colorFilter.Color;
      ExcelColorFilterType filterType = colorFilter.ColorFilterType;
      break;

    case ExcelFilterType.IconFilter:
      IconFilter iconFilter = (filter.FilteredItems as IconFilter);
      int iconId = iconFilter.IconId;
      ExcelIconSetType iconSetType = iconFilter.IconSetType;
      break;
  }

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx", ExcelOpenType.Automatic)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'selecting the filter by column index
  Dim filter As IAutoFilter = worksheet.AutoFilters(0)

  Select Case filter.FilterType
    Case ExcelFilterType.CombinationFilter
      Dim filterItems As CombinationFilter = TryCast(filter.FilteredItems, CombinationFilter)
      For index As Integer = 0 To filterItems.Count - 1
        If filterItems(index).CombinationFilterType = ExcelCombinationFilterType.TextFilter Then
          Dim textValue As String = TryCast(filterItems(index), TextFilter).Text
        Else
          Dim groupType As DateTimeGroupingType = TryCast(filterItems(index), DateTimeFilter).GroupingType
        End If
      Next
      Exit Select

    Case ExcelFilterType.DynamicFilter
      Dim dateFilter As DynamicFilter = TryCast(filter.FilteredItems, DynamicFilter)
      Dim dynamicFilterType As DynamicFilterType = dateFilter.DateFilterType
      Exit Select

    Case ExcelFilterType.CustomFilter
      Dim firstCondition As IAutoFilterCondition = filter.FirstCondition
      Dim types As ExcelFilterDataType = firstCondition.DataType
      Exit Select

    Case ExcelFilterType.ColorFilter
      Dim colorFilter As ColorFilter = TryCast(filter.FilteredItems, ColorFilter)
      Dim color As Color = colorFilter.Color
      Dim filterType As ExcelColorFilterType = colorFilter.ColorFilterType
      Exit Select

    Case ExcelFilterType.IconFilter
      Dim iconFilter As IconFilter = TryCast(filter.FilteredItems, IconFilter)
      Dim iconId As Int32 = iconFilter.IconId
      Dim iconSetType As ExcelIconSetType = iconFilter.IconSetType
      Exit Select
  End Select

  workbook.SaveAs("Output.xlsx")
End Using

A complete working example to access filters from Excel worksheet in C# is present on this GitHub page.

Hyperlink can be created in a workbook to provide quick access to web pages, places in your document and files. Hyperlink may target to any one of the following

  • Worksheet range
  • Web URL
  • E-mail
  • External files

The following code example illustrates how to insert various hyperlinks.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating a Hyperlink for a Website
  IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
  hyperlink.Type = ExcelHyperLinkType.Url;
  hyperlink.Address = "http://www.syncfusion.com";
  hyperlink.ScreenTip = "To know more about Syncfusion products, go through this link.";

  //Creating a Hyperlink for e-mail
  IHyperLink hyperlink1 = sheet.HyperLinks.Add(sheet.Range["C7"]);
  hyperlink1.Type = ExcelHyperLinkType.Url;
  hyperlink1.Address = "mailto:Username@syncfusion.com";
  hyperlink1.ScreenTip = "Send Mail";

  //Creating a Hyperlink for Opening Files using type as File
  IHyperLink hyperlink2 = sheet.HyperLinks.Add(sheet.Range["C9"]);
  hyperlink2.Type = ExcelHyperLinkType.File;
  hyperlink2.Address = "C:/Program files";
  hyperlink2.ScreenTip = "File path";
  hyperlink2.TextToDisplay = "Hyperlink for files using File as type";

  //Creating a Hyperlink for Opening Files using type as Unc
  IHyperLink hyperlink3 = sheet.HyperLinks.Add(sheet.Range["C11"]);
  hyperlink3.Type = ExcelHyperLinkType.Unc;
  hyperlink3.Address = "C:/Documents and Settings";
  hyperlink3.ScreenTip = "Click here for files";
  hyperlink3.TextToDisplay = "Hyperlink for files using Unc as type";

  //Creating a Hyperlink to another cell using type as Workbook
  IHyperLink hyperlink4 = sheet.HyperLinks.Add(sheet.Range["C13"]);
  hyperlink4.Type = ExcelHyperLinkType.Workbook;
  hyperlink4.Address = "Sheet1!A15";
  hyperlink4.ScreenTip = "Click here";
  hyperlink4.TextToDisplay = "Hyperlink to cell A15";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet sheet = workbook.Worksheets[0];

  //Creating a Hyperlink for a Website
  IHyperLink hyperlink = sheet.HyperLinks.Add(sheet.Range["C5"]);
  hyperlink.Type = ExcelHyperLinkType.Url;
  hyperlink.Address = "http://www.syncfusion.com";
  hyperlink.ScreenTip = "To know more about Syncfusion products, go through this link.";

  //Creating a Hyperlink for e-mail
  IHyperLink hyperlink1 = sheet.HyperLinks.Add(sheet.Range["C7"]);
  hyperlink1.Type = ExcelHyperLinkType.Url;
  hyperlink1.Address = "mailto:Username@syncfusion.com";
  hyperlink1.ScreenTip = "Send Mail";

  //Creating a Hyperlink for Opening Files using type as File
  IHyperLink hyperlink2 = sheet.HyperLinks.Add(sheet.Range["C9"]);
  hyperlink2.Type = ExcelHyperLinkType.File;
  hyperlink2.Address = "C:/Program files";
  hyperlink2.ScreenTip = "File path";
  hyperlink2.TextToDisplay = "Hyperlink for files using File as type";

  //Creating a Hyperlink for Opening Files using type as Unc
  IHyperLink hyperlink3 = sheet.HyperLinks.Add(sheet.Range["C11"]);
  hyperlink3.Type = ExcelHyperLinkType.Unc;
  hyperlink3.Address = "C:/Documents and Settings";
  hyperlink3.ScreenTip = "Click here for files";
  hyperlink3.TextToDisplay = "Hyperlink for files using Unc as type";

  //Creating a Hyperlink to another cell using type as Workbook
  IHyperLink hyperlink4 = sheet.HyperLinks.Add(sheet.Range["C13"]);
  hyperlink4.Type = ExcelHyperLinkType.Workbook;
  hyperlink4.Address = "Sheet1!A15";
  hyperlink4.ScreenTip = "Click here";
  hyperlink4.TextToDisplay = "Hyperlink to cell A15";

  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Creating a Hyperlink for a Website
  Dim hyperlink As IHyperLink = sheet.HyperLinks.Add(sheet.Range("C5"))
  hyperlink.Type = ExcelHyperLinkType.Url
  hyperlink.Address = "http://www.Syncfusion.com"
  hyperlink.ScreenTip = "To know more about Syncfusion products, go through this link."

  'Creating a Hyperlink for e-mail
  Dim hyperlink1 As IHyperLink = sheet.HyperLinks.Add(sheet.Range("C7"))
  hyperlink1.Type = ExcelHyperLinkType.Url
  hyperlink1.Address = "mailto:Username@syncfusion.com"
  hyperlink1.ScreenTip = "Send Mail"

  'Creating a Hyperlink for Opening Files using type as File
  Dim hyperlink2 As IHyperLink = sheet.HyperLinks.Add(sheet.Range("C9"))
  hyperlink2.Type = ExcelHyperLinkType.File
  hyperlink2.Address = "C:/Program files"
  hyperlink2.ScreenTip = "File path"
  hyperlink2.TextToDisplay = "Hyperlink for files using File as type"

  'Creating a Hyperlink for Opening Files using type as Unc
  Dim hyperlink3 As IHyperLink = sheet.HyperLinks.Add(sheet.Range("C11"))
  hyperlink3.Type = ExcelHyperLinkType.Unc
  hyperlink3.Address = "C:/Documents and Settings"
  hyperlink3.ScreenTip = "Click here for files"
  hyperlink3.TextToDisplay = "Hyperlink for files using Unc as type"

  'Creating a Hyperlink to another cell using type as Workbook
  Dim hyperlink4 As IHyperLink = sheet.HyperLinks.Add(sheet.Range("C13"))
  hyperlink4.Type = ExcelHyperLinkType.Workbook
  hyperlink4.Address = "Sheet1!A15"
  hyperlink4.ScreenTip = "Click here"
  hyperlink4.TextToDisplay = "Hyperlink to cell A15"

  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to add hyperlinks in C# is present on this GitHub page.

The properties of existing hyperlink can be modified by accessing it through the IRange instance.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Modifying hyperlink’s text to display
  IHyperLink hyperlink = worksheet.Range["C5"].Hyperlinks[0];
  hyperlink.TextToDisplay = "Syncfusion";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Modifying hyperlink’s text to display
  IHyperLink hyperlink = sheet.Range["C5"].Hyperlinks[0];
  hyperlink.TextToDisplay = "Syncfusion";

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Modifying hyperlink’s text to display
  Dim hyperlink As IHyperLink = sheet.Range("C5").Hyperlinks(0)
  hyperlink.TextToDisplay = "Syncfusion"

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to modify existing hyperlink in C# is present on this GitHub page.

Similarly, a hyperlink can also be removed from a range by accessing it through the IRange instance.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Removing Hyperlink from Range "C7"
  worksheet.Range["C7"].Hyperlinks.RemoveAt(0);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Removing Hyperlink from Range "C7"
  sheet.Range["C7"].Hyperlinks.RemoveAt(0);

  workbook.Version = ExcelVersion.Xlsx;
  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Removing Hyperlink from Range "C7"
  sheet.Range("C7").Hyperlinks.RemoveAt(0)

  workbook.Version = ExcelVersion.Xlsx
  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to remove existing hyperlink in C# is present on this GitHub page.

Adding Hyperlinks to Shapes

Hyperlink can be added to the following shapes.

  • Picture
  • AutoShape
  • TextBox

The following code example illustrates how to insert hyperlinks to shapes.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Adding hyperlink to TextBox 
  ITextBox textBox = worksheet.TextBoxes.AddTextBox(1, 1, 100, 100);
  IHyperLink hyperlink = worksheet.HyperLinks.Add((textBox as IShape), ExcelHyperLinkType.Url, "http://www.Syncfusion.com", "click here");

  //Adding hyperlink to AutoShape
  IShape autoShape = worksheet.Shapes.AddAutoShapes(AutoShapeType.Cloud, 1, 1, 100, 100);
  hyperlink = worksheet.HyperLinks.Add(autoShape, ExcelHyperLinkType.Url, "mailto:Username@syncfusion.com", "Send Mail");

  //Adding hyperlink to picture
  IPictureShape picture = worksheet.Pictures.AddPictureAsLink(5, 5, 10, 10, "Image.png");
  hyperlink = worksheet.HyperLinks.Add(picture);
  hyperlink.Type = ExcelHyperLinkType.Unc;
  hyperlink.Address = "C:/Documents and Settings";
  hyperlink.ScreenTip = "Click here for files";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");

  //Adding hyperlink to TextBox 
  IWorksheet sheet = workbook.Worksheets[0];
  ITextBox textBox = sheet.TextBoxes.AddTextBox(1, 1, 100, 100);
  IHyperLink hyperlink = sheet.HyperLinks.Add((textBox as IShape), ExcelHyperLinkType.Url, "http://www.Syncfusion.com", "click here");

  //Adding hyperlink to AutoShape
  sheet = workbook.Worksheets[1];
  IShape autoShape = sheet.Shapes.AddAutoShapes(AutoShapeType.Cloud, 1, 1, 100, 100);
  hyperlink = sheet.HyperLinks.Add(autoShape, ExcelHyperLinkType.Url, "mailto:Username@syncfusion.com", "Send Mail");

  //Adding hyperlink to picture
  sheet = workbook.Worksheets[2];
  IPictureShape picture = sheet.Pictures.AddPicture("Image.png");
  hyperlink = sheet.HyperLinks.Add(picture);
  hyperlink.Type = ExcelHyperLinkType.Unc;
  hyperlink.Address = "C:/Documents and Settings";
  hyperlink.ScreenTip = "Click here for files";

  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")

  'Text box 
  Dim sheet As IWorksheet = workbook.Worksheets(0)
  Dim textBox As ITextBox = sheet.TextBoxes.AddTextBox(1, 1, 100, 100)
  Dim hyperlink As IHyperLink = sheet.HyperLinks.Add(TryCast(textBox, IShape), ExcelHyperLinkType.Url, "http://www.Syncfusion.com", "click here")

  'AutoShapes 
  sheet = workbook.Worksheets(1)
  Dim autoShape As IShape = sheet.Shapes.AddAutoShapes(AutoShapeType.Cloud, 1, 1, 100, 100)
  hyperlink = sheet.HyperLinks.Add(autoShape, ExcelHyperLinkType.Url, "mailto:Username@syncfusion.com", "Send Mail")

  'Pictures 
  sheet = workbook.Worksheets(2)
  Dim picture As IPictureShape = sheet.Pictures.AddPicture("Image.png")
  hyperlink = sheet.HyperLinks.Add(picture)
  hyperlink.Type = ExcelHyperLinkType.Unc
  hyperlink.Address = "C:/Documents and Settings"
  hyperlink.ScreenTip = "Click here for files"

  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to add hyperlink to shape in C# is present on this GitHub page.

Modifying Hyperlinks on Shapes

Properties of existing hyperlink can be modified by accessing it through IWorksheet instance or IShape instance.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Modifying hyperlink’s screen tip through IWorksheet instance
  IHyperLink hyperlink = worksheet.HyperLinks[0];
  hyperlink.ScreenTip = "Syncfusion";

  //Modifying hyperlink’s screen tip through IShape instance
  hyperlink = worksheet.Shapes[0].Hyperlink;
  hyperlink.ScreenTip = "Syncfusion";

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Modifying hyperlink’s screen tip through IWorksheet instance
  IHyperLink hyperlink = sheet.HyperLinks[0];
  hyperlink.ScreenTip = "Syncfusion";

  //Modifying hyperlink’s screen tip through IShape instance
  hyperlink = sheet.Shapes[0].Hyperlink;
  hyperlink.ScreenTip = "Syncfusion";

  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Modifying hyperlink’s screen tip through IWorksheet instance
  Dim hyperlink As IHyperLink = sheet.HyperLinks(0)
  hyperlink.ScreenTip = "Syncfusion"

  'Modifying hyperlink’s screen tip through IShape instance
  hyperlink = sheet.Shapes(0).Hyperlink
  hyperlink.ScreenTip = "Syncfusion"

  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to modify shape hyperlink in C# is present on this GitHub page.

Removing Hyperlinks from Shapes

The following code snippet explains how to remove hyperlink of shape.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  FileStream fileStream = new FileStream("Sample.xlsx", FileMode.Open, FileAccess.Read);
  IWorkbook workbook = application.Workbooks.Open(fileStream);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Removing hyperlink from sheet with Index
  worksheet.HyperLinks.RemoveAt(0);

  //Saving the workbook as stream
  FileStream stream = new FileStream("Hyperlink.xlsx", FileMode.Create, FileAccess.ReadWrite);
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Open("Sample.xlsx");
  IWorksheet sheet = workbook.Worksheets[0];

  //Removing hyperlink from sheet with Index
  sheet.HyperLinks.RemoveAt(0);

  workbook.SaveAs("Hyperlink.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Open("Sample.xlsx")
  Dim sheet As IWorksheet = workbook.Worksheets(0)

  'Removing Hyperlink from sheet with Index
  sheet.HyperLinks.RemoveAt(0)

  workbook.SaveAs("Hyperlink.xlsx")
End Using

A complete working example to remove shape hyperlink in C# is present on this GitHub page.

List of APIs under IRange

Cell Address

The following code snippet explains the usage of Address, AddressGlobal, AddressLocal, AddressR1C1, AddressR1C1Local properties.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Address
  string address = worksheet.Range[3, 4].Address;

  //Global Address
  string address_Global = worksheet.Range[3, 4].AddressGlobal;

  //Local Address
  string address_Local = worksheet.Range[3, 4].AddressLocal;

  //R1C1 Address
  string address_R1C1 = worksheet.Range[3, 4].AddressR1C1;

  //Local R1C1 Address
  string address_R1C1_Local = worksheet.Range[3, 4].AddressR1C1Local;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Address
  string address = worksheet.Range[3, 4].Address;

  //Global Address
  string address_Global = worksheet.Range[3, 4].AddressGlobal;

  //Local Address
  string address_Local = worksheet.Range[3, 4].AddressLocal;

  //R1C1 Address
  string address_R1C1 = worksheet.Range[3, 4].AddressR1C1;

  //Local R1C1 Address
  string address_R1C1_Local = worksheet.Range[3, 4].AddressR1C1Local;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Address
  Dim address As String = worksheet.Range(3, 4).Address

  'Global Address
  Dim address_Global As String = worksheet.Range(3, 4).AddressGlobal

  'Local Address
  Dim address_Local As String = worksheet.Range(3, 4).AddressLocal

  'R1C1 Address
  Dim address_R1C1 As String = worksheet.Range(3, 4).AddressR1C1

  'Local R1C1 Address
  Dim address_R1C1_Local As String = worksheet.Range(3, 4).AddressR1C1Local

  workbook.SaveAs("Output.xlsx")
End Using

Boolean

As the name says, Boolean property gets or sets the boolean value in a worksheet range. The following code snippet explains this.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Boolean - True
  worksheet.Range["B3"].Boolean = true;
  bool b3 = worksheet.Range["B3"].Boolean;

  //Boolean - False
  worksheet.Range["B4"].Boolean = false;
  bool b4 = worksheet.Range["B4"].Boolean;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Boolean - True
  worksheet.Range["B3"].Boolean = true;
  bool b3 = worksheet.Range["B3"].Boolean;

  //Boolean - False
  worksheet.Range["B4"].Boolean = false;
  bool b4 = worksheet.Range["B4"].Boolean;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Boolean - True
  worksheet.Range("B3").Boolean = True
  Dim b3 As Boolean = worksheet.Range("B3").Boolean

  'Boolean - False
  worksheet.Range("B4").Boolean = False
  Dim b4 As Boolean = worksheet.Range("B4").Boolean

  workbook.SaveAs("Output.xlsx")
End Using

Borders

The following code snippet explains how to set border styles for a worksheet range using Borders property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set text
  worksheet.Range["C2"].Text = "Sample";

  //Set borders
  IBorders borders = worksheet.Range["C2"].Borders;

  //Set border color
  borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
  borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;

  //Set line style
  borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
  borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set text
  worksheet.Range["C2"].Text = "Sample";

  //Set borders
  IBorders borders = worksheet.Range["C2"].Borders;

  //Set border color
  borders[ExcelBordersIndex.EdgeTop].Color = ExcelKnownColors.Red;
  borders[ExcelBordersIndex.EdgeBottom].Color = ExcelKnownColors.Blue;

  //Set line style
  borders[ExcelBordersIndex.EdgeTop].LineStyle = ExcelLineStyle.Thick;
  borders[ExcelBordersIndex.EdgeBottom].LineStyle = ExcelLineStyle.Thick;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set text
  worksheet.Range("C2").Text = "Sample"

  'Set borders
  Dim borders As IBorders = worksheet.Range("C2").Borders

  'Set border color
  borders(ExcelBordersIndex.EdgeTop).Color = ExcelKnownColors.Red
  borders(ExcelBordersIndex.EdgeBottom).Color = ExcelKnownColors.Blue

  'Set line style
  borders(ExcelBordersIndex.EdgeTop).LineStyle = ExcelLineStyle.Thick
  borders(ExcelBordersIndex.EdgeBottom).LineStyle = ExcelLineStyle.Thick

  workbook.SaveAs("Output.xlsx")
End Using

Built-In-Style

The following code snippet explains how to add BuiltInStyle for a worksheet range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set text
  worksheet.Range["C2"].Text = "Sample";

  //Set built in style
  worksheet.Range["C2"].BuiltInStyle = BuiltInStyles.Accent3;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set text
  worksheet.Range["C2"].Text = "Sample";

  //Set built in style
  worksheet.Range["C2"].BuiltInStyle = BuiltInStyles.Accent3;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set text
  worksheet.Range("C2").Text = "Sample"

  'Set built in style
  worksheet.Range("C2").BuiltInStyle = BuiltInStyles.Accent3

  workbook.SaveAs("Output.xlsx")
End Using

Calculated Value

CalculatedValue is the evaluated value of the formula. The following code snippet explains how to get the CalculatedValue of the formula.

NOTE

It is mandatory to enable sheet calculations i.e., worksheet.EnableSheetCalculations(); before accessing the CalculatedValue. Else, CalculatedValue will be returned as null.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["A2"].Number = 20;

  //Set formula
  worksheet.Range["A3"].Formula = "=SUM(A1:A2)";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the calculated value
  string value = worksheet.Range["A3"].CalculatedValue;

  //Disable sheet calculations
  worksheet.DisableSheetCalculations();

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["A2"].Number = 20;

  //Set formula
  worksheet.Range["A3"].Formula = "=SUM(A1:A2)";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the calculated value
  string value = worksheet.Range["A3"].CalculatedValue;

  //Disable sheet calculations
  worksheet.DisableSheetCalculations();

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A1").Number = 10
  worksheet.Range("A2").Number = 20

  'Set formula
  worksheet.Range("A3").Formula = "=SUM(A1:A2)"

  'Enable sheet calculations
  worksheet.EnableSheetCalculations()

  'Get the calculated value
  Dim value As String = worksheet.Range("A3").CalculatedValue

  'Disable sheet calculations
  worksheet.DisableSheetCalculations()

  workbook.SaveAs("Output.xlsx")
End Using

Cells

Cells property maintains the collection of cells in a worksheet range. The following code snippet explains how to access this property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Range of cells
  IRange[] cells = worksheet.Range["A1:E5"].Cells;

  foreach (IRange cell in cells)
  {
    //Assign value
    cell.Text = cell.AddressLocal;
  }

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Range of cells
  IRange[] cells = worksheet.Range["A1:E5"].Cells;

  foreach (IRange cell in cells)
  {
    //Assign value
    cell.Text = cell.AddressLocal;
  }

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Range of cells
  Dim cells() As IRange = worksheet.Range("A1:E5").Cells

  For Each cell As IRange In cells
    'Assign value
    cell.Text = cell.AddressLocal
  Next

  workbook.SaveAs("Output.xlsx")
End Using

Cell Style Name

CellStyleName represents the name of the style of worksheet range/cell. The default value is Normal.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add style
  IStyle style = workbook.Styles.Add("CustomStyle");

  //Set color
  style.ColorIndex = ExcelKnownColors.Red;

  //Set style
  worksheet.Range["C2"].CellStyle = style;

  //Get the cell style name
  string cellStyleName = worksheet.Range["C2"].CellStyleName;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add style
  IStyle style = workbook.Styles.Add("CustomStyle");

  //Set color
  style.ColorIndex = ExcelKnownColors.Red;

  //Set style
  worksheet.Range["C2"].CellStyle = style;

  //Get the cell style name
  string cellStyleName = worksheet.Range["C2"].CellStyleName;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add style
  Dim style As IStyle = workbook.Styles.Add("CustomStyle")

  'Set color
  style.ColorIndex = ExcelKnownColors.Red

  'Set style
  worksheet.Range("C2").CellStyle = style

  'Get the cell style name
  Dim cellStyleName As String = worksheet.Range("C2").CellStyleName

  workbook.SaveAs("Output.xlsx")
End Using

Column

Column property gets the column index of first column in worksheet range, which is one-index based.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get first column in the range
  int firstColumn = worksheet.Range["E1:R3"].Column;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Get first column in the range
  int firstColumn = worksheet.Range["E1:R3"].Column;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Get first column in the range
  Dim firstColumn As Integer = worksheet.Range("E1:R3").Column

  workbook.SaveAs("Output.xlsx")
End Using

Columns

Columns property maintains the collection of columns in a worksheet range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Columns
  IRange[] columns = worksheet.Range["A1:E5"].Columns;

  foreach (IRange column in columns)
  {
    //Assign value
    column.Text = column.AddressLocal;
  }

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Columns
  IRange[] columns = worksheet.Range["A1:E5"].Columns;

  foreach (IRange column in columns)
  {
    //Assign value
    column.Text = column.AddressLocal;
  }

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Columns
  Dim columns() As IRange = worksheet.Range("A1:E5").Columns

  For Each column As IRange In columns
    'Assign value
    column.Text = column.AddressLocal
  Next

  workbook.SaveAs("Output.xlsx")
End Using

Comment

To know in detail about Comment, please navigate to https://help.syncfusion.com/file-formats/xlsio/working-with-drawing-objects#comments.

Conditional Formats

To know in detail about Conditional Formats, please navigate to https://help.syncfusion.com/file-formats/xlsio/working-with-conditional-formatting.

Count

Count property returns the number of cells in that particular worksheet range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Number of cells
  int count = worksheet.Range["A1:E5"].Count;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Number of cells
  int count = worksheet.Range["A1:E5"].Count;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Number of cells
  Dim count As Integer = worksheet.Range("A1:E5").Count

  workbook.SaveAs("Output.xlsx")
End Using

Data Validation

To know in detail about Data Validation, please navigate to https://help.syncfusion.com/file-formats/xlsio/working-with-data-validation.

End

End property returns the last cell in the particular worksheet range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the range
  IRange lastCell = worksheet.Range["A1:E5"].End;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the range
  IRange lastCell = worksheet.Range["A1:E5"].End;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Last cell in the range
  Dim lastCell As IRange = worksheet.Range("A1:E5").End

  workbook.SaveAs("Output.xlsx")
End Using

Entire Column

EntireColumn, as the name says gets the entire column of the particular range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the entire column
  IRange lastCell = worksheet.Range["A1"].EntireColumn.End;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the entire column
  IRange lastCell = worksheet.Range["A1"].EntireColumn.End;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Last cell in the entire column
  Dim lastCell As IRange = worksheet.Range("A1").EntireColumn.End

  workbook.SaveAs("Output.xlsx")
End Using

NOTE

Using EntireColumn property excessively leads to time consumption and affects the performance.

Entire Row

EntireRow, as the name says gets the entire row of the particular range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the entire row
  IRange lastCell = worksheet.Range["A1"].EntireRow.End;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Last cell in the entire row
  IRange lastCell = worksheet.Range["A1"].EntireRow.End;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Last cell in the entire row
  Dim lastCell As IRange = worksheet.Range("A1").EntireRow.End

  workbook.SaveAs("Output.xlsx")
End Using

Formula

Formula property gets or sets the formula in specified range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["B1"].Number = 20;

  //Set formula
  worksheet.Range["C1"].Formula = "=SUM(A1:B1)";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the claculated value of formula
  string value = worksheet.Range["C1"].CalculatedValue;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["B1"].Number = 20;

  //Set formula
  worksheet.Range["C1"].Formula = "=SUM(A1:B1)";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the claculated value of formula
  string value = worksheet.Range["C1"].CalculatedValue;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A1").Number = 10
  worksheet.Range("B1").Number = 20

  'Set formula
  worksheet.Range("C1").Formula = "=SUM(A1:B1)"

  'Enable sheet calculations
  worksheet.EnableSheetCalculations()

  'Get the claculated value of formula
  Dim value As String = worksheet.Range("C1").CalculatedValue

  workbook.SaveAs("Output.xlsx")
End Using

Formula Array

To know in detail about FormulaArray, please navigate to https://help.syncfusion.com/file-formats/xlsio/working-with-formulas#array-of-formula

Formula Bool Value

FormulaBoolValue gets the CalculatedValue of formula as boolean.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["B1"].Number = 20;

  //Set formula
  worksheet.Range["C1"].Formula = "=SUM(A1:B1)";

  //Set boolean
  worksheet.Range["D1"].Boolean = true;

  //Set formula
  worksheet.Range["E1"].Formula = "=D1";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the formula boolean value
  bool value_C1 = worksheet.Range["C1"].FormulaBoolValue;
  bool value_E1 = worksheet.Range["E1"].FormulaBoolValue;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["B1"].Number = 20;

  //Set formula
  worksheet.Range["C1"].Formula = "=SUM(A1:B1)";

  //Set boolean
  worksheet.Range["D1"].Boolean = true;

  //Set formula
  worksheet.Range["E1"].Formula = "=D1";                

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the formula boolean value
  bool value_C1 = worksheet.Range["C1"].FormulaBoolValue;
  bool value_E1 = worksheet.Range["E1"].FormulaBoolValue;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A1").Number = 10
  worksheet.Range("B1").Number = 20

  'Set formula
  worksheet.Range("C1").Formula = "=SUM(A1:B1)"

  'Set boolean
  worksheet.Range("D1").Boolean = True

  'Set formula
  worksheet.Range("E1").Formula = "=D1"

  'Enable sheet calculations
  worksheet.EnableSheetCalculations()

  'Get the formula boolean value
  Dim value_C1 As Boolean = worksheet.Range("C1").FormulaBoolValue
  Dim value_E1 As Boolean = worksheet.Range("E1").FormulaBoolValue

  workbook.SaveAs("Output.xlsx")
End Using

Has Boolean

HasBoolean returns whether the range has boolean value.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Boolean = true;
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["A3"].Value2 = true;

  //Get if the cell has boolean value
  bool hasBoolean_A1 = worksheet.Range["A1"].HasBoolean;
  bool hasBoolean_A2 = worksheet.Range["A2"].HasBoolean;
  bool hasBoolean_A3 = worksheet.Range["A3"].HasBoolean;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Boolean = true;
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["A3"].Value2 = true;

  //Get if the cell has boolean value
  bool hasBoolean_A1 = worksheet.Range["A1"].HasBoolean;
  bool hasBoolean_A2 = worksheet.Range["A2"].HasBoolean;
  bool hasBoolean_A3 = worksheet.Range["A3"].HasBoolean;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A1").Boolean = True
  worksheet.Range("A2").Number = 10
  worksheet.Range("A3").Value2 = True

  'Get if the cell has boolean value
  Dim hasBoolean_A1 As Boolean = worksheet.Range("A1").HasBoolean
  Dim hasBoolean_A2 As Boolean = worksheet.Range("A2").HasBoolean
  Dim hasBoolean_A3 As Boolean = worksheet.Range("A3").HasBoolean

  workbook.SaveAs("Output.xlsx")
End Using

Has DataValidation

The following code snippet explains the behavior of HasDataValidation property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Data Validation for Text Length
  IDataValidation txtLengthValidation = worksheet.Range["A3"].DataValidation;
  worksheet.Range["A1"].Text = "Enter the Text in A3";
  worksheet.Range["A1"].AutofitColumns();
  txtLengthValidation.AllowType = ExcelDataType.TextLength;
  txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
  txtLengthValidation.FirstFormula = "0";
  txtLengthValidation.SecondFormula = "5";

  bool validation_A1 = worksheet.Range["A1"].HasDataValidation;
  bool validation_A3 = worksheet.Range["A3"].HasDataValidation;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Data Validation for Text Length
  IDataValidation txtLengthValidation = worksheet.Range["A3"].DataValidation;
  worksheet.Range["A1"].Text = "Enter the Text in A3";
  worksheet.Range["A1"].AutofitColumns();
  txtLengthValidation.AllowType = ExcelDataType.TextLength;
  txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between;
  txtLengthValidation.FirstFormula = "0";
  txtLengthValidation.SecondFormula = "5";

  bool validation_A1 = worksheet.Range["A1"].HasDataValidation;
  bool validation_A3 = worksheet.Range["A3"].HasDataValidation;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Data Validation for Text Length
  Dim txtLengthValidation As IDataValidation = worksheet.Range("A3").DataValidation
  worksheet.Range("A1").Text = "Enter the Text in A3"
  worksheet.Range("A1").AutofitColumns()
  txtLengthValidation.AllowType = ExcelDataType.TextLength
  txtLengthValidation.CompareOperator = ExcelDataValidationComparisonOperator.Between
  txtLengthValidation.FirstFormula = "0"
  txtLengthValidation.SecondFormula = "5"

  workbook.SaveAs("Output.xlsx")
End Using

Has DateTime

The following code snippet explains the behavior of HasDateTime property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set value through Value property
  worksheet.Range["B1"].Value = "Hello World";

  //Set date time through DatTime property
  worksheet.Range["B2"].DateTime = DateTime.Now;

  //Set value through Value2 property
  worksheet.Range["B3"].Value2 = "Hello World";

  //Set date time through Value2 property
  worksheet.Range["B4"].Value2 = DateTime.Now;

  bool dateTime_B1 = worksheet.Range["B1"].HasDateTime;
  bool dateTime_B2 = worksheet.Range["B2"].HasDateTime;
  bool dateTime_B3 = worksheet.Range["B3"].HasDateTime;
  bool dateTime_B4 = worksheet.Range["B4"].HasDateTime;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set value through Value property
  worksheet.Range["B1"].Value = "Hello World";

  //Set date time through DatTime property
  worksheet.Range["B2"].DateTime = DateTime.Now;

  //Set value through Value2 property
  worksheet.Range["B3"].Value2 = "Hello World";

  //Set date time through Value2 property
  worksheet.Range["B4"].Value2 = DateTime.Now;

  bool dateTime_B1 = worksheet.Range["B1"].HasDateTime;
  bool dateTime_B2 = worksheet.Range["B2"].HasDateTime;
  bool dateTime_B3 = worksheet.Range["B3"].HasDateTime;
  bool dateTime_B4 = worksheet.Range["B4"].HasDateTime;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set value through Value property
  worksheet.Range("B1").Value = "Hello World"

  'Set date time through DatTime property
  worksheet.Range("B2").DateTime = DateTime.Now

  'Set value through Value2 property
  worksheet.Range("B3").Value2 = "Hello World"

  'Set date time through Value2 property
  worksheet.Range("B4").Value2 = DateTime.Now

  Dim dateTime_B1 As Boolean = worksheet.Range("B1").HasDateTime
  Dim dateTime_B2 As Boolean = worksheet.Range("B2").HasDateTime
  Dim dateTime_B3 As Boolean = worksheet.Range("B3").HasDateTime
  Dim dateTime_B4 As Boolean = worksheet.Range("B4").HasDateTime

  workbook.SaveAs("Output.xlsx")
End Using

Has External Formula

The following code snippet explains the behavior of HasExternalFormula property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set normal external formula
  worksheet.Range["C1"].Formula = "[One.xlsx]Sheet1!$A$1*5";

  //Set normal formula
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["B2"].Number = 20;
  worksheet.Range["C2"].Formula = "=SUM(A2:B2)";

  bool extFormula_C1 = worksheet.Range["C1"].HasExternalFormula;
  bool extFormula_C2 = worksheet.Range["C2"].HasExternalFormula;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set normal external formula
  worksheet.Range["C1"].Formula = "[One.xlsx]Sheet1!$A$1*5";

  //Set normal formula
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["B2"].Number = 20;
  worksheet.Range["C2"].Formula = "=SUM(A2:B2)";

  bool extFormula_C1 = worksheet.Range["C1"].HasExternalFormula;
  bool extFormula_C2 = worksheet.Range["C2"].HasExternalFormula;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set normal external formula
  worksheet.Range("C1").Formula = "[One.xlsx]Sheet1!$A$1*5"

  'Set normal formula
  worksheet.Range("A2").Number = 10
  worksheet.Range("B2").Number = 20
  worksheet.Range("C2").Formula = "=SUM(A2:B2)"

  Dim extFormula_C1 As Boolean = worksheet.Range("C1").HasExternalFormula
  Dim extFormula_C2 As Boolean = worksheet.Range("C2").HasExternalFormula

  workbook.SaveAs("Output.xlsx")
End Using

Has Formula

The following code snippet explains the behavior of HasFormula property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["B2"].Number = 20;

  //Set formula
  worksheet.Range["C2"].Formula = "=SUM(A2:B2)";

  bool formula_A2 = worksheet.Range["A2"].HasFormula;
  bool formula_C2 = worksheet.Range["C2"].HasFormula;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A2"].Number = 10;
  worksheet.Range["B2"].Number = 20;

  //Set formula
  worksheet.Range["C2"].Formula = "=SUM(A2:B2)";

  bool formula_A2 = worksheet.Range["A2"].HasFormula;
  bool formula_C2 = worksheet.Range["C2"].HasFormula;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A2").Number = 10
  worksheet.Range("B2").Number = 20

  'Set formula
  worksheet.Range("C2").Formula = "=SUM(A2:B2)"

  Dim formula_A2 As Boolean = worksheet.Range("A2").HasFormula
  Dim formula_C2 As Boolean = worksheet.Range("C2").HasFormula

  workbook.SaveAs("Output.xlsx")
End Using

Has Formula Array

The following code snippet explains the behavior of HasFormulaArray property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Assign array formula
  worksheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";

  //Adding a named range for the range A1 to D1
  worksheet.Names.Add("ArrayRange", worksheet.Range["A1:D1"]);

  //Assign formula array with named range
  worksheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";

  bool formulaArray = worksheet.Range["A1"].HasFormulaArray;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Assign array formula
  worksheet.Range["A1:D1"].FormulaArray = "{1,2,3,4}";

  //Adding a named range for the range A1 to D1
  worksheet.Names.Add("ArrayRange", worksheet.Range["A1:D1"]);

  //Assign formula array with named range
  worksheet.Range["A2:D2"].FormulaArray = "ArrayRange+100";

  bool formulaArray = worksheet.Range["A1"].HasFormulaArray;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Assign array formula
  worksheet.Range("A1:D1").FormulaArray = "{1,2,3,4}"

  'Adding a named range for the range A1 to D1
  worksheet.Names.Add("ArrayRange", worksheet.Range("A1:D1"))

  'Assign formula array with named range
  worksheet.Range("A2:D2").FormulaArray = "ArrayRange+100"

  Dim formulaArray As Boolean = worksheet.Range("A1").HasFormulaArray

  workbook.SaveAs("Output.xlsx")
End Using

Has Formula Bool Value

The following code snippet explains the behavior of HasFormulaBoolValue property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set boolean
  worksheet.Range["D1"].Boolean = true;

  //Set formula
  worksheet.Range["E1"].Formula = "=D1";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the formula boolean value
  bool value_E1 = worksheet.Range["E1"].FormulaBoolValue;

  //Has formula boolean value
  bool hasValue_E1 = worksheet.Range["E1"].HasFormulaBoolValue;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set boolean
  worksheet.Range["D1"].Boolean = true;

  //Set formula
  worksheet.Range["E1"].Formula = "=D1";

  //Enable sheet calculations
  worksheet.EnableSheetCalculations();

  //Get the formula boolean value
  bool value_E1 = worksheet.Range["E1"].FormulaBoolValue;

  //Has formula boolean value
  bool hasValue_E1 = worksheet.Range["E1"].HasFormulaBoolValue;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set boolean
  worksheet.Range("D1").Boolean = True

  'Set formula
  worksheet.Range("E1").Formula = "=D1"

  'Enable sheet calculations
  worksheet.EnableSheetCalculations()

  'Get the formula boolean value
  Dim value_E1 As Boolean = worksheet.Range("E1").FormulaBoolValue

  'Has formula boolean value
  Dim hasValue_E1 As Boolean = worksheet.Range("E1").HasFormulaBoolValue

  workbook.SaveAs("Output.xlsx")
End Using

Has Number

HasNumber property determines whether the cell has number in it.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["A2"].Text = "Sample";

  //Has number
  bool hasNumber_A1 = worksheet.Range["A1"].HasNumber;
  bool hasNumber_A2 = worksheet.Range["A2"].HasNumber;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Set values
  worksheet.Range["A1"].Number = 10;
  worksheet.Range["A2"].Text = "Sample";

  //Has number
  bool hasNumber_A1 = worksheet.Range["A1"].HasNumber;
  bool hasNumber_A2 = worksheet.Range["A2"].HasNumber;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Set values
  worksheet.Range("A1").Number = 10
  worksheet.Range("A2").Text = "Sample"

  'Has number
  Dim hasNumber_A1 As Boolean = worksheet.Range("A1").HasNumber
  Dim hasNumber_A2 As Boolean = worksheet.Range("A2").HasNumber

  workbook.SaveAs("Output.xlsx")
End Using

Has RichText

HasRichText property determines whether the cell has rich-text in it.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add Text
  IRange range = worksheet.Range["A1"];
  range.Text = "RichText";
  IRichTextString richText = range.RichText;

  //Formatting first 4 characters
  IFont redFont = workbook.CreateFont();
  redFont.Bold = true;
  redFont.Italic = true;
  redFont.RGBColor = Color.Red;
  richText.SetFont(0, 3, redFont);

  //Formatting last 4 characters
  IFont blueFont = workbook.CreateFont();
  blueFont.Bold = true;
  blueFont.Italic = true;
  blueFont.RGBColor = Color.Blue;
  richText.SetFont(4, 7, blueFont);

  //Has RichText
  bool hasRichText = worksheet.Range["A1"].HasRichText;
  bool hasNumber_A2 = worksheet.Range["A2"].HasNumber;

  //Saving the Excel to the MemoryStream 
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  //Set the position as '0'
  stream.Position = 0;

  //Download the PDF file in the browser
  FileStreamResult fileStreamResult = new FileStreamResult(stream, "application/excel");
  fileStreamResult.FileDownloadName = "Output.xlsx";
  return fileStreamResult;
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Xlsx;
  IWorkbook workbook = application.Workbooks.Create(1);
  IWorksheet worksheet = workbook.Worksheets[0];

  //Add Text
  IRange range = worksheet.Range["A1"];
  range.Text = "RichText";
  IRichTextString richText = range.RichText;

  //Formatting first 4 characters
  IFont redFont = workbook.CreateFont();
  redFont.Bold = true;
  redFont.Italic = true;
  redFont.RGBColor = Color.Red;
  richText.SetFont(0, 3, redFont);

  //Formatting last 4 characters
  IFont blueFont = workbook.CreateFont();
  blueFont.Bold = true;
  blueFont.Italic = true;
  blueFont.RGBColor = Color.Blue;
  richText.SetFont(4, 7, blueFont);

  //Has RichText
  bool hasRichText = worksheet.Range["A1"].HasRichText;

  workbook.SaveAs("Output.xlsx");
}
Using excelEngine As ExcelEngine = New ExcelEngine()
  Dim application As IApplication = excelEngine.Excel
  application.DefaultVersion = ExcelVersion.Xlsx
  Dim workbook As IWorkbook = application.Workbooks.Create(1)
  Dim worksheet As IWorksheet = workbook.Worksheets(0)

  'Add Text
  Dim range As IRange = worksheet.Range("A1")
  range.Text = "RichText"
  Dim richText As IRichTextString = range.RichText

  'Formatting first 4 characters
  Dim redFont As IFont = workbook.CreateFont()
  redFont.Bold = True
  redFont.Italic = True
  redFont.RGBColor = Color.Red
  richText.SetFont(0, 3, redFont)

  'Formatting last 4 characters
  Dim blueFont As IFont = workbook.CreateFont()
  blueFont.Bold = True
  blueFont.Italic = True
  blueFont.RGBColor = Color.Blue
  richText.SetFont(4, 7, blueFont)

  'Has RichText
  Dim hasRichText As Boolean = worksheet.Range("A1").HasRichText

  workbook.SaveAs("Output.xlsx")
End Using

To know in detail about RichText, please navigate to https://help.syncfusion.com/file-formats/xlsio/working-with-cell-or-range-formatting#rich-text-formatting.