unit u_op_seae_excel;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  dxExEdtr, DB, dxTL, dxDBCtrl, dxDBGrid, dxCntner, StdCtrls, Mask,
  wwdbedit, Wwdotdot, Wwdbcomb, bsSkinCtrls, Dialogs, Excel, ADODB,
  BusinessSkinForm, DBCtrls;

type
  Tfrm_op_seae_excel = class(TForm)
    bsBusinessSkinForm1: TbsBusinessSkinForm;
    t_excel_bill: TADOQuery;
    t_excel_bill1: TDataSource;
    bsSkinPanel1: TbsSkinPanel;
    t_excel_bill_bill: TADOQuery;
    t_excel_bill_bill1: TDataSource;
    bsSkinPanel8: TbsSkinPanel;
    bsSkinButton10: TbsSkinButton;
    bsSkinButton11: TbsSkinButton;
    bsSkinButton12: TbsSkinButton;
    Excel1: TExcel;
    t_op_ctn: TADOQuery;
    bsSkinOpenDialog1: TOpenDialog;
    SaveDialog: TSaveDialog;
    bsSkinGroupBox2: TbsSkinGroupBox;
    bsSkinPanel6: TbsSkinPanel;
    bsSkinPanel7: TbsSkinPanel;
    bsSkinButton7: TbsSkinButton;
    bsSkinButton8: TbsSkinButton;
    bsSkinButton9: TbsSkinButton;
    t_excel_billEX_ID: TAutoIncField;
    t_excel_billDSDesigner: TStringField;
    t_excel_billDSDesigner2: TBlobField;
    dxDBGrid2: TdxDBGrid;
    dxDBGrid2Column1: TdxDBGridMaskColumn;
    dxDBGrid2X: TdxDBGridMaskColumn;
    dxDBGrid2Y: TdxDBGridMaskColumn;
    bsSkinPanel9: TbsSkinPanel;
    wwDBComboBox2: TwwDBComboBox;
    DBEdit1: TDBEdit;
    DBEdit2: TDBEdit;
    dxDBGrid2Column4: TdxDBGridColumn;
    DBEdit5: TDBEdit;
    bsSkinButton1: TbsSkinButton;
    bsSkinButton2: TbsSkinButton;
    bsSkinButton4: TbsSkinButton;
    bsSkinGroupBox3: TbsSkinGroupBox;
    bsSkinPanel2: TbsSkinPanel;
    dxDBGrid1: TdxDBGrid;
    dxDBGrid1Column1: TdxDBGridMaskColumn;
    Memo1: TMemo;
    bsSkinButton13: TbsSkinButton;
    procedure FormShow(Sender: TObject);
    procedure bsSkinButton1Click(Sender: TObject);
    procedure bsSkinButton2Click(Sender: TObject);
    procedure bsSkinButton4Click(Sender: TObject);
    procedure t_excel_billPostError(DataSet: TDataSet; E: EDatabaseError;
      var Action: TDataAction);
    procedure t_excel_bill_billPostError(DataSet: TDataSet;
      E: EDatabaseError; var Action: TDataAction);
    procedure t_excel_billBeforeDelete(DataSet: TDataSet);
    procedure bsSkinButton7Click(Sender: TObject);
    procedure bsSkinButton8Click(Sender: TObject);
    procedure bsSkinButton9Click(Sender: TObject);
    procedure t_excel_bill_ctnBeforeInsert(DataSet: TDataSet);
    procedure t_excel_bill_billBeforePost(DataSet: TDataSet);
    procedure t_excel_bill_billAfterInsert(DataSet: TDataSet);
    procedure bsSkinButton11Click(Sender: TObject);
    procedure bsSkinButton12Click(Sender: TObject);
    procedure bsSkinButton10Click(Sender: TObject);
    procedure bsSkinButton13Click(Sender: TObject);
  private
    { Private declarations }
  public
    tradetype:string;
    { Public declarations }
  end;

var
  frm_op_seae_excel: Tfrm_op_seae_excel;

implementation

uses u_main, my_sys_function, u_op_seae;

{$R *.dfm}

procedure Tfrm_op_seae_excel.FormShow(Sender: TObject);
var
  i:integer;
begin
  if tradetype='SEA' then begin
    wwDBComboBox2.Items.Clear;
    wwDBComboBox2.Items.Add('固定值');
    wwDBComboBox2.Items.Add('集装箱111');
    for i:=0 to frm_op_seae.t_op_seae2.DataSet.FieldCount-1 do begin
      wwDBComboBox2.Items.Add(frm_op_seae.t_op_seae2.DataSet.Fields[i].FieldName);
    end;
  end else if tradetype='AIR' then begin
{
    wwDBComboBox2.Items.Clear;
    wwDBComboBox2.Items.Add('固定值');
    for i:=0 to frm_op_aire.t_op_aire1.DataSet.FieldCount-1 do begin
      wwDBComboBox2.Items.Add(frm_op_aire.t_op_aire1.DataSet.Fields[i].FieldName);
    end;
    }
  end;

  t_excel_bill.open;
  t_excel_bill_bill.open;
end;

procedure Tfrm_op_seae_excel.bsSkinButton1Click(Sender: TObject);
begin
  t_excel_bill.insert;
end;

procedure Tfrm_op_seae_excel.bsSkinButton2Click(Sender: TObject);
begin
  table_post(t_excel_bill);
end;

procedure Tfrm_op_seae_excel.bsSkinButton4Click(Sender: TObject);
begin
  table_delete(t_excel_bill);
end;

procedure Tfrm_op_seae_excel.t_excel_billPostError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
  showmessage('格式名称重复');
end;

procedure Tfrm_op_seae_excel.t_excel_bill_billPostError(DataSet: TDataSet;
  E: EDatabaseError; var Action: TDataAction);
begin
  showmessage('字段名称重复');
end;

procedure Tfrm_op_seae_excel.t_excel_billBeforeDelete(DataSet: TDataSet);
begin
  table_before_delete(t_excel_bill_bill,'子表');
end;

procedure Tfrm_op_seae_excel.bsSkinButton7Click(Sender: TObject);
begin
  t_excel_bill_bill.insert;
end;

procedure Tfrm_op_seae_excel.bsSkinButton8Click(Sender: TObject);
begin
  table_post(t_excel_bill_bill);
end;

procedure Tfrm_op_seae_excel.bsSkinButton9Click(Sender: TObject);
begin
  table_delete(t_excel_bill_bill);
end;

procedure Tfrm_op_seae_excel.t_excel_bill_ctnBeforeInsert(
  DataSet: TDataSet);
begin
  table_before_insert(t_excel_bill);
end;

procedure Tfrm_op_seae_excel.t_excel_bill_billBeforePost(
  DataSet: TDataSet);
begin
  table_before_post(t_excel_bill_bill,'字段名称');
  table_before_post(t_excel_bill_bill,'坐标X');
  table_before_post(t_excel_bill_bill,'坐标Y');
end;

procedure Tfrm_op_seae_excel.t_excel_bill_billAfterInsert(
  DataSet: TDataSet);
begin
  t_excel_bill_bill['EX_ID']:=t_excel_bill['EX_ID'];
end;

procedure Tfrm_op_seae_excel.bsSkinButton11Click(Sender: TObject);
begin
  close;
end;

procedure Tfrm_op_seae_excel.bsSkinButton12Click(Sender: TObject);
begin
  if t_excel_bill.IsEmpty then
    exit;
  if (t_excel_bill.state=dsinsert)or(t_excel_bill.state=dsedit)then
    t_excel_bill.Post;
  if bsSkinOpenDialog1.Execute then
  begin
    t_excel_bill.EDIT;
    t_excel_billDSDesigner2.LoadFromFile(bsSkinOpenDialog1.FileName);
    t_excel_bill.post;
  end;
end;

procedure Tfrm_op_seae_excel.bsSkinButton10Click(Sender: TObject);
var
  str:Tstringlist;
  wide_str:widestring;
  i:integer;
  Y:INTEGER;
begin
  if tradetype='SEA' then begin
  if t_excel_bill.IsEmpty then
    exit;
  t_excel_billDSDesigner2.SaveToFile(ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls');

  str:=Tstringlist.Create;
  str.Clear;

  excel1.Connect;
  Excel1.Exec('[open('+'"'+ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls'+'"'+')]');
  t_excel_bill_bill.First;
  while not t_excel_bill_bill.eof do
  begin
    if t_excel_bill_bill.fieldbyname('字段名称').asstring<>'集装箱111'then
    begin
      if trim(t_excel_bill_bill.fieldbyname('字段名称').asstring)<>'固定值'then
      begin
        Memo1.Text:=frm_op_seae.t_op_seae2.DataSet.fieldbyname(t_excel_bill_bill.fieldbyname('字段名称').asstring).asstring;
        for i:=0 to Memo1.Lines.Count-1 do
        begin
          excel1.putstr(t_excel_bill_bill.fieldbyname('坐标Y').asinteger+i,
                      t_excel_bill_bill.fieldbyname('坐标X').asinteger,
                      Memo1.Lines[i]);
        end;
      end
      else
      begin
        excel1.putstr(t_excel_bill_bill.fieldbyname('坐标Y').asinteger,
                      t_excel_bill_bill.fieldbyname('坐标X').asinteger,
                      trim(t_excel_bill_bill.fieldbyname('固定值').asstring));
      end;
    end
    else
    begin
      t_op_ctn.close;
      t_op_ctn.Parameters[0].value:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('编号').asstring;
      t_op_ctn.open;
      Y:=t_excel_bill_bill.fieldbyname('坐标Y').asinteger;
      t_op_ctn.first;
      while not t_op_ctn.eof do
      begin
        wide_str:=t_op_ctn.fieldbyname('箱号').asstring
                 +copy('                    ',1,20-length(t_op_ctn.fieldbyname('箱号').asstring))
                 +t_op_ctn.fieldbyname('封号').asstring;
        excel1.putstr(Y,1,wide_str);

        wide_str:=t_op_ctn.fieldbyname('数量').asstring+'X'
                 +t_op_ctn.fieldbyname('表现形式').asstring+'  '
                 +t_op_ctn.fieldbyname('件数').asstring
                 +t_op_ctn.fieldbyname('包装').asstring;
        excel1.putstr(Y,2,wide_str);

        wide_str:=t_op_ctn.fieldbyname('重量').asstring+'KGS';
        excel1.putstr(Y,5,wide_str);

        wide_str:=t_op_ctn.fieldbyname('尺码').asstring+'CBM';
        excel1.putstr(Y,6,wide_str);
        Y:=Y+1;
        t_op_ctn.next;
      end;
      wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('件数').asstring
               +frm_op_seae.t_op_seae2.DataSet.fieldbyname('包装').asstring;
      excel1.putstr(33,2,wide_str);
      wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('重量').asstring+'KGS';
      excel1.putstr(33,5,wide_str);
      wide_str:=frm_op_seae.t_op_seae2.DataSet.fieldbyname('尺码').asstring+'CBM';
      excel1.putstr(33,6,wide_str);
    end;
    t_excel_bill_bill.next;
  end;

  SaveDialog.Filter := '(*.xls)|*.xls';
  SaveDialog.FileName := frm_op_seae.t_op_seae2.DataSet.fieldbyname('主提单号').asstring+'.xls';
  if SaveDialog.Execute then
  begin
    Excel1.Exec('[SAVE.AS("'+SaveDialog.FileName+'";1;"";false;"";false)]');
    Excel1.Exec('[CLOSE(false)]');
    Excel1.Exec('[open("'+SaveDialog.FileName+'")]');
    Excel1.Exec('[APP.maxIMIZE()]'); //最大化excel
  end;
  end else if tradetype='AIR' then begin
    if t_excel_bill.IsEmpty then
      exit;
    t_excel_billDSDesigner2.SaveToFile(ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls');

    str:=Tstringlist.Create;
    str.Clear;

    excel1.Connect;
    Excel1.Exec('[open('+'"'+ExtractFilePath(ParamStr(0))+t_excel_bill.fieldbyname('EX_ID').asstring+'.xls'+'"'+')]');
    t_excel_bill_bill.First;
    while not t_excel_bill_bill.eof do
    begin
      if trim(t_excel_bill_bill.fieldbyname('字段名称').asstring)<>'固定值'then
      begin
        Memo1.Text:=frm_op_aire.t_op_aire1.DataSet.fieldbyname(t_excel_bill_bill.fieldbyname('字段名称').asstring).asstring;
        for i:=0 to Memo1.Lines.Count-1 do
        begin
            excel1.putstr(t_excel_bill_bill.fieldbyname('坐标Y').asinteger+i,
                      t_excel_bill_bill.fieldbyname('坐标X').asinteger,
                      Memo1.Lines[i]);
        end;
      end
      else
      begin
        excel1.putstr(t_excel_bill_bill.fieldbyname('坐标Y').asinteger,
                        t_excel_bill_bill.fieldbyname('坐标X').asinteger,
                        trim(t_excel_bill_bill.fieldbyname('固定值').asstring));
      end;
    t_excel_bill_bill.next;
  end;

  SaveDialog.Filter := '(*.xls)|*.xls';
  SaveDialog.FileName := frm_op_seae.t_op_seae2.DataSet.fieldbyname('主提单号').asstring+'.xls';
  if SaveDialog.Execute then
  begin
    Excel1.Exec('[SAVE.AS("'+SaveDialog.FileName+'";1;"";false;"";false)]');
    Excel1.Exec('[CLOSE(false)]');
    Excel1.Exec('[open("'+SaveDialog.FileName+'")]');
    Excel1.Exec('[APP.maxIMIZE()]'); //最大化excel
  end;

  end;
end;

procedure Tfrm_op_seae_excel.bsSkinButton13Click(Sender: TObject);
begin
  if t_excel_bill.IsEmpty then
    exit;
end;

end.