向已存Excel文件中导入数据小结

其实前面提到的导出数据到Excel文件中的写入流方法都是新建状态下的Excel文件，重新定位文件头和文件尾，会的损坏原文件。

假如有一个已存Excel文件xlsFile.xls中，已有其它数据，只需向其中添加一引起数据，显然在前几种方法难解决。

 

现有办法解决，效率相对还过得去（还可以向其中写Excel公式）：

大体方法以描述：定义一个 lst :TstringList ，向其中添加要写入Excel文件的内容。每列以#9表格控制符结尾。

然后把数据先写到剪切板上 clipboard.astext := lst.Text;

然后打开已存xlsFile.xls,定位要写入数据的cell，shtSheet.Paste;

 

 

如下代码：

var

   省。。。。

 

tmpstr:= ExtractFilePath(Application.ExeName) +  '漂染车间'+exdtdtProductDate.Text+'生产排程.xls';

  try
    exclplctnApp.Connect;
  except
    on E: Exception do
    begin
      E.Message := '无法连接Excel';
      Raise;
    end;
  end;
  exclplctnApp.Visible[0] := False;

  exclwrkbkBook.ConnectTo(exclplctnApp.Workbooks.Open(tmpstr,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,EmptyParam,0));
  exclwrkshtSheet.ConnectTo(exclwrkbkBook.Sheets[1] as _Worksheet);

  (exclplctnApp.Worksheets.Item[1] as _WorkSheet).Activate(0);//激活worksheet
  //下面写入数据

  lst := TStringList.Create;
  try
    try
      dsTastList.DisableControls;
      dsTastList.First;
      n:= 21;
      for i:= 1 to dsTastList.RecordCount  do
      begin
        s_tmp := EmptyStr;

        tmpstr:= '=IF(O22="**",F22&K22&O22&N22,IF(OR(K22="黑色",K22="白色"'+
        ',K22="原纱克"),F22&K22,IF(OR(K22<>"黑色",K22<>"白色",K22<>"原纱克"),F22&K22&O22,"")))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr + #9;  //名称引用

        tmpstr:= '=IF(G22="车间自排",F22&G22&K22,IF(D22<>"",F22&G22,""))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr + #9;//缸台引用

        tmpstr:= '=O22&S22';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr + #9;//颜色引用

        tmpstr:= '=IF(OR(I22<=0,I22=""),"",IF(AND(LEFT(Q22,2)="TD",I22<0.8),"小缸",'+
        'IF(AND(LEFT(Q22,2)<>"TD",I22<2.5),"401",IF((I22<10.5),"301",IF((I22<31.5)'+
        ',"107",IF((I22<42),"106",IF((I22<63),"105",IF((I22<73.5),"205","104"))))))))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9;  //缸台整理

        tmpstr:= '=IF(D22<>"",1,"")';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9; //轮数

        s_tmp := s_tmp + dsTastList.FieldByName('WD_P_DATE').AsString + #9; //生产日期

        tmpstr:= '=IF(AND(I22<>"")*OR(K22="黑色",K22="白色",K22="原纱克"),"车间自排",'+
        'IF(D22<>"104",D22,IF(I22<100,"104",IF(I22<220,"108",IF(I22<320,"103",'+
        'IF(I22<440,"102",IF(I22<640,"101","分批")))))))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9;//生产缸台

        tmpstr:= '=IF(AND(O22="星韵",A22<>A21),SUMIF(A:A,F22&K22&O22&N22,T:T),'+
        'IF(AND(A22<>A21)*OR(K22="黑色",K22="白色",K22="原纱克"),SUMIF(A:A,'+
        'F22&K22,T:T),IF(A22<>A21,SUMIF(A:A,F22&K22&O22,T:T),"")))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        tmpstr:= StringReplace(tmpstr,'21',IntToStr(i+n-1),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9;//生产数量

        tmpstr:= '=IF(AND(O22="星韵",A22<>A21),SUMIF(A:A,F22&K22&O22&N22,J:J),'+
        'IF(AND(A22<>A21)*OR(K22="黑色",K22="白色",K22="原纱克"),'+
        'SUMIF(A:A,F22&K22,J:J),IF(A22<>A21,SUMIF(A:A,F22&K22&O22,J:J),"")))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        tmpstr:= StringReplace(tmpstr,'21',IntToStr(i+n-1),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9;  //引用数量

        tmpstr:= '=IF(LEFT(Q22,2)="TD",T22*1.5,T22)';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9; //未完成数

 

        s_tmp := s_tmp + dsTastList.FieldByName('C_COLOR_Z').AsString + #9; //类别
        s_tmp := s_tmp + dsTastList.FieldByName('S_S_TYPE').AsString + #9; //类别
        s_tmp := s_tmp + dsTastList.FieldByName('WH_NO').AsString + #9; //生产单号
        s_tmp := s_tmp + dsTastList.FieldByName('WD_S_NO').AsString + #9; //订单号
        s_tmp := s_tmp + dsTastList.FieldByName('WD_CV_NAME').AsString + #9; //客户
        s_tmp := s_tmp + dsTastList.FieldByName('WH_URGENCY').AsString + #9; //紧急程度
        s_tmp := s_tmp + dsTastList.FieldByName('WD_P_CODE').AsString + #9; //物料编号
        s_tmp := s_tmp + dsTastList.FieldByName('WD_P_NAME').AsString + #9; //物料名称
        s_tmp := s_tmp + dsTastList.FieldByName('WD_COL_NAME').AsString + #9; //颜色
        s_tmp := s_tmp + dsTastList.FieldByName('WD_S_QTY').AsString + #9; //订单数量
        s_tmp := s_tmp + dsTastList.FieldByName('WD_MUST_IN_QTY').AsString + #9; //指定入库
        s_tmp := s_tmp + dsTastList.FieldByName('WD_U_CODE').AsString+ #9; //主单位
        s_tmp := s_tmp + dsTastList.FieldByName('WD_U2_QTY').AsString + #9; //次数量
        s_tmp := s_tmp + dsTastList.FieldByName('WD_U2_CODE').AsString + #9; //次单位
        s_tmp := s_tmp + dsTastList.FieldByName('WD_YQ_DATE').AsString+ #9; //生产交期
        s_tmp := s_tmp + dsTastList.FieldByName('WD_JQ_IS_WAIT').AsString+ #9; //交期待定
        s_tmp := s_tmp + dsTastList.FieldByName('S_STATUS').AsString+ #9; //状态
        s_tmp := s_tmp + DateToStr(Now) + #9;  //处理日期

        tmpstr:= '=IF(A22="","",VLOOKUP(A22,$A$21:$G$2360,7,FALSE))';
        tmpstr:= StringReplace(tmpstr,'22',IntToStr(i+n),[rfReplaceAll]);
        s_tmp := s_tmp + tmpstr+ #9; //缸台号

        s_tmp := s_tmp +  '' + #9; //车间生产反馈

        lst.Add(s_tmp);

        dsTastList.Next;
      end;

      if lst.Count > 0 then
      begin
        clipboard.astext := lst.Text;

        exclwrkshtSheet.Range['A22','A22'].Select;
        exclwrkshtSheet.Paste;
        Clipboard.Clear;
        exclplctnApp.Visible[0] := True;
      end;
    except
      exclplctnApp.Workbooks.Close(0);
      exclplctnApp.Disconnect;//关闭Excel
      exclplctnApp.Quit;
    end;
  finally
    //exclplctnApp.Columns.AutoFit;
    dsTastList.EnableControls;
    exclplctnApp.Save;
  end;