Solved

Store entire Excel spreadsheet on as/400?

Posted on 2004-09-14
5
503 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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

Article by: Justin
In light of the WannaCry ransomware attack that affected millions of Windows machines, you might wonder if your Mac needs protecting. Yes, it does and here is how to do it.
This article outlines some of the reasons why an email message gets flagged as spam on a recipient's end.
If you're a developer or IT admin, you’re probably tasked with managing multiple websites, servers, applications, and levels of security on a daily basis. While this can be extremely time consuming, it can also be frustrating when systems aren't wor…
This is my first video review of Microsoft Bookings, I will be doing a part two with a bit more information, but wanted to get this out to you folks.

688 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