OBJECT Table 50039 Excel Buffer 2 { OBJECT-PROPERTIES { Date=22.07.08; Time=12:07:30; Modified=Yes; Version List=NAVW14.00.03,NAVRU3.70,YB; } PROPERTIES { CaptionML=[ENU=Excel Buffer; RUS=Excel Буфер]; } FIELDS { { 1 ; ;Row No. ;Integer ;OnValidate=BEGIN xlRowID := ''; IF "Row No." <> 0 THEN xlRowID := FORMAT("Row No."); END; CaptionML=[ENU=Row No.; RUS=Линия Но.] } { 2 ; ;xlRowID ;Text10 ;CaptionML=[ENU=xlRowID; RUS=xlRowID] } { 3 ; ;Column No. ;Integer ;OnValidate=VAR x@1000 : Integer; i@1001 : Integer; c@1002 : Char; BEGIN xlColID := ''; IF "Column No." <> 0 THEN BEGIN x := "Column No." - 1; c := 65 + x MOD 26; xlColID[10] := c; i := 10; WHILE x > 25 DO BEGIN x := x DIV 26; i := i - 1; c := 64 + x MOD 26; xlColID[i] := c; END; FOR x := i TO 10 DO xlColID[1+x-i] := xlColID[x]; END; END; CaptionML=[ENU=Column No.; RUS=Столбец Но.] } { 4 ; ;xlColID ;Text10 ;CaptionML=[ENU=xlColID; RUS=xlColID] } { 5 ; ;Cell Value as Text ;Text250 ;CaptionML=[ENU=Cell Value as Text; RUS=Значение Ячейки как Текст] } { 6 ; ;Comment ;Text250 ;CaptionML=[ENU=Comment; RUS=Комментарий] } { 7 ; ;Formula ;Text250 ;CaptionML=[ENU=Formula; RUS=Формула] } { 8 ; ;Bold ;Boolean ;CaptionML=[ENU=Bold; RUS=Bold] } { 9 ; ;Italic ;Boolean ;CaptionML=[ENU=Italic; RUS=Italic] } { 10 ; ;Underline ;Boolean ;CaptionML=[ENU=Underline; RUS=Underline] } { 11 ; ;NumberFormat ;Text30 ;CaptionML=[ENU=NumberFormat; RUS=Формат. Число] } { 12 ; ;Formula2 ;Text250 ;CaptionML=[ENU=Formula2; RUS=Формула2] } { 13 ; ;Formula3 ;Text250 ;CaptionML=[ENU=Formula3; RUS=Формула3] } { 14 ; ;Formula4 ;Text250 ;CaptionML=[ENU=Formula4; RUS=Формула4] } { 12400; ;Font Size ;Integer ;CaptionML=[ENU=Font Size; RUS=Шрифт Размер] } { 50000; ;Type Of Data ;Option ;CaptionML=[ENU=Type Of Data; RUS=Тип Данных]; OptionString=[ ,Decimal] } { 50001; ;Worksheet Name ;Text30 } { 60000; ;Left ;Boolean } { 60001; ;Right ;Boolean } { 60002; ;Top ;Boolean } { 60003; ;Bottom ;Boolean } { 60004; ;WrapText ;Boolean } { 60005; ;Width ;Decimal } { 60006; ;Height ;Decimal } } KEYS { { ;Row No.,Column No. } } CODE { VAR Text000@1000 : TextConst 'ENU=Excel not found.;RUS=Excel не установлен.'; Text001@1001 : TextConst 'ENU=You must enter a file name.;RUS=Вы должны ввести имя файла.'; Text002@1002 : TextConst 'ENU=You must enter an Excel worksheet name.;RUS=Вы должны ввести имя книги Excel.'; Text003@1003 : TextConst 'ENU=The file %1 does not exist.;RUS=Файл %1 не существует.'; Text004@1004 : TextConst 'ENU=The Excel worksheet %1 does not exist.;RUS=Лист Excel %1 не существует.'; Text005@1005 : TextConst 'ENU=Creating Excel worksheet...\\;RUS=Создание листа Excel...\\'; Text006@1006 : TextConst 'ENU=%1%3%4%3Page %2;RUS=%1%3%4%3Страница %2'; Text007@1007 : TextConst 'ENU=Reading Excel worksheet...\\;RUS=Чтение листа Excel...\\'; Text008@1008 : TextConst 'ENU=": Filter ";RUS=": Фильтр "'; Text009@1009 : TextConst 'ENU=" can not be converted into an Excel formula.";RUS=" не может быть преобразован в формулу Excel."'; Text010@1010 : TextConst 'ENU=": Operator ";RUS=": Оператор "'; Text011@1011 : TextConst 'ENU=" is not valid.";RUS=" некорректен."'; Text012@1012 : TextConst 'ENU=: Filter containing more than 1 comparison operator can not be converted into an Excel formula.;RUS=: Фильтр, содержащий более 1 оператора сравнения, не может быть преобразован в формулу Excel.'; Text013@1013 : TextConst 'ENU=&B;RUS=&B'; Text014@1014 : TextConst 'ENU=&D;RUS=&D'; Text015@1015 : TextConst 'ENU=&P;RUS=&P'; Text016@1016 : TextConst 'ENU=A1;RUS=A1'; Text017@1017 : TextConst 'ENU=SUMIF;RUS=SUMIF'; Text018@1018 : TextConst 'ENU=#N/A;RUS=#N/A'; Text019@1019 : TextConst 'ENU=GLAcc;RUS=ФинСчет'; Text020@1020 : TextConst 'ENU=Period;RUS=Период'; Text021@1021 : TextConst 'ENU=Budget;RUS=Бюджет'; InfoExcelBuf@1036 : TEMPORARY Record 370; XlApp@1022 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00024500-0000-0000-C000-000000000046}:Unknown Automation Server.Application"; XlWrkBk@1023 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020819-0000-0000-C000-000000000046}:Unknown Automation Server.Workbook"; XlWrkSht@1024 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020820-0000-0000-C000-000000000046}:Unknown Automation Server.Worksheet"; XlWrkshts@1025 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{000208D8-0000-0000-C000-000000000046}:Unknown Automation Server._Worksheet"; XlRange@1026 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range"; FormulaUnitErr@1027 : Text[250]; Text022@1028 : TextConst 'ENU=Formula is too long.;RUS=Слишком длинная формула.'; RangeStartXlRow@1034 : Text[30]; RangeStartXlCol@1033 : Text[30]; RangeEndXlRow@1032 : Text[30]; RangeEndXlCol@1031 : Text[30]; CurrentRow@1029 : Integer; CurrentCol@1030 : Integer; UseInfoSheed@1035 : Boolean; Text023@1037 : TextConst 'ENU=Information;RUS=Информация'; Text024@1038 : TextConst 'ENU=The number of columns in the table you are trying to export exceeds the Microsoft Excel column limitation;RUS=Число столбцов в таблице, которую вы пытаетесь экспортировать, превышает ограничение, действующее в Microsoft Excel'; XlEdgeBottom@1210000 : Integer; XlContinuous@1210003 : Integer; XlLineStyleNone@1210002 : Integer; XlLandscape@1210001 : Integer; PROCEDURE CreateBook@1(); BEGIN IF NOT CREATE(XlApp,TRUE) THEN ERROR(Text000); XlApp.Visible(FALSE); XlWrkBk := XlApp.Workbooks.Add; XlWrkSht := XlWrkBk.Worksheets.Add; END; PROCEDURE OpenBook@2(FileName@1000 : Text[250];SheetName@1001 : Text[250]); VAR i@1002 : Integer; EndOfLoop@1003 : Integer; Found@1004 : Boolean; BEGIN IF FileName = '' THEN ERROR(Text001); IF SheetName = '' THEN ERROR(Text002); IF NOT EXISTS(FileName) THEN ERROR(Text003,FileName); IF NOT CREATE(XlApp,TRUE) THEN ERROR(Text000); XlApp.Workbooks.Open(FileName); XlWrkBk := XlApp.ActiveWorkbook; i := 1; EndOfLoop := XlWrkBk.Worksheets.Count; WHILE (i <= EndOfLoop) AND (NOT Found) DO BEGIN XlWrkshts := XlWrkBk.Worksheets.Item(i); IF SheetName = XlWrkshts.Name THEN Found := TRUE; i := i + 1; END; IF Found THEN XlWrkSht := XlWrkBk.Worksheets.Item(SheetName) ELSE BEGIN XlWrkBk.Close(FALSE); XlApp.Quit; CLEAR(XlApp); ERROR(Text004,SheetName); END; END; 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; Version@1013 : Integer; VersionString@1014 : Text[30]; BEGIN VersionString := XlApp.Version; IF STRPOS(VersionString,'.') <> 0 THEN VersionString := COPYSTR(VersionString,1,STRPOS(VersionString,'.')-1); EVALUATE(Version,VersionString); IF (Version < 12) THEN BEGIN SETFILTER("Column No.", '>256'); IF FIND('-') THEN ERROR(Text024); SETRANGE("Column No."); END; Window.OPEN( 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; IF UseInfoSheed THEN BEGIN IF InfoExcelBuf.FIND('-') THEN BEGIN XlWrkSht := XlWrkBk.Worksheets.Add(); XlWrkSht.Name := Text023; REPEAT InfoRecNo := InfoRecNo + 1; Window.UPDATE(1,ROUND((RecNo + InfoRecNo) / TotalRecNo * 10000,1)); IF InfoExcelBuf.NumberFormat <> '' THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).NumberFormat := InfoExcelBuf.NumberFormat; IF InfoExcelBuf.Formula = '' THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Value := InfoExcelBuf."Cell Value as Text" ELSE XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Formula := InfoExcelBuf.GetFormula; IF InfoExcelBuf.Comment <> '' THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).AddComment := InfoExcelBuf.Comment; IF InfoExcelBuf.Bold THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Bold := InfoExcelBuf.Bold; IF InfoExcelBuf.Italic THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Font.Italic := InfoExcelBuf.Italic; XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.LineStyle := XlLineStyleNone; IF InfoExcelBuf.Underline THEN XlWrkSht.Range(InfoExcelBuf.xlColID + InfoExcelBuf.xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous; UNTIL InfoExcelBuf.NEXT = 0; XlWrkSht.Range('A' + FORMAT(1) + ':' + xlColID + xlRowID).Columns.AutoFit; END; END; Window.CLOSE; END; PROCEDURE CreateRangeName@9(RangeName@1000 : Text[30];FromColumnNo@1001 : Integer;FromRowNo@1002 : Integer); VAR TempExcelBuf@1003 : TEMPORARY Record 370; ToxlRowID@1004 : Text[10]; BEGIN SETCURRENTKEY("Row No.","Column No."); IF FIND('+') THEN ToxlRowID := xlRowID; TempExcelBuf.VALIDATE("Row No.",FromRowNo); TempExcelBuf.VALIDATE("Column No.",FromColumnNo); XlWrkSht.Names.Add( RangeName, '=' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + TempExcelBuf.xlRowID + ':' + GetExcelReference(4) + TempExcelBuf.xlColID + GetExcelReference(4) + ToxlRowID); END; PROCEDURE GiveUserControl@3(); BEGIN XlApp.Visible(TRUE); XlApp.UserControl(TRUE); //CLEAR(XlApp); CLEARALL; END; PROCEDURE ReadSheet@4(); VAR i@1000 : Integer; j@1001 : Integer; Maxi@1002 : Integer; Maxj@1003 : Integer; Window@1004 : Dialog; BEGIN Window.OPEN( Text007 + '@1@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.UPDATE(1,0); DELETEALL; XlRange := XlWrkSht.Range(GetExcelReference(5)).SpecialCells(11); Maxi := XlRange.Row; Maxj := XlRange.Column; i := 1; REPEAT j := 1; VALIDATE("Row No.",i); REPEAT VALIDATE("Column No.",j); "Cell Value as Text" := DELCHR(FORMAT(XlWrkSht.Range(xlColID + xlRowID).Value),'<',' '); IF "Cell Value as Text" <> '' THEN BEGIN INSERT; END; j := j + 1; UNTIL j > Maxj; i := i + 1; Window.UPDATE(1,ROUND(i / Maxi * 10000,1)); UNTIL i > Maxi; XlWrkBk.Close(FALSE); XlApp.Quit; CLEAR(XlApp); Window.CLOSE; END; PROCEDURE SelectSheetsName@6(FileName@1000 : Text[250]) : Text[250]; VAR i@1001 : Integer; SheetName@1002 : Text[250]; EndOfLoop@1003 : Integer; SheetsList@1004 : Text[250]; OptionNo@1005 : Integer; BEGIN IF FileName <> '' THEN BEGIN IF NOT EXISTS(FileName) THEN ERROR(Text003,FileName); END ELSE EXIT(''); IF NOT CREATE(XlApp,TRUE) THEN ERROR(Text000); XlApp.Workbooks.Open(FileName); XlWrkBk := XlApp.ActiveWorkbook; i := 1; EndOfLoop := XlWrkBk.Worksheets.Count; WHILE i <= EndOfLoop DO BEGIN XlWrkshts := XlWrkBk.Worksheets.Item(i); SheetName := XlWrkshts.Name; IF (SheetName <> '') AND (STRLEN(SheetsList) + STRLEN(SheetName) < 250) THEN SheetsList := SheetsList + SheetName + ',' ELSE i := EndOfLoop; i := i + 1; END; XlWrkBk.Close(FALSE); XlApp.Quit; CLEAR(XlApp); OptionNo := STRMENU(SheetsList,1); IF OptionNo <> 0 THEN EXIT(SELECTSTR(OptionNo,SheetsList)) ELSE EXIT(''); END; PROCEDURE FilterToFormula@7(Filter@1001 : Text[250];Ref1@1002 : Text[250];Ref2@1003 : Text[250]) Formula@1000 : Text[250]; VAR FormulaUnit@1004 : ARRAY [250] OF Code[20]; IsValue@1005 : ARRAY [250] OF Boolean; i@1006 : Integer; j@1007 : Integer; CountComparison@1008 : Integer; BEGIN i := 1; j := 2; REPEAT IF STRPOS('|&>.<()*/+-?@',COPYSTR(Filter,i,1)) > 0 THEN BEGIN IF FormulaUnit[j] <> '' THEN j := j + 1; FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i,1); IF COPYSTR(Filter,i,2) = '..' THEN BEGIN //Case: X..Y FormulaUnit[j] := FormulaUnit[j] + '.'; i := i + 1; END; IF (STRPOS('><',COPYSTR(Filter,i,1)) > 0) THEN BEGIN IF (STRPOS('>=',COPYSTR(Filter,i + 1,1)) > 0) THEN BEGIN //Case: <>X, <=X, >=X FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i + 1,1); i := i + 1; END; END; j := j + 1; END ELSE BEGIN FormulaUnit[j] := FormulaUnit[j] + COPYSTR(Filter,i,1); IsValue[j] := TRUE; END; i := i + 1; UNTIL COPYSTR(Filter,i,1) = ''; j := 1; Formula := '='; WHILE NextValue(FormulaUnit,IsValue,j) DO BEGIN CASE FormulaUnit[j] OF '..': BEGIN CASE TRUE OF IsValue[j - 1] AND IsValue[j + 1]: BEGIN IF FormulaUnit[j - 1] > FormulaUnit[j + 1] THEN BEGIN Formula := ''; FormulaUnitErr := Filter + Text008 + FormulaUnit[j - 1] + '..' + FormulaUnit[j + 1] + Text009; EXIT; END ELSE BEGIN Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j - 1],Ref2) + '-' + SumIf(Ref1,'>=',FormulaUnit[j + 1],Ref2); FormulaUnit[j - 1] := ''; FormulaUnit[j + 1] := ''; END; END; IsValue[j - 1] AND NOT IsValue[j + 1] : BEGIN Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j - 1],Ref2); FormulaUnit[j - 1] := ''; END; NOT IsValue[j - 1] AND IsValue[j + 1]: BEGIN Formula := Formula + SumIf(Ref1,'<=',FormulaUnit[j + 1],Ref2); FormulaUnit[j + 1] := ''; END; END; END; '>': BEGIN Formula := Formula + SumIf(Ref1,'>',FormulaUnit[j + 1],Ref2); CountComparison := CountComparison + 1; FormulaUnit[j + 1] := ''; END; '<': BEGIN Formula := Formula + SumIf(Ref1,'<',FormulaUnit[j + 1],Ref2); CountComparison := CountComparison + 1; FormulaUnit[j + 1] := ''; END; '>=': BEGIN Formula := Formula + SumIf(Ref1,'>=',FormulaUnit[j + 1],Ref2); CountComparison := CountComparison + 1; FormulaUnit[j + 1] := ''; END; '<=': BEGIN Formula := Formula + SumIf(Ref1,'<=',FormulaUnit[j + 1],Ref2); CountComparison := CountComparison + 1; FormulaUnit[j + 1] := ''; END; '<>','&','+','-','/','*','?','@': BEGIN Formula := ''; FormulaUnitErr := Filter + Text010 + FormulaUnit[j] + Text009; EXIT; END; '|': BEGIN IF IsValue[j - 1] AND (FormulaUnit[j - 1] <> '') THEN BEGIN Formula := Formula + SumIf(Ref1,'=',FormulaUnit[j - 1],Ref2); FormulaUnit[j - 1] := ''; END; Formula := Formula + '+'; END; '(',')': BEGIN Formula := Formula + FormulaUnit[j]; END; ELSE BEGIN Formula := ''; FormulaUnitErr := Filter + Text010 + FormulaUnit[j] + Text011; EXIT; END; END; IF CountComparison > 1 THEN BEGIN Formula := ''; FormulaUnitErr := Filter + Text012; EXIT; END; FormulaUnit[j] := ''; END; IF IsValue[j - 1] AND (FormulaUnit[j - 1] <> '') THEN Formula := Formula + SumIf(Ref1,'=',FormulaUnit[j - 1],Ref2); END; LOCAL PROCEDURE NextValue@8(FormulaUnit@1000 : ARRAY [250] OF Code[20];IsValue@1001 : ARRAY [250] OF Boolean;VAR j@1002 : Integer) : Boolean; BEGIN REPEAT j := j + 1; UNTIL NOT IsValue[j]; IF FormulaUnit[j] <> '' THEN EXIT(TRUE) ELSE EXIT(FALSE); END; LOCAL PROCEDURE SumIf@15(Ref1@1000 : Text[250];Operator@1001 : Text[250];Value@1002 : Code[20];Ref2@1003 : Text[250]) : Text[250]; VAR Symbol@1004 : Char; BEGIN Symbol := 34; EXIT( GetExcelReference(6) + '(' + Ref1 + ';' + FORMAT(Symbol) + Operator + '''' + Value + '''' + FORMAT(Symbol) + ';' + Ref2 + ')'); END; PROCEDURE GetFormulaUnitErr@21() : Text[250]; BEGIN EXIT(FormulaUnitErr); END; PROCEDURE GetExcelReference@10(Which@1000 : Integer) : Text[250]; BEGIN CASE Which OF 1:EXIT(Text013); // DO NOT TRANSLATE: &B is the Excel code to turn bold printing on or off for customized Header/Footer. 2:EXIT(Text014); // DO NOT TRANSLATE: &D is the Excel code to print the current date in customized Header/Footer. 3:EXIT(Text015); // DO NOT TRANSLATE: &P is the Excel code to print the page number in customized Header/Footer. 4:EXIT('$'); // DO NOT TRANSLATE: $ is the Excel code for absolute reference to cells. 5:EXIT(Text016); // DO NOT TRANSLATE: A1 is the Excel reference of the first cell. 6:EXIT(Text017); // DO NOT TRANSLATE: SUMIF is the name of the Excel function used to summarize values according to some conditions. 7:EXIT(Text018); // DO NOT TRANSLATE: The #N/A Excel error value occurs when a value is not available to a function or formula. 8:EXIT(Text019); // DO NOT TRANSLATE: GLAcc is used to define an Excel range name. You must refer to Excel rules to change this term. 9:EXIT(Text020); // DO NOT TRANSLATE: Period is used to define an Excel range name. You must refer to Excel rules to change this term. 10:EXIT(Text021); // DO NOT TRANSLATE: Budget is used to define an Excel worksheet name. You must refer to Excel rules to change this term. END; END; PROCEDURE ExportBudgetFilterToFormula@11(VAR ExcelBuf@1000 : Record 370) : Boolean; VAR ExcelBufFormula@1001 : TEMPORARY Record 370; FirstRow@1002 : Integer; LastRow@1003 : Integer; HasFormulaError@1005 : Boolean; ThisCellHasFormulaError@1006 : Boolean; BEGIN ExcelBuf.SETFILTER(Formula,'<>%1',''); IF ExcelBuf.FIND('-') THEN REPEAT ExcelBufFormula := ExcelBuf; ExcelBufFormula.INSERT; UNTIL ExcelBuf.NEXT = 0; ExcelBuf.RESET; WITH ExcelBufFormula DO IF FIND('-') THEN REPEAT ThisCellHasFormulaError := FALSE; ExcelBuf.SETRANGE("Column No.",1); ExcelBuf.SETFILTER("Row No.",'<>%1',"Row No."); ExcelBuf.SETFILTER("Cell Value as Text",Formula); IF ExcelBuf.FIND('-') THEN REPEAT IF NOT GET(ExcelBuf."Row No.","Column No.") THEN ExcelBuf.MARK(TRUE); UNTIL ExcelBuf.NEXT = 0; ClearFormula; ExcelBuf.SETRANGE("Cell Value as Text"); ExcelBuf.SETRANGE("Row No."); IF ExcelBuf.FIND('-') THEN REPEAT IF ExcelBuf.MARK THEN BEGIN LastRow := ExcelBuf."Row No."; IF FirstRow = 0 THEN FirstRow := LastRow; END ELSE IF FirstRow <> 0 THEN BEGIN IF FirstRow = LastRow THEN ThisCellHasFormulaError := AddToFormula(xlColID + FORMAT(FirstRow)) ELSE ThisCellHasFormulaError := AddToFormula('sum(' + xlColID + FORMAT(FirstRow) + ':' + xlColID + FORMAT(LastRow) + ')'); FirstRow := 0; IF ThisCellHasFormulaError THEN BEGIN SetFormula(ExcelBuf.GetExcelReference(7)); Comment := ExcelBuf.GetFormulaUnitErr; END; END; UNTIL ThisCellHasFormulaError OR (ExcelBuf.NEXT = 0); IF NOT ThisCellHasFormulaError AND (FirstRow <> 0) THEN BEGIN IF FirstRow = LastRow THEN ThisCellHasFormulaError := AddToFormula(xlColID + FORMAT(FirstRow)) ELSE ThisCellHasFormulaError := AddToFormula('sum(' + xlColID + FORMAT(FirstRow) + ':' + xlColID + FORMAT(LastRow) + ')'); FirstRow := 0; IF ThisCellHasFormulaError THEN BEGIN SetFormula(ExcelBuf.GetExcelReference(7)); Comment := ExcelBuf.GetFormulaUnitErr; END; END; ExcelBuf.RESET; ExcelBuf.GET("Row No.","Column No."); ExcelBuf.SetFormula(GetFormula); ExcelBuf.MODIFY; HasFormulaError := HasFormulaError OR ThisCellHasFormulaError; UNTIL NEXT = 0; EXIT(HasFormulaError); END; PROCEDURE AddToFormula@12(Text@1001 : Text[30]) : Boolean; VAR Overflow@1002 : Boolean; LongFormula@1000 : Text[1000]; BEGIN LongFormula := GetFormula; IF LongFormula = '' THEN LongFormula := '='; IF LongFormula <> '=' THEN IF STRLEN(LongFormula) + 1 > MAXSTRLEN(LongFormula) THEN Overflow := TRUE ELSE LongFormula := LongFormula + '+'; IF STRLEN(LongFormula) + STRLEN(Text) > MAXSTRLEN(LongFormula) THEN Overflow := TRUE ELSE SetFormula(LongFormula + Text); EXIT(Overflow); END; PROCEDURE GetFormula@13() : Text[1000]; BEGIN EXIT(Formula + Formula2 + Formula3 + Formula4); END; PROCEDURE SetFormula@22(LongFormula@1000 : Text[1000]); BEGIN ClearFormula; IF LongFormula = '' THEN EXIT; Formula := COPYSTR(LongFormula,1,MAXSTRLEN(Formula)); IF STRLEN(LongFormula) > MAXSTRLEN(Formula) THEN Formula2 := COPYSTR(LongFormula,MAXSTRLEN(Formula) + 1,MAXSTRLEN(Formula2)); IF STRLEN(LongFormula) > MAXSTRLEN(Formula) + MAXSTRLEN(Formula2) THEN Formula3 := COPYSTR(LongFormula,MAXSTRLEN(Formula) + MAXSTRLEN(Formula2) + 1,MAXSTRLEN(Formula3)); IF STRLEN(LongFormula) > MAXSTRLEN(Formula) + MAXSTRLEN(Formula2) + MAXSTRLEN(Formula3) THEN Formula4 := COPYSTR(LongFormula,MAXSTRLEN(Formula) + MAXSTRLEN(Formula2) + MAXSTRLEN(Formula3) + 1,MAXSTRLEN(Formula4)); END; PROCEDURE ClearFormula@18(); BEGIN Formula := ''; Formula2 := ''; Formula3 := ''; Formula4 := ''; END; PROCEDURE NewRow@14(); BEGIN CurrentRow := CurrentRow + 1; CurrentCol := 0; END; PROCEDURE AddColumn@16(Value@1000 : Variant;IsFormula@1001 : Boolean;CommentText@1002 : Text[1000];IsBold@1003 : Boolean;IsItalics@1004 : Boolean;IsUnderline@1005 : Boolean;NumFormat@1006 : Text[30]); BEGIN IF CurrentRow < 1 THEN NewRow; CurrentCol := CurrentCol + 1; INIT; VALIDATE("Row No.",CurrentRow); VALIDATE("Column No.",CurrentCol); IF IsFormula THEN SetFormula(FORMAT(Value)) ELSE "Cell Value as Text" := FORMAT(Value); Comment := CommentText; Bold := IsBold; Italic := IsItalics; Underline := IsUnderline; NumberFormat := NumFormat; INSERT; END; PROCEDURE StartRange@19(); VAR TempExcelBuf@1000 : TEMPORARY Record 370; BEGIN TempExcelBuf.VALIDATE("Row No.",CurrentRow); TempExcelBuf.VALIDATE("Column No.",CurrentCol); RangeStartXlRow := TempExcelBuf.xlRowID; RangeStartXlCol := TempExcelBuf.xlColID; END; PROCEDURE EndRange@23(); VAR TempExcelBuf@1000 : TEMPORARY Record 370; BEGIN TempExcelBuf.VALIDATE("Row No.",CurrentRow); TempExcelBuf.VALIDATE("Column No.",CurrentCol); RangeEndXlRow := TempExcelBuf.xlRowID; RangeEndXlCol := TempExcelBuf.xlColID; END; PROCEDURE CreateRange@45(RangeName@1000 : Text[50]) : Boolean; BEGIN XlWrkSht.Names.Add( RangeName, '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow + ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow); END; PROCEDURE AutoFit@20(RangeName@1000 : Text[50]); VAR XlRange1@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range"; BEGIN XlRange1 := XlWrkSht.Range(RangeName); XlRange1.Columns.AutoFit; END; PROCEDURE BorderAround@39(RangeName@1000 : Text[50]); VAR XlRange1@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range"; BEGIN XlRange1 := XlWrkSht.Range(RangeName); XlRange1.BorderAround(1); END; PROCEDURE ClearNewRow@26(); BEGIN CurrentRow := 0; CurrentCol := 0; END; PROCEDURE SetUseInfoSheed@25(); BEGIN UseInfoSheed := TRUE; END; PROCEDURE AddInfoColumn@24(Value@1006 : Variant;IsFormula@1005 : Boolean;CommentText@1004 : Text[1000];IsBold@1003 : Boolean;IsItalics@1002 : Boolean;IsUnderline@1001 : Boolean;NumFormat@1000 : Text[30]); BEGIN IF CurrentRow < 1 THEN NewRow; CurrentCol := CurrentCol + 1; INIT; InfoExcelBuf.VALIDATE("Row No.",CurrentRow); InfoExcelBuf.VALIDATE("Column No.",CurrentCol); IF IsFormula THEN InfoExcelBuf.SetFormula(FORMAT(Value)) ELSE InfoExcelBuf."Cell Value as Text" := FORMAT(Value); InfoExcelBuf.Bold := IsBold; InfoExcelBuf.Italic := IsItalics; InfoExcelBuf.Underline := IsUnderline; InfoExcelBuf.NumberFormat := NumFormat; InfoExcelBuf.INSERT; END; PROCEDURE UTgetGlobalValue@35(globalVariable@1001 : Text[30];VAR value@1000 : Variant); BEGIN CASE globalVariable OF 'CurrentRow': value := CurrentRow; 'CurrentCol': value := CurrentCol; 'RangeStartXlRow': value := RangeStartXlRow; 'RangeStartXlCol': value := RangeStartXlCol; 'RangeEndXlRow': value := RangeEndXlRow; 'RangeEndXlCol': value := RangeEndXlCol; 'XlWrkSht': value := XlWrkSht; ELSE ERROR('Global variable %1 is not included for test.',globalVariable); END; END; PROCEDURE SetCurrent@27(NewCurrentRow@1000 : Integer;NewCurrentCol@1001 : Integer); BEGIN CurrentRow := NewCurrentRow; CurrentCol := NewCurrentCol; END; PROCEDURE CreateValidationRule@17(Range@1000 : Code[20]); VAR XLValidation@1001 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{0002442F-0000-0000-C000-000000000046}:Unknown Automation Server.Validation"; XLRange@1002 : Automation "{00020813-0000-0000-C000-000000000046} 1.2:{00020846-0000-0000-C000-000000000046}:Unknown Automation Server.Range"; BEGIN XlApp.Goto(Range); XLRange := XlApp.Selection; XLValidation := XLRange.Validation; XLValidation.Add(3,1,1, '=' + GetExcelReference(4) + RangeStartXlCol + GetExcelReference(4) + RangeStartXlRow + ':' + GetExcelReference(4) + RangeEndXlCol + GetExcelReference(4) + RangeEndXlRow); XlApp.Goto(XlApp.PreviousSelections(1)); END; PROCEDURE SaveCellsToExcel@1210000(); VAR RecNo@1210002 : Integer; Window@1210001 : Dialog; TotalRecNo@1210000 : Integer; XlEdgeBottom@1210003 : Integer; XlContinuous@1210005 : Integer; BEGIN Window.OPEN( Text005 + '@1@@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.UPDATE(1,0); XlEdgeBottom := 9; XlContinuous := 1; TotalRecNo := COUNTAPPROX; RecNo := 0; IF FIND('-') THEN REPEAT RecNo := RecNo + 1; Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1)); IF Formula = '' THEN XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text" ELSE XlWrkSht.Range(xlColID + xlRowID).Formula := Formula; 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; IF "Font Size" <> 0 THEN XlWrkSht.Range(xlColID + xlRowID).Font.Size := "Font Size"; IF Underline THEN XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous; UNTIL NEXT = 0; Window.CLOSE; END; PROCEDURE InsertLines@1000000001(LineNo@1000000000 : Integer); VAR TextRange@1000000002 : Text[30]; xlDown@1000000001 : Integer; BEGIN xlDown := -4121; TextRange := FORMAT(LineNo)+':'+FORMAT(LineNo); XlWrkSht.Range(TextRange).Copy; TextRange := FORMAT(LineNo+1)+':'+FORMAT(LineNo+1); XlWrkSht.Range(TextRange).Select; XlWrkSht.Range(TextRange).Insert(xlDown); XlWrkSht.Range(TextRange).ClearContents; END; PROCEDURE CopyLine@1000000003(LineNo@1000000000 : Integer;From@1000000003 : Integer); VAR TextRange@1000000002 : Text[30]; xlDown@1000000001 : Integer; BEGIN TextRange := FORMAT(From)+':'+FORMAT(From); XlWrkSht.Range(TextRange).Select; XlWrkSht.Range(TextRange).Copy; TextRange := FORMAT(LineNo)+':'+FORMAT(LineNo); XlWrkSht.Range(TextRange).Select; XlWrkSht.Paste; XlApp.CutCopyMode := 1; END; PROCEDURE DeleteLines@1000000004(LineFrom@1000000000 : Integer;LineTo@1000000001 : Integer); VAR TextRange@1000000002 : Text[30]; BEGIN TextRange := FORMAT(LineFrom)+':'+FORMAT(LineTo); XlWrkSht.Range(TextRange).Select; XlWrkSht.Range(TextRange).Delete; END; PROCEDURE RunMacros@1000000000(MacrosName@1000000000 : Text[60]); BEGIN XlApp.Run(MacrosName); END; PROCEDURE RunMacros1P@1000000011(MacrosName@1000000000 : Text[60];Parameter1@1000000001 : Text[60]); BEGIN XlApp.Run(MacrosName,Parameter1); END; PROCEDURE RunMacros2P@1000000015(MacrosName@1000000000 : Text[60];Parameter1@1000000001 : Text[60];Parameter2@1000000002 : Text[60]); BEGIN XlApp.Run(MacrosName,Parameter1,Parameter2); END; PROCEDURE RunMacros3P@1000000017(MacrosName@1000000000 : Text[60];Parameter1@1000000001 : Text[60];Parameter2@1000000002 : Text[60];Parameter3@1000000003 : Text[60]); BEGIN XlApp.Run(MacrosName,Parameter1,Parameter2,Parameter3); END; PROCEDURE RunMacros4P@1000000018(MacrosName@1000000000 : Text[60];Parameter1@1000000001 : Text[60];Parameter2@1000000002 : Text[60];Parameter3@1000000003 : Text[60];Parameter4@1000000004 : Text[60]); BEGIN XlApp.Run(MacrosName,Parameter1,Parameter2,Parameter3,Parameter4); END; PROCEDURE Save@1000000012(); BEGIN XlWrkBk.Save; END; PROCEDURE SaveAs@1000000007(FileName@1000000000 : Text[250]); BEGIN XlWrkBk.SaveCopyAs(FileName); END; PROCEDURE CloseBook@1000000006(Save@1000000000 : Boolean); BEGIN XlWrkBk.Close(Save); END; PROCEDURE CopyBook@1000000008(); BEGIN XlWrkBk.SaveCopyAs; END; PROCEDURE QuitExcel@1000000009(); BEGIN XlApp.Quit; END; PROCEDURE SelectSheet@1000000010(Caption@1000000000 : Text[60]); BEGIN XlWrkSht := XlWrkBk.Worksheets.Item(Caption); XlWrkSht.Activate; END; PROCEDURE SaveCellsToExcelInWshs@1000000013(); VAR RecNo@1210002 : Integer; Window@1210001 : Dialog; TotalRecNo@1210000 : Integer; DecForEval@1000000000 : Decimal; CurrWsh@1000000001 : Text[30]; BEGIN SETCURRENTKEY("Worksheet Name","Row No.","Column No."); Window.OPEN( Text005 + '@1@@@@@@@@@@@@@@@@@@@@@@@@@\'); Window.UPDATE(1,0); TotalRecNo := COUNTAPPROX; RecNo := 0; IF FIND('-') THEN BEGIN CurrWsh := "Worksheet Name"; SelectSheet(CurrWsh); REPEAT IF CurrWsh <> "Worksheet Name" THEN BEGIN CurrWsh := "Worksheet Name"; SelectSheet(CurrWsh); END; RecNo := RecNo + 1; Window.UPDATE(1,ROUND(RecNo / TotalRecNo * 10000,1)); IF Formula = '' THEN BEGIN CASE "Type Of Data" OF "Type Of Data"::Decimal: BEGIN IF EVALUATE(DecForEval,"Cell Value as Text") THEN XlWrkSht.Range(xlColID + xlRowID).Value := DecForEval ELSE XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text"; END; ELSE BEGIN XlWrkSht.Range(xlColID + xlRowID).Value := "Cell Value as Text" END; END; END ELSE XlWrkSht.Range(xlColID + xlRowID).Formula := Formula; 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; IF "Font Size" <> 0 THEN XlWrkSht.Range(xlColID + xlRowID).Font.Size := "Font Size"; IF Underline THEN XlWrkSht.Range(xlColID + xlRowID).Borders.Item(XlEdgeBottom).LineStyle := XlContinuous; UNTIL NEXT = 0; END; Window.CLOSE; END; PROCEDURE EnterCellWithSheet@1210005(SheetName@1210000 : Text[250];RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];FontSize@1000000001 : Integer;Bold@1003 : Boolean); BEGIN INIT; VALIDATE("Worksheet Name", SheetName); VALIDATE("Row No.",RowNo); VALIDATE("Column No.",ColumnNo); "Cell Value as Text" := CellValue; "Font Size" := FontSize; //Если FontSize = 0, то кегль будет таким как в шаблоне (не изменится) Bold := Bold; INSERT; END; PROCEDURE EnterCell5@1000000014(RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];FontSize@1000000001 : Integer;Bold@1003 : Boolean); BEGIN INIT; VALIDATE("Row No.",RowNo); VALIDATE("Column No.",ColumnNo); "Cell Value as Text" := CellValue; "Font Size" := FontSize; //Если FontSize = 0, то кегль будет таким как в шаблоне (не изменится) Bold := Bold; INSERT; END; PROCEDURE EnterCell4@1210001(RowNo@1000 : Integer;ColumnNo@1001 : Integer;p_Bold@1003 : Boolean;CellValue@1210000 : Text[250]); BEGIN VALIDATE("Row No.",RowNo); VALIDATE("Column No.",ColumnNo); "Cell Value as Text" := CellValue; Bold := p_Bold; INSERT; END; PROCEDURE EnterCell5Dec@1000000016(RowNo@1000 : Integer;ColumnNo@1001 : Integer;CellValue@1002 : Text[250];FontSize@1000000001 : Integer;Bold@1003 : Boolean); BEGIN INIT; "Type Of Data" := "Type Of Data"::Decimal; VALIDATE("Row No.",RowNo); VALIDATE("Column No.",ColumnNo); "Cell Value as Text" := CellValue; "Font Size" := FontSize; //Если FontSize = 0, то кегль будет таким как в шаблоне (не изменится) Bold := Bold; INSERT; END; BEGIN END. } }