unit1.pas

用于数据格式的相互转换,可以将txt、dbf转换成excel。

            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.