Solved

Creating an Excel sheet with Tabs from iSeries

Posted on 2011-03-17
3
1,201 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
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 34

Assisted Solution

by:Gary Patterson
Gary Patterson earned 250 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

Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

Question has a verified solution.

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

Suggested Solutions

The question appears often enough, how do I transfer my data from my old server to the new server while preserving file shares, share permissions, and NTFS permisions.  Here are my tips for handling such a transfer.
February 24, 2017 — On February 23, Travis Ormandy, a vulnerability researcher at Google, reported on Twitter (https://twitter.com/taviso/status/834900838837411840) that massive stores of data have been leaked by CloudFlare, a company that provide…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
Although Jacob Bernoulli (1654-1705) has been credited as the creator of "Binomial Distribution Table", Gottfried Leibniz (1646-1716) did his dissertation on the subject in 1666; Leibniz you may recall is the co-inventor of "Calculus" and beat Isaac…

777 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