Open source Components excelreport 1.5.2 User's Manual

Excelreport is a report engine component that is based on npoi development. It separates data from styles and formats based on the idea of separation of concerns. Let template hosting style, format, etc. npoi not good at and implement cumbersome information, combined with the advantages of NPOI data processing, Excel report generation to simplify. At the same time, the basic elements of the composition of the report are abstracted, which further simplifies the process of generating Excel reports.

Official site

Nuget:https://www.nuget.org/packages/excelreport

Github:https://github.com/hanzhaoxin/excelreport

Cnblogs:http://www.cnblogs.com/hanzhaoxin/tag/excelreport/

QQ group:116476496

Team Introduction

Jensen

From Shenzhen, China, is the initiator and developer of this project, and began development of Excelreport in November 2014, responsible for excelreport development, testing and bug fixing.

Personal blog Address:/HTTP hanzhaoxin.cnblogs.com

License description

Excelreport uses the MIT license, which means it can be used for any commercial or non-commercial project, and you don't have to worry about using it to open up your own source code and to promote your product with the influence of Excelreport.

Of course, as an open source license, there are certainly some obligations. For example, all copies of the software and software must contain the above copyright notice and this license statement.

For a complete MIT license, see: http://mit-license.org/

Version Upgrade Instructions:

If you are using an upgraded version of Excelreport,bug repair and feature extensions in your project, you may not be able to affect your existing projects without having to upgrade them with confidence. If schema modification version information has changed, be careful to upgrade, which can affect your existing projects.

Why do you use Excelreport?

Before we answer this question, let's take a look at Tony Qu's answer, why use Npoi?

1) You do not need to install Microsoft Office on the server to avoid copyright issues.

2) Npoi is more convenient and user-friendly than the Office PIA API.

3) You don't have to spend a lot of effort to maintain Npoi,npoi Team will constantly update, improve npoi, absolute cost savings.

4) Many things are not done by HTML and CVS, for example, formula calculation [cell C1]=A1+B1*A2 cells

Advanced styles such as text rotation, alignment, width, and so on, where formula calculation can properly reduce the computational pressure on the server side

Back to our question, why use Excelreport?

1) Complex things are simple, impossible things are possible.

2) You do not need to know npoi a large number of APIs that you do not need to use. NET language to write crappy Java syntax.

3) Using Tony Brother's description, you don't have to spend a lot of effort to maintain Excelreport,excelreport team will constantly update, improve excelreport, absolute cost savings.

Directory

1 report element and element formatter

2 Demo: Generate a report using Excelreport

3 Formatter Example

3.1 Local Formatter

3.2 Cell Formatter

3.3 Tabular Formatter

3.4 Repeating Cell Formatter

4 Multi-Sheet report generation

1 report element and element formatter

Before we start the example, let's take a chapter to explain how Excelreport abstracts the contents of a report into elements.

How do you populate the data for an element? To illustrate the first question, we start with an existing report.

The following report has two sheet:sheet1 named "Payroll" Sheet2 named "Payroll Bar", which is shown below.

Let's mark the data department with a blue frame first.

The name is the right word, first talk about the concept of report elements in Excelreport:

Elements: Populate a Data source object in a report template, which we call an element.

Local elements: A data source object that is populated into a report template is part of a cell's contents, and we call such a data source object as a local element.

Cell elements: A data source object that is populated into a report template is the contents of a cell, and we call such a data source object as a cell element.

Table elements: A data source that is populated into a report template is a collection of objects that are a collection of content for multiple cells in a row, and we call such a collection of data sources as tabular elements.

Repeating cell elements: a data source that is populated into a report template is a collection of objects that are a collection of content for multiple cells in multiple rows, and we call such a collection of data sources as repeating cell elements.

The table element and repeating cell element data sources appear as collections. is a complex element. 】

"Corresponding, local elements and cell elements are called single elements. 】

With the definition, we put the elements in position.

So, how does excelreport populate the data for the element?

The words element formatter is for this purpose.

Report elements	Element formatter
Local elements	Partformatter
Cell element	Cellformatter
Table elements	Tableformatter
Repeating cell elements	Repeaterformatter

The role of the element formatter is to populate the report template with formatting elements. 】

2 Demo: Generate a report using Excelreport

The target report, which generates the Payroll-payroll report, which is analyzed in the previous chapter.

First step: Design the template

"Parameter format in Template: $[parametername]"

Step two: Generate template fill rule file from template

1) Open the template Fill rule file generation tool

2) Drag a well-designed template into the Excel template file selection box

3) Click "Generate Template Rule file (. XML) button to generate the rule file.

Step three: Populate the template, generate a report "to add a reference to Excelreport and Npoi (more than 2.0) in the project"

To generate an export report:

"Note: demo example" generate Payroll Table-Payroll Report "Source See Solution: Excelreportexamples"

3 Formatter Example

Using the example in the previous chapter, we learned the steps of Excelreport to generate a report, and also used various formatters. In this chapter, we will explain each of the formatters in order to understand them in more detail.

3.1 Local Formatter

A local formatter is used to format a portion of the contents of a cell that is populated. The Fill data type is string.

Constituent functions:

Parameters	Describe
CellPoint	Location of the cell where the parameter is located
ParameterName	Name of parameter
Value	The value to format the fill

Example: (Local formatter example: Hello$[username],welcome in Excelreport ' s group $[groupno].)

First step: Design the template

Step two: Generate template fill rule file from template

Step three: Populate the template, generate the report

To generate an export report:

3.2 Cell Formatter

The cell formatter is used to format the fill of a cell. The Fill data type is object, as described in the following table:

Fill data type	Excel the corresponding data type in	Visual Setup Items
String	String
Datetime	Datetime
Boolean	Boolean
Int16 Int32 Int64 Byte Single Double UInt16 UInt32 UInt64	Double
Byte[]	Image

Constituent functions:

Parameters	Describe
CellPoint	Location of the cell where the parameter is located
Value	The value to format the fill

Example: (Example of a cell formatter)

First step: Design the template

Step two: Generate template fill rule file by template (see Chapter 2nd "Demo" for details)

Step three: Populate the template, generate the report

To generate an export report:

3.3 Tabular Formatter

The table formatter is used to format the fill of a table. The Fill data type is ienumerable<tsource>.

constructor function:

Parameters	Describe
Templaterowindex	Template Row Row Labels
DataSource	To format the populated data
Columninfos	Column information collection

Tablecolumninfo constructor:

Parameters	Describe
ColumnIndex	Column label
Dgsetvalue	Func<tsource, the object> type of the delegate object (the return value of object, you can refer to the second section to populate the data type with the data type table in Excel.) ）

Example: (Table formatter example)

First step: Design the template

Step two: Generate template fill rule file by template (see Chapter 2nd "Demo" for details)

Step three: Populate the template, generate the report

To generate an export report:

Question reply: "About Tableformatter has the following knowledge points, repeatedly asked, here to make a unified answer." 】 Question one: Can the Tableformatter data source be a DataTable? Answer: You can write this: Problem two: My entity class does not implement an outer chain, in the entity object has an id attribute, I want to export the content is the ID corresponding to the name, what to do? A: There's no difference between "sex" in the example. All right, stick to the line code:

3.4 Repeating Cell Formatter

The repeating cell formatter is used to format the fill of a repeating cell element. The Fill data type is ienumerable<tsource>.

constructor function:

Parameters	Describe
Starttagcell	Repeating cell (start) Identifying cells
Endtagcell	repeating cell (end) Identifying cell
DataSource	To format the populated data
Cellinfos	A collection of cell information contained in a repeating cell

Repeatercellinfo constructor:

Parameters	Describe
CellPoint	Cell coordinates
Dgsetvalue	Func<tsource, the object> type of the delegate object (the return value of object, you can refer to the second section to populate the data type with the data type table in Excel.) ）

Example: (repeating cell formatter example)

First step: Design the template

Hides the repeating unit identity row as required.

Step two: Generate template fill rule file by template (see Chapter 2nd "Demo" for details)

Step three: Populate the template, generate the report

To generate an export report:

4 Multi-Sheet report generation

So far, all of our exports are directly using static methods in the Exporthelper class. In fact, in the design of the Excelreport component, the export class is the end point, Exporthelper class just as its name is simply to facilitate the operation of the helper class provided.

"Oh, it's still part of the class, you can expand on demand. 】

Back to the topic of this chapter, we are going to talk about the generation of multi-sheet reports. What the? What does this have to do with the front-winded ones? Well, in fact, the relationship is not big, I just want to say that the generation of attention to the report, you see the export class is right.

Parameters	Describe
TemplateFile	Template file path
Containers	Sheet each element in an array of formatted containers corresponds to a sheet "

Example: (Multi-sheet report generation example)

First step: Design the template

Step two: Generate template fill rule file by template (see Chapter 2nd "Demo" for details)

Step three: Populate the template, generate the report

To generate an export report:

Open source Components excelreport 1.5.2 User's Manual