Remove auto filter and color while exporting GridView to excel in ASP.Net

Last Reply on Sep 13, 2016 09:14 AM By manickam

Posted on Sep 13, 2016 08:39 AM

 

    private void ExporttoExcel(DataTable dt1)
    {
        DataTable dt = new DataTable("ProductData");      

        foreach (DataColumn column in dt1.Columns)
        {
            dt.Columns.Add(column.ColumnName);          
        }

        foreach (DataRow row in dt1.Rows)
        {
            dt.Rows.Add();
            for (int i = 0; i < dt1.Columns.Count; i++)
            {
                dt.Rows[dt.Rows.Count - 1][i] = row[i].ToString().Replace("&nbsp;", "");
            }
        }

        Guid messageId = System.Guid.NewGuid();
        string data = null;
        using (XLWorkbook wb = new XLWorkbook())
        {
          
             wb.Worksheets.Add(dt);
           
             //wb.Style.Alignment.Horizontal = XLAlignmentHorizontalValues.Center;
             //wb.Style.Font.Bold = true;
             //wb.PageOptions.BlackAndWhite = true;  
        
                                                                          
            Response.Clear();
            Response.Buffer = true;
            Response.Charset = "";            
            Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
            Response.AddHeader("content-disposition", "attachment;filename=ProductData_"+messageId+".xlsx");
            HttpContext.Current.Response.ContentEncoding = Encoding.GetEncoding("Windows-1252");
            using (MemoryStream MyMemoryStream = new MemoryStream())
            {
                wb.SaveAs(MyMemoryStream);
                MyMemoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }
        }
    }  

 

Posted on Sep 13, 2016 09:14 AM

 

var sheet1 = wb.Worksheets.Add(dt);
sheet1.Table("Table1").ShowAutoFilter = false;
sheet1.Table("Table1").Theme = XLTableTheme.None;

Its solved, Thanks for your information.