topic Re: Excel sheet format in CL_DOCUMENT_BCS in Application Development and Automation Discussions

Excel sheet format in CL_DOCUMENT_BCS

Former Member — Mon, 11 Jun 2012 15:51:48 GMT

Hi all,

I am using this CL_DOCUMENT_BCS to send internal table data in excel through mail, that is
working fine.

My problem is when i open the excel sheet through mail, it is displayed as below.

But i want the excell to dispay with the necessary colum width as below.

Kindly advice me what need to be done.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Mon, 11 Jun 2012 15:58:47 GMT

Hi Rohini,

Please check this link,

http://scn.sap.com/thread/1988139

There are lot of suggestions on how to handle formatted excel.

My favorite :ABAP2XLSX

Thanks,

Shambu

Re: Excel sheet format in CL_DOCUMENT_BCS

Clemenss — Mon, 11 Jun 2012 18:52:10 GMT

no details no code no help

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 09:07:35 GMT

Hi Shambu,

I have gone through the link, but still confused wath need to be done.

Could you please guide me, i just want my column to increase in lenght so that all that data will display.

Regards,

Shegar

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 09:24:22 GMT

Did you try to implement the correct answer?

Select 10 records from EKPO and Loop at the internal table.

Inside that loop, copy the code and check.

Thanks,

Shambu

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 11:51:19 GMT

Hi Shambu,

But which code i have to copy and check.

Also I have not idead what is ABAP2XLSX and how to check whether this is installed in my system, if installed how to use it.

Regards,

Rohini.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 12:08:18 GMT

HI Rohini,

You can copy the code for CL_IXML (The correct answer in the link) and check.

ABAP2XLSX may not be present in your system.

Its not a standard component. Its a project in Code Exchange.

You will have to use SAPLINK to import all the objects in ABAP2XLSX.

Anyways, you can try CL_IXML and check.

They have the same query as you.

Just try it out and let us know.

Thanks,

Shambu

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 17:07:47 GMT

Hi Shambu,

I copied all the code from "CL_IXML (The correct answer in the link)", but got stuck in

L_OSTREAM = L_STREAMFACTORY->CREATE_OSTREAM_ITABLE( TABLE = L_XML_TABLE ).

what is the table type i have to define for L_XML_TABLE.

Regards,

shegar.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Tue, 12 Jun 2012 21:46:56 GMT

Shegar,

What we use is:

TYPES: BEGIN OF xml_line,
data(255) TYPE x,
END OF xml_line.

DATA:

l_xml_table TYPE TABLE OF xml_line,
wa_xml TYPE xml_line.

This works for us. Best of luck.

Kevin

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 09:48:56 GMT

Hi Kevin,

I tried it now it is coming, but the problem is when i tried to put some date in it_ekpo and exceute.

the mail is sent and when i try to open the excel sheet it is giving me the following error.

Please help

Rohini

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 10:27:18 GMT

Hi Shambu and Kevin,

When i try to put value = header, the excel sheet is opening properly,

r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Header' ).

But when i try to put data i.e value = 'Data', i am not able to open my excel sheet.

Regards,

Rohini.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 14:30:06 GMT

Rohini,

Write the date to a character field and then put in the character field. Then use 'DATA' and you shoud be fine.

    WRITE w_out-acdat TO l_char MM/DD/YYYY.

    l_value = l_char.

    r_data = l_document->create_simple_element( name = 'Data' value = l_value   parent = r_cell ).           " Data

    r_data->set_attribute_ns( name = 'Type' prefix = 'ss' value = 'String' ).                               " Cell format

You may also want to use "CONVERSION_EXIT_ALPHA_OUTPUT" on some of your data to take care of leading 0's.

Good luck and let us know how it goes.

Kevin

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 15:46:37 GMT

Hi Kavin,

Still i am getting the same error, i am not able to open the excel.

data: cha TYPE c lenth 10.

LOOP AT it_ekpo. "ls_t100. " lv_string."it_ekpo INTO wa_ekpo. "ls_t100. "
    WRITE it_ekpo-ebeln to cha.
    l_value = cha.
    r_cell = l_document->create_simple_element( name = 'Cell' parent = r_row ).
    r_cell->set_attribute_ns( name = 'StyleID' prefix = 'ss' value = 'Data' ).
    r_data = l_document->create_simple_element( name = 'Data' value = l_value   parent = r_cell ).           " Data
    r_data->set_attribute_ns( name = 'Type' prefix = 'ss').

endloop.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 17:12:59 GMT

Hi Rohini,

Please take a look at this WIKI.

http://wiki.sdn.sap.com/wiki/display/Snippets/Formatted+Excel+as+Email+Attachment

Just execute this and check.

Thanks,

Shambu

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 17:28:24 GMT

Hi Shambu,

I was able to execute the report, but no output is coming from the report.

No mail is executed from the report.

Regards,

Rohini

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Wed, 13 Jun 2012 18:00:44 GMT

COMMIT WORK after sending mail!

Thanks,

Shambu

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Thu, 14 Jun 2012 08:38:58 GMT

Hi Shambu,

Your simply genius, now after COMMIT WORK it is sending mail.

Regards,

Rohini.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Fri, 15 Jun 2012 14:21:53 GMT

Hi Shambu,

Now it is working fine, but the issue which i am facing is during column having curreny,

I am not able to get comma seperator in currency for e.g 125487 (1,24,487).

Regards,

Shegar.

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Fri, 15 Jun 2012 15:11:16 GMT

What value are passing in L_VALUE variable?

Is it 1234567 or 1,23,456?

Re: Excel sheet format in CL_DOCUMENT_BCS

Former Member — Fri, 15 Jun 2012 15:23:42 GMT

Hi Shambu,

I really appreciate your effort which you are putting in ressolving my issue.

it_anlp-kansw TYPE ANLC-KANSW value 1909.63.

loop at itab.

    R_CELL = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Cell' PARENT = R_ROW ).
    R_CELL->SET_ATTRIBUTE_NS( NAME = 'StyleID' PREFIX = 'ss' VALUE = 'Data' ).
    L_VALUE = IT_ANLP-KANSW.
    R_DATA = L_DOCUMENT->CREATE_SIMPLE_ELEMENT( NAME = 'Data' VALUE = L_VALUE   PARENT = R_CELL ).          " Data
    R_DATA->SET_ATTRIBUTE_NS( NAME = 'Type' PREFIX = 'ss' VALUE = 'String' ).                              " Cell format

endloop.

when i open th excel sheet, it come like the below

As 1909.63 is currency amt it should be righ aligned and should display like 1,909.63.

Regards,

shegar