· Updated · Dynamics NAV/365 BC · 2 min read
Excel Buffer - Detect Excel's Language
Dynamics NAV allows you to “easily” export information to an Excel file, via code, using the Excel Buffer table.
To export a cell value as a formula, you may add and use the following function to your object:
LOCAL EnterFormula(RowNo : Integer;ColumnNo : Integer;CellValue : Text[250];Bold : Boolean;UnderLine : Boolean;NumberFormat : Text[30])
ExcelBuf.INIT;
ExcelBuf.VALIDATE(“Row No.”,RowNo);
ExcelBuf.VALIDATE(“Column No.”,ColumnNo);
ExcelBuf.”Cell Value as Text” := ”;
ExcelBuf.Formula := CellValue; // is converted to formula later.
ExcelBuf.Bold := Bold;
ExcelBuf.Underline := UnderLine;
ExcelBuf.NumberFormat := NumberFormat;
ExcelBuf.INSERT;
For the sake of simplicity, if you want to sum the value of two cells you’ll write the following formula in Excel:
=SUM(A1:A2)
That will be fine if Excel’s language is English. What if Excel is in Portuguese? Or Dutch? Or if you have different users with different Excel languages?
Well, there’s a way!
First, create a new function in Excel Buffer table (T 370) to check the Excel language. The function can be created in your object but it is a good practice to create it in Excel Buffer (or a new object exclusively for this) so that you have the Excel automations/dotnet dependencies in a single object that you need to compile only once in a while.
So the function would look like this:
GetLanguageID() LanguageID : Integer
IF NOT CREATE(XlApp2,TRUE,TRUE) THEN
ERROR(Text000);
XlApp2.Visible(FALSE);
LanguageID := XlApp2.LanguageSettings.LanguageID(1);
XlApp2.Quit;
CLEAR(XlApp2);
EXIT(LanguageID);
XlApp2 is a local variable:
Name DataType Subtype Length
XlApp2 Automation/Dotnet ‘Microsoft Excel 15.0 Object Library’.Application
Then, in your object you can create a new function to retrieve the correct formula, like this:
GetSumFormula() : Text[30]
CASE ExcelLanguageID OF
2070:
EXIT(‘SOMA’);
1043:
EXIT(‘SOM’);
ELSE
EXIT(‘SUM’);
END;
And a new function to retrieve and store the ExcelLanguageID, to be used when needed:
GetExcelLanguageID() : Text[30]
IF ExcelLanguageID <> 0 THEN
EXIT;EXIT(ExcelBuf.GetLanguageID);
ExcelLanguageID is a global variable:
Name DataType
ExcelLanguageID Integer
So you’d use the following line of code to add the formula to the field in row 3, column 1:
EnterFormula(3,1,
’=’ + GetSumFormula + ‘(A1:B1)‘,
TRUE,FALSE,”);
Any questions you have, feel free to ask.
