Re: How to create an Excel with multiple Tabs from AS400? -- RPG400-L

Hi Nisha;

You can simply produce your Excel sheet in XML format. In this sample I have attached is an IceBreak RPG program that is producing the Excel sheet. You can however do the same trick in plain RPGLE with some string operations.

________________________________

Advanced use of the SQL_Execute_Callback()

Suppose you want to produce an Excel spread sheet on the fly base on XML and all data is loaded using SQL. In that case SQL_Execute_Callback() build-in function is perfect. This solution has two components:

• A XML template file
• The ASPX source using SQL_Execute_Callback()

First let's take a look at the XML template. This template is produced by saving a spreadsheet from Excel in XML format. Then we have modified all dynamic data to be referred to by static include and tag names.

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40";

xmlns:x2="http://schemas.microsoft.com/office/excel/2003/xml";

xmlns:udc="http://schemas.microsoft.com/data/udc";

xmlns:xsd="http://www.w3.org/2001/XMLSchema";

xmlns:udcxf="http://schemas.microsoft.com/data/udc/xmlfile";>

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

<Version>11.8132</Version>

</DocumentProperties>

<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">

<WindowHeight>10995</WindowHeight>

<WindowWidth>21360</WindowWidth>

<WindowTopX>0</WindowTopX>

<WindowTopY>645</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

<FutureVer>11</FutureVer>

</ExcelWorkbook>

<Styles>

<Style ss:ID="Default" ss:Name="Normal">

<Alignment ss:Vertical="Bottom"/>

<Borders/>

<Font/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

<Style ss:ID="s21">

<Font ss:Bold="1"/>

</Style>

<Style ss:ID="s22">

<NumberFormat/>

</Style>

<Style ss:ID="s23">

<NumberFormat ss:Format="@"/>

</Style>

<Style ss:ID="s24">

<NumberFormat ss:Format="Short Date"/>

</Style>

</Styles>

<Worksheet ss:Name="<%= SheetName %>">

<Names>

<NamedRange ss:Name="_FilterDatabase" ss:RefersTo="=<%= SheetName %>!R1C1:R<%= %char(Rows+1) %>C<%= %char(Cols) %>" ss:Hidden="1"/>

</Names>

<Table ss:ExpandedColumnCount="<%= %char(Cols) %>" ss:ExpandedRowCount="<%= %char(Rows+1) %>" x:FullColumns="1" x:FullRows="1">



<Column ss:Width="<% = %char(len * 2) %>"/>



<Row>



<Cell ss:StyleID="s21"><Data ss:Type="String"><% = sqlHdr(i).sqlColName %></Data><NamedCell ss:Name="_FilterDatabase"/></Cell>



</Row>



<Row>



<Cell ss:StyleID="<% = style %>"><Data ss:Type="<% = type %>"><% = CellValue %></Data><NamedCell ss:Name="_FilterDatabase"/></Cell>



</Row>



</Table>

<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">

<Selected/>

<Panes>

<Pane>

<Number>1</Number>

<RangeSelection>R1C1:R<%= %char(Rows+1) %>C<%= %char(Cols) %></RangeSelection>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<x2:MapInfo x2:HideInactiveListBorder="false">

<x2:Schema x2:ID="Schema1" x2:Namespace="">

<xsd:schema xmlns:xsd="http://www.w3.org/2001/XMLSchema";>

<xsd:element nillable="true" name="resultset">

<xsd:complexType>

<xsd:sequence minOccurs="0">

<xsd:element minOccurs="0" maxOccurs="unbounded" nillable="true" name="row" form="unqualified">

<xsd:complexType>



<xsd:attribute name="<% = sqlHdr(i).SqlColName %>" form="unqualified" type="xsd:<% = type %>"></xsd:attribute>



</xsd:complexType>

</xsd:element>

</xsd:sequence>

</xsd:complexType>

</xsd:element>

</xsd:schema>

</x2:Schema>

<x2:Map x2:ID="resultset_Map" x2:SchemaID="Schema1" x2:RootElement="resultset">

<x2:Entry x2:Type="table" x2:ID="1" x2:ShowTotals="true">

<x2:Range><%= SheetName %>!R2C1:R<%= %char(Rows+1) %>C<%= %char(Cols) %></x2:Range>

<x2:HeaderRange>R1C1</x2:HeaderRange>

<x:FilterOn>True</x:FilterOn>

<x2:XPath>/resultset/row</x2:XPath>



<x2:Field x2:ID="<% = sqlHdr(i).SqlColName %>">

<x2:Range>RC[<%= %char(i-1) %>]</x2:Range>

<x2:XPath>@<% = sqlHdr(i).SqlColName %></x2:XPath>

<x2:XSDType><% = type %></x2:XSDType>

<ss:Cell/>

<x2:Aggregate>None</x2:Aggregate>

</x2:Field>



</x2:Entry>

</x2:Map>

</x2:MapInfo>



</Workbook>

This is a bit long, however that is required to make a XML list in Excel. Please note that the template is prepared for static include so RPG variables and buldings are used in the template

Now let's have a look at the RPGLE - ASPX code.

<%

/include qasphdr,sqlvar

d*Name++++++++++ETDsFrom+++To/L+++IDc.Keywords+++++++++++++++++++++++++++++Comments++++++++++++

d Sql_Cmd s 1024 varying

D start s 10i 0

D limit s 10i 0

d sheetname s 32 varying

d style s 16 varying

d type s 32 varying

d q s 1 inz('''')

D i s 10i 0

D len s 10i 0

d SqlHdr ds dim(200) likeds(I_sqlHdr)

d RenderCell pr n

d CellValue 4096 varying

d Row 10i 0 value

d Rows 10i 0 value

d Col 10i 0 value

d Cols 10i 0 value

d SqlVar likeds(I_sqlvar)

d CallbackHead pr

d Cols 10i 0 value

d SqlHdr dim(200) likeds(I_sqlHdr)

/free

//' -------------------------------------------------------------------------------------------

//' Mainline

//' -------------------------------------------------------------------------------------------

//' These 3 lines causes:

//' 1) Excel to open it

//' 2) prompt for a download

//' 3) Keep the temporary file to stay in the cache until excell has it open

SetContentType('application/vnd.ms-excel; charset=utf-8');

SetHeader ('content-disposition'

: 'attachment; filename=AnXMLexcelList.xls');

SetCacheTimeout(10);

sheetname = 'Sheet1';

start = 1;

limit = 99999; //' allways all !!!

Sql_Cmd = 'Select * '

+ 'from product ';

SQL_Execute_Header(

sql_cmd:

%paddr(CallbackHead)

);

SQL_Execute_CallBack(

sql_cmd: //' The Select * from ...

Start: //' From row number in the result set where 1 is the first

Limit: //' Maximum number of rows returned in the resultset

%paddr(RenderCell) //' Custom Cell rendering call-back function

);

*inrt = *on;

/end-free

//' -------------------------------------------------------------------------------------------

//' This is called once when the coloumn names are ready

//' we only copy the header array to a global array for later use

//' -------------------------------------------------------------------------------------------

p*name++++++++++..b...................keywords+++++++++++++++++++++++++++++comments++++++++++++

p CallbackHead B

d PI

d Cols 10i 0 value

d Sql_Hdr dim(200) likeds(I_sqlHdr)

d i s 10i 0

d comma s 1 varying

/free

SqlHdr = Sql_Hdr;

/end-free

p CallbackHead E

//' -------------------------------------------------------------------------------------------

//' The renderCell is called for each cell in the result set. here we produce the complete XML

//' -------------------------------------------------------------------------------------------

p*name++++++++++..b...................keywords+++++++++++++++++++++++++++++comments++++++++++++

p RenderCell B

d PI N

d CellValue 4096 varying

d Row 10i 0 value

d Rows 10i 0 value

d Col 10i 0 value

d Cols 10i 0 value

d SqlVar likeds(I_sqlvar)

/free

//' Row zero is the header only. from row = 1 etc it also contains data

//' So we build the initial XML stuff when the first column is detected

if (row = 0 and col = 1);

%><%

for i = 1 to cols;

len = %len(%trim(sqlHdr(i).sqlColName)) * 4;

if ( sqlHdr(i).SqlLen > len) ;

len = sqlHdr(i).SqlLen;

endif;

%><%

endfor;

%><%

for i = 1 to cols;

%><%

endfor;

%><%

//' from row = 1 and forward it also contains data from the resultset

//' we build each data cell here

elseif (row > 0);

if (col = 1);

%><%

endif;

select;

when sqlHdr(col).SqlType >= 480

and sqlHdr(col).SqlType <= 490;

type = 'Number';

style = 's22';

when sqlHdr(col).SqlType >= 491

and sqlHdr(col).SqlType <= 501;

type = 'Number';

style = 's22';

other;

type = 'String';

style = 's23';

endsl;

%><%

if (col = cols);

%><%

endif;

//' row -1 is the EOF indication. This event always comes once at last

//' here we finalize the XML with all the xPath stuff

elseif (row = -1 );

%><%

for i = 1 to cols;

select;

when sqlHdr(i).SqlType >= 480

and sqlHdr(i).SqlType <= 490;

type = 'double';

when sqlHdr(i).SqlType >= 491

and sqlHdr(i).SqlType <= 501;

type = 'integer';

other;

type = 'string';

endsl;

%><%

endfor;

%><%

for i = 1 to cols;

select;

when sqlHdr(i).SqlType >= 480

and sqlHdr(i).SqlType <= 490;

type = 'double';

when sqlHdr(i).SqlType >= 491

and sqlHdr(i).SqlType <= 501;

type = 'integer';

other;

type = 'string';

endsl;

%><%

endfor;

%><%

%><%

endif;

//' Return *ON as long a you want to itterate. If you want to break the loop then return *OFF

return *ON;

/end-free

p RenderCell

Best regards

Niels Liisberg

Chief Software Architect

System & Method

Håndværkersvinget 8
DK-2970 Hørsholm
Phone: +45 70 20 30 10

Fax: +45 70 20 30 11
Direct: +45 45 166 135
Mobile: +45 31 158 861
E-mail: nli@xxxxxxxxxxxxxxxxx
Web: www.system-method.com and www.Icebreak.org

“Everything should be made as simple as possible, but not simpler.”
— Albert Einstein

From: Nisha Ramesh <n4nisha@xxxxxxxxx<mailto:n4nisha@xxxxxxxxx>>
Reply-To: "RPG programming on the IBM i (AS/400 and iSeries)" <rpg400-l@xxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxx>>
Date: Mon, 7 Oct 2013 16:50:09 +0200
To: RPG programming on the IBM i / System i <rpg400-l@xxxxxxxxxxxx<mailto:rpg400-l@xxxxxxxxxxxx>>
Subject: How to create an Excel with multiple Tabs from AS400?

Hi all,

I have a requirement to create a multi tab Excel sheet from AS400. The tabs
are based on 2 different files which are different in structure. To create
a single tab excel, i have done with CrtCsvDta and then ftp the file in the
desired location and access the csv from that location using iseries
navigator. I know this technique wont work for muli tab Excel sheet.
Anybody came across same situation ?

Regards
Nisha
--
This is the RPG programming on the IBM i (AS/400 and iSeries) (RPG400-L) mailing list
To post a message email: RPG400-L@xxxxxxxxxxxx<mailto:RPG400-L@xxxxxxxxxxxx>
To subscribe, unsubscribe, or change list options,
visit: http://lists.midrange.com/mailman/listinfo/rpg400-l
or email: RPG400-L-request@xxxxxxxxxxxx<mailto:RPG400-L-request@xxxxxxxxxxxx>
Before posting, please take a moment to review the archives
at http://archive.midrange.com/rpg400-l.