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

最新推荐文章于 2024-07-15 16:36:53 发布

vniceboy

最新推荐文章于 2024-07-15 16:36:53 发布

阅读量75

点赞数

分类专栏：学习历程文章标签： Excel J#

学习历程专栏收录该内容

8 篇文章 0 订阅

订阅专栏

其实前面提到的导出数据到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;