Community
Application Development and Automation Discussions
Join the discussions or start your own on all things application development, including tools and APIs, programming models, and keeping your skills sharp.
cancel
Turn on suggestions
Auto-suggest helps you quickly narrow down your search results by suggesting possible matches as you type.
Showing results for 
Search instead for 
Did you mean: 
Read only

How to Create EXCEL File on Application Server

Go to solution
Former Member

‎2010 May 17 3:58 PM

0 Likes
3,491

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

1 ACCEPTED SOLUTION
Read only

Go to solution
former_member214857
Contributor

‎2010 May 17 5:17 PM

0 Likes
1,472

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

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

Reply
7 REPLIES 7
Read only

Go to solution
Former Member

‎2010 May 17 4:23 PM

0 Likes
1,472

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">

<DocumentProperties xmlns="urn:schemas-microsoft-com:office:office">

<Author>bmengue</Author>

<LastAuthor>bmengue</LastAuthor>

<Created>2010-05-17T15:19:12Z</Created>

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

<Version>11.9999</Version>

</DocumentProperties>

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

<WindowHeight>10230</WindowHeight>

<WindowWidth>18075</WindowWidth>

<WindowTopX>240</WindowTopX>

<WindowTopY>105</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

...

<Worksheet ss:Name="Plan1">

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

x:FullRows="1">

<Row>

<Cell><Data ss:Type="String">A1</Data></Cell>

</Row>

<Row>

<Cell ss:Index="2"><Data ss:Type="String">B2</Data></Cell>

</Row>

<Row>

<Cell ss:Index="3"><Data ss:Type="String">C3</Data></Cell>

</Row>

<Row>

<Cell ss:Index="4" ss:StyleID="s21"><Data ss:Type="String">D4</Data></Cell>

</Row>

</Table>

...

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

Read only

Go to solution
former_member214857
Contributor

‎2010 May 17 5:17 PM

0 Likes
1,473

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

Read only

Go to solution
Former Member
In response to former_member214857

‎2010 May 17 6:55 PM

0 Likes
1,472

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

Read only

Go to solution
Former Member

‎2010 May 17 7:56 PM

0 Likes
1,472

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

Read only

Go to solution
Former Member

‎2010 May 18 12:25 PM

0 Likes
1,472

Hi,

Thank you everybody for your inputs.

Regards

Anurag

Read only

Go to solution
JerryWang
Product and Topic Expert
Product and Topic Expert

‎2010 May 19 5:12 AM

0 Likes
1,472

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.

Read only

Go to solution
JerryWang
Product and Topic Expert
Product and Topic Expert

‎2010 May 19 5:13 AM

0 Likes
1,472

?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"/

Column ss:Width="220"/

Column ss:Width="220"/

Column ss:Width="220"/

Column ss:Width="220"/

Column ss:Width="220"/

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