jerem488 Posted June 20, 2018 Posted June 20, 2018 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. expandcollapse popup#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]
Zedna Posted June 20, 2018 Posted June 20, 2018 Here are some minor speed optimizations: expandcollapse popup#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 Resources UDF ResourcesEx UDF AutoIt Forum Search
jerem488 Posted June 21, 2018 Author Posted June 21, 2018 I take, even small optimizations! Thank you Qui ose gagneWho Dares Win[left]CyberExploit[/left]
Subz Posted June 21, 2018 Posted June 21, 2018 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: expandcollapse popup#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
jerem488 Posted June 21, 2018 Author Posted June 21, 2018 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: expandcollapse popup#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]
Subz Posted June 21, 2018 Posted June 21, 2018 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.
jerem488 Posted June 21, 2018 Author Posted June 21, 2018 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 ! Thanks a lot ! I'm going to see the code changes Qui ose gagneWho Dares Win[left]CyberExploit[/left]
Subz Posted June 21, 2018 Posted June 21, 2018 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. Xandy 1
jerem488 Posted June 21, 2018 Author Posted June 21, 2018 Oh right, I saw that big mistake 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]
Subz Posted June 21, 2018 Posted June 21, 2018 Is it suppose to create a new file or just append to the previous document or added as a different sheet?
jerem488 Posted June 21, 2018 Author Posted June 21, 2018 (edited) Append to the previous document, successive Edited June 21, 2018 by jerem488 Qui ose gagneWho Dares Win[left]CyberExploit[/left]
Subz Posted June 21, 2018 Posted June 21, 2018 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. expandcollapse popup#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
jerem488 Posted June 22, 2018 Author Posted June 22, 2018 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. expandcollapse popup#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]
Subz Posted June 22, 2018 Posted June 22, 2018 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
jerem488 Posted June 22, 2018 Author Posted June 22, 2018 Yes, I had just done it! Thanks for the quickly response Qui ose gagneWho Dares Win[left]CyberExploit[/left]
Recommended Posts
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 accountSign in
Already have an account? Sign in here.
Sign In Now