Copy TDataSet to XLS via ADO

Question

I'm trying to copy the content from a TDBGrid to an Excel file using an ADO connection to transfer the data. This works for values that are <= 255 characters but fails for longer strings. What can I do to copy strings that are longer than 255 characters?

Changing the DataType to adLongVarWChar tbl.Columns.Append doesn't work. The ADOQuery gets a varchar field with Size 255 regardsless what I use when I create the table.

procedure DBGridToExcelADO(DBGrid: TDBGrid; FileName: string; SheetName: string);
var
  cat               : _Catalog;
  tbl               : _Table;
  col               : _Column;
  i                 : integer;
  ADOConnection     : TADOConnection;
  ADOConnectionExcel: TADOConnection;
  ADOQuery          : TADOQuery;
  ScrollEvents      : TScrollEvents;
  SavePlace         : TBookmark;
begin
  //exporting
  ADOConnectionExcel := TADOConnection.Create(nil);
  try
    ADOConnectionExcel.LoginPrompt      := False;
    ADOConnectionExcel.ConnectionString := 'Provider=Microsoft.Jet.OLEDB.4.0; Data Source=' + FileName + ';Extended Properties=Excel 8.0';

    ADOConnectionExcel.Open;
    //WorkBook creation (database)
    cat := CoCatalog.Create;
    cat._Set_ActiveConnection(ADOConnectionExcel.ConnectionObject);
    //WorkSheet creation (table)
    tbl := CoTable.Create;
    tbl.Set_Name(SheetName);
    //Columns creation (fields)
    DBGrid.DataSource.DataSet.First;

    with DBGrid.Columns do
    begin
      for i := 0 to Count - 1 do
        if Items[i].Visible then
        begin
          col := nil;
          col := CoColumn.Create;
          with col do
          begin
            Set_Name(Items[i].Title.Caption);
            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;

    //exporting
    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;

    DisableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);
    SavePlace := DBGrid.DataSource.DataSet.GetBookmark;
    try
      with DBGrid.DataSource.DataSet do
      begin
        First;
        while not Eof do
        begin
          ADOQuery.Append;
          with DBGrid.Columns do
          begin
            ADOQuery.Edit;
            for i := 0 to Count - 1 do
              if Items[i].Visible then
              begin
                //Fails if Length > 255
                ADOQuery.FieldByName(Items[i].Title.Caption).AsString := FieldByName(Items[i].FieldName).AsString;
              end;
            ADOQuery.Post;
          end;
          Next;
        end;
      end;

    finally
      DBGrid.DataSource.DataSet.GotoBookmark(SavePlace);
      DBGrid.DataSource.DataSet.FreeBookmark(SavePlace);
      EnableDependencies(DBGrid.DataSource.DataSet, ScrollEvents);

      ADOQuery.Close;
      ADOConnection.Close;

      ADOQuery.Free;
      ADOConnection.Free;
    end;
  finally
    if Assigned(ADOConnection) and ADOConnection.Connected then ADOConnectionExcel.Close;
    ADOConnectionExcel.Free;
  end;
end;

perhaps you can do it outside ADO ? You can use COM interface (TExcelApplication component) - though it is not very fast method. Or maybe DDE. Or creatign Excel-XML file and opening it by excel.exe — Arioch 'The, Dec 03 '12 at 12:03

score 1 · Accepted Answer · answered Dec 03 '12 at 13:00

1

You can use TJvDBGridExcelExport JVCL coming in the Jedi (www.delphi-jedi.org/). I have used this with good results, and this is opensource.

answered Dec 03 '12 at 13:00

Ftaveras

719
12
26

Unfortunately I can't include the jedi components completely, but the TJvDBGridExcelExport was a good starting point to reimplementing the export. – Trellmor Dec 04 '12 at 11:46

Copy TDataSet to XLS via ADO

1 Answers1