将dataset中的数据导出至Excel中而不需要安装MS Excel的方法（含UNICODE支持）_//调用该方法可将dataset中的数据导出到excel-CSDN博客

本文链接：https://blog.csdn.net/genispan/article/details/9259247

偶尔做界面程序，需要一个导出Excel，而在客户端又不用安装MS Excel的方法，总结如下。

测试了两种方法，第一种方法如下（此方法支持UNICODE不存在问题）：

参考：http://www.swissdelphicenter.ch/torry/showcode.php?id=1427

procedure DBGridToExcelADO(Query: TDataSet; FileName: string; SheetName: string);
var
  cat: _Catalog;
  tbl: _Table;
  col: _Column;
  i: integer;
  ADOConnection: TADOConnection;
  ADOQuery: TADOQuery;
begin
  if FileExists(FileName) then  // It's better to delete the file first, or there may be a "external table is not in the expected format" error. by genispan
      DeleteFile(FileName);
  //WorkBook creation (database)
  cat := CoCatalog.Create;
  //cat._Set_ActiveConnection
  cat.Set_ActiveConnection('Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0');
  //WorkSheet creation (table)
  tbl := CoTable.Create;
  tbl.Set_Name(SheetName);
    //Columns creation (fields)
  Query.First;
  with Query.Fields do
    begin
      for i := 0 to Count - 1 do
      begin
          col := nil;
          col := CoColumn.Create;
          with col do
            begin
              Set_Name(Query.Fields[i].FieldName);
              Set_Type_(adVarWChar);
            end;
          //add column to table
          tbl.Columns.Append(col, adVarWChar, 20);
      end;
    end;
  //add table to database
  cat.Tables.Append(tbl);

  col := nil;
  tbl := nil;
  cat := nil;

  ADOConnection := TADOConnection.Create(nil);
  ADOConnection.LoginPrompt := False;
  ADOConnection.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';
  ADOQuery := TADOQuery.Create(nil);
  ADOQuery.Connection := ADOConnection;
  ADOQuery.SQL.Text := 'Select * from [' + SheetName + '$]';
  ADOQuery.Open;
  try
  with Query do
    begin
      First;
      while not Eof do
        begin
          ADOQuery.Append;
          with Query.Fields do
            begin
              ADOQuery.Edit;
              for i := 0 to Count - 1 do
                  ADOQuery.FieldByName(Query.Fields[i].FieldName).AsString := FieldByName(Query.Fields[i].FieldName).AsString;
              ADOQuery.Post;
            end;
          Next;
        end;
    end;

  finally
      ADOQuery.Close;
      ADOConnection.Close;
      ADOQuery.Free;
      ADOConnection.Free;
  end;
end;

第二种方法，此方法效率更高，但导出UNICODE字符串存在问题，如有高手看到可留言帮助解决下，以下为整理好了的pas单元源码：

unit uExcel;

interface

Uses
    DB, Classes, Dialogs,DBGrids,Controls;
var
    CXlsBof: array[0..5] of Word = ($809, 8, 0, $10, 0, 0);
    //OPCode, size, codepage
    CXlsCodePage: array[0..2] of Word = ($0042, $0002, $04B0);
    CXlsEof: array[0..1] of Word = ($0A, 00);
    CXlsLabel: array[0..5] of Word = ($204, 0, 0, 0, 0, 0);
    CXlsNumber: array[0..4] of Word = ($203, 14, 0, 0, 0);
    CXlsRk: array[0..4] of Word = ($27E, 10, 0, 0, 0);
    CXlsBlank: array[0..4] of Word = ($201, 6, 0, 0, $17);


Type
TDS2Excel = Class(TObject)
Private
  FCol: word;
  FRow: word;
  FDataSet: TDataSet;
  FDbGrid  :TDbGrid;
  Stream: TStream;
  FWillWriteHead: boolean;
  FBookMark: TBookmark;
  procedure IncColRow;
  procedure WriteBlankCell;
  procedure WriteFloatCell(const AValue: Double);
  procedure WriteIntegerCell(const AValue: Integer);
  procedure WriteStringCell(const AValue: string);
  procedure WritePrefix;
  procedure WriteSuffix;
  procedure WriteTitle;
  procedure WriteDataCell;

  procedure Save2Stream(aStream: TStream);
Public
  procedure Save2File(FileName: string; WillWriteHead: Boolean);
  procedure Save2Files(WillWriteHead: Boolean);
  Constructor Create(aDataSet: TDataSet;aDBGrid:TDBGrid);
end;

implementation

uses SysUtils;

Constructor TDS2Excel.Create(aDataSet: TDataSet;aDBGrid:TDBGrid);
begin
    inherited Create;
    FDataSet := aDataSet;
    FDbGrid  :=aDbGrid;
end;


procedure TDS2Excel.IncColRow;
begin
if FDbGrid <>nil then
begin
    if FCol = FDbGrid.Columns.Count - 1 then
    begin
      Inc(FRow);
      FCol :=0;
    end
    else
      Inc(FCol);
end else
begin
    if FCol = FDataSet.FieldCount - 1 then
    begin
      Inc(FRow);
      FCol :=0;
    end
    else
      Inc(FCol);
end;
end;

procedure TDS2Excel.WriteBlankCell;
begin
    CXlsBlank[2] := FRow;
    CXlsBlank[3] := FCol;
    Stream.WriteBuffer(CXlsBlank, SizeOf(CXlsBlank));
    IncColRow;
end;

procedure TDS2Excel.WriteFloatCell(const AValue: Double);
begin
    CXlsNumber[2] := FRow;
    CXlsNumber[3] := FCol;
    Stream.WriteBuffer(CXlsNumber, SizeOf(CXlsNumber));
    Stream.WriteBuffer(AValue, 8);
    IncColRow;
end;

procedure TDS2Excel.WriteIntegerCell(const AValue: Integer);
var
    V: Integer;
begin
    CXlsRk[2] := FRow;
    CXlsRk[3] := FCol;
    Stream.WriteBuffer(CXlsRk, SizeOf(CXlsRk));
    V := (AValue shl 2) or 2;
    Stream.WriteBuffer(V, 4);
    IncColRow;
end;

procedure TDS2Excel.WriteStringCell(const AValue: string);
var
    L: Word;
     _str : AnsiString;
begin
    _str := AnsiString(AValue);  // in delphi XE, there will be error for unicode, fix me !!!!!!!!!!!  --by genispan
    L := Length(_str);
    CXlsLabel[1] := 8 + L;
    CXlsLabel[2] := FRow;
    CXlsLabel[3] := FCol;
    CXlsLabel[5] := L;
    Stream.WriteBuffer(CXlsLabel, SizeOf(CXlsLabel));
    Stream.WriteBuffer(Pointer(_str)^, L);
    IncColRow;
end;

procedure TDS2Excel.WritePrefix;
begin
  Stream.WriteBuffer(CXlsBof, SizeOf(CXlsBof));
  Stream.WriteBuffer(cxlscodepage, SizeOf(cxlscodepage));
end;

procedure TDS2Excel.WriteSuffix;
begin
  Stream.WriteBuffer(CXlsEof, SizeOf(CXlsEof));
end;

procedure TDS2Excel.WriteTitle;
var
   n: word;
begin
    if FDbGrid <> nil then
    for n := 0 to FDBGrid.Columns.Count - 1 do
      WriteStringCell(FDBGrid.Columns[n].Title.Caption)
    else
    for n := 0 to FDataSet.FieldCount - 1 do
      WriteStringCell(FDataSet.Fields[n].FieldName);
end;

procedure TDS2Excel.WriteDataCell;
var
  n: word;
begin
  WritePrefix;
  if FWillWriteHead then WriteTitle;
  FDataSet.DisableControls;
  FBookMark := FDataSet.GetBookmark;
  FDataSet.First;

  if FDbGrid=nil then
  begin
    while not FDataSet.Eof do
    begin
    for n := 0 to FDataSet.FieldCount - 1 do
    begin
      try
      if FDataSet.Fields[n].IsNull then
        WriteBlankCell
      else begin
        case FDataSet.Fields[n].DataType of
          ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
              WriteIntegerCell(FDataSet.Fields[n].AsInteger);
          ftFloat, ftCurrency, ftBCD:
              WriteFloatCell(FDataSet.Fields[n].AsFloat);
          ftTypedBinary:
        else
          WriteStringCell(FDataSet.Fields[n].AsString);
        end;
      end;
      except
        WriteBlankCell;
      end;
    end;
        FDataSet.Next;
    end;
  end
  else
  begin
    while not FDbGrid.DataSource.DataSet.Eof do
    begin
      for n := 0 to FDbGrid.Columns.Count - 1 do
      begin
        if FDbGrid.Columns[n].Field.IsNull then
          WriteBlankCell
        else begin
          case FDbGrid.Columns[n].Field.DataType of
            ftSmallint, ftInteger, ftWord, ftAutoInc, ftBytes:
                WriteIntegerCell(FDbGrid.Columns[n].Field.AsInteger);
            ftFloat, ftCurrency, ftBCD:
                WriteFloatCell(FDbGrid.Columns[n].Field.AsFloat);
          else
            WriteStringCell(FDbGrid.Columns[n].Field.AsString);
          end;
        end;
      end;
      FDbGrid.DataSource.DataSet.Next
    end;
  end;

  WriteSuffix;
  if FDataSet.BookmarkValid(FBookMark) then FDataSet.GotoBookmark(FBookMark);
  FDataSet.EnableControls;
end;

procedure TDS2Excel.Save2Stream(aStream: TStream);
begin
    FCol := 0;
    FRow := 0;
    Stream := aStream;
    WriteDataCell;
end;

procedure TDS2Excel.Save2File(FileName: string; WillWriteHead: Boolean);
var
    aFileStream: TFileStream;
begin
    FWillWriteHead := WillWriteHead;
    if FileExists(FileName) then DeleteFile(FileName);
    aFileStream := TFileStream.Create(FileName, fmCreate);
    Try
      Save2Stream(aFileStream);
    Finally
      aFileStream.Free;
    end;
end;

procedure TDS2Excel.Save2FileS(WillWriteHead: Boolean);
var
    SaveDialog11: TSaveDialog;
begin
    SaveDialog11 := TSaveDialog.Create(nil);
    Try
      SaveDialog11.Filter := 'Excel|*.xls';
      SaveDialog11.InitialDir := 'C:\';
      SaveDialog11.FileName:='*.xls';
      if not SaveDialog11.Execute then exit;
      if FileExists(SaveDialog11.FileName) then DeleteFile(SaveDialog11.FileName);
      Save2File(SaveDialog11.FileName, WillWriteHead);
    Finally
      SaveDialog11.Free;
    end;
end;

end.