<html>
  <head>
    <meta content="text/html; charset=utf-8" http-equiv="Content-Type">
  </head>
  <body bgcolor="#FFFFFF" text="#000000">
    <p>Denis,</p>
    <p>Answer is yes - and only yesterday was I working on such a
      program.</p>
    <p>Years ago I did the same from Delphi and used the type libraries.
      With FPC, the key is to use variants. As an example, I've attached
      a neat little unit for exporting a DBGrid to an Excel spreadsheet.</p>
    <p>Regards</p>
    <p>Tony Whyman</p>
    <p>MWA</p>
    <p>unit ExcelWorkbook;<br>
      <br>
      {$MODE Delphi}<br>
      <br>
      interface<br>
      <br>
      <br>
      uses<br>
        Classes, SysUtils, variants, DBGrids, DB, Forms;<br>
      <br>
      type<br>
      <br>
        { TExcelWorkbook }<br>
      <br>
        TExcelWorkbook = class<br>
        private<br>
          FWorksheet: variant;<br>
          FWorkbook: variant;<br>
          FServer: variant;<br>
          procedure CopyToWorkbook(DBGrid: TDBGrid; aFieldCount:
      integer); overload;<br>
          procedure CopyToWorkbook(ds: TDataSet; aFieldCount: integer);
      overload;<br>
          procedure SetCell(Row,Col: integer; aText: string);<br>
          procedure WriteFieldList(Fields: TFields; FieldCount:
      integer);<br>
          procedure WriteRecord(DataSet: TDataSet; row, aFieldCount:
      integer);<br>
        public<br>
          constructor Create;<br>
          destructor Destroy; override;<br>
          procedure SaveAs(DBGrid: TDBGrid; aWorkbookFile: string);<br>
          procedure OpenInExcel(DBGrid: TDBGrid);<br>
        end;<br>
      <br>
      implementation<br>
      <br>
      uses ComObj, CSVGridUnit, memds;<br>
      <br>
      const<br>
        ServerName = 'Excel.Application';<br>
      <br>
      resourcestring<br>
      <br>
        sUnknownField = 'Unknown Field Type';<br>
        sBadGraphic   = 'Unable to generate CSV data for a Graphic
      Field';<br>
        sBadParadox   = 'Unable to generate CSV data for a Paradox OLE
      Field';<br>
        sBadDBase     = 'Unable to generate CSV data  for a DBase OLE
      Field';<br>
        sBadBinary    = 'Unable to generate CSV data  for a Binary
      Field';<br>
        sBadCursor    = 'Unable to generate CSV data  for a Cursor
      Field';<br>
      <br>
        { TExcelWorkbook }<br>
      <br>
      procedure TExcelWorkbook.CopyToWorkbook(DBGrid: TDBGrid;
      aFieldCount: integer);<br>
      var ds: TMemDataset;<br>
          i: integer;<br>
      begin<br>
        if (DBGrid.DataSource = nil) or (DBGrid.DataSource.DataSet =
      nil) then<br>
          raise Exception.Create('Create Excel Workbook: A Dataset must
      be assigned');<br>
      <br>
        ds := TMemDataset.Create(Application);<br>
        try<br>
          ds.Clear(True);<br>
          AddFileDefs(ds,DBGrid.Columns);<br>
          ds.CreateTable;<br>
          ds.Active := true;<br>
          CopyData(ds,DBGrid.DataSource.DataSet);<br>
          SetColumnHeadings(ds,DBGrid.Columns);<br>
          for i := 1 to DBGrid.Columns.Count do<br>
             FWorksheet.Cells.Item(1,i).ColumnWidth :=
      DBGrid.Columns[i-1].Width div 5;<br>
          CopyToWorkbook(ds,aFieldCount);<br>
        finally<br>
          ds.Free;<br>
        end;<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.CopyToWorkbook(ds: TDataSet; aFieldCount:
      integer);<br>
      var<br>
        {$IF FPC_FULLVERSION >= 20700 }<br>
        bk: TBookmark;<br>
        {$ELSE}<br>
        bk: TBookmarkStr;<br>
        {$ENDIF}<br>
          row: integer;<br>
      begin<br>
        row := 2;<br>
        with ds do<br>
        begin<br>
          bk := Bookmark;<br>
          DisableControls;<br>
          try<br>
            if aFieldCount = 0 then<br>
               aFieldCount := FieldCount;<br>
            Last;<br>
            WriteFieldList(Fields,aFieldCount);<br>
            First;<br>
            while not EOF do<br>
            begin<br>
              WriteRecord(ds,row,aFieldCount);<br>
              Next;<br>
              Inc(row);<br>
            end;<br>
          finally<br>
            Bookmark := bk;<br>
            EnableControls;<br>
          end;<br>
        end<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.SetCell(Row, Col: integer; aText:
      string);<br>
      var w: WideString;<br>
      begin<br>
        w := UTF8Decode(aText);<br>
        FWorksheet.Cells.Item(Row,Col).Value := w;<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.WriteFieldList(Fields: TFields;
      FieldCount: integer);<br>
      var I: integer;<br>
      begin<br>
        for I := 0 to FieldCount - 1 do<br>
          SetCell(1,I+1,Fields[I].FieldName);<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.WriteRecord(DataSet: TDataSet; row,
      aFieldCount: integer);<br>
      var I: integer;<br>
      begin<br>
        with DataSet do<br>
        begin<br>
          for I := 0 to aFieldCount - 1 do<br>
          begin<br>
            case Fields[I].DataType of<br>
            ftUnknown:  raise Exception.Create(sUnknownField);<br>
            ftString:   SetCell(row,I+1,Fields[I].AsString);<br>
            ftSmallint,<br>
            ftInteger,<br>
            ftWord,<br>
            ftLargeInt,<br>
            ftBoolean:  SetCell(row,I+1,Fields[I].DisplayText);<br>
            ftFloat,<br>
            ftCurrency,<br>
            ftFmtBCD,<br>
            ftBCD:      SetCell(row,I+1,Fields[I].AsString);<br>
            ftDate,<br>
            ftTime:    
      SetCell(row,I+1,DateTimeToStr(Fields[I].AsDateTime));<br>
            ftDateTime: SetCell(row,I+1,Fields[I].AsString);<br>
            ftBytes,<br>
            ftVarBytes,<br>
            ftBlob,<br>
            ftAutoInc: SetCell(row,I+1,Fields[I].AsString);<br>
            ftMemo:     SetCell(row,I+1,Fields[I].AsString);<br>
            ftGraphic:  raise Exception.Create(sBadGraphic);<br>
            ftFmtMemo:  SetCell(row,I+1,Fields[I].AsString);<br>
            ftParadoxOle: raise Exception.Create(sBadParadox);<br>
            ftDBaseOle:   raise Exception.Create(sBadDBase);<br>
            ftTypedBinary:raise Exception.Create(sBadBinary);<br>
            ftCursor:    raise Exception.Create(sBadCursor);<br>
           end<br>
          end;<br>
        end;<br>
      end;<br>
      <br>
      constructor TExcelWorkbook.Create;<br>
      begin<br>
        try<br>
          FServer := CreateOleObject(ServerName);<br>
        except<br>
          raise Exception.Create('Unable to start Excel.');<br>
         end;<br>
        FWorkbook := FServer.Workbooks.Add;<br>
        FWorksheet := FWorkbook.Worksheets.Add;<br>
      end;<br>
      <br>
      destructor TExcelWorkbook.Destroy;<br>
      begin<br>
        if not FServer.Visible then<br>
        begin<br>
         if not VarIsEmpty(FWorkbook) then<br>
            FWorkbook.Close(0); {Do not save Changes}<br>
          FServer.Quit;<br>
        end;<br>
        inherited Destroy;<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.SaveAs(DBGrid: TDBGrid; aWorkbookFile:
      string);<br>
      var w:widestring;<br>
      begin<br>
        CopyToWorkbook(DBGrid,0);<br>
        w := UTF8Decode(aWorkbookFile);<br>
        FWorkbook.SaveAs(w);<br>
      end;<br>
      <br>
      procedure TExcelWorkbook.OpenInExcel(DBGrid: TDBGrid);<br>
      begin<br>
        FServer.Visible := true;<br>
        CopyToWorkbook(DBGrid,0);<br>
      end;<br>
      <br>
      <br>
      end.<br>
      <br>
    </p>
    <br>
    <div class="moz-cite-prefix">On 05/10/16 09:54, Dennis via Lazarus
      wrote:<br>
    </div>
    <blockquote cite="mid:57F4BFE2.8030206@avidsoft.com.hk" type="cite">
      <meta http-equiv="content-type" content="text/html; charset=utf-8">
      <font size="+1">I am having problem using the Type Library
        generated.<br>
        Don't know how to use it.<br>
        <br>
        I tried to 'learn' from old Delphi 5 source code (the only
        version of Delphi I have) that worked with Excel 2000 but found
        that the type library are so different from the one generated by
        FPC.<br>
        <br>
        Dennis<br>
      </font> <br>
      <fieldset class="mimeAttachmentHeader"></fieldset>
      <br>
    </blockquote>
    <br>
  </body>
</html>