Title: Borland Dataset to Microsoft Excel
Question: Based on Lubomir Rosenstein's article: "MICROSOFT AUTOMATION: DATASET EXPORT AND PRINTING"
(http://www.delphi3000.com/articles/article_1282.asp) and an interesting comment from an Anonymous user I decided to add this "humble" piece of code.
Hopefully this can help Mr. anonymous to feel a little bit less unsatisfied.
Answer:
// Please see:
// http://www.delphi3000.com/articles/article_1282.asp
// http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnautoma/html/msdn_thrdole.asp
// file://C:\Program Files\Microsoft Office\Office\1033\VBAXL9.CHM
procedure TForm1.DataSetToExcelFile(const Dataset: TDataset;
const Filename: string);
var
DefaultLCID: LCID;
i: Integer;
Row: Integer;
ExcelApp: TExcelApplication;
Worksheet: TExcelWorksheet;
Workbook: TExcelWorkbook;
begin
DefaultLCID := GetUserDefaultLCID;
ExcelApp := TExcelApplication.Create(Self);
ExcelApp.ConnectKind := ckNewInstance;
ExcelApp.Connect;
ExcelApp.ScreenUpdating[DefaultLCID] := False; // optimize presentation
try
// create workbook
Workbook := TExcelWorkbook.Create(Self);
Workbook.ConnectTo(ExcelApp.Workbooks.Add(TOleEnum(xlWBATWorksheet),
DefaultLCID));
// create worksheet
Worksheet := TExcelWorksheet.Create(Self);
Worksheet.ConnectTo(Workbook.Worksheets[1] as _Worksheet);
Worksheet.Name := 'First WorkSheet';
// populate with Dataset information
Dataset.DisableControls;
try
// header
for i := 0 to Dataset.FieldCount - 1 do
begin
if Dataset.Fields[i].Visible then
begin
Worksheet.Cells.Item[1, i + 1].Value :=
Dataset.Fields[i].DisplayLabel;
Worksheet.Cells.Item[1, i + 1].ColumnWidth :=
Dataset.Fields[i].DisplayWidth;
end;
end;
Worksheet.Range['A1', 'A1'].EntireRow.Interior.Color := clblue;
Worksheet.Range['A1', 'A1'].EntireRow.Font.Bold := True;
Worksheet.Range['A1', 'A1'].EntireRow.Font.Color := clWhite;
// data
Row := 2;
Dataset.First; // TODO: add a bookmark
while not Dataset.Eof do
begin
for i := 0 to Dataset.FieldCount - 1 do
begin
if Dataset.Fields[i].Visible then
begin
Worksheet.Cells.Item[Row, i + 1].Value :=
Dataset.Fields[i].Text;
Application.ProcessMessages;
end;
end;
Inc(Row);
Dataset.Next;
end;
// save it
Workbook.SaveAs(
EdFilename.Text, // Filename
XlWindowState(xlNormal), // FileFormat
EmptyParam, // Password,
EmptyParam, // WriteResPass
False, // ReadOnlyRecommended
False, // CreateBackup
xlNoChange, // AccessMode
xlUserResolution, // ConflictResolution
False, // AddToMru
EmptyParam, // TextCodepage
EmptyParam, // TextVisualLayout
DefaultLCID);
Workbook.Close;
finally
Dataset.EnableControls;
Workbook.Free;
Worksheet.Free;
end;
finally
ExcelApp.ScreenUpdating[DefaultLCID] := True; // optimize presentation
ExcelApp.Disconnect;
ExcelApp.Free;
end;
end;
// Formattted with "Source Code Formatter 2.41"
// http://www.slm.wau.nl/wkao/delforexp.html