Jump to content

Problem with blanck end of string


jerem488
 Share

Recommended Posts

Hi all,

I read an excel file, it works fine. I create a combo list with values of the excel file.

But I don't understand why my StringStripWS() function doesn't work. For example in the Excel file attached to this topic, the "Open Banco Sabadell" cell, there is a blanck at the end of the string and the StringStripWS() function doesn't delete the blanck at the end.

You're going to tell me to remove it manually in the Excel File, but the Excel file attached to this topic is part of many other Excel files.

 

#include <GUIConstantsEx.au3>
#include <Excel.au3>
#include <String.au3>

Global $s_File = @DesktopDir & "\s_2017.xls"
Global $o_Excel = _Excel_Open()
Global $o_Workbook = _Excel_BookOpen($o_Excel, $s_File)

Local $h_GUI = GUICreate("Selection", 490, 200)
Local $id_Tournois = GUICtrlCreateCombo("", 20, 35, 450)
GUICtrlSetData($id_Tournois, _ArrayToString(Tournaments("B"), "|", 1))
GUISetState(@SW_SHOW, $h_GUI)
While 1
    Switch GUIGetMsg()
        Case $GUI_EVENT_CLOSE
            ExitLoop
    EndSwitch
WEnd
GUIDelete($h_GUI)
_Excel_BookClose($o_Workbook)
_Excel_Close($o_Excel)

Func Tournaments($s_Letter)
    Local $a_Tournois[0]
    Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter))
    $s_Results_StripWS = StringStripWS(_ArrayToString($a_Results), 7)
    For $i = 1 To StringLen($s_Results_StripWS)
        ConsoleWrite(":" & StringMid($s_Results_StripWS, $i, 1) & ":" & Asc(StringMid($s_Results_StripWS, $i, 1)) & @LF)
    Next
    $a_Results_TitleCase = _StringTitleCase($s_Results_StripWS)
    _ArrayAdd($a_Tournois, $a_Results_TitleCase)
    $a_Unique = _ArrayUnique($a_Tournois)
    Local $i_Tournament = _ArraySearch($a_Unique, "Tournament")
    _ArrayDelete($a_Unique, $i_Tournament)
    _ArraySort($a_Unique)
    ConsoleWrite("Number of rows:" & UBound($a_Unique, $UBOUND_ROWS) & @LF)
    Return $a_Unique
EndFunc

 

s_2017.xls

Qui ose gagneWho Dares Win[left]CyberExploit[/left]

Link to comment
Share on other sites

$s_Results_StripWS = StringStripWS(_ArrayToString($a_Results), 7)

_ArrayToString = Places the elements of a 1D or 2D array into a single string, separated by the specified delimiters

Single string is made with _ArrayToString (single is the keyword here)

StringStripWS (with flag 7) = leading, trailing, and double (for a single string)

It is normal that you don't eliminate a simple ws inside a single string.

Once you have sorted out all the elements of the array, do the StringStripWs on each individual cell 

Link to comment
Share on other sites

You can't use StringStripWS like this. The following works fine for me --

Func Tournaments($s_Letter)
    Local $a_Tournois[0]
    Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter))

    For $i = 0 To UBound($a_Results) - 1
        $a_Results[$i] = _StringTitleCase(StringStripWS($a_Results[$i], 7))
    Next

    $a_Unique = _ArrayUnique($a_Results)
    Local $i_Tournament = _ArraySearch($a_Unique, "Tournament")
    _ArrayDelete($a_Unique, $i_Tournament)
    _ArraySort($a_Unique)
    ConsoleWrite("Number of rows:" & UBound($a_Unique, $UBOUND_ROWS) & @LF)
    Return $a_Unique
EndFunc

 

Link to comment
Share on other sites

Alternative method which will trim spaces and set proper casing in the document.

Func Tournaments($s_Letter)
    Local $o_Range = $o_Workbook.Sheets("Tournament_List").Usedrange.Columns($s_Letter & ":" & $s_Letter)
    $o_Range.Value = $o_Excel.Trim($o_Range)
    $o_Range.Value = $o_Excel.Proper($o_Range)
    Local $a_Results = _Excel_RangeRead($o_Workbook, Default, $o_Range)
    _ArrayDelete($a_Results, 0) ;~ Delete the first row item "Tournament"
    _ArraySort($a_Results)
    Local $a_Tournois = _ArrayUnique($a_Results)
    Return $a_Tournois
EndFunc

 

Link to comment
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
 Share

  • Recently Browsing   0 members

    • No registered users viewing this page.
×
×
  • Create New...