topic Re: How to Create EXCEL File on Application Server in Application Development and Automation Discussions

How to Create EXCEL File on Application Server

Former Member — Mon, 17 May 2010 14:58:38 GMT

Hi,

I have a requirement to create an EXCEL file on Application Server. The program is supposed to run in background mode.

We have already explored the option to create tab delimited file with .XLS extension. But this is not acceptable.

It would be great if someone can explain a simple process for the same.

Thanks in Advance

Anurag

Edited by: AnuragK on May 17, 2010 5:01 PM

Re: How to Create EXCEL File on Application Server

Former Member — Mon, 17 May 2010 15:23:54 GMT

Hi,

You can create the file in XML format.

Create a XLS (in Excel) with your formats and needs. Save it as XML. Open this file with Nodepad.

Your program have to follow this format.

Best regards,

Leandro Mengue

Example:

<?xml version="1.0"?>

<?mso-application progid="Excel.Sheet"?>

<Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40">

<Author>bmengue</Author>

<LastAuthor>bmengue</LastAuthor>

<Company>ANDREAS STIHL Moto-Serras Ltda.</Company>

</DocumentProperties>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

...

<Table ss:ExpandedColumnCount="4" ss:ExpandedRowCount="4" x:FullColumns="1"

x:FullRows="1">

<Row>

</Row>

<Row>

</Row>

<Row>

</Row>

<Row>

</Row>

</Table>

...

replace A1,B2,C3,D4 by your values for cells A1,B2,C2,D4 ...

Re: How to Create EXCEL File on Application Server

former_member214857 — Mon, 17 May 2010 16:17:10 GMT

Hi Anurag

Actually, when you perform some action in background workprocesses they don´t execute actions related with some OLE objects. So is necessary create XLS file manually. You can check sintax for all fields and properties in http://msdn.microsoft.com/.

There is full hierarchy for this format.

I hope it helps

Carlos Machado

Re: How to Create EXCEL File on Application Server

Former Member — Mon, 17 May 2010 17:55:57 GMT

Hi,

Sorry, but I think that the method using ole_objects do not work in the server! (eg. background)... remember that the server may be an UNIX, ...

This code runs in the presentation server (SAP GUI):

DATA: excel TYPE ole2_object,

activeworkbook TYPE ole2_object,

workbooks TYPE ole2_object.

CREATE OBJECT excel 'Excel.application'.

CALL METHOD OF excel 'WORKBOOKS' = workbooks.

CALL METHOD OF workbooks 'OPEN'

EXPORTING #1 = 'C:\SAPTEMP\zke60010.xls'.

CALL METHOD OF excel 'ACTIVEWORKBOOK' = activeworkbook.

CALL METHOD OF activeworkbook 'SAVE''.

CALL METHOD OF excel 'QUIT'.

Best regards,

Leandro Mengue

Re: How to Create EXCEL File on Application Server

Former Member — Mon, 17 May 2010 18:56:03 GMT

Hello Anurag,

An alternative is to create a text file in application server where in each column is separated with a delimiter i.e. "#" or "," etc...

They just need to be opened using excel sheet, using delimters, after downloading from application server.

One tip in case you use a comma i.e, "," as a seperator is, in case a column value has a special character i.e. "," "." ":" etc.., then do not forget to embed such column in inverted double quotes.

Hope this helps us.

Pavan

Re: How to Create EXCEL File on Application Server

Former Member — Tue, 18 May 2010 11:25:59 GMT

Hi,

Thank you everybody for your inputs.

Regards

Anurag

Re: How to Create EXCEL File on Application Server

JerryWang — Wed, 19 May 2010 04:12:15 GMT

Hello AnuragK ,

In background processing you can not use OLE, but you can write a simple XSLT. I have similiar requirement: to write a report and run it in background, the report collects information and sends it as an excel to management team every day.

CALL TRANSFORMATION /SF002007/DEV_MONITOR
    SOURCE table = mt_excel_table
    RESULT XML xmlstr.

mt_excel_table is an internal table which are to be converted to excel file.

Re: How to Create EXCEL File on Application Server

JerryWang — Wed, 19 May 2010 04:13:08 GMT

?sap.transform simple?

?mso-application progid="Excel.Sheet"?

tt:transform xmlns:tt="http://www.sap.com/transformation-templates"

tt:root name="table"/

tt:template

Workbook xmlns="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:o="urn:schemas-microsoft-com:office:office"

xmlns:x="urn:schemas-microsoft-com:office:excel"

xmlns:ss="urn:schemas-microsoft-com:office:spreadsheet"

xmlns:html="http://www.w3.org/TR/REC-html40"

ExcelWorkbook xmlns="urn:schemas-microsoft-com:office:excel"

/ExcelWorkbook

Worksheet ss:Name="Sheet1"

Table

Column ss:Width="120"/

Column ss:Width="220"/

tt:loop ref=".table"

Row

Cell

Data ss:Type="String"

tt:value ref="DEV_ORDER_ID"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="DEL_ORDER_ID"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="CUSTOMER_NAME"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="CONTENT_TYPE"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="CONTENT_TITLE"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="DESCRIPTION"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="PLAN_DEL_DATE"/

/Data

/Cell

Cell

Data ss:Type="String"

tt:value ref="STATUS"/

/Data

/Cell

/Row

/tt:loop

/Table

/Worksheet

/Workbook

/tt:template

/tt:transform

Edited by: Jerry Wang on May 19, 2010 6:19 AM