zjmb.~pas

delphi+excel报表管理程序

unit zjmb;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls, ComCtrls,ComObj, ExtCtrls;

type
  Tfrmzjmb = class(TForm)
    PageControl1: TPageControl;
    TabSheet1: TTabSheet;
    TabSheet2: TTabSheet;
    Label1: TLabel;
    Label2: TLabel;
    Edit1: TEdit;
    Edit2: TEdit;
    Button1: TButton;
    Panel1: TPanel;
    Button2: TButton;
    ComboBox1: TComboBox;
    Label3: TLabel;
    Panel2: TPanel;
    Label4: TLabel;
    CheckBox1: TCheckBox;
    Button3: TButton;
    Label5: TLabel;
    Label6: TLabel;
    ComboBox2: TComboBox;
    Panel3: TPanel;
    Label7: TLabel;
    Label8: TLabel;
    Label9: TLabel;
    procedure Button1Click(Sender: TObject);
    procedure TabSheet2Show(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure ComboBox2Select(Sender: TObject);
    procedure Button3Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  frmzjmb: Tfrmzjmb;

implementation

uses main;

{$R *.dfm}

procedure Tfrmzjmb.Button1Click(Sender: TObject);
var
mblj:string;
begin
if trim(edit1.Text)='' then
begin
  application.MessageBox('模板名称不能为空，请输入！','系统提示');
  edit1.SetFocus;
  exit;
end;
try
//CopyFile(pchar(fzbb1),pchar(fzbb2),false);
mblj:='C:\report\报表\模板\'+trim(edit1.Text)+'.xls';
copyfile('C:\report\报表\模板\blank.xls',pchar(mblj),false);
except;
application.MessageBox('模板创建失败，退出系统重新登陆！','系统提示');
exit;
end;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.Open;
frmmain.ADOQuery1.Insert;
frmmain.ADOQuery1.FieldByName('id').AsString:=inttostr(frmmain.ADOQuery1.RecordCount+1);
frmmain.ADOQuery1.FieldByName('tablename').AsString:=trim(edit1.Text)+'.xls';
frmmain.ADOQuery1.FieldByName('flag').AsString:='1';
frmmain.ADOQuery1.FieldByName('biaoji').AsString:='0';
frmmain.ADOQuery1.FieldByName('mbdg').AsString:='0';
frmmain.ADOQuery1.FieldByName('beizhu').AsString:=trim(edit2.Text);
frmmain.adoquery1.ExecSQL;
frmmain.adoquery1.UpdateBatch;
application.MessageBox('模板创建成功!','系统提示');
end;

procedure Tfrmzjmb.TabSheet2Show(Sender: TObject);
var
j:integer;
i:integer;
begin
//更新报表类型列表
try
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.Open;
i:=frmmain.ADOQuery1.RecordCount;
ComboBox1.Clear;
for j := 1 to i do
begin
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.SQL.Add('where id=:a1');
frmmain.adoquery1.Parameters.ParamValues['a1']:=inttostr(j);
frmmain.ADOQuery1.Open;
if (frmmain.ADOQuery1.Fields.Fields[2].AsString='1') then
ComboBox1.Items.Add(frmmain.ADOQuery1.Fields.Fields[1].AsString);
end;
except;
end;
//更新报表类型列表结束
end;

procedure Tfrmzjmb.Button2Click(Sender: TObject);
var
ExcelApp: Variant;
bjmb:string;//定义复制报表变量 （目标文件）
begin
//判断报表类型列表的内容是否为空
if ComboBox1.Text='' then
begin
  application.MessageBox('请选择要编辑的模板','系统提示');
  exit;
end
else
begin
//判断报表类型列表结束
//打开选定的模板
bjmb:='C:\report\报表\模板\'+trim(ComboBox1.Text);
try
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.Visible := True;
ExcelApp.Caption := '编辑模板 Microsoft Excel';
ExcelApp.WorkBooks.Open(bjmb);
except;
begin
application.MessageBox('打开模板失败！','系统提示');
exit;
end;
end;
//ExcelApp.ActiveSheet.PrintPreview;
end;
end;

procedure Tfrmzjmb.FormShow(Sender: TObject);
var
j:integer;
i:integer;
begin
//更新报表类型列表
try
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.Open;
i:=frmmain.ADOQuery1.RecordCount;
ComboBox1.Clear;
ComboBox2.Clear;
for j := 1 to i do
begin
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.SQL.Add('where id=:a1');
frmmain.adoquery1.Parameters.ParamValues['a1']:=inttostr(j);
frmmain.ADOQuery1.Open;
if (frmmain.ADOQuery1.Fields.Fields[2].AsString='1') then
if (frmmain.ADOQuery1.Fields.Fields[4].AsString='0') then
ComboBox1.Items.Add(frmmain.ADOQuery1.Fields.Fields[1].AsString);
ComboBox2.Items.Add(frmmain.ADOQuery1.Fields.Fields[1].AsString);
end;
except;
end;
//更新报表类型列表结束
end;

procedure Tfrmzjmb.ComboBox2Select(Sender: TObject);
begin
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.SQL.Add('where tablename=:a1');
frmmain.adoquery1.Parameters.ParamValues['a1']:=trim(ComboBox2.Text);
frmmain.ADOQuery1.Open;
if (frmmain.ADOQuery1.Fields.Fields[3].AsString='1') then
  begin
  CheckBox1.Checked:=true;
  checkbox1.Enabled:=false;
  button3.Enabled:=false;
  label6.Visible:=true;
  end;
if (frmmain.ADOQuery1.Fields.Fields[3].AsString='0') then
  begin
  CheckBox1.Checked:=false;
  checkbox1.Enabled:=true;
  button3.Enabled:=true;
  label6.Visible:=false;
  end;
end;

procedure Tfrmzjmb.Button3Click(Sender: TObject);
var
ExcelApp: Variant;
bj:string;
begin
bj:='C:\report\报表\模板\'+trim(ComboBox2.Text);
try
//更新数据库的标记信息
frmmain.ADOQuery1.Close;
frmmain.ADOQuery1.SQL.Clear;
frmmain.ADOQuery1.SQL.Add('select * from one');
frmmain.ADOQuery1.SQL.Add('where tablename=:a1');
frmmain.adoquery1.Parameters.ParamValues['a1']:=trim(ComboBox2.Text);
frmmain.ADOQuery1.Open;
frmmain.ADOQuery1.Edit;
if checkbox1.Checked=true then
  begin
  frmmain.adoquery1.FieldByName('biaoji').AsString:=trim('1');
  frmmain.ADOQuery1.Post;
  //application.MessageBox('标记成功！','系统提示');
  end
else
  begin
  application.MessageBox('请选中标记！','系统提示');
  exit;
  end;
//结束
//为模板作标记
ExcelApp := CreateOleObject( 'Excel.Application' );
ExcelApp.Visible := True;
ExcelApp.Caption := '打印报表';
ExcelApp.WorkBooks.Open(bj);
ExcelApp.ActiveSheet.pictures.insert('D:\报表录入打印\template01.bmp').select;
//保存模板
ExcelApp.workbooks[1].save;
ExcelApp.workbooks[1].close;
ExcelApp.quit;
ExcelApp:=unassigned;
//作标记结束
//更改按钮等的状态
label6.Visible:=true;
checkbox1.Enabled:=false;
button3.Enabled:=false;
application.MessageBox('标记成功！','系统提示');
//结束
except
application.MessageBox('标记失败!','系统提示');
end;
end;

end.