The SAS Output Delivery System and reporting techniques

ODS EXCEL AND REPORT WRITING INTERFACE

Reply
Occasional Contributor
Posts: 8

ODS EXCEL AND REPORT WRITING INTERFACE

[ Edited ]

Hello everybody,

 

This is the first time i post a comment !

I'm trying to produce a report on EXCEL 2010 using ODSOUT (RWI).

 

My firts point is I can not manage to produce my report on a single sheet because there are two DATA _NULL_ that hamper the one sheet production. The first one produce a banner with the name of the company.

The second DATA _NULL_, procduce the "body" results.

 

I would like to get a single sheet report but I don't know how to do. May be there are some default options that hamper the report.

And my styles are not taken into account. Particularly the Border. I don't know why !

 

I'm under 9.4M2 at work and at home I tried under the university edition.

 

I post my program below, it is running without error :

 

 

/*
	PREMIERS TESTS DE PROD DES RAPPORTS SOUS EXCEL
*/
DATA page2 ;
INFILE DATALINES DELIMITER='*' TRUNCOVER DSD  ;
LENGTH  _info_ $4 level $1. sort 8. var1 $80. var2 $12. var3 $200. var4 $200.var5 $200. var6 $200. tableau $1. bloc 8  titreTableau $55. ;
LABEL var1 ="^_" var2 = "Volumes" var3 ="^S={FONTWEIGHT=BOLD}NPS" var4 ="Promotors" var5 ="Neutral" var6 ="Détractors" ;
INPUT _info_ $ level $ sort var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ tableau $ bloc titreTableau $ ;
DATALINES;
bold*5*.*Résultats mensuels DG ASS (mai - 2014)*23502*37|+*51%|+*35%|0*14%|+*A*0*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*1*DIR ASS1*229*31|+*50%|=*31%|0*19%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*2*DIR ASS2*4105*37|+*52%|+*33%|0*15%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*3*DIR ASS3*1156*21|+*38%|+*44%|0*17%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*4*DIR ASS4*16272*38|+* 51%|+*35%|0*13%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*5*DIR ASS5*367*10|-*36%|=*38%|0*26%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*6*DIR ASS6*343*41|+*53%|=*35%|0*12%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*7*DIR ASS7*1030*45|=*57%|=*31%|0*12%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Comparaison mois précédent (avril - 2014)**38*52%*34%*14%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*2*Comparaison année précédente (mai - 2013)**29*45%*38%*16%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Moyenne annuelle cumulée**37*51%*35%*14%*C*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
;
RUN ;

%LET mypath = %SYSFUNC(PATHNAME(WORK));
%PUT &mypath. ;
ODS _ALL_ CLOSE ;
/* pour éviter un WARNING */
GOPTIONS DEV=PNG ;
ODS ESCAPECHAR="^";
/* modèles pour l'ODS */
/* ODS PATH(PREPEND) RWI.firm RWI.graph ;  <== ne fonctionne apparemment pas ! Voir plus bas
   retester avec les styles et modèles dans la work ! 
*/
ODS EXCEL FILE = "&mypath./test_ReportRWI_Excel.xlsx" 
OPTIONS( /*START_AT ="1,1" <== uniquement en 9.4M3 */
         EMBEDDED_TITLES = "yes"
         SHEET_NAME="Recommandation"
         TAB_COLOR = "cx94D2DE"
        )
/*STYLE = excel*/; /* <== s'impose à tous les autres styles mais il peut être modifié dans une tabulate, report ou print */
;
/*ODS TEXT="^S={FONTSIZE=4PT} Pretext via ODS TEXT" ;*/
/* ODS TEXT="^_" ;*/

DATA _NULL_ ;
	bg = " BACKGROUNDCOLOR=cx94D2DE " ;
     DECLARE ODSOUT o () ;
	 o.TABLE_START (STYLE_ATTR:"FRAME=VOID RULES=NONE") ;
	   /*o.ROW_START () ;
	   	     o.FORMAT_CELL(DATA:"^_", STYLE_ATTR:"FONTSIZE=2PT BACKGROUNDCOLOR=red ", COLUMN_SPAN:4) ;
           o.ROW_END () ;*/
	   o.ROW_START () ;
	     o.FORMAT_CELL(DATA:"SAS &SYSVER.", 
                       STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=#E64415"!!bg, ROW_SPAN:2) ;
             o.FORMAT_CELL(DATA:"Période^_de^_mesure^_:^nmai 2014", 
                       STYLE_ATTR:"JUST=LEFT VJUST=TOP "!!bg, ROW_SPAN:2) ;

	     o.FORMAT_CELL(DATA:"DIRECTION GENERALE FIRM", 
						STYLE_ATTR:"WIDTH=72.5% JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg) ;
	   o.FORMAT_CELL(DATA:"FIRM", 
                       STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=BLACK"!!bg, ROW_SPAN:2) ;
           o.ROW_END () ;
	   o.ROW_START () ;
	     o.FORMAT_CELL(DATA:"I- CUSTOMERS EXPERIENCE - Recommandation FIRM", 
						STYLE_ATTR:"JUST=CENTER FONTSIZE=12PT FONTSTYLE=ITALIC"!!bg) ;
	   o.ROW_END () ;
	 o.TABLE_END () ;
RUN ;

    DATA _NULL_ ;
    SET work.page2 END=eof ;
	    BY bloc ;
	    ARRAY val_pastille $ var3-var6 ;
	    vide = " FONTSIZE=1PT BACKGROUNDCOLOR=WHITE " ;
	    space = vide!!" WIDTH=1MM " ;
	    calage = '  '  ;
	    IF _N_=1 THEN DO ;
		    DECLARE ODSOUT o () ;    
		    o.TABLE_START () ; 
		     o.ROW_START (TYPE:"H") ;
		      o.FORMAT_CELL(DATA:"^_", ROW_SPAN:2, INHIBIT:"TBLR", STYLE_ATTR:space) ;
		      o.FORMAT_CELL(DATA:"^_", COLUMN_SPAN:2, INHIBIT:"TBLR", STYLE_ATTR:vide) ;
		      o.FORMAT_CELL(DATA:"^_", INHIBIT:"TL", STYLE_ATTR:space) ;
		      o.FORMAT_CELL(DATA:titreTableau, COLUMN_SPAN:4) ;
		     o.ROW_END () ;
		     o.ROW_START (TYPE:"H") ;
		      o.FORMAT_CELL(DATA:"^_", INHIBIT:"TL" , STYLE_ATTR:vide) ;
		      o.FORMAT_CELL(DATA:VLABEL(var2) , STYLE_ATTR:"WIDTH=2CM") ;
		      o.FORMAT_CELL(DATA:"^_", INHIBIT:"TB" , STYLE_ATTR:space) ;
		    DO OVER val_pastille ;
		      o.FORMAT_CELL(DATA:VLABEL(val_pastille) ) ;
	    	END ;
	         o.ROW_END () ;
	    END ; 
	    IF FIRST.bloc AND _N_ > 1 THEN DO ;
		    o.ROW_START (TYPE:"B") ;
		    DO i=1 TO DIM(val_pastille)+4 ;
		     o.FORMAT_CELL(DATA:"^_", INHIBIT:"LR", STYLE_ATTR:"FONTSIZE=6PT PADDING=0 BORDERWIDTH=0") ;
		    END ;
		    o.ROW_END () ;
	    END ;
	    LENGTH inhib $ 2 val $ 6 pastille $ 1 gras $ 20 ;
	    IF FIRST.bloc AND LAST.bloc THEN inhib=" " ;
	    ELSE IF FIRST.bloc THEN inhib="B" ;
	    ELSE IF LAST.bloc THEN inhib="T" ;
	    ELSE inhib="BT" ;
	    LENGTH mEf $ 40 ;
	    IF _info_="boldgray" THEN mEf = " FONT_WEIGHT=BOLD BACKGROUND=cxDEDEDE " ;
	    ELSE mEf = "" ;
	    SELECT (tableau) ;
	    WHEN ("A") DO ;
		     o.ROW_START (TYPE:"B") ;
		      o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BTL" , STYLE_ATTR:space) ;
		      o.FORMAT_CELL(DATA:var1 , INHIBIT:inhib , STYLE_ATTR:mEf!!IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" TEXTALIGN=LEFT") ;
		      o.FORMAT_CELL(DATA:STRIP(var2)!!"^_^_" , INHIBIT:inhib , STYLE_ATTR:mEf!!" ASIS=ON TEXTALIGN=RIGHT") ;
		      o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BT" , STYLE_ATTR:"WIDTH=2MM BACKGROUND=WHITE") ;
		    DO OVER val_pastille ;
			    val = SCAN(val_pastille,1,"|") ;
			    pastille = SCAN(val_pastille,2,"|") ;
			    gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
			    o.FORMAT_CELL(DATA:CATS("^_^_",val,"^_^_") , INHIBIT:inhib 
			          , STYLE_ATTR:mEf!!gras!!IFC(pastille = "+"," POSTTEXT='^{STYLE[COLOR=GREEN FONT_WEIGHT=BOLD] ^{unicode 2B06}}' JUST=L ",
			                                  IFC(pastille = "-"," POSTTEXT='^{STYLE[COLOR=RED   FONT_WEIGHT=BOLD] ^{unicode 2B07}}' JUST=L ",
			                                  IFC(pastille = "="," POSTTEXT='^{STYLE[COLOR=DARKGRAY   FONT_WEIGHT=BOLD] ^{unicode 25CF}}' JUST=L "
			                                  ,"")
			                                  ,"")
			                                  ,"") 
			                 );
		    END ;
		     o.ROW_END () ;
	    END ;
	    WHEN ("B") DO ;
		   o.ROW_START (TYPE:"B") ;
		    o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BTL" , STYLE_ATTR:space) ;
		    o.FORMAT_CELL(DATA:var1 , STYLE_ATTR:IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" JUST=L", COLUMN_SPAN:2) ;
		    o.FORMAT_CELL(DATA:"^_" , INHIBIT:"BT" , STYLE_ATTR:"WIDTH=2MM") ;
		    DO OVER val_pastille ;
			    gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
			    o.FORMAT_CELL(DATA:CATS("^_^_",val_pastille,"^_^_") , STYLE_ATTR:gras!!calage) ;
		    END ;
		   o.ROW_END () ;
	    END ;
	    OTHERWISE ;
	    END ;
	    IF eof THEN DO ;
	      o.TABLE_END() ;
	    END ;
    RUN ;
 
ODS EXCEL CLOSE ;

 

 

 

 

If anybody could help me to produce my report, il would be great.

 

 Amazigh.

 

 

 

 

SAS Super FREQ
Posts: 8,744

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi:
The RWI was initially designed to work with PDF and HTML, I believe. You might want to quickly check with Tech Support to find out whether this will work with ODS EXCEL or not. For example, this Tip Sheet: http://support.sas.com/rnd/base/ods/Tipsheet_RWI.pdf indicates that the RWI is only supported by HTML and PDF.

cynthia
Occasional Contributor
Posts: 8

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi Madame,

 

Thank You for your answer.

 

I read your last paper to help me with this report Smiley Happy.

 

I, initially produce this report with PDF in fact. All is wonderfull with PDF, the styles and borders are well recovered.

 

What a pity we can not do it with excel for now Smiley Frustrated

 

Amazigh.

SAS Super FREQ
Posts: 8,744

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi:
I'm glad my paper helped you. One thing you can try is using the ODS MSOFFICE2K destination, which is an HTML-based destination that uses Microsoft friendly HTML. Excel can open any HTML file (ever since Office 97) so there is a chance that with MSOFFICE2K, you MIGHT be able to create an HTML file that Excel can open. I have not tried it. I usually use RWI with PDF.

cynthia
Occasional Contributor
Posts: 8

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi,

 

In fact, to resolve my first issue, I add the EXCEL option  SHEET_INTERVAL = "NONE" Man Embarassed  and I got my one sheet report. I just have to modify the banner in the first DATA _NULL_ to fit with the "BODY" report in the second DATA _NULL_. 

 

My headache is the borders and the style that are not resolved.

 

The TAG_ATTR could handle both may be ?

 

Amazigh.

SAS Super FREQ
Posts: 8,744

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi, I doubt that TAGATTR will handle the borders. Typically, with Excel borders do not respond to ODS STYLE attribute level overrides. And TAGATTR is meant to impact a cell or a row usually, so impacting borders for the entire table with TAGATTR probably won't work as you envision. And, TAGATTR is an ODS EXCEL and ODS EXCELXP style attribute only. If you want to keep going with ODS Excel and the RWI, I fear that you will eventually run into something that you can't work around. This is why it's probably best to check with Tech Support and see whether they have any other tracks where people tried ODS EXCEL with the RWI.

cynthia
Occasional Contributor
Posts: 8

Re: ODS EXCEL AND REPORT WRITING INTERFACE

Hi,

 

I Sent a message to the support. The INHIBIT is not apply on the repot. To handle the border we have to try with

BORDERTOPCOLOR= BORDERTOPWIDTH= in the STYLE_ATTR attribute.

 

Amazigh.

 

Occasional Contributor
Posts: 8

Re: ODS EXCEL AND REPORT WRITING INTERFACE

[ Edited ]

Hi,

 

I succeed to draw my complex report with RWI and ODS EXCEL. To handle the borders, the INHIBIT statement is not apply and I replaced it by the BORDERBOTTOM(COLOR-WIDTH ), BORDERLEFT(COLOR-WIDTH), BORDERRIGHT(COLOR-WIDTH),

BORDERTOP(COLOR-WIDTH) in the STYLE_ATTR.

 

DATA page2 ;
INFILE DATALINES DELIMITER='*' TRUNCOVER DSD  ;
LENGTH  _info_ $8 level $1. sort 8. var1 $80. var2 $12. var3 $200. var4 $200.var5 $200. var6 $200. tableau $1. bloc 8  titreTableau $55. ;
LABEL var1 ="^_" var2 = "Volumes" var3 ="^S={FONTWEIGHT=BOLD}NPS" var4 ="Promotors" var5 ="Neutral" var6 ="Detractors" ;
INPUT _info_ $ level $ sort var1 $ var2 $ var3 $ var4 $ var5 $ var6 $ tableau $ bloc titreTableau $ ;
DATALINES;
boldgray*5*.*Résultats mensuels DG ASS (mai - 2014)*23502*37|+*51%|+*35%|0*14%|+*A*0*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*1*DIR ASS1*229*31|+*50%|=*31%|0*19%|-*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*2*DIR ASS2*4105*37|-*52%|+*33%|0*15%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*3*DIR ASS3*1156*21|+*38%|-*44%|0*17%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*4*DIR ASS4*16272*38|=* 51%|+*35%|0*13%|+*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*5*DIR ASS5*367*10|-*36%|=*38%|0*26%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*6*DIR ASS6*343*41|+*53%|+*35%|0*12%|-*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
*4*7*DIR ASS7*1030*45|-*57%|-*31%|0*12%|=*A*1*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Comparaison mois précedent (avril - 2014)**38*52%*34%*14%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*2*Comparaison année précédente (mai - 2013)**29*45%*38%*16%*B*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
bold*5*1*Moyenne annuelle cumulée**37*51%*35%*14%*C*2*^S={FONTWEIGHT=BOLD FONTSIZE=11PT}Recommandation FIRM
;
RUN ;

%LET mypath = %SYSFUNC(PATHNAME(WORK));
ODS _ALL_ CLOSE ;
GOPTIONS DEV=PNG ;
ODS ESCAPECHAR="^";
ODS EXCEL FILE = "&mypath./test_ExcelRWI.xlsx" 
OPTIONS( /*START_AT ="1,1"*/
         SHEET_INTERVAL  = "NONE"
         SHEET_NAME      = "Recommandation"
         TAB_COLOR       = "RED"
         ABSOLUTE_COLUMN_WIDTH ='8,42,55,2,6,11,8,11'
         GRIDLINES       ='NO'
         ZOOM           = "110"
        )

DATA _NULL_ ;
  bg     = " BACKGROUNDCOLOR=cx94D2DE " ;
  top    = " BORDERTOPCOLOR=cx94D2DE    BORDERTOPWIDTH=0    " ;
  bot    = " BORDERBOTTOMCOLOR=cx94D2DE BORDERBOTTOMWIDTH=0 " ;
  left   = " BORDERLEFTCOLOR=cx94D2DE   BORDERLEFTWIDTH=0   " ;
  right  = " BORDERRIGHTCOLOR=cx94D2DE  BORDERRIGHTWIDTH=0  " ;

     DECLARE ODSOUT o () ;
         o.TABLE_START (STYLE_ATTR:"FRAME=VOID RULES=NONE") ;
	   o.ROW_START () ;
	    o.FORMAT_CELL(DATA:"SAS &SYSVER.", 
                    STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=#E64415"!!bg!!top!!bot!!left!!right, ROW_SPAN:2) ;
            o.FORMAT_CELL(DATA:"Période^_de^_mesure^_: ^nmai 2014", 
                    STYLE_ATTR:"JUST=LEFT VJUST=MIDDLE FONTWEIGHT=BOLD "!!bg!!top!!bot!!left!!right, ROW_SPAN:2) ;
	    o.FORMAT_CELL(DATA:"DIRECTION GENERALE ASSURANCE", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;						
	    o.FORMAT_CELL(DATA:"FIRM", 
                    STYLE_ATTR:"JUST=CENTER VJUST=MIDDLE FONTWEIGHT=BOLD FOREGROUND=BLACK"!!bg!!top!!bot!!left!!right, ROW_SPAN:2) ;
         o.ROW_END () ;
         o.ROW_START () ;
	    o.FORMAT_CELL(DATA:"I- CUSTOMERS EXPERIENCE - Recommandation", 
					STYLE_ATTR:"JUST=CENTER FONTSIZE=12PT FONTSTYLE=ITALIC"!!bg!!top!!bot!!left!!right) ;
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;	
	    o.FORMAT_CELL(DATA:"^_", 
					STYLE_ATTR:" JUST=CENTER FONTSIZE=14PT FONTWEIGHT=BOLD"!!bg!!top!!bot!!left!!right) ;						
	
         o.ROW_END () ;
	 o.TABLE_END () ;
RUN ;

    DATA _NULL_ ;
    SET work.Page2 END=eof ;
	    BY bloc ;
	    ARRAY val_pastille $ var3-var6 ;
	    vide  = " FONTSIZE=1PT BACKGROUNDCOLOR=WHITE " ;
	    space = vide!!" WIDTH=1MM " ;
	    top   = " BORDERTOPCOLOR=#FFFFFF    BORDERTOPWIDTH=0    " ;
            bot   = " BORDERBOTTOMCOLOR=#FFFFFF BORDERBOTTOMWIDTH=0 " ;
            left  = " BORDERLEFTCOLOR=#FFFFFF   BORDERLEFTWIDTH=0   " ;
            right = " BORDERRIGHTCOLOR=#FFFFFF  BORDERRIGHTWIDTH=0  " ;
	    calage= '  '  ;
	    IF _N_=1 THEN DO ;
		    DECLARE ODSOUT o () ;    
		    o.TABLE_START () ; 
		     o.ROW_START (TYPE:"H") ;
		      o.FORMAT_CELL(DATA:"^_", ROW_SPAN:2,    STYLE_ATTR:space!!top!!bot!!left!!right) ;
		      o.FORMAT_CELL(DATA:"^_", COLUMN_SPAN:2, STYLE_ATTR:vide!!top!!bot!!left!!right) ;
		      o.FORMAT_CELL(DATA:"^_",                STYLE_ATTR:space!!top!!left!!bot) ;
		      o.FORMAT_CELL(DATA:titreTableau, COLUMN_SPAN:4, STYLE_ATTR: " BACKGROUNDCOLOR=#17375E FOREGROUND=WHITE FONTWEIGHT=BOLD " ) ;
		     o.ROW_END () ;
		     o.ROW_START (TYPE:"H") ;
		      o.FORMAT_CELL(DATA:"^_" , STYLE_ATTR:vide!!top!!left) ;
		      o.FORMAT_CELL(DATA:VLABEL(var2) , STYLE_ATTR:"BACKGROUNDCOLOR=#17375E FOREGROUND=WHITE ") ;
		      o.FORMAT_CELL(DATA:"^_", STYLE_ATTR:space!!top!!bot) ;
		    DO OVER val_pastille ;
		      o.FORMAT_CELL(DATA:VLABEL(val_pastille), STYLE_ATTR: " BACKGROUNDCOLOR=#17375E FOREGROUND=WHITE FONTWEIGHT=BOLD " ) ;
	    	END ;
	         o.ROW_END () ;
	    END ; 
	    /* IF _InfoPrec_="linebreak" THEN DO ; */
	    IF FIRST.bloc AND _N_ > 1 THEN DO ; /* on est au niveau du linebreak de _InfoPrec_ ==> insertion de la rigole blanche */
		    o.ROW_START (TYPE:"B") ;
		    DO i=1 TO DIM(val_pastille)+4 ;
		     o.FORMAT_CELL(DATA:"^_", STYLE_ATTR:"FONTSIZE=6PT PADDING=0 BORDERWIDTH=0 "!!top!!bot!!left!!right) ;
		    END ;
		    o.ROW_END () ;
	    END ;
	    LENGTH inhib $ 100 val $ 6 pastille $ 1 gras $ 20 ;
	    IF FIRST.bloc AND LAST.bloc THEN inhib=" " ;
	    ELSE IF FIRST.bloc THEN inhib=bot ;
	    ELSE IF LAST.bloc THEN inhib=top ;
	    ELSE inhib=bot!!top ;
	    LENGTH mEf $ 40 ;
	    IF _info_="boldgray" THEN mEf = " FONT_WEIGHT=BOLD BACKGROUND=cxDEDEDE " ;
	    ELSE mEf = "" ;
	    SELECT (tableau) ;
	    WHEN ("A") DO ;
		     o.ROW_START (TYPE:"B") ;
		      o.FORMAT_CELL(DATA:"^_" , STYLE_ATTR:space!!top!!bot!!left) ;
		      o.FORMAT_CELL(DATA:var1 , STYLE_ATTR:mEf!!IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" TEXTALIGN=LEFT"!!inhib);
		      o.FORMAT_CELL(DATA:STRIP(var2)!!"^_^_" , STYLE_ATTR:mEf!!" ASIS=ON TEXTALIGN=RIGHT"!!inhib) ;
		      o.FORMAT_CELL(DATA:"^_" , STYLE_ATTR:"WIDTH=2MM BACKGROUND=WHITE"!!bot!!top) ;
		    DO OVER val_pastille ;
			    val = SCAN(val_pastille,1,"|") ;
			    pastille = SCAN(val_pastille,2,"|") ;
			    gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
			    o.FORMAT_CELL(DATA:CATS("^_^_",val,"^_^_") 
			          , STYLE_ATTR:mEf!!gras!!IFC(pastille = "+"," POSTTEXT='^{STYLE[COLOR=GREEN FONT_WEIGHT=BOLD] ^{unicode 2B06}}' JUST=L ",
			                                  IFC(pastille = "-"," POSTTEXT='^{STYLE[COLOR=RED   FONT_WEIGHT=BOLD] ^{unicode 2B07}}' JUST=L ",
			                                  IFC(pastille = "="," POSTTEXT='^{STYLE[COLOR=DARKGRAY   FONT_WEIGHT=BOLD] ^{unicode 25CF}}' JUST=L "
			                                  ,"")
			                                  ,"")
			                                  ,"")!!inhib 
			                 );
		    END ;
		     o.ROW_END () ;
	    END ;
	    WHEN ("B","C") DO ;
		   o.ROW_START (TYPE:"B") ;
		    o.FORMAT_CELL(DATA:"^_" , STYLE_ATTR:space!!top!!bot!!left) ;
		    o.FORMAT_CELL(DATA:var1 , STYLE_ATTR:IFC(_info_="bold","FONT_WEIGHT=BOLD","")!!" JUST=L", COLUMN_SPAN:2) ;
		    o.FORMAT_CELL(DATA:"^_" , STYLE_ATTR:"WIDTH=2MM"!!top!!bot) ;
		    DO OVER val_pastille ;
			    gras = IFC(UPCASE(VNAME(val_pastille))="VAR3", "FONT_WEIGHT=BOLD ", "") ;
			    o.FORMAT_CELL(DATA:CATS("^_^_",val_pastille,"^_^_") , STYLE_ATTR:gras!!calage) ;
		    END ;
		   o.ROW_END () ;
	    END ;
	    OTHERWISE ;
	    END ;
	    IF eof THEN DO ;
	      o.TABLE_END() ;
	    END ;
    RUN ;
 
ODS EXCEL CLOSE ;

 

 

 

 

Amazigh.

Ask a Question
Discussion stats
  • 7 replies
  • 524 views
  • 2 likes
  • 2 in conversation