Skip to Content
author's profile photo Former Member
Former Member

Need some help on formatting techniques used in excel sheets

Hi All

I have to convert the data in the internal table to a certain format XXXXXX and need to perform mapping for the same.

I got stuck at a point were in i need to follow certain rules to achieve that format like

1. left justify character fields

2. right justify numeric values

3. right justify amount decimal point and 2 decimal places not to use commas and $ symbol.

4.Null values need to be blank.

Can anyone plz send me the code for the following rules as i have never worked on this kind of requirement.

Helpfull answers will be awarded.

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

1 Answer

  • Best Answer
    author's profile photo Former Member
    Former Member
    Posted on Jan 10, 2007 at 10:27 AM

    Dear SKK,

    I'm providing you with the following piece of code ... Its working fine for me ... hopefully it suits your requirement ...

    ************************************************************************

    • D A T A D E C L A R A T I O N *

    ************************************************************************

    TYPES: BEGIN OF TY_EXCEL,

    CELL_01(80) TYPE C,

    CELL_02(80) TYPE C,

    CELL_03(80) TYPE C,

    CELL_04(80) TYPE C,

    CELL_05(80) TYPE C,

    CELL_06(80) TYPE C,

    CELL_07(80) TYPE C,

    CELL_08(80) TYPE C,

    CELL_09(80) TYPE C,

    CELL_10(80) TYPE C,

    END OF TY_EXCEL.

    DATA: IT_EXCEL TYPE STANDARD TABLE OF TY_EXCEL,

    WA_EXCEL TYPE TY_EXCEL..

    ************************************************************************

    • E V E N T : S T A R T - O F - S E L E C T I O N *

    ************************************************************************

    START-OF-SELECTION.

    • Here you populate the Internal Table.

    • -------------------------------------

    • Display - Top of the Page.

    • --------------------------------------

    PERFORM DISPLAY_TOP_OF_PAGE.

    ************************************************************************

    • E V E N T : E N D - O F - S E L E C T I O N *

    ************************************************************************

    END-OF-SELECTION.

    SET PF-STATUS 'GUI_STATUS'.

    ************************************************************************

    • E V E N T : A T U S E R - C O M M AN D *

    ************************************************************************

    AT USER-COMMAND.

    CASE SY-UCOMM.

    WHEN 'EXPORT'.

    • Exporting the report data to Excel.

    PERFORM EXPORT_TO_EXCEL.

    ENDCASE.

    &----


    *& Form DISPLAY_TOP_OF_PAGE

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM DISPLAY_TOP_OF_PAGE .

    SKIP.

    WRITE: /05(128) SY-ULINE,

    /05 SY-VLINE,

    06(127) 'O R I C A'

    CENTERED COLOR 1,

    132 SY-VLINE.

    WRITE: /05(128) SY-ULINE,

    /05 SY-VLINE,

    06(127) 'Shift Asset Depreciation - Period/Year-wise Report.'

    CENTERED COLOR 4 INTENSIFIED OFF,

    132 SY-VLINE.

    WRITE: /05(128) SY-ULINE.

    *----


    • E X C E L O P E R A T I O N

    *----


    CLEAR: IT_EXCEL[],

    WA_EXCEL.

    PERFORM APPEND_BLANK_LINE USING 1.

    WA_EXCEL-cell_02 = ' XYZ Ltd. '.

    APPEND WA_EXCEL TO IT_EXCEL.

    CLEAR: WA_EXCEL.

    WA_EXCEL-cell_02 = 'Shift Asset Depreciation - Period/Year-wise Report.'.

    APPEND WA_EXCEL TO IT_EXCEL.

    PERFORM APPEND_BLANK_LINE USING 1.

    ENDFORM. " DISPLAY_TOP_OF_PAGE

    &----


    *& Form APPEND_BLANK_LINE

    &----


    • text

    ----


    • -->P_1 text

    ----


    FORM APPEND_BLANK_LINE USING P_LINE TYPE I.

    DO P_LINE TIMES.

    CLEAR: WA_EXCEL.

    APPEND WA_EXCEL TO IT_EXCEL.

    enddo.

    ENDFORM.

    &----


    *& Form EXPORT_TO_EXCEL

    &----


    • text

    ----


    • --> p1 text

    • <-- p2 text

    ----


    FORM EXPORT_TO_EXCEL .

    DATA: L_FILE_NAME(60) TYPE C.

    • Create a file name

    CONCATENATE 'C:\' 'Shift_Depn_' SY-DATUM6(2) '.' SY-DATUM4(2)

    '.' SY-DATUM+0(4) INTO L_FILE_NAME.

    • Pass the internal table (it_excel which is already populated )

    • to the function module for excel download.

    CALL FUNCTION 'WS_EXCEL'

    exporting

    filename = L_FILE_NAME

    tables

    data = IT_EXCEL

    exceptions

    unknown_error = 1

    others = 2.

    if sy-subrc <> 0.

    message e001(ymm) with 'Error in exporting to Excel.'.

    endif.

    ENDFORM. " EXPORT_TO_EXCEL

    *~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~

    When you click the button - Export to Excel ( GUI-Status) you'll be able to export the content of the Internal Table to an Excel file .......

    Regards,

    Abir

    ************************************

    • Don't forget to award points *

                                                                        Add a comment
                                                                        10|10000 characters needed characters exceeded

                                                                        • Former Member

                                                                          Hi abir,

                                                                          Thanks for ur reply and code.actuvally my reqirement is different ie user can fill the excel sheet,the excel sheet has comapny code and line items...nearly above 50fields r there in excel sheeet we r going convert the excel sheet in to some formet then send to inbound control before sending to inbound we can follow some rules wht i mention in previous theard..and check the comany code and line items as per conditions..i completed all part of coding except the below part..

                                                                          I have to convert the data in the internal table to a certain format XXXXXX and need to perform mapping for the same.

                                                                          I got stuck at a point were in i need to follow certain rules to achieve that format like

                                                                          1. left justify character fields

                                                                          2. right justify numeric values

                                                                          3. right justify amount decimal point and 2 decimal places not to use commas and $ symbol.

                                                                          4.Null values need to be blank.

                                                                          Can anyone plz send me the code for the following rules as i have never worked on this kind of requirement.

                                                                          Helpfull answers will be awarded.

                                                                          thanks

                                                                      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.