Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

Creating an Excel sheet with Tabs from iSeries

Posted on 2011-03-17
3
Medium Priority
?
1,245 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
3 Comments
 
LVL 13

Accepted Solution

by:
_b_h earned 1000 total points
ID: 35162128

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
ID: 35162196
Thanks Barry, I will try this out.
0
 
LVL 35

Assisted Solution

by:Gary Patterson
Gary Patterson earned 1000 total points
ID: 35165997
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

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article, WatchGuard's Director of Security Strategy and Research Teri Radichel, takes a look at insider threats, the risk they can pose to your organization, and the best ways to defend against them.
The core idea of this article is to make you acquainted with the best way in which you can export Exchange mailbox to PST format.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

722 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