Importing Records From Excel Into Table Object in MS Dynamics NAV 2013 R2 (or) NAV 2015

Steps for Importing Records From Excel Into Table Object in MS Dynamics NAV 2013 R2
--------------------------------------------------------------------------------------------------------------

STEP - 1:
CREATE a new Report with Blank DataItem (or) With DataItem (e.g Customer), In My Demo I used Blank DataItem.
Properties : Value
------------------------
ProcessingOnly Yes
-----------------------
STEP - 2:
Create Globals Variables. [ Hint : View -> C/AL GLOBALS ]
VAriable_Name DataType SubType Length
-----------------------------------------------
CustomerDemo Record 50000 [Note : 50000 Is a "Customer Demo Table"
Excelbuffer Recoed 370
ServerFileName Text 50
SheetName Text 50
ImportFromExcel Boolean
RecNo Integer
NoOfRowsSkip Integer
i Integer
----------------------------------------------
Create Globals Constant Variables.
-------------------------
Name ConstValue
Text000 %1 table has been successfully updated with %2 Records.
Text001 Select (or) Import Excel File
ExcelFileExtensionOk .xlsx
----------------------------------------------
STEP - 3:
Design RequestPage (Follow By [VIEW -> Request Page])
------------------------------------------------------
Expanded Type SubType SourceExpr Name Caption
----------------------------------------------------------------------------------------
1 Container ContentArea RequestPage <RequestPage>
1 Group Group Options <Options>
0 Field ImporFromExcel ImportFromExcel Import From Excel
------------------------------------------------------------------------------------------------
STEP - 3:
OPEN the C/AL Editor { Note: Press F9 - on Request Options Page Designer, Only. }
---------------------------------------------------------------------------------
Create Local Variables. [ Hint : View -> C/AL LOCAL ]
-----------------------------------------------------
Name DataType Subtype Length
-----------------------------------------------------
FileMgt Codeunit 419 - File Management
Answer Boolean
------------------------------------------------------
STEP - 4:
Write Code on the Following Trigger:

######## Start ############[Trigers]###############

OnQueryClosePage(CloseAction : Action None) : Boolean
// Code Start

IF ImportFromExcel THEN
  BEGIN
    IF CloseAction = ACTION::OK THEN // Validation For OK Button.
      BEGIN
        ServerFileName := FileMgt.UploadFile(Text001,ExcelFileExtensionOk);
        IF ServerFileName = '' THEN
          EXIT(FALSE);

        SheetName := ExcelBuffer.SelectSheetsName(ServerFileName);
        IF SheetName = '' THEN
          EXIT(FALSE);
      END;
  END;
IF CloseAction = ACTION::Cancel THEN  // Validation For Cancel Button.
  BEGIN
    Answer := CONFIRM('Are you sure want to Cancel?');
    IF Answer THEN
      EXIT(TRUE)
    ELSE
      EXIT(FALSE)
  END;
IF ImportFromExcel = FALSE THEN // Validation For CheckBox.
  BEGIN
    Answer := CONFIRM('You did not select ImportFromExport Check Box.\ If You Click Yes Then You Will Exit From Importing UI.');
    IF Answer THEN
      EXIT(TRUE)
    ELSE
      EXIT(FALSE)
  END;
// Code End

######## END ############[Trigers]###############

STEP - 5:
SELECT Report DataSet Designer [Thru.- Window -> [# Report #ID ImportFromExcel - Report Dataset Designer.
------------------------------------------------------------------------------------------------
OPEN the C/AL CODE [ Hint. View -> C/AL Code, (or) F9]
-------------------------------------------------------
Step - 6:
WRITE the Following Code on respective Trigers.
--------------------------------------------------------
######## Start ############[Trigers]###############

----------------------------------
OnInitReport()
ImportFromExcel := TRUE;
-----------------------------------

OnPreReport()
ImportExcelData() // User Define Function

-------------------------------------------------------------------

OnPostReport()

IF ImportFromExcel THEN
  BEGIN
    ExcelBuffer.DELETEALL;
    IF i > 0 THEN
      MESSAGE(Text000,CustomerDemo.TABLECAPTION,(i-NoOfRowsSkip));
  END;
-----------------------------------------

########### END ##########[Trigers]###############

------------------------------------------------------------------------
######################## My Function #############

ImportExcelData()

// Code for Importing Records From Excel to NAV Table

IF ImportFromExcel THEN
  BEGIN
    CustomerDemo.DELETEALL;
    ExcelBuffer.LOCKTABLE;
    CustomerDemo.LOCKTABLE;
    CustomerDemo.INIT;
    NoOfRowsSkip := 5;  // Bcoz I have planned to skip First 5 Rows/Records.
    i := NoOfRowsSkip;
    ExcelBuffer.OpenBook(ServerFileName,SheetName);
    ExcelBuffer.ReadSheet;
    IF ExcelBuffer.FINDFIRST THEN
      REPEAT
        IF (ExcelBuffer."Row No." = i) AND (ExcelBuffer."Column No." = 1) THEN
          CustomerDemo."No." := ExcelBuffer."Cell Value as Text";
        IF (ExcelBuffer."Row No." = i) AND (ExcelBuffer."Column No." = 2) THEN
            CustomerDemo.Name := ExcelBuffer."Cell Value as Text";
        IF (ExcelBuffer."Row No." = i) AND (ExcelBuffer."Column No." = 3) THEN
          CustomerDemo.Address := ExcelBuffer."Cell Value as Text";
        IF (ExcelBuffer."Row No." = i) AND (ExcelBuffer."Column No." = 4) THEN
          EVALUATE(CustomerDemo."Balance (LCY)", ExcelBuffer."Cell Value as Text");

        IF (ExcelBuffer."Row No." = i) AND (ExcelBuffer."Column No." = 4) THEN BEGIN
          CustomerDemo.INSERT;
          i += 1;
        END;
     UNTIL ExcelBuffer.NEXT = 0 ;
 END;

// End Code

-------------------------------------------------------------------------

############### END Function #############################
STEP - 7:
SAVE, COMPILE And RUN.....
-----------------------------------------------
Thanks & Best Regards
Binesh Singh Rajput
(MCP, MS, MCTS)
----------------------------------------------

##########################################################