About data filtering after Excel export download

2016.5.11

Two ways:

Foreground filter

1. generate file stream download

① Backstage Accept Form form, stitching SQL

         Public voidDown (Studenti I,intStatus =0) {List<string> ls =Newlist<string>(); List<SqlParameter> LP =NewList<sqlparameter>(); if(I.recommname! =NULL) {ls. ADD ("[email protected]"); Lp. ADD (NewSqlParameter ("@Recomm", I.recomm)); }            if(I.datetime! =0) {ls. ADD ("[email protected]"); Lp. ADD (NewSqlParameter ("@DateTime", I.datetime)); }            ///range of ValuesDownbll.downstudeni (LS, LP. ToArray (),"Trainee Management"); }

View Code

② using SQL to query out a DataTable

         Public Static voidDownstudeni (list<string> name, sqlparameter[] parameter,stringTitle) {            stringsql ="SELECT * from Studenti where"; foreach(varIinchname) SQL+ = i +" and"; SQL+="0=0"; DataTable DT=SD.            Search (sql, parameter);        Excelhelper.createexceltodown (DT, Title); }

View Code

③ uses Nopi to write files

        /// <summary>        ///table into Excel and download/// </summary>        /// <param name= "DT" >Table</param>        /// <param name= "title" >file name</param>         Public Static voidCreateexceltodown (DataTable DT,stringtitle) {            using(Workbook book =NewHssfworkbook ()) {Sheet Sheet= Book. Createsheet ("Sheet1"); Row HeaderRow= Sheet. CreateRow (0); CellStyle style=Book .                Createcellstyle (); Style. Alignment=HorizontalAlignment.Center; //1. Conversion table Header                 for(inti =0; i < dt. Columns.count; i++) {cell cell=HeaderRow.                    Createcell (i); Cell. CellStyle=style; Cell. Setcellvalue (dt. Columns[i].                ColumnName); }                //2. Fill in the data                intRowlen =dt.                Rows.Count; intCollen =dt.                Columns.count;  for(inti =0; i < Rowlen; i++) {DataRow Dr=dt.                    Rows[i]; Row R= Sheet. CreateRow ((i+1));  for(intj =0; J < Collen; J + +) {R.createcell (j). Setcellvalue (Dr[j].                    ToString ()); }                }                //3. Download                using(MemoryStream ms =NewMemoryStream ()) {Book.                    Write (MS); HttpContext.Current.Response.AddHeader ("content-disposition",string. Format ("attachment; Filename={0}.xls", Httputility.urlencode (title +"_"+ DateTime.Now.ToString ("YYYY-MM-DD") , System.Text.Encoding.UTF8)); HttpContext.Current.Response.BinaryWrite (Ms.                    ToArray ());                HttpContext.Current.Response.End (); }            }        }

View Code

This method is good to find 1-side SQL can be downloaded, but excel in the Chinese column name, I think it will be in SQL as more trouble

2. Write HTML directly

① Find all the data you want

② use Linq.where () in all data to isolate the data to be used

③ then passes the model into the class, generating the HTML code

④ then write the website for download

About data filtering after Excel export download