Excel Multiple Worksheets in Dynamics NAV

There are a few solutions for adding Multiple Worksheets to an Excel Workbook for earlier versions of Dynamics NAV, many of these solutions are not transferable into Dynamics NAV version 2013, I thought I would add my solution for Dynamics NAV 2013 R2, in Part One I will show the changes to the Excel Buffer, in Part Two the code added in a report to use the multiple Excel Worksheets, in Part Three I will add functions for formatting and to create real formulas.

The standard code that creates the Worksheets can be found in the functions in table 370 Excel Buffer, this solution will work with a minimal amount of changes in the code, no addition fields are required, the reports and table are in a download at the end of this post is for Dynamics NAV 2013 R2.

The  Solution

The way this solution works is quite simple, it uses the column number Zero to identify a new Worksheet, the last row is saved in a variable to off-set the Row Number for the new Worksheet, the row number is changed temporarily minus the off-set so the rows are correct in the Worksheets, the first Worksheet might have 30 Rows, Row 31 Column 0 will hold the Worksheet name the code will set the off-set to 30 so Row 31 becomes Row 1 of the new Worksheet.

Excel Buffer – Table 370

C/AL Globals

• Variable: Name =  RowOffSet, DataType = Integer
• Text Constants:  Name = SheetBadChars   value =  .?!*\/[]’:
• Function: Name = ValidateSheetName, Local = SheetName text 250
• Function: Name = AddNewWorksheet, Local = NewSheetName text 250
• Function: Name = GetCellID, Locals = ColNo Integer, RowNo Integer, TempExcelBuffer Record Excel Buffer

Documentation

Here I add some comments to tell other developers about the changes I have made.

ADC1.00 Added Functions ValidateSheetName, NewWorkSheet, GetCellID
ADC1.00 Added Variable RowOffSet integer
ADC1.00 Added Commented Code

 

New Function ValidateSheetName

I cannot say that the end user will not use bad characters in a code or text string, if the code was SUMMER\WINTER or the description was “New Summer and Autumn Collection Paris 2015” length (43) which is greater than 31 characters Excel would raise an error when creating the Worksheet Name, this function will strip any bad characters and size any text string passed in to a valid Worksheet Name, the bad characters are in the text constant ‘SheetBadChars’, I will use DELCHR to remove these from the string.

//ADC1.00
IF SheetName = '' THEN
  EXIT(SheetName);

//Remove Bad Characters
SheetName := DELCHR(SheetName,'=',SheetBadChars);
//Strip Leading or Trailing Spaces
SheetName := DELCHR(SheetName,'<>');
//Check the Length of the Sheet Name
IF STRLEN(SheetName) > 30 THEN
  SheetName := FORMAT(SheetName,30);

EXIT(SheetName);

 

New Function AddNewWorksheet

In the code below I check to see if the Excel Buffer record is a new Worksheet, set the ActiveSheetName and add the Worksheet to the Workbook.

 
//ADC1.00 //Is this a new worksheet? 
IF ("Column No." = 0) AND ("Cell Value as Text" <> '') THEN BEGIN
  //Create a New Worksheet Name   
  NewSheetName := ValidateSheetName("Cell Value as Text");
  //Compare to the Active Sheet Name   
  IF NewSheetName <> ActiveSheetName THEN BEGIN
    //Set the ActiveSheetName
    ActiveSheetName := NewSheetName;
    //Set the RowOffSet
    RowOffSet := "Row No." - 1;
    //Add the new Worksheet
    XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(ActiveSheetName);
 END; 
END; 

 

New Function GetCellID

In the code below I pass in a Column and Row Number and return the Cell Reference, Row 1 Column 1 returns A1.

//ADC1.00
//Set the xlColID from Numeric to Alpha (A)
TempExcelBuf.VALIDATE("Column No.",ColNo);
//Set the xlRowID from Numeric to Text  (1)
TempExcelBuf.VALIDATE("Row No.",RowNo);

//Return the Cell ID as a Text String (A1)
EXIT(TempExcelBuf.xlColID+TempExcelBuf.xlRowID);

 

Edit Function WriteSheet

In the code below I have commented my changes.

//ADC1.00

• Set the RowOffSet to Zero
• If the Column No. is Zero and the “Cell Value as Text” has a value set the RowOffSet and ActiveSheetName
• Add the new Worksheet to the Workbook
• Change the Row No. minus the RowOffSet add the Cell or Formula
• Change the Row No. back to the original value plus the RowOffSet

LastUpdate := CURRENTDATETIME;
ExcelBufferDialogMgt.Open(Text005);

CRLF := 10;
RecNo := 1;
TotalRecNo := COUNT + InfoExcelBuf.COUNT;
RecNo := 0;

//ADC1.00 Clear the Variable
RowOffSet := 0;

XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape);

// commit is required because of the result
//boolean check of ExcelBufferDialogMgt.RUN
COMMIT;

IF FINDSET THEN
  REPEAT
    RecNo := RecNo + 1;
    IF NOT UpdateProgressDialog(
      ExcelBufferDialogMgt,LastUpdate,RecNo,TotalRecNo) THEN BEGIN
      QuitExcel;
      ERROR(Text035)
    END;

    //ADC1.00
    //Is this a New Worksheet, if it is add it
    IF ("Column No." = 0) AND ("Cell Value as Text" <> '') THEN
      AddNewWorksheet;
    
    //ADC1.00 Change the Row No. to the Sheet Row No.
    "Row No." := "Row No." - RowOffSet;

    //ADC1.00 If this is not a New Worksheet Write the Cell
    IF "Column No." > 0 THEN

      IF Formula = '' THEN
        WriteCellValue(Rec)
      ELSE
        WriteCellFormula(Rec);

    //ADC1.00 Change the "Row No." back
    "Row No." := "Row No." + RowOffSet;
  
  UNTIL NEXT = 0;

 
That is all the changes to the Excel Buffer, in Part Two I will create a report to show how I can use this feature.

The Sample Table is for Version 2013 R2, the new code and functions are meant to explore a way of adding Multiple Worksheets to an Excel Workbook from Dynamics NAV 2013 R2, it is not meant to go straight into a production environment and should be added and tested in a Cronus test environment.

Please feel free to leave a comment below, any feedback is welcomed and might help others that are looking at this solution, the download contains both fob and text files.
 

Download “Excel Multiple Worksheets Report Extended 2013 R2” Excel-Multiple-Worksheets-Report-Extended-2013-R2.zip – Downloaded 1389 times – 96 KB