Tag: Excel

Posted on

ClosedXML gives me a SmartTag error in NAV 2013 R2

In NAV 2009 R2 I did a solution that used ClosedXML to create Excel Documents.  ClosedXML is built to give an easy access to the office documents and is built on top of the Open XML SDK 2.0 for Microsoft Office.

I upgraded this solution to NAV 2013 without any problems.  Then to NAV 2013 R2 and there is where the problem started.

With NAV 2013 Microsoft changed the way NAV creates Excel documents.  They move away from the old COM model to this Open XML method.  You can see the changes in Table 370 Excel Buffer.

In NAV 2013 Microsoft used version 2.0 of the Open XML SDK.  In NAV 2013 R2 Microsoft is using version 2.5 and that is where my problems started.

When trying to save the Excel document with the SaveAs function I got a SmartTag error.

Could not load type ‘DocumentFormat.OpenXml.Spreadsheet.SmartTags’

I searched and found that the SmartTags are discontinued in version 2.5 but they are still used in ClosedXML.  As usual there is a solution and I found it on the Closed XML forum.

A WORKAROUND TO GET CLOSEDXML working for DocumentFormat.OpenXml SDK 2.5:

  1. Download ClosedXML source code
  2. Open ClosedXML source with Visual Studio
  3. Browse to the References of “ClosedXML” project and remove the reference DocumentFormat.OpenXml
  4. Use Nuget to install the latest Document.OpenXml 2.5 SDK as reference into the “ClosedXML” project
  5. Open the file XLWSContentManager.cs and remove the line 86
  6. Compile the project

For some reason it does not work for that way for “ClosedXML_Net3.5” project.

I opened my NAV 2013 R2 Developement machine with Visual Studio 2012, opened the solution and removed the ClosedXML_Net3.5 project.  Then replaced all references to Document.OpenXML with the pre-installed version 2.5 – yes the version 2.5 is installed with NAV Server.

The new ClosedXML 0.69.1.0 was built and it works with OpenXML 2.5 and I can now do the upgrade from NAV 2013 to NAV 2013 R2.

Here is the ClosedXML version 0.69.1.0 rebuilt for Document.OpenXML 2.5

Posted on

My Excel Helper to bring opened Excel Documents to front

In one of my projects I use Excel a lot for reporting.  I create the Excel documents with OpenXML and with the help of the Excel Buffer table.  With the click of a button the user creates a beautiful Excel Workbook but when I open the workbook it is displayed in the back of Dynamics NAV client window.

One of the users has been complaining about this and in the process of upgrading from NAV 2009 to NAV 2013 he asked me if this will be fixed.  I of course had to in order to increase the appreciation of the upgrade.

So I looked into this but did not find any .net object to use.  Looked like I had to import user32.dll to do this and that is not possible directly in NAV.  Hence a new class dll was born.

[code language=”csharp”] public static class WindowHelper
{
[System.Runtime.InteropServices.DllImport("user32.dll")]
static extern bool SetForegroundWindow(IntPtr hWnd);

public static void ActivateAppByProcessName(string processName)
{
Process[] p = Process.GetProcessesByName(processName);
if (p.Length > 0)
ActivateAppByHandle(p[0].MainWindowHandle);
}

public static void ActivateAppByPartialProcessName(string processName)
{
Process[] processes = Process.GetProcesses();
foreach (Process process in processes)
{
if (process.MainWindowTitle.Contains(processName))
{
ActivateAppByHandle(process.MainWindowHandle);
}
}
}

public static void ActivateAppByFileName(string fileName)
{
string[] fileNameParts = fileName.Split(new Char[] { ‘.’ });
string windowTitle = "";
for (int i = 0; i < fileNameParts.Length – 1; i++)
{
if (i == 0)
{
windowTitle += fileNameParts[i];
}
else
{
windowTitle += "." + fileNameParts[i];
}
}
ActivateAppByPartialProcessName(windowTitle);
}

static void ActivateAppByHandle(System.IntPtr WindowHandle)
{
SetForegroundWindow(WindowHandle);
}
}[/code]

In the same solution I had a problem converting the Excel Workbook to a PDF file. So I also added that support to this class.

[code language=”csharp”] public static class ExcelHelper
{
public static void ExportPDF(Microsoft.Office.Interop.Excel.Workbook xlWrkBk, string pdfFileName)
{
xlWrkBk.ExportAsFixedFormat(Microsoft.Office.Interop.Excel.XlFixedFormatType.xlTypePDF, pdfFileName);
}
}[/code]

Now I am able to open Excel in front of the NAV client and also able to convert the Excel Workbook to a PDF file.  In Table 370, Excel Buffer, I add a local variable for this class and add a single line in the code.

[code]The changes I make are below[code] PROCEDURE GiveUserControl@3();
VAR
WindowHelper@50000 : DotNet "’UserHelperClass, Version=1.0.0.0, Culture=neutral, PublicKeyToken=22affbe033b077df’.UserHelperClass.WindowHelper" RUNONCLIENT;
BEGIN
IF NOT ISNULL(XlApp) THEN BEGIN
XlApp.Visible := TRUE;
// Dynamics.is – Bring Excel to front –
WindowHelper.ActivateAppByFileName(XlWrkBk.Name);
// Dynamics.is – Bring Excel to front +
XlApp.UserControl := TRUE;
CLEAR(XlApp);
END;
END;
[/code]

And in Codeunit 424 in the bottom of the ExportData trigger

[code]
xlApp.Visible := TRUE;
//Dynamics.is – Bring Excel to Front –
WindowHelper.ActivateAppByFileName(FileMgt.GetFileName(FileName));
//Dynamics.is – Bring Excel to Front +[/code]

The class and then changed objects are attached below.

UserHelperClass  ExcelBufferUpdate

Posted on

Old format or invalid type library Excel error in Rapid Start

As a part of the new localized NAV 2013 we need to configure the Rapid Start package to be used with new installations.  As we started to work with the packages trying to export to Excel we got an error.

ExcelLocalizationError

The cause is known by Microsoft.

You receive this error calling an Excel method when the   following conditions are true:   

  • The method requires an LCID (locale identifier).
  • You run an English version of Excel. However, the regional settings for the computer are configured for a non-English language.

If the client computer runs the English version of Excel and the locale for the current user is configured for a language other   than English, Excel will try to locate the language pack for the configured   language. If the language pack is not found, the error is reported.

We saw the same error in NAV 2009 R2 Role Tailored Client.  The solution is simple, download and install a language pack for Microsoft Office that matches your regional settings.  You should find the language pack on the Office Language Interface Pack (LIP) downloads page.

Link for Office 2016

 

Posted on

Accessing your non english data from the MS SQL server

If you are like me, located in a non-English speaking country and would like users to be able to use other tools then the Dynamics NAV clients to access the company data you will find that all the meta data in the database is in English.  This means that you will have to translate the fields and sometimes the data to your language.

The problems are:

  • Field captions are unavailable in the MS SQL database
  • Option values are shown as number
  • Boolean is shown as number
  • Global dimension do not have the correct caption
  • Time is shown as DateTime
  • Not easy to see the difference between normal date and closing date

The solution that I am using is to create a separate database on the same database server and create localized views in that database.  What you get with the solution is:

  • A date table that can be used to show all properties of a given date
  • A import of the option value captions
  • A selection of tables to make accessible
  • A batch job to create the localized SQL view for each table

Here is a list of the fields in the date table

Field No. Field Name Data Type Length
1 Date Date
2 Date Name Text 30
3 Year Integer
4 Week Integer
5 Month Integer
6 Month Name Text 20
7 Day of Week Integer
8 Day Name Text 20
9 Day of Month Integer
10 Closing Date Boolean
11 SQL Month Name Text 20
12 SQL Day Name Text 20
13 Quarter Integer
14 Year Month Integer
15 Year Month Name Text 20
16 Month Year  Name Text 20
17 Quarter Name Text 20
18 VAT Period Integer
19 VAT Period Name Text 20
20 Sorting Date Integer
21 HRMS Integer Start Integer
22 HRMS Integer End Integer
23 Day of Year Integer
24 Day of Half Year Integer
25 Day of Quarter Integer
26 Day of Accounting Integer
27 Half Years Integer
28 Half Year of Year Integer
29 Is Holiday Boolean
30 Is Working Day Boolean
31 Month of Half Year Integer
32 Month of Quarter Integer
33 Month of Year Integer
34 Quarters of Half Year Integer
35 Quarters of Year Integer
36 Week of Year Integer
37 Is Week Day Boolean
41 Half Year Name Text 20
42 Week Name Text 20
102 Fiscal Day Date
103 Fiscal Year Integer
104 Fiscal Week Integer
105 Fiscal Month Integer
107 Fiscal Day of Week Integer
109 Fiscal Day of Month Integer
113 Fiscal Quarter Integer
123 Fiscal Day of Year Integer
124 Fiscal Day of Half Year Integer
125 Fiscal Day of Quarter Integer
127 Fiscal Half Years Integer
128 Fiscal Half Year of Year Integer
131 Fiscal Month of Half Year Integer
132 Fiscal Month of Quarter Integer
133 Fiscal Month of Year Integer
134 Fiscal Quarters of Half Year Integer
135 Fiscal Quarters of Year Integer
136 Fiscal Week of Half Year Integer
137 Fiscal Week of Month Integer
138 Fiscal Week of Quarter Integer
139 Fiscal Week of Year Integer
140 Fiscal Quarter Name Text 20
141 Fiscal Half Year Name Text 20
142 Fiscal Week Name Text 20
143 Fiscal Month Name Text 20

Lets take the G/L Entry table as an example. In the MS SQL the field names are in English and the data not readable for the normal user.

SELECT [Entry No_],[G_L Account No_],[Posting Date],[Document Type]
      ,[Document No_],[Description],[Bal_ Account No_],[Amount]
      ,[Global Dimension 1 Code],[Global Dimension 2 Code],[User ID] 
... 
      ,[FA Entry Type],[FA Entry No_] 
FROM [Dynamics NAV Demo Database (IS 2009 R2)].[dbo].[CRONUS Ísland hf_$G_L Entry]

and for example the [Document Type] will show as numbers.  Here is an example of the output of my tool to create a localized view for this table.

IF EXISTS(SELECT * FROM sys.views WHERE object_id = OBJECT_ID(N'dbo.ISL$CRONUS Ísland hf_$Fjárhagsfærsla'))
DROP VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
GO
CREATE VIEW [dbo].[ISL$CRONUS Ísland hf_$Fjárhagsfærsla]
AS
SELECT
[Entry No_] As [Færslunr_]
,[G_L Account No_] As [Fjárhagsreikn_nr_]
,[Posting Date] As [Bókunardags_]
,CASE [Document Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Greiðsla'
WHEN 2 THEN 'Reikningur'
WHEN 3 THEN 'Kreditreikningur'
WHEN 4 THEN 'Vaxtareikningur'
WHEN 5 THEN 'Innheimtubréf'
WHEN 6 THEN 'Endurgreiðsla'
END As [Tegund fylgiskjals]
,[Document No_] As [Númer fylgiskjals]
,[Description] As [Lýsing]
,[Bal_ Account No_] As [Mótreikningur nr_]
,[Amount] As [Upphæð]
,[Global Dimension 1 Code] As [Deild Kóti]
,[Global Dimension 2 Code] As [Verkefni Kóti]
,[User ID] As [Kenni notanda]
,CASE [System-Created Entry]
WHEN 1 THEN 'Já'
WHEN 0 THEN 'Nei'
END As [Kerfisfærsla]
...
,CASE [FA Entry Type]
WHEN 0 THEN ' '
WHEN 1 THEN 'Eignir'
WHEN 2 THEN 'Viðhald'
END As [Eignafærslutegund]
,[FA Entry No_] As [Eignafærslunr_]
FROM [CRONUS Ísland hf_$G_L Entry]
GO

Executing this will give me a view in my database that I can use to fetch localized data from the G/L Entry table with all the above problems solved.

By using the additional date table as dimension in OLAP or as join in a SQL query I can easily find all aspects of the “Posting Date” in the G/L Entry table and group entries accordingly.

Posted on

Creating an Excel Pivot table from a ledger table

I just finished creating a report that can export job ledger entries to Excel and build a Pivot Table for analysis of the data.  Global dimensions and data from the master and supplement tables are included in the export.  A similar report can be built for other ledger tables.

The first step is to create a workbook.

Fields for rows and columns are mandatory.  They can be selected from a field list.

The user can filter the job ledger entry table before exporting.  A text file will be created and opened by Excel.  I use a system object to create the text file in the system code page.  This will result in a simple pivot table in Excel.

The Excel workbook will include three sheets.  One for the pivot table, one for filter information and one for the data.  The user can now customize the pivot table to display what ever is needed and then save the workbook to a secure location.  When saving a dialog similar to the one below will appear.  Here the uses selects No and saves the workbook in an Excel format.

Then the user can update this workbook with new data.  When updating a workbook the previous data is completely replaced.  Run the same report and change the option to Update Workbook.  Select the Excel Document and the sheet names will be automatically applied with standard names.

This will give the user a fresh data and a updates pivot table that can be saved to a different file name if needed.  Then code is somewhat based on the solution from Microsoft that exports Account Schedules to pivot table and I use four basic functions as shown below.
[code htmlscript=”false”]CreateExcel()
CREATE(xlApp,TRUE,TRUE);

CreateWorkbook()
WITH "Job Ledger Entry" DO BEGIN
xlApp.Workbooks.OpenText(TextFileName);
xlWorkSheet := xlApp.ActiveSheet;
xlSheetName := GetDataSheetName;
xlWorkSheet.Name := xlSheetName;

xlApp.ActiveWorkbook.Sheets.Add();
xlWorkSheet3 := xlApp.ActiveSheet;
xlWorkSheet3.Name := GetInfoSheetName;
xlWorkSheet3.Range(‘A1’).Value := FORMAT(Text009);
xlWorkSheet3.Range(‘B1’).Value := TABLECAPTION;

IF GETFILTER("Work Type Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

IF NoOfRows &gt; 0 THEN BEGIN
ExcelBuffer.VALIDATE("Column No.",NoOfColumns);
PivotTableRange := STRSUBSTNO(‘%1!$A:$%2′,xlSheetName, ExcelBuffer.xlColID);
xlWorkSheet.Range(PivotTableRange).Columns.AutoFit;
xlPivotCache := xlApp.ActiveWorkbook.PivotCaches.Add(1,PivotTableRange);
xlPivotCache.CreatePivotTable(”,’PivotTable1’);

xlWorkSheet2 := xlApp.ActiveSheet();
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1′);
xlWorkSheet2.Name := GetPivotSheetName;

xlPivotField := xlPivotTable.PivotFields(RowFieldName);
xlPivotField.Orientation := 1; //xlRowField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(ColFieldName);
xlPivotField.Orientation := 2; //xlColumnField
xlPivotField.Position := 1;

xlPivotField := xlPivotTable.PivotFields(FIELDCAPTION("Total Price (LCY)"));
xlPivotField.Orientation := 4; //xlDataField
xlPivotField.Position := 1;
xlPivotField."Function" := 0; //sum
xlPivotField.Caption := STRSUBSTNO(Text026,FIELDCAPTION("Total Price (LCY)"));

FormatString := DELCHR(FORMAT(ROUND(1000.01,GLSetup."Unit-Amount Rounding Precision"),0),’ ” THEN BEGIN
xlWorkSheet3.Range(‘A2’).Value := FIELDCAPTION("Work Type Code") + Text010;
xlWorkSheet3.Range(‘B2’).Value := GETFILTER("Work Type Code");
END;
IF GETFILTER("Entry Type") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A3’).Value := FIELDCAPTION("Entry Type") + Text010;
xlWorkSheet3.Range(‘B3’).Value := GETFILTER("Entry Type");
END;
IF GETFILTER("Posting Date") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A4’).Value := FIELDCAPTION("Posting Date") + Text010;
xlWorkSheet3.Range(‘B4’).Value := GETFILTER("Posting Date");
END;
IF GETFILTER("Global Dimension 1 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A5’).Value := FIELDCAPTION("Global Dimension 1 Code") + Text010;
xlWorkSheet3.Range(‘B5’).Value := GETFILTER("Global Dimension 1 Code");
END;
IF GETFILTER("Global Dimension 2 Code") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A6’).Value := FIELDCAPTION("Global Dimension 2 Code") + Text010;
xlWorkSheet3.Range(‘B6’).Value := GETFILTER("Global Dimension 2 Code");
END;
IF GETFILTER("Job No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A7’).Value := FIELDCAPTION("Job No.") + Text010;
xlWorkSheet3.Range(‘B7’).Value := GETFILTER("Job No.");
END;
IF GETFILTER("Job Task No.") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A8’).Value := FIELDCAPTION("Job Task No.") + Text010;
xlWorkSheet3.Range(‘B8’).Value := GETFILTER("Job Task No.");
END;
IF GETFILTER("Job Posting Group") <> ” THEN BEGIN
xlWorkSheet3.Range(‘A9’).Value := FIELDCAPTION("Job Posting Group") + Text010;
xlWorkSheet3.Range(‘B9’).Value := GETFILTER("Job Posting Group");
END;
xlWorkSheet3.Range(‘A1:B9’).Columns.AutoFit;

END;

xlRange := xlWorkSheet.Range(‘A1’).SpecialCells(11);
ExcelBuffer.VALIDATE("Column No.",xlRange.Column);
xlWorkSheet.Range(STRSUBSTNO(‘A1:%1%2’,ExcelBuffer.xlColID,xlRange.Row)).ClearContents;
xlRange := xlWorkSheet.Range(‘$A$1’);
xlQueryTables := xlWorkSheet.QueryTables;
xlQueryTable := xlQueryTables.Add(STRSUBSTNO(‘TEXT;%1’,TextFileName),xlRange);
xlQueryTable.Name := Text025;
xlQueryTable.FieldNames := TRUE;
xlQueryTable.RowNumbers := FALSE;
xlQueryTable.FillAdjacentFormulas := FALSE;
xlQueryTable.RefreshOnFileOpen := FALSE;
xlQueryTable.RefreshStyle := 1; //xlInsertDeleteCells
xlQueryTable.SavePassword := FALSE;
xlQueryTable.SaveData := FALSE;
xlQueryTable.AdjustColumnWidth := TRUE;
xlQueryTable.RefreshPeriod := 0;
xlQueryTable.TextFilePromptOnRefresh := FALSE;
xlQueryTable.TextFilePlatform := 1252;
xlQueryTable.TextFileStartRow := 1;
xlQueryTable.TextFileParseType := 1; //xlDelimited
xlQueryTable.TextFileTextQualifier := 1; //xlTextQualifierDoubleQuote
xlQueryTable.TextFileConsecutiveDelimiter := FALSE;
xlQueryTable.TextFileTabDelimiter := TRUE;
xlQueryTable.TextFileSemicolonDelimiter := FALSE;
xlQueryTable.TextFileCommaDelimiter := FALSE;
xlQueryTable.TextFileSpaceDelimiter := FALSE;
xlQueryTable.TextFileTrailingMinusNumbers := TRUE;
xlQueryTable.Refresh(FALSE);

xlWorkSheet2.Select;
xlPivotTable := xlWorkSheet2.PivotTables(‘PivotTable1’);
xlPivotTable.RefreshTable;

ShowExcel()
xlApp.Visible := TRUE;[/code]
And after creating the text file these functions are executed.
[code htmlscript=”false”]CreateExcel;
IF ExcelOption = ExcelOption::"Create Workbook" THEN
CreateWorkbook
ELSE
UpdateWorkbook;
ShowExcel;[/code]
If you, your company or your client would like to have this solution for other ledger tables just contact me.

The zipped attachment below is encrypted, please look at the product page for more information.

Job Pivot Report

 

Posted on

Geometric mean

In one of my recent projects I needed to calculate a geometric mean for a group of numbers.  The Geometric mean formula is generally, if the numbers are x_1,\ldots,x_n, the geometric mean G satisfies G = \sqrt[n]{x_1 x_2 \cdots x_n},

The problem appeared when I needed to multiply numbers and got a overflow on the decimal data type in Dynamics NAV.

I produced two solutions to this problem; first for Classic Client using Excel and secondly for Role Tailored Client using Add-in.  Using Excel for this job is slow but the Add-in works great.

In Excel I add values to a column and then use the built in function GEOMEAN(‘<Range>’) to calculate the value.  This works for large values.

The GeoMean Class Add-in I created in Visual Studio in VB.NET
[code htmlscript=”false” lang=”vb”]Public Class GeoMeanClass
Dim TotalValue As Double
Dim NoOfValues As Integer
Public Sub ResetValue()
TotalValue = 1
NoOfValues = 0
End Sub
Public Sub AddValue(ByVal Value As Decimal)
If Value = 0 Then Exit Sub
TotalValue = TotalValue * Value
NoOfValues = NoOfValues + 1
End Sub
Public ReadOnly Property NoOfStoredValues() As Integer
Get
Return NoOfValues
End Get
End Property
Public ReadOnly Property GetGeoMean() As Decimal
Get
If NoOfValues = 0 Then
Return 0
Else
Return CDec(TotalValue ^ (1 / NoOfValues))
End If
End Get
End Property
End Class[/code]
Attached are the Add-in and the codeunits needed to calculate Geometric mean.

GeoMean

 

Posted on

Using OpenXML to create an Excel Document

In one of my projects I needed to create a complex Excel document from Dynamics NAV.  I did this by using COM automation in the same way that Microsoft is doing in the Excel Buffer table no. 370.  The problem is that this is dead slow.  It could take up to two minutes to create a single Excel document on the Role Tailored Client.

I saw in Kauffmann’s blog that it is possible to use OpenXML on the server side to create the Excel document.  Great blog and a big help.  The same Excel document is now created in under two seconds.

As I started the batch to create the OpenXML Excel documents I received an error: “hexadecimal value 0x1F, is an invalid character”.  I was inserting an invalid character into the XML.  I did a quick search and found a solution, I needed to filter the data through a white list.  Here is my AddCell function:

LOCAL PROCEDURE AddCell@16(CurrentRow@1200050001 : Integer;CurrentCol@1200050000 : Integer;Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : BigText;IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30];Attention@1200050002 : Boolean;Alignment@1200050006 : 'General,Left,Right,Center');
VAR
XlColor@1200050008 : DotNet "'ClosedXML, Version=0.64.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLColor";
CellValueAsText@1200050007 : Text[1024];
CommentLine@1200050003 : Text[1024];
CommentPosition@1200050004 : Integer;
CommentLength@1200050005 : Integer;
BEGIN
IF CurrentRow = 0 THEN
GlobalRowNo := GlobalRowNo + 1
ELSE
GlobalRowNo := CurrentRow;
IF CurrentCol = 0 THEN
GlobalColumnNo := GlobalColumnNo + 1
ELSE
GlobalColumnNo := CurrentCol;

//Comments are not yet supported by ClosedXML
//CommentLength := CommentText.LENGTH;
//IF CommentLength > 0 THEN BEGIN
// CommentPosition := 1;
// WHILE CommentPosition < CommentLength DO BEGIN
// CommentPosition := CommentPosition + CommentText.GETSUBTEXT(CommentLine,CommentPosition,MAXSTRLEN(CommentLine));
// XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Comment.AddText := CommentLine;
// END;
//END;

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Bold := IsBold;
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.Italic := IsItalics;
IF IsUnderline THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.SetBottomBorder :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Border.BottomBorder,'Continuous');

XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.NumberFormat.SetFormat := NumFormat;

IF Attention THEN BEGIN
XlColor := XlColor.FromName('Red');
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Font.SetFontColor := XlColor;
END;

IF IsFormula THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).FormulaA1 := Value
ELSE
CASE TRUE OF
Value.ISTEXT,Value.ISCHAR,Value.ISCODE :
BEGIN
CellValueAsText := FORMAT(Value);
CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := CellValueAsText;
END;
ELSE IF FORMAT(Value) <> '0' THEN
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Value := Value;
END;

CASE Alignment OF
Alignment::General:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'General');
Alignment::Center:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Center');
Alignment::Right:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Right');
Alignment::Left:
XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.SetHorizontal :=
GetEnumValue(XlWrkShtDotNet.Cell(GlobalRowNo,GlobalColumnNo).Style.Alignment.Horizontal,'Left');
END;
END;

 

The line

CellValueAsText := XMLWhiteCharCheck.XmlCharacterWhitelist(CellValueAsText);

 

is used to clean the string that is passed to the XML.

The GetEnumValue function is

LOCAL PROCEDURE GetEnumValue@150002034(Enum@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Enum";Value@150002026 : Text[30]) ReturnValue : Integer;
VAR
Convert@150002025 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.Convert";
BEGIN
ReturnValue := Convert.ToInt32(Enum.Parse(Enum.GetType(),Value));
END;

 

When constructing the OpenXML dotnet object I also construct the white characther check object.

XMLWhiteCharCheck := XMLWhiteCharCheck.XMLCharWhiteList;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();

 

Attached is the Add-in needed on the server side.

XMLCharWhiteList Add-in

 

 

Posted on

Renumbering tool for Dynamics NAV Objects

I needed to renumber objects for Skyrr Payroll addon and found on Mibuso an old renumbering tool.  I downloaded it and installed.  I corrected the bug and created an import function for Excel worksheet.

The Excel worksheet has the object type in column A, object ID in column B, object name in column C, object caption in column D and the object new ID in column E.  The first row is the header row.  I simply copied my object list from Object Designer and worked out my new ids in Excel.

After importing this Excel worksheet I have a list of new and old IDs in NAV.

If any of the new IDs is an old ID, you will need to renumber first from the old ID to temporary ID and then do another renumbering to the new ID.

I create two sheets in my Excel workbook to solve this.

This renumbering tool does not support Pages and I did not need that support in this case.

Renumbering Tool Source Code

Posted on

File Handling and RTC

In the Role Tailored Client all file system commands used by the Classic Client have been removed.  For those who have been using commands such as EXISTS, RENAME and ERASE there is a code update to be done.  I created a codeunit that includes these functions and can be used to replace the commands in the old code.  These functions work both in the Role Tailored Client and in Classic Client.

The attached code includes the following functions;

  • SelectExcelFileName
  • ConfirmFileExists
  • FileExists
  • FileRename
  • FileErase
  • FileCopy

Code is attached here.