Курсовые работы / ПРИС П _2
.pdfend.
Программный код модуля «Регистрации пользователя»:
unit RegForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, StdCtrls, Buttons, ExtCtrls;
type
TRegistration = class(TForm) Panel1: TPanel;
Label1: TLabel;
Panel2: TPanel;
Label2: TLabel;
Panel3: TPanel;
BitBtn1: TBitBtn;
BitBtn2: TBitBtn; Edit2: TEdit; Edit1: TEdit;
procedure BitBtn2Click(Sender: TObject); procedure FormShow(Sender: TObject); procedure BitBtn1Click(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
var
Registration: TRegistration; pwd:String; posetitel:string;
implementation
{$R *.dfm}
uses Data_Modul;
procedure TRegistration.BitBtn2Click(Sender: TObject); begin
Registration.Close;
end;
procedure TRegistration.FormShow(Sender: TObject); begin
DM.USQuery1.SQL.Clear; DM.USQuery1.SQL.Add('SELECT Users.*FROM Users;'); DM.USQuery1.Open;
end;
procedure TRegistration.BitBtn1Click(Sender: TObject); var
ogPath:PChar; buttonSelected : Integer;
begin
if (Edit1.Text='') or (Edit2.Text='') then begin
if Edit1.Text='' then begin
MessageDlg('Введитеимяпользователя!',mtError, [mbOK], 0); Edit1.SetFocus;
end;
if Edit2.Text='' then begin
MessageDlg('Введитепароль!',mtError, [mbOK], 0); Edit2.SetFocus;
end; end
else begin
if DM.USQuery1.Locate('U_Name',Edit1.Text,[]) then begin
MessageDlg('Такой пользователь уже существует!',mtError, [mbOK], 0);
Edit1.Text:='';
61
Edit2.Text:='';
Edit1.SetFocus; end
else begin
DM.USQuery1.Insert; DM.USQuery1.FieldByName('U_Name').AsString:=Edit1.Text; DM.USQuery1.FieldByName('U_Password').AsString:=Edit2.Text;
DM.USQuery1.Post;
MessageDlg('Вы создали нового пользователя '+Edit1.Text+'! Перезайти?',mtInformation, mbOKCancel, 0);
if buttonSelected = mrOK then begin
ogPath:=PChar(Application.ExeName); WinExec(ogPath,SW_SHOW); Application.Terminate;
end;
if buttonSelected = mrCancel then begin
Registration.Close;
end;
end;
end;
end;
end.
Программный код модуля «Индивидуальный отчет по доходам»:
unit IndividDoxod;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, ADODB, Menus, StdCtrls, ExtCtrls, Grids, DBGrids, Excel97, ComObj, ComCtrls, DBCtrls;
type
TInDoxod = class(TForm) Panel1: TPanel; Label3: TLabel; Label4: TLabel; Label5: TLabel; DBGrid1: TDBGrid;
RadioGroup1: TRadioGroup;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton; DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; GroupBox2: TGroupBox;
Label2: TLabel; ComboBox1: TComboBox; Edit1: TEdit; PopupMenu1: TPopupMenu; Excel1: TMenuItem; DataSource1: TDataSource; SaveDialog1: TSaveDialog; InDoxodQuery1: TADOQuery; Button1: TButton; Label1: TLabel;
DBNavigator1: TDBNavigator; Button2: TButton; GroupBox1: TGroupBox; GroupBox3: TGroupBox; Edit2: TEdit;
Edit3: TEdit;
Edit4: TEdit;
Edit5: TEdit;
Edit6: TEdit;
Label6: TLabel;
Label7: TLabel;
Label8: TLabel;
Label9: TLabel;
Label10: TLabel;
procedure DBGrid1TitleClick(Column: TColumn); procedure Edit1Change(Sender: TObject); procedure FormCreate(Sender: TObject);
procedure DateTimePicker2Change(Sender: TObject);
62
procedure Button1Click(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure Excel1Click(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
const
SQL='SELECT Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data' +' FROM Family INNER JOIN (Doxod INNER JOIN Now_Doxod ON Doxod.D_ID =
Now_Doxod.ND_Doxod) ON Family.F_ID = Now_Doxod.ND_FIO'
+' GROUP BY Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data';
var
InDoxod: TInDoxod; s:string; period1,period2:string;
Procedure SendToExcel(ShFile: String; Querys: TADOQuery; d1: String; d2: String);
implementation
uses Data_Modul;
Procedure SendToExcel(ShFile: String; Querys: TADOQuery; d1: String; d2: String); Var ExcelApp, Workbook, Range, Cell1, Cell2, Cell3, ArrayData: Variant;
RowCOunt, ColCOunt, BeginCol, BeginRow, j: integer; Begin
BeginCol:=1;
BeginRow:=5;
RowCOunt:=Querys.RecordCount+1;
ColCOunt:=Querys.FieldDefs.Count;
ExcelApp:=CreateOleObject('Excel.Application');
ExcelApp.Application.EnableEvents:=false;
Workbook:=ExcelApp.WorkBooks.Add;
Workbook.SaveAs(ShFile);
ArrayData:=VarArrayCreate([1,RowCOunt,1,ColCOunt],varVariant);
Querys.DisableControls;
Querys.First;
For j:=1 to Querys.FieldDefs.Count do ArrayData[1,j]:=InDoxod.DBGrid1.Columns.Items[j-1].Title.Caption;
While Not Querys.Eof do Begin
For j:=1 to Querys.FieldDefs.Count do Begin
ArrayData[Querys.RecNo+1, j]:=Querys.FieldbyName(Querys.FieldDefs.Items[j- 1].DisplayName).value;
End;
Querys.Next;
End;
Querys.EnableControls;
Workbook.WorkSheets[1].Columns[1].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[2].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[3].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[4].ColumnWidth:=20;
Cell1:=Workbook.WorkSheets[1].Cells[BeginRow,BeginCol]; Cell2:=Workbook.WorkSheets[1].Cells[BeginRow+RowCOunt-1,BeginCol+ColCOunt-1]; Cell3:=Workbook.WorkSheets[1].Cells.Range[Cell1,Cell2]; Range:=Workbook.WorkSheets[1].Range[Cell1,Cell2]; ExcelApp.Range['A5:L5'].Font.Bold := True;
Range.Value:=ArrayData;
Range.Borders.LineStyle:=1; Range.HorizontalAlignment := 3;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, 4]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1,
4]]:='ИНДИВИДУАЛЬНЫЙОТЧЕТПОДОХОДАМ'; ExcelApp.Selection.HorizontalAlignment := 3;
ExcelApp.Range[ExcelApp.Cells[3, 1], ExcelApp.Cells[3, 4]].Select; ExcelApp.Selection.MergeCells:=True;
ExcelApp.Range[ExcelApp.Cells[3, 1], ExcelApp.Cells[3, 4]]:='МАКСИМАЛЬНЫЙДОХОД'; ExcelApp.Selection.HorizontalAlignment := 3;
ExcelApp.Range['A2']:='C';
ExcelApp.Range['B2']:=d1;
ExcelApp.Range['C2']:='По';
ExcelApp.Range['D2']:=d2;
ExcelApp.Range['A4']:=InDoxod.Edit3.Text;
ExcelApp.Range['B4']:=InDoxod.Edit4.Text;
ExcelApp.Range['C4']:=InDoxod.Edit5.Text;
63
ExcelApp.Range['D4']:=InDoxod.Edit6.Text;
Range:=Workbook.WorkSheets[1].Range[Cell1,Cell3];
ExcelApp.Visible:=True;
End;
{$R *.dfm}
procedure TInDoxod.DBGrid1TitleClick(Column: TColumn); var
i:integer;
clr:TColor; begin
s:=' ASC';
clr:= clMoneyGreen;
if RadioButton2.Checked then begin
s:=' DESC'; clr:= clSkyBlue;
end;
for i:=0 to DBGrid1.Columns.Count-1 do DBGrid1.Columns.Items[i].Title.Color:= clBtnFace; DBGrid1.Columns.Items[Column.Index].Title.Color:=clr; InDoxodQuery1.Sort:= DBGrid1.Columns.Items[Column.Index].FieldName+s;
end;
procedure TInDoxod.Edit1Change(Sender: TObject); var
s :string; begin
if ComboBox1.ItemIndex=-1 then ComboBox1.ItemIndex:=0; S:=DBGrid1.Columns.Items[ComboBox1.ItemIndex].FieldName; period1:=Datetostr(DateTimePicker1.Date); period2:=Datetostr(DateTimePicker2.Date); InDoxodQuery1.SQL.Clear;
InDoxodQuery1.SQL.Add('SELECT Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data'
+' FROM Family INNER JOIN (Doxod INNER JOIN Now_Doxod ON Doxod.D_ID = Now_Doxod.ND_Doxod) ON Family.F_ID = Now_Doxod.ND_FIO'
+' WHERE '+s+' LIKE "'+Edit1.text+'%" AND(Now_Doxod.ND_Data Between :period1 And
:period2)'
+' GROUP BY Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data;'); InDoxodQuery1.Parameters.ParamByName('period1').Value:=period1; InDoxodQuery1.Parameters.ParamByName('period2').Value:= period2; InDoxodQuery1.Close;
InDoxodQuery1.Open;
Button1.Click;
end;
procedure TInDoxod.FormCreate(Sender: TObject); begin
InDoxodQuery1.SQL.Clear;
InDoxodQuery1.SQL.Add(SQL+';');
InDoxodQuery1.Open;
ComboBox1.Items.Add(DBGrid1.Columns.Items[0].Title.Caption);
ComboBox1.Items.Add(DBGrid1.Columns.Items[1].Title.Caption);
DateTimePicker1.Date:=StrToDate('01.01.2000');
DateTimePicker2.Date:=Now;
Button1.Click;
end;
procedure TInDoxod.DateTimePicker2Change(Sender: TObject); begin
If DateTimePicker2.DateTime < DateTimePicker1.DateTime then begin
showmessage('Начальный период не может быть больше конечного!');
DateTimePicker2.Date:=DateTimePicker1.Date; end
else begin
period1:=Datetostr(DateTimePicker1.Date);
period2:=Datetostr(DateTimePicker2.Date);
InDoxodQuery1.SQL.Clear;
InDoxodQuery1.SQL.Add('SELECT Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data'
+' FROM Family INNER JOIN (Doxod INNER JOIN Now_Doxod ON Doxod.D_ID = Now_Doxod.ND_Doxod) ON Family.F_ID = Now_Doxod.ND_FIO'
+' WHERE (Now_Doxod.ND_Data Between :period1 And :period2)'
+' GROUP BY Family.F_Name, Doxod.D_Doxod, Now_Doxod.ND_Sum, Now_Doxod.ND_Data;'); InDoxodQuery1.Parameters.ParamByName('period1').Value:=period1;
64
InDoxodQuery1.Parameters.ParamByName('period2').Value:= period2; InDoxodQuery1.Open;
Button1.Click;
end;
end;
procedure TInDoxod.Button1Click(Sender: TObject); var
s,t:real;
f,d,u:string; begin
s:=0;
t:=0;
DBGrid1.DataSource.DataSet.First;
while not DBGrid1.DataSource.DataSet.Eof do begin
s:=s+DBGrid1.DataSource.DataSet.FieldByName('ND_Sum').Value;
if t<DBGrid1.DataSource.DataSet.FieldByName('ND_Sum').Value then begin t:=DBGrid1.DataSource.DataSet.FieldByName('ND_Sum').Value; f:=DBGrid1.DataSource.DataSet.FieldByName('F_Name').Value; d:=DBGrid1.DataSource.DataSet.FieldByName('D_Doxod').Value; u:=DBGrid1.DataSource.DataSet.FieldByName('ND_Data').Value; end;
DBGrid1.DataSource.DataSet.Next;
end;
Edit2.Text:=FloatToStr(S)+' РУБ.'; Edit3.Text:=f;
Edit4.Text:=d; Edit6.Text:=FloatToStr(t)+' РУБ.'; Edit5.Text:=u;
end;
procedure TInDoxod.FormClose(Sender: TObject; var Action: TCloseAction); begin
InDoxodQuery1.Close;
end;
procedure TInDoxod.Excel1Click(Sender: TObject); begin
period1:=Datetostr(DateTimePicker1.Date);
period2:=Datetostr(DateTimePicker2.Date); if SaveDialog1.Execute then
SendToExcel(SaveDialog1.FileName,InDoxodQuery1,period1,period2);
end;
end.
Программный код модуля «Индивидуальный отчет по расходам»:
unit IndividRasxod;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, ADODB, Menus, StdCtrls, ExtCtrls, Grids, DBGrids, Excel97, ComObj, ComCtrls, DBCtrls;
type
TInRasxod = class(TForm) Panel1: TPanel; Label3: TLabel; Label4: TLabel; Label5: TLabel; DBGrid1: TDBGrid;
RadioGroup1: TRadioGroup;
RadioButton1: TRadioButton;
RadioButton2: TRadioButton; DateTimePicker1: TDateTimePicker; DateTimePicker2: TDateTimePicker; Button1: TButton;
GroupBox2: TGroupBox; Label2: TLabel; Label1: TLabel; ComboBox1: TComboBox; Edit1: TEdit; PopupMenu1: TPopupMenu; Excel1: TMenuItem;
65
DataSource1: TDataSource;
SaveDialog1: TSaveDialog;
InRasxodQuery1: TADOQuery;
DBNavigator1: TDBNavigator; GroupBox1: TGroupBox; Label7: TLabel;
Label8: TLabel;
Label9: TLabel;
Label10: TLabel; Edit3: TEdit; Edit4: TEdit; Edit5: TEdit; Edit6: TEdit; Button2: TButton;
GroupBox3: TGroupBox; Label6: TLabel; Edit2: TEdit;
procedure Button1Click(Sender: TObject); procedure DateTimePicker2Change(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure FormCreate(Sender: TObject);
procedure Edit1Change(Sender: TObject); procedure DBGrid1TitleClick(Column: TColumn); procedure Excel1Click(Sender: TObject);
private
{Private declarations } public
{Public declarations } end;
const
SQL='SELECT Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data' +' FROM Family INNER JOIN (Rasxod INNER JOIN Now_Rasxod ON Rasxod.R_ID =
Now_Rasxod.NR_Rasxod) ON Family.F_ID = Now_Rasxod.NR_FIO'
+' GROUP BY Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data';
var
InRasxod: TInRasxod; s:string; period1,period2:string;
Procedure SendToExcel(ShFile: String; Querys: TADOQuery; d1: String; d2: String); implementation
uses Data_modul;
Procedure SendToExcel(ShFile: String; Querys: TADOQuery; d1: String; d2: String); Var ExcelApp, Workbook, Range, Cell1, Cell2, Cell3, ArrayData: Variant;
RowCOunt, ColCOunt, BeginCol, BeginRow, j: integer; Begin
BeginCol:=1;
BeginRow:=5;
RowCOunt:=Querys.RecordCount+1;
ColCOunt:=Querys.FieldDefs.Count;
ExcelApp:=CreateOleObject('Excel.Application');
ExcelApp.Application.EnableEvents:=false;
Workbook:=ExcelApp.WorkBooks.Add;
Workbook.SaveAs(ShFile);
ArrayData:=VarArrayCreate([1,RowCOunt,1,ColCOunt],varVariant);
Querys.DisableControls;
Querys.First;
For j:=1 to Querys.FieldDefs.Count do ArrayData[1,j]:=InRasxod.DBGrid1.Columns.Items[j-1].Title.Caption;
While Not Querys.Eof do Begin
For j:=1 to Querys.FieldDefs.Count do Begin
ArrayData[Querys.RecNo+1, j]:=Querys.FieldbyName(Querys.FieldDefs.Items[j- 1].DisplayName).value;
End;
Querys.Next;
End;
Querys.EnableControls;
Workbook.WorkSheets[1].Columns[1].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[2].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[3].ColumnWidth:=20;
Workbook.WorkSheets[1].Columns[4].ColumnWidth:=20;
Cell1:=Workbook.WorkSheets[1].Cells[BeginRow,BeginCol]; Cell2:=Workbook.WorkSheets[1].Cells[BeginRow+RowCOunt-1,BeginCol+ColCOunt-1]; Cell3:=Workbook.WorkSheets[1].Cells.Range[Cell1,Cell2]; Range:=Workbook.WorkSheets[1].Range[Cell1,Cell2];
66
ExcelApp.Range['A5:L5'].Font.Bold := True; Range.Value:=ArrayData; Range.Borders.LineStyle:=1; Range.HorizontalAlignment := 3;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, 4]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1,
4]]:='ИНДИВИДУАЛЬНЫЙОТЧЕТПОРАСХОДАМ'; ExcelApp.Selection.HorizontalAlignment := 3; ExcelApp.Range[ExcelApp.Cells[3, 1], ExcelApp.Cells[3, 4]].Select; ExcelApp.Selection.MergeCells:=True;
ExcelApp.Range[ExcelApp.Cells[3, 1], ExcelApp.Cells[3, 4]]:='МАКСИМАЛЬНЫЙРАСХОД'; ExcelApp.Selection.HorizontalAlignment := 3;
ExcelApp.Range['A2']:='C';
ExcelApp.Range['B2']:=d1;
ExcelApp.Range['C2']:='По';
ExcelApp.Range['D2']:=d2;
ExcelApp.Range['A4']:=InRasxod.Edit3.Text;
ExcelApp.Range['B4']:=InRasxod.Edit4.Text;
ExcelApp.Range['C4']:=InRasxod.Edit5.Text;
ExcelApp.Range['D4']:=InRasxod.Edit6.Text;
Range:=Workbook.WorkSheets[1].Range[Cell1,Cell3];
ExcelApp.Visible:=True;
End;
{$R *.dfm}
procedure TInRasxod.Button1Click(Sender: TObject); var
s,t:real;
f,d,u:string; begin
s:=0;
t:=0;
DBGrid1.DataSource.DataSet.First;
while not DBGrid1.DataSource.DataSet.Eof do begin
s:=s+DBGrid1.DataSource.DataSet.FieldByName('NR_Sum').Value;
if t<DBGrid1.DataSource.DataSet.FieldByName('NR_Sum').Value then begin t:=DBGrid1.DataSource.DataSet.FieldByName('NR_Sum').Value; f:=DBGrid1.DataSource.DataSet.FieldByName('F_Name').Value; d:=DBGrid1.DataSource.DataSet.FieldByName('R_Rasxod').Value; u:=DBGrid1.DataSource.DataSet.FieldByName('NR_Data').Value; end;
DBGrid1.DataSource.DataSet.Next;
end;
Edit2.Text:=FloatToStr(S)+' РУБ.'; Edit3.Text:=f;
Edit4.Text:=d; Edit6.Text:=FloatToStr(t)+' РУБ.'; Edit5.Text:=u;
end;
procedure TInRasxod.FormClose(Sender: TObject; var Action: TCloseAction); begin
InRasxodQuery1.Close;
end;
procedure TInRasxod.DateTimePicker2Change(Sender: TObject); begin
If DateTimePicker2.DateTime < DateTimePicker1.DateTime then showmessage('Начальный период не может быть больше конечного!') else
begin period1:=Datetostr(DateTimePicker1.Date); period2:=Datetostr(DateTimePicker2.Date); InRasxodQuery1.SQL.Clear;
InRasxodQuery1.SQL.Add('SELECT Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data'
+' FROM Family INNER JOIN (Rasxod INNER JOIN Now_Rasxod ON Rasxod.R_ID = Now_Rasxod.NR_Rasxod) ON Family.F_ID = Now_Rasxod.NR_FIO'
+' WHERE (Now_Rasxod.NR_Data Between :period1 And :period2)'
+' GROUP BY Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data;'); InRasxodQuery1.Parameters.ParamByName('period1').Value:=period1; InRasxodQuery1.Parameters.ParamByName('period2').Value:= period2; InRasxodQuery1.Open;
Button1.Click;
end;
67
end;
procedure TInRasxod.FormCreate(Sender: TObject); begin
InRasxodQuery1.SQL.Clear;
InRasxodQuery1.SQL.Add(SQL+';');
InRasxodQuery1.Open;
ComboBox1.Items.Add(DBGrid1.Columns.Items[0].Title.Caption);
ComboBox1.Items.Add(DBGrid1.Columns.Items[1].Title.Caption);
DateTimePicker1.Date:=StrToDate('01.01.2000');
DateTimePicker2.Date:=Now;
Button1.Click;
end;
procedure TInRasxod.Edit1Change(Sender: TObject); var
s :string; begin
if ComboBox1.ItemIndex=-1 then ComboBox1.ItemIndex:=0; S:=DBGrid1.Columns.Items[ComboBox1.ItemIndex].FieldName; period1:=Datetostr(DateTimePicker1.Date); period2:=Datetostr(DateTimePicker2.Date); InRasxodQuery1.SQL.Clear;
InRasxodQuery1.SQL.Add('SELECT Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data'
+' FROM Family INNER JOIN (Rasxod INNER JOIN Now_Rasxod ON Rasxod.R_ID = Now_Rasxod.NR_Rasxod) ON Family.F_ID = Now_Rasxod.NR_FIO'
+' WHERE '+s+' LIKE "'+Edit1.text+'%" AND (Now_Rasxod.NR_Data Between :period1 And :period2)'
+' GROUP BY Family.F_Name, Rasxod.R_Rasxod, Now_Rasxod.NR_Sum, Now_Rasxod.NR_Data;'); InRasxodQuery1.Parameters.ParamByName('period1').Value:=period1; InRasxodQuery1.Parameters.ParamByName('period2').Value:= period2; InRasxodQuery1.Close;
InRasxodQuery1.Open;
Button1.Click;
end;
procedure TInRasxod.DBGrid1TitleClick(Column: TColumn); var
i:integer;
clr:TColor; begin
s:=' ASC';
clr:= clMoneyGreen;
if RadioButton2.Checked then begin
s:=' DESC'; clr:= clSkyBlue;
end;
for i:=0 to DBGrid1.Columns.Count-1 do DBGrid1.Columns.Items[i].Title.Color:= clBtnFace; DBGrid1.Columns.Items[Column.Index].Title.Color:=clr; InRasxodQuery1.Sort:= DBGrid1.Columns.Items[Column.Index].FieldName+s;
end;
procedure TInRasxod.Excel1Click(Sender: TObject); begin
period1:=Datetostr(DateTimePicker1.Date);
period2:=Datetostr(DateTimePicker2.Date); if SaveDialog1.Execute then
SendToExcel(SaveDialog1.FileName,InRasxodQuery1,period1,period2);
end;
end.
Программный код модуля «Баланс семьи»:
unit BalancForm;
interface
uses
Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms, Dialogs, DB, ADODB, Menus, StdCtrls, ExtCtrls, Grids, DBGrids, Excel97, ComObj, ComCtrls, DBCtrls, TeEngine, Series, TeeProcs, Chart, DbChart;
type
TBalanc = class(TForm) Panel1: TPanel; Label3: TLabel; Label4: TLabel;
68
Label5: TLabel;
DBGrid1: TDBGrid; RadioGroup1: TRadioGroup; RadioButton1: TRadioButton; RadioButton2: TRadioButton;
DateTimePicker1: TDateTimePicker;
DateTimePicker2: TDateTimePicker; DBNavigator1: TDBNavigator; PopupMenu1: TPopupMenu;
Excel1: TMenuItem; DataSource1: TDataSource; SaveDialog1: TSaveDialog; DoxQuery: TADOQuery; Button1: TButton; GroupBox3: TGroupBox; Label6: TLabel; GroupBox4: TGroupBox; Label11: TLabel; Label12: TLabel; ComboBox1: TComboBox; Edit1: TEdit;
DBGrid2: TDBGrid; DBNavigator2: TDBNavigator; DataSource2: TDataSource; RasQuery: TADOQuery; Label1: TLabel;
Label2: TLabel;
Edit3: TEdit;
Edit2: TEdit;
Edit4: TEdit;
procedure FormCreate(Sender: TObject); procedure Button1Click(Sender: TObject); procedure DBGrid2TitleClick(Column: TColumn); procedure DBGrid1TitleClick(Column: TColumn); procedure Edit1Change(Sender: TObject);
procedure FormClose(Sender: TObject; var Action: TCloseAction); procedure DateTimePicker2Change(Sender: TObject);
procedure Excel1Click(Sender: TObject); private
{Private declarations } public
{Public declarations } end;
const
SQL1='SELECT Family.F_Name, Sum(Now_Doxod.ND_Sum) AS [Sum-ND_Sum], Now_Doxod.ND_Data' +' FROM Family INNER JOIN Now_Doxod ON Family.F_ID = Now_Doxod.ND_FIO'
+' GROUP BY Family.F_Name, Now_Doxod.ND_Data';
SQL2='SELECT Family.F_Name, Sum(Now_Rasxod.NR_Sum) AS [Sum-NR_Sum], Now_Rasxod.NR_Data' +' FROM Family INNER JOIN Now_Rasxod ON Family.F_ID = Now_Rasxod.NR_FIO'
+' GROUP BY Family.F_Name, Now_Rasxod.NR_Data';
var
Balanc: TBalanc; s:string; period1,period2:string;
Procedure SendToExcel(ShFile: String; Querys1: TADOQuery; Querys2: TADOQuery; d1: String; d2: String);
implementation
uses Data_Modul;
Procedure SendToExcel(ShFile: String; Querys1: TADOQuery; Querys2:TADOQuery; d1: String; d2: String);
Var ExcelApp, Workbook, Range, Cell1, Cell2, Cell3, ArrayData1, ArrayData2: Variant; RowCOunt, ColCOunt, BeginCol, BeginRow, j: integer;
Begin BeginCol:=1; BeginRow:=5;
RowCOunt:=Querys1.RecordCount+1;
ColCOunt:=Querys1.FieldDefs.Count;
ExcelApp:=CreateOleObject('Excel.Application');
ExcelApp.Application.EnableEvents:=false;
Workbook:=ExcelApp.WorkBooks.Add;
Workbook.SaveAs(ShFile);
ArrayData1:=VarArrayCreate([1,RowCOunt,1,ColCOunt],varVariant);
Querys1.DisableControls;
Querys1.First;
For j:=1 to Querys1.FieldDefs.Count do
69
ArrayData1[1,j]:=Balanc.DBGrid1.Columns.Items[j-1].Title.Caption; While Not Querys1.Eof do
Begin
For j:=1 to Querys1.FieldDefs.Count do Begin
ArrayData1[Querys1.RecNo+1, j]:=Querys1.FieldbyName(Querys1.FieldDefs.Items[j- 1].DisplayName).value;
End;
Querys1.Next;
End;
Querys1.EnableControls;
Cell1:=Workbook.WorkSheets[1].Cells[BeginRow,BeginCol]; Cell2:=Workbook.WorkSheets[1].Cells[BeginRow+RowCOunt-1,BeginCol+ColCOunt-1]; Cell3:=Workbook.WorkSheets[1].Cells.Range[Cell1,Cell2]; Range:=Workbook.WorkSheets[1].Range[Cell1,Cell2];
Range.Value:=ArrayData1;
Range.Borders.LineStyle:=1; Range.HorizontalAlignment := 3; BeginCol:=5;
BeginRow:=5;
RowCOunt:=Querys2.RecordCount+1;
ColCOunt:=Querys2.FieldDefs.Count;
ArrayData2:=VarArrayCreate([1,RowCOunt,1,ColCOunt],varVariant);
Querys2.DisableControls;
Querys2.First;
For j:=1 to Querys2.FieldDefs.Count do ArrayData2[1,j]:=Balanc.DBGrid2.Columns.Items[j-1].Title.Caption; While Not Querys2.Eof do
Begin
For j:=1 to Querys2.FieldDefs.Count do Begin
ArrayData2[Querys2.RecNo+1, j]:=Querys2.FieldbyName(Querys2.FieldDefs.Items[j- 1].DisplayName).value;
End;
Querys2.Next;
End;
Querys2.EnableControls;
Cell1:=Workbook.WorkSheets[1].Cells[BeginRow,BeginCol]; Cell2:=Workbook.WorkSheets[1].Cells[BeginRow+RowCOunt-1,BeginCol+ColCOunt-1]; Cell3:=Workbook.WorkSheets[1].Cells.Range[Cell1,Cell2]; Range:=Workbook.WorkSheets[1].Range[Cell1,Cell2]; Workbook.WorkSheets[1].Columns[1].ColumnWidth:=19; Workbook.WorkSheets[1].Columns[2].ColumnWidth:=15; Workbook.WorkSheets[1].Columns[3].ColumnWidth:=19; Workbook.WorkSheets[1].Columns[4].ColumnWidth:=15; Workbook.WorkSheets[1].Columns[5].ColumnWidth:=19; Workbook.WorkSheets[1].Columns[6].ColumnWidth:=15; Workbook.WorkSheets[1].Columns[7].ColumnWidth:=15; ExcelApp.Range['A5:L5'].Font.Bold := True;
Range.Value:=ArrayData2;
Range.Borders.LineStyle:=1; Range.HorizontalAlignment := 3;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, 7]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Range['A1:G1'].Font.Bold := True; Range.HorizontalAlignment := 3;
ExcelApp.Range[ExcelApp.Cells[1, 1], ExcelApp.Cells[1, 7]]:='БАЛАНССЕМЬИ'; ExcelApp.Selection.HorizontalAlignment := 3;
ExcelApp.Range['B2']:='C';
ExcelApp.Range['C2']:=d1;
ExcelApp.Range['D2']:='По';
ExcelApp.Range['E2']:=d2; ExcelApp.Range['A3'].Font.Bold := True; ExcelApp.Range['A3']:='ОБЩИЙДОХОД:'; ExcelApp.Range['B3']:=Balanc.Edit2.Text; ExcelApp.Range['C3'].Font.Bold := True;
ExcelApp.Range['C3']:='ОБЩИЙ РАСХОД:';
ExcelApp.Range['D3']:=Balanc.Edit3.Text; ExcelApp.Range['E3'].Font.Bold := True;
ExcelApp.Range['E3']:='ОСТАТОК НА СЧЕТЕ:';
ExcelApp.Range['F3']:=Balanc.Edit4.Text; ExcelApp.Range[ExcelApp.Cells[4, 1], ExcelApp.Cells[4, 3]].Select; ExcelApp.Selection.MergeCells:=True; ExcelApp.Range['A4'].Font.Bold := True;
ExcelApp.Range[ExcelApp.Cells[4, 1], ExcelApp.Cells[4, 3]]:='ДОХОДЫ'; ExcelApp.Range[ExcelApp.Cells[4, 5], ExcelApp.Cells[4, 7]].Select; ExcelApp.Selection.MergeCells:=True;
ExcelApp.Range['E4'].Font.Bold := True; ExcelApp.Range[ExcelApp.Cells[4, 5], ExcelApp.Cells[4, 7]]:='РАСХОДЫ';
70