How to: Sort Data in Worksheets Programmatically
Updated: July 2008
Applies to |
|---|
The information in this topic applies only to the specified Visual Studio Tools for Office projects and versions of Microsoft Office. Project type
Microsoft Office version
For more information, see Features Available by Application and Project Type. |
You can sort data that is contained in worksheet ranges and lists at run time. The following code sorts a multi-column range named Fruits by the data in the first column, and then by the data in the second column.
Starting in Visual Studio 2008 Service Pack 1 (SP1), you can sort data at run time in application-level add-ins.
To sort data in a NamedRange control
Call the Sort method of the NamedRange control. The following example requires a NamedRange control named Fruits on a worksheet. This code must be placed in a sheet class, not in the ThisWorkbook class.
this.Fruits.Sort( this.Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending, this.Fruits.Columns[2, missing], missing, Excel.XlSortOrder.xlAscending, missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, missing, missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);
Place the following code in Sheet1.vb or Sheet1.cs to sort data in a ListObject control. The code assumes that you have a Microsoft.Office.Tools.Excel.ListObject control named fruitList in a worksheet named Sheet1.
To sort data in a ListObject control
Call the Sort(Object, XlSortOrder, Object, Object, XlSortOrder, Object, XlSortOrder, XlYesNoGuess, Object, Object, XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDataOption, XlSortDataOption) method of the Range property of the ListObject host control.
this.fruitList.Range.Sort( this.fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending, this.fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending, missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes, missing, missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);
To sort data in a native range
Call the Sort(Object, XlSortOrder, Object, Object, XlSortOrder, Object, XlSortOrder, XlYesNoGuess, Object, Object, XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDataOption, XlSortDataOption) method of the native Excel Range control. The following example requires a native Excel control named Fruits on a worksheet.
Excel.Range Fruits = Application.get_Range("A1", "B3"); Fruits.Sort( Fruits.Columns[1, missing], Excel.XlSortOrder.xlAscending, Fruits.Columns[2, missing], missing, Excel.XlSortOrder.xlAscending, missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlNo, missing, missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);
To sort data in a ListObject control
Call the Sort(Object, XlSortOrder, Object, Object, XlSortOrder, Object, XlSortOrder, XlYesNoGuess, Object, Object, XlSortOrientation, XlSortMethod, XlSortDataOption, XlSortDataOption, XlSortDataOption) method of the Range property of the native Excel ListObject control. The following example assumes that you have a native Excel ListObject control named fruitList in the active worksheet.
Excel.ListObject fruitList = ((Excel.Worksheet)Application.ActiveSheet). ListObjects.Add(Microsoft.Office.Interop.Excel.XlListObjectSourceType.xlSrcRange, Application.get_Range("A1", "B3"), missing, Microsoft.Office.Interop.Excel.XlYesNoGuess.xlNo, missing); fruitList.Range.Sort( fruitList.ListColumns[1].Range, Excel.XlSortOrder.xlAscending, fruitList.ListColumns[2].Range, missing, Excel.XlSortOrder.xlAscending, missing, Excel.XlSortOrder.xlAscending, Excel.XlYesNoGuess.xlYes, missing, missing, Excel.XlSortOrientation.xlSortColumns, Excel.XlSortMethod.xlPinYin, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal, Excel.XlSortDataOption.xlSortNormal);
