Dynamics NAV

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)
----------------------------------------------
##########################################################