Due to many and frequent questions on the topic of performance when exporting to MS Excel on RTC, I’ve summarized some of the answers in this blog to try and explain the impact of running MS Office automation objects on RTC .
Let’s start with Stefan’s excellent blog on automation objects and considerations running server/client side:
As you can see, it means that office automation must run client side, and to reiterate recommendation given:
General
- It is good practice to design your Automation interfaces to get as much work done in a single method invocation. And when consuming a component, try to use it in way that minimizes chattiness. This is utmost importance for the client side automation scenario.
Currently (and traditionally), some export to excel functions use office automation and table 370 (Excel Buffer), where export is run on row-by-row basis, meaning each row is exported and formatted in a separate call. If you are exporting hundreds or thousands of row, you’re in effect maximizing ‘chattiness’, which will result in considerably slower performance when exporting data to excel (using standard application functionality and especially when running on RTC). This applies to functions like reports Aged Accounts Recevaibles (120) (export to excel), Customer/Item Sales (export to excel), then Budget: Export to Excel, Account Schedules: Export to Excel, Trial Balance: Export to Excel, RIM: Export to excel… these will all run slower on RTC, as office automation is invoked for each row exported.
This does not apply to export using Send-to option (Send-to Excel), as this uses msxml6.0. This also doesn’t include export of Analysis by Dimensions, as here all data are exported to a flat file (then downloaded to client), which is then formatted in few calls using office automation. This will not strain performance and will run about as quickly from RTC as from classic.
So what to do about functions that do see significant drop in performance (specially for larger data amount)? Well, there has to be several ways around this, here are some:
-
If you are going to export large amounts of data to excel, why not try Jet Reports for your own reporting?
-
Then, MSXML can run serverside (and thus runs fast) and you can create spreadsheet format file for excel. Example: RIM functionality offers export to xml or Excel. Both (by design) run client side as RIM is really intended for setup data, and that normally means a small amount of data and should run fine client side. But should you want to, it takes only few lines of code to switch to serverside running for xml, and then you can export large amounts of data to xml in same time classic client would run, then download and open generated xml file with excel (and import the same way back).
-
You can potentialy use SAVEASEXCEL, and it is maybe the simplest solution, but this will have a limitation in number of records you can export (65K), so you can’t use it for larger export. Also you’ll need to modify each report separately and also imoprt if you want to use it to export budgets.
-
Follow the example from Analysis by dimensions (codeunit 424), which exports data to a flat file, that is then afterwards downloaded to client and opened with excel. Excel file formatting is done in only few additional calls, so the performance on RTC is similar to that on classic client. Also, as a flat file size will only increase slowly with amount of data, you can process and export large amount of records quite quickly, created file is downloaded to client and office automation invoked only for file formatting and opening.
- …
Text005 +
‘@1@@@@@@@@@@@@@@@@@@@@@@@@@\’);
Window.UPDATE(1,0);
XlEdgeBottom := 9;
XlContinuous := 1;
XlLineStyleNone := -4142;
XlLandscape := 2;
CRLF := 10;
RecNo := 1;
TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
RecNo := 0;
XlWrkSht.Name := SheetName;
IF ReportHeader <> ” THEN
XlWrkSht.PageSetup.LeftHeader :=
STRSUBSTNO(‘%1%2%1%3%4’,GetExcelReference(1),ReportHeader,CRLF,CompanyName);
XlWrkSht.PageSetup.RightHeader :=
STRSUBSTNO(Text006,GetExcelReference(2),GetExcelReference(3),CRLF,UserID2);
XlWrkSht.PageSetup.Orientation := XlLandscape;
IF FIND(‘-‘) THEN BEGIN
REPEAT
RecNo := RecNo + 1;
Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1));
IF NumberFormat <> ” THEN
XlWrkSht.Range(xlColID + xlRowID).NumberFormat := NumberFormat;
IF Formula = ” THEN
XlWrkSht.Range(xlColID + xlRowID).Value := “Cell Value as Text”
ELSE
XlWrkSht.Range(xlColID + xlRowID).Formula := GetFormula;
IF Comment <> ” THEN
XlWrkSht.Range(xlColID + xlRowID).AddComment := Comment;
IF Bold THEN
XlWrkSht.Range(xlColID + xlRowID).Font.Bold := Bold;
IF Italic THEN
XlWrkSht.Range(xlColID + xlRowID).Font.Italic := Italic;
XlWrkSht.Range(xlColID + xlRowID).Borders.LineStyle := XlLineStyleNone;
IF Underline THEN
XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous;
UNTIL NEXT = 0;
XlWrkSht.Range(GetExcelReference(5) + ‘:’ + xlColID + xlRowID).Columns.AutoFit;
END;
Window.OPEN(
Text005 +
‘@1@@@@@@@@@@@@@@@@@@@@@@@@@\’);
Window.UPDATE(1,0);
XlApp.ActiveWorkbook.Close(FALSE);
c := 9;
Tab[1] := c;
XlContinuous := 1;
XLLandscape := 2;
XLLinestyleNone := -4142;
XlEdgeBottom := 9;
CRLF := 10;
TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
NoOfRows := 0;
InitCharTables;
TextFile.CREATETEMPFILE;
FileName := TextFile.NAME + ‘.txt’;
TextFile.CLOSE;
TextFile.CREATE(FileName);
TextFile.TEXTMODE(TRUE);
IF FINDFIRST THEN BEGIN
FOR i := 1 TO STRLEN(xlColID) DO
CurrAnsiCode := CurrAnsiCode + xlColID[i];
RecNo := xlRowID;
END;
IF FIND(‘-‘) THEN BEGIN
REPEAT
IF xlRowID <> RecNo THEN BEGIN
EVALUATE(CurrXlRow,RecNo);
EVALUATE(NextXlRow,xlRowID);
FOR i := 1 TO (NextXlRow -CurrXlRow) DO
BEGIN
Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);
TextFile.WRITE(Line[1]);
Line[1] := ”;
END;
CurrAnsiCode := 65;
AnsiCode := 0;
NewLine := TRUE;
END;
Window.UPDATE(1,ROUND(NoOfRows / TotalRecNo * 10000,1));
FOR i := 1 TO STRLEN(xlColID) DO
AnsiCode := AnsiCode + xlColID[i];
IF ((AnsiCode – CurrAnsiCode) > 1) OR (((AnsiCode – CurrAnsiCode) > 0) AND NewLine) THEN
FOR i := 1 TO (AnsiCode – CurrAnsiCode) DO
Line[1] := Line[1] + Tab;
IF NumberFormat = ” THEN BEGIN
IF EVALUATE(DecVal,”Cell Value as Text”) THEN
Line[1] := Line[1] + FORMAT(DecVal) + Tab
ELSE
Line[1] := Line[1] + “Cell Value as Text” + Tab
END ELSE
Line[1] := Line[1] + “Cell Value as Text” + Tab;
IF Comment <> ” THEN
Line[1] := Line[1] + Comment + Tab;
NoOfRows := NoOfRows + 1;
RecNo := xlRowID;
CurrAnsiCode := 0;
AnsiCode := 0;
FOR i := 1 TO STRLEN(xlColID) DO
CurrAnsiCode := CurrAnsiCode + xlColID[i];
NewLine := FALSE;
UNTIL NEXT = 0;
END;
TextFile.CLOSE;
FileName := ThreeTierMngt.DownloadTempFile(FileName);
XlApp.Workbooks.OpenText(FileName);
XlWrkSht := XlApp.ActiveSheet;
xlSheetName := SheetName;
xlSheetName := CONVERTSTR(xlSheetName,’ -+’,’___’);
XlWrkSht.Name := xlSheetName;
…
where following are local variables:
Name DataType Subtype Length
Tab Text 1
c Char
CRLF Char
Window Dialog
TextFile File
FileName Text 1024
Line Text 1024
Line2 Text 1024
ThreeTierMngt Codeunit 3-Tier Automation Mgt.
xlSheetName Text 100
i Integer
CurrXlRow Integer
DecVal Decimal
NextXlRow Integer
CurrXlCol Integer
AnsiCode Integer
CurrAnsiCode Integer
NewLine Boolean
And that should be it really. Now all the export functionalities mentioned above (and the other exports using this table), should export to flat file (that will in the end result in an Excel export file), and relatively fast. Keep in mind this is just illustrating example, tested on W1 and only on the exports that are mentioned above.
One note thoguh, countries with ‘space’ as digit grouping symbol (some of these are Norway, France, and more….) might experience the old issue of numbers being exported to text (as digit grouping symbol is not a proper space and ANSI sign for that is different then ASCII, so the resulting export is treated as text). If your regional settings have this digit grouping symbol (looks like a space), and you’re using something similar to the code above, add the following lines to the above code to resolve that: (in same trigger, CreateCSV), add following lines:
…
TotalRecNo := COUNTAPPROX + InfoExcelBuf.COUNTAPPROX;
NoOfRows := 0;
ansispace[1] := 32; //ADDED LINE
asciispace[1] := 160; //ADDED LINE
…
BEGIN
Line[1] := CONVERTSTR(Line[1],CharsNavision,CharsWindows);
Line[1] := CONVERTSTR(Line[1],asciispace,ansispace); //ADDED LINE
TextFile.WRITE(Line[1]);
Line[1] := ”;
…
where ansispace and asciispace are local variables Type Text, Length 1.
Now while fiddling with this I also run accross couple of things i’d like to comment on in std application, i’m just adding them here to be thorough.
- Filename : Creating new file when exporting, regardless of the file name selected (and you get the option to select), your file will get the temp file name. This is funcitonality as it is today, but change it easily in the same trigger, by renaming the file after export (or download), or copying to the file with given filename (after export or download).
- Account schedule: export to excel. In classic client, if you select to create a new book, you will not have an option of selecting file nor sheet name. These are available only when updating existing workbook. In RTC, as aftermath of page transformation, selecting filename is available for both options, and if users are not familiar wiht this fucntionality and try selecting file name when creating workbook (which you’re not supposed to do), an automation error might occur on RTC. To align request page functionality with classic request from, add a group to request page of report 29, and indent fields FileName and SheetName under that group. Set Editable and Enabled properties of the group to Show, where Show is a global boolean variable with IncludeInDatatset = TRUE; Then replace the code in trigger PageUpdateRequestForm wiht following code:
IF Option = Option::”Update Workbook” THEN BEGIN
IF NOT ISSERVICETIER THEN BEGIN
FileNameEnable := TRUE;
SheetNameEnable := TRUE;
END ELSE BEGIN //MODIFIED LINE
IF Option = 1 THEN show := TRUE ELSE //ADDED LINE
show := FALSE; //ADDED LINE
END; //ADDED LINE
END ELSE BEGIN //MODIFIED LINE
FileName := ”;
UploadedFileName := ”;
SheetName := ”;
IF NOT ISSERVICETIER THEN BEGIN
FileNameEnable := FALSE;
SheetNameEnable := FALSE;
END ELSE //MODIFIED LINE
BEGIN //ADDED LINE
IF Option = 1 THEN show := TRUE ELSE //ADDED LINE
show := FALSE; //ADDED LINE
END; //ADDED LINE
END;
and request page should work as request from does on classic, avoiding automation errors if you’re not familiar with this report’s usage.
// Copyright © Microsoft Corporation. All Rights Reserved.
// This code released under the terms of the
// Microsoft Public License (MS-PL, http://opensource.org/licenses/ms-pl.html.)