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.

Choosey

Do not export the DBGrid, export the dataset https://mcmap.net/q/1391997/-export-delphi-stringgrid-to-excel just fill arrData from the given example with the field values. – Banderilla

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. – Chefoo

@No'amNewman depending on the datataypes the csv approach might give more trouble in convertions e.g. DateTime values ... – Banderilla

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. – Dugas

Okay I will go have a look at that . Thanks . – Choosey

possible duplicate of export delphi stringgrid to excel, except you read data from the DB instead of the grid cells. – Perspire

Answer 1

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;

Visional

And if We want export an ClientDataset to Excel then what we do? – Hydrography

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; – Visional

Answer 2

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}

Bile