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

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

Answers

  • AlexDenAlexDen Member Posts: 85
    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','','','');
    
  • PETBOZPETBOZ Member Posts: 1
    @AlexDen It works perfectly. Thank you very much.
  • SynkronizerSynkronizer Member Posts: 1
    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
  • MiguelCostaMiguelCosta Member Posts: 4
    Thank you. Works perfect.
  • omerfarukomerfaruk Member Posts: 16
    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)
Sign In or Register to comment.