Select Excel export solution for GridView

I. Requirement Analysis
First, you can choose to export data based on your needs. All the data has been exported before. This new requirement comes again, but think twice. It doesn't make much sense to export tens of thousands of data records, users can choose to export the data they need. If necessary, there is a solution.
There are more than one solution to any problem, and the same is true for selective export of the GridView. First, the GridView is bound to the table data of the database, and you can select export to query the required data from the data, the data can be obtained directly from the front-end GridView table. The problem arises: how to determine the selected data and how to select the primary key of the selected information (or obtain the entire information, which efficiency is better?

Ii. Solution
ONE

1. the foreground retrieves the primary key information of the selected row and transmits it to the background in JSON format. After parsing in the background, it obtains data from the database and exports it to Excel through DataTable.

(1). Export the selected Operation
Copy codeThe Code is as follows:
<Asp: Button ID = "Export" runat = "server" CssClass = "btn-plain" Text = "Export selected" OnClientClick = "return getnumberExport () "OnClick =" Export_Click "/>

(2) Foreground JS, get the primary key of the information of the selected row, that is, the GridView should have a column that is the primary key, get all the primary keys to save in JSON format, and then upload them to the server
Copy codeThe Code is as follows:
<Script type = "text/javascript">
Function getnumberExport (){
If (ExportCheck ()){
// Obtain the element through the GridView ID
Var gv = document. getElementById ("ctl00_ContentPlaceHolder1_gridView ");
// Obtain the Input html of the GridView
Var mycheck = gv. getElementsByTagName ("input ");
// Define a new array
Var fam = new Array ();
Var hg;
Var id;
// Cyclically check the checkbox tag to obtain the primary key information of each data entry
For (var I = 0; I <mycheck. length; I ++ ){
If (mycheck [I]. type = 'checkbox') // hidden
{
// If the checkbox is selected
If (mycheck [I]. checked = true ){
Var numid = new Object ();
Hg = gv. rows (I + 1). cells (20). innerHTML;
// The substring () method is used to extract characters that are intermediary between two specified subscripts of a string.
// LastIndexOf () reverse string, from the back to the specified character
Hg = hg. substring (hg. lastIndexOf ("=") + 1, hg. lastIndexOf ("\""));
// Obtain the primary key of a piece of data
Id = gv. rows (I + 1). cells (1). innerHTML;
Numid. number = hg;
Numid. id = id;
Fam. push (numid );
}
}
}
// $. AjaxSetup ({
// Async: false // set to synchronous request
//});
// Convert the array to the JSON type
Var nid = JSON. stringify (fam );
// Send data to the server in post Mode
$. Post ("List. aspx", {Action: "action", numid: nid}, function (result ){
});
}
Else {
Return false;
}
}
</Script>

(3) Parse JSON in the background, convert to able, and export to Excel
Copy codeThe Code is as follows:
Protected void Page_Load (object sender, EventArgs e)
{
// Determine whether the server correctly receives data
If (Request. Params ["Action"]! = Null)
{
// Obtain the front-end JSON
String numid = Request. Params ["numid"]. ToString ();
Session ["numid"] = numid;
}
}
/// <Summary>
/// Select EXCEL for export
/// </Summary>
/// <Param name = "sender"> </param>
/// <Param name = "e"> </param>
Protected void Export_Click (object sender, EventArgs e)
{
FamilyPlanningFacade familyPlanningFacade = new FamilyPlanningFacade (); // instantiate the family planning appearance Layer
DataSet dsAllFamily = new DataSet ();
DataSet dsOneFamily = new DataSet ();
DsAllFamily = familyPlanningFacade. GetAllList (); // obtain all family planning information
DsAllFamily. Tables [0]. Rows. Clear ();
// Parse JSON. The Newtonsoft. Json and Newtonsoft. Json. Linq libraries need to be introduced.
JArray numidget = (JArray) JsonConvert. DeserializeObject (Session ["numid"]. ToString ());
// Retrieve the value required in JSON. query the database using this value to obtain a data record and add the data to the new row.
For (int I = 0; I <numidget. Count; I ++)
{
JObject numberget = (JObject) numidget [I];
String num = numberget ["number"]. ToString ();
If (num. Equals (""))
{
// Obtain the key value of the id
String id = numberget ["id"]. ToString ();
// EnBasic. ID = id;
// Query a piece of data by id value (unique)
DsExport = famfacade. SelectExport (id );
// Create a row
DataRow row = newDataTable. NewRow ();
// Add data to the new China Construction Bank
Row. ItemArray = dsExport. Tables [0]. Rows [0]. ItemArray;
// Add this row to the DataTable
NewDataTable. Rows. Add (row );
}
}
DataTable dtfamilyplanning = new DataTable (); // create a data table
Dtfamilyplanning = newDataTable. Tables [0];
// Set table fields
Dtfamilyplanning = dtfamilyplanning. DefaultView. ToTable (false, new string [] {"ID", "name", "sex "});
// Modify the header information. To make the exported Excel header Chinese Characters
Dtfamilyplanning. Columns ["ID"]. ColumnName = "no ";
Dtfamilyplanning. Columns ["name"]. ColumnName = "name ";
Dtfamilyplanning. Columns ["sex"]. ColumnName = "gender ";
// Other information in the... table is not written in detail.
// Create a DataTableToExcel Class Object
DataTableToExcel dtToExcel = new DataTableToExcel ();
// Export data to Excel
DtToExcel. ToExcel (dtfamilyplanning );
}

(4). DataTable is exported as an Excel method. It is a good method. It is used by me on the shoulders of giants.
Copy codeThe Code is as follows:
Public void ToExcel (DataTable dt)
{
DataGrid dgExcel = new DataGrid ();
DgExcel. DataSource = dt;
DgExcel. DataBind ();
HttpContext. Current. Response. Charset = "GB2312 ";
String fileName = HttpUtility. UrlEncode (Guid. NewGuid (). ToString (), System. Text. Encoding. UTF8 );
String str = "attachment; filename =" + fileName + ". xls ";
HttpContext. Current. Response. ContentEncoding = System. Text. Encoding. UTF8;
HttpContext. Current. Response. ContentType = "application/ms-excel ";
HttpContext. Current. Response. AppendHeader ("content-disposition", str );
StringWriter sw = new StringWriter ();
HtmlTextWriter htmTextWriter = new HtmlTextWriter (sw );
DgExcel. RenderControl (htmTextWriter );
HttpContext. current. response. write ("String style = "<style> td {mso-number-format: \" \\@\ ";}</style> "; // prevent deleting the value 0 of All numeric data starting with 0 when exporting an excel file
HttpContext. Current. Response. Write (style );
HttpContext. Current. Response. Write ("HttpContext. Current. Response. Write (sw );
HttpContext. Current. Response. Write ("</body> HttpContext. Current. Response. End ();
}

Finally, let's take a look at the entire implementation process of this method:
1. Obtain the primary key value of each selected row from the foreground,
2. save all the key values to the array and convert the array to JSON format,
3. upload the data to the background through AJAX,
4. the backend receives and parses JSON to obtain the value in JSON,
5. You can query the database to obtain the selected data and add each row of data to the DataTable,
6. Finally, export it to Excel.
Problems with this method: 1. the front-end obtains the primary key value of the row selected by the GridView, and obtains the primary key location through innerHTML. That is to say, if the primary key location in the HTML changes, the primary key retrieval code also needs to change, which has a big impact, this has a great impact on system flexibility.
Let's look at the second method:

TWO

2. Go to the primary key set of the front-end GridView table directly in the background. The obtained result is similar to the previous method.

(1). Export the selected Operation
Copy codeThe Code is as follows:
<Asp: Button ID = "Export" runat = "server" CssClass = "btn-plain" Text = "Export selected" OnClick = "Export_Click"/>

(2) Background button event
Copy codeThe Code is as follows:
Protected void Export_Click (object sender, EventArgs e)
{
DataSet dsAllFamily = new DataSet ();
DataSet dsOneFamily = new DataSet ();
FamilyPlanningFacade familyPlanningFacade = new FamilyPlanningFacade (); // instantiate the family planning appearance
DsAllFamily = familyPlanningFacade. GetAllList (); // obtain all family planning information
DsAllFamily. Tables [0]. Rows. Clear ();
// Loop through each row of the gridView to find the row selected by the CheckBox
Foreach (GridViewRow msgRow in this. gridView. Rows)
{
// Obtain the CheckBox to be traversed by ID
CheckBox chk = (CheckBox) msgRow. FindControl ("DeleteThis ");
// Determine whether the CheckBox is selected
If (chk. Checked)
{
// Obtain the key value of the id
String id = msgRow. Cells [1]. Text. ToString ();
DsOneFamily = familyPlanningFacade. GetList (id );
// Create a row
DataRow row = dsAllFaamily. Tables [0]. NewRow ();
// Add data to the new China Construction Bank
Row. ItemArray = dsOneFamily. Tables [0]. Rows [0]. ItemArray;
// Add this row to the DataTable
DsAllFamily. Tables [0]. Rows. Add (row );
}
}
If (dsAllFamily. Tables [0]. Rows. Count = 0)
{
PersonalFiles. Web. MessageShow. MessageBox. Show (this, "select the information to export! ");
Return;
}

DataTable dtfamilyplanning = new DataTable (); // create a data table
Dtfamilyplanning = dsAllFamily. Tables [0];
// Set table fields
Dtfamilyplanning = dtfamilyplanning. DefaultView. ToTable (false, new string [] {"ID", "name", "sex "});
// Modify the header information. To make the exported Excel header Chinese Characters
Dtfamilyplanning. Columns ["ID"]. ColumnName = "no ";
Dtfamilyplanning. Columns ["name"]. ColumnName = "name ";
Dtfamilyplanning. Columns ["sex"]. ColumnName = "gender ";
// Other information in the... table is not written in detail.
// Create a DataTableToExcel Class Object
DataTableToExcel dtToExcel = new DataTableToExcel ();
// Export data to Excel
DtToExcel. ToExcel (dtfamilyplanning );
}

The implementation process of this method is very simple. The primary key data displayed by the front-end GridView is obtained directly in the background, which is much easier than the JSON data transmitted by the front-end.
Three

3. in addition to the two methods, there is also a faster way to obtain the displayed data directly from the GridView without re-querying the database. The bad thing is that if the data is operated by multiple people, in addition, if the page is updated frequently, obtaining data directly from the interface GridView may not be the latest data.
Copy codeThe Code is as follows:
Protected void Export_Click (object sender, EventArgs e)
{
DataTable dsExport = new DataTable ();
// Obtain each column of the gridView cyclically and obtain the header
For (int I = 0; I <this. gridView. Columns. Count-1; I ++)
{
// Add the header information to the DataTable Header
DsExport. Columns. Add (this. gridView. Columns [I]. HeaderText );
}
// Loop through each row of the gridView to find the row selected by the CheckBox
Foreach (GridViewRow msgRow in this. gridView. Rows)
{
// Obtain the CheckBox to be traversed by ID
CheckBox chk = (CheckBox) msgRow. FindControl ("DeleteThis ");
// Determine whether the CheckBox is selected
If (chk. Checked ){
// Define the DataTable new row
System. Data. DataRow dr = dsExport. NewRow ();
For (int I = 0; I <msgRow. Cells. Count-1; I ++)
{
// Obtain the Cells data
Dr [I] = msgRow. Cells [I]. Text. ToString ();
}
// Add a new row to the DataTable
DsExport. Rows. Add (dr );
}
Else
{
PersonalFiles. Web. MessageShow. MessageBox. Show (this, "select the information to export! ");
Return;
}
}
// Remove unnecessary columns from the DataTable
DsExport. Columns. Remove ("select ");
DsExport. Columns. Remove ("details ");
DsExport. Columns. Remove ("edit ");
PersonalFiles. BLL. DataTableToExcel dtToExcel = new PersonalFiles. BLL. DataTableToExcel ();
// Export data to Excel
DtToExcel. ToExcel (dsExport );
}

The above three methods are clearly written, and each has its own advantages. Selecting Based on the requirements of the actual project, the learning process must be implemented by multiple approaches. I have previously written a blog on JSON, it's never used. This time, I learned how to use Heiheihei, and learned it over and over again.