导出EXCEL

function ExportExcel(DBGridEh1: TDBGridEh;aTitle: string): Boolean;
var
  Book: IXLSWorkbook;
  ws: IXLSWorksheet;
  iSel,iCol,iCol1, iRow: Integer;
  SaveDialog1: TSaveDialog;
  function GetDbgridEHColumnsCount: Integer;
  var
    i: Integer;
  begin
    Result := 0;
    for i := 0 to DBGridEh1.Columns.Count - 1 do
    begin
      if DBGridEh1.Columns.Items[i].Visible then
      begin
        Inc(Result);
      end;
    end;
  end;
begin
  SaveDialog1 := TSaveDialog.Create(nil);
  SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';
  try
    DBGridEh1.DataSource.DataSet.DisableControls;
    iSel := DBGridEh1.DataSource.DataSet.RecNo;
    if SaveDialog1.Execute then
    begin
      try
        Book := TXLSWorkbook.Create;
        ws := Book.Sheets.Add;
        ws.Name := '导出数据';
        iCol := GetDbgridEHColumnsCount;
        iRow := DBGridEh1.DataSource.DataSet.RecordCount;
        //单据头
        with ws.RCRange[2, 2, 2, iCol + 1] do
        begin
          Interior.ColorIndex := 9;
          Font.ColorIndex := 2;
          VerticalAlignment := xlVAlignCenter;
          HorizontalAlignment := xlHAlignCenter;
          RowHeight := 22; 
        end;

        //表格样式
        with ws.RCRange[2, 2,iRow+2, iCol +1] do
        begin
          Borders.Color := 9;
        end;

        //标题
        with ws.RCRange[1, 2, 1, iCol+ 1] do
        begin
          Merge(false);
          HorizontalAlignment := xlHAlignCenter;
          VerticalAlignment := xlVAlignCenter;
          RowHeight := 30;
          Font.Bold := True;
          Font.Size := Font.Size + 3;
        end;
  
        with DBGridEh1 do
        begin
          iCol1 := 0;
          for iCol := 0 to DBGridEh1.Columns.Count - 1 do
          begin
            if DBGridEh1.Columns.Items[iCol].Visible then
            begin
              ws.Cells.Item[2, iCol1+2].Value := DBGridEh1.Columns.Items[iCol].Title.Caption;
              ws.RCRange[2,iCol1+2,2,iCol1 + 2].ColumnWidth := Trunc(DBGridEh1.Columns.Items[iCol].Width* 0.1433);
              Inc(iCol1);
            end;
          end;
        end;
        iCol := 0;

        DBGridEh1.DataSource.DataSet.First;
        with DBGridEh1.DataSource.DataSet do
        begin
          for iRow := 0 to RecordCount - 1 do
          begin
            iCol1 := 0;
            for iCol := 0 to DBGridEh1.Columns.Count  - 1 do
            begin
              if DBGridEh1.Columns.Items[iCol].Visible then
              begin 
                ws.Cells.Item[iRow+3, iCol1+2].Value := FieldByName(DBGridEh1.Columns.Items[iCol].FieldName).Text;
                Inc(iCol1);
              end; 
            end;
            Next;
          end;
        end;

        ws.Cells.Item[1, 2].Value := aTitle;
        Book.SaveAs(SaveDialog1.FileName + '.xls');
        Book := nil; 
        if MessageBox(0,'导出成功，是否要打开此文档？','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then
        begin
          ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);
        end;
      except 
      end;
    end;
  finally
    DBGridEh1.DataSource.DataSet.RecNo := iSel;
    DBGridEh1.DataSource.DataSet.EnableControls;
    FreeAndNil(SaveDialog1);
  end;
end;

function ExportExcel(aAdoquery: TADOQuery;aTitle: string): Boolean;
var
  Book: IXLSWorkbook;
  ws: IXLSWorksheet;
  iCol, iRow: Integer;
  SaveDialog1: TSaveDialog;
begin
  SaveDialog1 := TSaveDialog.Create(nil);
  SaveDialog1.Filter := 'Excel文件(*.xls)|*.xls';
  try
    if SaveDialog1.Execute then
    begin
      Result := False;
      try
        Book := TXLSWorkbook.Create;
        ws := Book.Sheets.Add;
        ws.Name := '导出数据';
        iCol := aAdoquery.FieldCount;
        iRow := aAdoquery.RecordCount;
        //单据头
        with ws.RCRange[2, 2, 2, iCol + 1] do
        begin
          Interior.ColorIndex := 9;
          Font.ColorIndex := 2;
          VerticalAlignment := xlVAlignCenter;
          HorizontalAlignment := xlHAlignCenter;
          RowHeight := 22; 
        end;

        //表格样式
        with ws.RCRange[2, 2,iRow+2, iCol +1] do
        begin
          Borders.Color := 9;
        end;

        //标题
        with ws.RCRange[1, 2, 1, iCol+ 1] do
        begin
          Merge(false);
          HorizontalAlignment := xlHAlignCenter;
          VerticalAlignment := xlVAlignCenter;
          RowHeight := 30;
          Font.Bold := True;
          Font.Size := Font.Size + 3;
        end;
  
        with aAdoquery do
        begin
          for iCol := 0 to FieldCount - 1 do
          begin
            ws.Cells.Item[2, iCol+2].Value := Fields[iCol].DisplayName;
      //      ws.RCRange[2,icol+2,2,iCol + 2].ColumnWidth :=  Fields[iCol].DisplayWidth;
          end;
        end;
    
        with aAdoquery do
        begin
          for iRow := 0 to RecordCount - 1 do
          begin
            for iCol := 0 to FieldCount - 1 do
            begin
              ws.Cells.Item[iRow+3, iCol+2].Value :=Fields[iCol].Text;
            end;
            Next;
          end;
        end;

        ws.Cells.Item[1, 2].Value := aTitle;
        Book.SaveAs(SaveDialog1.FileName + '.xls');
        Book := nil;
        Result := True;
        if MessageBox(0,'导出成功，是否要打开此文档？','询问',MB_YESNO + MB_ICONQUESTION) = IDYES then
        begin
          ShellExecute(0,'open',PAnsiChar(SaveDialog1.FileName + '.xls'),nil,nil,SW_NORMAL);
        end;
      except 
      end;
    end;
  finally
    FreeAndNil(SaveDialog1);
  end;
end;