How to set custom sheet name for XLS, XLSPLAIN, ODS, FODS, PFODS, NATIVE_FDOCX and NATIVE_PFDOCX

Owned by martinajindrova
Last updated: Aug 15, 2015

XFRX#INIT and direct output

loSession=EVALUATE([xfrx("XFRX#INIT")])
&& if each report has only one page, you can suppress page number in sheet name  
loSession.setOtherParams("ADDPAGENUMBERTOSHEETDISPLAYNAME",.F.) && since XFRX 15.6
loSession.setOtherParams("NEXT_SHEET_NAME","First Report")
loSession.ProcessReport(lcPath+"_reports\basetext") 
loSession.setOtherParams("NEXT_SHEET_NAME","Second Report") 
loSession.ProcessReport(lcPath+"_reports\baseshapes")...

 

XFRX#INIT, direct output to XFF and transform to or XFRX#LISTENER...

XFRX#INIT

loXFF=EVALUATE([xfrx("XFRX#DRAW")]) 
loXFF.OpenDocument("out.xff")
loSession=EVALUATE([xfrx("XFRX#INIT")]) 
loSession.setOtherParams("ADDPAGENUMBERTOSHEETDISPLAYNAME",.F.) && since XFRX 15.6 
loHACK=CREATEOBJECT("_XFRXHACK",loSession)
loSession.TransformReport(loXFF)
RELE loHACK

 XFRX#LISTENER

loSession=EVALUATE([XFRX("XFRX#LISTENER")])
lnRetVal = loSession.SetParams("temp.xls",,,,,,"XLS")
IF lnRetVal = 0
   loSession.setOtherParams("ADDPAGENUMBERTOSHEETDISPLAYNAME",.F.)
   loHACK=CREATEOBJECT("_XFRXHACK",loSession)
   REPORT FORM (lcPath+"_reports\firstreport") NOPAGEEJECT object loSession
   REPORT FORM (lcPath+"_reports\secondreport") NOPAGEEJECT NORESET object loSession
   REPORT FORM (lcPath+"_reports\thirdreport") NOPAGEEJECT NORESET object loSession
   REPORT FORM (lcPath+"_reports\fourthreport") NORESET object loSession
   RELE loHACK
ENDIF  

 HACK class

DEFINE CLASS _XFRXHACK AS CUSTOM
   oEWriter=.NULL.
   oEDoc=.NULL.
   nPageno=0
   nReportno=0
   cTarget=""
   nVersion=0
   lXLS=.F.
   
   PROCEDURE GetReportName(m.tnReportNo)
      RETURN IIF(This.nReportNo=1,"First Report",IIF(This.nReportNo=2,"Second Report",;
                  IIF(This.nReportNo=3,"Third Report","Fourth Report")))
   ENDPROC

   ROCEDURE Init(m.loSession)
      m.loSession=IIF(TYPE("m.loSession.oxfSession")="O",m.loSession.oxfSession,m.loSession)
      This.nVersion=VAL(CHRTRAN(m.loSession.GetVersion(),;
                        CHRTRAN(m.loSession.GetVersion(),"0123456789",""),""))
      This.cTarget=m.loSession.cTarget
      This.lXLS=This.cTarget=="XLS" OR This.cTarget=="XLSPLAIN"
      =BINDEVENT(m.loSession.oDocumentWriter,"NewPage",This,"NewPage",0)
      =BINDEVENT(m.loSession.oDocumentWriter,"NewReport",;
                 This,"NewReport",;
                 IIF(This.cTarget=="XLS" OR This.cTarget=="XLSPLAIN",0,1))
      This.oEWriter=m.loSession.oDocumentWriter
      This.oEDoc=m.loSession.oDocument
   ENDPROC

   PROCEDURE newReport(m.tnReportNo, m.tcCodePage, m.toReport)
      This.nPageno=0
      This.nReportno=m.tnReportNo
      IF This.lXLS
         This.oEWriter.cNextSheetName=This.GetReportName(This.nReportNo)
      ELSE
         This.oEDoc.cNextSheetName=This.GetReportName(This.nReportNo)
         IF This.nVersion<1560000
            IF TYPE("This.oEWriter.oOXML")="O"
               This.oEWriter.oOXML.lAddPageNumberToSheetDisplyName=.F.
            ELSE
               IF TYPE("This.oEWriter.oOO")="O"
                  This.oEWriter.oOO.lAddPageNumberToSheetDisplyName=.F.
               ENDIF
            ENDIF
         ENDIF
      ENDIF
   ENDPROC

   PROCEDURE newPage(m.tnHeight, m.tnWidth, m.tnPageNo, m.tnOri)
      This.nPageno=This.nPageno+1
      IF This.lXLS
         This.oEWriter.cNextSheetName=This.GetReportName(This.nReportNo)+ ;
                                       IIF(This.nPageno=1,""," "+LTRIM(STR(This.nPageno)))
      ELSE
         This.oEDoc.cNextSheetName=This.GetReportName(This.nReportNo)+ ;
                                     IIF(This.nPageno=1,""," "+LTRIM(STR(This.nPageno)))
      ENDIF
   ENDPROC
 ENDDEFINE
 

 Using NEXT_SHEET_NAME_EXPR

Full suport  for XLS/XLSPLAIN and XFRX#INIT + TranformReport() or XFRX#LISTENER since XFRX 15.6

loSession=EVALUATE([XFRX("XFRX#LISTENER")])
lnRetVal = loSession.SetParams(laOuts(lii,2),,NOT llOpen,"1250",,,laOuts(lii,3))
IF lnRetVal = 0 
   loSession.setOtherParams("NEXT_SHEET_NAME_EXPR","_HACKSHEETNAME(this)")
   REPORT FORM (lcPath+"_reports\firstreport") NOPAGEEJECT object loSession 
   REPORT FORM (lcPath+"_reports\secondreport") NOPAGEEJECT NORESET object loSession 
   REPORT FORM (lcPath+"_reports\thirdreport") NOPAGEEJECT NORESET object loSession 
   REPORT FORM (lcPath+"_reports\fourthreport") NORESET object loSession 

ENDIF

PROCEDURE _HACKSHEETNAME(m.loReport)
   RETURN IIF(m.loReport.tnReportNumber=1,"First Report",;
          IIF(m.loReport.tnReportNumber=2,"Second Report",;
          IIF(m.loReport.tnReportNumber=3,"Third Report","Fourth Report")))+;
          IIF(m.loReport.nCurrentReportPageNo=1,""," "+;
              LTRIM(STR(m.loReport.nCurrentReportPageNo)))
ENDPROC