Purpose
Generate an Excel file with several worksheets

Solution
- Generate the data for each worksheet as text file (CSV format)
- Use JavaScript and Excel automation to create worksheets and import the data files

Example:
For the SAP purchasing source list (transactions ME01/ME03), we generate an Excel file which for each plant contains a separate worksheet with material, validation dates,  vendor number, vendor name:

 

JavaScript functions
The following JavaScript functions are very general so that they can easily be used for similar tasks.
 

JavaScript

 

InputScript
  

GuiXT 
 // determine all plants in table EORD
CreateTable V[plants] plant
Set V[table] "EORD"
Set V[fields] "WERKS"
Set V[orderby] "WERKS"
Set V[ABAPTRUE] "X"

Call /guixt/dbselect _
  export.table="table" _
  export.fields="fields" _
  export.orderBy="orderby" _
  export.distinct="ABAPTRUE" _
  import.values="plants"

// temporary file
Set V[tempfile] "&%[TEMP]\xl.&V[today_y-m-d hms].csv"

// Excel output file
Set V[xlfile]  "E:\temp\xltest.xlsx"

// fields needed from EORD and LFA1
CreateTable V[slist] werks matnr vdatu bdatu lifnr name1

Set V[table] "EORD join LFA1 on LFA1~LIFNR = EORD~LIFNR"
Set V[fields] "EORD~WERKS,EORD~MATNR,EORD~VDATU type D,
                         EORD~BDATU type D,EORD~LIFNR,LFA1~NAME1"
Set V[orderby] "EORD~WERKS,EORD~MATNR,EORD~VDATU"

// start Excel
CallJS XL_start

// plant index
Set V[k] 0

label next_plant

if V[k<&V[plants.rowcount]]
  Set V[k] &V[k] + 1
  Set V[current_plant] "&V[plants.plant.&V[k]]"
  Set V[condition] "EORD~WERKS = '&V[current_plant]' "

  // read plant data
  Call /guixt/dbselect _
    export.table="table" _
    export.fields="fields" _
    export.condition="condition" _
    export.orderBy="orderby" _
    import.values="slist"

   // generate text file (CSV-format)
  CopyText fromTable=V[slist] toText="temp" delimiter=";"
  CopyText fromText="temp" toFile="&V[tempfile]"  -utf8

  // copy as new worksheet
  CallJS XL_copy_textfile "&V[tempfile]" "Plant &V[current_plant]"

  // next one
  goto next_plant
endif

CallJS XL_save  "&V[xlfile]"
CallJS XL_close

// remove temp file
RemoveFile "&V[tempfile]"

Message "Excel file &V[xlfile] generated" -statusline
Return

 

.zip file with JavaScript and InputScript

 

Components InputAssistant+Controls