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
*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.
