export to excel

rakesh chaudhari

12y4.2k.NET

How to Export GridView to Excel (xlsx, Excel 2007,10 Format) ?

my code is as
public void ExportDataExcel(DataTable dt)
{
Response.Clear();
Response.ClearContent();
Response.Charset = "";
Response.ContentEncoding = System.Text.Encoding.Default;

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
Response.ContentEncoding = System.Text.Encoding.Default;
Response.AddHeader("Content-Disposition", "attachment;filename=" + "OpenComplaints" + ".xlsx");

using (System.IO.StringWriter sw = new System.IO.StringWriter())
{
using (HtmlTextWriter htw = new HtmlTextWriter(sw))
{

htw.RenderBeginTag(HtmlTextWriterTag.Link);
htw.RenderEndTag();

// instantiate a datagrid
DataGrid dg = new DataGrid();
dg.GridLines = GridLines.Both;
dg.CaptionAlign = TableCaptionAlign.Left;
dg.HeaderStyle.Font.Bold = true;
dg.HeaderStyle.ForeColor = System.Drawing.Color.DarkBlue;
dg.HeaderStyle.BackColor = System.Drawing.Color.SkyBlue;
dg.ItemStyle.BackColor = System.Drawing.Color.LightGray;
dg.ItemStyle.Width = 100;
dg.ItemStyle.Wrap = true;
dg.DataSource = dt;
dg.DataBind();

dg.RenderControl(htw);
Response.Write(sw.ToString());
Response.Flush();
Response.End();
}
}

}
Please help me when i call this function then it give only blank excel sheet without any column or row or header so plz help and thanks in advance

6 Replies

Know the answer? Post it — somebody with the same question will find it here.

Neboja12y ago

Hi, try the following code:

public void ExportDataExcel(DataTable dt)
{
    using (System.IO.StringWriter sw = new System.IO.StringWriter())
    {
        using (HtmlTextWriter htw = new HtmlTextWriter(sw))
        {
            htw.RenderBeginTag(HtmlTextWriterTag.Link);
            htw.RenderEndTag();
 
            // instantiate a datagrid
            DataGrid dg = new DataGrid();
            dg.GridLines = GridLines.Both;
            dg.CaptionAlign = TableCaptionAlign.Left;
            dg.HeaderStyle.Font.Bold = true;
            dg.HeaderStyle.ForeColor = System.Drawing.Color.DarkBlue;
            dg.HeaderStyle.BackColor = System.Drawing.Color.SkyBlue;
            dg.ItemStyle.BackColor = System.Drawing.Color.LightGray;
            dg.ItemStyle.Width = 100;
            dg.ItemStyle.Wrap = true;
            dg.DataSource = dt;
            dg.DataBind();
 
            dg.RenderControl(htw);
 
            ExcelFile excel;
            var option = LoadOptions.HtmlDefault;
            using (var htmlStream = new System.IO.MemoryStream(option.Encoding.GetBytes(sw.ToString())))
                excel = ExcelFile.Load(htmlStream, option);
 
            excel.Save(Response, "OpenComplaints.xlsx");
        }
    }
}

The Excel File object is a part of this .NET component, also note that you can easily import a DataTable object directly into an Excel file in C# (see InsertDataTable method) and thus avoid using a DataGrid control. After the insertion you can style and format the content of that worksheet.

Nimit Joshi12y ago
Hey you can refer to the following article:

http://www.c-sharpcorner.com/UploadFile/0c1bb2/export-gridview-to-excel/
Rahul Prasad12y ago
hello,

Does this article work? Hope I'm not misunderstanding.
Knowledgebase/Spire.DataExport/Program-Guide/Export-Data-from-Listview-to-Excel-with-C-/VB.NET

all the best
Pawan Kumar12y ago
protected void bttnExportXL_Click(object sender, EventArgs e)
{
gridViewMaster.AllowPaging = false;
gridViewMaster.AllowSorting = false;
Response.Clear();

DateTime.Now.ToShortDateString() +".xlsx");
Response.AddHeader("content-disposition", "attachment;filename=Report.xlsx");
Response.Charset = "";

Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";

System.IO.StringWriter stringWrite = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);

foreach (GridViewRow r in gridViewMaster.Rows)
{
if (r.RowType == DataControlRowType.DataRow)
{
for (int columnIndex = 0; columnIndex < r.Cells.Count; columnIndex++)
{
r.Cells[columnIndex].Attributes.Add("class", "text");
}
}
}

gridViewMaster.RenderControl(htmlWrite);

string style = @" ";
Response.Write(style);

Response.Write(stringWrite.ToString());
Response.End();

}
rakesh chaudhari12y ago
it not works friend i want xlsx format not in xls also thanks for your answer
Abhay Shanker12y ago
Do coding as below:--

private void ExportGridToExcel()
   {
       Response.Clear();
       Response.Buffer = true;
       Response.ClearContent();
       Response.ClearHeaders();
       Response.Charset = "";
       string FileName ="Vithal"+DateTime.Now+".xls";
       StringWriter strwritter = new StringWriter();
       HtmlTextWriter htmltextwrtter = new HtmlTextWriter(strwritter);
       Response.Cache.SetCacheability(HttpCacheability.NoCache);
       Response.ContentType ="application/vnd.ms-excel";
       Response.AddHeader("Content-Disposition","attachment;filename=" + FileName);
       GridView1.GridLines = GridLines.Both;
       GridView1.HeaderStyle.Font.Bold = true;
       GridView1.RenderControl(htmltextwrtter);
       Response.Write(strwritter.ToString());
       Response.End();
    }
public override void VerifyRenderingInServerForm(Control control)
   {
       //required to avoid the run time error "
       //Control 'GridView1' of type 'Grid View' must be placed inside a form tag with runat=server."
   }