Exporting to Excel formula problem

jwilder

I have done tons of exporting to excel in NAV 2009 R2 and previos version but am running into something annoying in NAV 2016. I am using the Excel buffer table and exporting a formula as I always have using the "Cell Value As Text" for example: =YEAR(A1) (Where cell A1 is a date). In NAV 2009 this goes into Excel as a working formula and actually displays the Year while keeping the formula behind the scenes.

In NAV 2016 it shows as the text of the formula and does not display the result of the formula. If I click on the cell, hit F2 and Enter it actually then registers it as a formula and works fine. Super annoying though.

If I use the Formula field in the Excel buffer it is not what I want because it calculates the formula as it exports but then just puts in the formula result as text so the actual formula is gone and will never recalculate again.

Anyone know of a work around for this (basically make it work like the ExcelBuffer of old)?

binilabraham

Hi,
In 2016, if you are using the 'AddColumn' function in Table 370 to insert a column then make sure the Parameters 'NumFormat' and 'CellType' as like following example;
ExcelBuf.AddColumn(SalesHeader."Order Date",FALSE,'',FALSE,FALSE,FALSE,'YYYY',ExcelBuf."Cell Type"::Date);
I think this can solve your issue.

Crunch

The above did not work for us, where we upgraded an old excel report.
The report did not use AddColumn, but just inserted into excelbuffer.

I added the following to have formulas autocalculate:

.....
TempExcelBuffer.ClearFormula;
IF IsFormula THEN BEGIN
  TempExcelBuffer."Cell Value as Text" := '';
  TempExcelBuffer.SetFormula(CellValue);
END
ELSE BEGIN
  TempExcelBuffer."Cell Value as Text" := CellValue;
  TempExcelBuffer.Formula := '';
END;
......
TempExcelBuffer.INSERT;

ricardopaiva

You may use the Formula property in the Excel dotnet dependencies.
See report 82 and search for EnterFormula.

Also check this post about formulas in different languages:
https://ricardomoinhos.com/excel-buffer-detect-excels-language/