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
|