I. Demand analysis
First, according to user needs, can be selective export, has done all the export, this new demand again, but carefully think about it, all exported tens of thousands of data, the significance is not large, and the user needs to selectively export the data is required. There is a need for solutions.
There are more than one solution to any problem, the selective export of the GridView is the same, first the GridView is bound to the database table data, select the export, we can also query the data needs, can be directly from the front of the GridView table to obtain data, the problem comes, How to determine the selected, how to select the main key of the information (or get the whole message) two direction, in the end which is more efficient which is more excellent?
Ii. Solutions
One
1. The foreground gets the primary key information of the selected row, through JSON format, transmits to the backstage, after the background resolves, then obtains the data from the database, through the DataTable exports to the Excel
⑴. Export the selected action
Copy Code code as follows:
<asp:button id= "Export" runat= "cssclass=" Btn-lit "text=" Export selected "onclientclick=" Return Getnumberexport () " onclick= "Export_click"/>
⑵. Foreground JS, get the primary key of that information for the selected row, that is, the GridView has a column that is the primary key, gets all the primary keys saved in JSON format, and uploads to the server
Copy Code code as follows:
<script type= "Text/javascript" >
function Getnumberexport () {
if (Exportcheck ()) {
Getting elements from the GridView ID
var gv = document.getElementById ("Ctl00_contentplaceholder1_gridview");
Gets the input HTML for the GridView
var MyCheck = gv.getelementsbytagname ("input");
Define a new array
var fam = new Array ();
var Hg;
var id;
Loop check CheckBox tab to get primary key information for each piece of data
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. InnerHTML;
The substring () method extracts characters from a string mediation between two specified subscripts.
LastIndexOf () to fetch the string, from the back to the specified character
HG = Hg.substring (Hg.lastindexof ("=") + 1, Hg.lastindexof (""));
Gets 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 sync request
//});
Convert an array to a JSON type
var nid = Json.stringify (FAM);
Post method, transferring data to the server
$.post ("List.aspx", {action: "Action", Numid:nid}, function (Result) {
});
}
else {
return false;
}
}
</script>
⑶. Background parse JSON, convert to DataTable, export to Excel
Copy Code code as follows:
protected void Page_Load (object sender, EventArgs e)
{
Determine if the server is receiving data correctly
if (request.params["Action"]!=null)
{
Get JSON for the foreground
String numid = request.params["Numid"]. ToString ();
session["Numid"] = Numid;
}
}
<summary>
Selectively export Excel
</summary>
<param name= "Sender" ></param>
<param name= "E" ></param>
protected void Export_click (object sender, EventArgs e)
{
Familyplanningfacade Familyplanningfacade = new Familyplanningfacade (); Instantiate the appearance layer of IPPF
DataSet dsallfamily = new DataSet ();
DataSet dsonefamily = new DataSet ();
dsallfamily = Familyplanningfacade.getalllist (); Get all family planning information
Dsallfamily.tables[0]. Rows.clear ();
parsing Json; need to introduce Newtonsoft.json and NEWTONSOFT.JSON.LINQ libraries
Jarray Numidget = (jarray) jsonconvert.deserializeobject (session["Numid"]. ToString ());
Iterate through the values needed in JSON, retrieve a data by querying the database with this value, and add data to a new row
for (int i = 0; i < Numidget. Count; i++)
{
Jobject numberget = (jobject) numidget[i];
String num = numberget["number"]. ToString ();
if (Num. Equals (""))
{
Gets the key value of the ID
String id = numberget["id"]. ToString ();
Enbasic.id = ID;
Query to a single data by the value of ID (unique)
Dsexport = Famfacade. Selectexport (ID);
New Construction Bank
DataRow row = Newdatatable.newrow ();
Add data to a new row
Row. ItemArray = Dsexport.tables[0]. Rows[0]. ItemArray;
Add this row to the DataTable
NEWDATATABLE.ROWS.ADD (row);
}
}
DataTable dtfamilyplanning = new DataTable (); Create a family planning information datasheet
dtfamilyplanning = Newdatatable.tables[0];
Set up a table field
dtfamilyplanning = dtfamilyplanning. Defaultview.totable (False, new string[] {"ID", "name", "Sex"});
Modify the header information to make the exported Excel header a Chinese character
Dtfamilyplanning. columns["ID"]. ColumnName = "number";
Dtfamilyplanning. columns["Name"]. ColumnName = "name";
Dtfamilyplanning. columns["Sex"]. ColumnName = "gender";
//.... Other information in the table is no longer read
New object for Datatabletoexcel class
Datatabletoexcel dttoexcel = new Datatabletoexcel ();
Export Data to Excel
Dttoexcel.toexcel (dtfamilyplanning);
}
⑷. DataTable exported to Excel method, very good way, standing on the shoulders of giants for me to use.
Copy Code code 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 0 of all digital data that starts with 0 from exporting Excel
HttpContext.Current.Response.Write (style);
HttpContext.Current.Response.Write ("HttpContext.Current.Response.Write (SW);
HttpContext.Current.Response.Write ("</body>HttpContext.Current.Response.End ();
}
Finally, look at the entire implementation of this method:
1 to get the primary key value of each selected row through the foreground,
2 to save all the key values to the array, the array into JSON format,
3 is uploaded to the background via Ajax,
4 background receive, parse JSON, get the value in JSON,
5 You can query the database for each selected piece of data, add each row of data to the DataTable,
6 finally exported to Excel.
The problem with this method: First, the foreground gets the main key value of the GridView selected row, the location of the primary key by innerHTML, that is, if the primary key position in the HTML changes, get the primary key code also change, this effect is relatively large, far-reaching, so the flexibility of the system is very influential.
Let's look at the second method:
Two
2. After the background directly to the front of the GridView table's primary key collection, get the implementation and the previous method similar.
⑴. Export the selected action
Copy Code code as follows:
<asp:button id= "Export" runat= "server" cssclass= "Btn-lit" text= "Export selected" onclick= "Export_click"
⑵. Background button Event
Copy Code code 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 (); Get all family planning information
Dsallfamily.tables[0]. Rows.clear ();
Loop GridView each line to find the row where the checkbox is selected
foreach (GridViewRow msgrow in This.gridView.Rows)
{
Get the checkbox you need to traverse by ID
CheckBox chk = (checkbox) Msgrow.findcontrol ("DeleteThis");
Determine if a checkbox is selected
if (CHK). Checked)
{
Gets the key value of the ID
String id = msgrow.cells[1]. Text.tostring ();
dsonefamily = familyplanningfacade.getlist (ID);
New Construction Bank
DataRow row = Dsallfaamily.tables[0]. NewRow ();
Add data to a new row
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, "Please select the information you want to export!") ");
Return
}
DataTable dtfamilyplanning = new DataTable (); Create a family planning information datasheet
dtfamilyplanning = Dsallfamily.tables[0];
Set up a table field
dtfamilyplanning = dtfamilyplanning. Defaultview.totable (False, new string[] {"ID", "name", "Sex"});
Modify the header information to make the exported Excel header a Chinese character
Dtfamilyplanning. columns["ID"]. ColumnName = "number";
Dtfamilyplanning. columns["Name"]. ColumnName = "name";
Dtfamilyplanning. columns["Sex"]. ColumnName = "gender";
//.... Other information in the table is no longer read
New object for Datatabletoexcel class
Datatabletoexcel dttoexcel = new Datatabletoexcel ();
Export Data to Excel
Dttoexcel.toexcel (dtfamilyplanning);
}
The implementation of this method is very simple, directly in the background to obtain the foreground of the GridView display of the primary key data, compared to the front to pass the JSON data is relatively simple a lot.
Three
3. In addition to these two, there is a faster method, do not need to requery the database, but directly from the GridView directly to the display of data, the bad place is if the data more than people to operate, and frequent updates, then directly from the interface gridview to obtain data may not be the latest data.
Copy Code code as follows:
protected void Export_click (object sender, EventArgs e)
{
DataTable Dsexport = new DataTable ();
Loop to get each column of the GridView, get the table header
for (int i=0;i<this.gridview.columns.count-1;i++)
{
Adding table header information to a DataTable header
DSEXPORT.COLUMNS.ADD (This.gridview.columns[i]. HeaderText);
}
Loop GridView each line to find the row where the checkbox is selected
foreach (GridViewRow msgrow in This.gridView.Rows)
{
Get the checkbox you need to traverse by ID
CheckBox chk = (checkbox) Msgrow.findcontrol ("DeleteThis");
Determine if a checkbox is selected
if (CHK). Checked) {
Defining a new row for a DataTable
System.Data.DataRow dr = Dsexport.newrow ();
for (int i = 0; i < msgrow.cells.count-1; i++)
{
Get Cells Data
Dr[i] =msgrow.cells[i]. Text.tostring ();
}
Add new rows to DataTable
DSEXPORT.ROWS.ADD (DR);
}
Else
{
PersonalFiles.Web.MessageShow.MessageBox.Show (This, "Please select the information you want to export!") ");
Return
}
}
Removing unwanted columns from the DataTable
DsExport.Columns.Remove ("Selection");
DsExport.Columns.Remove ("detailed");
DsExport.Columns.Remove ("edit");
PersonalFiles.BLL.DataTableToExcel dttoexcel = new PersonalFiles.BLL.DataTableToExcel ();
Export Data to Excel
Dttoexcel.toexcel (Dsexport);
}
Three ways to write it clearly, each has the advantage, according to the actual project request to choose, the learning process must be more contact with the realization of various methods, previously written about JSON blog, is not used, this time an opportunity to learn a usage, feel good, repeated learning, the accumulation of the process, Heiheihei.
