|
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
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
midrange.com
