delphi 導入excel

unit ExcelProUnit;

interface
type
  TExcelFunction = procedure(asheet: OleVariant); //聲明導入函數(shù)

  {訪問單元格：sheet.cells[row,col]

轉為string：vartostr(sheet.cells[row,col])

轉為datetime：vartodatetime(sheet.cells[row,col])

}
  //afilename為數(shù)據(jù)源文件名，func為執(zhí)行導入的函數(shù)
procedure RunExcelApplication(afilename: string; func: TExcelFunction);

implementation
uses Controls, Forms, ComObj, windows, sysutils;

procedure RunExcelApplication(afilename: string;
  func: TExcelFunction);
Var
  ExcelApp : Variant ;
  oldCursor: TCurSor;
begin
  oldCursor := Screen.Cursor;
 //保存鼠標指針狀態(tài)
  Screen.Cursor := crHourGlass;
  try
    CoInitializeEx(nil, 0);
    ExcelApp := CreateOleObject('Excel.Application');
    ExcelApp.Visible := true;
    try
      ExcelApp.WorkBooks.open(afilename);
//打開源文件
      ExcelApp.WorkSheets[1].Activate;
      ExcelApp.visible := False; //隱藏excel窗體
      if Assigned(func) then //執(zhí)行導入函數(shù)
        func(ExcelApp.ActiveSheet); //傳遞sheet給函數(shù)進行導入
    finally
      ExcelApp.WorkBooks.Close ;
      ExcelApp.Quit ;
      Screen.Cursor := oldCursor;
    end;
  except on e: Exception do
    begin
      MessageBox(GetActiveWindow, pchar(e.message), '提示', MB_OK + MB_ICONINFORMATION);
      Screen.Cursor := OldCursor;
      Exit;
    end;
  end;
end;

end.

unit frmBuyingItemsP;

interface

uses
  Windows, Messages, SysUtils, Variants, Classes, Graphics, Controls, Forms,
  Dialogs,EmbeddableFormU, dxSkinsCore, dxSkinOffice2010Black,
  dxSkinOffice2010Blue, dxSkinOffice2010Silver, dxSkinsDefaultPainters,
  dxSkinsdxBarPainter, dxBar, cxClasses, cxGraphics, cxControls, cxLookAndFeels,
  cxLookAndFeelPainters, cxStyles, dxSkinscxPCPainter, cxCustomData, cxFilter,
  cxData, cxDataStorage, cxEdit, DB, cxDBData, cxGridLevel, cxGridCustomView,
  cxGridCustomTableView, cxGridTableView, cxGridDBTableView, cxGrid, ExtCtrls,
  RzPanel, StdCtrls,cxCheckBox, DBClient, ADODB, ComCtrls;

type
  TCheckBoxClickEvent=procedure(Sender: TObject) of object;
type
  TCheckBoxClick = class(TObject)
  private
    FOnCheckBoxClick:TCheckBoxClickEvent; //定義一個內部事件，private里的只能在類內部調用
  public
    property View_UpCheckBoxColumnPropertiesChange:TCheckBoxClickEvent read FOnCheckBoxClick write FOnCheckBoxClick; //定義一個外部的事件
end;

type
  TfrmBuyingItems = class(TEmbeddableForm)
    dxBarManager1: TdxBarManager;
    dxBarManager1Bar1: TdxBar;
    barsearch: TdxBarButton;
    barexport: TdxBarButton;
    barimport: TdxBarButton;
    baradd: TdxBarButton;
    barmodify: TdxBarButton;
    barclose: TdxBarButton;
    RzGroupBox1: TRzGroupBox;
    cxitems: TcxGridDBTableView;
    cxGrid1Level1: TcxGridLevel;
    cxGrid1: TcxGrid;
    barsave: TdxBarButton;
    edtno: TLabeledEdit;
    cxitemsColumn1: TcxGridDBColumn;
    cxitemsColumn2: TcxGridDBColumn;
    cxitemsColumn3: TcxGridDBColumn;
    cxitemsColumn4: TcxGridDBColumn;
    cxitemsColumn5: TcxGridDBColumn;
    cxitemsColumn6: TcxGridDBColumn;
    cxitemsColumn7: TcxGridDBColumn;
    cxitemsColumn8: TcxGridDBColumn;
    cxitemsColumn9: TcxGridDBColumn;
    cxitemsColumn10: TcxGridDBColumn;
    cxitemsColumn11: TcxGridDBColumn;
    cxitemsColumn12: TcxGridDBColumn;
    cxitemsColumn13: TcxGridDBColumn;
    cxitemsColumn14: TcxGridDBColumn;
    cxitemsColumn15: TcxGridDBColumn;
    cxitemsColumn16: TcxGridDBColumn;
    cxitemsColumn17: TcxGridDBColumn;
    cxitemsColumn18: TcxGridDBColumn;
    cxitemsColumn19: TcxGridDBColumn;
    cxitemsColumn20: TcxGridDBColumn;
    cxitemsColumn21: TcxGridDBColumn;
    cxitemsColumn22: TcxGridDBColumn;
    cxitemsColumn23: TcxGridDBColumn;
    cxitemsColumn24: TcxGridDBColumn;
    cxitemsColumn25: TcxGridDBColumn;
    cxitemsColumn26: TcxGridDBColumn;
    cxitemsColumn27: TcxGridDBColumn;
    cxitemsColumn28: TcxGridDBColumn;
    cxitemsColumn29: TcxGridDBColumn;
    cxitemsColumn30: TcxGridDBColumn;
    cxitemsColumn31: TcxGridDBColumn;
    cxitemsColumn32: TcxGridDBColumn;
    cxitemsColumn33: TcxGridDBColumn;
    cxitemsColumn34: TcxGridDBColumn;
    edtname: TLabeledEdit;
    cxitemsColumn35: TcxGridDBColumn;
    ClientDataSet1: TClientDataSet;
    ADOQuery1: TADOQuery;
    OpenDialog1: TOpenDialog;
    barimport2: TdxBarButton;
    RichEdit1: TRichEdit;
    procedure barcloseClick(Sender: TObject);
    procedure FormShow(Sender: TObject);
    procedure barsearchClick(Sender: TObject);
    procedure FormCreate(Sender: TObject);
    procedure barimportClick(Sender: TObject);
    procedure barsaveClick(Sender: TObject);
  private
    { Private declarations }
  public
    { Public declarations }
    procedure View_UpCheckBoxColumnPropertiesChange(Sender: TObject);
  end;

var
  frmBuyingItems: TfrmBuyingItems;

implementation

{$R *.dfm}
uses dmbuyingitemsP,ExcelProUnit,dbmoduleP,Comobj,WordXP;

var
  sl: tStrings;
  pubsql:string;

procedure GetFromExcel(asheet: OleVariant);
var
  s, rs: string;
  row: integer;
  no,item_no,item_no_old,choice_name, name,name_old,buying_price,face_price,add_price,
    native_trans_fee, price, national_tran_fee,service_charge_rate,
    service_charge_fee, profit, chinese_kind_name, english_name,
    weight, volume, american_price, real_american_price, hs_code,
    upload_day, downshelf_day, leftdays, buying_name, buying_url,
    status, korea_name, chinese_name,
    clearance_sign_id_id, transport_way_id_id, tariff, add_express_fee: string;
    adodata: TADOQuery;
  id:string;
  clearance_sign,transport_way:string;
begin
  row := 1;
  s := trim(vartostr(aSheet.cells[row, 1]));
  pubsql := '';
  while s <> '' do
  begin
    if row > 490 then
    begin
    no := trim(vartostr(aSheet.cells[row, 1]));
    item_no := trim(vartostr(aSheet.cells[row, 2]));
    item_no := dmbuyingitems.getmaxBuyingItems_Id;
    item_no_old := trim(vartostr(aSheet.cells[row, 2]));
    choice_name := trim(vartostr(aSheet.cells[row, 3]));
    name := trim(vartostr(aSheet.cells[row, 4]));
    name := choice_name + ' ' + item_no;
    name_old := trim(vartostr(aSheet.cells[row, 4]));
    buying_price := trim(vartostr(aSheet.cells[row, 5]));
    if (buying_price = '') or (buying_price = Null) then
      buying_price := '0';
    face_price := trim(vartostr(aSheet.cells[row, 6]));
    if (face_price = '') or (face_price = Null) then
      face_price := '0';
    add_price := trim(vartostr(aSheet.cells[row, 7]));
    if (add_price = '') or (add_price = Null) then
      add_price := '0';
    native_trans_fee := trim(vartostr(aSheet.cells[row, 8]));
    if (native_trans_fee = '') or (native_trans_fee = Null) then
      native_trans_fee := '0';
    price := trim(vartostr(aSheet.cells[row, 9]));
    if (price = '') or (price = Null) then
      price := '0';
    national_tran_fee := trim(vartostr(aSheet.cells[row, 10]));
    if (national_tran_fee = '') or (national_tran_fee = Null) then
      national_tran_fee := '0';
    service_charge_rate := trim(vartostr(aSheet.cells[row, 11]));
    if (service_charge_rate = '') or (service_charge_rate = Null) then
      service_charge_rate := '0';
    service_charge_fee := trim(vartostr(aSheet.cells[row, 12]));
    if (service_charge_fee = '') or (service_charge_fee = Null) then
      service_charge_fee := '0';
    profit := trim(vartostr(aSheet.cells[row, 13]));
    if (profit = '') or (profit = Null) then
      profit := '0';
    chinese_kind_name := trim(vartostr(aSheet.cells[row, 14]));
    english_name := trim(vartostr(aSheet.cells[row, 15]));
    weight := trim(vartostr(aSheet.cells[row, 16]));
    if (weight = '') or (weight = Null) then
      weight := '0';
    volume := trim(vartostr(aSheet.cells[row, 17]));
    if (volume = '') or (volume = Null) then
      volume := '0';
    american_price := trim(vartostr(aSheet.cells[row, 18]));
    if (american_price = '') or (american_price = Null) then
      american_price := '0';
    real_american_price := trim(vartostr(aSheet.cells[row, 19]));
    if (real_american_price = '') or (real_american_price = Null) then
      real_american_price := '0';
    hs_code := trim(vartostr(aSheet.cells[row, 20]));
    upload_day := trim(vartostr(aSheet.cells[row, 21]));
    downshelf_day := trim(vartostr(aSheet.cells[row, 22]));
    leftdays := trim(vartostr(aSheet.cells[row, 23]));
    if (leftdays = '') or (leftdays = Null) then
      leftdays := '0';
    buying_name := trim(vartostr(aSheet.cells[row, 24]));
    buying_url := trim(vartostr(aSheet.cells[row, 25]));
    status := trim(vartostr(aSheet.cells[row, 26]));
    korea_name := trim(vartostr(aSheet.cells[row, 27]));
    chinese_name := trim(vartostr(aSheet.cells[row, 28]));
    transport_way := trim(vartostr(aSheet.cells[row, 29]));
    clearance_sign := trim(vartostr(aSheet.cells[row,30]));
    if (clearance_sign = '') or (clearance_sign = null) then
    begin
      Application.MessageBox('請輸入通關符號','提示',MB_ICONWARNING);
      Abort;
    end;
    if (transport_way = '') or (transport_way = null) then
    begin
      Application.MessageBox('請輸入貨運方式','提示',MB_ICONWARNING);
      Abort;
    end;

    clearance_sign_id_id := dmbuyingitems.get_clearance_sign_id(clearance_sign);
    transport_way_id_id := dmbuyingitems.get_transport_way_id(transport_way);

    clearance_sign_id_id := '1';
    transport_way_id_id := '1';
    tariff := trim(vartostr(aSheet.cells[row, 31]));
    if (tariff = '') or (tariff = Null) then
      tariff := '0';
    add_express_fee := trim(vartostr(aSheet.cells[row, 32]));
    if (add_express_fee = '') or (add_express_fee = Null) then
      add_express_fee := '0';

    pubsql := pubsql + ' insert into erp_buyingitem(no,item_no,item_no_old,choice_name, name,name_old,buying_price,face_price,add_price,'
      + ' native_trans_fee, price, national_tran_fee,service_charge_rate,'
      + ' service_charge_fee, profit, chinese_kind_name, english_name,'
      + ' weight, volume, american_price, real_american_price, hs_code,'
      + ' upload_day, downshelf_day, leftdays, buying_name, buying_url, '
      + ' status, korea_name, chinese_name,'
      + ' clearance_sign_id_id, transport_way_id_id, tariff, add_express_fee)';
    pubsql := pubsql + 'select ' + QuotedStr(no) + ',' + QuotedStr(item_no) + ',' + QuotedStr(item_no_old) + ',' + QuotedStr(choice_name)
      + ',' + QuotedStr(name) + ',' + QuotedStr(name_old) + ',' + QuotedStr(buying_price) + ',' + QuotedStr(face_price) + ',' + QuotedStr(add_price)
      + ',' + QuotedStr(native_trans_fee) + ',' + QuotedStr(price) + ',' + QuotedStr(national_tran_fee) + ',' + QuotedStr(service_charge_rate)
      + ',' + QuotedStr(service_charge_fee) + ',' + QuotedStr(profit) + ',' + QuotedStr(chinese_kind_name) + ',' + QuotedStr(english_name)
      + ',' + QuotedStr(weight) + ',' + QuotedStr(volume) + ',' + QuotedStr(american_price) + ',' + QuotedStr(real_american_price)
      + ',' + QuotedStr(hs_code) + ',' + QuotedStr(upload_day) + ',' + QuotedStr( downshelf_day) + ',' + QuotedStr(leftdays)
      + ',' + QuotedStr(buying_name) + ',' + QuotedStr(buying_url) + ',' + QuotedStr(status) + ',' + QuotedStr(korea_name)
      + ',' + QuotedStr(chinese_name) + ',' + QuotedStr(clearance_sign_id_id) + ',' + QuotedStr(transport_way_id_id) + ','
      + QuotedStr(tariff) + ',' + QuotedStr(add_express_fee);
    end;
    inc(row);
    sl.Add(rs);
    s := trim(vartostr(aSheet.cells[row, 3]));

  end;
end;

procedure TfrmBuyingItems.barcloseClick(Sender: TObject);
begin
  close;
end;

procedure TfrmBuyingItems.barimportClick(Sender: TObject);
begin
  OpenDialog1.Title := '請選擇正確的excel文件';
  OpenDialog1.Filter := 'Excel(*.xls)|*.xls';

  if OpenDialog1.Execute then
  begin
  //  RunExcelApplication(ExtractFilePath(application.ExeName) + 'success.xls', GetFromExcel);
    RunExcelApplication(OpenDialog1.FileName, GetFromExcel);
    RichEdit1.Text := pubsql;
    try
      dbmodule.SHSCon.BeginTrans;
      dmbuyingitems.exesql(pubsql);
      dbmodule.SHSCon.CommitTrans;
      Application.MessageBox('導入成功！','提示',MB_OK);
      barsearchClick(self);
    Except
      dbmodule.SHSCon.RollbackTrans;
      Application.MessageBox('導入失敗！','提示',MB_OK);
    end;
    //memo1.Lines.AddStrings(sl);
  end;
  {
    RunExcelApplication(ExtractFilePath(application.ExeName) + 'success.xlsx', GetFromExcel);
  memo1.Lines.AddStrings(sl);
  }
end;

procedure TfrmBuyingItems.barsaveClick(Sender: TObject);
 var excelx,excely : string;
   ExcelApp,WorkBook:oleVariant;
   ExcelRowCount,i:integer;
begin
  OpenDialog1.Title := '請選擇正確的excel文件';
  OpenDialog1.Filter := 'Excel(*.xls)|*.xls';

  if OpenDialog1.Execute then
  begin
try

ExcelApp := CreateOleObject('Excel.Application');

WorkBook := CreateOleObject('Excel.Sheet');
WorkBook := ExcelApp.WorkBooks.Open(opendialog1.FileName);//使用opendialog對話框指定
//excel檔路徑



ExcelApp.Visible := false;

ExcelRowCount := WorkBook.WorkSheets[1].UsedRange.Rows.Count;

for i := 1 to excelrowcount + 1 do

begin

excelx := excelapp.Cells[i,1].Value;

excely := excelapp.Cells[i,2].Value;

if ((excelapp.Cells[i,1].Value = '') and (ExcelApp.Cells[i,2].Value = '')) then
//指定excel檔的第 i 行 ,第 1,2(看情況而定)行如果為空就退出,這樣的設定,最好是你的
//檔案力這兩行//對應數(shù)據(jù)庫中不能為空的數(shù)據(jù)

exit

else

with adoquery1 do

begin

close;
sql.clear;
sql.add('insert into test(name,address) values(:name,:address)');
Parameters.parambyname('name').value := excelx;//excel檔的第一列插入到test表的 name欄位;
Parameters.parambyname('address').value := excely;//excel檔的第二列插入到test表的address 欄位;
execsql;

end;

end;

finally

WorkBook.Close;

ExcelApp.Quit;

ExcelApp := Unassigned;

WorkBook := Unassigned;
end;
  end;

end;

procedure TfrmBuyingItems.barsearchClick(Sender: TObject);
var
  item_no,name:string;
begin
  dmbuyingitems.getBuyingItems(item_no,name);
  cxitems.DataController.DataSource := dmbuyingitems.dsitems;
end;

procedure TfrmBuyingItems.FormCreate(Sender: TObject);
begin
  sl := TStringList.Create;
end;


procedure TfrmBuyingItems.FormShow(Sender: TObject);
var
  i:Integer;
begin
  for i := 0 to self.ComponentCount - 1 do
  begin
    if Self.Components[i] is TLabeledEdit then
    begin
      with Self.Components[i] as TLabeledEdit do
      begin
        BevelEdges := [beBottom];
        BevelInner:=bvNone;
        BevelKind :=bkSoft;
        BevelOuter:=bvRaised;
        BorderStyle:=bsNone;
        ParentColor:=True;
      end;
    end;
  end;
  barsearchClick(self);
  ClientDataSet1.FieldDefs.Clear;
  for i:=0 to dmbuyingitems.adoItems.FieldCount-1 do
  begin
    with ClientDataSet1.FieldDefs.AddFieldDef do
    begin
      Name:= dmbuyingitems.adoItems.Fields[i].DisplayName;
      if dmbuyingitems.adoItems.Fields.Fields[i].DataType=ftAutoInc then
        DataType:=ftInteger
      else if dmbuyingitems.adoItems.Fields.Fields[i].DataType=ftWideString then
        DataType:=ftString
      else
        DataType :=dmbuyingitems.adoItems.Fields.Fields[i].DataType;//取原數(shù)據(jù)字段數(shù)據(jù)類型
      Size:=dmbuyingitems.adoItems.Fields.Fields[i].Size;
    end;
  end;
  ClientDataSet1.CreateDataSet;
  dmbuyingitems.dsitems.DataSet := dmbuyingitems.adoItems;
  cxitems.DataController.DataSource := dmbuyingitems.dsitems;

 // cxyzjl.ClearItems;
 // cxyzjl.CreateColumn;//建立一個沒綁定的列
  cxitems.Columns[0].Caption:='選擇';
//  cxitems.DataController.CreateAllItems;//建立所有綁定的列
//  dw_checker1.Columns[0].DataBinding.FieldName := 'flag';
  cxitems.Columns[0].Width:=45;

    //下列5行語句是為了讓沒綁定列成為 CheckBox ：
  cxitems.DataController.KeyFieldNames:='id';
  cxitems.DataController.MasterKeyFieldNames := 'id';
  cxitems.DataController.DetailKeyFieldNames := 'id';
  cxitems.DataController.DataModeController.SmartRefresh:=true;
  cxitems.Columns[0].DataBinding.ValueType:='Boolean';
  cxitems.Columns[0].PropertiesClass:= TcxCheckBoxProperties;
  (cxitems.Columns[0].Properties as TcxCheckBoxProperties).NullStyle:=nssUnchecked;
 //由于CheckBox列是動態(tài)列，所以需要給其關聯(lián)一個OnChange的事件：
  (cxitems.Columns[0].Properties as TcxCheckBoxProperties).OnChange:=View_UpCheckBoxColumnPropertiesChange;//關聯(lián)事件
  cxitems.OptionsView.Indicator:=true;
  cxitems.OptionsView.NoDataToDisplayInfoText := '';

end;

procedure TfrmBuyingItems.View_UpCheckBoxColumnPropertiesChange(
  Sender: TObject);
begin
  ////////////////////////////////////////////////////
  if cxitems.Focused = true then
  if (Sender as TcxCheckBox).checked then
  begin
    cxitems.ViewData.Rows[cxitems.Controller.FocusedRowIndex].Values[0]:= true;
  end
  else
  begin
   cxitems.ViewData.Rows[cxitems.Controller.FocusedRowIndex].Values[0]:= false;
  end;
end;

end.