<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>