ArtRepor v2.5 for Delphi & Lazarus

Table of Contents

Overview

Declaration

Properties

Methods

Events

Report types

Template creation

Named Ranges

Report variables and names

User-defined variables

User-defined formulas

ArtReport providers

Overview

ArReport provides easy way to create XLS spreadsheet reports (using installed spreadsheet application like MS Excel or OpenOffice Calc) for Delphi & Lazarus developers. ArtReport contains main component TArtRepot - native Delphi/Lazarus component and two providers for MS Excel and OpenOffice Calc.

The main features of ArtReport:

• Several report types:
  • simple report;
  • master-detail report;
  • master-detail-subdetail report;
  • grouped report;
  • subgrouped report;
  • multicolumn report.
• Several reports per one sheet and several reports on several sheets
• User-defined variables and formulas in report
• Using all spreadsheet application formatting abilities for report templates creation
• Printing report
• Running spreadsheet application Macros for postprocessing

ArtReport uses special provider component to generate report. ArtReport package includes providers for MS Excel (TARExcelProvider) and OO Calc (TARCalcProvider). One can develop new providers for other spreadsheet applications.

To use ArtReport you should create report template (see Template Creation), then specify template file in Template property and set SaveAs  property to desirable name of  report.  Set Page property to specify sheet number in template. Set data sources (MasterSource, DetailSource and SubDetailSource) and data fields (MasterField, DetailField, SubMasterField, SubDetailField, GroupField, SubGroupField) depending on report type. For complex report you can set Grouping and/or SubGrouping properties to TRUE if you want to create outline structure. Also you can set ShowEmpty and/or ShowEmptySub to TRUE unless you want to exclude groups without subelements from your report. For multicolumn report, use Columns property to specify number of columns or write OnGetColumns event handler. If your template has user-defined variables write OnGetValue event handler to specify their values. Set RunMacro property to macro name in your template in case you want to run this macro after report creation. Set Print property to TRUE if you want to print report. Set ShowReport property to  if you want to open resulting report file in your default spreadsheet application.

Also you should place ArtReport provider component (TARExcelProvider or TARCalcProvider)  on your form and specify it as value of  Provider property.

Finally run Report procedure to create report.

Declaration

ArtReport = class (TComponent);

Methods:

constructor Create(parent:TComponent);

destructor Destroy;

procedure Report;

Properties:

Template : string;

Page : integer;

SaveAs : string;

MasterSource : TDataSource;

DetailSource : TDataSource;

SubDetailSource : TDataSource;

MasterField : string;

DetailField : string;

SubMasterField : string;

SubDetailField : string;

GroupField : string;

SubGroupField : string;

ShowReport : boolean;

ShowEmpty : boolean;

ShowEmptySub : boolean;

Grouping : boolean;

SubGrouping : boolean;

MasterRangeName : string;

RangeName : string;

SubRangeName : string;

Columns : integer;

RunMacro : string;

Print : boolean;

Error : string;

SheetName : string;

Provider : TArtRepProvider;

Visible : boolean;

Events:

OnGetValue : TTArtRepValueProc;

  TArtRepValueProc = procedure(name:pchar;var Val:Variant) of object; 

OnGetColumns : TArtRepColumnsProc;

TArtRepColumnsProc = procedure(group:integer;var Count:integer) of object;

OnProgress : TArtRepProgressProc;

TArtRepProgressProc = procedure(num,total:integer) of object;

Properties

Template : string;

Indicates the name of template file. If Page property set to 0 ArtReport will create new file on the basis of that template. In other case ArtReport will open file. So if you want to create multi page report you should create multi sheets template, set Page to zero, use template to create report on first sheet (run Report procedure), than set Template equal to SaveAs, set Page property to the number of sheet with the next report, run Report procedure to create report on specified sheet and so on.

Page : integer;

Specifies sheet number in template file where report should be created. If Page is equal to 0 (default) ArtReport will create report on current active sheet.

SaveAs : string;

Indicates the name of resulting report file.

MasterSource : TDataSource;

Specifies the TDataSource component for the dataset to use as a dataset for simple, grouped and  subgrouped report and as master dataset for master-detail and master-detail-subdetail report.

DetailSource : TDataSource;

Specifies the TDataSource component for the dataset to use as detail dataset for master-detail and master-detail-subdetail report.

SubDetailSource : TDataSource;

Specifies the TDataSource component for the dataset to use as subdetail dataset for master-subdetail report.

MasterField : string;

Specifies field in a master dataset to link with field in detail dataset specified by DetailField property in order to establish a master-detail relationship between the datasets and create master-detail or master-detail-subdetail report.

DetailField : string;

Specifies field in a detail dataset to link with field in master dataset specified by MasterField property in order to establish a detail-master relationship between the datasets and create master-detail or master-detail-subdetail report. Detail dataset should be ordered or indexed on the field specified by DetailField property.

SubMasterField : string;

Specifies field in a detail dataset to link with field in subdetail dataset specified by SubDetailField property in order to establish a master-detail relationship between the datasets and create master-detail-subdetail report.

SubDetailField : string;

Specifies field in a subdetail dataset to link with field in detail dataset specified by SubMasterField property in order to establish a detail-master relationship between the datasets and create master-detail-subdetail report. Subdetail dataset should be ordered or indexed on the field specified by SubDetailField property.

GroupField : string;

Specifies field in a master dataset to group resulting rows in grouped or subgrouped report. Master dataset should be ordered or indexed on the field specified by GroupField property.

SubGroupField : string;

Specifies field in a master dataset to create two-level grouping in subgrouped report. Master dataset should be ordered or indexed on the field specified by GroupField property and then on the field specified by SubGroupField property.

ShowReport : boolean;

Indicates whether you want to open resulting file in your default spreadsheet application after report creation.

ShowEmpty : boolean;

If not set groups without subelements will be excluded from report (for master-detail,  master-detail-subdetail, grouped and subgrouped report types).

ShowEmptySub : boolean;

If not set subgroups without subelements will be excluded from report (for  master-detail-subdetail and subgrouped report types).

Grouping : boolean;

Indicates whether to create outline structure for groups (for master-detail,  master-detail-subdetail, grouped and subgrouped report types).

SubGrouping : boolean;

Indicates whether to create outline structure for subgroups (for  master-detail-subdetail and subgrouped report types).

MasterRangeName : string;

Specifies the name of report cells range in resulting file.

RangeName : string;

Specifies the base name for group ranges. Group ranges will have names <RangeName>_1,  <RangeName>_2 and so on.

SubRangeName : string;

Specifies the base name for subgroup ranges. Subgroup ranges will have names <SubRangeName>_X_1,  <SubRangeName>_X_2 and so on where X is the number of corresponded group.

Columns : integer;

Specifies number of columns in multicolumn report. If OnGetColumns not implemented all multicolumn groups will have Columns number of columns.

RunMacro : string;

Specifies macro name to run after report creation (e.g. for postprocessing). Macro has to exists in your template.

Print : boolean;

Specifies whether to print resulting report;

Error : string;

Contains last error string.

SheetName : string;

Specifies sheet name to set.

Provider : TArtRepProvider;

Specifies ArtReport provider component.

Visible : boolean;

Shows report creation progress (mostly for debugging).

Methods

procedure Report;

Creates report.

Events

OnGetValue : TArtRepValueProc;

TArtRepValueProc = procedure(Name:string;var Val:Variant) of object;

Write OnGetValue event handler to specify value for user-defined variable situated in report. The name of variable is passed as Name parameter. The value can by returned by setting Val parameter. The default value for Val is empty string.

OnGetColumns : TArtRepColumnsProc;

TArtRepColumnsProc = procedure(Group:integer;var Count:integer) of object;

Write OnGetColumns event handler to set number of columns in multicolumn groups in multicolumn report. Group parameter specifies multicolumn group number, Count parameter is used to set number of columns in group. If OnGetColumns event handler is not implemented, Columns property is used to specify the number of columns in all multicolumn groups.

OnProgress : TArtRepProgressProc;

TArtRepProgressProc = procedure(num,total:integer) of object;

Write OnProgress event handler to show report progress. Total shows total record to process and num is the number of current record. Percentage of completion  is  num / total * 100;

Report types

TArtReport supports five report types

• simple report;
• master-detail report;
• master-detail-subdetail report;
• grouped report;
• subgrouped report.

Every of these reports also can be of multicolumn type. So actually there are ten report types.

Simple report

Simple report use only one data source (MasterDataSource) to retrieves data and represents data as a simple table.

Master-detail report

Master-detail report uses two data sources MasterSource and DetailSource. Dataset specified by MasterSource is related to dataset specified by DetailSource as master-detail. Relationship is established by specifying pair of fields MasterField from master dataset and DetailField from detail dataset. Detail dataset should be sorted or indexed on the field specified by DetailField property. Master-detail report creates two-level table. For every record from master dataset it creates subtable by retrieving records from detail dataset with value of DetailField is equal to MasterField value of master dataset record. If for the record from master dataset there are no corresponded records from detail dataset and ShowEmpty property is equal to false ArtReport will not creates corresponded subtable. The value of Grouping property indicates whether to create outline structure.

Master-detail-subdetail report

Master-detail-subdetail report extends Master-detail report type. It uses three data sources MasterSource, DetailSource and SubDetailSource. Dataset specified by MasterSource and dataset specified by DetailSource are related as master-detail by pair of fields MasterField and DetailField. In just the same way detail dataset and subdetail dataset are related as master-detail by pair of fields SubMasterField and SubDetailField. Detail dataset should be sorted or indexed on the field specified by DetailField property and subdetail dataset should be sorted or indexed on the field specified by SubDetailField property. Master-detail-subdetail report creates three-level table. First level corresponds to master dataset record, second level corresponds to related records from detail dataset and third level is for related records from subdetail dataset. Values of  ShowEmpty and ShowEmptySub properties determine whether ArtReport will create subtables for records without relative detailed records. The values of Grouping  and SubGrouping properties indicate whether to create one- and second-level outline structure.

Grouped report

Grouped report is similar to master-detail report but data are retrieved from only one dataset specified by MasterSource. Grouped report creates two-level table. For every record group with same value of GroupField ArtReport will create one subtable. Dataset should be sorted or indexed on the field specified by GroupField property.

SubGrouped report

Subgrouped report is similar to master-detail-subdetail report but data are retrieved from only one dataset specified by MasterSource. Subgrouped report creates three-level table. ArtReport will create one subtable for every record group with same value of GroupField. Inside this group for every subgroup with same value of SubGroupField field ArtReport will create second-level subtable. Dataset should be sorted or indexed on the field specified by GroupField property and then on the field specified by SubGroupField property.

Multicolumn report

Multicolumn report is created on the basis of previous five report types. One can use multicolumn report when it is unknown the exact number of columns in resulting report file. For example when it is necessary to create cumulative monthly reports (for one month, two months, three months and so on) one can create twelve “one column” templates or one  multicolumn report template. By setting Columns property it is possible to specify number of columns in multicolumn report. If there are several column group with different number of columns one should write OnGetColumns event handler.

Template creation

Template is spreadsheet template file with special structure. First two rows have special meaning and will be deleted from resulting report file. The first row is used for report type specification. The value in first cell should be

• simple              for simple report
• detail               for master-detail  report
• subdetail          for master-detail-subdetail  report
• group               for grouped report
• subgroup         for subgrouped report

Multicolumn reports will be considered later.

Second row is used for auto fitting columns in report. If cell of second row contains #autofit then the corresponded column of resulting report will be auto fitted (i.e. width of the columns will be changed to achieve the best fit).

The structure of template is defined by directive rows. Directive rows contains directive name in its first cell and will be deleted from resulting report. Directive name begins with # (pound sign).

Template body enclosed in #begin and #end directives. All cells from the beginning of the template file till #begin directive and from #end directive till the end of template file will stay unchanged in resulting report.

Template body consists of header, footer and data table template. data table template is situated between #masterbegin and #masterend directives. It represents data retrieved from datasets and depends on report type. Template header and footer are situated between  #begin and #masterbegin and #masterend and #end directives respectively. They can contain report variables, user-defined variables and functions (see below).

Data table template for simple report

data table template for simple report is a template for representing one record retrieved from dataset. For each record ArtReport will create one entry on the basis of data table template. data table template can consists of several rows. Its cells can contain report variables, user-defined variables and functions and dataset field names. Dataset field names should be preceded with $ (dollar sign). The structure of report template file for simple report is

Data table template for master-detail report

For master-detail report data table template consists of master header, master footer and detail data table template. Detail data table template is enclosed in #detailbegin and #detailend directives. Master header and footer are situated between #masterbegin and #detailbegin and between #detailend and #masterend directives respectively. Their cells can contain report variables, user-defined variables and functions and master dataset field names. For each record from master dataset ArtReport  creates one entry in resulting report (unless ShowEmpty property is false and there are no corresponded detail records) contained master header, detail data table and master footer . Detail data table template is a template for representing one record retrieved from detail dataset. Its cells can contain report variables, user-defined variables and functions and dataset field names. Field names from master dataset should be preceded with “M.” (e.g. $M.Masterfield), field names from detail dataset can be preceded with “D.” (e.g. $D.Detailfield or $Detailfield). The structure of report template file for master-detail report is

Data table template for master-detail-subdetail report

Structure of master-detail-subdetail report template extends one for master-detail report. Now detail data table template is divided into detail header, detail footer and subdetail data table template (situated between #subdetailbegin and #subdetailend). For each record retrieved from detail dataset ArtReport creates one entry in resulting report (unless ShowEmptySub property is false and there are no corresponded subdetail records) contained detail header, subdetail data table and detail footer. The cells of subdetail data table template can contain report variables, user-defined variables and functions and dataset field names. Field names from master dataset should be preceded with “M.” (e.g. $M.Masterfield), field names from detail dataset should be preceded with “D.” (e.g. $D.Detailfield) and field names from subdetail dataset can be preceded with “S.” (e.g. $S.Subdetailfield or $Subdetailfield). The structure of report template file for master-detail-subdetail report is

Data table template for grouped report

Data table template for grouped report coincides with one for master-detail report except directives #detailbegin and #detailend are replaced by #groupbegin and #groupend respectively and there is no necessity for M. and D. prefixes as all data retrieved from only one dataset. The type of report (in first cell) should be group.

Data table template for subgrouped report

Data table template for subgrouped report coincides with one for master-detail-subdetail report except directives #detailbegin, #detailend, #subdetailbegin, #subdetailend are replaced by #groupbegin, #groupend, #subgroupbegin, #subgroupend respectively and there is no necessity for M., D. and S. prefixes as all data retrieved from only one dataset. The type of report (in first cell) should be subgroup.

Multicolumn report

Multicolumn report is useful when the exact number of columns in report is unknown. In our context it is mean that the dataset can contain fields <fieldname>1, <fieldname>2, … <fieldname>N (e.g. data1, data2, …, data10) with the same <fieldname> and value of N is unknown. Let’s call such kind of fields “multifield group”. To solve this problem one should create N report templates. Dataset can contain more than one multifield group. In this case the number of templates increases exponentially. Another way to solve this problem is to use multicolumn report. Multicolumn report template is created on the basis of ordinary report of any type (i.e. for dataset where every multifield group corresponds only one field <fieldname>).  Then one should add two rows at the beginning of the template file. In the first cell of first row one should type multicolumn. Second row is used to multicolumn groups specification. Multicolumn group corresponds to one or more multifield groups. For example if dataset has two multifield groups with same number of fields and it is desirable to obtain report with consecutively placed field pairs one should create only one multicolumn group with two column corresponded to both multifield groups. If all multifield groups have different number of fields one should create multicolumn group for every multifield group. To create multicolumn group one should add empty column before and one empty column after column group. In second-row cells of these columns one should type #begin and #end directives. Let us call such columns directive columns. Inside multicolumn group field names corresponded to multifield groups should preceded with $$ (two dollar sign). To specify number of columns in multicolumn group one should set Columns property (if there only one multicolumn group or every multicolumn groups have same number of columns) or write OnGetColumns event handler. At run time ArtReport creates ordinary report template with specified number of columns. Every multifield names $$<fieldname> are replaced with $<fieldname>1, $<fieldname>2 and so on. Also every user-defined variables preceded with && (two ampersands) (i.e. &&<varname>) are replaced with &<varname>1, &<varname>2 and so on. ArtReport deletes first two rows and all directive columns. So ordinary report template is resulted and ArtReport continues to handle it corresponded to its type. The structure of multicolumn report template file is

Named Ranges

ArtReport creates some named ranges on resulting sheet. For simple report it creates only one named range with the name specified by MasterRangeName property and situated in first column of data table. For master-detail and grouped reports ArtReport also creates named ranges in first column of each detail data table with names RangeName1, RangeName2 and so on where RangeName is specified by RangeName property. Further for master-detail-subdetail and subgrouped reports ArtReport creates named ranges in first column of each subdetail data table with names SubRangeNameN_1, SubRangeNameN_2 and so on where N is the number of corresponded detail data table and SubRangeName is specified by SubRangeName property. By default MasterRangeName, RangeName and SubRangeName are equal to “Base” value. You can use named ranges in spreadsheet formulas to calculate totals, summarized data and other. For example if you wish to count total of some column in simple report, add the next formula in the same column inside template header (or template footer) =SUM(OFFSET(Base,0,COLUMN(RC)-1,ROWS(Base),1)). To calculate subtotal of each detail data table in master-detail report use user-defined formula (see below) &=SUM(OFFSET(Base{&masterrecno},0,COLUMN(RC)-1,ROWS(Base{&masterrecno}),1)).

Report variables and names

You can use numerous report variables and names in your report. Report variables should be preceded with & (ampersand sign). Report names can be preceded with & (ampersand sign) or you can use report names directly inside formula (i.e. next two formulas give the same result =MasterCount and &={&MasterCount}). ArtReport replaces all variables with corresponded values and names with corresponded spreadsheet names. You can use report variables as the only values in cells or inside user-defined formulas (see below). Report names can be used only in user-defined formulas. The set of variables and names in template header and footer differs from one in datetable template. Next table summarizes variables and names allowed in template header and footer.

For the next report example corresponded names will have the values:

MasterCount = 2

DetailTotal = 3 (master-detail report type) or GroupTotal = 3 (subgrouped report type)

SubdetailAll = 7 (master-detail report type) or SubGroupAll = 7 (subgrouped report type)

Next table summarizes variables and names allowed in data table template.

User-defined variables

You can use your own variables. User-defined variable can have any name not coincident with standard variables and names and preceded by &. To specify the value of user-defined variable you should write OnGetValue event handler.

User-defined formulas

You can use report variables, report names and dataset fields inside spreadsheet formulas. To instruct ArtReport to substitute  report variables with their values formula should be preceded by & (ampersand sign). Report variables, report names and dataset field names should be enclosed in braces ({}). For example to evaluate value of Val field divided by total number of records in report you can use formula &={$Val}/{&MasterCount}.

Important! You should use R1C1 reference style in user-defined formulas.

User-defined expressions

You can use user-defined expressions in the same way as user-defined formulas. Expression should be preceded with &? (ampersand and question signs). Report variables and dataset field names should be enclosed in braces ({}). ArtReport substitutes report variables and dataset field names with corresponded values. 

For example if field Val is equal to 100 and user defined variable Currency is equal to dollars then &?I have {$Val} {$Currency} results in I have 100 dollars. 

You should not use report names in user-defined expressions, because ArtReport will substitute them with corresponded spreadsheet names not values. For example field Val field contains 100 then &?{$Val}/{&MasterCount} will become  100/Mastercount. If you need to use report names exploit user-defined formula.

ArtReport Providers

ArtReport provider is component that provides report generation for particular spreadsheet application. ArtReport provider is child of TArtRepProvider class.

Declaration:

  TArtRepProvider = class(TComponent)

    procedure Connect; virtual; abstract;

    procedure SetVisible(visible:boolean); virtual; abstract;

    procedure OpenDoc(name:string; page:integer); virtual; abstract;

    procedure CreateDoc(name:string); virtual; abstract;

    procedure SetCalculation(auto:boolean); virtual; abstract;

    function GetCellText(row,col:integer) : string; virtual; abstract;

    procedure SetCellText(row,col:integer; txt:string); virtual; abstract;

    function GetCellFormula(row,col:integer) : string; virtual; abstract;

    procedure SetCellFormula(row,col:integer; Formula:Variant); virtual; abstract;

    procedure DeleteRow(row : integer); virtual; abstract;

    procedure DeleteRows(row,count : integer); virtual; abstract;

    procedure DeleteColumn(col : integer); virtual; abstract;

    procedure InsertColumns(col, count:integer); virtual; abstract;

    procedure CopyColumns(col, tocol, count:integer); virtual; abstract;

    procedure InsertRows(row, count:integer); virtual; abstract;

    procedure CopyRows(row, torow, count:integer); virtual; abstract;

    procedure Group(row, count : integer); virtual; abstract;

    procedure SetRange(R1,C1,R2,C2:integer; Data:Variant); virtual; abstract;

    procedure AddNameRange(R1,C1,R2,C2:integer; name:string); virtual; abstract;

    procedure AddName(name:string; val:integer); virtual; abstract;

    function LastUsedRow : integer; virtual; abstract;

    function DocLoaded : boolean; virtual; abstract;

    function LastUsedColumn : integer; virtual; abstract;

    procedure CleanUp; virtual; abstract;

    procedure AutoFit(col : integer); virtual; abstract;

    procedure ActivateCell(row,col : integer); virtual; abstract;

    procedure ShowLevels(level : integer); virtual; abstract;

    procedure SetSheetName(name : string); virtual; abstract;

    procedure Run(macro : string); virtual; abstract;

    procedure Print; virtual; abstract;

    procedure SaveDocAs(s:string); virtual; abstract;

    procedure SaveDoc; virtual; abstract;

    procedure BeforeQuit; virtual; abstract;

    procedure CloseDoc; virtual; abstract;

    function Installed : boolean;  virtual; abstract;

    function ProviderName : string; virtual; abstract;

    procedure MarkSaved; virtual; abstract;

    procedure Quit; virtual; abstract;

    function GetMyVariant(Field: TField;str:boolean=true): OLEVariant; virtual; abstract;

    function GetDate(val : TdateTime): OLEVariant; virtual; abstract;

  end;

Any successor of TArtRepProvider class should implement all abstract methods of base class. Also it can have specific properties and methods.

ArtReport package contains two ArtReport provider for MS Excel and OpenOffice Calc: TARExcelProvider and TARCalcProvider.

TARExcelProvider has addition property

property FormulaLocal : boolean;

If you use localized version of MS Office and use localized function names in user-defined formulas, you should set FormulaLocal to true.

TARCalcProvider also has several addition properties and methods:

property InputFilterName : string;

Specifies name of input filter. Default is MS Excel 97.

property OutputFilterName : string;

Specifies name of output filter. Default is MS Excel 97.

property ConvertLocalNames : boolean;

Has the same meaning as FormulaLocal for TARExcelProvider. As OO Calc (even localized) don't understand localized function names you should specify them by calling AddConvert method.

property ConvertDecimal : boolean;

Change local decimal separator specified by Delphi/Lazaris DecimalSeparator variable with decimal point in user-defined formulas. Works only if  ConvertLocalNames is true.

If you use user-defined formulas in your template, set ConvertRCRef to true to convert R1C1 references into A1 reference style, as there are some problems with understanding R1C1 references by OO Calc.

property ConvertComma : boolean;

Change comma formula parameter delimiter (MS Excel formula sintax) with semicolon (OO Calc formula sintax) in user-defined formulas. Works only if  ConvertRCRef is true.

procedure AddConvert(local, native:string);

Adds new pair of local and English formula names. This pair is used  if  ConvertLocalNames set to true.

You can develop you own ArtReport providers for other spreadsheet applications. See source code for TARExcelProvider and TARCalcProvider for more details.