Exemple  : SQL EXCEL : Exemple simple et complet with endUser4gl

*1 ) Récup des données d'un fichier Excel

*2) generation Txt  

*3)  appel automatique de Excel et conversion du fichier Texte en fichier Excel

objectifs :

*Recupérer des données d'un fichier Excel   : 240 pays";

* lire le fichier Input : 10 lignes";

*Démultiplier les enregistrements lus par pays ";

* et constituer un nouveau fichier Excel de 2400 lignes;

** ****************************************************************************************** *

**EndUser4gl : Exemple d'un langage de4 génération

** *******************************************************************************************

**

** Exemple  : SQL EXCEL : Exemple simple et complet with endUser4gl

** 1 ) Récup des données d'un fichier Excel

** 2) generation Txt  

** 3)  appel automatique de Excel et dconversion Excel

**

** ********************************************

GLOBAL

        #Include "c:\EndUser4gl\4gl\hlp\IncludeSysEU\includesyseu.h"

                INTEGER : i,y, X  ;

                CHAR :   Filewrite:255  := "C:\EndUser4gl\Excel\Appli1TxtXls\report1.csv" ;

              CHAR :   Table:countrytab:4 Occurs 500 ;

            CHAR :   Table:zfa:10 Occurs 10,zpays:4;      

** Structure Output file/fichier en sortie   

        STRUCT : Ref

                {

                        CHAR

                        fa:10 ,      s1:1 :=";",

                        fb: 10 ,     s2:1 :=";",

                        fc:10 ,      s3:1 :=";",

                        fd:10,       s4:1 :=";",

                        fe :10 ,     s5:1 :=";",

                        ff:10 ,      s6:1 :=";",

                        fg :10 ,     s7:1 :=";",        

                        fh:10,       s8:1 :=";",

                        fi:10,       s9:1 :=";", ** tab

                        s10:2 :=        "\\n" ; ** or   return carriage

                }

END_Global

BEGIN_Main

        Tabledelete (Filewrite) ;

        SetFileAttribute(Filewrite:FILE_ATTRIBUTE_NORMAL);

                Fileopen(1,Filewrite,Write) Returning (X) ;

        Message_RETURN_CODE_FILEOPENW(X);

DISPlay "   " ; SetColorText(Hwdisplay:green,red) ;

Display " SVP/Wait  .... Formatting ";

            i :=1;

SetColorText(Hwdisplay:black,yellow) ;

display " Recupérer des données d'un fichier Excel   : 240 pays";

display " lire le fichier Input : 10 lignes";

display " Démultiplier les enregistrements lus par pays ";

display " et constituer un nouveau fichier Excel de 2400 lignes";

skip_line 3;

 SetColorText(Hwdisplay:yellow,black ) ;

display " to recover data of an Excel file  : 240 pays";

display " Read file Input : 10 lines";

display " to gear down the recordings read by country ";

display " and to constitute a new Excel file of 2400 lines";

 ** Recupérer des données d'un fichier Excel

 ** to recover data of an Excel file  

skip_line 3;

Display "Wait.........";

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");    

SetEnvDatabaseSQL{Xls}("C:\EndUser4gl\Excel\Appli1TxtXls\pays.xls");            

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  ")

        ForEachRowFind :

                ** save into table memory         

                 Countrytab[i] := GetDataColumn: 1,CHAR;        ** Colonne 1 du fichier Excel /Column 1 Excel file      

                 zpays := Countrytab[i];

** display " pays..     :",zpays, " i : ",i;

                        i++;

        END_ForEachRow;

        ** --------------------------------------------------------------------------------- **

        ** SELECT   

        ** --------------------------------------------------------------------------------- **

         **  

         ** Recupérer des données d'un fichier Excel

         ** to recover data of an Excel file

         ** Démultiplier les enregistrements lus par pays

         ** to gear down the recordings read by country

         **

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");    

SetEnvDatabaseSQL{Xls}("C:\EndUser4gl\Excel\Appli1TxtXls\Input.xls");           

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  ")

        ForEachRowFind :

                ** save into memory         

                 zfa[1] := GetDataColumn: 1,CHAR;       ** Colonne 1 du fichier Excel /Column 1 Excel file      

                 zfa[2] := GetDataColumn: 2,CHAR;

                 zfa[3] := GetDataColumn: 3,CHAR;

                 zfa[4] := GetDataColumn: 4,CHAR;

                 zfa[5] := GetDataColumn: 5,CHAR;

                 zfa[6] := GetDataColumn: 6,CHAR;

                 zfa[7] := GetDataColumn: 7,CHAR;

                 zfa[8] := GetDataColumn: 8,CHAR;

                 zfa[9] := GetDataColumn: 9,CHAR;

                 zfa[10] := GetDataColumn: 10,CHAR;

                for(y :=1 ;y < i;y++)

                begin_for

                 ref.fa := zfa[1];              

                 ref.fb := zfa[2];

                 Ref.fc := zfa[3];

                 Ref.fd := zfa[4];

                 Ref.fe := zfa[5];

                 Ref.ff := Countrytab[y];

                 Ref.fg := zfa[7];

                 Ref.fh := zfa[8];

                 Ref.fi := zfa[9];

                        Fileadd(1,Ref) Returning (X); ** add txt file

               End_for;

        END_ForEachRow;

        Fileclose(1);

 DestroyEnvSql();

 Display "end........";

 **

 ** Appel Excel/Call Excel

 **

                Connect_MSEXCEL("C:\EndUser4gl\Excel\Appli1TxtXls\Input.xls":Visible);

                  Connect_MSEXCEL(Filewrite:Visible);

 ** bye, bye..

         END_Main

/* ******************************** PROCEDURES *************************************** */

PROC Message_RETURN_CODE_FILEOPENW(INTEGER U)

BEGIN_Procedure

        CASE_OF U

                WHEN 1 : Display " Impossible : read only :

                        file is open by other pgm  ",U Column 70 ;exit_pgm();

END_When;

                WHEN 2 : Display "  path or file no exist or not  read only ",U ,FileWrite   ;

                        exit_pgm();

                END_When;

                Otherwise :

                Display " Open File Ok " ,U Column 60;

        END_Case        ;

END_Procedure

** Exemple  : SQL Project by ENDUSER4GL

** *****************************************************************************************

#Include "c:\EndUser4gl\4gl\hlp\IncludeSysEU\includesyseu.h"

INTEGER : Msgsql,X,Messagsql,i;

char  : file1:255 := "C:\EndUser4gl\Excel\Appli2Filter\InputFilter.xls" ,wcci:128 := "";

char :  Filewrite:255 := "C:\EndUser4gl\Excel\Appli2Filter\OutputFilter.xls" ;

        STRUCT : Ref

                        {

                        CHAR fa:10 ,     s1:2 :="\\t",** ou  appuyer sur la touche TAB

                             fb:20 ,     s2:2 :="\\t",

                             fc:20 ,     s3:2 :="\\t",

                             fd:20 ,     s4:2 :="\\t",

                             fe :20 ,    s5:2 :="\\t",

                             ff :20 ,    s6:2 :="\\t",

                        s10:2 :="\\n" ; ** ou  appuyer sur la touche return (entrée)

                                }

 Tableexist(Filewrite) returning (x) ;

/* 2 return codes */

** x = 0 : Ok , x = 1 Not ok */

if (x void) then

Tabledelete (Filewrite) ;

Display " TableDelete Ok " ,Filewrite Column 60;

ELSE

        Display "Table Delete Not find";

END_If ;

SetFileAttribute(Filewrite:FILE_ATTRIBUTE_NORMAL);

                Fileopen(1,Filewrite,Write) Returning (X) ;

** save/copy file

** Fichier de la J-1

**

SetColorText(Hwdisplay:black,green) ;

skip_line 3;

Display" CHECK FILTER..: Begin";

** ****************************************************************************************

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");

  display"1 file :",file1 ;

   setEnvDatabaseSQL{Xls}(file1);

  i :=0;

** Recherche

** permet de regrouper plusieurs critérés dans 1 seul fichier de sortie

** several criteria in 1 only output file

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  WHERE ([Feuil1$].NOM LIKE '%DUPONT%')

                                                         or   ([Feuil1$].NOM LIKE '%DURAND%')

                                                         or   ([Feuil1$].NOM LIKE '%TOTO%')

                                                         or   ([Feuil1$].NOM LIKE '%SIMON%') ")

        ForEachRowFind :

                  ref.fa := GetDataColumn: 1,CHAR;

                  ref.fb := GetDataColumn: 8,CHAR;              

                  Ref.fc  := GetDataColumn: 9,CHAR;

                  Ref.fd  := GetDataColumn: 10,CHAR;

                  Ref.fe  := GetDataColumn: 11,CHAR;

                  ref.ff  := "\\t";

                  Fileadd(1,Ref) Returning (X);

             i++;

        END_ForEachRow;

   DestroyEnvSql();

 Fileclose(1);

**

** envoyer le fichier ........

** send file

**

If (i > 0)

then

SendMailExt("bergoux@wanadoo.fr",

"bergoux@wanadoo.fr",

"bergoux@wanadoo.fr",Filewrite,

"Suivi Filtre avec un automate..",

"Automate  ... Nouveau fichier de suivi AUTOMATISE : check + Envoi Mail

\\n Ph Bergougnoux

\\ liste des Teams  :

dupont,durand,toto,simon",$DIALOGOUTLOOK:1);

end_if;

display"appuyer sur une touche p/terminer :"  ;

enter;

 Connect_MSEXCEL(file1:Visible);

  Connect_MSEXCEL(Filewrite:Visible);

** Exemple  : SQL avec Excel and endUser4gl

** *****************************************************************************************

** Cet exemple permet de fusionner plusieurs fichiers Excel en 1 seul

** +

** crée un fichier Excel avec un critère : regrouper tous les DUPONT trouvés dans 1 seul fichier

** + envoi un mail du fichier DUPONT

**

** This example makes it possible to only amalgamate several Excel files into 1

** + creates an Excel file with a criterion: to gather all DUPONT found in 1 only file

** Send Mail Dupont file

#Include "c:\EndUser4gl\4gl\hlp\IncludeSysEU\includesyseu.h"

char  : file1:255 := "C:\EndUser4gl\Excel\Appli3Fusion\Input1.xls" ;

char  : file2:255 := "C:\EndUser4gl\Excel\Appli3Fusion\Input2.xls" ;

char  : file3:255 := "C:\EndUser4gl\Excel\Appli3Fusion\Input3.xls" ;

INTEGER : Msgsql,X,Messagsql,i;

CHAR : Table1:255 := "C:\EndUser4gl\Excel\Appli3Fusion\OutputFusion.xls" ;

char :  Filewrite:255 := "C:\EndUser4gl\Excel\Appli3Fusion\OutPutDupont.xls" ;

        STRUCT : Ref

                        {

                        CHAR fa:10, s1:2 :="\\t",** ou appuyer sur la touche TAB

                        fb: 15 ,    s2:2 :="\\t",

                        fc:12 ,     s3:2 :="\\t",

                        fd:10,      s4:2 :="\\t",

                        fe :20 ,    s5:2 :="\\t",

                                    s10:2 :="\\n" ; ** ou appuyer sur la touche return (entrée)

                        }

Tableexist(Table1) returning (x) ;

DISPlay "....." ; SetColorText(Hwdisplay:black,yellow) ;

display "Fusion : Excel";

/* 2 return codes */

** x = 0 : Ok , x = 1 Not ok */

if (x void) then

Tabledelete (Table1) ;

Display " TableDelete Ok " ,Table1 Column 60;

ELSE

        Display "Table Delete Not find";

END_If ;

 SetFileAttribute(Filewrite:FILE_ATTRIBUTE_NORMAL);

                Fileopen(1,Filewrite,Write) Returning (X) ;

SetColorText(Hwdisplay:red,blue) ;

Display " Cet exemple permet de fusionner plusieurs fichiers Excel en 1 seul";

Display " + ";

Display "crée un fichier Excel avec un critère : regrouper tous les DUPONT trouvés dans 1 seul fichier";

Display "envoi un mail du fichier DUPONT";

 skip_line 3;

SetColorText(Hwdisplay:green,black ) ;

Display "This example makes it possible to only amalgamate several Excel files into 1";

Display " + ";

Display "creates an Excel file with a criterion: to gather all DUPONT found in 1 only file ";

Display "Send a mail Dupont file";

** ****************************************************************************************

i:=0;

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");

  display"1 file :",file1 ;

   setEnvDatabaseSQL{Xls}(file1);

        ExecuteSQL{Xls}(" SELECT [Feuil1$].NOM as 'NAME1' ,[Feuil1$].Prenom as 'Prenom/surname' ,

                                 [Feuil1$].VILLE as 'Ville/town' ,[Feuil1$].PAYS as 'Pays/Contry'  

                          into  C:\EndUser4gl\Excel\Appli3Fusion\OutputFusion.Input1  

                          from  [Feuil1$]    ")   returning(x);

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  WHERE [Feuil1$].NOM LIKE '%DUPONT%' ")

        ForEachRowFind :

                 ref.fa := GetDataColumn: 8,CHAR;

                 ref.fb := GetDataColumn: 9,CHAR;               

                 Ref.fc  := GetDataColumn: 10,CHAR;

                 Ref.fd  := GetDataColumn: 11,CHAR;

                 Ref.fe  := "File INPut1";

            i++;

                  Fileadd(1,Ref) Returning (X);

        END_ForEachRow;

 DestroyEnvSql();

** ****************************************************************************************

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");

  display"2 file :",file2 ;

   setEnvDatabaseSQL{Xls}(file2);

          ExecuteSQL{Xls}(" SELECT [Feuil1$].NOM as 'NAME1' ,[Feuil1$].Prenom as 'Prenom/surname' ,

                                 [Feuil1$].VILLE as 'Ville/town' ,[Feuil1$].PAYS as 'Pays/Contry'  

                          into  C:\EndUser4gl\Excel\Appli3Fusion\OutputFusion.Input2  

                          from  [Feuil1$]    ")   returning(x);

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  WHERE [Feuil1$].NOM LIKE '%DUPONT%' ")

        ForEachRowFind :

                 ref.fa := GetDataColumn: 8,CHAR;

                 ref.fb := GetDataColumn: 9,CHAR;               

                 Ref.fc  := GetDataColumn: 10,CHAR;

                 Ref.fd  := GetDataColumn: 11,CHAR;

                 Ref.fe  := "File INPut2";

                      i++;

                            Fileadd(1,Ref) Returning (X);

       END_ForEachRow;

 DestroyEnvSql();

** ****************************************************************************************

SetVerSQL{Xls}("Excel 97/2000");

SetVerDriverSQL{Xls}("790");

  display"3 file :",file3 ;

   setEnvDatabaseSQL{Xls}(file3);

          ExecuteSQL{Xls}(" SELECT [Feuil1$].NOM as 'NAME1' ,[Feuil1$].Prenom as 'Prenom/surname' ,

                                 [Feuil1$].VILLE as 'Ville/town' ,[Feuil1$].PAYS as 'Pays/Contry'  

                          into  C:\EndUser4gl\Excel\Appli3Fusion\OutputFusion.Input3  

                          from  [Feuil1$]    ")   returning(x);

        ExecuteSQLFetch{Xls}(" SELECT * From [Feuil1$]  WHERE [Feuil1$].NOM LIKE '%DUPONT%' ")

        ForEachRowFind :

                 ref.fa := GetDataColumn: 8,CHAR;

                 ref.fb := GetDataColumn: 9,CHAR;               

                 Ref.fc  := GetDataColumn: 10,CHAR;

                 Ref.fd  := GetDataColumn: 11,CHAR;

                 Ref.fe  := "File INPut3";

                      i++;

                            Fileadd(1,Ref) Returning (X);

                     END_ForEachRow;

 DestroyEnvSql();

 Fileclose(1);

         If (i >0) then

                        SetColorText(Hwdisplay:red,BLACK ) ;

                display "trouvé/find..DUPONT :",i ;

                  display " appuyer sur une touche ENTREE..pour CONTINUER";

** envoyer un mail pour KO

SendMailExt("bergoux@wanadoo.fr",

"bergoux@wanadoo.fr",

"bergoux@wanadoo.fr",Filewrite,

"Suivi des DUPONT avec un automate..: Philippe.. ",

"Automate Check DUPONT Nouveau fichier AUTOMATISE : check + Envoi Mail

\\n Philippe ",$DIALOGOUTLOOK:1);

                 enter;

         end_if;

DISPLAY "END..........  .";

Display "appuyer sur entrée p/terminer";

enter;

   MSEXCEL_Connect(table1:Visible);  

   MSEXCEL_Connect(Filewrite:Visible);