. NET about building an enterprise Excel report

Excel in the General enterprise application development will involve the generation of reports, and the general report format is generated in Excel format. The generation of a variety of reports has always been a pain in the minds of programmers, because for many programmers every report is meant to write a large number of code to implement, and some reports can be extremely complex and irregular, then waste a lot of time programmers to write and debug the code, Whether there is a way to make the code as little as possible, and to achieve a variety of Excel report generation, the following we turn to the topic.
We want to generate Excel reports, but we only need two things:
1. The style of the report, that is, which field should be filled in, which column should fill in what, font size, color, cell height, width, whether the cell is merged, whether across rows, across columns, is a horizontal report (the so-called horizontal report, I customize it to: An Excel report is a report that has a record or a record that is generated with its associated record, a single order report is usually generated by a single order header and its associated order details, which is my custom horizontal report or vertical report (the so-called vertical report, I customize it to: A report is generated by a recordset, to generate a report that records the student status of a class, the student record of the class is to be exported to this report, which is basically a statistical statement, a report may have tens of thousands of records or more, This is my custom vertical report) and so on report style.
2. Data on the report, for us to have a report style, we know in which or what cells should be filled with the data, the specific data, we can be in the form of a DataTable, can be expressed as a dataset, as for how to use the data format can be like, Define for yourself.
Now that we know that building Excel reports can generate a report of your own, as long as you have the style and data, it's not difficult to generate the data, but our difficulty is how to describe the style of the report, and how to describe it.

There are two ways to implement a description of a style:
1. Directly described in XML, this XML description of the main description of the content can be as follows (for an example to illustrate, now described is not very normative):
<?xml version= "1.0" encoding= "UTF-8"?>
<Style>
<excelstyle sort= "Portrait" space= "1" >
<Ranges>
<RangeStyle>
<range cellwidth= "2.0" cellheight= "cellbord=" 1 "cellbackcolor=" "></Range>
<titlecell titlename= "Part number" titlestartxpos= "1" titleendxpos= "1" titlestartypos= "1" titleendypos= "1" ></ Titlecell>
<datacell dataname= "PartNo" datastartxpos= "2" dataendxpos= "datastartypos=" "dataendypos=" "IsMerger=" False "align=" Xlhalign.xlhalignleft "istoarray=" True "Isdatetime=" false "interval=" 1 ">
</DataCell>
<titlefont titlefontname= "Song Body" titlefontsize= "9" titlefontcolor= "Black" titlefontbold= "False" titlefontitatic= " False "titlefontposition= Center" > </TitleFont>
<datafont datafontname= "Song Body" datafontsize= "one" datafontcolor= "Black" datafontbold= "True" datafontitatic= "False" datafonthyperlink= "False" datafontposition= "Center" >
</DataFont>
</RangeStyle>
</Ranges>
</ExcelStyle>
</Style>
The node-excelstyle attribute sort in this XML describes whether the formatting of the entire Excel is portrait or landscape (so I've described it as portrait or landscape), while property space describes how many rows are empty between rows and rows for each record in the report. Ranges is the parent of all of the following child nodes Rangestyle, and there are usually many rangestyle nodes for a single report. Rangestyle actually starts by actually describing which cell or cells to fill which fields, such as the node range defines cell height, width, border width, and color properties. Titlecell is the property that defines the report (because I have a vertical report, so there is title), such as the name of title, which cell to fill in this name. The node Datacell defines which field in the data is to be set, which cell to start, whether the cell is to be merged, how to align, and the spacing between the Word field and the line. The node title font defines the title font attributes, such as size, color, italic, bold, and alignment. Node Datafont defines the attributes of the data font, such as size, color, italic, bold, and alignment. A Rangestyle node actually defines a field to fill in, if your report to fill a lot of fields, there will be many rangestyle nodes to describe. This XML style definition file can write a tool to generate itself, I have a ready-made build tool called Excelstyle, but it is still very simple to use, but can generate the style I need, which I can send to you (I do not know how to provide the download).
2. XML description file and Excel template combination method, for some reports will be very complex, especially those horizontal reports, sometimes extremely complex, then need ready-made Excel templates, with Excel templates we can reference to the template to generate XML style definition files, In the concrete generation of the report, we just know which Excel template is used, which is the XML style file, and then bind our data, then even complex reports can be easily generated.

Specific use, can refer to the following test with the column code (declaration exceloperate,xmloperate is my own write a few common components, if necessary I can email to you, where the data I am in the form of a DataTable):

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 ()/No data exported with Excel templates
{
String Sql = "Select Top 100* from Customers";
Take out 100 piece of data
Excelfactory _factory = new Excelfactory ();
Defines a excelfactory with no parameters
_factory. Createexcelwithouttemplate ("C:\\bbbbbbb.xml", Getdatatable (SQL));
Load-defined style files Bbbbbbb.xml and freshly fetched data getdatatable (SQL)
_factory. Saveexcel ("C:\\bbbbbbbbbbbbbbbb.xls");
Save the exported file

}
[Test]
public void Exportwithtemplate ()//data export with Excel template
{
String Sql = "Select Top 1* from Customers";
Take out a piece of data
Excelfactory _factory = new Excelfactory ("C:\\qstandardcustomer.xls");
Import Template Qstandardcustomer.xls
_factory. Createexcel ("C:\\qstandardcustomer.xml", Getdatatable (SQL));
Load-defined style files Qstandadrcustomer.xml and freshly fetched data getdatatable (SQL)

_factory. Saveexcel ("C:\\qreportstr.xls");
Finally, you can 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];
}
}
}

From the above code is visible, the general report generation is a three-sentence code, even for some of the report's lines, some of the formatting to adjust we do not need to modify the program, as long as the XML description file can be changed, so that we greatly in the production of the report to save time, and can put more time in the business logic of the processing.