Delphi导出Excel

要求：将下列格式的数据导出到Excel中（横竖混合排列），并使其适合打印。

 

原始数据：

 

最终效果：

 

代码：

procedure TfamMain.ImportTOExcel;
var
  i,iRow,tRow,iCol,Max:integer;
  eclApp2:Variant;
  strcpd:String;
begin
//下载模板
    DownFile.URLHost := PubModule.URLHost;//获取路径
    DownFile.Organization := PubModule.Params.ParamByName('ORGANIZATION_ID').AsString;//参数
    DownFile.SaveFileName := GetTempDirectory + 'CPD配置信息.xls';//重命名
    DownFile.ServerFileName :='CPDConfigerTwo.xls';//模板
    DownFile.DisplaySuccess := false;
    DownFile.DownloadCFG := 'TemplateDownloadCfg.xml';
    if DownFile.Download = false then
      Exit;
    try
      try
        eclApp2 := CreateOleObject('Excel.Application');//创建进程
        eclApp2.WorkBooks.Open(GetTempDirectory + 'CPD配置信息.xls');//打开excel
      except
        messagebox(handle, '您的机器里可能未安装Microsoft Excel或异常', '无法生成', MB_OK or MB_ICONWARNING);
        Exit;
      end;
    //打开Excel后需要根据产品行信息的数量建立多个工作薄
    for i := 2 to cdsMain.RecordCount do
      eclApp2.Sheets.Add;
    //循环复制模版格式到新增加的工作表中
    for i := 1 to cdsMain.RecordCount do
      if i <> cdsMain.RecordCount then
      begin
        eclApp2.worksheets[cdsMain.RecordCount].select;
        eclApp2.Cells.select;
        eclApp2.Selection.Copy;
        eclApp2.worksheets[i].select;
        eclApp2.ActiveSheet.Paste;
      end;
    i := 1;
    begin
      cdsMain.First;//主数据源--写多个工作表
      while not cdsMain.Eof do
      begin
         cdsDetail.DisableControls;//从数据源--填充数据到工作表
         cdsDetail.Active :=false;
         cdsDetail.Params.ParamByName('PART_NUMBER').AsString := cdsMainMRP_ITEM_CODE.AsString;
         cdsDetail.Active :=true;
         cdsDetail.EnableControls;
         cdsDetail.IndexFieldNames :='GROUPNAME';//排序字段
         eclapp2.WorkSheets[i].cells[2, 1].value := '当前成品编码：'+ cdsMainMRP_ITEM_CODE.AsString ;
         eclapp2.WorkSheets[i].cells[2, 3].value := '生产单号：' + DBText3.Caption + '(' + cdsMainLINE_NO.AsString + '/' +
           IntToStr(cdsMain.RecordCount) + ')' + '  业务员:' + cdsMasterUSER_NAME.AsString;
         eclApp2.worksheets[i].Name := '(' + cdsMainLINE_NO.AsString + ')';
         tRow :=3;//标题行
      iRow :=tRow+1;//数据行
         Max :=iRow;//最大行
         iCol :=1;//列
         cdsDetail.First;
         strcpd :=cdsDetail.fieldbyname('GROUPNAME').asstring;//列标题
         while not cdsDetail.Eof do
         begin
           //在组名称相同的情况下写该组下的行数据
          if  cdsDetail.fieldbyname('GROUPNAME').asstring = strcpd then
              begin
                eclapp2.WorkSheets[i].cells[tRow, iCol].value := cdsDetail.fieldbyname('GROUPNAME').asstring ;//写组名-列标题
                eclapp2.WorkSheets[i].cells[iRow, iCol].value := cdsDetail.fieldbyname('ATTR_NUMBER').asstring +
                   ':' + cdsDetail.fieldbyname('ATTR_VALUE').asstring;//写属性名跟属性值-行数据
                INC(iRow);//加一
                //设置excel格式
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].RowHeight := 30;//行高
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].font.bold := true;//加粗
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].font.size := 14;//字体大小
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].borders.linestyle :=1;//边框
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [iRow])].font.size := 10;
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [iRow])].borders.linestyle :=1;
              end
           else //分组换列
              begin
                if iRow>Max then
       Max :=iRow;
                if iCol>3 then
          begin
       tRow :=Max+1;
            iCol :=0;
                    end;
                iRow :=tRow+1;
                eclapp2.WorkSheets[i].cells[tRow, iCol+1].value := cdsDetail.fieldbyname('GROUPNAME').asstring ;//写组名
                eclapp2.WorkSheets[i].cells[iRow, iCol+1].value := cdsDetail.fieldbyname('ATTR_NUMBER').asstring +
                   ':' + cdsDetail.fieldbyname('ATTR_VALUE').asstring; //写属性名跟属性值
                INC(iRow);
                INC(iCol);
                //设置excel格式
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].RowHeight := 30;//行高
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].font.bold := true;//字体加粗
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].font.size := 14;//字体大小
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [tRow])].borders.linestyle :=1;//边框样式
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [iRow-1])].font.size := 10;
                eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [iRow-1])].borders.linestyle :=1;
              end ;
           strcpd :=cdsDetail.fieldbyname('GROUPNAME').asstring;
           cdsDetail.Next;
         end;
        eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [Max])].VerticalAlignment :=xlCenter;//垂直对齐
        eclApp2.worksheets[i].Range[Format('A%d:D%0:d', [Max])].WrapText := True ;//自动换行
        //页面设置--方便打印
        eclApp2.worksheets[i].PageSetup.PrintArea := Format('A1:D%d',[Max]);//打印区域
        eclApp2.worksheets[i].PageSetup.LeftMargin := 10 ;//左边距
        eclApp2.worksheets[i].PageSetup.RightMargin := 10 ;//右边距
        eclApp2.worksheets[i].PageSetup.TopMargin := 10 ;//上边距
        eclApp2.worksheets[i].PageSetup.BottomMargin := 10 ;//下边距
        eclApp2.worksheets[i].PageSetup.CenterHorizontally := True ;//水平居中
        eclApp2.worksheets[i].PageSetup.CenterVertically := True;//垂直居中
        eclApp2.worksheets[i].PageSetup.Orientation := xlLandscape;//横向
        i:=i+1;//切换工作表
        cdsMain.Next;
       end;
    end;
      messagebox(handle, '数据导入Excel成功', '生成成功', MB_OK or MB_ICONWARNING);
      eclApp2.visible := True;//excel可见
      Application.BringToFront; //将应用程序提前
    finally
      eclApp2 := Unassigned;
    end;
end;