Subscribe via RSS Feed Connect on LinkedIn

Excel Multiple Worksheets Report Extended

20th June 2015 0 Comments

In this Final Part I will add functions for formatting and to create real formulas in Excel with Dynamics NAV 2013 R2, in Part One I looked at the changes to the Excel Buffer, in Part Two I explored the code added in a new report to use the multiple Excel Worksheets.

The Report 50006 Export Items by IPG to Excel 2 uses one of the functions in table 370 Excel Buffer I created in Part One, this solution will work with a minimal amount of changes in the code, no addition fields are required, the Table and Report are in the download at the end of this post for Dynamics NAV 2013 R2.

The Solution

The way the report works is quite simple, it creates a new Worksheet of Items linked to each Inventory Posting Group, the Worksheet Name is stored in the Excel Buffer table with a Column No. of Zero, this tells the code in the Excel Buffer to add a new Worksheet.

I am not going to look at all the code in the report as some functions can be found in the Standard Reports that use Excel or in Part Two of this series, I will look at the code in the Functions I have added in the Final Report.

Export Items by IPG 2 – Report 50006

C/AL Globals

  • Variable: Name = SheetRowNo, Integer
  • Variable: Name = HeaderRowNo, Integer
  • Text Constant: Name = Text002, value = Totals For %1
  • Text Constant: Name = Text003, value = Av. Purch. Cost (LCY)
  • Text Constant: Name = Text004, value = Av. Sales Price (LCY)
  • Text Constant: Name = TextSum, value = =SUM(%1:%2)
  • Text Constant: Name = TextAverage, value = =IF(%1=0,0,ROUND(%2/%1,2))
  • Text Constant: Name = AmtFormat, value = 0.00_ ;[Red]-0.00
  • Text Constant: Name = QtyFormat, value = 0_ ;[Red]-0
  • Function: Name = EnterFormulaInCell
  • Function: Name = AddColumnTotals
  • Function: Name = CloseSheet
  • Function: Name = SumColumn
  • Function: Name = SumRow
  • Function: Name = RowAverage

 

New Variables

  • SheetRowNo and HeaderRowNo are used to store integer values for the Sheet Header Row and a running Sheet Row Number, these are used to Sum the Columns.
  • The two formulas TextSum and TextAverage are Excel Formulas, the Cell ID’s will replace the %1 and %2 in the string with a code call, other formulas could be copied from Excel and a function used to populate the Cell ID’s in the formula.
  • AmtFormat (0.00) and QtyFormat (0) are Excel Cell Formats, these can be copied by formatting an Excel Cell, going to Custom selecting a format or creating your own.

 

New Function EnterFormulaInCell

A copy of the EnterCell function is used here to populate the Formula field in the Excel Buffer and clear the Cell Value as Text, when the buffer is processed the table function code will insert a formula string in the Excel Cell.

TempExcelBuffer.INIT;
TempExcelBuffer.VALIDATE("Row No.",RowNo);
TempExcelBuffer.VALIDATE("Column No.",ColumnNo);
TempExcelBuffer."Cell Value as Text" := '';
TempExcelBuffer.Formula := FormulaValue;
TempExcelBuffer.Bold := Bold;
TempExcelBuffer.Italic := Italic;
TempExcelBuffer.Underline := UnderLine;
TempExcelBuffer.NumberFormat := Format;
TempExcelBuffer."Cell Type" := CellType;
TempExcelBuffer.INSERT;

 

New Function AddColumnTotals

When this function is called the Column No. Starting Row No. and Ending Row No. are passed as parameters to the SumRow function, where the Cell ID’s are returned and the Excel Formula is created and passed back to the Excel Buffer.
The SheetRowNo is incremented to populate the two Row Average Cells in the Total Row.

//Only Add a Valid Total Row
IF SheetRowNo > HeaderRowNo THEN BEGIN
  RowNo := RowNo + 1;
  EnterCell(RowNo,1,'',FALSE,FALSE,TRUE,'@',TempExcelBuffer."Cell Type"::Text);
  EnterCell(RowNo,2,STRSUBSTNO(Text002,IPG.Code),FALSE,FALSE,TRUE,'@',TempExcelBuffer."Cell Type"::Text);
  EnterCell(RowNo,3,'',FALSE,FALSE,TRUE,'@',TempExcelBuffer."Cell Type"::Text);
  SumColumn(4,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,QtyFormat);
  SumColumn(5,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,AmtFormat);
  SumColumn(7,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,QtyFormat);
  SumColumn(8,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,AmtFormat);
  SumColumn(9,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,QtyFormat);
  SumColumn(10,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,AmtFormat);
  SumColumn(11,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,QtyFormat);
  SumColumn(12,HeaderRowNo+1,SheetRowNo,FALSE,FALSE,TRUE,AmtFormat);

  SheetRowNo := SheetRowNo + 1;
  //Average Purchase Price  (Column, Quantity, Amount)
  RowAverage(6,4,5,FALSE,FALSE,TRUE,AmtFormat);
  //Average Sales Price (Column, Quantity, Amount)
  RowAverage(13,11,12,FALSE,FALSE,TRUE,AmtFormat);
END;

 

New Function CloseSheet

In this simple function a call is made to the function that adds a Totals Row in the Worksheet using the AddColumnTotals function and the SheetRowNo.

IF (SheetRowNo = 0)OR(HeaderRowNo = 0) THEN
  EXIT;

//Add Column Totals
AddColumnTotals;

 

New Function SumColumn

This function takes the ColNo, HeaderRowNo and the SheetRowNo using a function on the Excel Buffer to return two Cell ID’s and creates the Excel function string to return the sum of Cells in a Column.

IF (ColNo = 0)OR(StartRowNo = 0)OR(EndRowNo = 0)OR(EndRowNo < StartRowNo) THEN
  EXIT;

FormulaText := (
                STRSUBSTNO(
                  TextSum,
                  ExcelBuffer.GetCellID(ColNo,StartRowNo),
                  ExcelBuffer.GetCellID(ColNo,EndRowNo)
                )
              );

EnterFormulaInCell(RowNo,ColNo,FormulaText,Bold,Italic,UnderLine,Format,TempExcelBuffer."Cell Type"::Number);

 

New Function SumRow

This function takes the SheetRowNo, StartColNo and the EndColNo using a function on the Excel Buffer to return two Cell ID’s and creates Excel the function string to return the sum of Cells in a Row.

IF (ColNo = 0)OR(StartColNo = 0)OR(EndColNo = 0)OR(EndColNo < StartColNo) THEN
  EXIT;

FormulaText := (
                STRSUBSTNO(
                  TextSum,
                  ExcelBuffer.GetCellID(StartColNo,SheetRowNo),
                  ExcelBuffer.GetCellID(EndColNo,SheetRowNo)
                )
              );

EnterFormulaInCell(RowNo,ColNo,FormulaText,Bold,Italic,UnderLine,Format,TempExcelBuffer."Cell Type"::Number);

 

New Function RowAverage

This function takes the ColNo, StartRowNo and the EndRowNo using a function on the Excel Buffer to return two Cell ID’s and creates the function string to return the average, rounding Cell 2 / Cell 1 if Cell 1 has a value.

IF (ColNo = 0)OR(StartColNo = 0)OR(EndColNo = 0)OR(EndColNo < StartColNo) THEN
  EXIT;

FormulaText := (
                STRSUBSTNO(
                  TextAverage,
                  ExcelBuffer.GetCellID(StartColNo,SheetRowNo),
                  ExcelBuffer.GetCellID(EndColNo,SheetRowNo)
                )
              );

EnterFormulaInCell(RowNo,ColNo,FormulaText,Bold,Italic,UnderLine,Format,TempExcelBuffer."Cell Type"::Number);

 
20150620215648
 

Conclusion

The Sample Reports and Table are 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, they are 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 1328 times – 96 KB


 

Leave a Reply

You must be logged in to post a comment.