Using Aspose.cell controls to generate Excel difficult reports (i)

Time flies, life, work, amateur research is always busy, blink quickly to the end of the month, the blog article task is not finished, pause to recall, summed up some experience and good things out, we share. This article mainly describes the report generation, based on the Aspose.cell control of the report generation. When it comes to reporting, it is estimated that everyone has a sense of comprehension and personal understanding, generally speaking, the general report generation, basically is based on the following ways: One is based on Microsoft Excel built-in engine to implement, one is to construct HTML format excle report, one is based on the control of the way to handle, There are many ways to control the controls, and personally think that the more famous are Aspose.cell (toll cracking) and npoi (open source).

The presentation of the report can be broadly divided into two types:

One is the universal two-dimensional table export of Excel format, this way by encapsulating an operation class, passing a DataTable parameter, the data can be exported. This report feature is easy to operate, general, can cope with the commonly used data reports, as shown below;

Since this kind of report is usually displayed in a data table, the usual practice is to make this thing a control, one can solve the problem of paging, one can solve the export, printing problems, such as my essay article "WinForm Interface Development" "pagination control" described in the solution.

Of course, you can also encapsulate the import and export of Excel as a common helper to invoke, as the import and export handlers for my encapsulated Aspose.cell are as follows:

Code

This encapsulates the Aspose.cell operation, each time the Excel file is generated or imported Excel content, it is very convenient, only need to call the following way to complete:

private void Button1_Click (object sender, EventArgs e)
{
DataTable dt = createtable ("Test 1, Test 2,test1,test2", "testtable");
for (int i = 0; i <; i++)
{
DataRow dr = dt. NewRow ();
for (int j = 0; j < dt. Columns.count; J + +)
{
DR[J] = i.tostring ();
}
Dt. Rows.Add (DR);
}

String outerror = "";
String fileName = @ "C:\test.xls";
Asposeexceltools.datatabletoexcel2 (DT, fileName, out outerror);


if (!string. IsNullOrEmpty (Outerror))
{
MessageBox.Show (Outerror);
}
Else
{
Process.Start (FileName);
}
}


Public DataTable createtable (string namestring, String tableName)
{
string[] NameArray = Namestring.split (new char[] {', ', '; '});
list<string> namelist = new list<string> ();
foreach (string item in NameArray)
{
if (!string. IsNullOrEmpty (item))
{
Namelist.add (item);
}
}

Return CreateTable (NameList, tableName);
}

Another is the Excel file as a template, and then fill in the necessary content, to form a more comprehensive, more complex report, this report is generally more professional, more good-looking, in some special occasions, you must use these fixed-format reports, as follows:

Or a report format like this

These reports, basically the use of variables, functions and other concepts to handle the data, such as the above out of the list, the cost center, department, warehouse number, etc., these through the variable binding should be able to, and the inside of the list, it can be implemented through the collection binding, The Aspose.cell control is very powerful and supports these operations, and the following steps describe the implementation code that the control makes for such a report.

The Aspose.cell control supports binding operations for a variety of parameter variables, such as supporting datasets, Datatable, IList Collections, entity class collections, class objects, and so on.

DataSet ds = Loaddataset ();//Using a DataSet object
list<customers> entity = GetCustomers ();//working with entities class objects
DataTable dt = getcustomerstable ();//Using DataTable Object

Create a Workbookdesigner object
Workbookdesigner designer = new Workbookdesigner ();

Make a report template
String path = System.IO.Path.Combine (Application.startuppath, "Smartmarkerdesigner.xls");
Designer. Open (path);

Set DataSet Object
Designer. Setdatasource (DS);

Set entity class object
Designer. Setdatasource ("Customers", entity);

Sets the DataTable object Designer. Setdatasource (DT);
Designer. Setdatasource (ds. tables["Order Details"]);

Set Variable Object
Designer. Setdatasource ("Variable", "single Variable");
Set a collection variable
Designer. Setdatasource ("Multivariable", new string[] {"Variable 1", "Variable 2", "Variable 3"});
Set a collection variable
Designer. Setdatasource ("MultiVariable2", new string[] {"Skip 1", "Skip 2", "Skip 3"});

Generate report content based on data source processing
Designer. Process ();

Save Excel File
String filetosave = System.IO.Path.Combine (Application.startuppath, "Smartmarker.xls");
if (file.exists (Filetosave))
{
File.delete (Filetosave);
}

Designer. Save (Filetosave, fileformattype.excel2003);
Open Excel File Process.Start (filetosave);

The above code explains the various parameter variables supported by the control, we first look at the report template, and then look at the report generated content, comparison is more intuitive.

The report 1 template looks like this (where objects referenced by a collection are referenced by &=, the object's properties or column names are referenced by means of &=customer.city, which is very intuitive and convenient:

Report 1 produces the effect as follows (customers can make a DataTable object, or you can list<customer> a collection of entity objects.)

The template for report 2 looks like this, the object can also be referenced by &=[order Detail], and the template supports some parameters, where {R} is a variable of the row, and the actual report may be a format c4*d4, where two &= represent a dynamic formula reference, It differs from ordinary variables and characters, such as &=&=c{r}*d{r}, which summarizes functions &=&=sum (C{r}:D {r}) and so on.

The build effect of report 2 is as follows

The template for report 3 looks like this, which uses object variables, object variable references such as &= $Variable format, a $ symbol more than a collection object or DataTable object, where the collection supports some traversal parameters, such as skip,horiontal and so on.

The build effect of report 3 is as follows

In summary, there are several ways to bind a template report in a variable way:

&=datasource.fieldname

&=[data Source]. [Field name]&= $VariableName &= $VariableArray &==dynamicformula&=&=repeatdynamicformula

In addition, the template report supports some parameters for secondary use, as follows:

NoaddAdapts to data without adding extra rows (not sure if this is the way to say) skip:nEach row records the number of skipped, N=1 represents a traversal n=2 ascending:n/Descending:nSort the data for. If n=1, then the column is the first keyword to sort, example: &=table1.field3 (ascending:1) HorizontalThe default is the upper and lower vertical output, if set to horizontal, then the output is horizontal, see the example above

Dynamic Formula variables

In addition, dynamic formulas such as &=&=c{r}*d{r} are seen in the above template for application to columns, and dynamic formulas support the following reference variables:

{R}-Current row variable

{2}, {-1} -offset position of the current row

If you want to summarize some rows and columns, you can use dynamic variables such as &=&=sum (C{r}:f{r}) to implement them.

What if the same column, not the same field to summarize it? , that would be more convenient, not so complicated, you just use normal summary functions such as =sum (C3:C4) such as the format, you can, if the action state increases, Excel will automatically adjust the Sum function within the column reference, perhaps the final output will change to =sum (C3:C11).

Summarize format variables

You can use Group:normal/merge/repeat to control the output of formats such as summary merges, such as examples of using both:

&=customers.customerid (Group:merge)

&=employees.employeeid (group:normal,skip:1)

The report comes out as follows:

Subtotaln function

representing 1=average, 2=count,3=counta, 4=max, 5=min, respectively,... 9=sum, wait .

The function is a function used to perform a series of summary calculations, n from 1~11Subtotaln:ref, where REF represents the specified column of the rollup

For example, &=products.units (Subtotal9:Products.ProductID) represents data summary statistics based on Units columns and is counted to ProductID.

For example, &=TABLE1.COLUMND (SUBTOTAL9:TABLE1.COLUMNA&TABLE1.COLUMNB), which represents summary statistics based on columnd columns, Statistics to Columna and COLUMNB on the grouping conditions.

Due to the length of the reason, introduced here, the next chapter continues to explore the content of the report based on the template, including the use of objects dynamically create rows and columns and formulas, using style and other aspects, combined with the actual complex report examples, based on the Aspose.cell report content for further practical analysis and discussion.

At the reader's request, put an example of the operation: Http://files.cnblogs.com/wuhuacong/TestAposeCell.rar

Using Aspose.cell controls to generate Excel difficult reports (i)