Dynamic data sort in Excel Addin

Dynamic data sort in Excel Addin

Sort:

Sorting data is an integral part of data analysis. You might want to arrange a list of names in alphabetical order, compile a list of product inventory levels from highest to lowest, or order rows by colors or icons. Sorting data helps you quickly visualize and understand your data better, organize and find the data that you want, and ultimately make more effective decisions.

You can sort data by text (A to Z or Z to A), numbers (smallest to largest or largest to smallest), and dates and times (oldest to newest and newest to oldest) in one or more columns. You can also sort by a custom list you create (such as Large, Medium, and Small) or by format, including cell color, font color, or icon set.

How to Manually sort in Excel:

  • Select the cell range you want to sort
  • Select the Sort and Filter on the Home Ribbon under Editing group, then click the Sort command
  • The cell range will be sorted by the selected column

In this article we will cover base parameters required to perform sorting on the data also we will see how dynamically we can perform sorting over the data.

Prerequisites:

  • Visual Studio 2015 or above having Microsoft Office for Developer Tool installed
  • Create Excel Addin in C# code style (Visual Studio Tools for Office)
  • Create a ribbon designer and put button

Code example:

private void btnSort_Click(object sender, RibbonControlEventArgs e)
{
	//get active sheet reference
	excel.Worksheet oWorksheet = Globals.ThisAddIn.Application.ActiveWorkbook.ActiveSheet;
	//retain active row selection
	excel.Range oRange = Globals.ThisAddIn.Application.Selection;
	//sort data
	oRange.Sort(Key1: oWorksheet.Range["A2"], Order1: excel.XlSortOrder.xlAscending,
		Header: excel.XlYesNoGuess.xlYes,Orientation:excel.XlSortOrientation.xlSortColumns);
}

Let’s understand base parameters which play critical rolls. I put list of available parameters in Sort method which can be passed as per situation:

  • Key1 : Specifies the first sort field, either as a range name (String) or Range object; determines the values to be sorted.
  • Order1 : Determines the sort order for the values specified in Key1.
  • Key2 : Second sort field; cannot be used when sorting a PivotTable.
  • Type : Specifies which elements are to be sorted.
  • Order2 : Determines the sort order for the values specified in Key2.
  • Key3 : Third sort field; cannot be used when sorting a PivotTable.
  • Order3 : Determines the sort order for the values specified in Key3.
  • Header : Specifies whether the first row contains header information. xlNo is the default value; specify xlGuess if you want Excel to attempt to determine the header.
  • OrderCustom : Specifies a one-based integer offset into the list of custom sort orders.
  • MatchCase : Set to True to perform a case-sensitive sort, False to perform a non-case-sensitive sort; cannot be used with PivotTables.
  • Orientation : Specifies if the sort should be by row (default) or column. Set xlSortColumns value to 1 to sort by column. Set xlSortRows value to 2 to sort by row (this is the default value).
  • SortMethod : Specifies the sort method.
  • DataOption1 : Specifies how to sort text in the range specified in Key1; does not apply to PivotTable sorting.
  • DataOption2 : Specifies how to sort text in the range specified in Key2; does not apply to PivotTable sorting.
  • DataOption3 : Specifies how to sort text in the range specified in Key3; does not apply to PivotTable sorting.

In our example we need only following parameters to accomplish the sorting over the data:

  • Key1
  • Order1
  • Header
  • Orientation : this is necessary to look as default sorting happens on rows

Let’s see output, below screenshot depicts input data:

Post code run the output would be:

Next : Custom sorting using built-in-dialog in Excel c#

Leave a Reply

Your email address will not be published.