NAV 2013 R2 Extending Excel Buffer with openXml

troydohrman

I'm trying to add functionality to the excel buffer table in NAV 2013 R2. I want to be able to set the column width. I'm following the example in the following document...

https://mbs.microsoft.com/files/partner ... 2013R2.pdf

I've recreated it exactly but when I call the SetColumnWidth function in my add-in from the WriteCellValue trigger, the Columns variable is Null and causes an error.

Here is the NAV code in the ExcelBuffer table...

LOCAL WriteCellValue(ExcelBuffer : Record "Excel Buffer")
WITH ExcelBuffer DO BEGIN
  GetCellDecorator(Bold,Italic,Underline,Decorator);

  CASE "Cell Type" OF
    "Cell Type"::Number:
      XlWrkShtWriter.SetCellValueNumber("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
    "Cell Type"::Text:
      XlWrkShtWriter.SetCellValueText("Row No.",xlColID,"Cell Value as Text",Decorator);
    "Cell Type"::Date:
      XlWrkShtWriter.SetCellValueDate("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
    "Cell Type"::Time:
      XlWrkShtWriter.SetCellValueTime("Row No.",xlColID,"Cell Value as Text",NumberFormat,Decorator);
    ELSE
      ERROR(Text039)
  END;
  // Cust1.00 Start
  IF "Column Width" > 0 THEN BEGIN
    myOpenXml.SetColumnWidth("Column No.", "Column Width", XlWrkShtWriter.Worksheet);
  END;
  // Cust1.00 End
END;

Here is the c# add-in code I created...

public static void SetColumnWidth(uint columnIndex, decimal width, Worksheet openXmlWorksheet)
        {
            var columns = openXmlWorksheet.GetFirstChild<Columns>();
            var column = columns.ChildElements.FirstOrDefault(t => (t is Column) && (t as Column).Min <= columnIndex && (t as Column).Max <= columnIndex) as Column;

            if (column == null)
            {
                column = new Column()
                {
                    Min = columnIndex,
                    Max = columnIndex
                };
                columns.Append(column);
            }
            column.CustomWidth = true;
            column.Width = Convert.ToDouble(width);
        }

Why would the columns variable be Null?

Juha

My guess is that the you use a wrong value as first parameter to SetColumnWidth.
You use "Column No." the example uses xlColID

/Juha

Rob_Hansen

Rather than working with OpenXML or the limitations of microsoft's wrapper, we've used the ClosedXML library to extend the excel buffer functionality which has worked very well. Generally we've structured it to work after the fact. i.e. new properties to call for column widths and other formatting that buffer the changes (or save them in new fields), then we let NAV do its usual thing to create the book, then we reopen the file using ClosedXML to apply the format changes before it's downloaded to the client and opened.