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

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

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

}
  //afilename為數(shù)據(jù)源文件名，func為執(zhí)行導(dǎo)入的函數(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;
 //保存鼠標(biāo)指針狀態(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í)行導(dǎo)入函數(shù)
        func(ExcelApp.ActiveSheet); //傳遞sheet給函數(shù)進(jìn)行導(dǎo)入
    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; //定義一個內(nèi)部事件，private里的只能在類內(nèi)部調(diào)用
  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(\\\’請輸入通關(guān)符號\\\’,\\\’提示\\\’,MB_ICONWARNING);
      Abort;
    end;
    if (transport_way = \\\’\\\’) or (transport_way = null) then
    begin
      Application.MessageBox(\\\’請輸入貨運(yùn)方式\\\’,\\\’提示\\\’,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(\\\’導(dǎo)入成功！\\\’,\\\’提示\\\’,MB_OK);
      barsearchClick(self);
    Except
      dbmodule.SHSCon.RollbackTrans;
      Application.MessageBox(\\\’導(dǎo)入失?。\\’,\\\’提示\\\’,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è)定,最好是你的
//檔案力這兩行//對應(yīng)數(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)列，所以需要給其關(guān)聯(lián)一個OnChange的事件：
  (cxitems.Columns[0].Properties as TcxCheckBoxProperties).OnChange:=View_UpCheckBoxColumnPropertiesChange;//關(guān)聯(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.

更多關(guān)于云服務(wù)器，域名注冊，虛擬主機(jī)的問題，請訪問西部數(shù)碼官網(wǎng)：youyuetrip.com





猜你還會喜歡下面的內(nèi)容

• 一般企業(yè)郵箱容量是多少
• 雙十一虛擬主機(jī)購買優(yōu)惠券去哪領(lǐng)
• 獨(dú)立IP云虛擬主機(jī)多少錢
• 多個網(wǎng)站買虛擬主機(jī)還是云服務(wù)器
• 云虛擬主機(jī)可以安裝寶塔嗎
• 常見的云虛擬主機(jī)有哪些
• 博客系統(tǒng)怎么在云虛擬主機(jī)安裝
• 辦網(wǎng)站沒有虛擬主機(jī)行嗎
• 便宜的云虛擬主機(jī)一定是好的嗎
• web怎么添加虛擬主機(jī)
• dv ssl怎么放入虛擬主機(jī)
• linux云虛擬主機(jī)如何ssl
• Java程序放在云虛擬主機(jī)嗎
• html云虛擬主機(jī)選什么系統(tǒng)好
• APP為什么不能用云虛擬主機(jī)
• wdcp怎么選云虛擬主機(jī)

熱門標(biāo)簽

其他問題云服務(wù)器問題域名及賬戶問題企業(yè)郵局市場咨詢云服務(wù)器云建站/云站群/小程序虛擬主機(jī)網(wǎng)絡(luò)知識企業(yè)郵箱域名注冊域名備案域名商標(biāo)注冊云主機(jī)更多標(biāo)簽...

大家感興趣的內(nèi)容