Exporting data from a DBGrid to Excel

Question

I wanted to know if anyone ones a way that I can export data from a DBGrid to Excel ? I am using Delphi 7 , Excel 2007 and ADO .
Any help will be appreciated.

Do not export the DBGrid, export the dataset http://stackoverflow.com/a/16642049/1699210 just fill arrData from the given example with the field values. — bummi, Jun 12 '13 at 06:02
I too was going to give the reference that Bummi gave. In order to use the variant array approach, you have to know how many rows you have in the query. Otherwise it may be simpler to use the csv approach which I gave in that question. — No'am Newman, Jun 12 '13 at 06:04
@No'amNewman depending on the datataypes the csv approach might give more trouble in convertions e.g. DateTime values ... — bummi, Jun 12 '13 at 06:06
There's 288000 hits on a Google search for "delphi export dbgrid excel". I suggest you start using these, then if you have specific questions about code parts not working, post them here. And bummi's right; if at all possible export the underlying dataset. — Jan Doggen, Jun 12 '13 at 06:21
possible duplicate of [export delphi stringgrid to excel](http://stackoverflow.com/questions/16641897/export-delphi-stringgrid-to-excel), except you read data from the DB instead of the grid cells. — Ken White, Jun 12 '13 at 12:47

score 16 · Accepted Answer · answered Jun 12 '13 at 14:05

If you want a fast export of raw data, just export your recordset (ADODataset.recordset) with something like that:

procedure ExportRecordsetToMSExcel(DestName: string; Data: _Recordset);
var
  ovExcelApp: OleVariant;
  ovExcelWorkbook: OleVariant;
  ovWS: OleVariant;
  ovRange: OleVariant;
begin
  ovExcelApp := CreateOleObject('Excel.Application'); //If Excel isnt installed will raise an exception
  try
    ovExcelWorkbook   := ovExcelApp.WorkBooks.Add;
    ovWS := ovExcelWorkbook.Worksheets.Item[1]; // go to first worksheet
    ovWS.Activate;
    ovWS.Select;
    ovRange := ovWS.Range['A1', 'A1']; //go to first cell
    ovRange.Resize[Data.RecordCount, Data.Fields.Count];
    ovRange.CopyFromRecordset(Data, Data.RecordCount, Data.Fields.Count); //this copy the entire recordset to the selected range in excel
    ovWS.SaveAs(DestName, 1, '', '', False, False);
  finally
    ovExcelWorkbook.Close(SaveChanges := False);
    ovWS := Unassigned;
    ovExcelWorkbook := Unassigned;
    ovExcelApp := Unassigned;
  end;
end;

And if We want export an ClientDataset to Excel then what we do? — Amin, Aug 27 '13 at 15:30
If you dont have a recordset, you have to iterate your dataset and write excel accesing cells. for example: ovWS.Cells[RowIndex, ColumnIndex].Value := Dataset.FieldByName('foo').AsString; — Agustin Seifert, Aug 28 '13 at 16:52

score 1 · Answer 2 · edited Mar 26 '18 at 11:25

It is working by using Tfilestream component

procedure TForm2.ExportdatatoexcelClick(Sender: TObject);
 var
  Stream: TFileStream;
  i: Integer;
  OutLine,f: string;
  sTemp,s: string;
begin
  Stream := TFileStream.Create('D:\Yogesh Delphi\employee1.csv', fmCreate);
  try
       s := string(adotable1.Fields[0].FieldName);

      for I := 1 to adotable1.FieldCount - 1 do
       begin
        s:= s+ ',' + string(adotable1.Fields[I].FieldName);
       end;
         s:= s+ #13#10;
        stream.Write(s[1], Length(s) * SizeOf(Char));
       {S := '';
      for I := 0 to adotable1.FieldCount - 1 do
        begin
         S := (adotable1.Fields[I].FieldName);
        outline := OutLine+S + ' ,';
        end; }

    while not adotable1.Eof do
    begin
      // You'll need to add your special handling here where OutLine is built
       s:='';
      OutLine := '';
      for i := 0 to adotable1.FieldCount - 1 do
      begin
        sTemp := adotable1.Fields[i].AsString;
        // Special handling to sTemp here
        OutLine := OutLine + sTemp +',';
      end;
      // Remove final unnecessary ','
      SetLength(OutLine, Length(OutLine) - 1);
      // Write line to file
      Stream.Write(OutLine[1], Length(OutLine) * SizeOf(Char));
      // Write line ending
      Stream.Write(sLineBreak, Length(sLineBreak));
      adotable1.Next;
    end;

  finally
    Stream.Free;  // Saves the file
  end;
    showmessage('Records Successfully Exported.') ;
end;
    {Yog}

Exporting data from a DBGrid to Excel

2 Answers2

Linked