Monday, December 9, 2013
Saturday, October 5, 2013
How long does it take to implement Dynamics NAV for your bussiness
Watch this short whiteboard session to learn about why business solutions from Microsoft are quick to implement and easy to use. This video applies to Microsoft's ERP (Enterprise Resource Planning) products for small and midsized businesses
How can a business solution from Microsoft help streamline your Business
Watch this short whiteboard session to learn about how business solutions from Microsoft NAV can help you streamline processes. This video applies to Microsoft's ERP (Enterprise Resource Planning) products for small and midsized businesses:
Wednesday, October 2, 2013
More new features with Zetadocs Express in upcoming NAV2013R2 release
Microsoft and EquisysPLC are very serious in getting collaboration in the Cloud!
I can't wait to play with it
I can't wait to play with it
Saturday, September 28, 2013
What's new with the Dynamics NAV 2013R2 Web client
The web client was a big success with the initial release of Dynamics NAV 2013. With Dynamics NAV 2013 R2 Microsoft has continued the development of the web client to include most, if not all, of the functionality available with the standard Windows client or RTC.
In order for users to have the same experience across all clients the Client Extensibility features from NAV 2009 have been dramatically improved upon. Previously we could only use client extensibility code on the Windows client and it had to be installed on every machine. Data display was limited to .NET / Windows Forms, or Windows Presentation Foundation forms with certain work arounds. Now with Dynamics NAV 2013 R2 we have the ability to use Javascript and HTML5 and we don't have to install it anywhere except the server. We can even use our add-ins on List pages. This continues the trend of introducing NAV to those outside of the NAV world and opens up a world of possibilities. This is the area in which I think the next big innovations will come and I truly hope to see more companies begin to take advantage of it.
These changes drive home what I love about Microsoft's current direction: interface standardization. I come from an educational technology / interface design background and the number one complaint I always hear about software is "It's too hard to figure out!!" The first step to making software easier is to allow users to instinctively learn from other software, to easily move from application to application. Microsoft is nudging developers in the right direction which will do nothing but make better software and happier customers in the long run.
Beware the Third Parameter of MODIFYALL
Beware the Third Parameter of MODIFYALL
The C/AL keyword MODIFYALL is tempting to use when a certain value must be changed for every record matching the current filtered recordset. One may or may not be aware of the third optional parameter for this keyword: TRUE/FALSE. FALSE is the default. If set to TRUE, the OnModify() trigger is executed when the record is modified.
Consider the following snippet of code:
SalesLine.SETRANGE("Document Type",SalesHeader."Document Type");
SalesLine.SETRANGE("Document No.",SalesHeader."No.");
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETFILTER(Quantity,'<>0');
SalesLine.MODIFYALL("Qty. to Ship",0,TRUE);
Using the above snippet of code, the "Qty. to Ship" value will be changed for every sales line matching the filtered recordset. The third parameter, TRUE, indicates the system should execute the OnModify() trigger for every record when changing the value. What the command does NOT do is execute the OnValidate() trigger of the Qty. to Ship field.
The OnValidate() trigger of the Qty. to Ship field of the Sales Line table contains, at minimum, critical code affecting the "Qty. to Ship (Base)" field, without which data errors will be introduced into the database. If code is introduced after the above snippet to release the document and post, Codeunit 80 skips past this particular issue and posts the document. Strange data errors then result when any further action is taken on this document.
The code below is a far safer block of code to execute:
SalesLine.SETRANGE("Document Type",SalesHeader."Document Type");
SalesLine.SETRANGE("Document No.",SalesHeader."No.");
SalesLine.SETRANGE(Type,SalesLine.Type::Item);
SalesLine.SETFILTER(Quantity,'<>0');
IF SalesLine.FINDSET(TRUE) THEN
REPEAT
SalesLine.VALIDATE("Qty. to Ship",0);
// safe! code executed to set Qty. to Ship (Base) also
SalesLine.MODIFY(TRUE);
// execute the OnModify() trigger
UNTIL SalesLine.NEXT = 0;
The key is knowing whether there is any code in the OnValidate() trigger of the field. If there is, it's better to be safe than sorry.
Thursday, September 12, 2013
Dynamics NAV 2013 R2 release at EMEA
There are a lot new features coming to us in october. Can't wait to show it to our customers
Info can be found ar the Microsoft launch page
Friday, August 16, 2013
Barcodes “magic” revealed - Microsoft Dynamics Community
Today I ran into a nice article on Barcodes.
Barcodes “magic” revealed – part 1 - Microsoft Dynamics NAV Guest Columnists - Microsoft Dynamics NAV - Microsoft Dynamics Community
Barcodes “magic” revealed – part 2 - Microsoft Dynamics NAV Guest Columnists - Microsoft Dynamics NAV - Microsoft Dynamics Community
enjoy
Barcodes “magic” revealed – part 1 - Microsoft Dynamics NAV Guest Columnists - Microsoft Dynamics NAV - Microsoft Dynamics Community
Barcodes “magic” revealed – part 2 - Microsoft Dynamics NAV Guest Columnists - Microsoft Dynamics NAV - Microsoft Dynamics Community
enjoy
Monday, August 12, 2013
Export to Excel on RTC using .Net Interop
Arjan Kauffman wrote a nice article on how to create a extended integration with Excel from within Dynamics NAV 2013. You can find his blog here! :
http://www.kauffmann.nl/blog/index.php/2011/09/24/export-to-excel-on-rtc-using-net-interop/
Introduction
http://www.kauffmann.nl/blog/index.php/2011/09/24/export-to-excel-on-rtc-using-net-interop/
Introduction
Exporting to Excel via the Role Tailored Client of Microsoft Dynamics NAV can be very slow when using the standard export functions from Table 370 (Excel Buffer). The reason for this performance issue is explained in this blog post on the Microsoft Dynamics NAV Team Blog.
The recommended solution from Microsoft is to let the Service Tier do the hard work for you, instead of using the Excel Automation objects. Unfortunately, the Service Tier typically does not have Excel installed, so the only way to create an Excel file is to create flat text, comma separated file (.csv). The disadvantage of this purpose is of course the lack of styling possibilities like text styles, borders, etc.
When I read the post from Microsoft, I started to think about another approach. Why not using the possibilities of Office Open XML? Office documents can be created using the Office Open XML format, wich is an open standard.
By the way, did you know that files created by Office 2007 and above, are in fact .zip files? Just rename the .xlsx, .docx or .pptx extension to .zip and open the file with your favorite zip program. You will find several xml documents in a folder structure that together form the complete Office document.
So basically, it must be possible to create an Excel file by just creating a few xml files and putting them together. However, I can tell you for sure that it is far more complex. Luckily, Microsoft provides the Open XML SDK for Microsoft Office. This .Net based SDK can be used to programmatically create Office Open XML files using an API. And since NAV 2009 supports .Net Interoperability, it must be possible to use this SDK to create an Excel file without the Excel application.
So, the approach of Microsoft to let the Service Tier do the hard work can be extended by using the Open XML SDK. That will combine the best of both worlds. No performance drop with using automation objects on the RTC, while still being able to create rich Excel files instead of flat .csv files.
To make a long story short, the Open XML SDK is still fairly complex to use. You need to know about al parts that an Office document is built of and how they work together, like WorkbookPart, WorksheetPart, StylePart, etc. I found myself creating a complete Excel application, just without user interface. Too much work and too complex to maintain.
Then I came across the project ClosedXML – The easy way to OpenXML on CodePlex. And that was exactly what I was looking for! An easy to use .Net assembly that implements nearly all the Excel features. Request #1 on the project site reads: ‘If you like this project please make an entry about it in your blog’. Well, hereby I do. Great work!
How can we use this with NAV 2009?
The first step is to prepare the development environment and the Service Tier. Otherwise you won’t be able to compile or run the software…
- Unzip the file and copy ClosedXML.dll to the Add-ins folder of your Classic Client and the Service Tier.
- Download DocumentFormat.OpenXml.dll. (Part of the Open XML SDK 2.0 for Microsoft Office, you can download and install the complete SDK as well)
- Locate the downloaded (or installed) DocumentFormat.OpenXml.dll and copy it to the Add-ins folder of the Service Tier. (It is not needed to copy it to the Add-ins folder of the Classic Client)
- Download text export of Table 370 Excel Buffer
- Merge the changes of Table 370 into your database. Changes are marked with // .Net
Here is are some previews of Table 370 with .Net Interop. I decided to use ISSERVICETIER and create separate DotNet functions to let the standard behavior intact for the Classic Client.
Global Variables
XlWrkBkDotNet@150002024 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.XLWorkbook";
XlWrkShtDotNet@150002025 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlWrkshtsDotNet@150002026 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlRangeDotNet@150002027 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLRange";
XLStyleDotNet@150002029 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLStyle";
XlWrkShtDotNet@150002025 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlWrkshtsDotNet@150002026 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLWorksheet";
XlRangeDotNet@150002027 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLRange";
XLStyleDotNet@150002029 : DotNet "'ClosedXML, Version=0.59.0.0, Culture=neutral, PublicKeyToken=fd1eb21b62ae805b'.ClosedXML.Excel.IXLStyle";
CreateBook
PROCEDURE CreateBook@1();
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();
XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Sheet1');
END ELSE BEGIN
// .Net <<
IF NOT CREATE(XlApp,TRUE,TRUE) THEN
ERROR(Text000);
XlApp.Visible(FALSE);
XlWrkBk := XlApp.Workbooks.Add;
XlWrkSht := XlWrkBk.Worksheets.Add;
END;
END;
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook();
XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Sheet1');
END ELSE BEGIN
// .Net <<
IF NOT CREATE(XlApp,TRUE,TRUE) THEN
ERROR(Text000);
XlApp.Visible(FALSE);
XlWrkBk := XlApp.Workbooks.Add;
XlWrkSht := XlWrkBk.Worksheets.Add;
END;
END;
OpenBook
PROCEDURE OpenBook@2(FileName@1000 : TEXT[250];SheetName@1001 : TEXT[250]);
VAR
i@1002 : INTEGER;
EndOfLoop@1003 : INTEGER;
Found@1004 : Boolean;
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
OpenBookDotNet(FileName,SheetName);
EXIT;
END;
// .Net <<
...
END;
PROCEDURE OpenBookDotNet@150002024(FileName@1000 : TEXT[250];SheetName@1001 : TEXT[250]);
VAR
i@1002 : INTEGER;
EndOfLoop@1003 : INTEGER;
Found@1004 : Boolean;
FileRTC@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File" RUNONCLIENT;
PathRTC@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path" RUNONCLIENT;
ThreeTierMgt@150002025 : Codeunit 419;
NVInStream@150002027 : InStream;
MemoryStream@150002028 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ClientTempFileName@150002026 : TEXT[1024];
BEGIN
// .Net >>
IF FileName = '' THEN
ERROR(Text001);
IF SheetName = '' THEN
ERROR(Text002);
//USE System.IO.File TO check file ON RTC
IF NOT FileRTC.Exists(FileName) THEN
ERROR(Text003,FileName);
//Copy file TO RTC Temp folder AND upload it TO SERVER in a STREAM
ClientTempFileName := ThreeTierMgt.ClientTempFileName('',PathRTC.GetExtension(FileName));
FileRTC.Copy(FileName, ClientTempFileName);
UPLOADINTOSTREAM('',ThreeTierMgt.Magicpath,'',ClientTempFileName,NVInStream);
MemoryStream := NVInStream;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);
i := 1;
EndOfLoop := XlWrkBkDotNet.Worksheets.Count;
WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
XlWrkshtsDotNet := XlWrkBkDotNet.Worksheet(i);
IF SheetName = XlWrkshtsDotNet.Name THEN
Found := TRUE;
i := i + 1;
END;
IF Found THEN
XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(SheetName)
ELSE BEGIN
CLEAR(XlWrkBkDotNet);
ERROR(Text004,SheetName);
END;
// .Net <<
END;
VAR
i@1002 : INTEGER;
EndOfLoop@1003 : INTEGER;
Found@1004 : Boolean;
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
OpenBookDotNet(FileName,SheetName);
EXIT;
END;
// .Net <<
...
END;
PROCEDURE OpenBookDotNet@150002024(FileName@1000 : TEXT[250];SheetName@1001 : TEXT[250]);
VAR
i@1002 : INTEGER;
EndOfLoop@1003 : INTEGER;
Found@1004 : Boolean;
FileRTC@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.File" RUNONCLIENT;
PathRTC@150002029 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.Path" RUNONCLIENT;
ThreeTierMgt@150002025 : Codeunit 419;
NVInStream@150002027 : InStream;
MemoryStream@150002028 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ClientTempFileName@150002026 : TEXT[1024];
BEGIN
// .Net >>
IF FileName = '' THEN
ERROR(Text001);
IF SheetName = '' THEN
ERROR(Text002);
//USE System.IO.File TO check file ON RTC
IF NOT FileRTC.Exists(FileName) THEN
ERROR(Text003,FileName);
//Copy file TO RTC Temp folder AND upload it TO SERVER in a STREAM
ClientTempFileName := ThreeTierMgt.ClientTempFileName('',PathRTC.GetExtension(FileName));
FileRTC.Copy(FileName, ClientTempFileName);
UPLOADINTOSTREAM('',ThreeTierMgt.Magicpath,'',ClientTempFileName,NVInStream);
MemoryStream := NVInStream;
XlWrkBkDotNet := XlWrkBkDotNet.XLWorkbook(MemoryStream);
i := 1;
EndOfLoop := XlWrkBkDotNet.Worksheets.Count;
WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN
XlWrkshtsDotNet := XlWrkBkDotNet.Worksheet(i);
IF SheetName = XlWrkshtsDotNet.Name THEN
Found := TRUE;
i := i + 1;
END;
IF Found THEN
XlWrkShtDotNet := XlWrkBkDotNet.Worksheet(SheetName)
ELSE BEGIN
CLEAR(XlWrkBkDotNet);
ERROR(Text004,SheetName);
END;
// .Net <<
END;
CreateSheet
PROCEDURE CreateSheet@5(SheetName@1000 : TEXT[250];ReportHeader@1001 : TEXT[80];CompanyName@1002 : TEXT[30];UserID2@1003 : TEXT[30]);
VAR
XlEdgeBottom@1004 : INTEGER;
XlContinuous@1005 : INTEGER;
XlLineStyleNone@1006 : INTEGER;
XlLandscape@1007 : INTEGER;
CRLF@1008 : CHAR;
WINDOW@1009 : Dialog;
RecNo@1010 : INTEGER;
InfoRecNo@1012 : INTEGER;
TotalRecNo@1011 : INTEGER;
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
CreateSheetDotNet(SheetName,ReportHeader,CompanyName,UserID2);
EXIT;
END;
// .Net <<
...
END;
PROCEDURE CreateSheetDotNet@150002030(SheetName@1000 : TEXT[250];ReportHeader@1001 : TEXT[80];CompanyName@1002 : TEXT[30];UserID2@1003 : TEXT[30]);
VAR
CRLF@1008 : CHAR;
WINDOW@1009 : Dialog;
RecNo@1010 : INTEGER;
InfoRecNo@1012 : INTEGER;
TotalRecNo@1011 : INTEGER;
BEGIN
// .Net >>
WINDOW.OPEN(
Text005 +
'@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
WINDOW.UPDATE(1,0);
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
RecNo := 0;
XlWrkShtDotNet.Name := SheetName;
IF ReportHeader <> '' THEN
XlWrkShtDotNet.PageSetup.Header.Left.AddText(
STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName));
XlWrkShtDotNet.PageSetup.Header.Right.AddText(
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2));
XlWrkShtDotNet.PageSetup.PageOrientation := GetEnumValue(XlWrkShtDotNet.PageSetup.PageOrientation, 'Landscape');
IF FIND('-') THEN BEGIN
REPEAT
RecNo := RecNo + 1;
WINDOW.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF NumberFormat <> '' THEN
XlWrkShtDotNet.Cell("Row No.","Column No.").Style.NumberFormat.SetFormat(NumberFormat);
IF Formula = '' THEN
XlWrkShtDotNet.Cell("Row No.","Column No.").Value := "Cell Value as Text"
ELSE
XlWrkShtDotNet.Cell("Row No.","Column No.").FormulaA1 := GetFormula;
//Comments are NOT yet supported BY ClosedXML
//IF Comment <> '' THEN
// XlWrkShtDotNet.Cell("Row No.","Column No.").Comment := Comment;
XLStyleDotNet := XlWrkShtDotNet.Cell("Row No.","Column No.").Style;
IF Bold THEN
XLStyleDotNet.Font.SetBold(Bold);
IF Italic THEN
XLStyleDotNet.Font.SetItalic(Italic);
XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
IF UNDERLINE THEN
XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
UNTIL NEXT = 0;
XlWrkShtDotNet.Columns(1, "Column No.").AdjustToContents();
END;
IF UseInfoSheed THEN BEGIN
IF InfoExcelBuf.FIND('-') THEN BEGIN
XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Information');
XlWrkShtDotNet.Name := Text023;
REPEAT
InfoRecNo := InfoRecNo + 1;
WINDOW.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));
IF InfoExcelBuf.NumberFormat <> '' THEN
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style.NumberFormat.SetFormat
(InfoExcelBuf.NumberFormat);
IF InfoExcelBuf.Formula = '' THEN
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Value := InfoExcelBuf."Cell Value as Text"
ELSE
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").FormulaA1 := InfoExcelBuf.GetFormula;
//Comments are NOT yet supported BY ClosedXML
//IF InfoExcelBuf.Comment <> '' THEN
// XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Comment := InfoExcelBuf.Comment;
XLStyleDotNet := XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style;
IF InfoExcelBuf.Bold THEN
XLStyleDotNet.Font.SetBold(InfoExcelBuf.Bold);
IF InfoExcelBuf.Italic THEN
XLStyleDotNet.Font.SetItalic(InfoExcelBuf.Italic);
XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
IF InfoExcelBuf.Underline THEN
XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
UNTIL InfoExcelBuf.NEXT = 0;
XlWrkShtDotNet.Columns(1, InfoExcelBuf."Column No.").AdjustToContents();
END;
END;
WINDOW.CLOSE;
// .Net <<
END;
VAR
XlEdgeBottom@1004 : INTEGER;
XlContinuous@1005 : INTEGER;
XlLineStyleNone@1006 : INTEGER;
XlLandscape@1007 : INTEGER;
CRLF@1008 : CHAR;
WINDOW@1009 : Dialog;
RecNo@1010 : INTEGER;
InfoRecNo@1012 : INTEGER;
TotalRecNo@1011 : INTEGER;
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
CreateSheetDotNet(SheetName,ReportHeader,CompanyName,UserID2);
EXIT;
END;
// .Net <<
...
END;
PROCEDURE CreateSheetDotNet@150002030(SheetName@1000 : TEXT[250];ReportHeader@1001 : TEXT[80];CompanyName@1002 : TEXT[30];UserID2@1003 : TEXT[30]);
VAR
CRLF@1008 : CHAR;
WINDOW@1009 : Dialog;
RecNo@1010 : INTEGER;
InfoRecNo@1012 : INTEGER;
TotalRecNo@1011 : INTEGER;
BEGIN
// .Net >>
WINDOW.OPEN(
Text005 +
'@1@@@@@@@@@@@@@@@@@@@@@@@@@\');
WINDOW.UPDATE(1,0);
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
RecNo := 0;
XlWrkShtDotNet.Name := SheetName;
IF ReportHeader <> '' THEN
XlWrkShtDotNet.PageSetup.Header.Left.AddText(
STRSUBSTNO('%1%2%1%3%4',GetExcelReference(1),ReportHeader,CRLF,CompanyName));
XlWrkShtDotNet.PageSetup.Header.Right.AddText(
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2));
XlWrkShtDotNet.PageSetup.PageOrientation := GetEnumValue(XlWrkShtDotNet.PageSetup.PageOrientation, 'Landscape');
IF FIND('-') THEN BEGIN
REPEAT
RecNo := RecNo + 1;
WINDOW.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF NumberFormat <> '' THEN
XlWrkShtDotNet.Cell("Row No.","Column No.").Style.NumberFormat.SetFormat(NumberFormat);
IF Formula = '' THEN
XlWrkShtDotNet.Cell("Row No.","Column No.").Value := "Cell Value as Text"
ELSE
XlWrkShtDotNet.Cell("Row No.","Column No.").FormulaA1 := GetFormula;
//Comments are NOT yet supported BY ClosedXML
//IF Comment <> '' THEN
// XlWrkShtDotNet.Cell("Row No.","Column No.").Comment := Comment;
XLStyleDotNet := XlWrkShtDotNet.Cell("Row No.","Column No.").Style;
IF Bold THEN
XLStyleDotNet.Font.SetBold(Bold);
IF Italic THEN
XLStyleDotNet.Font.SetItalic(Italic);
XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
IF UNDERLINE THEN
XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
UNTIL NEXT = 0;
XlWrkShtDotNet.Columns(1, "Column No.").AdjustToContents();
END;
IF UseInfoSheed THEN BEGIN
IF InfoExcelBuf.FIND('-') THEN BEGIN
XlWrkShtDotNet := XlWrkBkDotNet.Worksheets.Add('Information');
XlWrkShtDotNet.Name := Text023;
REPEAT
InfoRecNo := InfoRecNo + 1;
WINDOW.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1));
IF InfoExcelBuf.NumberFormat <> '' THEN
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style.NumberFormat.SetFormat
(InfoExcelBuf.NumberFormat);
IF InfoExcelBuf.Formula = '' THEN
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Value := InfoExcelBuf."Cell Value as Text"
ELSE
XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").FormulaA1 := InfoExcelBuf.GetFormula;
//Comments are NOT yet supported BY ClosedXML
//IF InfoExcelBuf.Comment <> '' THEN
// XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Comment := InfoExcelBuf.Comment;
XLStyleDotNet := XlWrkShtDotNet.Cell(InfoExcelBuf."Row No.",InfoExcelBuf."Column No.").Style;
IF InfoExcelBuf.Bold THEN
XLStyleDotNet.Font.SetBold(InfoExcelBuf.Bold);
IF InfoExcelBuf.Italic THEN
XLStyleDotNet.Font.SetItalic(InfoExcelBuf.Italic);
XLStyleDotNet.Border.OutsideBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'None');
IF InfoExcelBuf.Underline THEN
XLStyleDotNet.Border.BottomBorder := GetEnumValue(XLStyleDotNet.Border.BottomBorder,'Medium');
UNTIL InfoExcelBuf.NEXT = 0;
XlWrkShtDotNet.Columns(1, InfoExcelBuf."Column No.").AdjustToContents();
END;
END;
WINDOW.CLOSE;
// .Net <<
END;
GiveUserControl
PROCEDURE GiveUserControl@3();
VAR
TempFile@1000 : File;
FileName@1001 : TEXT[1024];
ToFile@1002 : TEXT[1024];
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
GiveUserControlDotNet;
EXIT;
END;
// .Net <<
XlApp.Visible(TRUE);
XlApp.UserControl(TRUE);
CLEAR(XlApp);
END;
PROCEDURE GiveUserControlDotNet@150002040();
VAR
MemoryStream@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ExcelApp@150002031 : DotNet"'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
ExcelAppClass@150002030 : DotNet"'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
NVInStream@150002025 : InStream;
ThreeTierMgt@150002027 : Codeunit 419;
FileNameRTC@150002028 : TEXT[1024];
BEGIN
// .Net >>
MemoryStream := MemoryStream.MemoryStream();
XlWrkBkDotNet.SaveAs(MemoryStream);
NVInStream := MemoryStream;
DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);
ExcelApp := ExcelAppClass.ApplicationClass();
ExcelApp.Workbooks.Add(FileNameRTC);
ExcelApp.Visible(TRUE);
ExcelApp.UserControl(TRUE);
// .Net <<
END;
VAR
TempFile@1000 : File;
FileName@1001 : TEXT[1024];
ToFile@1002 : TEXT[1024];
BEGIN
// .Net >>
IF ISSERVICETIER THEN BEGIN
GiveUserControlDotNet;
EXIT;
END;
// .Net <<
XlApp.Visible(TRUE);
XlApp.UserControl(TRUE);
CLEAR(XlApp);
END;
PROCEDURE GiveUserControlDotNet@150002040();
VAR
MemoryStream@150002024 : DotNet "'mscorlib, Version=2.0.0.0, Culture=neutral, PublicKeyToken=b77a5c561934e089'.System.IO.MemoryStream";
ExcelApp@150002031 : DotNet"'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel._Application" RUNONCLIENT;
ExcelAppClass@150002030 : DotNet"'Microsoft.Office.Interop.Excel, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c'.Microsoft.Office.Interop.Excel.ApplicationClass" RUNONCLIENT;
NVInStream@150002025 : InStream;
ThreeTierMgt@150002027 : Codeunit 419;
FileNameRTC@150002028 : TEXT[1024];
BEGIN
// .Net >>
MemoryStream := MemoryStream.MemoryStream();
XlWrkBkDotNet.SaveAs(MemoryStream);
NVInStream := MemoryStream;
DOWNLOADFROMSTREAM(NVInStream,'',ThreeTierMgt.Magicpath,'',FileNameRTC);
ExcelApp := ExcelAppClass.ApplicationClass();
ExcelApp.Workbooks.Add(FileNameRTC);
ExcelApp.Visible(TRUE);
ExcelApp.UserControl(TRUE);
// .Net <<
END;
A few remarks:
- Because the code is executed on the Service Tier, a created Excel file must be downloaded to the RTC before it can be opened by the local Excel application. This is done in the function GiveUserControlDotNet. The downloaded file is created with a temporary name. The Workbooks.Add function opens this file as a template. This forces Excel to ask for a new filename when the user saves the file. This is very close to the normal behavior of the Classic Client.
- The opposite counts for files that are imported. They must be uploaded to the Service Tier before they can be opened. See the OpenBookDotNet function for an example.
- When the Regional Settings of Windows does not match the installed language of Office, the Workbooks.Add function will throw an error. A solution for this is posted here.
- Adding comments to specific cells is not yet support with ClosedXML. I’m pretty sure it will be available at short notice.
Performance
And then the question: is it faster? And the answer is: yes, it is very fast! It even outperforms the Classic Client! I did a quick compare between Classic and RTC with an export of 1500 rows and 10 columns:
Client
|
Elapsed Time
|
Classic (Automation)
|
27 sec
|
RTC (Automation)
|
300 sec
|
RTC (.Net Interop)
|
14 sec
|
Enjoy!
Subscribe to:
Posts (Atom)