Solved

Store entire Excel spreadsheet on as/400?

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
debugger with zend php iseries 3 213
ODBC Linked Server - AS/400 data truncated in SQL Server but fine in MS Query 2 152
Access to SYSIBM tables 7 97
SQL400 max size 5 79
Microsoft Office Picture Manager was included in Office 2003, 2007, and 2010, but not in Office 2013. Users had hopes that it would be in Office 2016/Office 365, but it is not. Fortunately, the same zero-cost technique that works to install it with …
Some code to ensure data integrity when using macros within Excel. Also included code that helps secure your data within an Excel workbook.
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
Migrating to Microsoft Office 365 is becoming increasingly popular for organizations both large and small. If you have made the leap to Microsoft’s cloud platform, you know that you will need to create a corporate email signature for your Office 365…

920 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

15 Experts available now in Live!

Get 1:1 Help Now