delphi使用ado导出excel

--需要的头文件Comobj,shellAPI,Excel2000,添加保存对话框 object SaveDialogExport: TSaveDialog
--
 

var
  FileName, s: String;
  xlapp:TexcelApplication;
  xlWorkbook:TExcelWorkbook;
  xlWorksheet:TExcelWorksheet;
begin
  //导出
  if Self.cx_criticalpath.DataController.RecordCount = 0 then
  begin
    s := '没有导出数据...';
    Application.MessageBox(PChar(s), PChar(Application.Title),
      MB_OK or MB_ICONERROR);
    Exit;
  end;

  SaveDialogExport.Filter := 'Excel文件 (*.xls)';
  SaveDialogExport.Title := '导出为';

  if not SaveDialogExport.Execute then
    Exit;

  FileName := SaveDialogExport.FileName;
  Application.ProcessMessages;

  try
    xlApp := TExcelApplication.Create(self);
    xlWorkbook := TExcelWorkbook.create(self);
    xlworksheet := Texcelworksheet.create(self);
  except
    Application.MessageBox('无法创建Excel文件, 请确认是否安装了Excel软件',
      PChar(Application.Title), MB_OK + MB_ICONWarning);
    Exit;
  end;
  Screen.Cursor := crHourGlass;
  Application.ProcessMessages;
  xlapp.Connect;
  xlapp.Workbooks.Add(null,0);
  xlWorkbook.ConnectTo(xlapp.Workbooks[1]);
  xlWorksheet.ConnectTo(xlWorkbook.Sheets[1] as _Worksheet);

//设置列宽。。。
  xlapp.Range['a1', 'a4'].ColumnWidth := 15;
  xlapp.Range['b1', 'b4'].ColumnWidth := 15;
  xlapp.Range['c1', 'c4'].ColumnWidth := 15;
  xlapp.Range['d1', 'd4'].ColumnWidth := 15;
  xlapp.Range['e1', 'e4'].ColumnWidth := 15;
  xlapp.Range['f1', 'f4'].ColumnWidth := 20;
  xlapp.Range['g1', 'g4'].ColumnWidth := 20;

  xlWorksheet.cells.Item[1,1] := qry_critical.Fields[0].FieldName;
  xlWorksheet.Cells.Item[1,1].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,1].Font.bold := true;
  xlWorksheet.Cells.Item[1,1].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,1].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,1].font.size := 11;

  xlWorksheet.cells.Item[1,2] := qry_critical.Fields[9].FieldName;
  xlWorksheet.Cells.Item[1,2].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,2].Font.bold := true;
  xlWorksheet.Cells.Item[1,2].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,2].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,2].font.size := 11;

  xlWorksheet.cells.Item[1,3] := qry_critical.Fields[10].FieldName;
  xlWorksheet.Cells.Item[1,3].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,3].Font.bold := true;
  xlWorksheet.Cells.Item[1,3].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,3].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,3].font.size := 11;

  xlWorksheet.cells.Item[1,4] := qry_critical.Fields[11].FieldName;
  xlWorksheet.Cells.Item[1,4].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,4].Font.bold := true;
  xlWorksheet.Cells.Item[1,4].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,4].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,4].font.size := 11;

  xlWorksheet.cells.Item[1,5] := qry_critical.Fields[12].FieldName;
  xlWorksheet.Cells.Item[1,5].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,5].Font.bold := true;
  xlWorksheet.Cells.Item[1,5].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,5].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,5].font.size := 11;

  xlWorksheet.cells.Item[1,6] := qry_critical.Fields[13].FieldName;
  xlWorksheet.Cells.Item[1,6].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,6].Font.bold := true;
  xlWorksheet.Cells.Item[1,6].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,6].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,6].font.size := 11;

  xlWorksheet.cells.Item[1,7] := qry_critical.Fields[14].FieldName;
  xlWorksheet.Cells.Item[1,7].Font.name := '宋体';
  xlWorksheet.Cells.Item[1,7].Font.bold := true;
  xlWorksheet.Cells.Item[1,7].orientation := xlhorizontal;
  xlWorksheet.Cells.Item[1,7].verticalAlignment := xlTop;
  xlWorksheet.Cells.Item[1,7].font.size := 11;

  qry_critical.First;
  n := 2;
  while not qry_critical.eof do
  begin
    xlWorksheet.cells.Item[n,1] := qry_critical.Fields[0].AsString;
    xlWorksheet.Cells.Item[n,1].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,1].Font.bold := true;
    xlWorksheet.Cells.Item[n,1].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,1].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,1].font.size := 11;
    xlWorksheet.Cells.Item[n,1].wraptext := true;

    xlWorksheet.cells.Item[n,2] := qry_critical.Fields[9].AsString;
    xlWorksheet.Cells.Item[n,2].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,2].Font.bold := true;
    xlWorksheet.Cells.Item[n,2].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,2].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,2].font.size := 11;
    xlWorksheet.Cells.Item[n,2].wraptext := true;

    xlWorksheet.cells.Item[n,3] := qry_critical.Fields[10].AsString;
    xlWorksheet.Cells.Item[n,3].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,3].Font.bold := true;
    xlWorksheet.Cells.Item[n,3].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,3].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,3].font.size := 11;
    xlWorksheet.Cells.Item[n,3].wraptext := true;

    xlWorksheet.cells.Item[n,4] := qry_critical.Fields[11].AsString;
    xlWorksheet.Cells.Item[n,4].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,4].Font.bold := true;
    xlWorksheet.Cells.Item[n,4].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,4].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,4].font.size := 11;
    xlWorksheet.Cells.Item[n,4].wraptext := true;

    xlWorksheet.cells.Item[n,5] := qry_critical.Fields[12].AsString;
    xlWorksheet.Cells.Item[n,5].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,5].Font.bold := true;
    xlWorksheet.Cells.Item[n,5].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,5].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,5].font.size := 11;
    xlWorksheet.Cells.Item[n,5].wraptext := true;

    xlWorksheet.cells.Item[n,6] := qry_critical.Fields[13].AsString;
    xlWorksheet.Cells.Item[n,6].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,6].Font.bold := true;
    xlWorksheet.Cells.Item[n,6].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,6].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,6].font.size := 11;
    xlWorksheet.Cells.Item[n,6].wraptext := true;

    xlWorksheet.cells.Item[n,7] := qry_critical.Fields[14].AsString;
    xlWorksheet.Cells.Item[n,7].Font.name := '宋体';
    xlWorksheet.Cells.Item[n,7].Font.bold := true;
    xlWorksheet.Cells.Item[n,7].orientation := xlhorizontal;
    xlWorksheet.Cells.Item[n,7].verticalAlignment := xlTop;
    xlWorksheet.Cells.Item[n,7].font.size := 11;
    xlWorksheet.Cells.Item[n,7].wraptext := true;

    inc(n);
    qry_critical.Next;
  end;
  xlWorksheet.cells.Item[n+qry_critical.recordcount,1] := '';
  xlWorkbook.SaveCopyAs(FileName);
  xlapp.Quit;
  xlWorksheet.Disconnect;
  xlWorkbook.Disconnect;
  xlapp.Disconnect;
  FreeAndNil(xlWorkbook);
  FreeAndNil(xlWorksheet);
  FreeAndNil(xlapp);

  Screen.Cursor := crDefault;
  Application.ProcessMessages;
  s := '导出完成...';
  Application.MessageBox(PChar(s), PChar(Application.Title),
    MB_OK or MB_IconInformation);

本文转自鹅倌51CTO博客，原文链接：http://blog.51cto.com/kaixinbuliao/1104876 ，如需转载请自行联系原作者