?
Solved

Store entire Excel spreadsheet on as/400?

Posted on 2004-09-14
5
Medium Priority
?
504 Views
Last Modified: 2006-11-17
Dear Experts:

Is is possible to copy an Excel spreadsheet (with formatting and everything) to and from the iSeries?

Thanks,
BrianMc1958
0
Comment
Question by:BrianMc1958
[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
  • 2
  • 2
5 Comments
 

Author Comment

by:BrianMc1958
ID: 12058860
Addendum:

If so, is there any way to load data into it (from an existing comma-delimited file) WHILE STILL ON THE ISERIES?  

Basically, I am trying to leverage an existing data-extraction program by having an Excel spreadsheet pop up on the user's desktop with the results.  I would need a spreadsheet with formatting and some VBA code resident on the iSeries.  Then I'd try to fill it with the data, and "push" it onto the desktop...

Any ideas?

Thanks,
BrianMc1958  
0
 

Author Comment

by:BrianMc1958
ID: 12058932
Addendum #2:

The copying of the spreadsheet FROM the iSeries to the PC will need to be done programatically--say from CL.

Sorry.  Will try to THINK more before posting questions...

--BrianMc1958
0
 
LVL 14

Expert Comment

by:daveslater
ID: 12062378
Hi
I have not herd of any such solution but there is shareware out there in Java that can read and write in Excel format.

I am not a java programmer but I am sure that some one with a bit of Java on the AS/400 could create a solution.

Dave
0
 
LVL 16

Expert Comment

by:theo kouwenhoven
ID: 12075041
Hi BrianMc1958,

Creating Excel sheets is possible on the AS400 by the following way:
( I do this for EXCEL-2000)

Write the output from your file to an file in HTML format, but with the extention XLS.
now you can sed this by mail or put it on a server to give the user access to this sheet.

Formatting colunms is posible but limited, I do it with a template (in a SRCPF) that looks like this.

<HTML>                                              
<TABLE border="1" cellpadding="0" cellspacing="0">  
<COLUMNGROUP>                                      
<COL width=100 align="Left">      
<COL width=300 align="left">      
<COL width=100 align="center">
<COL width=100 align="right">        
</COLUMNGROUP>                    
<TR><TD><B>Customer number</B></TD>
<TD><B>Customer Name</B></TD>    
<TD><B>Period</B></TD>      
<TD><B>Tot.Sales</B></TD>        
##TABLE##
</TABLE>
</HTML>                                


Where the code ##TABLE## in the program is replaced by a loop that writes the following data:

e.g.
<TR><TD>123456</TD>
<TD>J.B Smith</TD>    
<TD>Sep 2004</TD>      
<TD>12345.67</TD>        


When you use windows XP you can create an Excel file the same way but XML is possible (never tried it my self)

Good luck
0
 
LVL 14

Accepted Solution

by:
daveslater earned 750 total points
ID: 12075465
Hi
Check out these babies available on the seriesnetwork
You must register first but the articals are free

http://www.iseriesnetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=17839
http://www.iseriesnetwork.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=18409


The sample source code that I've written for this article demonstrates
how to call this service program, and it also demonstrates a more
complex sample workbook -- including one that has multiple sheets --
and shows you how to use larger fonts and merge cells. You can
download this article from the iSeries Network Web site at the
following link:
http://www.iseriesnetwork.com/noderesources/code/clubtechcode/ExcelCrtDemo.zip .

More information about calling Java methods from RPG programs can be
found in the "WebSphere Development Studio ILE RPG Programmer's
Guide," which is in the Information Center at the following link:
http://publib.boulder.ibm.com/iseries/v5r2/ic2924/books/c0925074.pdf

The following is a link to the January 8, 2004, issue of this
newsletter, where I demonstrated reading an Excel file in RPG:
http://www.iseriesnetwork.com/resources/clubtech/index.cfm?fuseaction=ShowNewsletterIssue&ID=17839

Geert Van Landeghem also has a tutorial online that demonstrates how
to use these Java classes to create an Excel spreadsheet. You can read
his tutorial at the following link:
http://www.jasservices.com/articles/as400/001_genexcel.htm

Dave

0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

A while back, I ran into a situation where I was trying to use the calculated columns feature in SharePoint 2013 to do some simple math using values in two lists. Between certain data types not being accessible, and also with trying to make a one to…
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.
This course is ideal for IT System Administrators working with VMware vSphere and its associated products in their company infrastructure. This course teaches you how to install and maintain this virtualization technology to store data, prevent vuln…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

719 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