SAP R/3 форум ABAP консультантов
Russian ABAP Developer's Club

Home - FAQ - Search - Memberlist - Usergroups - Profile - Log in to check your private messages - Register - Log in - English
Blogs - Weblogs News

Export to XLS



 
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord
View previous topic :: View next topic  
Author Message
admin
Администратор
Администратор



Joined: 01 Sep 2007
Posts: 1636

PostPosted: Sat Sep 22, 2007 5:27 pm    Post subject: Export to XLS Reply with quote

Author Richard Harper
Original: http://www.sapfans.com/forums/viewtopic.php?t=29911

WS_ or GUI_ download will not do the job that you want to do. If you want to display the spreadsheet as it is created from SAP you need to use OLE to do this.

There are two methods that you can use. The first downloads the data to the spreadsheet, and then the user has to open the spreadsheet themselves and this goes something like:
Code:
Form Upload_Table using pu_file like rlgrap-filename
               changing pc_uploaded.
*
     Constants c_start_col type i value 3.   " Start column for data
                                              " in the spread sheet.
     Data:  t_worksheets type standard table of Worksheet_Names
                         with header line
                         Initial Size 5,
            t_fieldNames Type standard table of FieldNames,
            t_fieldvals  Type standard table of Cell_Values,
*
            w_fieldname Type Cell_Values,
            w_value     Type Cell_Values,
            w_lastcol   Type Cell_Values,
            Excel       Type Ole2_Object,
            W_Book      Type Ole2_Object,
            w_Sheet     Type Ole2_Object,
            w_row       Type I,
            w_column    Type I,
            w_maxcols   Type I,
            w_text      Type t100-Text,
            w_text1     Type t100-Text.


     Move False to pc_uploaded.
*
*    Work sheets to upload.
*
     Move 'Stockable MM01a' to t_worksheets-SheetName.
     Append t_worksheets.
*     Move 'Purchased Services MM01b' to t_worksheets-SheetName.
*     Append t_worksheets.
     Move 'Sold Services MM01b' to t_worksheets-SheetName.
     Append t_worksheets.
*     Move 'Consumables MM01c' to t_worksheets-SheetName.
*     Append t_worksheets.
*     Move 'RRB MM01e' to t_worksheets-SheetName.
*     Append t_worksheets.

*
*    Open the workbook
*
     Create Object Excel 'EXCEL.APPLICATION'.
     Call Method Of Excel 'WORKBOOKS' = W_Book.
     Call Method Of W_Book 'OPEN' EXPORTING #1 = Pu_File.
     If sy-subrc = 0.
*
*       Select the 5 work books in turn
*
        Loop at t_worksheets.
             Call Method Of Excel
                            'Worksheets' = w_Sheet
                  Exporting #1 = t_worksheets-SheetName.
             Call Method Of W_sheet 'Activate'.
             If sy-subrc = 0.
*
*               Get the field names in this sheet.
*
                Move 4 to w_row.
                Move c_start_col to w_column.
                Move '***' to w_lastcol.
                Zap t_fieldnames.
                Concatenate 'Getting field names for '
                            t_worksheets-Sheetname
                       into w_text separated by ' '.
                Call Function 'SAPGUI_PROGRESS_INDICATOR'
                  Exporting
                    Percentage       = 0
                    Text             = w_text.
*
*               Go along the columns until we have two blank
*               columns side by side.
*

                Do.
                    Perform GetCellValues using Excel w_row w_column
                                       changing w_fieldname.


To find additional functions, record what you want to do in the Excel macro record. That gives you the relevant methods and properties to use.

The second method you can use is to display the Excel spreadsheet inside a container in an SAP window. This gives a seamless feel to the process but can cause some major headaches - one of which is that there has to be a spreadsheet template - you cannot create the sheet from scratch.

The process below is for 4.0b.

In transaction SMW0, Select Binary data and then click search. Enter developement class SOFFICEINTEGRATION.

Select Settings->Maintain Mimetypes. If there is no mime type for 'application/ms-excel', create one with a file extention of *.xls.

Save that and then create a spreadsheet template. The easiest way I have found is to create an empty spreadsheet with a series of macros.

For example, the code below is a macro from one of my sheets that formats a 'header' for a spreadsheet:

Code:

ub Format_Header(Company As String, Date_Start As Variant, Date_End As Variant, Business_Areas As String)
    '*
    ReDim Ba_List(0) As String
    Dim Ba_Temp As String
    Dim Ba_Count As Integer
    Dim Pos As Integer
    Dim Last_Col As String
    Dim Title_Range As String
    '*
    Call Set_Columns
    '*
    '* Calculate the last column to be used by counting the number of business
    '* areas passed to the routine
    '*
    Ba_Temp = Business_Areas
    '*
    '* A list seperated with exclamation marks
    '*
    While Ba_Temp <> ""
          Pos = InStr(Ba_Temp, "!")
          If Pos > 0 Then
             Ba_List(Ba_Count) = Left(Ba_Temp, Pos - 1)
             Ba_Temp = Mid(Ba_Temp, Pos + 1)
             Ba_Count = Ba_Count + 1
             ReDim Preserve Ba_List(Ba_Count) As String
          Else
             Ba_List(Ba_Count) = Ba_Temp
             Ba_Temp = ""
          End If
    Wend
    '*
    '* Spreadsheet width is Column d+Columns(2*Ba_Count+1)
    '* This assumes that the columns do not go past column Z
    '*
    Last_Col = Chr$(Asc("D") + (2 * (Ba_Count + 1)))
    '*
    '* Company Name
    '*
    Title_Range = "B2:" & Last_Col & "2"
    Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
    Range("B2").Value = Company
    '*
    '* Title.....
    '*
    Title_Range = "B4:" & Last_Col & "4"
    Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
    Range("B4").Value = "DRAFT MANAGEMENT ACCOUNTS FOR THE PERIOD " & Date_Start & " TO " & Date_End
    '*
    '* Business area titles.  These start at column C with the totals
    '*
    Call Format_Cell(Range("C6"), C_Text_Format, White, Black, xlCenter)
    Range("C6").Value = "Total"
    Call Format_Cell(Range("D6"), C_Text_Format, White, Black, xlCenter)
    Range("D6").Value = "0/0"
    For Ba_Count = 0 To UBound(Ba_List)
        Title_Range = Chr$(Asc("E") + (2 * Ba_Count)) & "6"
        Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
        Range(Title_Range).Value = Ba_List(Ba_Count)
        Title_Range = Chr$(Asc("E") + ((2 * Ba_Count) + 1)) & "6"
        Call Format_Cell(Range(Title_Range), C_Text_Format, White, Black, xlCenter)
        Range(Title_Range).Value = "0/0"
    Next Ba_Count
End Sub


This does the work of formatting and entering data passed by SAP into a blank spreadsheet.

Then, in your ABAP, you must create the container and call the various macros:
Code:

Form Create_Spreadsheet.
*
        Data: w_retcode      type        t_oi_ret_string,
              w_doc_type     type        soi_document_type,
              w_url(256)     type c.
*
*    Create the spreadsheet
*
     Set Screen 0110.
     If g_Excel is Initial.
        Perform Create_Container Using 'Y_DRAFT_ACCOUNTS' '0110' 0
                              Changing g_Excel w_RetCode.
        If w_RetCode <> c_oi_errors=>Ret_Ok.
           Message I000 with Text-069.
        Else.
*
*          Set the document type for the container
*
           Call Method g_Excel->Get_Document_Proxy
                Exporting Document_Type  = 'Excel.Sheet.8'
                Importing Document_proxy = g_document
                          RetCode        = w_RetCode.
           If w_RetCode <> c_oi_errors=>Ret_Ok.
              Message I000 with Text-071.
           EndIf.
        EndIf.
     EndIf.
*
*    Continue ??
*
     If w_RetCode = c_oi_errors=>Ret_Ok.
        Call function 'SAP_OI_SEND_MIME_DATA'
             exporting
                  object_id            = 'DRAFTS'
             changing
                  url                  = w_url
             exceptions
                  object_not_found     = 1
                  internal_error       = 2
                  dp_invalid_parameter = 3
                  dp_error_put_table   = 4
                  dp_error_general     = 5
                  others               = 6.
        Call method g_document->open_document
                  exporting document_url = w_url
                            open_inplace = 'X'
                  receiving retcode = w_retcode.
        Call method g_document->update_document_links
                  receiving retcode = w_retcode.
        Perform Excel_Header changing w_retcode.
     EndIf.
EndForm.
*Eject
**********************************************************************
*
*       Procedure:               Create_Container
*
*       Purpose:                 Creates an Office automation container
*                                object.
*
*       Entry:                   Program id window to show control in
*       Entry:                   Dynpro number of window
*       Entry:                   Window Top position
*
*       Exit:                    Container Object.
*                                Return Code
*                                If window details provided, window
*                                is opened.
*
*       Called By:               Perform Create_contai using w_doc_type
*                                                   changing w_object.
*       Calls:
*
*       Modification History:
*
Form Create_Container using pu_repid      like sy-repid
                            pu_dynpro_nr  like sy-dynnr
                            pu_window_top type i
                   changing pc_object     type ref to
                                           i_oi_ole_container_control
                            pc_RetCode    type t_oi_ret_string.
*
     Data: w_retcode      type        t_oi_ret_string,
           w_Shell_Style  type I,
           w_Align        type I,
           w_Inplace_Mode type I.
*
     Move c_oi_errors=>Ret_Ok to w_RetCode.
     Call Function 'CONTROL_INIT' Exceptions Control_Init_Error = 1
                                             Others             = 2.
     If Sy-subrc = 0.
        Call Method C_Oi_Ole_Control_Creator=>Get_Ole_Container_Control
             Importing Control = pc_Object
                       Retcode = w_Retcode.
        If w_RetCode = c_oi_errors=>Ret_Ok.
           w_Shell_style =  ws_visible + ws_child + ws_border
                            + ws_clipchildren.
           w_Inplace_Mode = Pc_Object->Inplace_Mode_Enabled +
                            Pc_Object->Inplace_Mode_Scroll.
           Call Method Pc_Object->Init_Control
                Exporting R3_Application_Name      = 'R/3 Basis'
                          Inplace_Mode             = w_Inplace_Mode
                          Shell_Style              = w_Shell_Style
                          Register_On_Close_Event  = 'X'
                          Register_On_Custom_Event = 'X'
                          Receiving Retcode        = w_Retcode.
        Endif.
        If w_RetCode = c_oi_errors=>Ret_Ok.
           w_Align = Align_At_Right + Align_At_Bottom.
           Call Method Pc_Object->Set_Window_Properties
                Exporting Top   = pu_Window_Top
                          Align = w_Align.
        EndIf.
        If w_RetCode = c_oi_errors=>Ret_Ok.
           If pu_RepId <> ''.
              Call Method Pc_Object->Link_Control
                   Exporting Rep_Id    = pu_Repid
                             Dynpro_nr = pu_dynpro_nr
                   Receiving RetCode = w_Retcode.
           EndIf.
        EndIf.
        If w_RetCode = c_oi_errors=>Ret_Ok.
           Call method pc_object->get_link_server
                importing link_server = g_link_server
                          retcode     = w_retcode.
        EndIf.
        If w_RetCode = c_oi_errors=>Ret_Ok.
           Call method g_link_server->start_link_server
                receiving retcode = w_retcode.
        EndIf.
     EndIf.
     pc_retcode = w_Retcode.
EndForm.
*&---------------------------------------------------------------------*
*&      Module  STATUS_0110  OUTPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
Module Status_0110 Output.
       Set PF-status 'MAIN0110'.
       Set TitleBar '001'.
EndModule.
*&---------------------------------------------------------------------*
*&      Module  USER_COMMAND_0110  INPUT
*&---------------------------------------------------------------------*
*       text
*----------------------------------------------------------------------*
Module USER_COMMAND_0110 Input.
*
       Data: w_Retcode type t_oi_ret_string.
*
       Case sy-ucomm.
            When 'EXIT'.
                 Perform Exit_Spreadsheet changing w_retcode.
       EndCase.
EndModule.
*Eject
**********************************************************************
*
*       Procedure:               Excel_Header
*
*       Purpose:                 Formats argument to excel macro
*                                format_header and then calls the
*                                macro to format the spreadsheet
*
*       Entry:                   Global Objects
*
*       Exit:                    Return Code
*
*       Called By:               Perform Excel_Header
*                                        changing w_status.
*       Calls:
*
*       Modification History:
*
Form Excel_Header Changing pc_retcode type t_oi_ret_string.
*
     Data: w_butxt              like t001-butxt,   " Company name
           w_start_poper        like t009b-poper,  " Start period
           w_end_poper          like t009b-poper,  " End Period
           w_start_date         like sy-datum,     " Corresponding
           w_end_date           like sy-datum,     " dates
           w_start_excel(10)    type c,            " Excel format date
           w_end_excel(10)      type c,            " and end
           Begin of w_date,                        " Struc for
                 year(4) type n,                   " date conversikon
                 Mnth(2) type n,
                 Day(2)  type n,
           End of w_date,
           w_gtexts(1000)       type c,            " Business areas
           w_macro_string(1000) type c.
*
*    Company Name
*
     Read Table sd_bukrs index 1.
*
     Select single butxt
       into w_butxt
       from t001
      where bukrs = sd_bukrs-low.
*
*    Change the SAP Periods and year to Excel Dates.
*
     Read Table b-Monate index 1.
     If sy-subrc = 0.
*
*       Check if this is an open range
*
        If b-Monate-low is initial.
           Move 1 to w_start_poper.
        Else.
           Move b-Monate-Low to w_start_poper.
        EndIf.
        If b-Monate-high is initial.
           Move 16 to w_end_poper.
        Else.
           Move b-Monate-high to w_end_poper.
        EndIf.
     Else.
        Move 1 to w_start_poper.
        Move 16 to w_end_poper.
     EndIf.
*
     Call Function 'FIRST_DAY_IN_PERIOD_GET'
          Exporting
               I_Gjahr        = BilbJahr
               I_Periv        = 'P9'
               I_Poper        = b-monate-low
          Importing
               E_Date         = w_start_date
          Exceptions
               Input_False    = 1
               T009_Notfound  = 2
               T009b_Notfound = 3
               Others         = 4.
     Call Function 'LAST_DAY_IN_PERIOD_GET'
          Exporting
               I_Gjahr        = BilbJahr
               I_Periv        = 'P9'
               I_Poper        = b-monate-high
          Importing
               E_Date         = w_end_date
          Exceptions
               Input_False    = 1
               T009_Notfound  = 2
               T009b_Notfound = 3
               Others         = 4.
*
*    And then to Excel format
*
     Move w_start_date to w_date.
     Concatenate w_date-day '/' w_date-mnth '/' w_date-year
            into w_start_excel.
     Move w_end_date to w_date.
     Concatenate w_date-day '/' w_date-mnth '/' w_date-year
            into w_end_excel.
*
*    Build a list of business areas delimited by '!'
*
     Loop at t_tgsb.
          Concatenate w_gtexts t_tgsb-gtext
                 into w_gtexts
            separated by '!'.
     EndLoop.
*
*    Put all this together for the macro call.
*
     Move 'Module1.Format_Header(' to w_macro_string.
     Concatenate w_macro_string '"' w_butxt '","' w_start_excel '",'
                                '"' w_end_excel '","' w_gtexts '")'
                                    into w_macro_string.
BREAK-POINT.
     Call method g_Document->Execute_Macro
          Exporting Macro_String = w_macro_string
          Receiving RetCode      = pc_retcode.
EndForm.
*Eject
**********************************************************************
*
*       Procedure:               Exit_Spreadsheet
*
*       Purpose:                 Checks to see if spreadsheet has
*                                changed.  If so offers save and then
*                                closes global control
*
*       Entry:                   Global Objects
*
*       Exit:                    Container Object Closed
*                                Return Code
*
*       Called By:               Perform Exit_Spreadsheet
*                                        changing w_status.
*       Calls:
*
*       Modification History:
*
Form Exit_Spreadsheet Changing pc_retcode type t_oi_ret_string.
*
     Data: w_do_close    type i,
           w_Has_Changed type i.
*
     If not g_document is initial.
*
*       Has the document changed ??
*
        Call Method g_document->Is_Open
                               Receiving Ret_Value = w_do_close.
        If not w_do_close is initial.
           Call Method g_document->Close_Document
                Exporting Do_Save     = True
                Importing Has_Changed = w_Has_Changed
                          RetCode     = pc_RetCode.
           Call Method c_oi_errors=>Show_Message
                Exporting type = 'E'.
        Else.
           Call Method g_Document->Has_Changed
                Receiving Ret_Value = w_Has_Changed.
        EndIf.
*
*       Has the document changed ??
*
        If not w_Has_Changed is initial.
           Move c_oi_errors=>ret_ok to pc_retcode.
           Perform Save_Spreadsheet changing pc_retcode.
        EndIf.
*
*       If Save is ok,  then continue with close
*
        If w_retcode = c_oi_errors=>Ret_Ok.
           Call Method g_Document->Release_Document
                Receiving RetCode = pc_retcode.
           Free g_Document.
*
*          Stop the link server
*
           If Not g_Link_Server Is Initial.
              Call Method g_Link_Server->Stop_Link_Server
                   Receiving Retcode = pc_Retcode.
              Free g_Link_Server.
           Endif.
*
*          And finally remove the control.....
*
           If Not g_Excel Is Initial.
              Call Method g_Excel->Destroy_Control
                   Receiving Retcode = pc_Retcode.
              Free g_Excel.
              Call Function 'CONTROL_EXIT'.
           Endif.
           Leave To Screen 0.
        Else.
           Message I000 with text-072.
        EndIf.
     EndIf.
EndForm.


Further information can easily be found in your docuprint CD under BC - Desktop Integration.

As you can probably see from above, I'm still struggling a bit with this but it is starting to work.

If you are on 4.6c, read the docuprint stuff first as it has been extended from 87 pages to 206 pages with the additional functionality that offers.

Above all, download the "Bang Head Here" kit from the internet and pin that up on the hardest wall you can find, 'cos you're going to need it......

Have fun!

Regards, Rich
Back to top
View user's profile Send private message
Display posts from previous:   
Post new topic   Reply to topic    Russian ABAP Developer's Club Forum Index -> OLE2, Excel, WinWord All times are GMT + 4 Hours
Page 1 of 1

 
Jump to:  
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum
You cannot attach files in this forum
You can download files in this forum


All product names are trademarks of their respective companies. SAPNET.RU websites are in no way affiliated with SAP AG.
SAP, SAP R/3, R/3 software, mySAP, ABAP, BAPI, xApps, SAP NetWeaver and any other are registered trademarks of SAP AG.
Every effort is made to ensure content integrity. Use information on this site at your own risk.