TFlexCelReport Class
Component for creating reports on Excel based on a template. It will read an xls file, replace tags with data read from a database or memory, and save a new file with the data.
Syntax
Unit: FlexCel.Report
TFlexCelReport = class(TFlexCelObject, IDataTableFinder);
Constructors
Name | Description |
---|---|
Create | Overloaded Create Create(Boolean) Create(Integer, string, TDataSourceInfoList, TFlexCelReport) |
Methods
Name | Description |
---|---|
OnBeforeReadTemplate | Replace this event when creating a custom descendant of FlexCelReport. See also BeforeReadTemplate |
OnBeforeGenerateWorkbook | Replace this event when creating a custom descendant of FlexCelReport. See also BeforeGenerateWorkbook |
OnAfterGenerateWorkbook | Replace this event when creating a custom descendant of FlexCelReport. See also AfterGenerateWorkbook |
OnBeforeGenerateSheet | Replace this event when creating a custom descendant of FlexCelReport. See also BeforeGenerateSheet |
OnAfterGenerateSheet | Replace this event when creating a custom descendant of FlexCelReport. See also AfterGenerateSheet |
OnGetImageData | Replace this event when creating a custom descendant of FlexCelReport. See also GetImageData |
OnCustomizeChart | Replace this event when creating a custom descendant of FlexCelReport. See also CustomizeChart |
OnGetInclude | Replace this event when creating a custom descendant of FlexCelReport. See also GetInclude |
OnUserTable | Replace this event when creating a custom descendant of FlexCelReport. See also UserTable |
OnLoadTable | Replace this event when creating a custom descendant of FlexCelReport. See also LoadTable |
PreLoad | Used on included reports. For performance, the report will be parsed only once. |
ResolveString | This is the method that does the parsing. Could be made virtual and override it on a descendant class to support self defined Tags. |
Unload | This cleans the resources allocated by preload. |
Run | Overloaded Run(TExcelFile) Run(string, string) Run(TStream, TStream) Run(TStream, TStream, TFileFormats) |
Cancel | Cancels a running report. This method is equivalent to setting Canceled = true. |
AddTable | Overloaded AddTable(string, TArray<T>) AddTable(string, TVirtualDataTable) AddTable(string, TList<T>, TDisposeMode) AddTable(TDataModule, TRecordCountMode, TDisposeMode) AddTable(string, TVirtualDataTable, TDisposeMode) AddTable(string, TDataSet, TRecordCountMode, TDisposeMode) |
ClearTables | Clear the collection of tables available to the report. |
GetTable | Returns the VirtualDataTable with the specified name that was added to the report. |
AddConnection | Adds an adapter to use from the template on the DIRECT SQL commands. For security reasons, make sure this adapter ONLY GRANTS READONLY ACCESS TO THE DATA |
ClearConnections | Clear the collection of connections available to the report. |
AddSqlParameter | Adds an SQL parameter to use from the template on the DIRECT SQL commands. Note that the parameter must have a name even if you are using positional parameters ("?") because on the template you should always write named parameters. |
ClearParameters | Clear the collection of SQL parameters available to the report. |
SetValue | Sets a variable for the report. |
ClearValues | Destroys all variables on the report. To add new variables, use SetValue |
SetExpression | Sets a user-defined expression to be used in the report. Different from SetValue this method will evaluate the <#tags> in "value". This allows you to provide formula functionality to end users, and to reuse the same report for different formulas. |
ClearExpressions | Destroys all user-defined expressions on the report. To add new expressions, use SetExpression |
SetUserFunction | Adds a new user defined function to be used with the report. The User function object is managed by the report, so you don't have to free it once you used this method to add it to the report. For information on how to create the user function, see TFlexCelUserFunction |
ClearUserFunctions | Destroys all user defined functions on the report. To add new functions, use SetUserFunction |
SetUserFormat | Adds a new user defined format to be used with the report. For information on how to create the user format function, see TFlexCelUserFormat |
ClearUserFormats | Destroys all user defined formats on the report. To add new formats, use SetUserFormat |
AddRelationship | Overloaded AddRelationship(string, string, string, string) AddRelationship(string, string, TArray<string>, TArray<string>) |
ClearRelationships | Clears all relationships added by AddRelationship(string, string, string, string). |
Properties
Name | Description |
---|---|
DataConversionEvent | Set this function to return a the value that must be written in the report for an speific type of record, class or array. |
Canceled | If true the report has been canceled with Cancel method. You can't set this variable to false, and setting it true is the same as calling Cancel. |
Progress | Progress of the report. This variable must be accessed from other thread. |
DeleteEmptyRanges | This property is obsolete. Use DeleteEmptyBands instead. Determines if FlexCel will delete or just clear ranges with empty datasets (0 records). |
DeleteEmptyBandsFixed | Determines if FlexCel will delete or just clear ranges with empty datasets (0 records), for FIXED bands. Note that normally you don't want Fixed bands to delete rows, so this property defaults to TDeleteEmptyBands.ClearDataOnly. |
DeleteEmptyBands | Determines if FlexCel will delete or just clear ranges with empty datasets (0 records). Note that this property doesn't apply to FIXED bands, those are controlled with DeleteEmptyBandsFixed |
UseExcelTablesAsBands | Determines if FlexCel will use Tables named as "__table__" or similar as band definitions. |
RenameExcelTablesUsedAsBands | Determines if FlexCel will rename Tables used as band definitions after the report is run to remove the extra "__" in the name. |
AllowOverwritingFiles | Determines if FlexCel will automatically delete existing files or not. |
ResetCellSelections | When true, all sheets will selections will be reset to A1. This way, you do not need to care about setting the correct selection when editing the template. |
HtmlMode | When true, FlexCel will interpret the text as HTML, and honor the tags that it can understand. Note that when in HtmlMode, many consecutive spaces will be interpreted as one, and carriage returns will be interpreted as spaces. To enter real carriage returns you need to enter a tag (unless the text is inside <pre> tags). Also & symbols need to be escaped. For more info on HTML syntax supported, see TExcelFile.SetCellFromHtml(Integer, Integer, string, Integer)...[more] |
DisableSQLValidation | If false (the default) FlexCelReport will only allow DirectSQL queries that begin with "SELECT", to avoid people doing inserts or deletes from the config sheet. If true, FlexCelReport will pass the DirectSQL queries you write in the config sheet directly to the server. Caution: Setting this property to true might have security implications. Take a look at the remarks. |
EnterFormulas | When true, FlexCel will try to enter any string starting with "=" as a formula instead of text. If this property is true, any string you enter that starts with "=" must be a valid formula, or an error will be raised. When you know a priori which cells will have formulas, you might want to use the <#formula> tag instead. |
TryToConvertStrings | When true, FlexCel will try to convert strings to numbers or dates before entering them into the cells. USE THIS PROPERTY WITH CARE! You shouldn't normally need to use this property, since FlexCel automatically enters numbers or dates in the DataSets as number or dates in the Excel file. If you need to use this property, it means that data in your database is stored as strings when they should not be. So the correct fix is to fix the columns you know should have numbers to have numbers, NOT to use this property. This is just a workaround when you can't do anything else about it....[more] |
ExpressionRecursionLimit | Defines how much nesting you can have in Expressions before FlexCel throws an error. You might have an expression like <#A> which is defined based in another expression <#B> which in turn is defined based in another one that finally might come back to <#A> As it is not possible for FlexCel to know if the recursion will finish or loop forever, it will try until it reaches the limit you set here. Note that a too big limit could cause a stack overflow. |
RecalcMode | Determines if the report will be recalculated before saving. See TExcelFile.RecalcMode for more info. |
RecalcVersion | Before changing this property, look at TExcelFile.RecalcVersion Determines if the formulas will be recalculated when Excel opens them. |
ErrorActions | Determines if FlexCel will throw Exceptions or just ignore errors on specific situations. When the errors are ignored, they will be logged into the TFlexCelTrace class. |
ErrorsInResultFile | When true and there is an error reading cells in the template or writing the cells in the report, the error message will be written in the corresponding cell on the generated report. No Exception will be thrown. You can use this property to DEBUG reports, as it provides an easy way to see all errors at once in the place they are produced. But is it recommended that you leave this property FALSE in production, or you could create xls files with error messages inside. See also DebugExpressions |
DebugExpressions | Set this value to true if you want to analyze how FlexCel is evaluating the tags in a file. When true, a full stack trace will be written in the cell instead of the tag values. See 'Debugging reports' in the Reports Designer Guide for information on how to use those stack traces. |
DebugIntelligentPageBreaks | Set this value to true if you want to analyze how FlexCel is setting up the intelligent page breaks. When this property is true, FlexCel will add one row at the top of the spreadsheet (if the sheet has keeptogether column ranges), and one column at the left of the spreadsheet (if the sheet has keeptogether row ranges). This row and column will show the levels of keeptogether in the respective column and row. See 'Debugging intelligent page breaks' in the Reports Designer Guide...[more] |
SqlParameterReplace | Format string for replacing the standard parameter names on DIRECT SQL commands. You can leave it empty for ODBC, OLEDB or SQLSERVER databases. See Also SqlParametersType |
SqlParametersType | Type of parameters for the database. Positional parameters are the ones where you write "?" on the sql, and positional are when you write a name, like "@employee" or ":orderid". See Also SqlParameterReplace |
SemiAbsoluteReferences | When this property is set to true, absolute references to cells inside bands being copied will be treated as relative. This way, if you have "=$A$1" inside a band and cell A1 is also inside the band, it will change to A2,A3..etc when the band is copied down. This can be useful in a master-detail report, where you want the cells in the detail to point to a fixed cell inside every record of the master. See TExcelFile.SemiAbsoluteReferences...[more] |
Events
Name | Description |
---|---|
BeforeReadTemplate | Fires before starting to generate the report and before the template has been loaded. It allows you to provide the template password if you are using one. |
BeforeGenerateWorkbook | Fires before starting to generate the report but after the template has been loaded. It allows to do some in-place modifications to the template before generating the report. |
AfterGenerateWorkbook | Fires After the report has been fully generated but is not saved. Allows to do last clean up things before saving the report. |
BeforeGenerateSheet | Fires Before each sheet on the file is generated. |
AfterGenerateSheet | Fires After each sheet on the file is generated. |
GetImageData | Fires before an image is saved to the report. Use it if the image is on a proprietary format on the database, to return a format FlexCel can understand. |
CustomizeChart | Fires for each chart in each sheet, after the report has been generated. Allows to do custom modifications to the charts, like for example setting series colors. |
GetInclude | Fires before including a file with <#include>. Use it if you want to provide an alternative path for the file, of if you want to read the include file from a different place, for example a database or an embedded resource. |
UserTable | Fires on each <#USER TABLE> tag in the config sheet, allowing to add your own datasets to the report. |
LoadTable | Fires whenever an undefined table is called, allowing to load your own datasets in demand to the report. For more control, you might use User Tables. Look at the example for more information. |