Solved

Creating an Excel sheet with Tabs from iSeries

Posted on 2011-03-17
3
1,181 Views
Last Modified: 2012-05-11
Hi,

We have an application which uses COBOL and/or RPG on AS400. We have a requirement in which we need to create an excel from a batch program. We have done this before by simply creating a physical fie from the COBOL program with comma separated field. This is then FTPed to the destination as a .csv file. In the most current requirement that we have got, we have been asked to create various reports and put them in a single excel sheet under various tabs.

Is there any way, I can achieve this?

Thanks in advance for any assistance.

Regards
Ali.  
0
Comment
Question by:bhagatali
3 Comments
 
LVL 13

Accepted Solution

by:
_b_h earned 250 total points
Comment Utility

Check out this link:
http://www.code400.com/forum/showthread.php/7017-create-XML-from-RPG-read-as-EXCEL

It discusses generating XML with some excel wrapping that should do the trick.
Here is a workbook with sheets Sheet1 and Sheet2, each with some sample cells, and saved as XML spreadsheet.
You could use a similar template to write out an excel to the IFS.


<?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">
 <DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
  <Author>Lenovo User</Author>
  <LastAuthor>My User</LastAuthor>
  <Created>2011-03-18T01:46:40Z</Created>
  <Company>My Company</Company>
  <Version>14.00</Version>
 </DocumentProperties>
 <OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
  <AllowPNG/>
 </OfficeDocumentSettings>
 <ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
  <WindowHeight>8445</WindowHeight>
  <WindowWidth>19035</WindowWidth>
  <WindowTopX>360</WindowTopX>
  <WindowTopY>135</WindowTopY>
  <ActiveSheet>1</ActiveSheet>
  <ProtectStructure>False</ProtectStructure>
  <ProtectWindows>False</ProtectWindows>
 </ExcelWorkbook>
 <Styles>
  <Style ss:ID="Default" ss:Name="Normal">
   <Alignment ss:Vertical="Bottom"/>
   <Borders/>
   <Font ss:FontName="Arial" x:Family="Swiss" ss:Color="#000000"/>
   <Interior/>
   <NumberFormat/>
   <Protection/>
  </Style>
 </Styles>
 <Worksheet ss:Name="Sheet1">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">sheet 1 a1</Data></Cell>
    <Cell><Data ss:Type="String">sheet 1 b1 </Data></Cell>
    <Cell><Data ss:Type="String">sheet 1 c1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">sheet 1 a2</Data></Cell>
    <Cell><Data ss:Type="String">sheet 1 b2</Data></Cell>
    <Cell><Data ss:Type="String">sheet 1 c2</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Panes>
    <Pane>
     <Number>3</Number>
     <RangeSelection>R1C1:R2C3</RangeSelection>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
 <Worksheet ss:Name="Sheet2">
  <Table ss:ExpandedColumnCount="3" ss:ExpandedRowCount="2" x:FullColumns="1"
   x:FullRows="1">
   <Row>
    <Cell><Data ss:Type="String">sheet 2 a1</Data></Cell>
    <Cell><Data ss:Type="String">sheet 2 b1 </Data></Cell>
    <Cell><Data ss:Type="String">sheet 2 c1</Data></Cell>
   </Row>
   <Row>
    <Cell><Data ss:Type="String">sheet 2 a2</Data></Cell>
    <Cell><Data ss:Type="String">sheet 2 b2</Data></Cell>
    <Cell><Data ss:Type="String">sheet 2 c2</Data></Cell>
   </Row>
  </Table>
  <WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
   <PageSetup>
    <Header x:Margin="0.3"/>
    <Footer x:Margin="0.3"/>
    <PageMargins x:Bottom="0.75" x:Left="0.7" x:Right="0.7" x:Top="0.75"/>
   </PageSetup>
   <Selected/>
   <Panes>
    <Pane>
     <Number>3</Number>
     <ActiveRow>2</ActiveRow>
     <ActiveCol>2</ActiveCol>
    </Pane>
   </Panes>
   <ProtectObjects>False</ProtectObjects>
   <ProtectScenarios>False</ProtectScenarios>
  </WorksheetOptions>
 </Worksheet>
</Workbook>

Hope this helps!
Barry
0
 

Author Comment

by:bhagatali
Comment Utility
Thanks Barry, I will try this out.
0
 
LVL 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 250 total points
Comment Utility
I use the POI HSSF (or XSSF)  Java classes for this.  These Java classes handle the "heavy lifting" of reading and writing properly-formatted Excel spreadsheets for you.

You can instantiate them and use them from RPG (or Java, of course).  Very easy to use, IMO.

http://poi.apache.org/spreadsheet/index.html

Scott Klement has written some great articles specifically on the the subject of using POI HSSF in an AS/400 environment, including RPG.  He also supplies some handy helper tools, too.

http://www.scottklement.com/poi/

- Gary
0

Featured Post

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

This article will show you how to create an ISO CD-ROM/DVD-ROM image (*.iso), and MD5 checksum signature, for use with VMware vSphere Hypervisor 6.5 (ESXi 6.5). It's a good idea to compare checksums, because many installations fail because of a corr…
Restoring deleted objects in Active Directory has been a standard feature in Active Directory for many years, yet some admins may not know what is available.
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now