_ArraySort problem with Excel UDF?

[langthang084]

When I use _arraySort after read "Data.xls", the result is sort on a first character of the number (like 93, 92, 91, 90, 9, 89, 88...) but when use _arraysort with only the number, it's normal

Could anyone help me?

#include <array.au3>
#include <file.au3>
#include <excel.au3>

opt("WinTitleMatchMode", 2)

$PathListDonThuoc = @ScriptDir & "\Data.xls"
$SoBA_Start = 25000

Local $oApp2 = _Excel_Open()
Local $SourceListDonThuoc = _Excel_BookOpen($oApp2, $PathListDonThuoc)

$ListDonThuoc = _Excel_SheetList ( $SourceListDonThuoc )
_ArrayDisplay($ListDonThuoc, "DS ĐƠN THUOC")

Local $SoSheetCanXoa[1][2]

for $b = 0 to UBound($ListDonThuoc) - 1
   ToolTip($b)
   $Name_BA = $ListDonThuoc[$b][0]
   $SoBA2 = StringSplit($Name_BA, " ")
   $numEnd = UBound($SoBA2) - 1

   $Name = $SoBA2[$NumEnd - 1]
   $SoBA_Sheet = $SoBA2[$NumEnd]
   ;
   if Number($SOBA_Sheet) < Number($SoBA_Start) Then
      _ArrayAdd($SoSheetCanXoa, $b + 1 & "|" & $ListDonThuoc[$b][0])
      ContinueLoop
   EndIf
Next
   _ArrayDelete($SoSheetCanXoa, 0)
   _ArrayDisplay($SoSheetCanXoa, "So sheet can xoa - BEFORE")
   _ArraySort($SoSheetCanXoa, 1,0,0,0)
   _ArrayDisplay($SoSheetCanXoa, "So sheet can xoa")

 

Data.xls

[Melba23]

langthang084,

If you want the elements sorted as numbers, you need to force them into the correct datatype using Number before sorting - otherwise you will, as you have already discovered, get them sorted according to the ASCII values of the characters.

[langthang084]

langthang084,

If you want the elements sorted as numbers, you need to force them into the correct datatype using Number before sorting - otherwise you will, as you have already discovered, get them sorted according to the ASCII values of the characters.

I change 

_ArrayAdd($SoSheetCanXoa, $b + 1 & "|" & $ListDonThuoc[$b][0])

to 

_ArrayAdd($SoSheetCanXoa, Number($b + 1) & "|" & $ListDonThuoc[$b][0])

But it dont work. But if I use (with Local $SoSheetCanXoa[1])

_ArrayAdd($SoSheetCanXoa, $b + 1)

then it works normally

Edited November 26, 2015 by langthang084

[water]

Because with

& "|" & $ListDonThuoc[$b][0])

you do a string concatenation and the result is a string again.

[langthang084]

Thanks Water. I've sorted normally. But why when use _excel_SheetDelete (Sheet Number's sort descending), the result is fail

expandcollapse popup

#include <array.au3>
#include <file.au3>
#include <excel.au3>

opt("WinTitleMatchMode", 2)

$PathListDonThuoc = @ScriptDir & "\Data.xls"
$SoBA_Start = 25000

Local $oApp2 = _Excel_Open()
Local $SourceListDonThuoc = _Excel_BookOpen($oApp2, $PathListDonThuoc)

   $ListDonThuoc = _Excel_SheetList ( $SourceListDonThuoc )
   _ArrayDisplay($ListDonThuoc, "DS ĐƠN THUOC")
Local $SoSheetCanXoa[1][2]

for $b = 0 to UBound($ListDonThuoc) - 1
   ToolTip($b)
   $Name_BA = $ListDonThuoc[$b][0]
   $SoBA2 = StringSplit($Name_BA, " ")
   $numEnd = UBound($SoBA2) - 1

   $Name = $SoBA2[$NumEnd - 1]
   $SoBA_Sheet = $SoBA2[$NumEnd]
   ;
   if Number($SOBA_Sheet) < Number($SoBA_Start) Then
      _arrayadd($SoSheetCanXoa, "")
      $SoSheetCanXoa[UBound($SoSheetCanXoa) - 1][0] = $b + 1
      $SoSheetCanXoa[UBound($SoSheetCanXoa) - 1][1] = $ListDonThuoc[$b][0]

      ContinueLoop
   EndIf
Next
   _ArrayDelete($SoSheetCanXoa, 0)
   _ArraySort($SoSheetCanXoa, 1,0,0,0)
   _ArrayDisplay($SoSheetCanXoa, "So sheet can xoa")

   for $c = UBound($SoSheetCanXoa) - 1 to 0 step -1
      $DelSheet = $SoSheetCanXoa[$c][0]
      _Excel_SheetDelete($SourceListDonThuoc, $DelSheet)
   Next
   $ListDonThuoc = _Excel_SheetList ( $SourceListDonThuoc )
   _ArrayDisplay($ListDonThuoc, "DS ĐƠN THUOC")

 

[water]

When deleting which sheet do you get which error (means what is the value of macro @error)?
 

[langthang084]

@Water: @error = 2

Edited November 26, 2015 by langthang084

[langthang084]

And there're some sheet dont have error when delete, but they arent been deleted

[water]

According to the help file (hint!) this means: "Specified sheet does not exist. @extended is set to the COM error code".
Could you write the names of the sheets you want to delete to the console and then check that they exist?

[langthang084]

I change to delete by the name of the sheet, then it works. But I dont know why when delete by the number of the sheet (like above), it's wrong. (My script find the number of the sheet in my excel file (data.xls), so the sheet must exist)

Edited November 26, 2015 by langthang084

[water]

Don't know. I can only guess that the number is wrong and a sheet with this number no longer exists.