FlexCelReport 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

Namespace: FlexCel.Report

public class FlexCelReport: Component

Constructors

Name	Description
FlexCelReport	Overloaded FlexCelReport FlexCelReport(Boolean)

Methods

Name	Description
Run	Overloaded Run(ExcelFile) Run(String, String) Run(Stream, Stream) Run(Stream, Stream, TFileFormats)
Cancel	Cancels a running report. This method is equivalent to setting Canceled = true.
AddTable	Overloaded AddTable(DataSet) AddTable(String, DataTable) AddTable(String, DataView) AddTable(DataSet, TDisposeMode) AddTable(String, VirtualDataTable) AddTable<T>(String, IEnumerable<T>) AddTable(String, DataTable, TDisposeMode) AddTable(String, DataView, TDisposeMode) AddTable(String, VirtualDataTable, TDisposeMode) AddTable<T>(String, IEnumerable<T>, 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	Overloaded AddConnection(String, IDbDataAdapter, CultureInfo) AddConnection(String, IDbDataAdapter, CultureInfo, Boolean) AddConnection(String, IDbCommand, CultureInfo, Boolean)
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. 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, String[], String[])
ClearRelationships	Clears all relationships added by AddRelationship(​​String, String, String, String).
OnBeforeReadTemplate	Replace this event when creating a custom descendant of FlexCelReport. See also BeforeReadTemplate
OnBeforeGenerate​Workbook	Replace this event when creating a custom descendant of FlexCelReport. See also BeforeGenerate​Workbook
OnAfterGenerate​Workbook	Replace this event when creating a custom descendant of FlexCelReport. See also AfterGenerate​Workbook
OnBeforeGenerate​Sheet	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
Dispose	Override this instance when creating descendants of FlexCelReport. Remember to always call base.​Dispose(​disposing)​.​

Properties

Name	Description
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).
DeleteEmptyBands​Fixed	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 TDeleteEmpty​Bands.​Clear​Data​Only.
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 DeleteEmptyBands​Fixed
UseExcelTables​AsBands	Determines if FlexCel will use Tables named as "__table__" or similar as band definitions.
RenameExcelTables​Used​AsBands	Determines if FlexCel will rename Tables used as band definitions after the report is run to remove the extra "__" in the name.
AllowOverwriting​Files	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 <br> tag (unless the text is inside <pre> tags). Also & symbols need to be escaped. For more info on HTML syntax supported, see ExcelFile.​Set​Cell​From​Html(​​Int32, Int32, String, Int32)...[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]
ExpressionRecursion​Limit	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 ExcelFile.RecalcMode for more info.
RecalcVersion	Before changing this property, look at ExcelFile.​Recalc​Version 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 FlexCelTrace 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...[more]
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.
DebugIntelligent​Page​Breaks	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
SemiAbsolute​References	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 ExcelFile.​Semi​Absolute​References...[more]
UseLegacyLookup	When true, FlexCel will use DataViews to do lookups in DataSets, instead of using its own internal lookup algorithm. Only set this property to true if you have legacy reports that break when you use the internal lookup algorithm, because they depended in bugs in the DataView lookup implementation. For all new code, keep this property false.

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.
BeforeGenerate​Workbook	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.
AfterGenerate​Workbook	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.