?? unit1.pas
字號:
ADOQuery1.Fields[9].AsString := '3'; //FLGS
if JcFlag = 1 then
ADOQuery1.Fields[9].AsString := '4'; //FLGS
ADOQuery1.Fields[10].AsString := '1'; //FLSX
ADOQuery1.Fields[11].AsString := '5'; //ZCWS
ADOQuery1.Fields[12].AsString := '2'; //XSWS
ADOQuery1.Fields[13].AsString := rightstr('000000000000' + VarToStrDef(ExcelApp.Cells[i, CJQH].Value, ''), 12); //CJQH
if tempstr<> ADOQuery1.Fields[13].AsString then
begin
CJZDCLDH := 1;
tempstr := ADOQuery1.Fields[13].AsString;
end;
ADOQuery1.Fields[14].AsString := inttostr(CJZDCLDH); //CJZDCLDH
ADOQuery1.Fields[15].AsString := '0.000'; //YGZ
ADOQuery1.Fields[16].AsString := '0.000'; //YGF
ADOQuery1.Fields[17].AsString := '0.000'; //YGP
ADOQuery1.Fields[18].AsString := '0.000'; //YGG
ADOQuery1.Fields[19].AsString := '0.000'; //YGJ
ADOQuery1.Fields[20].AsString := '0.000'; //ZXWGZ
ADOQuery1.Fields[21].AsString := '0.000'; //FXWGZ
ADOQuery1.Fields[22].AsString := '0.000'; //XX1WG
ADOQuery1.Fields[23].AsString := '0.000'; //XX2WG
ADOQuery1.Fields[24].AsString := '0.000'; //XX3WG
ADOQuery1.Fields[25].AsString := '0.000'; //XX4WG
ADOQuery1.Fields[26].AsString := '0.000'; //YGXL
dbfstr := ADOQuery1.Fields[1].AsString;
ADOQuery1.Post;
CLDH := CLDH +1;
CJZDCLDH := CJZDCLDH + 1;
//********************
end;
ADOQuery1.Close;
{ //排序整理
CopyDbfFile('jcdnb\' + DbfTemp,'jcdnb.db');
ADOQuery1.SQL.Clear;
//insert into temp.dbf select * from jcdnb顏北一區T03002.dbf order by jzqh,cldh,cjzddz,cjzdcldh'
ADOQuery1.SQL.Add('insert into ' + DbfTemp + ' select * from ' + DbfTmp
+ ' order by jzqh,cjzddz,int(cldh)');
ADOQuery1.ExecSQL;
DeleteFile(ExtractFilePath(application.ExeName) + 'jcdnb\' + DbfTmp);
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select * from ' + DbfTemp);
ADOQuery1.Open;
tempstr := DateTimeToStr(Now);
CLDH := 1;
CJZDCLDH := 1;
while not ADOQuery1.Eof do
begin
ADOQuery1.Edit;
ADOQuery1.Fields[3].AsString := inttostr(CLDH); //CLDH
if tempstr<> ADOQuery1.Fields[13].AsString then
begin
CJZDCLDH := 1;
tempstr := ADOQuery1.Fields[13].AsString;
end;
ADOQuery1.Fields[14].AsString := inttostr(CJZDCLDH); //CJZDCLDH
ADOQuery1.Post;
ADOQuery1.Next;
CLDH := CLDH +1;
CJZDCLDH := CJZDCLDH + 1;
end;
ADOQuery1.Close;
RenameFile(ExtractFilePath(application.ExeName) + 'jcdnb\' + DbfTemp,
ExtractFilePath(application.ExeName) + 'jcdnb\' + ExcelStr);
//處理完成
}
if FileExists(ExtractFilePath(application.ExeName) + 'jcdnb\' + ExcelStr) then
DeleteFile(ExtractFilePath(application.ExeName) + 'jcdnb\' + ExcelStr);
RenameFile(ExtractFilePath(application.ExeName) + 'jcdnb\' + DbfTmp,
ExtractFilePath(application.ExeName) + 'jcdnb\' + ExcelStr);
receive.Lines.Add('處理' + OpenDialog1.Files.Strings[FileNum] + '完畢，進行下一個操作！');
//Jcdnb2Txt 處理
if Jc2Txt then Jcdnb2Txt(ExcelStr);
end;
ExcelApp.Quit;
receive.Lines.Add(' 處理xls文件完畢，謝謝使用！！');
self.Cursor := crdefault;
except
ExcelApp.Quit;
ADOQuery1.Close;
self.Cursor := crdefault;
receive.Lines.Add('請檢查Excel文件，確認信息正確！！');
result := false;
end;
end;
function TForm1.E2ND() :Boolean;
var
ExcelStr, dbfstr, tempstr: string;
i, j, FileNum, RowStart: integer;
XM,BH,DZ,DH,DXH,ZHH,HM,YYH,CL,DJ,LB,SYDL,JJDL,SYSS: integer;
ExcelApp, aSheet: Variant;
begin
result := true;
try
receive.Lines.Add(OpenDialog1.Files.CommaText);
self.Cursor := crhelp;
ExcelApp := CreateOLEObject('Excel.Application');
for FileNum := 0 to (OpenDialog1.Files.Count-1) do
begin
dbfstr := ExtractFileName(OpenDialog1.Files.Strings[FileNum]);
if AnsiUpperCase(rightstr(dbfstr, 3)) = 'XLS' then
receive.Lines.Add('正在處理' + OpenDialog1.Files.Strings[FileNum] + ',請稍后...');
ExcelStr := ExtractFileName(OpenDialog1.Files.Strings[FileNum]);
ExcelStr := ChangeFileExt(ExcelStr, '.dbf'); //改后綴名
ExcelStr := 'cbjxz' + StringReplace(ExcelStr, ' ', '', [rfReplaceAll]); //'Abcd' 置換字符串
receive.Lines.Add('正在處理' + trim(ExcelStr));
CopyDbfFile('Newcbjxz\' + DbfTmp,'newcbjxz.db');
ADOQuery1.ConnectionString := NewConStr;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select * from ' + DbfTmp);
ADOQuery1.Open;
ExcelApp.workBooks.Open(OpenDialog1.Files.Strings[FileNum]);
aSheet := ExcelApp.Worksheets[1];
ExcelApp.Worksheets[1].activate;
//判斷起始行
for RowStart := 1 to 10 do
begin
j := 0;
for i := 1 to 10 do
begin
if length(VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
j := j + 1;
end;
if j >= 2 then break;
end;
if j < 2 then
begin
// showmessage('起始行！'+inttostr(RowStart));
// showmessage('此Excel文件錯誤！');
// exit;
end;
//判斷起始行
//判斷 項目XMNUM 表號BHNUM 地址DZNUM 段號DHNUM 段序號DXHNUM
for i := 1 to 50 do
begin //i is column
if Pos('項目', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
XM := i;
if Pos('表號', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
BH := i;
if Pos('地址', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
DZ := i;
if Pos('段號', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
DH := i;
if Pos('段序', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
DXH := i;
if Pos('戶名', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
HM := i;
if Pos('戶號', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
ZHH := i;
if Pos('營業號', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
YYH := i;
if Pos('電價', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
DJ := i;
if Pos('乘率', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
CL := i;
if Pos('加鎖示數', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
SYSS := i;
if Pos('類別', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
LB := i;
if Pos('實用電量', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
SYDL := i;
if Pos('加減電量', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
JJDL := i;
end;
Application.ProcessMessages;
tempstr := FormatDateTime('yyyy-mm-dd hh:mm:ss', now);
for i := RowStart + 1 to aSheet.UsedRange.Rows.Count do
begin // aSheet.UsedRange.Rows.Count
if VarToStrDef(ExcelApp.Cells[i, 2].Value, '') = '' then continue; //遇到總戶號為空跳過
receive.Lines.Add(VarToStrDef(ExcelApp.Cells[i, BH].Value, '')+ ' , '+
VarToStrDef(ExcelApp.Cells[i, XM].Value, ''));
ADOQuery1.Append;
ADOQuery1.Fields[2].AsString := VarToStrDef(ExcelApp.Cells[i, DH].Value, ''); //DH
ADOQuery1.Fields[3].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, DXH].Value, ''),i)); //DXH
ADOQuery1.Fields[4].AsString := VarToStrDef(ExcelApp.Cells[i, ZHH].Value, ''); //HH
ADOQuery1.Fields[5].AsString := VarToStrDef(ExcelApp.Cells[i, HM].Value, ''); //HM
ADOQuery1.Fields[6].AsString := VarToStrDef(ExcelApp.Cells[i, DZ].Value, ''); //DZ
ADOQuery1.Fields[10].AsString := VarToStrDef(ExcelApp.Cells[i, YYH].Value, ''); //YYH
ADOQuery1.Fields[12].AsString := rightstr('000000000' + EdBh.Text + VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 13);
ADOQuery1.Fields[13].AsString := '5'; //ZCWS
// 示數類別項目
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('常規', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[14].AsString := '121'; //總
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('高峰', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[14].AsString := '123'; //峰
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('低谷', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[14].AsString := '125'; //谷
ADOQuery1.Fields[15].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, SYSS].Value, ''),0)); //SYSS
ADOQuery1.Fields[17].AsString := FloatToStr(StrToFloatDef(VarToStrDef(ExcelApp.Cells[i, CL].Value, ''),1.00)); //CL
ADOQuery1.Fields[18].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, SYDL].Value, ''),0)); //SCDL
ADOQuery1.Fields[19].AsString := '01'; //CBZT
ADOQuery1.Fields[20].AsString := '01'; //YCM
ADOQuery1.Fields[21].AsString := tempstr; //CBSJ
ADOQuery1.Fields[25].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, JJDL].Value, ''),0)); //JJDL
ADOQuery1.Fields[26].AsString := FloatToStr(StrToFloatDef(VarToStrDef(ExcelApp.Cells[i, DJ].Value, ''),0.52)); //DJ
ADOQuery1.Post;
//********************
end;
ADOQuery1.Close;
Application.ProcessMessages;
//排序整理
CopyDbfFile('Newcbjxz\' + DbfTemp,'newcbjxz.db');
ADOQuery1.SQL.Clear;
if ChkDxh.Checked then
begin
ADOQuery1.SQL.Add('insert into ' + DbfTemp + ' select * from ' + DbfTmp
+ ' order by mr_sect_no,mr_sn,mp_id,meter_id,read_type');
end
else
begin
ADOQuery1.SQL.Add('insert into ' + DbfTemp + ' select * from ' + DbfTmp );
end;
ADOQuery1.ExecSQL;
if FileExists(ExtractFilePath(application.ExeName) + 'Newcbjxz\' + DbfTmp) then
DeleteFile(ExtractFilePath(application.ExeName) + 'Newcbjxz\' + DbfTmp);
if FileExists(ExtractFilePath(application.ExeName) + 'Newcbjxz\' + ExcelStr) then
DeleteFile(ExtractFilePath(application.ExeName) + 'Newcbjxz\' + ExcelStr);
RenameFile(ExtractFilePath(application.ExeName) + 'Newcbjxz\' + DbfTemp,
ExtractFilePath(application.ExeName) + 'Newcbjxz\' + ExcelStr);
receive.Lines.Add('處理' + OpenDialog1.Files.Strings[FileNum] + '完畢，進行下一個操作！');
end;
ExcelApp.Quit;
receive.Lines.Add(' 處理xls文件完畢，謝謝使用！！');
self.Cursor := crdefault;
except
ExcelApp.Quit;
ADOQuery1.Close;
self.Cursor := crdefault;
receive.Lines.Add('請檢查Excel文件，確認信息正確！！');
result := false;
end;
end;
function TForm1.E2OD(): Boolean;
var
ExcelStr, dbfstr, tempstr: string;
i, j, FileNum, RowStart: integer;
XM,BH,DZ,DH,DXH,ZHH,HM,YYH,CL,DJ,LB,SYDL,JJDL,SYSS: integer;
ExcelApp, aSheet: Variant;
begin
result := true;
try
receive.Lines.Add(OpenDialog1.Files.CommaText);
self.Cursor := crhelp;
ExcelApp := CreateOLEObject('Excel.Application');
for FileNum := 0 to (OpenDialog1.Files.Count-1) do
begin
dbfstr := ExtractFileName(OpenDialog1.Files.Strings[FileNum]);
if AnsiUpperCase(rightstr(dbfstr, 3)) = 'XLS' then
receive.Lines.Add('正在處理' + OpenDialog1.Files.Strings[FileNum] + ',請稍后...');
ExcelStr := ExtractFileName(OpenDialog1.Files.Strings[FileNum]);
ExcelStr := ChangeFileExt(ExcelStr, '.dbf'); //改后綴名
ExcelStr := 'cbjxz' + StringReplace(ExcelStr, ' ', '', [rfReplaceAll]); //'Abcd' 置換字符串
receive.Lines.Add('正在處理' + trim(ExcelStr));
CopyDbfFile('Oldcbjxz\' + DbfTmp,'cbjxz.db');
ADOQuery1.ConnectionString := ConStr;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('select * from ' + DbfTmp);
ADOQuery1.Open;
ExcelApp.workBooks.Open(OpenDialog1.Files.Strings[FileNum]);
aSheet := ExcelApp.Worksheets[1];
ExcelApp.Worksheets[1].activate;
//判斷起始行
for RowStart := 1 to 10 do
begin
j := 0;
for i := 1 to 10 do
begin
if length(VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
j := j + 1;
end;
if j >= 2 then break;
end;
if j < 2 then
begin
// showmessage('起始行！'+inttostr(RowStart));
// showmessage('此Excel文件錯誤！');
// exit;
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -