?? unit1.pas
字號:
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, BH].Value, '') = '' then continue; //遇到總戶號為空跳過
receive.Lines.Add(VarToStrDef(ExcelApp.Cells[i, BH].Value, '')+ ' , '+
VarToStrDef(ExcelApp.Cells[i, XM].Value, ''));
ADOQuery1.Append;
ADOQuery1.Fields[0].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, ZHH].Value, ''),0)); //HH
ADOQuery1.Fields[1].AsString := rightstr('000000000' + VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 9);
// 示數類別項目
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('常規', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[2].AsString := '4'; //總
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('高峰', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[2].AsString := '1'; //峰
if (Pos('有功', VarToStrDef(ExcelApp.Cells[i, LB].Value, '')) > 0)
and (Pos('低谷', VarToStrDef(ExcelApp.Cells[i, XM].Value, '')) > 0) then
ADOQuery1.Fields[2].AsString := '3'; //谷
ADOQuery1.Fields[3].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, YYH].Value, ''),1)); //YYH
ADOQuery1.Fields[4].AsString := VarToStrDef(ExcelApp.Cells[i, DH].Value, ''); //DH
ADOQuery1.Fields[5].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, DXH].Value, ''),i)); //DXH
ADOQuery1.Fields[6].AsString := VarToStrDef(ExcelApp.Cells[i, HM].Value, ''); //HM
ADOQuery1.Fields[7].AsString := VarToStrDef(ExcelApp.Cells[i, DZ].Value, ''); //DZ
ADOQuery1.Fields[8].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, CL].Value, ''),1)); //CL
ADOQuery1.Fields[9].AsString := FloatToStr(StrToFloatDef(VarToStrDef(ExcelApp.Cells[i, DJ].Value, ''),0.52)); //DJ
ADOQuery1.Fields[10].AsString := FloatToStr(StrToFloatDef(VarToStrDef(ExcelApp.Cells[i, SYSS].Value, ''),0.000)); //SYSS
ADOQuery1.Fields[11].AsString := '0.000'; //BYSS
ADOQuery1.Fields[12].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, JJDL].Value, ''),0)); //JJDL
ADOQuery1.Fields[13].AsString := '1'; //YCM
ADOQuery1.Fields[14].AsString := tempstr; //CBSJ
ADOQuery1.Fields[16].AsString := '0'; //CBZT
ADOQuery1.Fields[17].AsString := IntToStr(StrToIntDef(VarToStrDef(ExcelApp.Cells[i, SYDL].Value, ''),0)); //SCDL
ADOQuery1.Fields[18].AsString := '1'; //SCYCM
ADOQuery1.Fields[20].AsString := '5'; //ZCWS
ADOQuery1.Fields[22].AsString := '10'; //YWBZ
ADOQuery1.Post;
//********************
end;
ADOQuery1.Close;
//排序整理
CopyDbfFile('Oldcbjxz\' + DbfTemp,'cbjxz.db');
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('insert into ' + DbfTemp + ' select * from ' + DbfTmp
+ ' order by dh,dxh,yyh,bh,sslbxm');
ADOQuery1.ExecSQL;
if FileExists(ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + DbfTmp) then
DeleteFile(ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + DbfTmp);
if FileExists(ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + ExcelStr) then
DeleteFile(ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + ExcelStr);
RenameFile(ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + DbfTemp,
ExtractFilePath(application.ExeName) + 'Oldcbjxz\' + 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.E2Txt(): Boolean;
var
ExcelStr, dbfstr, tempstr: string;
i, j, FileNum, RowStart: integer;
BH,CJQH : 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, '.txt'); //改后綴名
ExcelStr := StringReplace(ExcelStr, ' ', '', [rfReplaceAll]); //'Abcd' 置換字符串
receive.Lines.Add('正在處理' + trim(ExcelStr));
CopyDbfFile('Txt\'+trim(ExcelStr),'Temp.tx');
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
BH := i;
if Pos('采集器', VarToStrDef(ExcelApp.Cells[RowStart, i].Value, '')) > 0 then
CJQH := i;
end;
Application.ProcessMessages;
tempstr := DateTimeToStr(Now);
for i := RowStart + 1 to aSheet.UsedRange.Rows.Count do
begin // aSheet.UsedRange.Rows.Count
if VarToStrDef(ExcelApp.Cells[i, BH].Value, '') = '' then continue; //遇到總戶號為空跳過
if dbfstr = rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 12) then continue;//表號相同跳過
receive.Lines.Add(rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 12) + ', ' +
rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, CJQH].Value, ''), 12));
WriteFile('Txt\' + trim(ExcelStr),rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 12) +
rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, CJQH].Value, ''), 12)+#13#10);
dbfstr := rightstr('000000000' +
VarToStrDef(ExcelApp.Cells[i, BH].Value, ''), 12);
//********************
end;
receive.Lines.Add('處理' + OpenDialog1.Files.Strings[FileNum] + '完畢，進行下一個操作！');
end;
ExcelApp.Quit;
receive.Lines.Add(' 處理xls文件完畢，謝謝使用！！');
self.Cursor := crdefault;
except
ExcelApp.Quit;
self.Cursor := crdefault;
receive.Lines.Add('請檢查Excel文件，確認信息正確！！');
result := false;
end;
end;
//PackDbf操作
function TForm1.PackDbf():Boolean;
var
s: string;
label stat;
begin
result := true;
//ADOQuery1
ADOQuery1.ConnectionString := ConStr;
ADOQuery1.SQL.Clear;
ADOQuery1.SQL.Add('delete * from Temp');
ADOQuery1.ExecSQL;
ADOQuery1.Close;
//********************************8
s := ExtractFilePath(application.ExeName);
// ShellExecute(handle,'open',pchar(s), nil, nil, SW_ShowNormal);
ADOCommand1.ConnectionString := 'Provider=MSDASQL.1;Persist Security Info=False;Extended Properties="DSN=Visual FoxPro Database;UID=;SourceDB= ' + s + ';' +
' SourceType=DBF;Exclusive=No;BackgroundFetch=Yes;Collate=Machine;Null=Yes;Deleted=Yes;"';
ADOCommand1.CommandText := 'pack Temp.dbf';
ADOCommand1.Execute;
end;
procedure TForm1.EnableButton(BEnable: Boolean);
var
i: integer;
begin
for i := 0 to self.ComponentCount -1 do
begin
if self.Components[i] is TButton then
TButton(self.Components[i]).Enabled := BEnable;
end;
end;
//定義連接字符串
procedure TForm1.FormCreate(Sender: TObject);
begin
ConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="'
+ ExtractFilePath(application.ExeName) + 'Oldcbjxz' //ExtractFilePath(zz)
+ '";Extended Properties=dBase 5.0;Persist Security Info=False';
JcdnbConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="'
+ ExtractFilePath(application.ExeName) + 'jcdnb' //ExtractFilePath(zz)
+ '";Extended Properties=dBase 5.0;Persist Security Info=False';
NewConStr := 'Provider=Microsoft.Jet.OLEDB.4.0;Data Source="'
+ ExtractFilePath(application.ExeName) + 'Newcbjxz' //ExtractFilePath(zz)
+ '";Extended Properties=dBase 5.0;Persist Security Info=False';
DbfTmp := 'zjftmp.dbf';
DbfTemp := 'joetmp.dbf';
end;
procedure TForm1.BtnExitClick(Sender: TObject);
begin
Form1.Close;
end;
procedure TForm1.BtnE2JcClick(Sender: TObject);
begin
if not OpenDialogFun('xls') then exit;
EnableButton(false);
if RBRb.Checked then
E2Jc(true,0);
if RBHg.Checked then
E2Jc(true,1);
EnableButton(true);
end;
procedure TForm1.BtnJcdnb2TxtClick(Sender: TObject);
begin
if not OpenDialogFun('dbf') then exit;
EnableButton(false);
Jcdnb2Txt('');
EnableButton(true);
end;
procedure TForm1.BtnE2NDClick(Sender: TObject);
begin
if not OpenDialogFun('xls') then exit;
EnableButton(false);
E2ND();
EnableButton(true);
end;
procedure TForm1.BtnE2TxtClick(Sender: TObject);
begin
if not OpenDialogFun('xls') then exit;
EnableButton(false);
E2Txt();
EnableButton(true);
end;
procedure TForm1.BtnAllDoneClick(Sender: TObject);
begin
if not OpenDialogFun('xls') then exit;
EnableButton(false);
E2OD();
E2ND();
if RBRb.Checked then
E2Jc(true,0);
if RBHg.Checked then
E2Jc(true,1);
EnableButton(true);
end;
end.
?? 快捷鍵說明
復制代碼
Ctrl + C
搜索代碼
Ctrl + F
全屏模式
F11
切換主題
Ctrl + Shift + D
顯示快捷鍵
?
增大字號
Ctrl + =
減小字號
Ctrl + -