SCIP.be
Photos/Travel
Articles
Kleinkunst
Components
Applications
Overview components
Outlook, OneNote and ExcelProvider
EntityFramework extensions
Google Geocoder
FontComboBox and FontManager
FileDrop
TscExcelExport
TscFontCombobox
TscDataList
TscFileDrop
TscSystemInfo
TscScrollingText
TscLinkLabel
Nederlands

Component: TscExcelExport

Version 4.21 (December 2012) - 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 and XE3 (32 and 64 bit) and it supports Excel 97, 2000, XP, 2003, 2007, 2010 and 2013.

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 Delphi 2006, 2007, 2009, 2010, XE, XE2 and XE3 (ExcelExportUse and ExcelExportCreate)
 

 

Info

Type library / COM

Delphi / Office

Demo application

Installation

Technical info


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','A2'].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

 

Registration

Benefits of registering

Prices

 

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 and XE3)
  • Version: 4.21 (2012-12-28)
  • Author: Stefan Cruysberghs
Download now