Solved

Creating an Excel sheet with Tabs from iSeries

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

[Webinar] Code, Load, and Grow

Managing multiple websites, servers, applications, and security on a daily basis? Join us for a webinar on May 25th to learn how to simplify administration and management of virtual hosts for IT admins, create a secure environment, and deploy code more effectively and frequently.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ODBC Informix Driver 11 150
Reading variable length EBCDIC in SAS 9 157
Storing REST data JSON to an iSeries (as400) IFS file 12 237
XML response optional elements 12 78
Unified and professional email signatures help maintain a consistent company brand image to the outside world. This article shows how to create an email signature in Exchange Server 2010 using a transport rule and how to overcome native limitations …
How many times a day do you open, acknowledge, or close an IT incident? What’s your process? Do you have a process depending on the incident, systems involved, and other factors? New Relic Alerts gives you options for how you interact with notifica…
Finding and deleting duplicate (picture) files can be a time consuming task. My wife and I, our three kids and their families all share one dilemma: Managing our pictures. Between desktops, laptops, phones, tablets, and cameras; over the last decade…
Suggested Courses

739 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