Solved

Creating an Excel sheet with Tabs from iSeries

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

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

Active Directory Webinar

We all know we need to protect and secure our privileges, but where to start? Join Experts Exchange and ManageEngine on Tuesday, April 11, 2017 10:00 AM PDT to learn how to track and secure privileged users in Active Directory.

Question has a verified solution.

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

In case you ever have to remove a faulty web part from a page , add the following to the end of the page url ?contents=1
With User Account Control (UAC) enabled in Windows 7, one needs to open an elevated Command Prompt in order to run scripts under administrative privileges. Although the elevated Command Prompt accomplishes the task, the question How to run as script…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

828 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