Solved

Store entire Excel spreadsheet on as/400?

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

How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

Join & Write a Comment

In this article, I will show you HOW TO: Install VMware Tools for Windows on a VMware Windows virtual machine on a VMware vSphere Hypervisor 6.5 (ESXi 6.5) Host Server, using the VMware Host Client. The virtual machine has Windows Server 2016 instal…
Is your company's data protection keeping pace with virtualization? Here are 7 dynamic ways to adapt to rapid breakthroughs in technology.
Here's a very brief overview of the methods PRTG Network Monitor (https://www.paessler.com/prtg) offers for monitoring bandwidth, to help you decide which methods you´d like to investigate in more detail.  The methods are covered in more detail in o…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

757 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

Need Help in Real-Time?

Connect with top rated Experts

23 Experts available now in Live!

Get 1:1 Help Now