Dynamics NAV 2016: Increase the height of combined wrapped fields in exported Excel report templates

This blogpost will be more technical one, and special thanks to our developer Kirill Cheremisin who did not give up solving this issue and actually gave birth to this solution.

When you will work with Russian localized version, you will very soon discover that many statutory required documents - invoices (way-bills) and VAT invoices - are created as reports that are exported to Excel in the standard functionality. In order not to re-create those reports in RDLC format, you can still use this Excel option, but there is one small issue that constantly bothers most of our clients.

The problem is that the Excel templates, used in the standard functionality, are created from a numerous combined cells. This is done in order to make the alignment of the fields more easy, as there are lot of different fields that need to be aligned:

When standard NAV exports data into the Excel template, the long item names will not fit into the needed cells of the document. The problem here is that Microsoft Excel itself has a drawback of not wrapping the text of the cell in a combined field.

You can perform small test yourself to follow this functional gap of Microsoft Office Excel. First, enter some long text into one cell of Excel:

Then, On the Home tab, in the Alignment group, click Wrap Text. Here would be the expected result - the line will get increase its height to let all two lines of text fit into one cell:

Now, combine two other cells in the Excel sheet and place the same text there:

And, try wrap it in the same way. Unfortunately, you would not get the expected result - there will still be one line only, the wrapping did not occur:

In order to show all the text in this cell, you would need manually expand the height of the cell. Otherwise the text is missing.

Now, lets turn to NAV system. As I told, many statutory required documents are created as export to Excel in Russian localization. Those documents contain a lot of combined cells, and if you have long item names in the lines of sales order, users will face a situation that item name will not fit into the line of the document and will not be readable. Clients will not accept those documents, or users will need to spend additional time to expand the height of the cells manually:

For some time we at Awara IT Solutions tried to overcome this by searching a way of automatic expanding the height of the cell while doing export of reports to match exactly the length of the text which is a variable value, and finally went to the following solution:

1. Prepare several lines of different height in the reporting template, from minimal to maximal expected:

For each new cell, create its name, but with a suffix that would be equal to the quantity of the lines in the cel; - in our example, "ItemName", "ItemName_4", "ItemName_5" (e.g., _4 would refer to four lines):

In the Names Dispatcher in Excel:

2. Then, re-write the data export of the report using templates with suffixes and calculate the number of lines by analyzing the cell width and the text length that should be imported to that cell, and show the line that would equal to the quantity of those lines:

CASE TRUE OF
  STRLEN(_LineValue[1]) <= 42: _CellSuffix := '';
  STRLEN(_LineValue[1]) <= 84: _CellSuffix := '_4';
  ELSE _CellSuffix := '_5';
END;
 
IF NOT ExcelReportBuilderMgr.TryAddSection('BODY' + _CellSuffix) THEN BEGIN
  ExcelReportBuilderMgr.AddPagebreak;
  FillPageHeader;
  ExcelReportBuilderMgr.AddSection('BODY' + _CellSuffix);
END;
 
ExcelReportBuilderMgr.AddDataToSection('ItemName' + _CellSuffix,_LineValue[1]);
….

But, this is not very exact method of calculation, as your item name might contain long words and Excel itself wraps by words, not by symbols. So, it is better to use improved version:

_LineCountForDescr := CalcLineCount(Текст, 21);
CASE TRUE OF
  _LineCountForDescr <= 2: _CellSuffix := '';
  _LineCountForDescr <= 4: _CellSuffix := '_4';
  ELSE _CellSuffix := '_5';
END; 

 
//Function CalcLineCount:
LOCAL CalcLineCount(VAR _Text : Text;_CharsAtLine : Integer) _LineCount : Integer
_Text := DELCHR(_Text, '<>', ' ');
_TextLen := STRLEN(_Text);
IF _TextLen > 0 THEN
BEGIN
  _CharNumberAtText := 0;
  _CharNumberAtLine := 0;
  _LineCount := 1;
  WHILE _CharNumberAtText < _TextLen DO
  BEGIN
    IF _CharNumberAtLine > _CharsAtLine THEN
    BEGIN
      IF _LastSpacePosition = _CurrentSpacePosition THEN // length of word more then possible length of line
      BEGIN
        _LineCount += 1;
        _CharNumberAtLine := 1;
      END
      ELSE
      BEGIN
        _LineCount += 1;
        _CharNumberAtText := _CurrentSpacePosition + 1;
        _LastSpacePosition := _CurrentSpacePosition;
        _CharNumberAtLine := 1;
      END;
    END ELSE
    BEGIN
      _CharNumberAtText += 1;
      _CharNumberAtLine += 1;
      IF COPYSTR(_Text, _CharNumberAtText, 1) = ' ' THEN
        _CurrentSpacePosition := _CharNumberAtText;
    END;
  END;
END
ELSE
  EXIT(0);
EXIT(_LineCount);

The result will be the following:

and

So instead of re-drawing the reports, you can modify the export routine using this code and make your customer happy.