Code Flexcel Report Như Thế Nào

Introduction

This oimlya.comument is a part of a 2-part description on how khổng lồ create Excelfiles by “reporting” instead of with code. In this part we will look athow khổng lồ mix up the coded needed to lớn create the files, & in the next partFlexCel Reports Designer Guide we will look at how to modify the Excel tệp tin used as a template khổng lồ create the reports.

Bạn đang xem: Code flexcel report như thế nào

About Excel Reporting

FlexCel gives you two ways to lớn create an Excel file:

Each method has its good và bad things, & it is good to lớn know theadvantages & drawbacks of each.

Creating a report using XlsFile is a low-level approach.As with most lower màn chơi approaches, it will be very fast to lớn run (if coded right!),và you will have sầu access to lớn the entire API, so you can dowhatever you can vì chưng with FlexCelReport & more. After all,FlexCelReport uses XlsFile internally to lớn work its magic.

Whatever you can bởi in FlexCelReport, you can vì it with the API.

But you need to lớn take a lot of care when coding directly in the API: XlsFile reports can be a nightmare tomaintain if not coded correctly. When you reach enough number of lines like:

xls.SetCellValue(3, 4, "Title"); var fmt = xls.GetFormat(xls.GetCellFormat(3, 4)); fmt.Font.Name = "Helvetica"; fmt.Font.Sizeđôi mươi = 14 * 20; int XF = xls.AddFormat(fmt); xls.SetCellFormat(3, 4, XF);changing the report can become quite difficult. Imagine the user wantsto lớn insert a column with the expenses (và don't ask why, users alwayswant lớn insert a column).

Now you should change the line:

xls.SetCellFormat(3, 4, XF);to

xls.SetCellFormat(3, 5, XF);But wait! You need to lớn change also all references to lớn column 5 to lớn 6, from6 to lớn 7... If the report is complex enough, (for example you have sầu amaster-detail) this will be no fun at all.

But there is something much worse with using XlsFile directly. And this is that only the author can change the report.If your user wants to lớn change the hình ảnh lớn a new one, or maybe make column C a little wider, he needs lớn callyou và you need lớn recompile the application and send him a newexecutable.

FlexCelReport is a higher cấp độ approach. The design is cleanlyseparated on three different layers, data layer, interface layer andpresentation layer, và most of the work is done on the presentationlayer, with Excel. You design the report visually on Excel, and you messas little as possible with the data layer. If the user wants khổng lồ changethe biểu tượng logo, he can just open the template & change the hình ảnh sản phẩm. If he wantsto insert a column, he can just open the template and insert a column.And the application does not even need to be recompiled.

As with any higher màn chơi approach, it is slower than using the APIdirectly, and there are some things where it is more limited. But all inall, reports are really fast too & the things you cannot bởi are in many cases not worth doing anyway.

So, the option is yours.

Organization of a FlexCel Report

A FlexCel report can be seen as three different modules working togetherto create the Excel file. Different from a “Coded” report where there is not a clear separation betweendata access and presentation, here each part has its own place, & canbe developed separately by different people with different skills.

Data Layer

This is the layer that contains the data mã sản phẩm of the information we want to lớn sover out. The data might be stored at a database, or in lists of objects in memory.

Interface Layer

This layer works as the glue between the data và the presentation layers. It has to prepare the data in a way that the presentation layer can easily consume.

Presentation Layer

This is the most complex & changing layer of the three. Here is whereyou kiến thiết all the visual aspects of the report, like data position,fonts, colors, etc.

The big advantage of this “layering” is that they are somehowindependent, so you can work on them at the same time. Also, Data andInterface layers are small và bởi not change much on the lifetime of theapplication. Presentation does change a lot, but it is done completelyin Excel, so there is no need khổng lồ recompile the application each time acell color or a position changes.

Note

The data flow goes from the Presentation layer to lớn theData layer & baông chồng. It is the presentation that asks FlexCel for thedata it needs, and FlexCel that in turn asks for the data. It is not theapplication that tells FlexCel the data it needs (As in SetCellValue(xx) ),but FlexCel that will ask for the data khổng lồ the application when it needsit.

On this oimlya.comument we are going to speak about Data và Interfacelayers. The Presentation layer is discussed on a different oimlya.comument,FlexCel Reports Designer Guide because it is complex enough khổng lồ deserveit, and because it might be changed by someone who only understandsExcel, not .NET. And we don't want lớn force hlặng to read this oimlya.comumenttoo.

Data Layer

The objective of the data Layer is to lớn have sầu all the data ready for the reportwhen it needs it. Currently, there are six ways to lớn provide the data:

Via User defined functions: User defined functions are classesyou define on your code that allow for specific needs on thepresentation layer that it can't handle alone. For example, youmight define a user function named “NumberToString(int number) thatwill return “One” when number=1, “Two” when number =2 and so on. Onthe presentation layer you would write to lớn obtain the string “Two”

Via DataSets: .NET DataSets are a collection of memory tablesthat can be sorted, filtered or manipulated. Whatever format yourdata is, you can use it to fill a DataTable and then use that dataon FlexCel.

Via Direct Squốc lộ in the template. For maximum flexibility, you canset up the data layer on the template too. In this case, you need toadd a database connection to lớn FlexCel, và all the rest is done onthe template. This allows the users lớn completely edit the reports,even the data layer from the template, but also it allows users tovì chưng things they might not be intended to. Use it with care.

Via Virtual Datasets: If your business objects don’t implementIQueryable, and the overhead of copying your existing objects todatasets is too much, you can implement your own wrapper objects toprovide this data khổng lồ FlexCel without copying it. But please takethis option with care, as it might be simpler và more performing tojust dump your objects into a DataSet và use that. FlexCel has an optimized implementation of a virtual datamix for DataSets, and if you create your own virtual datasets youwill have sầu to lớn implement a similar màn chơi of performance (and functionality). Please readthe Appendix II for a more detailed mô tả tìm kiếm of virtual datasets.

Linq based DataSources

With FlexCel you can use any object that implements IEnumerableas a datasource for a report (in .NET 3.5 or newer). Internally, FlexCelwill use Linq to query the objects and read the results.

To use Linq, you just need a collection of objects. For every object inthe collection, you can use all the public properties as fields in thereport.So if for example you have sầu the following class:

class MyObject public string FirstName get; set; public string LastName get; set; And the các mục of MyObject:

var MyObjectList = new ListMyObject>();You can add this collection as a datasource for the report with theline:FlexCelReport.AddTable("MyName", Objs)

And then in the template write & inside a “__MyName__” range to export thecollection.

You can also use Entity Framework collections or any other of collectionthat implements IEnumerable as datasource.

Note

Record count: When FlexCel runs a report from anIEnumerable datasource, it needs khổng lồ know the count of objects inadvance, so it can insert the rows before filling the data. For normalcollections like List this isn’t a problem since theyprovide a “Count” method that will be used by Linq & it is very fast.

For Entity Framework collections, two different SQLs will be sent to lớn thehệ thống, once to get the record count và the second to actually fetchthe records. Other collections could have sầu no way to lớn know the recordcount in advance, và in those cases Linq will loop over all objects inthe collection khổng lồ get the count. In this third case, it will be fasterif you provide the record count yourself. Look at the Performanceguide lớn see how you can supply the record count.

Note

Transactions: When you are running a report that accessthe database (like a report from Entity Framework objects) you must besure that the database isn’t updated while the report is running.

As said in the note above, in database reports FlexCel sends two SQLs tothe server for every table, the first lớn get the count & the second toget the data. If when you run the second Squốc lộ the number of recordschanged, you will see weird things in the report as the number of rowsinserted won’t be the same as the number of records written to thereport. Also for master-detail & even a single table, you must makesure that data doesn’t change while it is being fetched. To ensure this,the report should be run inside a “Snapshot” or “Serializable”transaction.

Snapshot transactions are preferred if supported becausethey don’t block other users from modifying the tables while the reportruns. Please take a look at the Performanceguide and the EntityFramework kiểm tra for more information about transactions.

Datamix based DataSources

Besides Linq, FlexCel also allows using .NET DataSets as datasources.DataSets are less flexible than LINQ datasources & can use morememory, but on the other hand, they can be fasterthan Linq in many cases. Also, you might have sầu already your data inDataSets, and in this case nothing will be faster than a direct reportfrom it.

The reason why it uses more memory is that it has lớn fill thedataset before running the report, loading all the data into memory. Thereason it can be faster many times is that it loads all the data justonce from the database, & then works from memory, avoiding furtheraccess to the database which can be slow.

To use a DataSet in a report, just add the dataphối or the tables with:

FlexCelReport.AddTable(DataSet);

Or any of the similar AddTable overloads.

As with LINQ, if there is a chance that the data can be modified whileyou are filling the datasets, those datasets must be filled inside a“Snapshot” or “Serializable” transaction. An advantage of datasets hereis that those transactions will complete faster. Once the data is loadedin the dataphối, you can run the report without worries about other userschanging the data, as it is all loaded in memory.

Data Relationships

FlexCel supports Master-Detail reports, where you have a “master”datasource và a “detail” datasource where for every master record youhave different detail records. You could have an “employee” table andfor every employee, the orders they sold.

For these kinds of reports, you must tell FlexCel which is the master andwhich is the detail. Also, you must tell FlexCel how those two tablesare related; for example, both tables could be related by an“EmployeeId” field that is used khổng lồ know which orders correspond to whichemployee.

You can specify those relationships in two ways:

Implicit Relationships

When the individual objects inside a collection of objects contain apublic property that is itself a collection of objects, the secondcollection is implicitly a detail of the first. For example, if you havethe class:

class Employee public string FirstName get; set; public string LastName get; set; public ListOrder> Orders get; set; Then “Orders” is an implicit detail of “Employee” & when you create aMaster-Detail report, FlexCel will output the correct orders for everyemployee.

Implicit relationships are used inEntity Framework, and are a simpleway to lớn provide master-detail relationships if you have your objectsorganized this way.

Explicit Relationships

Sometimes (as it happens with DataSets) you don’t have implicitrelationships in your data. You might have sầu two different Lists ofobjects:

ListEmployee2> Employees; ListOrder2> Orders;Where the objects are defined as:

class Employee2 public int EmployeeId get; set; public string FirstName get; set; public string LastName get; set; class Order2 public int EmployeeId get; set; public int OrderId get; set; public string OrderName get; set; And related by an Id (in this case EmployeeId). If you were to create areport out of those two lists, you would get the full list of orders forevery Employee. You need khổng lồ tell FlexCel that the “Employees” collectionis related lớn the “Orders” collection by the EmployeeId.

You can add explicit relationships with

FlexCelReport.AddRelationship(...)

But you will almost never need to vày so. FlexCel automatically loads allexisting Data Relationships in datasets, so if the tables on yourdatamix are related, then you don’t need lớn do anything else.

For other objects different from datasets you will probably want to lớn useimplicit relationships. But there might be cases where you need explicitones. Explicit relationships are more powerful than implicit, in thesense that you can have sầu the same tables related in different ways. Forexample, The “Orders” collection could have sầu also an explicitrelationship with a “Date” table. If you run the report “Orders by Date”then that relationship will be used. If you run the report “Orders byEmployee” then the employee relation will be used. If you had implicitrelationships, then both “Date” và “Employee” collections would need an“Orders” field, and that could lead khổng lồ repeated data.

Explicit data relationships can be defined with any objects, evenbetween DataSets & and LINQ collections. Whenever FlexCel finds anexplicit Data Relationship, it will filter the detail table lớn show thecorrect records for the master.

Xem thêm: Cách Kiểm Tra Tivi Mới Mua, Vì Sao Cần Phải Kiểm Tra Tivi Led Mới Mua

Tip

You might also define the relationships directly in thetemplate instead of by code. This is useful when using Direct Squốc lộ, sincethe tables are defined directly in the Excel template & you can’t thiết đặt the relationships in code. Also, you can addcustom relationships not related to lớn datasets, when using virtual datasets.

Interface Layer

This is the simplest of the three, as all work is done internally. Tomix up the interface layer, you only need khổng lồ tell FlexCel which datasets,tables and user functions it has available from the data layer. Forthis, you use “SetValue”, “SetUserFunction”, “AddTable” and“AddConnection” methods on FlexCelReport. Once you have told FlexCelwhat it can use, just call FlexCelReport.Run & this is all.

Note

It is worth spending some time thinking aboutwhat tables, functions và variables from the data layer you want tomake available for the final user. Limiting it too much might meancutting the power of the user to lớn customize his report. And just addingeverything might be too inefficient, since you are loading thousands ofrows the user is not going khổng lồ use, and might also have sầu some securityissues, as the user might get access khổng lồ tables he is not expected lớn.You can also use SQL on the templates to allow maximum flexibility, butthen you need to be extra careful about security.

Appendix: Virtual DataSets

This is an advanced topic, targeted to experienced developers with really specific needs.

As explained earlier, the easiest way khổng lồ provide data khổng lồ FlexCel is via IQueryable collections or DataSets. Both methods are fast, optimized,with lots of added functionality, lượt thích data relationships, filtering orsorting, và if you are experiencing issues with them, you areprobably better using them.

But you might want khổng lồ use your own objects directly, và they might not be stored in any of those containers.

You can vày this by writing wrapper objects around your data,implementing the abstract classes **** and****. In fact, standard IQueryable collections andDatasets also interoperate with FlexCel by implementing those classes.

Note

If you are curious and have sầu FlexCel source code, you can tìm kiếm for TLinqDataTable,TLinqDataTableState, TAdoDotNetDataTable andTAdoDotNetDataTableState lớn see how they are implemented.

We have two classes lớn implement:

On the other side we have . EachVirtualDataTableState corresponds with a band on the presentationlayer, & if 2 bands nói qua the same data source they will have sầu 2 different VirtualDataTableState objects associated (but a single shared VirtualDataTable).

This is probably easier lớn visualize with an example. Let's imagine wehave sầu the following report:

And this code:

FlexCelReport.AddTable(Customers);FlexCelReport.AddTable(Company);FlexCelReport.Run();There are two VirtualDataTables here (Company and Customers), và threeVirtualDataTableStates (One for the Company Bvà, one for the firstCustomers B& & one for the second Customers Band)

Warning

Take note that the same VirtualDataTable is used by two differentVirtualDataTableStates, and might be used by otherVirtualDataTableStates in other threads.

This is why you cannot save sầu any“state” information on the VirtualDataTable, & if you write to anyprivate variable inside of it (for example to keep a cache) you shoulduse locks khổng lồ avoid threading issues.

Always assume that some other class might be reading your data.

VirtualDataTableState on the other hand is a simple class that will notbe accessed by more than one class at the time, và you can do whateveryou want inside it without worries of other threads trying to lớn access it.

Creating a VirtualDataTable descendant

The first step inkhổng lồ creating our own data access is khổng lồ create a VirtualDataTable descendant & override its methods.You vì not need toimplement every method khổng lồ make it work, just the ones that provide thefunctionality you want lớn give lớn your over users.

VirtualDataTable Required Methods:

On every DataTable you define, you need khổng lồ implement at least thefollowing methods:

GetColumn, GetColumnCaption, GetColumnName andColumnCount:

Those methods define the “columns” of your dataset, and the fields youcan write on the tags on the template.

CreateState: It allows FlexCel khổng lồ create VirtualDataTableStateinstances of this DataTable for each b&. You will not createVirtualDataTableState instances directly on your user code, FlexCel willcreate them using this method.

VirtualDataTable Optional Methods:

Now, depending on the functionality you want khổng lồ provide to lớn the over user,you might want khổng lồ implement the following methods:

FilterData: Will return a new VirtualDataTable with the filtereddata.

You need lớn implement this method if you want to lớn provide the user with theability to create new datasets by filtering existing ones on the configsheet. If you do not implement it, any attempt to create a filtereddataset on the config sheet will raise an exception.

Also when FlexCel needs to create a datamix that is a copy of theexisting one (for example for sorting it) it will gọi FilterData withrowFilter null. So even if you don’t implement filtering, it is normallya good idea lớn at least implement the case for “rowFilter” = null andreturn a clone of the datatable.

Note that this only applies khổng lồ standard filters. For or filters you vì chưng not need toimplement this.

GetDistinct: Will return a new VirtualDataTable with only uniquerecords.

Implement this method if you want to lớn let your user write filters on the config sheet.

LookUp: Will look for a record on the datamix, và return thecorresponding value. This method allows the user to lớn use tags on their reports.

GetRelationWith: Use this method to lớn return implicit relationshipsbetween your data tables. For example the VirtualDataTableimplementation of Datasets uses this method to return the ADO.NETDataRelations between datasets. The Linq implementation returns asrelated any nested dataset.

Creating a VirtualDataTableState descendant:

Again, you vì not need lớn implement every method in this class, và themethods you don't implement will just reduce functionality.

VirtualDataTableState Required Methods:

RowCount: Here you will tell FlexCel how many records this datasethas on its current state. Remember that this might not be the totalrecord count. If for example you are on a master-detail relationship,and the master is on record 'A', you need khổng lồ return the count of recordsthat correspond with 'A'. Make sure this method is fast, since it iscalled a lot of times.

GetValue: Here you will finally tell FlexCel what is the value ofthe data at a given row và column. You can know the row by reading the“Position” property, và the column is a parameter to the method.

As with RowCount, this method should return the records on a specificstate, not all the records on the datatable. If you are in amaster-detail relationship & only two records of detail correspond themaster position, GetValue(position = 0) should return the first record,and GetValue(Position = 1) should return the second. It doesn't matterif the total number of records is 5000.

VirtualDataTableState Optional Methods:

MoveMasterRecord: This method is called each time the master changesits position when you are on a master-detail relationship. You shoulduse it to lớn “filter” the data và cađậy the records that you will need toreturn on RowCount và GetValue. For example, when the master tablemoves khổng lồ record “B”, you should find out here all the records that applyto “B”, & cabịt them somewhere where RowCount và GetValue can readthem.

You should probably create indexes on the data on the constructor ofthis class, so MoveMasterRecord can be fast finding the information itneeds.

You vị not need khổng lồ implement this method if the VirtualDataTable is notgoing to be used on Master-Detail relationships or Split relationships.

FilteredRowCount: This method returns the total count of records forthe current state (similar khổng lồ RowCount), but, without considering Splitconditions.

If the dataset has 200 records, of which only đôi mươi apply for the currentmaster record, và you have sầu a Split of 5 records, RowCount will return 5and FilteredRowCount will return 20.

This method is used by the Master Split table lớn know how much recordsit should have sầu. In the last example, with a FilteredRowCount of đôi mươi and asplit every 5 records, you need 4 master records. You vị not need toimplement this method if you vì chưng not want to lớn provide “Split”functionality.

MoveFirst/MoveNext: Implement these methods if you want lớn dosomething in your data when FlexCel moves the active sầu record. Forexample, the LINQ implementation uses those methods lớn move theIEnumerator. The datamix implementation does nothing as it doesn’t needkhổng lồ traông chồng the changes in position.

EOF: Return true if the datamix is at the last record. If you don’timplement this method, the default implementation is to lớn check Position== RecordCount. But RecordCount might be expensive sầu lớn calculate, & inthis case, if you explicitly implement this method, it will work faster.

Finally

When you have defined both classes, you need lớn create instances of yourVirtualDataTable, and add them to lớn FlexCel with FlexCelReport.AddTable.

For examples of how the whole process is done,please take a look at the Virtual DataSet demo.