Page 1 of 1
Parameters from iSeries to Excel
Posted: Fri Feb 03, 2012 8:29 am
by sgoteti
I want to pass on the variables using the parameters to a project and use these paramaters to creat a excel row. The first time i execute the project, i want to create an excel and in subsequent runs i want to append to the same excel. I want to know how to map the parameter variables to the excel rowset. Please let me know how to do this.
Thanks
Re: Parameters from iSeries to Excel
Posted: Tue Feb 14, 2012 2:18 pm
by Support_Duane
The only way that I know of would be to place the variable values into a file, read the file and then write the Excel record.
Here's an example:
Code: Select all<project name="place variables in excel file" mainModule="Main" version="2.0">
<module name="Main">
<createWorkspace version="1.0" />
<setVariable label="set fld1" name="fld1" value="This is row one field one" version="2.0" />
<setVariable label="set fld2" name="fld2" value="This is row one field two" version="2.0" />
<setVariable label="set fld3" name="fld3" value="This is row one field three" version="2.0" />
<setVariable label="set fld4" name="fld4" value="This is row one field four" version="2.0" />
<setVariable label="set fld5" name="fld5" value="This is row one field five" version="2.0" />
<print file="temp.csv" append="false" version="1.0">
<![CDATA[${Concat(fld1, "|", fld2, "|", fld3, "|", fld4, "|", fld5)}]]>
</print>
<readCSV inputFile="temp.csv" outputRowSetVariable="data" fieldDelimiter="pipe" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" textQualifier="none" version="1.0" />
<writeExcel inputRowSetVariable="${data}" outputFile="/djohnson/outfile/excelTest.xlsx" whenFileExists="append" excelFormat="excel2007" version="2.0" />
<setVariable label="set fld1" name="fld1" value="This is row two field one" version="2.0" />
<setVariable label="set fld2" name="fld2" value="This is row two field two" version="2.0" />
<setVariable label="set fld3" name="fld3" value="This is row two field three" version="2.0" />
<setVariable label="set fld4" name="fld4" value="This is row two field four" version="2.0" />
<setVariable label="set fld5" name="fld5" value="This is row two field five" version="2.0" />
<print file="temp.csv" append="false" version="1.0">
<![CDATA[${Concat(fld1, "|", fld2, "|", fld3, "|", fld4, "|", fld5)}]]>
</print>
<readCSV inputFile="temp.csv" outputRowSetVariable="data" fieldDelimiter="pipe" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" textQualifier="none" version="1.0" />
<writeExcel inputRowSetVariable="${data}" outputFile="/djohnson/outfile/excelTest.xlsx" whenFileExists="append" excelFormat="excel2007" version="2.0" />
<setVariable label="set fld1" name="fld1" value="This is row three field one" version="2.0" />
<setVariable label="set fld2" name="fld2" value="This is row three field two" version="2.0" />
<setVariable label="set fld3" name="fld3" value="This is row three field three" version="2.0" />
<setVariable label="set fld4" name="fld4" value="This is row three field four" version="2.0" />
<setVariable label="set fld5" name="fld5" value="This is row three field five" version="2.0" />
<print file="temp.csv" append="false" version="1.0">
<![CDATA[${Concat(fld1, "|", fld2, "|", fld3, "|", fld4, "|", fld5)}]]>
</print>
<readCSV inputFile="temp.csv" outputRowSetVariable="data" fieldDelimiter="pipe" skipInvalidRecords="true" skipFirstRow="false" recordDelimiter="LF" textQualifier="none" version="1.0" />
<writeExcel inputRowSetVariable="${data}" outputFile="/djohnson/outfile/excelTest.xlsx" whenFileExists="append" excelFormat="excel2007" version="2.0" />
<deleteWorkspace version="1.0" />
</module>
</project>