unit1.pas

财务发票邮寄明细生成程序。针对本公司的实际情况编写。如果需要改改字段就行。

unit Unit1;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs, StdCtrls,ComObj,Excel2000, DB, Grids, DBGrids, ADODB;

type
  TForm1 = class(TForm)
    Button1: TButton;
    Button2: TButton;
    Edit1: TEdit;
    Button3: TButton;
    Label1: TLabel;
    Button4: TButton;
    Label2: TLabel;
    ADOConnection1: TADOConnection;
    DBGrid1: TDBGrid;
    DataSource1: TDataSource;
    OpenDialog1: TOpenDialog;
    ADOQuery1: TADOQuery;
    ComboBox1: TComboBox;
    procedure Button1Click(Sender: TObject);
    procedure Button2Click(Sender: TObject);
    procedure Button3Click(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure Button4Click(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
  end;

var
  Form1: TForm1;

implementation

{$R *.dfm}

procedure TForm1.Button1Click(Sender: TObject);
var
 s,sfoot,qy:string;
 i:integer;
 Excelid: Variant;
begin
  Case ComBobox1.ItemIndex of
  0: qy:='区域1';
  1: qy:='区域2' ;
  2: qy:='区域3';
  3: qy:='区域4';
  4: qy:='区域5';
  else
  qy:='没区域';
  end;
  try
    Excelid:=CreateOleObject( 'Excel.Application' );
  except
    on Exception do raise exception.Create('无法创建Xls文件，请确认是否安装EXCEL')
  end;
  Excelid.Visible := True;
  Excelid.WorkBooks.Add;
  Excelid.Caption :='增值税发票邮寄明细';
  Excelid.worksheets[1].range['A1:H1'].Merge(True);
  Excelid.ActiveSheet.Rows[1].RowHeight := 1/0.035; // 1厘米
  Excelid.WorkSheets[1].Cells[1,1].Value :='增值税发票邮寄明细' ;
  i:=2;
  Excelid.worksheets[1].range['A2:D2'].Merge(True);
  Excelid.worksheets[1].range['E2:H2'].Merge(True);
  Excelid.WorkSheets[1].Cells[i,1].Value :='区域：'+qy ;
  Excelid.WorkSheets[1].Cells[i,5].Value :='收件人：'+ComBobox1.Text ;
  i:=3;
  Excelid.worksheets[1].range['A3:D3'].Merge(True);
  Excelid.worksheets[1].range['E3:H3'].Merge(True);
  Excelid.WorkSheets[1].Cells[i,1].Value :='邮寄人：' ;
  Excelid.WorkSheets[1].Cells[i,5].Value :='邮寄日期：' ;
  i:=4;
  Excelid.worksheets[1].range['A4:D4'].Merge(True);
  Excelid.worksheets[1].range['E4:H4'].Merge(True);
  Excelid.WorkSheets[1].Cells[i,1].Value :='联系电话：' ;
  Excelid.WorkSheets[1].Cells[i,5].Value :='数量：' ;
  i:=5;
  Excelid.worksheets[1].range['A5:H5'].Merge(True);
  Excelid.WorkSheets[1].Cells[i,1].Value :='快递号：' ;

  Excelid.worksheets[1].range['A1:H7'].HorizontalAlignment := $FFFFEFF4;
  Excelid.worksheets[1].range['A1:H7'].VerticalAlignment := $FFFFEFF4;
  Excelid.worksheets[1].Columns[1].ColumnWidth := 5;
  Excelid.worksheets[1].Columns[2].ColumnWidth := 10;
  Excelid.worksheets[1].Columns[3].ColumnWidth := 10;
  Excelid.worksheets[1].Columns[4].ColumnWidth := 40;
  Excelid.worksheets[1].Columns[5].ColumnWidth := 10;
  Excelid.worksheets[1].Columns[6].ColumnWidth := 10;
  Excelid.worksheets[1].Columns[7].ColumnWidth := 10;
  Excelid.worksheets[1].Columns[8].ColumnWidth := 15;
  i:=6;
  Excelid.WorkSheets[1].Cells[i,1].Value := '序号';
  Excelid.WorkSheets[1].Cells[i,2].Value := '发票号';
  Excelid.WorkSheets[1].Cells[i,3].Value := '日期';
  Excelid.WorkSheets[1].Cells[i,4].Value := '客户名称';
  Excelid.WorkSheets[1].Cells[i,5].Value := '部门';
  Excelid.WorkSheets[1].Cells[i,6].Value := '数量';
  Excelid.WorkSheets[1].Cells[i,7].Value := '金额';
  Excelid.WorkSheets[1].Cells[i,8].Value := '签收单';
  Excelid.worksheets[1].Range['A1:H5'].Font.Name := '宋体';
  Excelid.worksheets[1].Range['A1:H5'].Font.Size := 14;
  Excelid.worksheets[1].Range['A1:H5'].Borders.LineStyle := 1;
  Excelid.worksheets[1].Range['A1:H5'].font.bold:=true;
  i:=7;
    while not Adoquery1.Eof do
    begin
      Excelid.WorkSheets[1].Cells[i,2].Value := Adoquery1.FieldByName('INVOICENO').AsString;
      Excelid.WorkSheets[1].Cells[i,3].Value := Adoquery1.FieldByName('INVOICETATE').AsString;
      Excelid.WorkSheets[1].Cells[i,4].Value := Adoquery1.FieldByName('DEALER').AsString;
      Excelid.WorkSheets[1].Cells[i,8].Value := Adoquery1.FieldByName('SIGNNO').AsString;
      Excelid.WorkSheets[1].Cells[i,1].Value := i-6;
      Inc(i);
      Adoquery1.Next;
      s:= 'A6:H'+ IntToStr(i+4);
    end;
  sfoot:='A'+IntToStr(i)+':H'+IntToStr(i+4);
  Excelid.worksheets[1].range[sfoot].Merge(FALSE);
  Excelid.WorkSheets[1].range[sfoot].Value := '商场盖章：                               签收人：                   日期：';
  Excelid.worksheets[1].range[s].HorizontalAlignment := $FFFFEFF4;
  Excelid.worksheets[1].range[s].VerticalAlignment := $FFFFEFF4;
  Excelid.worksheets[1].Range[s].Font.Name := '宋体';
  Excelid.worksheets[1].Range[s].Font.Size := 11;
  Excelid.worksheets[1].Range[s].Borders.LineStyle := 1;
end;

procedure TForm1.Button2Click(Sender: TObject);
begin
  OpenDialog1.Title := '请选择相应的Excel文件';
  OpenDialog1.Filter := 'Excel(*.xls)|*.xls';
  if OpenDialog1.Execute then
    edit1.Text := OpenDialog1.FileName;
end;

procedure TForm1.Button3Click(Sender: TObject);
var
  sSql, xlspath: string;
begin
  if trim(edit1.Text) = '' then
  begin
    MessageBox(GetActiveWindow(), '请正确选择相关路径！', '警告', MB_OK +
      MB_ICONWARNING);
    exit;
  end;
    xlspath := trim(edit1.Text);
    try
      adoquery1.Close;
      adoquery1.SQL.Clear;
      sSql :='INSERT INTO  INVOICE (DEALER,INVOICETATE,INVOICENO,SIGNTATE,SIGNNO,SIGNER,BOSIGNER)'
        +' SELECT 商场简称,发票日期,发票号码,签收日期,签收单号,签收人,非客户经理签收人'
        +' FROM [excel 8.0;database=' + xlspath + '].[Sheet1$]';
      adoquery1.Parameters.Clear;
      adoquery1.ParamCheck := false;
      adoquery1.SQL.Text := sSql;
      adoquery1.Execsql;
      MessageBox(GetActiveWindow(), '批量导入EXCEL成功!', '警告', MB_OK +
        MB_ICONWARNING);
    except
      MessageBox(GetActiveWindow(), '批量导入EXCEL失败!', '警告', MB_OK +
        MB_ICONWARNING);
    end;
  end;

procedure TForm1.FormCreate(Sender: TObject);
var
ConnStr:string;
begin
    ConnStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source='+GetCurrentDir
    +'\sodb.mdb' + ';Persist Security Info=False';
  ADOConnection1.Connected := False;
  try
    ADOConnection1.ConnectionString :=ConnStr;
    ADOConnection1.Connected := true;
      except
    MessageBox(GetActiveWindow(), '连接ACCESS失败!', '警告', MB_OK +
      MB_ICONWARNING);
    end;
end;

procedure TForm1.Button4Click(Sender: TObject);
begin
  if ComBobox1.Text ='三联' then
  begin
  ADOQuery1.SQL.Clear ;
  ADOQuery1.Close ;
  ADOQuery1.SQL.text := 'Select * From INVOICE WHERE DEALER LIKE "%'+ComBobox1.Text+'%"';
  ADOQuery1.Open;
  ADOQuery1.First;
  end
  else
  begin
  ADOQuery1.SQL.Clear ;
  ADOQuery1.Close ;
  ADOQuery1.SQL.Text :='select * from INVOICE where SIGNER ="'+ComBobox1.Text+'"';
  ADOQuery1.Open;
  ADOQuery1.First;
  end;
end;
end.