Excel sheet format in CL_DOCUMENT_BCS

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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.

[Former Member]

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

[Former Member]

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

[Former Member]

COMMIT WORK after sending mail!

Thanks,

Shambu

[Former Member]

Hi Shambu,

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

Regards,

Rohini.

[Former Member]

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.

[Former Member]

What value are passing in L_VALUE variable?

Is it 1234567 or 1,23,456?

[Former Member]

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

[Former Member]

Try to pass the type  as 'Currency' and check.

[Former Member]

Hi Shambu,

Which type ur asking me to pass as Currency.

[Former Member]

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 = 'Currency' ). 

I didnt test this.Just check if this works.

Also, you can try to explicitly add Thousand's separator to the value and pass it to the EXCEL.

Thanks,

Shambu

[Former Member]

Hi Shambu,

I tried that also, but when i tried to open the excel sheet it give me the below error.

Regards,

[Former Member]

Again, write out the currency to a character string where you can use the currency type and format.  then put that string into the LVALUE and pass that in as a "STRING". If you need it as a number use 'NUMBER' as below: 

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

Hope that this does it for you.

Kevin

[Former Member]

Did you also try 'Number'?

OR

You can add the Thousand's separator explicitly and send that to excel.

[former_member213851]

Hi Rohini,

To get currency in required format and Alignment  ,change Excel settings.

Go to excel cell containing Currency, Right click and select Format cells.

Now inside Numbers tab go to Currency and select desired format.

Similarly, go to Alignment and change Vertical and Horizontal alignment as per requirement. 

Best Regards,

Sachin

[Former Member]

Hi Shambu,

How to use thousand separator explicitly.

Regards,

Shegar

[Former Member]

Hi Kevin Hill,

I tried those thing and it is coming fine with the thousand seperator, but the problem is that amount is left aligned.

How to give the currency the right alignment.

Kindly find the print shot.

Regards,

Rohinit

[Former Member]

Hi Shambu,

Now my output is like below, but the curreny has to be right aligned how to do it.

Regards,

Rohini

[Former Member]

Hi,

You need to add a new style for this.

We have 2 now: Data and Header.

Add a new one, say Data1.

* Style for Data
r_style1 = l_document->create_simple_element( name = 'Style' parent = r_styles ).
r_style1->set_attribute_ns( name = 'ID' prefix = 'ss' value = 'Data1' ).
r_format = l_document->create_simple_element( name = 'Alignment' parent = r_style1 ).
r_format->set_attribute_ns( name = 'Horizontal' prefix = 'ss' value = 'Right' ).
r_format = l_document->create_simple_element( name = 'NumberFormat' parent = r_style1 ).
r_format->set_attribute_ns( name = 'Format' prefix = 'ss' value = 'Standard' ).
r_border = l_document->create_simple_element( name = 'Borders' parent = r_style1 ).
r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
r_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = 'Bottom' ).
r_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = '1' ).
r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
r_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = 'Left' ).
r_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = '1' ).
r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
r_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = 'Top' ).
r_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = '1' ).
r_format = l_document->create_simple_element( name = 'Border' parent = r_border ).
r_format->set_attribute_ns( name = 'Position' prefix = 'ss' value = 'Right' ).
r_format->set_attribute_ns( name = 'LineStyle' prefix = 'ss' value = 'Continuous' ).
r_format->set_attribute_ns( name = 'Weight' prefix = 'ss' value = '1' ).

Now, use the new style for your Currency cell.

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

Hope this is clear.

Thanks,

Shambu

[Former Member]

Hi Shambu,

I am really amazed by your knowldege in ABAP.

Your code relly solved my problem, now i am able to get it.

Thanks a lot for the effort you put in solving my issue.

Regards,

Rohini.

[Former Member]

Hi Rohini,

Please mark the query as Answered and mark other helpful answers to the one's who have helped you.

Thanks,

Shambu

[Former Member]

One more thing how to aligh the column heading center.

And how to make the Subtotal and Grand Total Bold.

Regards,

Rohini.

[Former Member]

Hi,

The code for this is already there.

You can see how the column headings are in Bold.

Just copy that style(Header) into a new one and remove the code for adding the color and use that style for Sub total and total.

Also, for Center Justified, I had given the statement to add for right justification above.

Change the Header style by having a look at these.

Thanks,

Shambu

[Former Member]

Hi Shambu,

Now my client has come with new issue. In the excel sheet for the amoutn column there is green icon in the cell. My client doesnt want that green icon in the cell, pls advice me how to get rid of that.

Also pls adive me how  to use ABAP2XLSX. In my system i have installed now i can run the programme ZSAPLINK.

Please help me how will ABAP2XLSX help me to format.

[tploik]

Hi.

Maybe someone will need:

.....

l_value = <fname>

CONDENSE l_value NO-GAPS.

....

r_data->set_attribute_ns( name = 'Type'  prefix = 'ss'  value = 'Number'  ).

...

[Clemenss]

no details no code no help