如何将一个数据记录集中的数据,导入到Excel文件中

Question

如何将一个数据记录集中的数据导入到Excel文件中

Answer 1

我是通过循环每个Cell写进去的，呵呵，不要笑我太笨。

Answer 2

抄一段做过的代码给你吧，有点乱。
procedure TForm8.PrepareReport;
var
wkBook : _WorkBook;
LCID : Integer;
wkSheet, wkSheet2: _WorkSheet;
path: string;
DSet1: TADODataSet;
i, l, x, y: integer;
tpProductID, tpYuanLiaoID: array of integer;
begin
  path:=extractfilepath(application.ExeName)+'tPFJH.xlt';
  ExcelA1.Connect;
  LCID:= GetUserDefaultLCID();
{ 打开原文件
  wkBook:= ExcelA1.WorkBooks.Open(path,EmptyParam,EmptyParam,
           EmptyParam,EmptyParam,EmptyParam,EmptyParam,
           EmptyParam,EmptyParam,EmptyParam,EmptyParam,
           EmptyParam,EmptyParam,LCID);
}
  wkBook:= ExcelA1.Workbooks.Add(path,LCID);       {通过模板文件生成新表}
  wkSheet:= wkBook.Sheets[1] as _WorkSheet;
  wkSheet2:= wkBook.Sheets[2] as _WorkSheet;
  DSet1:= TADODataSet.Create(Form8);
  try
    DSet1.Connection:= DataModuleMain.MainConnection1;
    // colume (Products) name
    DSet1.CommandText:=
     'select pfl2.[Name], [ChanLiang], pp.[PFID] from YCuPFJHsub pp '
    +'  Left join '
    +'    (select p.[name], pfl.* from YCuPFList PFL Left join YCuProduct p on p.[id] = pfl.[ProductID]) pfl2 '
    +'    on pfl2.[ID] = pp.[PFID] '
    +'where masterID = '
    + ADOTableMID;    DSet1.Active:= True;
    l:= DSet1.RecordCount-1;
    SetLength(tpProductID, l+1);
    for i:=0 to l do
    begin
      wkSheet.Cells.Item[4,(2+2*i)].Value:= DSet1.Recordset.Fields[0].Value;
      wkSheet.Cells.Item[5,(2+2*i)].Value:= DSet1.Recordset.Fields[1].Value;
      tpProductID[i]:= DSet1.Recordset.Fields[2].Value;
      DSet1.Next;
    end;    DSet1.Active:= False;       // Row (YuanLiao) name
    DSet1.CommandText:=
     'select DISTINCT y.[Name], py.[YuanLiaoID], y.[price] from YCuPFListsub py '
    +'Left outer join YCuYuanLiao y on y.[id]= YuanLiaoID '
    +'where MasterID IN (select [PFID] from YCuPFJHsub where masterID = '
    + ADOTableMID
    + ')';    DSet1.Active:= True;
    l:= DSet1.RecordCount-1;
    SetLength(tpYuanLiaoID, l+1);
    for i:=0 to l do
    begin
      wkSheet.Cells.Item[7+i,1].Value:= DSet1.Recordset.Fields[0].Value;
      wkSheet2.Cells.Item[7+i,1].Value:= DSet1.Recordset.Fields[0].Value;
      wkSheet2.Cells.Item[7+i,2].Value:= DSet1.Recordset.Fields[2].Value;
      tpYuanLiaoID[i]:= DSet1.Recordset.Fields[1].Value;
      DSet1.Next;
    end;    DSet1.Active:= False;       // Input data
    DSet1.CommandText:= 'select MasterID, YuanliaoID,YongLiang from YCuPFListsub '
                      + 'where MasterID IN (select [PFID] from YCuPFJHsub '
                      + 'where masterID = '
                      + ADOTableMID
                      + ')';
    DSet1.Active:= True;
    repeat
      for x:= 0 to High(tpProductID) do
        if tpProductID[x]= integer(DSet1.Recordset.Fields[0].Value) then
          Break;
      for y:= 0 to High(tpYuanLiaoID) do
        if integer(DSet1.Recordset.Fields[1].Value) = tpYuanLiaoID[y] then
          Break;
      wkSheet.Cells.Item[7+y,2+x*2].Value:= DSet1.Recordset.Fields[2].Value;
      DSet1.Next;
    until DSet1.Eof;{   // 如果在模板文件中设定过了，则可不用下面这段，可以提高速度。
    for i:= 7 to 59 do    //count and sum
    begin
      wkSheet.Cells.Item[i,3].Value:= '=B'+IntToStr(i)+'*B5/100';
      wkSheet.Cells.Item[i,5].Value:= '=D'+IntToStr(i)+'*D5/100';
      wkSheet.Cells.Item[i,7].Value:= '=F'+IntToStr(i)+'*F5/100';
      wkSheet.Cells.Item[i,9].Value:= '=H'+IntToStr(i)+'*H5/100';
      wkSheet.Cells.Item[i,11].Value:= '=J'+IntToStr(i)+'*J5/100';
      wkSheet.Cells.Item[i,13].Value:= '=L'+IntToStr(i)+'*L5/100';
      wkSheet.Cells.Item[i,15].Value:= '=N'+IntToStr(i)+'*N5/100';
      wkSheet.Cells.Item[i,17].Value:= '=P'+IntToStr(i)+'*P5/100';
      wkSheet.Cells.Item[i,19].Value:= '=R'+IntToStr(i)+'*R5/100';
    end;
    wkSheet.Range['T7','T59'].Value:= '=C7+E7+G7+I7+K7+M7+O7+Q7+S7';
    wkSheet.Range['B60','T60'].Value:= '=SUM(B7:B59)';
}
    wkSheet.Cells.Item[2,8].Value:= ADOTableMName;
    wkSheet.Cells.Item[2,2].Value:= ADOTableMGongGuangHao;
    wkSheet.Range['Q3','Q3'].Value2:= ADOTableMMDate;
    wkSheet.Range['B61','B61'].Value2:= ADOTableMMaker;
    wkSheet.Range['H61','H61'].Value2:= ADOTableMReChecker;
    wkSheet.Range['N61','N61'].Value2:= ADOTableMSigner;
    wkSheet.Range['B3','B3'].Value2:= ADOTableMUnit;  finally
    DSet1.Free;
  end;
  ExcelA1.Visible[0]:= True;
  Hide;
end;

Answer 3

function DataSettoexcel(Adataset: TDataSet;Allcolumns: TStringlist): boolean;
var
  excelobj, excel, workbook, sheet: olevariant;
  i, j, row, col: integer;
begin
  result := false;
  if Adataset.RecordCount = 0 then Exit;
  try
    excelobj := createoleobject('excel.sheet');
    excel := excelobj.application;
    excel.visible := true;
    workbook := excel.workbooks.add;
    sheet := workbook.sheets[1];
  except
    messagebox(getactivewindow, '无法调用mircorsoft excel! ' + chr(13) + chr(10) +
      '请检查是否安装了mircorsoft excel。', '提示', mb_ok + mb_iconinformation);
    exit;
  end;
  sheet.activate;
// 列标题
  row := 1;
  col := 1;
  Adataset.First;
  for i := 0 to Allcolumns.Count- 1 do
  begin
    sheet.cells(row, col) := Allcolumns.Strings[i];
    inc(col);
  end;
// 表内容
  for i := 0 to Adataset.RecordCount - 1 do
  begin
    row := row + 1;
    col := 1;
    for j := 0 to Allcolumns.Count - 1 do
    begin
      sheet.cells(row, col) :=Adataset.Fields[j].AsString;
      inc(col);
    end;
    Adataset.Next;
  end;
  result := true;
end;
Allcolumns是大约的表头列表