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