How to Merge cells in Excel while extracting report from navision using Excel Buffer...

MouleeswaranR

How to Merge cells in Excel while extracting report from navision using Excel Buffer...

I am using navision 2016 . i have tried this https://forum.mibuso.com/discussion/70593/how-to-merge-excel-cell-when-exporting-excel-buffer-nav-2016... but i didnt get solution......

please do needfull

AlexDen

Hi.

Need make the following modifications in Excel Buffer (tested in Nav 2017):
Add global variable:

MergedCellsList : DotNet "'mscorlib, Version=4.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Collections.Generic.List`1";

Add two functions:

PROCEDURE MergeCells(RangeText : Text);
    BEGIN
      IF ISNULL(MergedCellsList) THEN
        MergedCellsList := MergedCellsList.List;

      MergedCellsList.Add(RangeText);
    END;

LOCAL PROCEDURE AddMergedCells();
    VAR
      WrkShtHelper : DotNet "'Microsoft.Dynamics.Nav.OpenXml, Version=10.0.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.Microsoft.Dynamics.Nav.OpenXml.Spreadsheet.WorksheetHelper";
      XlWorkSheet : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.Worksheet";
      XlMergeCells : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.MergeCells";
      XlMergeCell : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.Spreadsheet.MergeCell";
      StringValue : DotNet "'DocumentFormat.OpenXml, Version=2.5.5631.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35'.DocumentFormat.OpenXml.StringValue";
      I : Integer;
    BEGIN
      IF ISNULL(MergedCellsList) THEN
        EXIT;
      IF MergedCellsList.Count = 0 THEN
        EXIT;

      XlWorkSheet := XlWrkShtWriter.Worksheet;
      WrkShtHelper := WrkShtHelper.WorksheetHelper(XlWorkSheet);

      XlMergeCells := XlMergeCells.MergeCells();
      FOR I := 1 TO MergedCellsList.Count DO BEGIN
        StringValue := StringValue.StringValue;
        StringValue.Value := MergedCellsList.Item(I-1);
        XlMergeCell := XlMergeCell.MergeCell();
        XlMergeCell.Reference := StringValue;
        WrkShtHelper.AppendElementToOpenXmlElement(XlMergeCells, XlMergeCell);
      END;
      WrkShtHelper.AppendElementToOpenXmlElement(XlWorkSheet, XlMergeCells);
      XlWorkSheet.Save;

      MergedCellsList.Clear;
    END;

And add code to WriteSheet() function;

...
AddMergedCells;  // Add this line before AddPageSetup
XlWrkShtWriter.AddPageSetup(OrientationValues.Landscape);
...

And use it:

xlBuf.AddColumn('Merged cell',FALSE,'',FALSE,FALSE,FALSE,'',1);
xlBuf.MergeCells('A1:B2');

xlBuf.SetCurrent(3,0);
xlBuf.AddColumn('Merged cell 2',FALSE,'',FALSE,FALSE,FALSE,'',1);
xlBuf.MergeCells('A3:D3');

xlBuf.CreateBookAndOpenExcel('','SheetName','','','');

PETBOZ

@AlexDen It works perfectly. Thank you very much.

Synkronizer

I Think you can Also use Synkronizer Excel Add-in for This. Using this simple tool you can merge cells, columns, and Multiple excel files.
https://www.synkronizer.com/compare-excel-tables-features/merge

MiguelCosta

Thank you. Works perfect.

omerfaruk

If you need this , you wil probably want to vote for this feature for BC SaaS : https://experience.dynamics.com/ideas/idea/?ideaid=d6e5c925-14da-e911-b3b9-0003ff68aec1
( Extended functionality for Excel Buffer)