Rosi Components

Units

Class Hierarchy

Classes, Interfaces, Objects and Records

Types

Variables

Constants

Functions and Procedures

Identifiers

Class TrXLSExport

Description
Hierarchy
Fields
Methods
Properties

Unit

rXLSExport

Declaration

type TrXLSExport = class(TComponent)

Description

non visual component which provides functionality for exporting of any data directly to XLS file using OLE

Hierarchy

  • TComponent
  • TrXLSExport

Overview

Fields

Public Data: Variant;

Methods

Public function IsAppExist: boolean;
Public function OpenFile(NewFile: boolean; const OpenFileName: string = ''; CanReadOnly: boolean = true): boolean;
Public function SaveCloseFile(CloseApp, SaveFile: boolean; const SaveFileName: string = ''; FileFormat: integer = 0): boolean;
Public procedure Disconnect;
Public procedure SetVisibility(Visible: boolean);
Public function GetShortDateFormat: string;
Public function ConvertDateFormat(FieldDateFormat: string): string;
Public function GetSheetsCount: integer;
Public function SelectSheet(NewSheet: boolean; const SheetName: string = ''; SheetIndex: integer = 0): boolean;
Public function DeleteSheet(AllUnselected: boolean; const SheetName: string = ''): boolean;
Public procedure RememberActiveSheet;
Public procedure SelectRememberSheet;
Public function ActivateRangeSheet(var RangeName: string): boolean;
Public procedure SetCurrentRange(Row, Col, RowCount, ColCount: integer);
Public procedure SetStartPos(ClearExistingRows: boolean = true; AddToExistingRows: boolean = false);
Public function GetCellValue(Row, Col: integer): Variant;
Public procedure SetCellValue(Row, Col: integer; Value: Variant; const NumberFormat: string = '');
Public procedure SetRangeValue(RangeName: string; Value: Variant; const NumberFormat: string = '');
Public procedure SetDataSizeFromRange(RangeName: string);
Public procedure SetDataSize(RowCount, ColCount: integer);
Public function CopyCellsToData: boolean;
Public function CopyCellsToClipboard: boolean;
Public function CopyUsedRangeToData: boolean;
Public function CopyUsedRangeToClipboard: boolean;
Public function CopyCurrentRegionToData: boolean;
Public function CopyCurrentRegionToClipboard: boolean;
Public function PasteDataToCells(SetAutoFilter: boolean = false): boolean;
Public function PasteClipboardToCells(SetAutoFilter: boolean = false): boolean;
Public procedure FreezePanes(Row, Col: integer);
Public procedure SetColumnWidth(Col: integer; ColWidth: integer);
Public procedure SetRowHeight(Row: integer; RowHeight: integer);
Public procedure ColumnAutoFit(Col: integer; MaxColWidth: integer);
Public procedure RowAutoFit(Row: integer; MaxRowHeight: integer);
Public procedure UsedRangeAutoFit(MaxColWidth: integer = 0; MaxRowHeight: integer = 0; SetColumnWidth: boolean = true; SetRowHeight: boolean = true);
Public procedure FormatCells(Row, Col, RowCount, ColCount: integer; const NumberFormat: string; const FontName: string = ''; FontSize: integer = 0; Frm: TXLSCellFormats = [cfNone]);
Public procedure BorderCells(Row, Col, RowCount, ColCount: integer; Border: integer = xlAllBorders; LineColor: TColor = clNone; LineWidth: integer = xlThin; LineStyle: integer = xlContinuous);
Public procedure DatabarCells(Row, Col, RowCount, ColCount: integer; DatabarSetting: TDatabarSetting);
Public procedure ColorScaleCells(Row, Col, RowCount, ColCount: integer; ColorScaleSetting: TColorScaleSetting);
Public procedure IconSetCells(Row, Col, RowCount, ColCount: integer; IconSetSetting: TIconSetSetting);
Public procedure SetPageZoom(Zoom: integer; FitToPagesTall: integer = 1; FitToPagesWide: integer = 1; Orientation: byte = 0);
Public procedure SetWindowZoom(Zoom: integer);
Public function ExportDBTable(DBGrid: TCustomDBGrid; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;
Public function ExportDBTable(Dataset: TDataSet; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;
Public function ExportDBTable(rDBRecView: TrDBRecView; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;
Public function ExportDBTable(TreeView: TCustomTreeView; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;
Public function ExportDBTable(rStringGridEd: TrStringGridEd; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

Properties

Public property DataRowCount: integer read FDataRowCount;
Public property DataColCount: integer read FDataColCount;
Public property App: OleVariant read FApp;
Public property AppOpened: boolean read FAppOpened;
Public property Workbook: OleVariant read FWorkbook;
Public property Worksheet: OleVariant read FWorksheet;
Public property Range: OleVariant read FRange;
Public property ExportStructureOnly: boolean read FExportStructureOnly write FExportStructureOnly;
Public property ColWidthPixelRatio: double read FColWidthPixelRatio write FColWidthPixelRatio;
Public property RowHeightPixelRatio: double read FRowHeightPixelRatio write FRowHeightPixelRatio;
Published property StartRow: integer read FStartRow write SetStartRow default 1;
Published property StartCol: integer read FStartCol write SetStartCol default 1;
Published property ExportFile: string read FExportFile write FExportFile;
Published property SetFontName: string read FSetFontName write FSetFontName;
Published property SetFontSize: integer read FSetFontSize write FSetFontSize default 0;
Published property DefaultDateFormat: string read FDefaultDateFormat write FDefaultDateFormat;
Published property DefaultTimeFormat: string read FDefaultTimeFormat write FDefaultTimeFormat;
Published property DefaultNumFormat: string read FDefaultNumFormat write FDefaultNumFormat;
Published property MaxColWidth: integer read FMaxColWidth write FMaxColWidth default 100;
Published property MaxRowHeight: integer read FMaxRowHeight write FMaxRowHeight default 30;
Published property VerticalAlignment: TXLSVerticalAlignment read FVerticalAlignment write FVerticalAlignment default vaNone;
Published property PageZoom: integer read FPageZoom write FPageZoom default 0;
Published property WindowZoom: integer read FWindowZoom write FWindowZoom default 0;
Published property PictureMaxWidth: integer read FPictureMaxWidth write FPictureMaxWidth default 200;
Published property PictureMaxHeight: integer read FPictureMaxHeight write FPictureMaxHeight default 200;
Published property PictureCellMargin: integer read FPictureCellMargin write FPictureCellMargin default 5;
Published property PictureUseShadow: boolean read FPictureUseShadow write FPictureUseShadow default false;
Published property Options: TXLSExportOptions read FOptions write FOptions default[xleExportTitles, xleBoldTitles, xleOnlyVisibleFields, xleAddSumRow, xleSetCellFormat, xleSetColumnWidth, xleSetRowHeight];
Published property OptionColors: TXLSExportColors read FOptionColors write FOptionColors default[];
Published property ActionAfter: TActionAfter read FActionAfter write FActionAfter default aaShow;
Published property OnBeforeExport: TNotifyEvent read FOnBeforeExport write FOnBeforeExport;
Published property OnAfterExport: TNotifyEvent read FOnAfterExport write FOnAfterExport;
Published property UseDisableControl: boolean read FUseDisableControl write FUseDisableControl default true;
Published property ShowProgressBar: boolean read FShowProgressBar write FShowProgressBar default false;
Published property GetFieldType: TGetFieldType read FGetFieldType write FGetFieldType;
Published property OnProgressEvent: TProgressEvent read FOnProgressEvent write FOnProgressEvent;
Published property OnPreparePictureEvent: TPreparePictureEvent read FOnPreparePictureEvent write FOnPreparePictureEvent;
Published property OnPrepareDatabarEvent: TPrepareDatabarEvent read FOnPrepareDatabarEvent write FOnPrepareDatabarEvent;
Published property OnPrepareColorScaleEvent: TPrepareColorScaleEvent read FOnPrepareColorScaleEvent write FOnPrepareColorScaleEvent;
Published property OnPrepareIconSetEvent: TPrepareIconSetEvent read FOnPrepareIconSetEvent write FOnPrepareIconSetEvent;

Description

Fields

Public Data: Variant;

Data array contain data which was read from cells or which should be stored to cells

Methods

Public function IsAppExist: boolean;

test if Excel App is available

Public function OpenFile(NewFile: boolean; const OpenFileName: string = ''; CanReadOnly: boolean = true): boolean;

run Excel and open new or existing file

Public function SaveCloseFile(CloseApp, SaveFile: boolean; const SaveFileName: string = ''; FileFormat: integer = 0): boolean;

close Excel and optionally save file

Public procedure Disconnect;

Disconnect from current excel application, Excel keep opened

Public procedure SetVisibility(Visible: boolean);

set visibility of Excel application

Public function GetShortDateFormat: string;

return local NumericFormat string for short date in Excel

Public function ConvertDateFormat(FieldDateFormat: string): string;

return local NumericFormat string for short date in Excel

Public function GetSheetsCount: integer;

return number of sheets

Public function SelectSheet(NewSheet: boolean; const SheetName: string = ''; SheetIndex: integer = 0): boolean;

add new sheet and set its name or select active sheet according SheetName

Public function DeleteSheet(AllUnselected: boolean; const SheetName: string = ''): boolean;

delete all unselected sheets or delete one sheet according SheetName

Public procedure RememberActiveSheet;

remember actual active sheet

Public procedure SelectRememberSheet;

select active sheet after open or after call RememberActiveSheet

Public function ActivateRangeSheet(var RangeName: string): boolean;

select sheet by name defined in range name and return range name without sheet: sheet!range

Public procedure SetCurrentRange(Row, Col, RowCount, ColCount: integer);

set Range object

Public procedure SetStartPos(ClearExistingRows: boolean = true; AddToExistingRows: boolean = false);

set starting row for export, select first empty row if AddToExisting is true

Public function GetCellValue(Row, Col: integer): Variant;

return value of one cell

Public procedure SetCellValue(Row, Col: integer; Value: Variant; const NumberFormat: string = '');

set value of one cell

Public procedure SetRangeValue(RangeName: string; Value: Variant; const NumberFormat: string = '');

set value of RangeName

Public procedure SetDataSizeFromRange(RangeName: string);

prepare Data array according RangeName size

Public procedure SetDataSize(RowCount, ColCount: integer);

prepare Data array according RowCount and ColCount

Public function CopyCellsToData: boolean;

read values from cells to Data array, StartRow and StartCol + DataRowCount and DataColCount is used to define source range

Public function CopyCellsToClipboard: boolean;

copy values from cells to Clipboard, StartRow and StartCol + DataRowCount and DataColCount is used to define source range

Public function CopyUsedRangeToData: boolean;

read values from UsedRange of selected Sheet to Data array, size is set according UsedRange

Public function CopyUsedRangeToClipboard: boolean;

read values from UsedRange of selected Sheet to Clipboard

Public function CopyCurrentRegionToData: boolean;

read values from UsedRange of selected Sheet to Data array, size is set according UsedRange

Public function CopyCurrentRegionToClipboard: boolean;

read values from UsedRange of selected Sheet to Clipboard

Public function PasteDataToCells(SetAutoFilter: boolean = false): boolean;

write values from Data array to cells, StartRow and StartCol + DataRowCount and DataColCount is used to define target range

Public function PasteClipboardToCells(SetAutoFilter: boolean = false): boolean;

paste values from Clipboard to cells, StartRow and StartCol is used to define target range

Public procedure FreezePanes(Row, Col: integer);

switch on FreezePanes in selected sheet, if Row and Col is 0 FreezePanes is switched off

Public procedure SetColumnWidth(Col: integer; ColWidth: integer);

set width of one column

Public procedure SetRowHeight(Row: integer; RowHeight: integer);

set height of one row

Public procedure ColumnAutoFit(Col: integer; MaxColWidth: integer);

set automatic width of one column

Public procedure RowAutoFit(Row: integer; MaxRowHeight: integer);

set automatic height of one row

Public procedure UsedRangeAutoFit(MaxColWidth: integer = 0; MaxRowHeight: integer = 0; SetColumnWidth: boolean = true; SetRowHeight: boolean = true);

set automatic width of all columns and rows in selected sheet

Public procedure FormatCells(Row, Col, RowCount, ColCount: integer; const NumberFormat: string; const FontName: string = ''; FontSize: integer = 0; Frm: TXLSCellFormats = [cfNone]);

set Number format and/or Font for cells in square defined by Row/Col/Count

Public procedure BorderCells(Row, Col, RowCount, ColCount: integer; Border: integer = xlAllBorders; LineColor: TColor = clNone; LineWidth: integer = xlThin; LineStyle: integer = xlContinuous);

set Number format and/or Font for cells in square defined by Row/Col/Count

Public procedure DatabarCells(Row, Col, RowCount, ColCount: integer; DatabarSetting: TDatabarSetting);

set condition format as Databar for cells in square defined by Row/Col/Count

Public procedure ColorScaleCells(Row, Col, RowCount, ColCount: integer; ColorScaleSetting: TColorScaleSetting);

set condition format as ColorScale for cells in square defined by Row/Col/Count

Public procedure IconSetCells(Row, Col, RowCount, ColCount: integer; IconSetSetting: TIconSetSetting);

set condition format as IconSet for cells in square defined by Row/Col/Count

Public procedure SetPageZoom(Zoom: integer; FitToPagesTall: integer = 1; FitToPagesWide: integer = 1; Orientation: byte = 0);

set page zoom or number of pages for printout

Public procedure SetWindowZoom(Zoom: integer);

set window zoom

Public function ExportDBTable(DBGrid: TCustomDBGrid; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

execute creating export for DBGrid (columns collection is used)

Public function ExportDBTable(Dataset: TDataSet; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

execute creating export for Dataset (fields collection is used)

Public function ExportDBTable(rDBRecView: TrDBRecView; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

execute creating export for one record from rDBRecView (rows/fields collection is used)

Public function ExportDBTable(TreeView: TCustomTreeView; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

execute creating export for TreeView (Items collection is used)

Public function ExportDBTable(rStringGridEd: TrStringGridEd; AutoCloseApp: boolean = true; const ExportToSheetName: string = ''): boolean; overload;

execute creating export for rStringGrid (columns and rows collection is used)

Properties

Public property DataRowCount: integer read FDataRowCount;

indicate size of Data array

Public property DataColCount: integer read FDataColCount;

indicate size of Data array

Public property App: OleVariant read FApp;

return Excel application OLE object

Public property AppOpened: boolean read FAppOpened;

indicate whether excel app is opened by component

Public property Workbook: OleVariant read FWorkbook;

return active Workbook OLE object

Public property Worksheet: OleVariant read FWorksheet;

return active Worksheet OLE object

Public property Range: OleVariant read FRange;

return current Range OLE object

Public property ExportStructureOnly: boolean read FExportStructureOnly write FExportStructureOnly;

define that only structure (name of fields) will be exported

Public property ColWidthPixelRatio: double read FColWidthPixelRatio write FColWidthPixelRatio;

define pixels to column width ratio

Public property RowHeightPixelRatio: double read FRowHeightPixelRatio write FRowHeightPixelRatio;

define pixels to row height ratio

Published property StartRow: integer read FStartRow write SetStartRow default 1;

Defines first row for export

Published property StartCol: integer read FStartCol write SetStartCol default 1;

Defines first collumn for export

Published property ExportFile: string read FExportFile write FExportFile;

Defines output file name, if missing new file is opened

Published property SetFontName: string read FSetFontName write FSetFontName;

Defines FontName which will be set in output file

Published property SetFontSize: integer read FSetFontSize write FSetFontSize default 0;

Defines FontSize which will be set in output file

Published property DefaultDateFormat: string read FDefaultDateFormat write FDefaultDateFormat;

Defines default format for date fields

Published property DefaultTimeFormat: string read FDefaultTimeFormat write FDefaultTimeFormat;

Defines default format for time fields

Published property DefaultNumFormat: string read FDefaultNumFormat write FDefaultNumFormat;

Defines default format for numeric fields

Published property MaxColWidth: integer read FMaxColWidth write FMaxColWidth default 100;

Defines maximal width of columns after AutoFit

Published property MaxRowHeight: integer read FMaxRowHeight write FMaxRowHeight default 30;

Defines maximal height of rows after AutoFit

Published property VerticalAlignment: TXLSVerticalAlignment read FVerticalAlignment write FVerticalAlignment default vaNone;

Defines vertical alignment of all rows

Published property PageZoom: integer read FPageZoom write FPageZoom default 0;

Defines standard page zoom

Published property WindowZoom: integer read FWindowZoom write FWindowZoom default 0;

Defines standard window zoom

Published property PictureMaxWidth: integer read FPictureMaxWidth write FPictureMaxWidth default 200;

Defines maximal width of the picture, bigger picture will be resized

Published property PictureMaxHeight: integer read FPictureMaxHeight write FPictureMaxHeight default 200;

Defines maximal height of the picture, bigger picture will be resized

Published property PictureCellMargin: integer read FPictureCellMargin write FPictureCellMargin default 5;

Defines margin around the picture in the cell

Published property PictureUseShadow: boolean read FPictureUseShadow write FPictureUseShadow default false;

Defines usage of shadow around the picture in the cell

Published property Options: TXLSExportOptions read FOptions write FOptions default[xleExportTitles, xleBoldTitles, xleOnlyVisibleFields, xleAddSumRow, xleSetCellFormat, xleSetColumnWidth, xleSetRowHeight];

Defines options for ExportDBTable function

Published property OptionColors: TXLSExportColors read FOptionColors write FOptionColors default[];
 
Published property ActionAfter: TActionAfter read FActionAfter write FActionAfter default aaShow;

Defines action after exporting (None, Show, Print)

Published property OnBeforeExport: TNotifyEvent read FOnBeforeExport write FOnBeforeExport;

Defines event for user defined action

Published property OnAfterExport: TNotifyEvent read FOnAfterExport write FOnAfterExport;

Defines event for user defined action

Published property UseDisableControl: boolean read FUseDisableControl write FUseDisableControl default true;

indicate whether DisableControls is used during dataset scan

Published property ShowProgressBar: boolean read FShowProgressBar write FShowProgressBar default false;

indicate whether progress bar is shown

Published property GetFieldType: TGetFieldType read FGetFieldType write FGetFieldType;

Defines events for custom specification of field type (used for exporting of Pictures, Colors, databar, ColorScales

Published property OnProgressEvent: TProgressEvent read FOnProgressEvent write FOnProgressEvent;

Defines event used periodically during exporting of data

Published property OnPreparePictureEvent: TPreparePictureEvent read FOnPreparePictureEvent write FOnPreparePictureEvent;

Defines event for custom preparation of the picture before exporting

Published property OnPrepareDatabarEvent: TPrepareDatabarEvent read FOnPrepareDatabarEvent write FOnPrepareDatabarEvent;

Defines event for custom preparation of the databar format before exporting

Published property OnPrepareColorScaleEvent: TPrepareColorScaleEvent read FOnPrepareColorScaleEvent write FOnPrepareColorScaleEvent;

Defines event for custom preparation of the ColorScale format before exporting

Published property OnPrepareIconSetEvent: TPrepareIconSetEvent read FOnPrepareIconSetEvent write FOnPrepareIconSetEvent;

Defines event for custom preparation of the IconSet format before exporting

Generated by PasDoc 0.15.0.