Receiver Mail Adapter, Sending Excel sheet with multiple Sheet

[Former Member]

Hi,

I want to send a excel file through receiver mail adapter with multiple worksheet. Worksheet will be having the specific names. Contents will be having some records and depending on the number of records, worksheet should be there in excel file.

[Former Member]

Ritesh,

Use the following code: to create multiple sheet. If u face any problem conatct me at abinash_nanda@infosys.com

<?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>Abinash Nanda</Author>

<LastAuthor>Abinash Nanda</LastAuthor>

<Created>2007-04-22</Created>

<LastSaved>2007-05-01</LastSaved>

<Company>xyz</Company>

<Version/>

</DocumentProperties>

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

<DownloadComponents/>

</OfficeDocumentSettings>

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

<WindowHeight>8955</WindowHeight>

<WindowWidth>11355</WindowWidth>

<WindowTopX>360</WindowTopX>

<WindowTopY>120</WindowTopY>

<ProtectStructure>False</ProtectStructure>

<ProtectWindows>False</ProtectWindows>

</ExcelWorkbook>

<Styles>

<Style ss:ID="Default" ss:Name="Normal">

<Alignment ss:Vertical="Bottom"/>

<Borders/>

<Font/>

<Interior/>

<NumberFormat/>

<Protection/>

</Style>

</Styles>

<Worksheet ss:Name="ABC">

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

x:FullRows="1">

<Row>

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

</Row>

<Row>

<Cell><Data ss:Type="Number">2</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="Number">4</Data></Cell>

</Row>

</Table>

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

<Print>

<ValidPrinterInfo/>

<HorizontalResolution>600</HorizontalResolution>

<VerticalResolution>600</VerticalResolution>

</Print>

<Selected/>

<Panes>

<Pane>

<Number>3</Number>

<ActiveRow>1</ActiveRow>

<ActiveCol>1</ActiveCol>

</Pane>

</Panes>

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name="DEF">

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

x:FullRows="1">

<Row>

<Cell><Data ss:Type="Number">2</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="Number">2</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="Number">4</Data></Cell>

</Row>

</Table>

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

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

<Worksheet ss:Name="XYZ">

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

x:FullRows="1">

<Row>

<Cell><Data ss:Type="Number">2</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="Number">2</Data></Cell>

</Row>

<Row>

<Cell><Data ss:Type="Number">4</Data></Cell>

</Row>

</Table>

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

<ProtectObjects>False</ProtectObjects>

<ProtectScenarios>False</ProtectScenarios>

</WorksheetOptions>

</Worksheet>

</Workbook>

Thanks

Abinash

[Former Member]

HI,

See the below links

/people/community.user/blog/2006/09/07/email-reporting

/people/community.user/blog/2006/09/08/email-report-as-attachment-excelword

http://www.utoronto.ca/ian/books/html4ed/appb/mimetype.html

"JAVA MAPPING", an alternate way of reading a CSV file -/people/rahul.nawale2/blog/2006/07/18/java-mapping-an-alternate-way-of-reading-a-csv-file

Regards

Chilla

[Former Member]

Hello Chilla,

I have already gone through this blog, But It's not executing my requirement. I need to send multiple sheet in a excel file.

Thanks for your quick response

[henrique_pinto]

Hey Ritesh,

check out this blog:

/people/sap.user72/blog/2005/07/04/read-excel-instead-of-xml-through-fileadapter

It talks about a Java Excel API.

I personally have never used it but in the sample code in the blog it seems to be able to treat several sheets in a same workbook.

Im not sure if it is the same API, but here

http://jexcelapi.sourceforge.net/

you can find a open source java excel api.

Also, check out the javadocs in

http://jexcelapi.sourceforge.net/resources/javadocs/2_6/docs/index.html.

With it, you can easily fulfill your requirements. Just create a new WritableWorkbook, get the number of sheets to run a loop (with the getNumberOfSheets() method), and for each sheet (getSheet(int index) method), get the number of cells (with getColumns() and getRows() methods), and for each sheet that satisfies your requirement of minimum number of fields, add it to the new workbook you created. In the end, return this workbook as output of your method.

You can use this either on java mapping or custom module.

Regards,

Henrique.

[Former Member]

Hi Henrique,

Here is what I want : Xi receives XML message and sends a EXCEL file with multiple work sheets. I have to use Java mapping or any other map.

Please suggest.....

[Former Member]

HI,

Try to write a Adapter module to put the data into Excel sheet and in code we can handle the sheets also in excel , i hope ,.. try it will solve your requirement

how to write a adapter module see the below link

https://www.sdn.sap.com/irj/servlet/prt/portal/prtroot/docs/library/uuid/02706f11-0d01-0010-e5ae-ac2...

Regards

Chilla

[Former Member]

Hi Chilla,

I'll try this and will let you know.

Thanks

Regards

Ritesh

[Former Member]

Hi,

I've a problem about the abap report program.

I create a excel(sheet1) object, and fill the value, then need to copy (sheet1) to (sheet2) , how to coding it in abap program??

my source code as below:

CREATE OBJECT h_excel 'Excel.Application'.

SET PROPERTY OF h_excel 'Visible' = 1.

GET PROPERTY OF h_excel 'Workbooks' = h_workbooks.

CALL METHOD OF h_workbooks 'Add'

EXPORTING #1 = p_upload. --> sheet(1)

• I'd like to copy sheet(1) format to sheet(2) here ******

CALL METHOD OF h_excel 'Worksheets' = h_sheet.

CALL METHOD OF h_sheet 'Add'.

GET PROPERTY OF h_excel 'ActiveSheet' = h_sheet.

SET PROPERTY OF h_sheet 'NAME' = 'sheet(2)'. --> sheet(2)

CALL METHOD OF h_excel 'Worksheets' = h_sheet

EXPORTING #1 = 'sheet(2)'.

Please help me out!!

Thanks & Best Regards,

Eunice

[bhavesh_kantilal]

Hi,

Check this blog and see if it helps,

/people/community.user/blog/2006/09/08/email-report-as-attachment-excelword

Regards

Bhavesh

[Former Member]

Hello Bhavesh,

I have already gone through this blog, But It's not executing my requirement. I need to send multiple sheet in a excel file.

Thanks for your quick response