Re: Iseries file to an Excel spreadsheet

Your memory is correct, Pete. You can indeed create an area of the spreadsheet that refreshes itself. In fact, you can then use that area as the basis of a VLOOKUP function which would allow you to enter a value into a cell (say an item number) and then retrieve one or more fields (such as description, or price) dynamically from that rectangle.

So, Willie, you have a few basic choices here.

1. Dump the data directly to a comma-delimited file. Also know as a CSV, Excel recognizes these files and can open them. However, you don't get all the cool bells and whistles of a real spreadsheet.

2. Modify or create a real .XLS file. This takes rather more programming and a library that understands the Excel format. Libraries exist for both Java and RPG (the latter is HSSFR4, one of Scott Klement's fantastic creations).

3. Add a dynamic database connection to an existing spreadsheet. This would refresh every time you open the spreadsheet.

Those are the three main ones. There's also a fourth one that isn't used that much but is actually pretty cool:

4. Create a simple HTML table and display it in an HTTP server. The user can then right click on it to download it as a spreadsheet. We do that for a couple of our data lists; it's easier than finding a unique spot on the network for the user to open.

Which seems closest to your needs? How is the spreadsheet going to be used?

Joe

Depending upon the approach, Midrange-L may be the best place to post.....

There is quite of bit of history in the archives on the subject which is
may be why there wasn't much of a response.

There is a data tool for Excel that comes as part of IBM i Access. If I
recall correctly, there is a way to define the range of cells within the
sheet that you want to update and all you need to do (again, if I recall
correctly) is to open the spreadsheet and that will trigger a query that
will update the sheet. I am a little fuzzy on those details because I
abandoned MS Office products completely about two years ago.

You could write an RPG program that would update the sheet as well.
Scott Klement has some excellent tools that you can use to manipulate
spreadsheet data from RPG. http://www.scottklement.com/poi/

There are many ways to skin the cat. I don't know where (OS) and
how(language) you want to update the spreadsheet, but updating the
spreadsheet is more difficult than creating one so you'll have a bit of
a challnge to come up with an approach that works in your particular
scenario.

My only question was your reference to a "DCL", what is that?

Pete Helgren
Value Added Software, Inc
www.asaap.com
www.opensource4i.com


On 2/8/2011 2:07 PM, Willie J. Moore wrote:

I have posted this on the Midrange-L site with no response. I thought I would try here and see if I have better luck.
I have a file on the iseries and an Excel spreadsheet.
Spreadsheet name=open percentages.xa.2011, worksheet name=wek%2003.
I want to copy the iseries file into the spreadsheet, replacing the data in the worksheet. I have a 'DCL' with the spreadsheets name. I am having trouble replacing the spreadsheet data with the file data.
File layout: date, total orders, total items, prod due& prod due %.
Excel spreadsheet: date, total orders, total items, prod due& prod due %.

Any help would be greatly appreciated.

Thank you,

William Moore
California Fine Wire
805-489-5144
wjmoore@xxxxxxxxxxxx