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.
 

// global Excel object

var
XL = null;

// start Excel
function XL_start(options) {

    // start Excel
    XL = guixt.CreateObject("Excel.Application");

    // possible options: "visible"
    if (!options) options = "";
    options = options.toLowerCase();

    XL.Visible = (options.indexOf("visible") >= 0);
}

// copy textfile (CSV format) as new worksheet with given name
function XL_copy_textfile(filename, sheetname) {

  
var prevsheet = null;
   if (XL.ActiveWorkBook) {

        prevsheet = XL.ActiveWorkBook.Sheets(XL.ActiveWorkBook.Sheets.Count);
  
};

    var xlDelimited = 1;
    XL.WorkBooks.OpenText(filename, 65001, null, xlDelimited,
         null, null, null, null, null, null, true, ";");

    var wb = XL.ActiveWorkBook;
    wb.Sheets(1).Copy(null, prevsheet);
   
wb.Close(false);
    wb =
null;

    XL.ActiveWorkBook.ActiveSheet.Name = sheetname;
};

// save Excel file
function XL_save(filename) {

   
// file format
    var xlOpenXMLWorkbook = 51;

    // overwrite without prompt
    XL.DisplayAlerts = false; 

     // save whole workbook
    XL.ActiveWorkbook.SaveAs(filename, xlOpenXMLWorkbook);
}

// close Excel
function XL_close() {

   
XL.DisplayAlerts =
false;
    XL.ActiveWorkbook.Close(
false);
   
XL.Quit();
    XL = null;
}

 

InputScript
 
 

 // 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