× The internal search function is temporarily non-functional. The current search engine is no longer viable and we are researching alternatives.
As a stop gap measure, we are using Google's custom search engine service.
If you know of an easy to use, open source, search engine ... please contact support@midrange.com.



John mentions the POI Java class - we use Scott Klement's *SRVPGM that lets you call the methods from RPG. Using it you can read and write directly from and to Excel files, whether in the older XLS format or the current XLSX.

Yes, they both look like "binary garbage", as you described it - heh!! The older XLS format is a binary one - the new XLSX is, in fact, a compressed archive - a ZIP file with a different extension. If you see the letters PK at the start, that's a ZIP file.

But reading both, well, use Scott's service program - actually quite easy. We use it to read from a customer file, then update a few cells and send it back.Here's the link to his articles and the download - http://www.scottklement.com/poi/

Several folks on these lists have used this tool - the sample source code gives you a good start.

This service program approach, well, I don't recommend it for bulk reading or writing - it can be rather slow. A call from RPG to a full Java class that does it all would be much faster.

There is a commercial solution from RJS Software Systems, where I used to work - RPG2SQL - it is also a service program, basically - it makes calls to a middleware server that runs Excel, uses its functions, then returns data to your RPG or COBOL program - again, with a spreadsheet of much size, it's not the fastest thing on 4 feet but is very doable - as I recall, there's a command that reads an entire spreadsheet directly into a PF. www.rjssoftware.com is your starting point. Cost, IIRC, isn't exorbitant at all.

HTH
Vern

On 11/10/2014 12:40 PM, John Yeung wrote:
On Mon, Nov 10, 2014 at 1:19 PM, Chris Bipes
<chris.bipes@xxxxxxxxxxxxxxx> wrote:
Can you give an example of a program that can open the
Excel spreadsheet and extract the data? If I can process
directly, then I will skip the conversion.
The most common recommendation for those who have the capability and
resources to write custom code is to use Java, using Apache's POI-HSSF
library. There are RPG wrappers for this, if direct Java coding is a
problem.
-snip-

As an Amazon Associate we earn from qualifying purchases.

This thread ...

Replies:

Follow On AppleNews
Return to Archive home page | Return to MIDRANGE.COM home page

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.