How to convert as400 data fl to .xls file

[ldb]

Hi Everyone,
I have been sucessful converting both flat files(sys/36 files) and external described files to .csv files and then sending them to various sites via email or FTP using commands in a CL program. Some sites require "true" .xls files. I know that there is software that can be purchased to do this, my client already has it. However, I would like to know if I can do it directly on the 400. Here are the commands I use to convert an external defined file to .csv

1.) Copy data file into a source file to add delimiters.
CPYTOIMPF FROMFILE(QS36F/WBDATA) TOFILE(QS36F/WBSRC) MBROPT(*REPLACE) RCDDLM(*EOR) DTAFMT(*DLM) STRDLM(*DBLQUOTE) FLDDLM(',')
2.) Then copy the source file which now contains data and delimiters into a stream file on the IFS
CPYTOSTMF FROMMBR('/QSYS.LIB/QS36F.LIB/WBSRC.FILE/WBSRC.MBR') TOSTMF('/FLTRNSFR/WBSRC.CSV') STMFOPT(*REPLACE) DBFCCSID(*FILE) STMFCODPAG(00819) ENDLINFMT(*CRLF)

This works fine. To make it convert to a .xls file is it simply a matter of changing the file extension and stmfcodpag? Is so, what is the code? Or else how is it done?

Thanks for your help.
ldb

[GLS400]

Hi ldb:

To make it convert to a .xls file is it simply a matter of changing the file extension and stmfcodpag? Is so, what is the code?

Not quite that simple....google "HSSF" and "POI".
(HSSF stands for horrible spread sheet function.)

Best of Luck
GLS

[jamief]

This is an example of writing XML directly to the IFS...Will open with excel
http://www.code400.com/forum/showthr...-read-as-EXCEL

jamie

[ldb]

I am sorry I must be missing something.... My current .csv files that I create can be opened by excel, everthing appears in columns and rows as it should be. However, for some reason (I am not sure why), certain sites request an .xls file. I was looking to take the data from my externally defined native 400 file convert it to a file with an .xls ending, and whatever else it needed to be called an excel file. Which by the way, I have no idea what else it needs to be called an excel file. Do you? Also, I would need to do this in batch, not interactive, as I am doing now for my other file conversions. I am not sure what XML has to do with it. Please keep in mind that I am soley an RPG coder and that may be why I'm not getting it. :-)

[jamief]

Microsoft Excel auto opens XML as an excel spread sheet...

[BenThurley]

Excel opens CSV files anyway doesn't it?

If it was just a one off then I would use the "Data transfer from iSeries server" utility that comes with the client access/navigator package.

It sounds like you want something to run in batch though. In this case you'll have to roll your sleeves up. I would have a read of this article as a start.
http://systeminetwork.com/node/60954

[Rico]

Easy400.net has a good open source tool HSSFCGI, the TABLEXLS command works well.

[jamief]

@ Ben - csv does open automagically, but with XML you can create headings, font changes, Tabs ..........
I should have been more specific.

jamie