Create Excel XLS with RPG

[QMAGIC]

Anyone have an example of RPG creating an Excel XLS file?

I have seen one that calls Java routines. Like to stay away from java
if I can.

I know how simple it is to create CSV files that Excel can open, but
client being PIA and wants a true Excel file.

I know I can use CA file transfer to create it but I want it to be
100% automated on the AS400. (It creates it in BIFF format which would
also be ok.)

After the file gets to the IFS I plan on having our program email it
out to the client.

Thanks in advance,
Dan

[Bradley V. Stone]

I don't know if microsoft publishes the format for excel (like they do
for RTF), but you may want to start by searching groups.google.com for
that answer. Chances are if it's out there, someone will have posted
a link to it.

Brad

[Leslie S. Russell]

Go here http://www.wotsit.org/search.asp , search for XLS. They have the
file format for BIFF and XLS up to Office 97.

"Bradley V. Stone" <bvs...@onebox.com> wrote in message
news:3f4bbc39....@news.gotocrystal.net...

[james durr]

Hello,

You can save yourself some time and check out our CVTDBFXLS command at
http://www.kemetech.com

It creates multi-sheet spreadsheets with fixed columns, headings and
formulas.

James Durr

[Andi Goering]

Dan,

yes it is possible to generate Excel's BIFF format at the AS/400 even
with RPG.

We offer a solution called **iExcelGen**, see http://www.goering.de
for more details, demo videos and free evaluation copys.

It means many man-years of development for us to finish....

and development always go on with new features.

So, if I could give you advice: Join us or any of the few other
competitors, dont try to "re-invent the wheel".

Best regards

Andreas Goering
GOERING iSeries Solutions

dann...@cox.net (QMAGIC) wrote in message news:<b2431aa9.0308...@posting.google.com>...

[Richard Schoen]

If you're open to a commercial solution, take a look at our RPG2SQL
Integrator.

You can write RPG or COBOL programs that can read/write Excel Spreadsheets
and other non-AS/400 databases such as MS SQL Server, Oracle, etc.

For more info, check out our web site or contact me directly.

Regards,
Richard Schoen
RJS Software Systems Inc.
"The AS/400 and iSeries Report and Data Delivery Experts"
Email: ric...@rjssoftware.com
Web Site: http://www.rjssoftware.com

"QMAGIC" <dann...@cox.net> wrote in message
news:b2431aa9.0308...@posting.google.com...

[Àlex Corretgé]

Hi, at http://sc.openoffice.org/excelfileformat.pdf you can get a good
manual about XLS file format specifications, and BIFF's rules and
tech.

fins aviat...
Àlex Corretgé

dann...@cox.net (QMAGIC) wrote in message news:<b2431aa9.0308...@posting.google.com>...

[QMAGIC]

Thanks for everyone's input.

I had searched Google, Search400 etc. and had not found an example.

I did check out the specifications for creating the BIFF 8 format
defined by Microsoft. After looking at it I'm impressed that Excel
ever worked. It covers everything anyone would ever want to do in a
spreadsheet. Not simple. That's why I was hoping someone had broken it
down to just the barebones that you need for data. For those that
have not looked at the specifications before I want to show an example
of the (I consider) goofy stuff they do in it.

All data items containing more than one byte are stored using the
Little-Endian method. That means the least significant byte is stored
first and the most significant byte last. This applies for all data
types like 16-bit integers, 32-bit integers, floating-point values and
Unicode characters.

Example: The 32-bit integer value 13579BDFH is converted into the byte
sequence DFH 9BH 57H 13H.

Anyway until I have lots of time I'll let the user spent 5 min a day
doing the process manually.

Thanks again,
Dan

Sorry not interested in a commercial solution.

[Leslie S. Russell]

Q,

I know this might be a stupid idea but why not do something like this;

1. Spit report data out into a physical file.
2. Copy physical file to QDLS or somewhere so the end result is a CSV.
3. Map a drive to that folder.
4. Use PC Organizer to open an excel spreadsheet.
5. Have a macro in the spread sheet import the CSV file into excel.

This will pretty much automate the entire process for you. However, in this
case I would still suggest Monarch or some 3rd party app because ... well
look at the code involved...

Sub importdelimited(sFile as String, sTable as String, sDelim as String)
'
' import a delimited text file to a table
'
Dim objDB As DAO.Database
Dim objRST As DAO.Recordset
Dim objFS As FileSystemObject
Dim objTS As TextStream
Dim sLine As String
Dim iDelim As Integer
Dim iLast As Integer
Dim sField As String
Dim ifield As Integer

' open recordset and text file
Set objDB = CurrentDb
Set objRST = objDB.OpenRecordset(sTable, dbOpenTable)
Set objFS = CreateObject("Scripting.FileSystemObject")
Set objTS = objFS.OpenTextFile(sFile, ForReading)
' loop over lines in textfile
sLine = objTS.ReadLine
Do While Not objTS.AtEndOfStream
' parse line into new record
objRST.AddNew
iLast = 1
ifield = 1 ' skip field 0
iDelim = InStr(1, sLine, sDelim)
' process all fields ending with a delimiter
Do While iDelim > 0
sField = Mid(sLine, iLast, iDelim - iLast)
If Len(sField) > 0 Then _
objRST.Fields(ifield).Value = sField
iLast = iDelim + 1
ifield = ifield + 1
iDelim = InStr(iLast, sLine, sDelim)
Loop
' process field ending with end of line
sField = Mid(sLine, iLast)
objRST.Fields(ifield).Value = sField
objRST.Update
sLine = objTS.ReadLine
Loop
' close down
objTS.Close
Set objFS = Nothing
objRST.Close
Set objDB = Nothing

End Sub

You might also check with MicroSloth and see what help they can be.
http://support.microsoft.com/default.aspx?scid=fh;en-us;xlw2k

"QMAGIC" <dann...@cox.net> wrote in message
news:b2431aa9.0308...@posting.google.com...