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.
... View more
