18.08.2011, 17:58 | #1 |
Участник
|
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: http://blogs.msdn.com/b/nav/archive/...-nav-2009.aspx. As you can see, it means that office automation must run client side, and to reiterate recommendation given: General
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:
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; ... with the code below: 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.
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.) Источник: http://feedproxy.google.com/~r/Micro...ng-on-rtc.aspx
__________________
Расскажите о новых и интересных блогах по Microsoft Dynamics, напишите личное сообщение администратору. |
|