X++ for Excel fields to sort

Public Core Method :
/**

  • Use as:X++ for Excel fields to sort
  • Author:HANK Jiang
  • Parameter:_dataRange – Example,A1:C6
  • _sortRange – Example,B2
  • _isDesc – Yes or No
  • _xlYesNoGuess – The first line specifies whether to include the header, value: 0, 1, 2
    */
    public void sort(str 10 _dataRange, str 10 _sortRange, boolean _isDesc=false, int _xlYesNoGuess=2)
    {
    #define.xlAscending(1)
    #define.xlDescending(2)

#define.xlPinYin(1)
#define.xlStroke(2)

/**

  • XlYesNoGuess Enum
    */
    #define.xlHeaderGuess(0)
    #define.xlHeaderYes(1)
    #define.xlHeaderNo(2)

#define.xlTopToBottom(1)
#define.xlLeftToRight(2)

#define.xlSortOnValues(0)

#define.xlSortNormal(0)
#define.xlSortTextAsNumbers(1)

COM SortCOM, SheetCOM, SortField, SortFields, DataRngCOM, SortRngCOM;
;

DataRngCOM = this.range(_dataRange).comObject(); // sheet.range(_RangeStr)
SortRngCOM = this.range(_sortRange).comObject();
SheetCOM = sheet.comObject(); //sheet is SysExcelWorksheet
SortCOM = SheetCOM.Sort();
SortFields = SortCOM.SortFields();
SortFields.Clear();

/**

  • SORT Range
    */
    SortFields.Add(SortRngCOM, #xlSortOnValues, (_isDesc ? #xlDescending : #xlAscending), 1, #xlSortNormal);
    //SortField = SortFields.Item(1);
    //SortField.CustomOrder();

/**

  • DATA Range
    */
    SortCOM.SetRange(DataRngCOM);

SortCOM.Header(_xlYesNoGuess); //#xlHeaderYes
SortCOM.MatchCase(False);
SortCOM.Orientation(#xlTopToBottom);
SortCOM.SortMethod(#xlPinYin);
SortCOM.Apply();
}

Demo:
static void HnExcel_sort_Job(Args _args)
{
HnExcelHelper excel = new HnExcelHelper();
;

excel.value(“A1”, “AA”);
excel.value(“A2”, “DD”);
excel.value(“A3”, “CC”);
excel.value(“A4”, “BB”);
excel.value(“A5”, “EE”);

excel.value(“B1”, “3”);
excel.value(“B2”, “2”);
excel.value(“B3”, “1”);
excel.value(“B4”, “5”);
excel.value(“B5”, “4”);

excel.value(“C1”, “A001”);
excel.value(“C2”, “A002”);
excel.value(“C3”, “A003”);
excel.value(“C4”, “A004”);
excel.value(“C5”, “A005”);

excel.insertRow(1, 1);

excel.setRowValue(1, [“Letter”, “Number”, “Name”]);

excel.sort(“A1:C6”, “B2”, false, 1);

excel.show();
}

Hope to have the help to you, thanks.