unit1.pas

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

                    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;