02-16-2006 2:54 PM
Hello,
My requirement is that i have to read data from an excel file. The difficulty is that i cannot use one of the standard function modules because i have to read from particular cells and particular sheets of the excel file.
Can anyone suggest a way out for this problem.
thanks
Aviroop
02-16-2006 2:58 PM
The code I copied from the Weblog.
Reading data from files is a very common requirement in project implementations. Excel spread sheets are simpler and better readable to text files. The standard ABAP function module ALSM_EXCEL_TO_INTERNAL_TABLE reads data from Excel file into internal table of type ALSMEX_TABLINE. This function module ONLY reads data from the active worksheet i.e., if the excel file has multiple worksheets, the data can be read from any one worksheet. The following customized function module is an alternate approach to read data from all the worksheets within a excel file into the internal table. The source code logic uses the XML classes and interfaces and the assumption is that the source Excel file will be saved as XML Spreadsheet file.
One of the advantages of Excel file is that it can be saved as XML file which is the global standard for data exchange between systems. When an Excel file is saved as XML Spreadsheet, the data is represented in the form of XML elements. Using standard XML Classes and Interfaces, any Excel file with multiple worksheets can be read from ABAP program assuming that the source Excel file is saved as XML Spreadsheet.
The main differences between XML files and Excel XML files lies with the nodes, elements and attribute names. Create an Excel file with two worksheets (MATERIALS, PLANTS) and with sample data in both the worksheets and save as XML Spreadsheet file type and this file is Excel XML file. Open the file in notepad and structure will be in the following format. You will see the difference where the Excel XML files have tags Workbook, Worksheet, Table, Row, Column, Cell and Data.
GET_CONTENT_AS_STRING
RECEIVING
RVAL = V_TXT_VAL.
Step 7.3.2.2
*Populate internal table with data
WA_EXCEL-WORKSHEET = V_WORKSHEET_SS_NAME.
WA_EXCEL-ROW = V_ROW_COUNT.
WA_EXCEL-COLUMN = V_CELL_COUNT.
WA_EXCEL-DTYPE = V_DATA_TYPE.
WA_EXCEL-VALUE = V_TXT_VAL.
APPEND WA_EXCEL TO IT_EXCEL.
The structure representing the internal table IT_EXCEL is enhanced structure of ALSMEX_TABLINE with the above fields.
There are few points need to be known. Any empty cells in the Excel XML files are not displayed in the XML structure.In order to keep track of empty cells, we can use the 'Index' property of the Cell. Also, the value of the cell can be extracted depending on the data type of the Cell(with leading zeros,signs,decimals and date).
There may be other ways of reading data from multiple worksheets but this approach has the advantage of working with XML and using standard SAP XML classes in a powerful way.
Kamal Shaik is a Certified ABAP and XI Consultant working for SAP GDC, India.
________________________________________
This approach can be enhanced further to map the other features of Excel XML files(like merging of cells). Any corrections or suggestions are welcome.
Comment on this weblog
Showing messages 1 through 5 of 5.
________________________________________
Titles Only
Main Topics
Oldest First
Problem to use this Algorithm
2006-02-01 03:48:14 Duarte Santos Business Card
Hi there Kamal, i am trying to do something like you explain in this weblog, the problem is that cannot find weblog´s with the first steps:
1.Upload data from Excel XML file.
2.Create XML Stream factory and Input stream to hold XML
data.
3.Create a new XML DOM.
4.Create the parser using the stream and the DOM object.
Can you help me?
Problem to use this Algorithm
2006-02-01 06:11:45 Kamal Shaik Business Card
Hi,
Following is the code for the first 4 steps and the required declarations.
*EXCEL XML file name
DATA: FILENAME TYPE STRING.
*Main iXML factory
DATA: G_IXML TYPE REF TO IF_IXML.
*XML data bytes size
DATA: G_XML_DATA_BYTES TYPE I.
*XML data
DATA: G_XML_DATA(500) OCCURS 0.
*Stream factory.
DATA: G_STREAMFACTORY TYPE REF TO IF_IXML_STREAM_FACTORY.
*XML istream
DATA: G_ISTREAM TYPE REF TO IF_IXML_ISTREAM.
*XML parser
DATA: G_PARSER TYPE REF TO IF_IXML_PARSER.
*XML DOM object
DATA: G_OBJ_DOM_XML TYPE REF TO IF_IXML_DOCUMENT.
*Upload the excel XML file
CALL METHOD cl_gui_frontend_services=>gui_upload
EXPORTING
filename = FILENAME
filetype = 'BIN'
IMPORTING
filelength = G_XML_DATA_BYTES
CHANGING
data_tab = G_XML_DATA.
*Load ixml library
CLASS CL_IXML DEFINITION LOAD.
*Creating the main factory for the iXML library
G_IXML = CL_IXML=>CREATE( ).
*Create a main stream factory.
CALL METHOD G_IXML->CREATE_STREAM_FACTORY
RECEIVING
RVAL = G_STREAMFACTORY.
*Create a stream for the itab containg XML raw content.
CALL METHOD G_STREAMFACTORY->CREATE_ISTREAM_ITABLE
EXPORTING
SIZE = G_XML_DATA_BYTES
TABLE = G_XML_DATA[]
RECEIVING
RVAL = G_ISTREAM.
*Create a new XML DOM object.
G_OBJ_DOM_XML = G_IXML->CREATE_DOCUMENT( ).
*Create XML parser.
CALL METHOD G_IXML->CREATE_PARSER
EXPORTING
STREAM_FACTORY = G_STREAMFACTORY
ISTREAM = G_ISTREAM
DOCUMENT = G_OBJ_DOM_XML
RECEIVING
RVAL = G_PARSER.
*Parse the XML source.
CALL METHOD G_PARSER->PARSE
RECEIVING
RVAL = V_SUBRC.
IF V_SUBRC <> 0.
EXIT.
ENDIF.
*Make sure DOM is created
IF G_PARSER->IS_DOM_GENERATING( ) NE 'X'.
EXIT.
ENDIF.
*Close the opened stream and thereby the opened xml file.
CALL METHOD G_ISTREAM->CLOSE
Nice One!
2006-01-25 06:52:59 Abdul Hakim Business Card
Hi Kamal,
Very nice weblog.Keep up ur good work..
Well is it possible to read from an excel work book which is not saved in XML format?
Regards,
Abdul
Nice One!
2006-01-25 07:50:06 Kamal Shaik Business Card
Hi Abdul,
We can read data from excel file using standard function module, but that would be from ONLY one worksheet. I couldn't find anything standard to read from multiple worksheets and that's how I came up with this approach.
Let me know if you find any alternate approach.
Thanks
Kamal
Nice One!
2006-01-28 09:15:59 Svetlin Rusev Business Card
I guess, multiple worksheets can be read using Desktop office intergration.
Svetlin
02-16-2006 3:00 PM
02-16-2006 3:03 PM
Hi,
Please check this code.use FM : 'ALSM_EXCEL_TO_INTERNAL_TABLE' to upload excel data into internal table. then process the internal table data according to starting row and column of the excel sheet.
Laxman
code :
CALL FUNCTION 'ALSM_EXCEL_TO_INTERNAL_TABLE'
EXPORTING
filename = p_file
i_begin_col = 1
i_begin_row = 1
i_end_col = 11
i_end_row = 9999
TABLES
intern = i_excel
EXCEPTIONS
inconsistent_parameters = 1
upload_ole = 2
OTHERS = 3.
IF sy-subrc <> 0.
MESSAGE e000(zo_spa) WITH text-063."Invalid File
ENDIF.
LOOP AT i_excel.
MOVE-CORRESPONDING i_excel TO i_intern1.
APPEND i_intern1.
CLEAR i_intern1.
ENDLOOP.
ws_row_id = '0001'.
LOOP AT i_intern1 INTO wa_intern1.
*Header is removed
IF wa_intern1-row EQ ws_row_id.
CONTINUE.
ENDIF.
CASE wa_intern1-col.
WHEN '1'.
CONDENSE wa_intern1-value-line.
i_data-cust_no = wa_intern1-value-line.
WHEN '2'.
CONDENSE wa_intern1-value-line.
i_data-inst_name = wa_intern1-value-line.
WHEN '3'.
CONDENSE wa_intern1-value-line.
i_data-street = wa_intern1-value-line.
WHEN '4'.
CONDENSE wa_intern1-value-line.
i_data-house_no = wa_intern1-value-line.
WHEN '5'.
CONDENSE wa_intern1-value-line.
i_data-city = wa_intern1-value-line.
WHEN '6'.
CONDENSE wa_intern1-value-line.
i_data-post_code = wa_intern1-value-line.
WHEN '7'.
CONDENSE wa_intern1-value-line.
i_data-mprn1 = wa_intern1-value-line.
WHEN '8'.
CONDENSE wa_intern1-value-line.
i_data-mprn2 = wa_intern1-value-line.
WHEN '9'.
CONDENSE wa_intern1-value-line.
i_data-mprn3 = wa_intern1-value-line.
WHEN '10'.
CONDENSE wa_intern1-value-line.
i_data-mprn4 = wa_intern1-value-line.
WHEN '11'.
CONDENSE wa_intern1-value-line.
i_data-mprn5 = wa_intern1-value-line.
ENDCASE.
AT END OF row.
APPEND i_data.
CLEAR i_data.
ENDAT.
ENDLOOP.
02-16-2006 3:06 PM
<i>....I cannot use one of the standard function modules because i have to read from particular cells and particular sheets of the excel file.</i>
I do not believe that ALSM_EXCEL_TO_INTERNAL_TABLE will help you with reading certain sheets of an excel file.
You can use this function module to show you how to work with OLE. You just need to add the part where it is selecting a certain sheet.
Regards,
Rich Heilman
03-03-2006 5:54 AM
Hi Alok,
Try using the FM ,
CATSXT_GET_HOLIDAYS for finding out no of days defined as holidays in ur HR Module.It will give you entries as dates falling on Holidays in an internal table. Take a count of the internal table.
Use the FM HR_SGPBS_YRS_MTHS_DAYS to find out no of days between a date range.
Subtract the days between date ranges and the no of days as holidays between them.
I have tried to write a piece of code for you.
If you find it helpful,Please reward point.
REPORT zharmayforum_days .
DATA: wa_days TYPE i.
PARAMETERS: wa_frdat TYPE sy-datum DEFAULT '20051216',
wa_todat TYPE sy-datum DEFAULT '20051221'.
PERFORM get_days USING wa_frdat wa_todat CHANGING wa_days.
WRITE: 'No. of days: ', wa_days.
&----
*& Form GET_DAYS
&----
text
----
-->P_WA_FRDAT text
-->P_WA_TODAT text
<--P_WA_DAYS text
----
FORM get_days USING p_wa_frdat
p_wa_todat
CHANGING p_wa_days.
DATA : tot_days TYPE i,
tot_holi TYPE i.
DATA : holi_table TYPE catsxt_iscal_day_itab.
DATA: lin TYPE i,
ini TYPE i,
knd TYPE c.
CALL FUNCTION 'HR_SGPBS_YRS_MTHS_DAYS'
EXPORTING
beg_da = p_wa_frdat
end_da = p_wa_todat
IMPORTING
NO_DAY =
NO_MONTH =
NO_YEAR =
no_cal_day = tot_days
EXCEPTIONS
dateint_error = 1
OTHERS = 2
.
IF sy-subrc <> 0.
MESSAGE ID SY-MSGID TYPE SY-MSGTY NUMBER SY-MSGNO
WITH SY-MSGV1 SY-MSGV2 SY-MSGV3 SY-MSGV4.
ENDIF.
CALL FUNCTION 'CATSXT_GET_HOLIDAYS'
EXPORTING
im_personnel_number = '00000024'
im_begin_date = p_wa_frdat
im_end_date = p_wa_todat
im_get_weekend_days = 'X'
IMPORTING
ex_holidays = holi_table.
DESCRIBE TABLE holi_table LINES lin OCCURS ini KIND knd.
p_wa_days = tot_days - lin.
ENDFORM. " GET_DAYS.
Thanks,
Mayank
09-29-2006 12:00 PM
to read data from excel for particular worksheet, I've modified FM ALSM_EXCEL_TO_INTERNAL_TABLE a little bit..
add 1 new parameter Called :
- SHEETNO TYPE I to your NEW FM
these are the steps:
- Create a new FM, copy all sourcecode from ALSM_EXCEL_TO_INTERNAL_TABLE, including parameters and other needed files to your new FM (eq : ZALSM_EXCEL).
- Add 1 new Import (parameter) as follow:
>> Sheetno type I
- Open ZALSM_EXCEL source code
- Find Sentence:
*****
set property of application 'Visible' = 1.
m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
*****
and replace with the following code:
*****
set property of application 'Visible' = 1.
m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
CALL METHOD OF APPLICATION 'Worksheets' =
worksheet EXPORTING #1 = sheetno.
m_message.
CALL METHOD OF worksheet 'Activate'.
m_message.
GET PROPERTY OF application 'ACTIVESHEET' = worksheet.
m_message.
*****
- then call the FM as follow:
CALL FUNCTION 'ZALSM_EXCEL'
EXPORTING
FILENAME = FILE
I_BEGIN_COL = '1'
I_BEGIN_ROW = '1'
I_END_COL = '200'
I_END_ROW = '5000'
this is a new parameter
SHEETNO = P_SHEET
****
TABLES
INTERN = XCEL
EXCEPTIONS
INCONSISTENT_PARAMETERS = 1
UPLOAD_OLE = 2
OTHERS = 3.
- hope its help.
regards,
andri thaslim
09-29-2006 2:23 PM
06-26-2012 7:56 AM
Check my own developed FM:
< Request for points removed >
************************************************************************
* PROGRAM TITLE : Z_READ_MULTISHEET_EXCEL. *
*----------------------------------------------------------------------*
* CREATED BY : SATYANARAYANA GUDEPU *
* DESCRIPTION : This FM is part of PB calculator programme, which *
* is used to upload Performance Bonus detials of employ*
* ees(P) by HR users. Excel sheet with multiple sheets *
* are taken as input. *
*----------------------------------------------------------------------*
* Note : All subsheets are must be similar to each other. *
*----------------------------------------------------------------------*
* TABLES : *
* MODULE : HR-PA *
* TYPE : ABAP Report *
* R/3 Release : SAP ECC 6.0 *
*----------------------------------------------------------------------*
* VERSION HISTORY *
*----------------------------------------------------------------------*
* AUTHOR | DATE |Req No| TR NO. | DESCRIPTION *
************************************************************************
*
*
************************************************************************
FUNCTION z_read_multisheet_excel.
*"----------------------------------------------------------------------
*"*"Local Interface:
*" IMPORTING
*" REFERENCE(XLS_FILE_NAME) TYPE RLGRAP-FILENAME
*" REFERENCE(START_ROW_ALLSHEETS) TYPE I
*" REFERENCE(START_COLUMN_ALLSHEETS) TYPE I
*" REFERENCE(END_ROW_ALLSHEETS) TYPE I
*" REFERENCE(END_COLUMN_ALLSHEETS) TYPE I
*" REFERENCE(MULTI_SHEET) TYPE CHAR1 DEFAULT 'X'
*" REFERENCE(WITH_HEADER) TYPE CHAR1 DEFAULT 'X'
*" TABLES
*" IT_DATATAB STRUCTURE ALSMEX_TABLINE
*" EXCEPTIONS
*" INCONSISTENT_PARAMETERS
*" UPLOAD_OLE
*"----------------------------------------------------------------------
TYPES: ty_t_sender(1500) TYPE c.
DATA: application TYPE ole2_object,
workbook TYPE ole2_object,
sheet TYPE ole2_object,
range TYPE ole2_object,
worksheet TYPE ole2_object,
h_cell TYPE ole2_object,
h_cell1 TYPE ole2_object,
ld_separator TYPE c,
ld_rc TYPE i,
lv_sheet TYPE i,
lv_flag TYPE i.
DATA: excel_tab TYPE TABLE OF ty_s_senderline,
excel_tab1 TYPE TABLE OF ty_s_senderline,
it_data TYPE STANDARD TABLE OF alsmex_tabline INITIAL SIZE 0.
*Function Module To Upload Data From
*Excel File Into Two Internal Tables
*© 2005 SAP AG 6
*__MESSAGE DEFINATION
DEFINE m_message.
case sy-subrc.
when 0.
when 1.
message id sy-msgid type sy-msgty number sy-msgno
with sy-msgv1 sy-msgv2 sy-msgv3 sy-msgv4.
when others. raise upload_ole.
endcase.
END-OF-DEFINITION.
*PARAMETER CHECK.
IF start_row_allsheets > end_row_allsheets.
RAISE inconsistent_parameters.
ENDIF.
IF start_column_allsheets > end_column_allsheets.
RAISE inconsistent_parameters.
ENDIF.
CLASS cl_abap_char_utilities DEFINITION LOAD.
ld_separator = cl_abap_char_utilities=>horizontal_tab.
*__Create Excel Application.
IF application-header = space OR application-handle = -1.
CREATE OBJECT application 'Excel.Application'.
m_message.
ENDIF.
CALL METHOD OF
application
'Workbooks' = workbook.
m_message.
CALL METHOD OF
workbook
'Open'
EXPORTING
#1 = xls_file_name.
m_message.
CLEAR: lv_sheet, lv_flag.
*__Check for multisheet.
IF multi_sheet IS NOT INITIAL.
lv_sheet = 99.
ELSE.
lv_sheet = 1.
ENDIF.
*__Get data from all worksheets; can work for 99 sheets.
DO lv_sheet TIMES.
lv_flag = lv_flag + 1.
CALL METHOD OF
application
'Worksheets' = sheet
EXPORTING
#1 = lv_flag.
m_message.
*__Make active the sheet of given sheet no.
CALL METHOD OF
sheet
'Activate'.
m_message.
GET PROPERTY OF application 'ACTIVESHEET' = sheet.
m_message.
CALL METHOD OF
sheet
'Cells' = h_cell
EXPORTING
#1 = start_row_allsheets
#2 = start_column_allsheets.
m_message.
CALL METHOD OF
sheet
'Cells' = h_cell1
EXPORTING
#1 = end_row_allsheets
#2 = end_column_allsheets.
m_message.
CALL METHOD OF
sheet
'RANGE' = range
EXPORTING
#1 = h_cell
#2 = h_cell1.
m_message.
CALL METHOD OF
range
'SELECT'.
m_message.
*__Copy marked area (SHEET1) into Clippboard
CALL METHOD OF
range
'COPY'.
m_message.
*__Read clipboard into ABAP.
REFRESH: excel_tab, it_data.
CALL METHOD cl_gui_frontend_services=>clipboard_import
IMPORTING
data = excel_tab
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc <> 0.
MESSAGE a037(alsmex).
ENDIF.
*__Function Module To Upload Data From Excel File Into Two Internal Tables
PERFORM separated_to_intern_convert TABLES excel_tab it_data
USING ld_separator.
APPEND LINES OF it_data TO it_datatab.
*__Clear the clipboard
REFRESH excel_tab.
CALL METHOD cl_gui_frontend_services=>clipboard_export
IMPORTING
data = excel_tab
CHANGING
rc = ld_rc
EXCEPTIONS
cntl_error = 1
error_no_gui = 2
not_supported_by_gui = 3
OTHERS = 4.
IF sy-subrc EQ 0.
ENDIF.
ENDDO.
*__Leaving Application
CALL METHOD OF
application
'QUIT'.
m_message.
CALL METHOD OF
application
'FREE'.
m_message.
* >>>>> Begin of change note 575877
* to kill the Excel process it's necessary to free all used objects
FREE OBJECT h_cell. m_message.
FREE OBJECT h_cell1. m_message.
FREE OBJECT range. m_message.
FREE OBJECT worksheet. m_message.
FREE OBJECT workbook. m_message.
FREE OBJECT application. m_message.
* <<<<< End of change note 575877
*__Remove header records.
ENDFUNCTION.
Moderator Message : Please do not ask for points hereafter.
Message was edited by: Kesavadas Thekkillath
06-26-2012 6:15 PM
And how would your employer feel if they new that code that they had paid you to develop is posted on the internet?
Rob
06-26-2012 9:10 AM
Hi Aviroop,
Please, look through the next document
http://wiki.sdn.sap.com/wiki/display/ABAP/Excel+with+SAP+-+An+overview
There is collected lots of different ways how to read\write Excel documents. Choose the one which fits you.
Also for your case you can use DOI (Desktop Office Integration).
06-26-2012 12:17 PM
06-26-2012 12:56 PM
Hii Aviroop,
OLE concept is best method to upload Excel sheet.
Regards,
Ashwath.
06-26-2012 5:12 PM
Question was posted : 6 years ago....
Aviroop Deb Feb 16, 2006 3:54 PM