Create Multiple Excel Worksheet in Microsoft Navision 2013

jigneshdhandha

Dear All

In Navision 2009 R2 We developed functionality to Export your Data into Excel With Multiple Sheets
Now Excel Buffer Structure is Changed in NAV 2013 So We can’t use old version code directly

For that I have developed Functionality and also prepare technical design document to develop this functionality

Attached Object Details :

Type ID Name
Table 370 Excel Buffer
Report 80000 Create Multiple Excel Sheet

To Create Multiple Worksheet in Microsoft Navision 2013 through Export to Excel Functionality

1) Import this fob of Table 370 Excel Buffer into Cronus Database
2) This is Sample Code to Create Multiple Worksheet into Excel

Boolean Arguments Explanation :

1: When you call CreateMultiSheet_gFnc function First Time you have to Set First Boolean Argument = TRUE Other Wise = False
2 : When you call CreateMultiSheet_gFnc function Not for First Time and Last Time then First Boolean Argument = False and Second Argument = false
3 : When you call CreateMultiSheet_gFnc function Last Time you have to Set Second Argument = TRUE Other Wise = False

3) Import attached Report 80000 and you can Create Multiple Worksheet in NAV 2013

Let's Start

Create Multiple Work Sheet in NAV 2013 through Export to Excel Functionality

1) Add below Function in Table – 370 Excel Buffer
Function Name Local
CreateMultiSheet_gFnc No

WriteSheetModified_gFnc No
WriteCellValueModified_gFnc Yes
WriteCellFormulaModified_gFnc Yes
GetCellDecoratorModified_gFnc Yes


Function Definition

1) CreateMultiSheet_gFnc

1) Function Arguments :
Var Name DataType Subtype Length Temporary
No SheetName_iTxt Text 250
No ReportHeader_iTxt Text 80
No CompanyName_iTxt Text 30
No UserID2_iTxt Text 30
Yes ExcelBuffer_vRecTmp Record Excel Buffer Yes
No FirstWorkSheet_iBln Boolean
No LastWorkSheet_iBln Boolean

2) Function Variables :
Name DataType Subtype Length
Window_lDlg Dialog


3) Function Code :

//For Creating Multiple Sheet
//1 : When you call this function First Time you have to Set FirstWorkSheet_iBln = TRUE Other Wise = False
//2 : When you call this function Not for First Time and Last Time then FirstWorkSheet_iBln = False and LastWorkSheet_iBln = false
//3 : When you call this function Last Time you have to Set LastWorkSheet_iBln = TRUE Other Wise = False
IF FirstWorkSheet_iBln THEN
CreateBook(SheetName_iTxt);

Window_lDlg.OPEN(
Text005 +
'@\');
Window_lDlg.UPDATE(1,0);


WITH ExcelBuffer_vRecTmp DO BEGIN
IF FIND('-') THEN BEGIN
IF NOT FirstWorkSheet_iBln THEN
XlWrkShtWriter := XlWrkBkWriter.AddWorksheet(SheetName_iTxt);
REPEAT
WriteSheetModified_gFnc(ReportHeader_iTxt,CompanyName_iTxt,UserID2_iTxt,ExcelBuffer_vRecTmp,XlWrkShtWriter,XlWrkBkWriter)
UNTIL NEXT = 0;
END;
END;

Window_lDlg.CLOSE;
IF LastWorkSheet_iBln THEN BEGIN
CloseBook;
OpenExcel;
GiveUserControl;
END;

2) WriteSheetModified_gFnc

1) Function Parameters :
Var Name DataType Subtype Length Temporary
No ReportHeader_iTxt Text 80
No CompanyName_iTxt Text 30
No UserID2_iTxt Text 30
Yes ExcelBuffer_vRecTmp Record Excel Buffer Yes
Yes XlWrkShtWriter_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Yes XlWrkBkWriter_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorkbookWriter.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

2) Function Variables :

Name DataType Subtype Length
ExcelBufferDialogMgt_lCdu Codeunit Excel Buffer Dialog Management
OrientationValues_lDot DotNet DocumentFormat.OpenXml.Spreadsheet.OrientationValues.'DocumentFormat.OpenXml, Version=2.0.5022.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
CRLF_Chr Char
RecNo_lInt Integer
InfoRecNo_lInt Integer
TotalRecNo_lInt Integer
LastUpdate_lDat DateTime



3) Function Code :

LastUpdate_lDat := CURRENTDATETIME;
ExcelBufferDialogMgt_lCdu.Open(Text005);

CRLF_Chr := 10;
RecNo_lInt := 1;
TotalRecNo_lInt := COUNT + InfoExcelBuf.COUNT;
RecNo_lInt := 0;

XlWrkShtWriter_vDot.AddPageSetup(OrientationValues_lDot.Landscape);

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

IF FINDSET THEN
REPEAT
RecNo_lInt := RecNo_lInt + 1;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt_lCdu,LastUpdate_lDat,RecNo_lInt,TotalRecNo_lInt) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
WITH ExcelBuffer_vRecTmp DO BEGIN
IF Formula = '' THEN
WriteCellValueModified_gFnc(ExcelBuffer_vRecTmp,XlWrkShtWriter_vDot)
ELSE
WriteCellFormulaModified_gFnc(ExcelBuffer_vRecTmp,XlWrkShtWriter_vDot);
END;
UNTIL NEXT = 0;


IF ReportHeader_iTxt <> '' THEN
XlWrkShtWriter_vDot.AddHeader(
TRUE,
STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader_iTxt,CRLF_Chr,CompanyName_iTxt));

XlWrkShtWriter_vDot.AddHeader(
FALSE,
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF_Chr,UserID2_iTxt));

IF UseInfoSheet THEN BEGIN
IF InfoExcelBuf.FINDSET THEN BEGIN
XlWrkShtWriter_vDot := XlWrkBkWriter_vDot.AddWorksheet(Text023);
REPEAT
InfoRecNo_lInt := InfoRecNo_lInt + 1;
IF NOT UpdateProgressDialog(ExcelBufferDialogMgt_lCdu,LastUpdate_lDat,RecNo_lInt + InfoRecNo_lInt,TotalRecNo_lInt) THEN BEGIN
QuitExcel;
ERROR(Text035)
END;
IF InfoExcelBuf.Formula = '' THEN
WriteCellValue(InfoExcelBuf)
ELSE
WriteCellFormula(InfoExcelBuf)
UNTIL InfoExcelBuf.NEXT = 0;
END;
END;

ExcelBufferDialogMgt_lCdu.Close;


3) WriteCellValueModified_gFnc

1) Function Parameters :
Var Name DataType Subtype Length Temporary
Yes ExcelBuffer_vRecTmp Record Excel Buffer Yes
Yes XlWrkShtWriter_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'


2) Functional Variables :
Name DataType Subtype Length
Decorator_lDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

3) Function Code :

WITH ExcelBuffer_vRecTmp DO BEGIN
GetCellDecoratorModified_gFnc(Bold,Italic,Underline,Decorator_lDot,XlWrkShtWriter_vDot);

CASE "Cell Type" OF
"Cell Type"::Number:
XlWrkShtWriter_vDot.SetCellValueNumber("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator_lDot);
"Cell Type"::Text:
XlWrkShtWriter_vDot.SetCellValueText("Row No.",xlColID,"Cell Value as Text",Decorator_lDot);
"Cell Type"::Date:
XlWrkShtWriter_vDot.SetCellValueDate("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator_lDot);
"Cell Type"::Time:
XlWrkShtWriter_vDot.SetCellValueTime("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator_lDot);
ELSE
ERROR(Text039)
END;
END;

4) WriteCellFormulaModified_gFnc

1) Function Parameters and Function Variables are same as WriteCellValueModified_gFnc Function
2) Function Code :

WITH ExcelBuffer_vRecTmp DO BEGIN
GetCellDecoratorModified_gFnc(Bold,Italic,Underline,Decorator_lDot,XlWrkShtWriter_vDot);

XlWrkShtWriter_vDot.SetCellFormula("Row No.",xlColID,GetFormula,NumberFormat,Decorator_lDot);
END;

5) GetCellDecoratorModified_gFnc

1) Function Parameters
Var Name DataType Subtype Length
No IsBold_iBln Boolean
No IsItalic_iBln Boolean
No IsUnderlined_iBln Boolean
Yes Decorator_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.CellDecorator.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'
Yes XlWrkShtWriter_vDot DotNet Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetWriter.'Microsoft.Dynamics.Nav.OpenXml, Version=7.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'

IF IsBold_iBln AND IsItalic_iBln AND IsUnderlined_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultBoldItalicUnderlinedCellDecorator;
EXIT;
END;

IF IsBold_iBln AND IsItalic_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultBoldItalicCellDecorator;
EXIT;
END;

IF IsBold_iBln AND IsUnderlined_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultBoldUnderlinedCellDecorator;
EXIT;
END;

IF IsBold_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultBoldCellDecorator;
EXIT;
END;

IF IsItalic_iBln AND IsUnderlined_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultItalicUnderlinedCellDecorator;
EXIT;
END;

IF IsItalic_iBln THEN BEGIN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultItalicCellDecorator;
EXIT;
END;

IF IsUnderlined_iBln THEN
Decorator_vDot := XlWrkShtWriter_vDot.DefaultUnderlinedCellDecorator
ELSE
Decorator_vDot := XlWrkShtWriter_vDot.DefaultCellDecorator;

2) Create Report 80000 – Create Multiple Excel Sheet

1) Add Three Data Item as shown in below Screen Shot



2) Add Code as per below

OnInitReport()

OnPreReport()

OnPostReport()
CreateExcelbook_gFnc;

Payment Terms - OnPreDataItem()
MakeExcelDataHeader_gFnc;

Payment Terms - OnAfterGetRecord()
MakeExcelDataBody_gFnc;

Payment Terms - OnPostDataItem()

Location - OnPreDataItem()
MakeExcelDataHeader2_gFnc;

Location - OnAfterGetRecord()
MakeExcelDataBody2_gFnc;

Location - OnPostDataItem()

Currency - OnPreDataItem()
MakeExcelDataHeader3_gFnc;

Currency - OnAfterGetRecord()
MakeExcelDataBody3_gFnc;

Currency - OnPostDataItem()

MakeExcelDataHeader_gFnc()
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION(Code),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Due Date Calculation"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Discount Date Calculation"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Discount %"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION(Description),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Calc. Pmt. Disc. on Cr. Memos"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION(Type),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Cash Discount %"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms".FIELDCAPTION("Tax/Charge Code"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.NewRow;

MakeExcelDataHeader2_gFnc()
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION(Code),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION(Name),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Name 2"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION(Address),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Address 2"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION(City),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Phone No."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Phone No. 2"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Telex No."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Fax No."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION(Contact),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.FIELDCAPTION("Post Code"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.NewRow;

MakeExcelDataBody_gFnc()
ExcelBuf1_gRec.AddColumn("Payment Terms".Code,FALSE,'',FALSE,FALSE,FALSE,'@',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Due Date Calculation",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Discount Date Calculation",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Discount %",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf1_gRec."Cell Type"::Number);
ExcelBuf1_gRec.AddColumn("Payment Terms".Description,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Calc. Pmt. Disc. on Cr. Memos",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn(FORMAT("Payment Terms".Type),FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Cash Discount %",FALSE,'',FALSE,FALSE,FALSE,'#,##0.00',ExcelBuf1_gRec."Cell Type"::Number);
ExcelBuf1_gRec.AddColumn("Payment Terms"."Tax/Charge Code",FALSE,'',FALSE,FALSE,FALSE,'@',ExcelBuf1_gRec."Cell Type"::Text);
ExcelBuf1_gRec.NewRow;

MakeExcelDataHeader3_gFnc()
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION(Code),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Unrealized Gains Acc."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Realized Gains Acc."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Unrealized Losses Acc."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Realized Losses Acc."),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION(Description),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Amount Decimal Places"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Unit-Amount Decimal Places"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Customer Filter"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.FIELDCAPTION("Vendor Filter"),FALSE,'',TRUE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.NewRow;

MakeExcelDataBody2_gFnc()
ExcelBuf2_gRec.AddColumn(Location.Code,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.Name,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Name 2",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.Address,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Address 2",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.City,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Phone No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Phone No. 2",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Telex No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Fax No.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location.Contact,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.AddColumn(Location."Post Code",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf2_gRec.NewRow;


MakeExcelDataBody3_gFnc()
ExcelBuf3_gRec.AddColumn(Currency.Code,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Unrealized Gains Acc.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Realized Gains Acc.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Unrealized Losses Acc.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Realized Losses Acc.",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency.Description,FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Amount Decimal Places",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Unit-Amount Decimal Places",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Customer Filter",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.AddColumn(Currency."Vendor Filter",FALSE,'',FALSE,FALSE,FALSE,'',ExcelBuf2_gRec."Cell Type"::Text);
ExcelBuf3_gRec.NewRow;

CreateExcelbook_gFnc()
ExcelBuf_gRec.CreateMultiSheet_gFnc(
'Payment Terms','',COMPANYNAME,USERID,ExcelBuf1_gRec,TRUE,FALSE);
ExcelBuf_gRec.CreateMultiSheet_gFnc(
'Location','',COMPANYNAME,USERID,ExcelBuf2_gRec,FALSE,FALSE);
ExcelBuf_gRec.CreateMultiSheet_gFnc(
'Currency','',COMPANYNAME,USERID,ExcelBuf3_gRec,FALSE,TRUE);
ERROR('');

3) When you Run Report you got output in Multiple Excel Worksheet

jigneshdhandha

You can try because this is already tested.

Thanks.

Rob_Hansen

Thanks for sharing your solution!

We implemented this functionality in a different way, rather than making any changes to the base Excel Buffer logic. We added some functions to the codeunit that use the ClosedXML library to create the multi-page book as a post-processing action. We save each Excel Buffer book to a temp file name on the server, then we use ClosedXML to copy all of the sheets into one Excel book and open that for the user. Same end result as your approach...just a different way of doing it.

The Excel Buffer changes in NAV 2013 definitely mean re-thinking the way things were approached in the past!

Miklos_Hollender

rdhansen wrote:

The Excel Buffer changes in NAV 2013 definitely mean re-thinking the way things were approached in the past!

Actually just saving the table in 2009 as 50370 Classic Excel Buffer and importing it works well.

Probably not the most future proof solution but it buys some time. In my experience a report made for a business loses validity after 5-10-15 years in the sense that either their business changes so much or their employee turnover gets so large that they cannot use it anymore (as the people who knew where it was and how to use it have left), so if this solution works for a just few more years it may be that the report loses validity anyway and you can save the time of rewriting it.

Rob_Hansen

The problem with that is that the Excel buffer is DEATHLY slow in NAV 2009 with its automation calls down to the client. For small Excel exports it's slow but usable...for large ones it takes AGES and will drive anyone to bash their head against the wall.