×
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-
midrange.com
