Excel Multiple Worksheets Report Extended
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);
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.