asked on

Create Excelfile from AS/400 batch program

Hi experts,

I have a RPG program that generates MIME mail messages, and is able to include attachments. Now I have to include an excel attachment that has to be created with data from the AS/400.
Normaly I will do that with STRPCCMD to start RTOPCB.EXE, but the job that creates the mail, is an sceduled job, so no STRPCCMD can be used.

Q: Is there a posibility to create Excel files directly from the AS/400 or do I have to convince the user that he must be satisfied with a CSV file (Comma Sepperated Value).

regards,

Murph

ASKER CERTIFIED SOLUTION

daveslater

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Theo Kouwenhoven

ASKER

Hey Dave,

Thanks this is working (almost the way I like), do you also know how I can make the file to set the right column width instead of keeping the standard Excel width?

Regards of a happy programmer :-)

Shalom Carmel

Murphey,
We had the same problem - creating CSV files on an AS400 server.
We ended up creating Excel XML files instead. When given the extension of .XLS rather than .XML, Excel (XP version) opens them up with no problems at all.

here is a sample XML file - can be created on any platform:
Notice the Row elements - they contain the data.
Copy it into notepad, save with extension XLS, and voila...
Your best way to proceed is to create an Excel file formatted to your liking, save it as XML, and use it as a template to create in the application.
You can preset column width, colors, built in functions, print areas and much more.
It is quite simple to write XML, you actually treat it just like another report.

<?xml version="1.0"?>
<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet" xmlns:html="http://www.w3.org/TR/REC-html40"
xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"
xmlns:x="urn:schemas-microsoft-com:office:excel"
xmlns:o="urn:schemas-microsoft-com:office:office">
<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">
<Title>your title</Title>
<Subject>your subject</Subject>
<Author/>
<LastAuthor>ShalomC</LastAuthor>
<LastPrinted>2003-07-24T14:44:44Z</LastPrinted>
<Created>2003-07-24T13:18:26Z</Created>
<Company>Acme Labs</Company>
<Version>10.4219</Version>
</DocumentProperties>
<OfficeDocumentSettings xmlns="urn:schemas-microsoft-com:office:office">
<RelyOnVML/>
<AllowPNG/>
<DownloadComponents/>
<LocationOfComponents HRef="/"/>
</OfficeDocumentSettings>
<ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel">
<WindowHeight>8385</WindowHeight>
<WindowWidth>14940</WindowWidth>
<WindowTopX>120</WindowTopX>
<WindowTopY>225</WindowTopY>
<ProtectStructure>False</ProtectStructure>
<ProtectWindows>False</ProtectWindows>
</ExcelWorkbook>
<Styles>
<Style ss:Name="Normal" ss:ID="Default">
<Alignment ss:Vertical="Bottom"/>
<Borders/>
<Font x:CharSet="177"/>
<Interior/>
<NumberFormat/>
<Protection/>
</Style>
<Style ss:ID="ReportHeader">
<Alignment ss:WrapText="1" ss:Vertical="Bottom"/>
<Borders>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
</Borders>
<Font ss:Bold="1" x:Family="Swiss"/>
<Interior ss:Pattern="Solid" ss:Color="#C0C0C0"/>
</Style>
<Style ss:ID="Regular">
<Borders>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Bottom"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Left"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Right"/>
<Border ss:Weight="1" ss:LineStyle="Continuous" ss:Position="Top"/>
</Borders>
</Style>
</Styles>
<Worksheet ss:Name="Tamlugim">
<Table>
<Row>
<Cell ss:StyleID="ReportHeader"><Data ss:Type="String">ZIP Code</Data></Cell>
<Cell ss:StyleID="ReportHeader">
<Data ss:Type="String">City</Data>
</Cell>
</Row>
<Row><Cell><Data ss:Type="String">01234</Data></Cell><Cell><Data ss:Type="String">Frankfurt</Data></Cell></Row>
<Row><Cell><Data ss:Type="String">05678</Data></Cell><Cell><Data ss:Type="String">Beijing</Data></Cell></Row>
</Table>
<WorksheetOptions xmlns="urn:schemas-microsoft-com:office:excel">
<Selected/>
<ProtectObjects>False</ProtectObjects>
<ProtectScenarios>False</ProtectScenarios>
</WorksheetOptions>
</Worksheet>
</Workbook>

Theo Kouwenhoven

ASKER

Using XML isn't working with Excel2000.

Using an HTML file (with extention .XLS) is working fine.