Worksheet Cells Manipulation

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 ranges.

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.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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)";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Range";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
}

TIPS

You can 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.Excel2013;

  //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.Excel2013

  '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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //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";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Range";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //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.Excel2013;

  //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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
}

Accessing Discontinuous Ranges

You can access discontinuous ranges and add them to the RangesCollection. You can modify the contents or applying formatting of discontinuous range through RangeCollection instance.

Following code snippet illustrates how to access discontinuous range.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Range";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
}

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. You can prefer IMigrantRange instead of IRange while writing large amount of data which is an optimal way.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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";
	}
  }

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Range";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
}

Accessing used range of a Worksheet

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

NOTE

By default, XlsIO considers a cell as used, even if there exists some formatting alone. You can disable this behavior, and make XlsIO consider a cell as used, only when there exists data, by using the UsedRangeIncludesFormatting property.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Range";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
    Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Range.xlsx", "application/msexcel", stream);
  }
}

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;
  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
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Precedents";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  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;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FormulaExcel.xlsx");
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Precedents.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Precedents.xlsx", "application/msexcel", stream);
  }
}

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

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  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
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Dependents";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  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;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FormulaExcel.xlsx");
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Dependents.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Dependents.xlsx", "application/msexcel", stream);
  }
}

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;
  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
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "DirectPrecedents";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  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;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FormulaExcel.xlsx");
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("DirectPrecedents.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("DirectPrecedents.xlsx", "application/msexcel", stream);
  }
}

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;
  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
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "DirectDependents";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  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;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.FormulaExcel.xlsx");
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("DirectDependents.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("DirectDependents.xlsx", "application/msexcel", stream);
  }
}

Clearing a Cell Content

You can delete everything in the cell, or just remove the formatting, contents, comments. The following code example illustrates how to clear a range along with its formatting.

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("ClearRange.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "ClearRange";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  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;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

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

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("ClearRange.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("ClearRange.xlsx", "application/msexcel", stream);
  }
}

Copy or Move a Range

You can copy a range of cells to another range using CopyTo method. You can also copy all the formats or only specific formats using ExcelCopyRangeOptions options.

Following code example illustrates how to copy a range of cells from the source to destination.

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("CopyRange.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "CopyRange";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "CopyRange";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("CopyRange.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("CopyRange.xlsx", "application/msexcel", stream);
  }
}

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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("MoveRange.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "MoveRange";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("MoveRange.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("MoveRange.xlsx", "application/msexcel", stream);
  }
}

You can copy a range 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("PasteLink.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "PasteLink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("ClearRange.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("ClearRange.xlsx", "application/msexcel", stream);
  }
}

Skip Blanks While Copying

Blank cells can be skipped while copying from source to destination range by setting the parameter skip blanks to TRUE.

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

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: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
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker. 
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile openFile = await openPicker.PickSingleFileAsync();
  
  //Opens the workbook. 
  IWorkbook workbook = await application.Workbooks.OpenAsync(openFile);
  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);
  
  //Initializes FileSavePicker.
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "CreateSpreadsheet";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });
  
  //Creates a storage file from FileSavePicker.
  StorageFile storageFile = await savePicker.PickSaveFileAsync();
  
  //Saves changes to the specified storage file.
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  string basePath = _hostingEnvironment.WebRootPath + @"\XlsIO\Sample.xlsx";              
  FileStream sampleFile = new FileStream(basePath, 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.Excel2013;
  workbook.SaveAs(stream);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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 as stream
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);
  workbook.Close();

  //Save the document as file and view the saved document
  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.
  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("SkipBlanks.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("SkipBlanks.xlsx", "application/msexcel", stream);
  }
}

Find and Replace

You can perform find and replace text and numbers in workbook or worksheet using XlsIO. Also, XlsIO provides the following options:

  • To search for data in formulas, values or comments.
  • To 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.

You can find all the occurrences of a text in worksheet by using FindAll method. To know more about Find and Replace, please refer IWorksheet in the API documentation section.
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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Find.xlsx")
End Using
ExcelEngine excelEngine = new ExcelEngine();
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Find";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Find.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Find.xlsx", "application/msexcel", stream);
  }
}

You can replace a text with another text with the help of Replace method which searches for text you’d like to change. You can replace a string, 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Replace.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Replace";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Replace.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Replace.xlsx", "application/msexcel", stream);
  }
}

Data Sorting

You can sort a range of cells based on data in one or more columns. You can perform sorting based on the following:

  • 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Sort.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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();

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Sort";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
}

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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Sort.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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.FromArgb(255, 255, 0, 0);

  //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.FromArgb(255, 0, 128, 0);

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Sort";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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 = Syncfusion.Drawing.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 = Syncfusion.Drawing.Color.Green;

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
}

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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Sort.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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.FromArgb(255, 255, 0, 0);

  //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.FromArgb(255, 0, 128, 0);

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Sort";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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 = Syncfusion.Drawing.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 = Syncfusion.Drawing.Color.Green;

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Sort.xlsx", "application/msexcel", stream);
  }
}

Data Filtering

Using AutoFilters, you can filter data to enable quick and easy way to find and work with a subset of data in a range of cells. When you filter data, entire rows are hidden if values in one or more columns don’t 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

Custom Filter

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

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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;

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(file);
  file.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

Combination Filter

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

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

Dynamic Filter

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

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

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 snippets 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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.FromArgb(255, 255, 0, 0), ExcelColorFilterType.CellColor);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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];

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

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

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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.FromArgb(255, 255, 0, 0), ExcelColorFilterType.FontColor);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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];

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

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, we can easily filter the data that has only a specific Icon in it.

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Filter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Filter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

Advanced Filter

Advanced Filter can be used to perform more complex filtering other than basic filters. You can filter a data 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;
  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.Excel2013;
  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.Excel2013
  workbook.SaveAs("AdvancedFilter.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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);

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "AdvancedFilter";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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];

  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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Filter.xlsx", "application/msexcel", stream);
  }
}

Accessing Filter

We can access the filter and its criteria, based on its column index. Following code snippets illustrates how to apply access different types of filters using XlsIO.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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;
  }

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Output";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  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);
	  Syncfusion.Drawing.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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android and iOS platforms. Please refer xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Output.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Output.xlsx", "application/msexcel", stream);
  }
}

You can create hyperlink 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.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}

You can modify the properties of existing hyperlink by accessing the Hyperlinks collection of the IRange instance.

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Hyperlink.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}

You can remove a hyperlink from a range by accessing the Hyperlinks collection of the IRange instance.

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.Excel2013;
  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.Excel2013
  workbook.SaveAs("Hyperlink.xlsx")
End Using
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
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.Excel2013;
  workbook.SaveAs(stream);
  stream.Dispose();
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Saving the workbook as stream
  MemoryStream stream = new MemoryStream();
  workbook.Version = ExcelVersion.Excel2013;
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}

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.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  workbook.Version = ExcelVersion.Excel2013;
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}

Modifying Hyperlinks on Shapes

Properties of existing hyperlink can be modified by accessing either the Hyperlinks collection of the IWorksheet instance or Hyperlink property in respective IShape instance.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  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";

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  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
  MemoryStream stream = new MemoryStream();
  workbook.SaveAs(stream);

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}

Removing Hyperlinks from Shapes

Hyperlinks from shapes can be removed by accessing Hyperlinks collection of the worksheet instance.

using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013
  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
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;

  //Instantiates the File Picker
  FileOpenPicker openPicker = new FileOpenPicker();
  openPicker.SuggestedStartLocation = PickerLocationId.Desktop;
  openPicker.FileTypeFilter.Add(".xlsx");
  openPicker.FileTypeFilter.Add(".xls");
  StorageFile file = await openPicker.PickSingleFileAsync();

  //Opens the workbook
  IWorkbook workbook = await application.Workbooks.OpenAsync(file);
  IWorksheet worksheet = workbook.Worksheets[0];

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

  //Initializes FileSavePicker
  FileSavePicker savePicker = new FileSavePicker();
  savePicker.SuggestedStartLocation = PickerLocationId.Desktop;
  savePicker.SuggestedFileName = "Hyperlink";
  savePicker.FileTypeChoices.Add("Excel Files", new List<string>() { ".xlsx" });

  //Creates a storage file from FileSavePicker
  StorageFile storageFile = await savePicker.PickSaveFileAsync();

  //Saves changes to the specified storage file
  await workbook.SaveAsAsync(storageFile);
}
using (ExcelEngine excelEngine = new ExcelEngine())
{
  IApplication application = excelEngine.Excel;
  application.DefaultVersion = ExcelVersion.Excel2013;
  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.Excel2013;

  //"App" is the class of Portable project
  Assembly assembly = typeof(App).GetTypeInfo().Assembly;
  Stream inputStream = assembly.GetManifestResourceStream("SampleBrowser.XlsIO.Samples.Template.Sample.xlsx");
  IWorkbook workbook = application.Workbooks.Open(inputStream);
  IWorksheet worksheet = workbook.Worksheets[0];

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

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

  stream.Position = 0;

  //Save the document as file and view the saved document

  //The operation in SaveAndView under Xamarin varies between Windows Phone, Android, and iOS platforms. Refer to the xlsio/xamarin section for respective code samples.

  if (Device.OS == TargetPlatform.WinPhone || Device.OS == TargetPlatform.Windows)
  {
	Xamarin.Forms.DependencyService.Get<ISaveWindowsPhone>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
  else
  {
	Xamarin.Forms.DependencyService.Get<ISave>().SaveAndView("Hyperlink.xlsx", "application/msexcel", stream);
  }
}