Excel отчет. Галактика 8.00

[Евгений]

Привет всем!!!

Не подскажете простой пример формирования отчета (Eсxel) в интерфейсе.

Галактика 8.00

[Sytin]

Interface CreateOtchet;
create view
var
CurIndex:integer;
COName:string;

FBasName : string;
XlRes:boolean;
Npp:integer;
d1,d2:date;
CurIndTmp:integer;

VidStr:string;

FirstDog
:boolean;

Sum3
, Sum4
, Sum5
, Sum6
, Sum7
, Sum8
, Sum9
, Sum10

, SumAll3
, SumAll4
, SumAll5
, SumAll6
, SumAll7
, SumAll8
, SumAll9
, SumAll10
:double;

from TabPick,TabOrg,TabDogovor,TabAkt,KatOrg
where ((
TabPick._Nrec==TabOrg._cTabPick
and TabOrg._Nrec==TabDogovor._cTabOrg
and TabDogovor._Nrec==TabAkt._cTabDogovor

and TabOrg._NrecOrg==KatOrg.nrec
));

parameters
d1,d2,VidStr;

HandleEvent
cmInit:{
xlCreateExcel('report.xls',true);
xlCreateMatrix(5000,20);


xlSetColumnWidth(30,1,1,1,1);
xlSetColumnWidth(15,1,2,1,2);
xlSetColumnWidth(10,1,3,1,10);
xlSetColumnWidth(15,1,11,1,12);

xlSetColumnWidth(15,1,3,1,3);
xlSetColumnWidth(15,1,10,1,10);


xlAlignCellsEx(4,3,1,1,1,12);
xlMergeCells(1,1,1,12);
xlSTWriteToMatrix(1,1,'Внутренний первичный документ "Моспроект-2" им. М.В. Посохина');


xlAlignCellsEx(4,3,2,1,2,12);
xlMergeCells(2,1,2,12);
xlSTWriteToMatrix(2,1,'Приложение №_____ к учетной политике.');


xlSTWriteToMatrix(3,1,'Информация о платежах (авансах) за период с '+DateToStr(d1,'DD.MM.YYYY')+' по '+dateToStr(d2,'DD.MM.YYYY'));
xlSetFontStyle(1,3,1,3,1);
xlAlignCellsEx(3,2,3,1,3,1);
xlMergeCells(3,1,3,12);

xlMergeCells(4,1,4,12);
xlAlignCellsEx(3,1,4,1,4,1);
xlSTWriteToMatrix(4,1,'Вид договоров: '+VidStr);

xlSetFontSize(8,4,1,32000,23);


xlMergeCells(5,1,6,1);
xlMergeCells(5,2,6,2);
xlMergeCells(5,3,6,3);
xlMergeCells(5,10,6,10);
xlMergeCells(5,11,6,11);
xlMergeCells(5,12,6,12);

xlMergeCells(5,4,5,5);
xlMergeCells(5,6,5,7);
xlMergeCells(5,8,5,9);


xlSetFontStyle(1,5,1,7,12);
xlSetRowHeight(42,5,1,5,12);
xlSTWriteToMatrix(5,1,'Организация');
xlSTWriteToMatrix(5,2,'Шифр договора');
xlSTWriteToMatrix(5,3,'Остаток авансовых платежей на начало периода');
xlSTWriteToMatrix(5,4,'Сумма поступивших/выданных платежей за период');
xlSTWriteToMatrix(5,6,'Сумма зачтенных платежей');
xlSTWriteToMatrix(5,8,'Возврат авансов');
xlSTWriteToMatrix(5,10,'Остаток авансовых платежей');
xlSTWriteToMatrix(5,11,'№ акта и дата');
xlSTWriteToMatrix(5,12,'№ п/п и дата');


xlSTWriteToMatrix(6,4,'Сумма');
xlSTWriteToMatrix(6,5,'В т.ч. НДС');

xlSTWriteToMatrix(6,6,'Сумма');
xlSTWriteToMatrix(6,7,'В т.ч. НДС');

xlSTWriteToMatrix(6,8,'Сумма');
xlSTWriteToMatrix(6,9,'В т.ч. НДС');

xlFrameCells(1 or 2 or 4 or 8 or 16 or 32 or 16 or 32,2,0,0,5,1,7,12);
xlAlignCellsEx(3,2,5,1,7,12);
//xlSetFontStyle(1,3,1,32000,12);

Npp:=1;
CurIndex:=7;

SumAll3:=0;
SumAll4:=0;
SumAll5:=0;
SumAll6:=0;
SumAll7:=0;
SumAll8:=0;
SumAll9:=0;
SumAll10:=0;

_loop TabPick{
xlMergeCells(CurIndex,1,CurIndex,12);
xlSetFontSize(10,CurIndex,1,CurIndex,12);
xlAlignCellsEx(3,2,CurIndex,1,CurIndex,12);
xlSetFontStyle(1,CurIndex,1,CurIndex,12);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSTWriteToMatrix(CurIndex,1,TabPick._NamePick);
CurIndex:=CurIndex+1;

Sum3:=0;
Sum4:=0;
Sum5:=0;
Sum6:=0;
Sum7:=0;
Sum8:=0;
Sum9:=0;
Sum10:=0;
_loop TabOrg{
FirstDog:=TRUE;

_loop TabDogovor{
if (FirstDog){
xlSetFontStyle(1,CurIndex,1,CurIndex,12);
xlAlignCellsEx(3,1,CurIndex,1,CurIndex,12);
//xlSetFontStyle(1,CurIndex,1,CurIndex,12);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSTWriteToMatrix(CurIndex,1,KatOrg.Name);
xlSetNumberFormat ( '# ##0,00',CurIndex,3,CurIndex,10);
xlSTWriteToMatrix(CurIndex,3,TabOrg._SumSaldo);
xlSTWriteToMatrix(CurIndex,10,TabOrg._SumSaldoEnd);
CurIndex:=CurIndex+1;
FirstDog:=FALSE;
}

xlAlignCellsEx(3,1,CurIndex,2,CurIndex,2);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSetNumberFormat ( '@',CurIndex,2,CurIndex,2);
xlSTWriteToMatrix(CurIndex,2,TabDogovor._NoDoc);
xlSetNumberFormat ( '# ##0,00',CurIndex,3,CurIndex,10);
xlSTWriteToMatrix(CurIndex,3,TabDogovor._SaldoDog);
xlSTWriteToMatrix(CurIndex,4,TabDogovor._SumPlPorDog);
xlSTWriteToMatrix(CurIndex,5,TabDogovor._SumPlPorDogNDS);
xlSTWriteToMatrix(CurIndex,8,TabDogovor._SumPlPorVozvr);
xlSTWriteToMatrix(CurIndex,9,TabDogovor._SumPlPorVozvrNDS);
xlSTWriteToMatrix(CurIndex,10,TabDogovor._Ostatok);
CurIndex:=CurIndex+1;

Sum3:=Sum3+Round(TabDogovor._SaldoDog,2);
Sum4:=Sum4+Round(TabDogovor._SumPlPorDog,2);
Sum5:=Sum5+Round(TabDogovor._SumPlPorDogNDS,2);
Sum8:=Sum8+Round(TabDogovor._SumPlPorVozvr,2);
Sum9:=Sum9+Round(TabDogovor._SumPlPorVozvrNDS,2);
Sum10:=Sum10+Round(TabDogovor._Ostatok,2);

SumAll3:=SumAll3+Round(TabDogovor._SaldoDog,2);
SumAll4:=SumAll4+Round(TabDogovor._SumPlPorDog,2);
SumAll5:=SumAll5+Round(TabDogovor._SumPlPorDogNDS,2);
SumAll8:=SumAll8+Round(TabDogovor._SumPlPorVozvr,2);
SumAll9:=SumAll9+Round(TabDogovor._SumPlPorVozvrNDS,2);
SumAll10:=SumAll10+Round(TabDogovor._Ostatok,2);

_loop TabAkt{
xlAlignCellsEx(3,2,CurIndex,1,CurIndex,12);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSetNumberFormat ( '# ##0,00',CurIndex,6,CurIndex,7);
xlSTWriteToMatrix(CurIndex,6,TabAkt._SumPlPorAkt);
xlSTWriteToMatrix(CurIndex,7,TabAkt._SumPlPorAktNDS);
xlSetNumberFormat ( '@',CurIndex,11,CurIndex,12);
xlSTWriteToMatrix(CurIndex,11,TabAkt._NomD);
xlSTWriteToMatrix(CurIndex,12,TabAkt._NomDPPList);
CurIndex:=CurIndex+1;

Sum6:=Sum6+Round(TabAkt._SumPlPorAkt,2);
Sum7:=Sum7+Round(TabAkt._SumPlPorAktNDS,2);

SumAll6:=SumAll6+Round(TabAkt._SumPlPorAkt,2);
SumAll7:=SumAll7+Round(TabAkt._SumPlPorAktNDS,2);
}
}
}
xlSetFontStyle(1,CurIndex,1,CurIndex,12);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSTWriteToMatrix(CurIndex,1,'Итого по центру ответственности: '+TabPick._NamePick);
xlSetNumberFormat ( '# ##0,00',CurIndex,3,CurIndex,10);
xlSTWriteToMatrix(CurIndex,3,Sum3);
xlSTWriteToMatrix(CurIndex,4,Sum4);
xlSTWriteToMatrix(CurIndex,5,Sum5);
xlSTWriteToMatrix(CurIndex,6,Sum6);
xlSTWriteToMatrix(CurIndex,7,Sum7);
xlSTWriteToMatrix(CurIndex,8,Sum8);
xlSTWriteToMatrix(CurIndex,9,Sum9);
xlSTWriteToMatrix(CurIndex,10,Sum10);
CurIndex:=CurIndex+1;
}
xlSetFontStyle(1,CurIndex,1,CurIndex,12);
xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,CurIndex,1,CurIndex,12);
xlSTWriteToMatrix(CurIndex,1,'Итого по договорам');
xlSetNumberFormat ( '# ##0,00',CurIndex,3,CurIndex,10);
xlSTWriteToMatrix(CurIndex,3,SumAll3);
xlSTWriteToMatrix(CurIndex,4,SumAll4);
xlSTWriteToMatrix(CurIndex,5,SumAll5);
xlSTWriteToMatrix(CurIndex,6,SumAll6);
xlSTWriteToMatrix(CurIndex,7,SumAll7);
xlSTWriteToMatrix(CurIndex,8,SumAll8);
xlSTWriteToMatrix(CurIndex,9,SumAll9);
xlSTWriteToMatrix(CurIndex,10,SumAll10);

xlFrameCells(1 or 2 or 4 or 8 or 16 or 32,2,0,0,5,1,CurIndex,12);

CurIndex:=CurIndex+2;
xlAlignCellsEx(3,3,CurIndex,2,CurIndex,4);
xlSTWriteToMatrix(CurIndex,2,'_________________');
xlSTWriteToMatrix(CurIndex,3,'______________');
xlSTWriteToMatrix(CurIndex,4,'_________');
CurIndex:=CurIndex+1;
xlAlignCellsEx(3,1,CurIndex,2,CurIndex,4);
xlSTWriteToMatrix(CurIndex,2,'(должность)');
xlSTWriteToMatrix(CurIndex,3,'(подпись)');
xlSTWriteToMatrix(CurIndex,4,'(ФИО)');
CurIndex:=CurIndex+1;


xlWrapText(1,1,CurIndex,12);
xlAlignCellsEx(3,2,3,3,CurIndex,12);

xlWriteMatrixToExcel(1,1);
xlFreeMatrix;

xlKillExcel;

abort;
}
end;
end.