Practice-General page list export Excel controls

First, let's take a picture first to understand what we want to talk about today:

 

The first image:

 

The second figure:

 

Note:

In many systems, or the background of some websites, including the background of the autumn garden, there will be some similar tool columns, such as the" Export"Button, because it is distributed in a large number of system lists,

Therefore, how to implement this function in the simplest and fastest way to save development time is what we will introduce below today.

 

Then let's take a look and click"Export"Button export function:

 

Open the exported Excel file:

Note:

From the above four pictures, the problem to be explained is:

The "Export" function appears repeatedly on N interfaces. You can export the current page list on any interface by writing the code for the "Export" function only once.

 

The following describes how to implement the function:

 

I. Implementation ideas

 

You can export a list in either of the following ways:

 

1: export data from the client:

In an ExtJs-based system developed by my colleagues, the reports are exported on the client,

Of course, if this method is used, he has a prerequisite for the use of his system,

That is, all information is displayed for each query without paging. Therefore, you do not need to submit the information to the server for another query. You can export the data directly from the client.

Of course, the disadvantage is that the data displayed by page can only be exported to the current page, so it is only applicable to scenarios where all data is displayed at a time.

 

2: export data from the server:

In many cases, the list is always paged. Therefore, you need to submit the list to the server to query all the data and then output the list. If only one page is processed, generally, you can write less code,

However, for the functions to be implemented today, you only need to drag the controls in to automatically implement the export function, without writing any code on the page. Therefore, you need some standard conventions to work with them.

 

What are the specific implementation methods?

In fact, the idea is very simple. You only need to specify the method name for binding the list and the default ID of the list control.

To put it simply,When you click the "Export" button, you can rebind it through reflection, and then obtain the internal html generated by rebinding the Control ID, and export it in Excel.

 

II. Implementation steps

 

The specific implementation steps are as follows:

1: Create a new user control and click "Export" to pull it.

2: click the "Export" button in the event: Find the agreed list control, such as "rptList". Find the control and then reflect the method of calling the Convention, such as "BindList ", after the call, the rptList will be filled with content.

3: Read and export the internal html of the rptList control.

 

 

Iii. Complete sample code

 

1. Create a sample website project, for example, ExcelExportDemo.

 

2: create a user control in: Export. ascx. In this example, only one "Export" button is placed.

 

The html is as follows:

<% @ Control Language = "C #" AutoEventWireup = "true" CodeFile = "Export. ascx. cs" Inherits = "Export" %>
<Asp: Button ID = "btnExportToXLS" runat = "server" Text = "Export" OnClick = "btnExportToXLS_Click"/>

 

The background code is as follows:

Protected void btnExportToXLS_Click (object sender, EventArgs e)
{
Control ct = this. Page. FindControl ("rptList ");
If (ct! = Null)
{
MethodInfo mi = this. Page. GetType (). GetMethod ("BindList", BindingFlags. Instance | BindingFlags. NonPublic | BindingFlags. Public );
If (mi! = Null)
{
Mi. Invoke (this. Page, null );
}
Toolkit. ExcelHelper. ExportToXLS (ct );
}
}

 

The code is concise and has two conventions: "rptList" and "BindList"

 

Next is the code for splitting and exporting the Excel file:

Public class ExcelHelper
{
Public static void ExportToXLS (params Control [] ctList)
{
HttpContext. Current. Response. Clear ();
HttpContext. Current. Response. ContentEncoding = System. Text. Encoding. GetEncoding ("gb2312 ");
HttpContext. Current. Response. Write ("<meta http-equiv = Content-Type content = text/html; charset = gb2312> ");
HttpContext. Current. Response. AppendHeader ("content-disposition", "attachment?filename=report.xls ");
HttpContext. Current. Response. ContentType = "application/vnd. ms-excel ";
StringBuilder sb = new StringBuilder ();
Sb. append ("System. IO. StringWriter sw = new System. IO. StringWriter ();
HtmlTextWriter htw = new HtmlTextWriter (sw); ctList [0]. RenderControl (htw );
Sb. Append (sw. ToString ());
Sw. Close ();
HttpContext. Current. Response. Write (sb. ToString ());
HttpContext. Current. Response. End ();
}
}

 

Pay attention to the encoding to prevent garbled characters.

OK, the above Code has completed a general export list button. Next we will continue to improve this example, add a list display and export the list.

 

3: Take the previous sectionList of infinite drop-down trees with strip lines-complete exampleTo display data.

 

For the convenience of examples, the Access database is still used, or the last Product table, and several rows of data are added for it,

 

 

Then, the project adds a reference to the CYQ. Data framework to display the database operation list:

The enumeration is not generated because it is only an example of a list application.

Shows the referenced project structure:

 

 

4: On the Default. aspx page, add a GridView control and pull the exported user control in:

The html is as follows:

<Body>
<Form id = "form1" runat = "server">
<Div>
<Asp: GridView ID = "rptList" runat = "server"> </asp: GridView>
<Br/>
<Uc1: Export ID = "Export1" runat = "server"/>
</Div>
</Form>
</Body>

 

Code bound to the background implementation list:

Protected void Page_Load (object sender, EventArgs e)
{
If (! IsPostBack)
{
BindList ();
}
}
Protected void BindList ()
{
Using (MAction action = new MAction ("Product "))
{
Action. Select (). Bind (rptList );
}
}
Public override void VerifyRenderingInServerForm (Control control Control)
{
// Do not trigger the "control must be placed in the form tag with runat = server" Verification
// --------- This can be processed in PageBase
}

 

Now, the entire example is over. Let's look at the results, for example:

 

 

Click Export to export the Excel file:

 

Open the Excel file, which is the data in the current list.

 

Iv. Summary

In the preceding example, the page does not export the background Code related to the button. Instead, you only need to write the ID of the list control as rptList according to the Conventions, and BindList is the method used to bind the data.

This allows you to export data.

 

V. Extensions and other instructions

 

1: You may need to export more than one list on a page. It may be multiple lists. This extension is not difficult:

Idea: you only need to bind N list controls to BindList, set the name of the agreed control to multiple, and add a loop.

 

2: There is no paging in the current example. How can this problem be solved? In fact, it is not difficult:

Idea: you only need to locate the index of the page by checking whether the "Export" event is in the paging control.

For example, the following common code with paging is used:

Protected void BindList ()
{
Using (MAction action = new MAction ("Product "))
{
Int count;
Action. Select (Pager1.PageIndex, Pager1.PageSize, string. Empty, out count). Bind (rptList );
}
}

 

There is no code change in the page. You only need to locate PageIndex and PageSize in the page control pager1.

For example, when obtaining the PageIndex, determine whether to "Export" the event, and then return the index to 1 or 0. For details, refer to the application framework to return the value.

Similarly, when obtaining PageSize, determine whether to "Export" the event, and then return the index to 0 or infinity. For details, refer to the returned value of the application framework.

 

 

 

Finally, download the example: Click to enter the download page.