Jump to content
jerem488

Optimize the code

Recommended Posts

jerem488

Hi all,

I have a script that works, but I think it's possible to optimize it. I find that the script takes a long time to process the files.

I need your opinion on the methods used and whether it is possible to optimize it.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>

Global $aArrayShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $aArrayIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat(Chr(32), 1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat(Chr(32), 1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat(Chr(32), 4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Chèque Banque Postale", "0CADO" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat(Chr(32), 3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat(Chr(32), 5), "DEBIT", ""], _
    ["Modes de règlement", "Chèque", "0CHQ" & _StringRepeat(Chr(32), 4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat(Chr(32), 1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat(Chr(32), 1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $bWriteExcelExport = False
Global $bFirstLine = True

Window()
Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    Global $hFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
    GUICtrlSetFont(-1, 10)
    Global $hSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    Global $idFolder = GUICtrlCreateInput("", 20, 60, 510)
    Global $hValider = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($hValider, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                Global $sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $hFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $sFileSelectFolder)
                    GUICtrlSetData($idFolder, $sFileSelectFolder)
                    GUICtrlSetState($hValider, $GUI_ENABLE)
                EndIf
            Case $hValider
                GUICtrlSetState($hFile, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($idFolder, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($hValider, $GUI_HIDE)
                Global $sLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArray($sFolder, "*", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($sFileSelectFolder & "\Exclusions.xls")
    FileDelete($sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        $sExtension = _WinAPI_PathFindExtension($aFileList[$i])
        If $sExtension = ".xls" Or $sExtension = ".xlsx" Then
            If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
        EndIf
    Next
    GUICtrlDelete($sLabelWait)
    GUICtrlSetState($hFile, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($idFolder, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($hValider, $GUI_SHOW)
    If ($i - 1) >= 1 And $bWriteExcelExport = True Then Window2($sFileSelectFolder & "\")
    If ($i - 1) >= 1 And $bWriteExcelExport = False Then Window3($sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 30, 70, 200)
    GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 207, 70, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sExclusionsFile, "0xAE0000")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $oExcel = _Excel_Open(False, False, False, False, True)
    If @error Then Exit
    Local $oWorkbookExport = _Excel_BookNew($oExcel)

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
    If @error Then _Excel_Close($oExcel)

    Local $sResultA1 = _Excel_RangeRead($oWorkbook, Default, "A1")
    If Not @error Then
        Local $bNewFile = True
        Local $nLine = 2
        If $sResultA1 = "CODE MAGASIN" Then $sCodeMagasin = _Excel_RangeRead($oWorkbook, Default, "A2")
        Local $n = 2
        Do
            Local $sLigne = ""
            Local $nLongueurLigne = 0
            Local $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            Local $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            Local $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            Local $sDate = ""                   ; 15nd character ; Length: 6 characters
            Local $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            Local $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            Local $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            Local $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            Local $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            Local $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

            ; ==> Recovery of Excel file cell contents
            Local $sColumA = _Excel_RangeRead($oWorkbook, Default, "A" & $n)
            If $sColumA = "" Then ExitLoop
            Local $sJour = _Excel_RangeRead($oWorkbook, Default, "B" & $n)
            Local $sCategorie = _Excel_RangeRead($oWorkbook, Default, "C" & $n)
            Local $sIndicateur = _Excel_RangeRead($oWorkbook, Default, "D" & $n)
            Local $sDebit = _Excel_RangeRead($oWorkbook, Default, "E" & $n)
            Local $sCredit = _Excel_RangeRead($oWorkbook, Default, "F" & $n)

            ; ==> Retrieving the account number
            $sNumeroCompte &= NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)

            ; ==> Retrieving the date
            $sYear = StringRight(StringLeft($sJour, 4), 2)
            $sMonth = StringRight(StringLeft($sJour, 6), 2)
            $sDay = StringRight(StringLeft($sJour, 8), 2)
            $sDate &= $sDay & $sMonth & $sYear

            ; ==> Retrieving the short text
            $sLibelleCourt &= Libelle($sCodeMagasin, "court")

            ; ==> Retrieving the long text
            $sLibelleLong &= Libelle($sCodeMagasin, "long")

            ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
            If $sDebit <> "" And $sCredit = "" Then
                If $sDebit <> 0 Then
                    $sLettreDebitCredit = "D"
                    ; ==> Recovery of debit amount
                    $sDebitCredit &= Formatage($sDebit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            Else
                If $sCredit <> 0 Then
                    $sLettreDebitCredit = "C"
                    ; ==> Recovery of credit amount
                    $sDebitCredit &= Formatage($sCredit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            EndIf

            ; ==> Complete line to write in the file
            $sLigne &= $sTypeEnregistrement & $sNumeroCompte & _StringRepeat(Chr(32), 2) & $sLigneFolio & $sDate & _StringRepeat(Chr(32), 1) & _
                $sLibelleCourt & $sLettreDebitCredit & $sDebitCredit & _StringRepeat(Chr(32), 52) & $sDevise & $sCodeJournal & _StringRepeat(Chr(32), 3) & $sLibelleLong

            $nLongueurLigne = StringLen($sLigne)
            $sLigne &= _StringRepeat(Chr(32), 256 - $nLongueurLigne)

            ; ==> Saving the results of the processed file
            If StringIsSpace($sNumeroCompte) Or $sNumeroCompte = "Acompte hors place" Or $sNumeroCompte = "Avoir hors place" Or $sDebitCredit = "ZERO" Then
                If $bFirstLine = True Then
                    _Excel_RangeWrite($oWorkbookExport, Default, "CODE MAGASIN", "A1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "JOUR", "B1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CATEGORIE", "C1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "INDICATEUR", "D1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "DEBIT", "E1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CREDIT", "F1")
                    $bWriteExcelExport = True
                    $bFirstLine = False
                EndIf

                _Excel_RangeCopyPaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A" & $n).EntireRow, $oWorkbookExport.Sheets(1).Range("A" & $nLine))
                $nLine += 1
            Else
                Local $hFileOpen = FileOpen($sFileSelectFolder & "\ImportQuadra.txt", 1)
                If $hFileOpen = -1 Then
                    MsgBox(16, "", "Cannot create file " & $sFileSelectFolder & "\ImportQuadra.txt")
                    Return False
                EndIf
                FileWriteLine($sFileSelectFolder & "\ImportQuadra.txt", $sLigne)
            EndIf
            $n += 1
        Until $sColumA = ""
        If $bWriteExcelExport = True Then _Excel_BookSaveAs($oWorkbookExport, $sFileSelectFolder & "\Exclusions.xls", Default, True)
        _Excel_BookClose($oWorkbookExport)
        _Excel_BookClose($oWorkbook)
        _Excel_Close($oExcel)
    EndIf
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".")
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat("0", 12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat("0", 12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)
    For $i = 0 To UBound($aArrayIndicator, 1) - 1
        Local $sNumeroCompte = ""
        Local $sSCompte = ""
        If $sCategorie == $aArrayIndicator[$i][0] And $sIndicateur == $aArrayIndicator[$i][1] Then
            $sNumeroCompte &= $aArrayIndicator[$i][2]
            If $aArrayIndicator[$i][1] = "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $aArrayIndicator[$i][1] = "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $aArrayIndicator[$i][4] = "SC" Then
                For $i = 0 To UBound($aArrayShop, 1) - 1
                    If $sCodeMagasin == $aArrayShop[$i][0] Then
                        $sSCompte = $aArrayShop[$i][2]
                        ExitLoop
                    EndIf
                Next
                $sNumeroCompte &= $sSCompte
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat(Chr(32), 8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($sCodeMagasin, $nLongueur)
    For $i = 0 To UBound($aArrayShop, 1) - 1
        Local $sLibelleCourt = ""
        Local $sLibelleLong = ""
        If $sCodeMagasin == $aArrayShop[$i][0] Then
            $sLibelleCourt &= StringLeft($aArrayShop[$i][1], 20)
            $sLibelleLong &= StringLeft($aArrayShop[$i][1], 30)
            ExitLoop
        EndIf
    Next
    If $nLongueur = "court" Then Return $sLibelleCourt
    If $nLongueur = "long" Then Return $sLibelleLong
EndFunc

 

Extraction.xls


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

Share this post


Link to post
Share on other sites
Zedna

Here are some minor speed optimizations:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $aArrayShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $aArrayIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de reglement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de reglement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de reglement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de reglement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de reglement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de reglement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de reglement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de reglement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de reglement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de reglement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $bWriteExcelExport = False
Global $bFirstLine = True

Window()
Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    Global $hFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
    GUICtrlSetFont(-1, 10)
    Global $hSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    Global $idFolder = GUICtrlCreateInput("", 20, 60, 510)
    Global $hValider = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($hValider, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                Global $sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $hFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $sFileSelectFolder)
                    GUICtrlSetData($idFolder, $sFileSelectFolder)
                    GUICtrlSetState($hValider, $GUI_ENABLE)
                EndIf
            Case $hValider
                GUICtrlSetState($hFile, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($idFolder, $GUI_HIDE)
                GUICtrlSetState($hSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($hValider, $GUI_HIDE)
                Global $sLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArray($sFolder, "*", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($sFileSelectFolder & "\Exclusions.xls")
    FileDelete($sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        $sExtension = _WinAPI_PathFindExtension($aFileList[$i])
        If $sExtension = ".xls" Or $sExtension = ".xlsx" Then
            If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
        EndIf
    Next
    GUICtrlDelete($sLabelWait)
    GUICtrlSetState($hFile, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($idFolder, $GUI_SHOW)
    GUICtrlSetState($hSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($hValider, $GUI_SHOW)
    If ($i - 1) >= 1 And $bWriteExcelExport = True Then Window2($sFileSelectFolder & "\")
    If ($i - 1) >= 1 And $bWriteExcelExport = False Then Window3($sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 30, 70, 200)
    GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 207, 70, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sExclusionsFile, "0xAE0000")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $hFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $oExcel = _Excel_Open(False, False, False, False, True)
    If @error Then Exit
    Local $oWorkbookExport = _Excel_BookNew($oExcel)

    Local $oWorkbook = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
    If @error Then _Excel_Close($oExcel)

    Local $sResultA1 = _Excel_RangeRead($oWorkbook, Default, "A1")
    If Not @error Then
        Local $bNewFile = True
        Local $nLine = 2
        If $sResultA1 = "CODE MAGASIN" Then $sCodeMagasin = _Excel_RangeRead($oWorkbook, Default, "A2")
        Local $n = 2
        Do
            Local $sLigne = ""
            Local $nLongueurLigne = 0
            Local $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            Local $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            Local $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            Local $sDate = ""                   ; 15nd character ; Length: 6 characters
            Local $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            Local $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            Local $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            Local $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            Local $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            Local $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

            ; ==> Recovery of Excel file cell contents
            Local $sColumA = _Excel_RangeRead($oWorkbook, Default, "A" & $n)
            If $sColumA == "" Then ExitLoop
            Local $sJour = _Excel_RangeRead($oWorkbook, Default, "B" & $n)
            Local $sCategorie = _Excel_RangeRead($oWorkbook, Default, "C" & $n)
            Local $sIndicateur = _Excel_RangeRead($oWorkbook, Default, "D" & $n)
            Local $sDebit = _Excel_RangeRead($oWorkbook, Default, "E" & $n)
            Local $sCredit = _Excel_RangeRead($oWorkbook, Default, "F" & $n)

            ; ==> Retrieving the account number
            $sNumeroCompte &= NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)

            ; ==> Retrieving the date
            $sYear = StringMid($sJour, 3, 2)
            $sMonth = StringMid($sJour, 5, 2)
            $sDay = StringMid($sJour, 7, 2)
            $sDate &= $sDay & $sMonth & $sYear

            ; ==> Retrieving the short text
            $sLibelleCourt &= Libelle($sCodeMagasin, "court")

            ; ==> Retrieving the long text
            $sLibelleLong &= Libelle($sCodeMagasin, "long")

            ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
            If $sDebit <> "" And $sCredit == "" Then
                If $sDebit <> 0 Then
                    $sLettreDebitCredit = "D"
                    ; ==> Recovery of debit amount
                    $sDebitCredit &= Formatage($sDebit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            Else
                If $sCredit <> 0 Then
                    $sLettreDebitCredit = "C"
                    ; ==> Recovery of credit amount
                    $sDebitCredit &= Formatage($sCredit)
                Else
                    $sDebitCredit = "ZERO"
                EndIf
            EndIf

            ; ==> Complete line to write in the file
            $sLigne &= $sTypeEnregistrement & $sNumeroCompte & _StringRepeat32(2) & $sLigneFolio & $sDate & _StringRepeat32(1) & _
                $sLibelleCourt & $sLettreDebitCredit & $sDebitCredit & _StringRepeat32(52) & $sDevise & $sCodeJournal & _StringRepeat32(3) & $sLibelleLong

            $nLongueurLigne = StringLen($sLigne)
            $sLigne &= _StringRepeat32(256 - $nLongueurLigne)

            ; ==> Saving the results of the processed file
            If StringIsSpace($sNumeroCompte) Or $sNumeroCompte == "Acompte hors place" Or $sNumeroCompte == "Avoir hors place" Or $sDebitCredit == "ZERO" Then
                If $bFirstLine = True Then
                    _Excel_RangeWrite($oWorkbookExport, Default, "CODE MAGASIN", "A1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "JOUR", "B1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CATEGORIE", "C1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "INDICATEUR", "D1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "DEBIT", "E1")
                    _Excel_RangeWrite($oWorkbookExport, Default, "CREDIT", "F1")
                    $bWriteExcelExport = True
                    $bFirstLine = False
                EndIf

                _Excel_RangeCopyPaste($oWorkbook.Sheets(1), $oWorkbook.Sheets(1).Range("A" & $n).EntireRow, $oWorkbookExport.Sheets(1).Range("A" & $nLine))
                $nLine += 1
            Else
                Local $hFileOpen = FileOpen($sFileSelectFolder & "\ImportQuadra.txt", 1)
                If $hFileOpen = -1 Then
                    MsgBox(16, "", "Cannot create file " & $sFileSelectFolder & "\ImportQuadra.txt")
                    Return False
                EndIf
                FileWriteLine($sFileSelectFolder & "\ImportQuadra.txt", $sLigne)
            EndIf
            $n += 1
        Until $sColumA == ""
        If $bWriteExcelExport = True Then _Excel_BookSaveAs($oWorkbookExport, $sFileSelectFolder & "\Exclusions.xls", Default, True)
        _Excel_BookClose($oWorkbookExport)
        _Excel_BookClose($oWorkbook)
        _Excel_Close($oExcel)
    EndIf
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($sCategorie, $sIndicateur, $sCodeMagasin)
    For $i = 0 To UBound($aArrayIndicator, 1) - 1
        Local $sNumeroCompte = ""
        Local $sSCompte = ""
        If $sCategorie == $aArrayIndicator[$i][0] And $sIndicateur == $aArrayIndicator[$i][1] Then
            $sNumeroCompte &= $aArrayIndicator[$i][2]
            If $aArrayIndicator[$i][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $aArrayIndicator[$i][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $aArrayIndicator[$i][4] == "SC" Then
                For $i = 0 To UBound($aArrayShop, 1) - 1
                    If $sCodeMagasin == $aArrayShop[$i][0] Then
                        $sSCompte = $aArrayShop[$i][2]
                        ExitLoop
                    EndIf
                Next
                $sNumeroCompte &= $sSCompte
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($sCodeMagasin, $nLongueur)
    For $i = 0 To UBound($aArrayShop, 1) - 1
        Local $sLibelleCourt = ""
        Local $sLibelleLong = ""
        If $sCodeMagasin == $aArrayShop[$i][0] Then
            $sLibelleCourt &= StringLeft($aArrayShop[$i][1], 20)
            $sLibelleLong &= StringLeft($aArrayShop[$i][1], 30)
            ExitLoop
        EndIf
    Next
    If $nLongueur == "court" Then Return $sLibelleCourt
    If $nLongueur == "long" Then Return $sLibelleLong
EndFunc

 

Share this post


Link to post
Share on other sites
jerem488

I take, even small optimizations! Thank you :)


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

Share this post


Link to post
Share on other sites
Subz

In edition to Zednas optimizations, have made a few other optimizations, which should make it run a bit faster, unfortunately haven't really had the time to debug, got to have some shuteye before work in a couple of hours.

Hope it helps:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xls")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])

    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport)
    _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
    If @error Then $g_bWriteExcelExport = False
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

 

Share this post


Link to post
Share on other sites
jerem488
4 hours ago, Subz said:

In edition to Zednas optimizations, have made a few other optimizations, which should make it run a bit faster, unfortunately haven't really had the time to debug, got to have some shuteye before work in a couple of hours.

Hope it helps:

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xls")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xls" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xls", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])

    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport)
    _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
    If @error Then $g_bWriteExcelExport = False
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

 

Hi Subz,

Did you change anything ?


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

Share this post


Link to post
Share on other sites
Subz

Weren't you able to test?  It should take about six seconds to run now, your earlier code took about 2 minutes to run on my system.

Share this post


Link to post
Share on other sites
jerem488
16 minutes ago, Subz said:

Weren't you able to test?  It should take about six seconds to run now, your earlier code took about 2 minutes to run on my system.

It works fine and very fast ! You are a genious ! :lmao: Thanks a lot !

I'm going to see the code changes :)


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

Share this post


Link to post
Share on other sites
Subz

You'll noticed that imported the entire sheet into an array and then just used this rather than reading/writing to Excel, once it finishes the loop it writes the $_oExport array to Exclusions Excel file.  I also added links to your final Window so you can open the .txt and Exclusions.xlsx file directly.  You may want to change the iFormat of the _Excel_BookSaveAs function when I ran through your code, I was given an error that it was incorrectly formatted, so changed it to xlsx.

 

  • Like 1

Share this post


Link to post
Share on other sites
jerem488

Oh right, I saw that big mistake :o

Quote

I also added links to your final Window so you can open the .txt and Exclusions.xlsx file directly.

Thank you, it's a good idea.

I just have one thing that doesn't work. The writing of the file "exclusions.xls" only works for the processing of the 1st file


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

Share this post


Link to post
Share on other sites
Subz

Is it suppose to create a new file or just append to the previous document or added as a different sheet?

Share this post


Link to post
Share on other sites
jerem488
Posted (edited)

Append to the previous document, successive

Edited by jerem488

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

Share this post


Link to post
Share on other sites
Subz

Haven't tested fully, but should work, if you want to move row up or down, just change the $_iLastRow + 2 to something lower.  If you want to remove the header then just do an _ArrayDelete($_aExport, 0) above or below line 297.

Anyway gotta go.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xlsx")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xlsx" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xlsx", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1, $_oExport, $_iLastRow
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])
    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_bFileExists = FileExists($g_sFileSelectFolder & "\Exclusions.xlsx")
    If $_bFileExists Then
        $_oExport = _Excel_BookOpen($oExcel, $g_sFileSelectFolder & "\Exclusions.xlsx")
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Error Opening Workbook", "Unable to open: " & $g_sFileSelectFolder & "\Exclusions.xlsx")
        EndIf
        $_iLastRow = $_oExport.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row + 2
    Else
        $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
        $_iLastRow = 1
    EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport, "A" & $_iLastRow)
    If $_bFileExists Then
        _Excel_BookSave($_oExport)
        If @error Then $g_bWriteExcelExport = False
    Else
        _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
        If @error Then $g_bWriteExcelExport = False
    Endif
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

 

Share this post


Link to post
Share on other sites
jerem488
10 hours ago, Subz said:

Haven't tested fully, but should work, if you want to move row up or down, just change the $_iLastRow + 2 to something lower.  If you want to remove the header then just do an _ArrayDelete($_aExport, 0) above or below line 297.

Anyway gotta go.

#include <GUIConstantsEx.au3>
#include <MsgBoxConstants.au3>
#include <FileConstants.au3>
#include <Array.au3>
#include <Excel.au3>
#include <File.au3>
#include <String.au3>
#include <WindowsConstants.au3>
#include <WinAPIShPath.au3>
#include <FontConstants.au3>
#include <StaticConstants.au3>
#include <Debug.au3>
#include <ExcelConstants.au3>

Global $chr32_max = StringRepeat(Chr(32), 256)
Global $chr0_max = _StringRepeat("0", 12)

; https://www.autoitscript.com/forum/topic/140190-stringrepeat-very-fast-using-memset/
Func StringRepeat($sChar, $nCount)
    $tBuffer = DLLStructCreate("char[" & $nCount & "]")
    DllCall("msvcrt.dll", "ptr:cdecl", "memset", "ptr", DLLStructGetPtr($tBuffer), "int", Asc($sChar), "int", $nCount)
    Return DLLStructGetData($tBuffer, 1)
EndFunc

Func _StringRepeat32($n)
    Return StringLeft($chr32_max, $n)
EndFunc

Func _StringRepeat0($n)
    Return StringLeft($chr0_max, $n)
EndFunc

Global $g_aShop[8][3] = [["424", "CAISSE COGR POST", "0008"], _
    ["337", "CAISSE COGR HLT", "0003"], _
    ["407", "CAISSE COGR XERT", "0009"], _
    ["431", "CAISSE COGR ZERCO", "0008"], _
    ["349", "CAISSE COGR 349", "0007"], _
    ["406", "CAISSE COGR DSOG", "0009"], _
    ["385", "CAISSE COGR VILLAR", "0004"], _
    ["420", "CAISSE COGR THORX", "0005"]]

Global $g_aIndicator[31][5] = [["Mouvements de Caisse", "CA HT", "", "", ""], _
    ["Mouvements de Caisse", "CA TTC", "", "CREDIT", ""], _
    ["Mouvements de Caisse", "TVA", "", "", ""], _
    ["Modes de règlement", "Acompte hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _ ; ALERTE
    ["Modes de règlement", "American Express", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Avoir hors place", "0AV", "DEBIT", "SC"], _
    ["Modes de règlement", "Bon cadeau hors place", "0COGR" & _StringRepeat32(1), "DEBIT", ""], _
    ["Modes de règlement", "BEST RECORD", "0BEST" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CADHOC", "0CADH" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "HAVAS", "0HAVA" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "KADEOS", "0KADE" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Shopping Pass", "0SHOP" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "TIR GROUPE", "0TIR" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "C.B. manuelle", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CA DO Cheque Banque Postale", "0CADO" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "Carte bancaire", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Carte Cadeau", "0CC", "DEBIT", "SC"], _
    ["Modes de règlement", "Carte ILLICADO", "0ILLI" & _StringRepeat32(3), "DEBIT", ""], _
    ["Modes de règlement", "CB BRANDALLEY", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB CFAO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB REDOUTE", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB SPARTOO", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "CB Web", "0CB" & _StringRepeat32(5), "DEBIT", ""], _
    ["Modes de règlement", "Cheque", "0CHQ" & _StringRepeat32(4), "DEBIT", ""], _
    ["Modes de règlement", "Reprise Acompte - P Diff", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise Acompte Réservation", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'acompte", "0COGR" & _StringRepeat32(1), "", ""], _
    ["Modes de règlement", "Reprise d'avoir", "0AV", "", "SC"], _
    ["Emissions (OSCC)", "Accompte", "0COGR" & _StringRepeat32(1), "CREDIT", ""], _
    ["Emissions (OSCC)", "Avoir", "0AV", "CREDIT", "SC"], _
    ["Emissions (OSCC)", "Carte cadeau", "0CC", "CREDIT", "SC"]]

Global $g_bWriteExcelExport = False
Global $bFirstLine = True
Global $g_idFile, $g_sFileSelectFolder, $g_idSelectFolderButton, $g_idFolder, $g_idOKButton, $g_idLabelWait

Window()

Func Window()
    Local $hGUI = GUICreate("Importation", 550, 147)
    $g_idFile = GUICtrlCreateLabel("Folder containing Excel files to import :", 20, 20, 280)
        GUICtrlSetFont(-1, 10)
    $g_idSelectFolderButton = GUICtrlCreateButton("Browse", 305, 17, 80)
    $g_idFolder = GUICtrlCreateInput("", 20, 60, 510)
    $g_idOKButton = GUICtrlCreateButton("OK", 20, 105, 80)
    GUICtrlSetState($g_idOKButton, $GUI_DISABLE)
    Local $sPath = @MyDocumentsDir & "\"

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $g_idSelectFolderButton
                If FileExists(@TempDir & "\Conectis.txt") Then
                    $sPath = FileReadLine(@TempDir & "\Conectis.txt", 1)
                EndIf
                $g_sFileSelectFolder = FileSelectFolder("", $sPath)
                If Not @error Then
                    Local $sPathFile = @TempDir & "\Conectis.txt"
                    Local $g_idFileOpen = FileOpen($sPathFile, $FO_OVERWRITE)
                    FileWriteLine(@TempDir & "\Conectis.txt", $g_sFileSelectFolder)
                    GUICtrlSetData($g_idFolder, $g_sFileSelectFolder)
                    GUICtrlSetState($g_idOKButton, $GUI_ENABLE)
                EndIf
            Case $g_idOKButton
                GUICtrlSetState($g_idFile, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idFolder, $GUI_HIDE)
                GUICtrlSetState($g_idSelectFolderButton, $GUI_HIDE)
                GUICtrlSetState($g_idOKButton, $GUI_HIDE)
                $g_idLabelWait = GUICtrlCreateLabel("Please wait...", 60, 52.5, 430, 25)
                GUICtrlSetFont(-1, 18)
                ListFiles($g_sFileSelectFolder)
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func ListFiles($sFolder)
    Local $aFileList = _FileListToArrayRec($sFolder, "*.xls;*.xlsx", 1)
    If @error = 1 Then Exit MsgBox(64, "", "The path is invalid")
    If @error = 4 Then Exit MsgBox(64, "", "No Excel files were found in this folder.")

    FileDelete($g_sFileSelectFolder & "\Exclusions.xlsx")
    FileDelete($g_sFileSelectFolder & "\ImportQuadra.txt")

    ; Loops the list of files
    For $i = 1 To UBound($aFileList) - 1
        If $aFileList[$i] <> "Exclusions.xlsx" Then TransposeExcel($sFolder, $aFileList[$i])
    Next
    GUICtrlDelete($g_idLabelWait)
    GUICtrlSetState($g_idFile, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idFolder, $GUI_SHOW)
    GUICtrlSetState($g_idSelectFolderButton, $GUI_SHOW)
    GUICtrlSetState($g_idOKButton, $GUI_SHOW)
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = True Then Window2($g_sFileSelectFolder & "\")
    If $aFileList[0] >= 1 And $g_bWriteExcelExport = False Then Window3($g_sFileSelectFolder & "\")
EndFunc

Func Window2($sFolder)

    Local $hGUI = GUICreate("Importation Quadra", 380, 150)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 10, 40, 100)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 220, 40, 100)
        GUICtrlSetFont($sQuadraFile, 9)
        GUICtrlSetColor($sQuadraFile, "0x4800FF")
        GUICtrlSetCursor($sQuadraFile, 0)
    Local $sExclusions = GUICtrlCreateLabel("File of exclusions to be processed:", 10, 70, 200)
        GUICtrlSetFont(-1, 9)
    Local $sExclusionsFile = GUICtrlCreateLabel("Exclusions.xlsx", 220, 70, 100)
        GUICtrlSetFont($sExclusionsFile, 9)
        GUICtrlSetColor($sExclusionsFile, "0xAE0000")
        GUICtrlSetCursor($sExclusionsFile, 0)
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 115, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Open", 285, 111.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
            Case $sQuadraFile
                ShellExecute($sFolder & "\ImportQuadra.txt")
            Case $sExclusionsFile
                ShellExecute($sFolder & "\Exclusions.xlsx")
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func Window3($sFolder)
    Local $hGUI = GUICreate("Importation Quadra", 380, 125)

    Local $sQuadra = GUICtrlCreateLabel("Import file:", 30, 40, 180)
    GUICtrlSetFont(-1, 9)
    Local $sQuadraFile = GUICtrlCreateLabel("ImportQuadra.txt", 195, 40, 100)
    GUICtrlSetFont(-1, 9)
    GUICtrlSetColor($sQuadraFile, "0x4800FF")
    Local $g_idFile = GUICtrlCreateLabel("Folder containing the generated files:", 20, 90, 260)
    GUICtrlSetFont(-1, 9, $FW_SEMIBOLD)
    Local $hOpenFolderButton = GUICtrlCreateButton("Ouvrir", 285, 86.5, 80)

    GUISetState(@SW_SHOW, $hGUI)

    While 1
        Switch GUIGetMsg()
            Case $GUI_EVENT_CLOSE
                ExitLoop
            Case $hOpenFolderButton
                Run('explorer.exe /e,"' & $sFolder & '"')
        EndSwitch
    WEnd

    GUIDelete($hGUI)
EndFunc

Func TransposeExcel($sFolder, $sFile)
    Local $_iIndex = 1, $_oExport, $_iLastRow
    Local $g_idFileOpen = FileOpen($g_sFileSelectFolder & "\ImportQuadra.txt", 1)
        If $g_idFileOpen = -1 Then
            MsgBox(16, "", "Cannot create file " & $g_sFileSelectFolder & "\ImportQuadra.txt")
            Return False
        EndIf
    Local $oExcel = _Excel_Open(False, False, False, False, True)
        If @error Then Return MsgBox(4096, "Excel Error", "Unable to open Excel.")
    Local $_oImport = _Excel_BookOpen($oExcel, $sFolder & "\" & $sFile)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Opening Workbook", "Unable to open : " & $sFolder & "\" & $sFile)
        EndIf
    Local $_aImport = _Excel_RangeRead($_oImport, Default, $_oImport.ActiveSheet.UsedRange)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel RangeRead Error",  "Error reading from workbook:" & @CRLF & "Filename : " & $sFolder & "\" & $sFile & @CRLF & "@error = " & @error & ", @extended = " & @extended)
        EndIf
        _Excel_BookClose($_oImport)

    Local $_aExport[1][6] = [["CODE MAGASIN", "JOUR", "CATEGORIE", "INDICATEUR", "DEBIT", "CREDIT"]]
    Local $_aDefault_ExportInfo[12], $_aDefault_ExportInfo
            $_aDefault_ExportInfo[0] = ""       ;~ $sLigne
            $_aDefault_ExportInfo[1] = 0        ;~ $nLongueurLigne = 0
            $_aDefault_ExportInfo[2] = "M"      ;~ $sTypeEnregistrement = "M"    ; 1st character ; Length: 1 character
            $_aDefault_ExportInfo[3] = ""       ;~ $sNumeroCompte = ""           ; 2nd character ; Length: 8 characters
            $_aDefault_ExportInfo[4] = "000"    ;~ $sLigneFolio = "000"          ; 12nd character ; Length: 3 characters
            $_aDefault_ExportInfo[5] = ""       ;~ $sDate = ""                   ; 15nd character ; Length: 6 characters
            $_aDefault_ExportInfo[6] = ""       ;~ $sLibelleCourt = ""           ; 22nd character ; Length: 20 characters
            $_aDefault_ExportInfo[7] = ""       ;~ $sLettreDebitCredit = ""      ; 23nd character ; Length: 1 character
            $_aDefault_ExportInfo[8] = ""       ;~ $sDebitCredit = ""            ; 43nd character ; Length: 13 characters
            $_aDefault_ExportInfo[9] = "EUR"    ;~ $sDevise = "EUR"              ; 108nd character ; Length: 3 characters
            $_aDefault_ExportInfo[10] = "ODC"   ;~ $sCodeJournal = "ODC"         ; 111nd character ; Length: 3 characters
            $_aDefault_ExportInfo[11] = ""      ;~ $sLibelleLong = ""            ; 117nd character ; Length: 30 characters

    For $i = 0 To UBound($_aImport) - 1
        If $_aImport[$i][0] = "CODE MAGASIN" Then ContinueLoop
        If $_aImport[$i][0] = "" Then ContinueLoop
        $_iIndex += 1
        $_aExportInfo = $_aDefault_ExportInfo
    ;~ Reformat Date
        $_aExportInfo[5] = StringFormat("%02i%02i%02i", StringMid($_aImport[$i][1], 7, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 3, 2))
        $_aImport[$i][1] = StringFormat("%2i/%2i/%4i", StringMid($_aImport[$i][1], 3, 2), StringMid($_aImport[$i][1], 5, 2), StringMid($_aImport[$i][1], 7, 2))
    ;~ Configure Column c Categorie - Retrieving the account number
        $_aExportInfo[3] &= NumeroCompte($_aImport[$i][2], $_aImport[$i][3], $_aImport[$i][0])
    ; ==> Retrieving the short text
        $_aExportInfo[6] &= Libelle($_aImport[$i][0], "court")
    ; ==> Retrieving the long text
        $_aExportInfo[11] &= Libelle($_aImport[$i][0], "long")
    ; ==> Retrieving the debit OR credit letter ('C' OU 'D')
        If $_aImport[$i][4] <> "" And $_aImport[$i][5] == "" Then
            If $_aImport[$i][4] <> 0 Then
                $_aExportInfo[7] = "D"
            ; ==> Recovery of debit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][4])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        Else
            If $_aImport[$i][5] <> 0 Then
                $_aExportInfo[7] = "C"
            ; ==> Recovery of credit amount
                $_aExportInfo[8] &= Formatage($_aImport[$i][5])
            Else
                $_aExportInfo[8] = "ZERO"
            EndIf
        EndIf
    ; ==> Complete line to write in the file
        $_aExportInfo[0] &= $_aExportInfo[2] & $_aExportInfo[3] & _StringRepeat32(2) & $_aExportInfo[4] & $_aExportInfo[5] & _StringRepeat32(1) & _
        $_aExportInfo[6] & $_aExportInfo[7] & $_aExportInfo[8] & _StringRepeat32(52) & $_aExportInfo[9] & $_aExportInfo[10] & _StringRepeat32(3) & $_aExportInfo[11]
        $_aExportInfo[1] = StringLen($_aExportInfo[0])
        $_aExportInfo[0] &= _StringRepeat32(256 - $_aExportInfo[1])
    ; ==> Saving the results of the processed file
        If StringIsSpace($_aExportInfo[3]) Or $_aExportInfo[3] == "Acompte hors place" Or $_aExportInfo[3] == "Avoir hors place" Or $_aExportInfo[8] == "ZERO" Then
            _ArrayAdd($_aExport, _ArrayToString($_aImport, "|", $i, $i))
            ContinueLoop
        Else
            FileWriteLine($g_sFileSelectFolder & "\ImportQuadra.txt", $_aExportInfo[0])
        EndIf
    Next
    Local $_bFileExists = FileExists($g_sFileSelectFolder & "\Exclusions.xlsx")
    If $_bFileExists Then
        $_oExport = _Excel_BookOpen($oExcel, $g_sFileSelectFolder & "\Exclusions.xlsx")
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Error Opening Workbook", "Unable to open: " & $g_sFileSelectFolder & "\Exclusions.xlsx")
        EndIf
        $_iLastRow = $_oExport.ActiveSheet.Range("A1").SpecialCells($xlCellTypeLastCell).Row + 2
    Else
        $_oExport = _Excel_BookNew($oExcel)
        If @error Then
            _Excel_Close($oExcel)
            Return MsgBox(4096, "Excel Create Workbook", "Unable to create new Workbook")
        EndIf
        $_iLastRow = 1
    EndIf
    _Excel_RangeWrite($_oExport, $_oExport.ActiveSheet, $_aExport, "A" & $_iLastRow)
    If $_bFileExists Then
        _Excel_BookSave($_oExport)
        If @error Then $g_bWriteExcelExport = False
    Else
        _Excel_BookSaveAs($_oExport, $g_sFileSelectFolder & "\Exclusions.xlsx", $xlWorkbookDefault)
        If @error Then $g_bWriteExcelExport = False
    Endif
    $g_bWriteExcelExport = True
    _Excel_BookClose($_oExport)
    _Excel_Close($oExcel)
EndFunc

Func Formatage($sCellule)
    ; ==> Function that formats in 12 characters the debit & credit columns of the rows
    Local $sMontant = ""
    If IsNumber($sCellule) Then
        ; ==> Any + and - characters that may be present are deleted
        $sCellule = StringRegExpReplace($sCellule, "[^\d\.]", "")

        Local $PositionPoint = StringInStr($sCellule, ".", 1)
        Local $sDecimales = ""
        If $PositionPoint Then ; There's a point
            $sDecimales = StringTrimLeft($sCellule, $PositionPoint)
            $sNumber = StringLeft($sCellule, $PositionPoint - 1)
            If StringLen($sDecimales) < 2 Then
                $sDecimales &= "0"
            EndIf
            $sMontant &= $sNumber & $sDecimales
            $nLongueur = StringLen($sMontant)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sMontant
        Else ; There is no point
            $sDecimales &= $sCellule & "00"
            $nLongueur = StringLen($sDecimales)
            $sMontant = "+" & _StringRepeat0(12 - $nLongueur) & $sDecimales
        EndIf
    EndIf
    Return $sMontant
EndFunc

Func NumeroCompte($p_sCategorie, $p_sIndicateur, $p_sCodeMagasin)
    Local $_iSearchShop
    Local $_iFindIndicator = _ArrayFindAll($g_aIndicator, $p_sCategorie, 0, 0, 0, 2, 0)
    Local $sNumeroCompte = "", $sSCompte = ""
    For $i = 0 To UBound($_iFindIndicator) - 1
        If $p_sIndicateur == $g_aIndicator[$_iFindIndicator[$i]][1] Then
            $sNumeroCompte &= $g_aIndicator[$_iFindIndicator[$i]][2]
            If $g_aIndicator[$_iFindIndicator[$i]][1] == "Acompte hors place" Then
                $sNumeroCompte = "Acompte hors place"
                ExitLoop
            ElseIf $g_aIndicator[$_iFindIndicator[$i]][1] == "Avoir hors place" Then
                $sNumeroCompte = "Avoir hors place"
                ExitLoop
            EndIf
            If $g_aIndicator[$_iFindIndicator[$i]][4] == "SC" Then
                $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
                    If $_iSearchShop <> -1 Then $sNumeroCompte &= $g_aShop[$_iSearchShop][2]
            EndIf
            $nLongueur = StringLen($sNumeroCompte)
            $sNumeroCompte &= _StringRepeat32(8 - $nLongueur)
            ExitLoop
        EndIf
    Next
    Return $sNumeroCompte
EndFunc

Func Libelle($p_sCodeMagasin, $p_nLongueur)
    Local $_iSearchShop = _ArraySearch($g_aShop, $p_sCodeMagasin, 0, 0, 0, 0, 1, 0)
    If $_iSearchShop = -1 Then Return
    Switch $p_nLongueur
        Case "court"
            Return StringLeft($g_aShop[$_iSearchShop][1], 20)
        Case "long"
            Return StringLeft($g_aShop[$_iSearchShop][1], 30)
    EndSwitch
EndFunc

 

It works fine :)

The "$_iIndex" variable is useless?

And where can I replace points ".", with a decimal point "," when we save in the exclusions.xlsx file only ? (Colums "DEBIT" and "CREDIT")

 


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

Share this post


Link to post
Share on other sites
Subz

When I started I was trying to follow your functions so there are bound to be entries that shouldn't be there, as I mentioned above it was rather rough optimisation as I didn't have time to debug.  So feel free to remove "$_iIndex", with regards to changing decimals, you want to change decimal point "." to a comma?  Remember that $_aImport is just an array of the "Extraction.xls" file or the ones being processed.  So below "; ==> Complete line to write in the file" just add  the following, remember the "If" statement above uses both $_aImport[$i][4] and $_aImport[$i][5] to update $_aExportInfo[$i][8] so if you move the lines anywhere above $_aExportInfo[$i][8] would return incorrect data.

;~ Replace Decimals
        $_aImport[$i][4] = StringReplace($_aImport[$i][4], ".", ",")
        $_aImport[$i][5] = StringReplace($_aImport[$i][5], ".", ",")
    ; ==> Complete line to write in the file

 

Share this post


Link to post
Share on other sites
jerem488

Yes, I had just done it! :D

Thanks for the quickly response :)


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

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

×