Delphi: Export to Excel

Below adapted solution is present from this discussion and this one.

I included here also export of field names as it is usually using (the first row in Excel is usually a row with field names).

To run this example you need one TButton component and one TADOTable component on your form.


procedure ExportRecordsetToMSExcel(DestName: string; Data: _Recordset);
var
  ovExcelApp: OleVariant;
  ovExcelWorkbook: OleVariant;
  ovWS: OleVariant;
  ovRange: OleVariant;
  arrData: Variant;
  i: Byte;
begin
  // uses System.Win.ComObj;
  //If Excel isnt installed will raise an exception
  ovExcelApp := CreateOleObject('Excel.Application');

  //if you want to show Excel, uncomment code below
  //ovExcelApp := GetActiveOleObject('Excel.Application');
  //ovExcelApp.Visible:=true;
  try
    ovExcelWorkbook   := ovExcelApp.WorkBooks.Add;
    // go to first worksheet
    ovWS := ovExcelWorkbook.Worksheets[1];
    ovWS.Activate;
    ovWS.Select;
    //go to first cell
    ovRange := ovWS.Range['A1', ovWS.Cells[1, Data.Fields.Count]];
    arrData := VarArrayCreate([1, Data.Fields.Count], varVariant);
    for i := 1 to Data.Fields.Count do
      arrData[i] := Data.Fields[i - 1].Name;
      //Fill first row by field names
    ovRange.Value := arrData;
    ovRange := ovWS.Range['A2', 'A2'];
    ovRange.Resize[Data.RecordCount, Data.Fields.Count];
    ovRange.CopyFromRecordset(Data, Data.RecordCount, Data.Fields.Count);
    //Copy to other rows data
    ovWS.SaveAs(DestName, 1, '', '', False, False);
  finally
    ovExcelWorkbook.Close(False);
    ovWS := Unassigned;
    ovExcelWorkbook := Unassigned;
    ovExcelApp := Unassigned;
  end;
end;

procedure TForm1.Button1Click(Sender: TObject);
begin
  ExportRecordsetToMSExcel('D:\x.xls', ADOTable1.Recordset );
end;

This entry was posted in Delphi, Posts in English and tagged , , . Bookmark the permalink.

6 Responses to Delphi: Export to Excel

  1. Chemist-i (from replace.org.ua) says:

    Great work, thank You. Why do you not use syntax highlighting?

  2. Pingback: ADO _recordset - Delphi-PRAXiS

  3. karpazir says:

    hello, thanks.
    how can i export 2 of 5 fields (no all fields)

    • patlatus says:

      You can modify lines 24 and 31 to replace Data.Fields.Count to 2. Assuming you want to export first two fields.

      ovRange := ovWS.Range[‘A1’, ovWS.Cells[1, Data.Fields.Count]];
      =>
      ovRange := ovWS.Range[‘A1’, ovWS.Cells[1, 2]];

      ovRange.Resize[Data.RecordCount, Data.Fields.Count];

      =>
      ovRange.Resize[Data.RecordCount, 2];

  4. Pingback: Export Delphi to Excel

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s