系統運維

unit ExcelProUnit;

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

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

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

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

}
//afilename為數據源文件名，func為執行導入的函數
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;
//保存鼠標指針狀態
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 //執行導入函數
func(ExcelApp.ActiveSheet); //傳遞sheet給函數進行導入
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.

硯山網站制作公司哪家好，找創新互聯建站！從網頁設計、網站建設、微信開發、APP開發、響應式網站建設等網站項目制作，到程序開發，運營維護。創新互聯建站成立于2013年到現在10年的時間，我們擁有了豐富的建站經驗和運維經驗，來保證我們的工作的順利進行。專注于網站建設就選創新互聯建站。

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(看情況而定)行如果為空就退出,這樣的設定,最好是你的
//檔案力這兩行//對應數據庫中不能為空的數據

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;//取原數據字段數據類型
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列是動態列，所以需要給其關聯一個OnChange的事件：
(cxitems.Columns[0].Properties as TcxCheckBoxProperties).OnChange:=View_UpCheckBoxColumnPropertiesChange;//關聯事件
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.

本文名稱：delphi導入excel
當前URL：http://vcdvsql.cn/article30/choppo.html

成都網站建設公司_創新互聯，為您提供品牌網站制作、動態網站、虛擬主機、域名注冊、網站維護、建站公司

廣告

聲明：本網站發布的內容（圖片、視頻和文字）以用戶投稿、用戶轉載內容為主，如果涉及侵權請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網站立場，如需處理請聯系客服。電話：028-86922220；郵箱：631063699@qq.com。內容未經允許不得轉載，或轉載時需注明來源： 創新互聯