Solved

Store entire Excel spreadsheet on as/400?

Posted on 2004-09-14
5
495 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
  • 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 250 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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

Suggested Solutions

There’s a good reason for why it’s called a homepage – it closely resembles that of a physical house and the only real difference is that it’s online. Your website’s homepage is where people come to visit you. It’s the family room of your website wh…
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.
In a recent question (https://www.experts-exchange.com/questions/28997919/Pagination-in-Adobe-Acrobat.html) here at Experts Exchange, a member asked how to add page numbers to a PDF file using Adobe Acrobat XI Pro. This short video Micro Tutorial sh…

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