News
Photos
Articles
Components
Applications
Kleinkunst

TscExcelExport

Version 4.29 (September 2017) - freeware for non-commercial use - Delphi VCL

This TscExcelExport component is an advanced, powerful but easy to use component which enables you to export all records of a dataset from Borland/Codegear/Embarcadero Delphi to Microsoft Excel. Many features are provided to change the layout, use conditional formatting, to add totals, to create groups, to set a filter, ... The component works in Delphi 5, 6, 7, 2006, 2007, 2009, 2010, XE, XE2, XE3, XE4, XE5, XE6, XE7, XE8, 10 Seattle, 10.1 Berlin and 10.2 Tokyo (32 and 64 bit) and it supports all Excel versions from 97 to 2016.

TscExcelExport component (Delphi VCL)

TscExcelExport component (Delphi VCL)

Features

Properties

All kinds of settings to modify the layout of the worksheet are provided.
  • Name of worksheet and file
  • Header and footer texts
  • Begin row and column of header, footer, titles (=fieldnames) and data (fieldvalues)
  • Width of columns or autofit
  • Font of header, footer, titles, summary and data (Alignment, WrapText, Orientation, MergeCells)
  • Backgroundcolor and borders of header, footer, titles, summary and data
  • Summaries for numeric or given fields (SUM, MIN, MAX, AVG, COUNT)
  • AutoFilter for titles
  • Create groups with given fields
  • Create new worksheets are reuse existing files
  • Access to the Excel objects (ExcelApplication, ExcelWorkbook, ExcelWorksheet, …)
  • ...

Events

  • To define the background color and font color, size, name and bold style of each cell
  • For exporting/writing data without using a TDataset

Methods

  • Export to Excel
  • Save worksheet as XLSX (Open XML), XLS, HTML, XML or CSV
  • Show print preview

Component editor

  • The component editor can be used to change some settings on an easy way.

Live templates

  • Live templates for all Delphi versions since 2006 (ExcelExportUse and ExcelExportCreate)
 

 

Info

Type library / COM

  • This ExcelExport component uses the Office server components from the Borland/CodeGear/Embarcadero COM type library to implement OLE automation.
  • By using compiler directives (in unit scExcelExportConfig.inc) you can override the default type library. The available libraries are Excel97, Excel2000, ExcelXP and Excel2010.
  • Microsoft Excel should be installed on the PC when using this component.

Delphi / Office

  • This VCL component was tested in
    • Borland Delphi 5 (SP1)
    • Borland Delphi 6
    • Borland Delphi 7
    • Borland Delphi (BDS) 2005
    • Borland Delphi (BDS) 2006
    • CodeGear Turbo Delphi
    • CodeGear Delphi 2007
    • CodeGear Delphi 2009
    • Embarcadero Delphi 2010
    • Embarcadero Delphi XE
    • Embarcadero Delphi XE2
    • Embarcadero Delphi XE3
    • Embarcadero Delphi XE4
    • Embarcadero Delphi XE5
    • Embarcadero Delphi XE6
    • Embarcadero Delphi XE7
    • Embarcadero Delphi XE8
    • Embarcadero Delphi 10 Seattle
    • Embarcadero Delphi 10.1 Berlin
    • Embarcadero Delphi 10.2 Tokyo
  • This ExcelExport component works with
    • Microsoft Excel 97
    • Microsoft Excel 2000
    • Microsoft Excel XP
    • Microsoft Excel 2003
    • Microsoft Excel 2007
    • Microsoft Excel 2010
    • Microsoft Excel 2013
    • Microsoft Excel 2016
  • It has been tested with Dutch and some English Office versions.
  • It will also give you access to the Excel Application, Workbook and Worksheet objects so you can access all VBA properties and methods.

Demo application

  • A full demo application with 18 examples is supplied to view and test all features of the component (and Excel automation).

Installation

  • Open the run-time package ExcelExportPack5.dpk (for Delphi5), ExcelExportPack6.dpk (for Delphi6) ExcelExportPack7.dpk (for Delphi7), ExcelExportPack9.dpk (for Delphi 2005), ExcelExportPack10.dpk (for Delphi 2006 and Turbo Delphi), ExcelExportPack11.dpk (for Delphi 2007), ExcelExportPack12.dpk (for Delphi 2009), ExcelExportPack14.dpk (for Delphi 2010), ExcelExportPackXE.dpk (for Delphi XE), ExcelExportPackXE2.dpk (for Delphi XE2), ..., ExcelExportPackXE10.dpk (for Delphi XE10), ExcelExportPackD10.dpk (for Delphi 10 Seattle), ExcelExportPackD101.dpk (for Delphi 10.1 Berlin) and ExcelExportPackD102.dpk (for Delphi 10.2 Tokyo). "Compile" the package.
  • Open the design-time package dclExcelExportPackx.dpk and "Compile" and "Install" it. The TscExcelExport component can be found in the tabsheet "SC" of the component palette.
  • When you like to add the component to an existing package, make sure the DCP file dclOffice which can be found in the Delphi/Lib folder is added as "required". When creating a new design-time package, the DCP file designide.dcp (dsnide50.dcp in Delphi5) should be added.

Technical info

  • See header of unit scExcelExport.pas for more information about all properties, methods, events, ...

Screenshots

 

Delphi examples

Example 1 : easiest way to export/write/copy dataset to Excel

scExcelExport1.Dataset:=Table1;
scExcelExport1.ExportDataset;
scExcelExport1.Disconnect;

Example 2 : using layout properties, adding summary cells and save file

scExcelExport1.WorksheetName := 'MyDataset';
scExcelExport1.Dataset:=Table1;
scExcelExport1.StyleColumnWidth:=cwOwnerWidth;
scExcelExport1.ColumnWidth := 20;
scExcelExport1.HeaderText.Text := 'Header';
scExcelExport1.BeginRowHeader := 2;
scExcelExport1.FontTitles := LabelTitle.Font;
scExcelExport1.FontTitles.Orientation := 45;
scExcelExport1.BorderTitles.BackColor := clYellow;
scExcelExport1.BorderTitles.BorderColor := clRed;
scExcelExport1.BorderTitles.LineStyle := blLine;
scExcelExport1.BeginRowTitles := 5;
scExcelExport1.FontData := LabelData.Font;
scExcelExport1.SummarySelection := ssValues;
scExcelExport1.SummaryCalculation := scMAX;
scExcelExport1.AutoFilter := True;
scExcelExport1.ExcelVisible:=False;
try
  scExcelExport1.ExportDataset;
  scExcelExport1.ExcelWorkSheet.Range['A1','A10'].Value := 'Delphi';
  scExcelExport1.SaveAs('c:\test.xls',ffXLS);
finally
  scExcelExport1.Disconnect;
end;

Example 3 : grouping data

try
  scExcelExport1.Dataset:=QuerySortShipVia;

  scExcelExport1.SummarySelection := ssValues;
  scExcelExport1.SummaryCalculation := scAVG;
  scExcelExport1.SummaryDisplayFormat := '###0.000';

  scExcelExport1.StyleColumnWidth := cwEnhAutoFit;

  scExcelExport1.GroupFields.Clear;
  scExcelExport1.GroupFields.Add('ShipVia');
  scExcelExport1.GroupFields.Add('Terms');

  scExcelExport1.ExportDataset;
finally
  scExcelExport1.Disconnect;
end;

Example 4 : export several datasets

scExcelExport1.ExcelVisible:=True;
try
  scExcelExport1.Dataset:=Table1;
  scExcelExport1.WorksheetName:='1';
  scExcelExport1.ConnectTo := ctNewExcel;
  scExcelExport1.ExportDataset;
  scExcelExport1.Disconnect;
  scExcelExport1.Dataset:=Table2;
  scExcelExport1.WorksheetName:='2';
  scExcelExport1.ConnectTo := ctNewWorkbook;
  scExcelExport1.ExportDataset;
  scExcelExport1.Disconnect;
  scExcelExport1.Dataset:=Table3;
  scExcelExport1.WorksheetName:='3';
  scExcelExport1.ConnectTo := ctNewWorksheet;
  scExcelExport1.ExportDataset;
finally
  scExcelExport1.Disconnect;
end;

Example 5 : change background color and font style in the OnGetCellStyle event

procedure scExcelExportGetCellStyleEvent(Sender: TObject; Field: TField; 
  var ColorBackground : TColor; FontCell : TxlFont);
begin
  if Field.FieldName = 'CustNo' then
  begin
    if Field.Value > 2000 then
    begin
      FontCell.Color := clRed;
      FontCell.Name := 'Times New Roman';
      FontCell.Size := 14;
    end;
    if Field.Value > 3000 then
    begin
      FontCell.Color := clGreen;
      FontCell.Style := [fsBold];
    end;
  end;

  if Field.FieldName = 'EmpNo' then
  begin
    if Field.Dataset.FieldByName('CustNo').Value > 2000 then
      ColorBackground := clRed;
  end;

  if Field.DataSet.FieldByName('EmpNo').Value > 100 then
    ColorBackground := clYellow;
end;

Example 6 : access to the properties of the Excel Worksheet object

try
  scExcelExport1.Dataset:=Table1;
  scExcelExport1.Connect;
  scExcelExport1.ExcelWorkSheet.Range['A2','C8'].Borders.Color := clRed;

  scExcelExport1.ExportDataset;

  scExcelExport1.ExcelWorkSheet.Range['B2','B2'].Select;  scExcelExport1.ExcelApplication.ActiveWindow.FreezePanes := True;

  scExcelExport1.ExcelWorkSheet.Range['B5','E7'].Cells.Clear;

  scExcelExport1.ExcelWorkSheet.Range[Format('A%d',[scExcelExport1.EndRowData+3]),
    Format('A%d',[scExcelExport1.EndRowData+3])].Value := 'Add extra info to the Excel worksheet';

  scExcelExport1.ExcelWorkSheet.Range['M1','M1'].Value := 10;
  scExcelExport1.ExcelWorkSheet.Range['M2','M2'].Value := 5;
  scExcelExport1.ExcelWorkSheet.Range['M3','M3'].Value := '=M1+M2';
  scExcelExport1.ExcelWorkSheet.Range['M3','M3'].Font.Color := clRed;
  scExcelExport1.ExcelWorkSheet.Range['M1','M20'].EntireColumn.Autofit;

  scExcelExport1.ExcelWorkSheet.Range['B2','B2'].AddComment('This is comment for a cell');
finally
  scExcelExport1.Disconnect;
end;

Copyrights and distribution

  • All copyrights to this component are owned by the author Stefan Cruysberghs.
  • This component is freeware for non-commercial use and may be freely distributed.
  • All sources (with detailed comments) are included.
  • The author doesn't give a warranty for error free running of this component.
  • Registered users can count on it that bugs will be solved as soon as possible.

Registration

  • If you like this component or you are using it in a commercial environment then you have to register it.
  • You will encourage the author to further develop and improve this ExcelExport component.
  • After registering you do not need a registration key! No email will be send. The version which can be downloaded at this page is fully functional.

Benefits of registering

  • Full source code for Delphi version 5 to XE8 and 10.2 Tokyo.
  • Unlimited deployment license.
  • Basic support can be provided by e-mail.

Single developer license

  • My webshop works with the MyCommerce eSellerate service for handling the payments. You can pay safely by credit card online, by fax or by phone. If you like to pay via PayPal, please send me an email.
  • Price: $35 (32 euro)
  • Buy single license via the webshop

    Or send an if you want to pay via PayPal or need an invoice

Site license (unlimited developers, unlimited time valid)

  • Price: $130 (120 euro)
  • Buy site license via the webshop

    Or send an if you want to pay via PayPal or need an invoice


Download

TscExcelExport component
  • Contents: TscExcelExport component with sources and example program (for Delphi 5, 6, 7, 2005, 2006, Turbo Delphi, 2007, 2009, 2010, XE, XE2, XE3, XE4, XE5, XE6, XE7, XE8, 10 Seattle, 10.1 Berlin and 10.2 Tokyo)
  • Version: 4.3 (2017-09-18)
  • File size: 6.3 Mb
  • Author: Stefan Cruysberghs
Download now