Skip to Content

Saving a file in AL11 and downloading the same with the number format as same as user,

Hello experts,

I am trying to download a file to AL11 folder And i could achieve this. As usual, i am converting all the columns in a row to a character format and then concatenating all of these into a single string. This sting is further 'TRANSFER' erred to the data set which is open and file download works fine. But After the download, whie using CG3Y, i am getting the currency format as left aligned and which is not numeric type anymore. I am not able to do a direct Sum on top of this, as it is not represented as a number or accouting format anymore. I am using the below code to


lv_floattpm = . " passing valuation currency to a floating point variable.
WRITE ls_gdwn-spotvalue_valuationcurr TO lv_tpmamount.

lv_spotvals_vc = lv_tpmamount.
CONDENSE lv_spotvals_vc.

And this value is further used in transferering to the output file dataset.

Output will be as below.

But the above one is not really in number format and converting this is a mess. The wish is to get the output format in the excelsheets as the settings in my user. As is in the pic above, it requires to many steps to adjust the format to be able to add summations etc. Could you please throw in your suggestion on this.


Regards,

ABb

currformat.png (3.5 kB)
Add a comment
10|10000 characters needed characters exceeded

  • I don't know what meaning you give to "third party tool". abap2xlsx has license Apache 2.0, you can use it, distribute, adapt, etc. If you prefer low quality programs, no problem, it's your choice.

  • I dont know much on Abap2xlsx. Had read it as a third party plugin. Could you please guide me a bit more on this.

  • Simplest example:

    DATA(lo_excel) = NEW zcl_excel( ).
    DATA(lo_worksheet) = lo_excel->get_active_worksheet( ).
    lo_worksheet->set_cell( ip_column = 1 ip_row = 1 ip_value = 245 ).
    DATA(lo_writer) = CAST zif_excel_writer( NEW zcl_excel_writer_2007( ) ).
    DATA(xstring) = lo_writer->write_file( lo_excel ).
    " Then create a binary XLSX file with content XSTRING

    Lots of demo programs provided with abap2xlsx.

Related questions

4 Answers

  • Best Answer
    Posted on May 18 at 09:39 PM

    Hello,

    Solved it as below.

    Mentioned below 2 different cases which i have in consideration.

    For GUI_DOWNLOAD option to desktop, No major changes were made in code. Identified that, by making the user format in SU01 and the user format in Windows the same the generated excel will have the required format by user.

    While downloading an excel from file downloaded to AL11, the generated excel file (to desktop using CG3Y) will not have the required format for columns with value fields. The file is always a string / char format one, due to the dataset file genearation option we use while downloading to AL11. So incase of value fields the output will not have the numeric format and converting to numeric format is tough. Identified that while concatenating the file to form dataset, the ´fields are all character format. It's a known issue of CSV or tab-delimited values, whatever the language is. That's just text, no information that it's a number. Excel can no longer interpret these as numbers and opens them as text. So I suspect the core issue is that separators don't match the user local preferences. I added the code to identify the separators and remove them; and this works. And downloading the file from AL11 to desktop using CG3Y has no issue while downloading to excel in desktop. Only issue is about the separators which is not available as we removed it in code. But the summation works for column.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 04 at 01:29 PM

    You need to use WRITE to convert the numeric and currency values. E.g. WRITE value TO characterfield CURRENCY currency.

    This will automatically use the users' settings for decimal and thousand separators, and adjust for currencies like HUF that have no decimals.

    See here for more details: https://help.sap.com/doc/abapdocu_751_index_htm/7.51/en-us/abapwrite_int_options.htm

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 04 at 05:07 PM

    Tried it. This doesnt help; it appears the same way which cannot be used for arithmetic operations.


    capturecur.png (2.3 kB)
    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on May 05 at 09:30 PM

    I did a simple test with Excel 365 and a text file. I'm in the US, so we use a comma (,) for thousands separator and a dot (.) for decimals. I've simply opened Excel on my PC and drag-and-dropped TXT file into it.

    If I enter these values in a text file then Excel has no problem interpreting them as numbers:

    10,000.00

    1,000.00

    It doesn't matter how they are aligned or whether leading / trailing spaces exist.

    But as soon as I switch the separator (10.000,00) Excel can no longer interpret these as numbers and opens them as text. So I suspect the core issue is that separators don't match the user local preferences.

    Add a comment
    10|10000 characters needed characters exceeded

    • Yes. the issue is with Separators. I added the code to identify the separators and remove them; and this works. And downloading the file from AL11 to desktop using CG3Y has no issue while downloading to excel in desktop. Only issue is about the separators which is not available as we removed it in code. But the summation works for column.

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.