[Add to favorites]. Net about enterprise Excel report generation

In general enterprise application development, report generation is involved, and the general report format is Excel format. The generation of various reports has always been a pain point for programmers, because for many programmers, every time they write a report, they need to write a large piece of code to implement it, in addition, some reports may be extremely complex and non-standard, which will waste a lot of time for programmers to write and debug the code. Is there any way to minimize the write of the code, in addition, we can generate various EXCEL reports. Next we will turn to the subject.

To generate an Excel report, we only need two things:

1. the report style, that is, where the field should be filled, what should be filled in the column, the font size, color, cell height, width, and whether the cells are merged and cross-row, cross-column or horizontal report (the so-called horizontal report, which is defined as an Excel report generated with a record or a record and its associated records, for example, an order report is usually generated by an order header and its related order details. This is my custom horizontal report) or a vertical report (so-called vertical report, custom: A report is generated by a record set. to generate a report that records the student status of a class, to export the student records of this class to this report, this report is basically a statistical report. A report may have tens of thousands or more records, this is my custom vertical Report) and other report styles.

2. the report data has a report style for us. We know which cells or cells should be filled with the data and the specific data, which can be expressed in the form of a able, it can also be expressed in the form of dataset. You can define the data format as you like.

Now we know that as long as the style and data are available to generate an Excel report, we can generate a report we want. It is not difficult to generate data, however, the difficulty is what is used to describe the report style and how to describe it.

We can use two methods to describe the style:

1. directly use XML to describe the content of this XML description file. The content of this description file can be described as follows (for example, the description of a column is not very standard ):

<? XML version = "1.0" encoding = "UTF-8"?>

<Style>

<Excelstyle sort = "portrait" Space = "1">

<Ranges>

<Rangestyle>

<Range cellwidth = "2.0" cellheight = "20" cellbord = "1" cellbackcolor = ""> </range>

<Titlecell titlename = "part No." titlestartxpos = "1" titleendxpos = "1" titlestartypos = "1" titleendypos = "1"> </titlecell>

<Datacell dataname = "partno" datastartxpos = "2" dataendxpos = "14" datastartypos = "34" dataendypos = "34" ismerger = "false" align = "xlhalign. xlhalignleft "istoarray =" true "isdatetime =" false "interval =" 1 ">

</Datacell>

<Titlefont titlefontname = "" titlefontsize = "9" titlefontcolor = "black" titlefontbold = "false" titlefontitatic = "false" titlefontposition = "center"> </titlefont>

<Datafont datafontname = "" datafontsize = "11" datafontcolor = "black" datafontbold = "true" datafontitatic = "false" placement = "false" datafontposition = "center">

</Datafont>

</Rangestyle>

</Ranges>

</Excelstyle>

</Style>

In this XML, the node excelstyle attribute sort describes whether the entire Excel format is vertical or horizontal (the so-called vertical or horizontal, as described above ), the attribute space describes the number of blank rows between the rows of each record in the report. Ranges is the parent node of rangestyle of all the following subnodes. For a report, there are usually many rangestyle nodes. Rangestyle actually begins to describe which cells or fields to fill in. For example, the node range defines the attributes of cell height, width, Border width, and color. Titlecell defines the attributes of the title, such as the title name and the cell in which the title is defined. The node datacell defines which field in the data to help determine, from which unit to fill in, whether the cell is to be merged, alignment, and the interval between the field row and row. Node title Font defines the title Font attributes, such as size, color, italic, bold, and alignment. Node datafont defines the data font attributes, such as size, color, italic, bold, and alignment. A rangestyle node defines a field. If you want to fill in many fields in your report, there will be many rangestyle nodes to describe. This XML style definition file can be generated by writing a tool by myself. I have a ready-made generation tool called excelstyle, but it is still very simple and inconvenient to use, however, you can generate the style you need. Which of the following can I send to you? (I don't know how to download it ).

2. the combination of XML Description files and Excel templates is complicated for some reports, especially those horizontal reports. In this case, a ready-made excel template is required, with an Excel template, we can refer to the template to generate an XML style definition file. When generating a report, we only need to know which excel template is used and which XML style file is used, after binding our data, even complex reports can be generated.

For specific usage, refer to the following test column code (declare exceloperate, xmloperate is a few general components I write myself, if necessary, I can send an email to you, here, the data is described in the form of a able ):

Sing system;

Using nunit. Framework;

Using exceloperate;

Using xmloperate;

Using system. Data;

Using system. Data. sqlclient;

Namespace testexcel

{

/// <Summary>

/// </Summary>

///

[Testfixture]

Public class excelexport

{

[Test]

Public void Export () // you do not need to export data from an Excel template.

{

String SQL = "select Top 100 * from MERs ";

// Retrieve 100 pieces of data

Excelfactory _ factory = new excelfactory ();

// Define an excelfactory without Parameters

_ Factory. createexcelwithouttemplate ("C: \ bbbbbbb. xml", getdatatable (SQL ));

// Load the defined Style File bbbbbbbbb. XML and the Retrieved Data getdatatable (SQL)

_ Factory. saveexcel ("C: \ bbbbbbbbbbbbbbbb.xls ");

// Save the exported file.

}

[Test]

Public void exportwithtemplate () // export data from an Excel template

{

String SQL = "select top 1 * from MERs ";

// Retrieve a piece of data

Excelfactory _ factory = new excelfactory ("C: \ qstandardcustomer.xls ");

// Import model version qstandardcustomer.xls

_ Factory. createexcel ("C: \ qstandardcustomer. xml", getdatatable (SQL ));

// Load the defined Style File qstandadrcustomer. XML and the Retrieved Data getdatatable (SQL)

_ Factory. saveexcel ("C: \ qreportstr.xls ");

// Save the exported Excel file.

}

Private datatable getdatatable (string SQL)

{

String myconnectionstring = "packet size = 4096; user id = sa; Data Source = 172.28.200.98; initial catalog = northwind ";

Sqlconnection myconnection = new sqlconnection (myconnectionstring );

Myconnection. open ();

Sqldataadapter myadapter = new sqldataadapter ();

Sqlcommand mycommand = new sqlcommand (SQL, myconnection );

Mycommand. commandtype = commandtype. text;

Myadapter. selectcommand = mycommand;

Dataset DS = new dataset ();

Myadapter. Fill (DS );

Myconnection. Close ();

Return Ds. Tables [0];

}

}

}

As shown in the code above, generally, the report is generated with three sentences of code. Even for some rows in the report, we do not need to modify the program to adjust some formats. We only need to modify the XML description file, in this way, we can greatly save time in generating reports, and put more time on the processing of business logic.