langthang084

_ArraySort problem with Excel UDF?

11 posts in this topic

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

Share this post


Link to post
Share on other sites



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.


Any of my own code posted anywhere on the forum is available for use by others without any restriction of any kind._______My UDFs:

Spoiler

ArrayMultiColSort ---- Sort arrays on multiple columns
ChooseFileFolder ---- Single and multiple selections from specified path treeview listing
Date_Time_Convert -- Easily convert date/time formats, including the language used
ExtMsgBox --------- A highly customisable replacement for MsgBox
GUIExtender -------- Extend and retract multiple sections within a GUI
GUIFrame ---------- Subdivide GUIs into many adjustable frames
GUIListViewEx ------- Insert, delete, move, drag, sort, edit and colour ListView items
GUITreeViewEx ------ Check/clear parent and child checkboxes in a TreeView
Marquee ----------- Scrolling tickertape GUIs
NoFocusLines ------- Remove the dotted focus lines from buttons, sliders, radios and checkboxes
Notify ------------- Small notifications on the edge of the display
Scrollbars ----------Automatically sized scrollbars with a single command
StringSize ---------- Automatically size controls to fit text
Toast -------------- Small GUIs which pop out of the notification area

 

Share this post


Link to post
Share on other sites

#3 ·  Posted (edited)

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 by langthang084

Share this post


Link to post
Share on other sites

Because with

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

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

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

#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")

 

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#7 ·  Posted (edited)

@Water: @error = 2

Edited by langthang084

Share this post


Link to post
Share on other sites

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

Share this post


Link to post
Share on other sites

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?


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

#10 ·  Posted (edited)

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 by langthang084

Share this post


Link to post
Share on other sites

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


My UDFs and Tutorials:

Spoiler

UDFs:
Active Directory (NEW 2017-04-18 - Version 1.4.8.0) - Download - General Help & Support - Example Scripts - Wiki
OutlookEX (NEW 2017-02-27 - Version 1.3.1.0) - Download - General Help & Support - Example Scripts - Wiki
ExcelChart (2015-04-01 - Version 0.4.0.0) - Download - General Help & Support - Example Scripts
Excel - Example Scripts - Wiki
Word - Wiki
PowerPoint (2015-06-06 - Version 0.0.5.0) - Download - General Help & Support

Tutorials:
ADO - Wiki

 

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!


Register a new account

Sign in

Already have an account? Sign in here.


Sign In Now