Export Grid view to excel and save excel file to folder

Question

I want to save excel file which export data of grid view. I have written code to export gridview data to excel but I don't know how to save exported file.

Following is my code to export gridview into excel :

Response.Clear();
Response.Buffer = true;
Response.ContentType = "application/vnd.ms-excel";
Response.AddHeader("content-disposition", "attachment;filename=MyFiles.xls");
Response.Charset = "";
this.EnableViewState = false;
System.IO.StringWriter sw = new System.IO.StringWriter();
System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
gvFiles.RenderControl(htw);
Response.Write(sw.ToString());
Response.End();

You know that you aren't exporting an excel file but a html table? Excel can interpret it, anyway it's not a real excel file. Have a look at [EPPLus](http://epplus.codeplex.com/releases/view/42439). — Tim Schmelter, May 02 '12 at 10:04
Like Tim says use EPPlus - it's a library that will generate actual .xlsx files for you and then you can download these instead. I've used it for a budget monitoring app and it's brilliant. — markp3rry, May 02 '12 at 10:13
Almost all answer here write a (HtmlTextWriter) string or have Interop code. DO NOT USE EITHER. This will cause you problems later on with DateTime and Decimal formatting. Also Excel will give a warning because you are not generating a "real" Excel file but a HTML page with an .xls extension. Start using a specialized library for creating Excel files, like [EPPlus](https://github.com/JanKallman/EPPlus). [Example here](https://stackoverflow.com/a/47293207/5836671) and [here](https://stackoverflow.com/a/39513057/5836671). — VDWWD, Dec 21 '18 at 09:25

score 11 · Accepted Answer · edited Nov 02 '13 at 13:01

11

You can do this:

private void ExportGridView()
{
    System.IO.StringWriter sw = new System.IO.StringWriter();
    System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);

    // Render grid view control.
    gvFiles.RenderControl(htw);

    // Write the rendered content to a file.
    string renderedGridView = sw.ToString();
    System.IO.File.WriteAllText(@"C:\Path\On\Server\ExportedFile.xlsx", renderedGridView);
}

edited Nov 02 '13 at 13:01

dvdmn

6,456
7
44
52

answered May 02 '12 at 11:11

npclaudiu

2,401
1
18
19

1

giving me error Control 'gvFiles' of type 'GridView' must be placed inside a form tag with runat=server. :( – Neo Mar 01 '13 at 13:07
1

got the ans public override void VerifyRenderingInServerForm(Control control) { } – Neo Mar 01 '13 at 13:12

score 2 · Answer 2 · answered May 02 '12 at 18:34

this may help you//

protected void exporttoexcel_Click(object sender, EventArgs e)
{
    Response.Clear();

    Response.AddHeader("content-disposition", "attachment;filename=" attachment" + ".xls");

    Response.Charset = "";

    // If you want the option to open the Excel file without saving than

    // comment out the line below

    // Response.Cache.SetCacheability(HttpCacheability.NoCache);

    Response.ContentType = "application/vnd.xls";

    System.IO.StringWriter stringWrite = new System.IO.StringWriter();

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

    GridView1.RenderControl(htmlWrite);

    Response.Write(stringWrite.ToString());

    Response.End();

}
public override void VerifyRenderingInServerForm(Control control)
{

    // Confirms that an HtmlForm control is rendered for the
    //specified ASP.NET server control at run time.

}

this helped me work on the complete Export-To-Excel code. am just not so sure about keeping `VerifyRenderingInServerForm(Control control)` empty though. — AceMark, Nov 19 '13 at 00:57

score 1 · Answer 3 · answered Mar 31 '13 at 06:43

public partial class exportgridtoexcel : System.Web.UI.Page
{
    SqlConnection con=new SqlConnection(ConfigurationManager.ConnectionStrings["dbconnection"].ConnectionString.ToString());
    protected void Page_Load(object sender, EventArgs e)
    {
        if (!Page.IsPostBack)
        {
            GetData();
        }
    }
    public void GetData()
    {
        SqlDataAdapter sda = new SqlDataAdapter("select * from EmpData", con);
        DataTable dt = new DataTable();
        sda.Fill(dt);
        GridView1.DataSource = dt;
        GridView1.DataBind();
    }

    protected void btnDownload_Click(object sender, EventArgs e)
    {
        GetData();
        exporttoexcel("Report.xls", GridView1);
        GridView1 = null;
        GridView1.Dispose();



    }
    public void exporttoexcel(string filename,GridView gv)
    {
        Response.ClearContent();
        Response.AddHeader("content-disposition", "attachment;filename=" + filename);
        Response.ContentType = "applicatio/excel";
        StringWriter sw = new StringWriter(); ;
        HtmlTextWriter htm=new HtmlTextWriter(sw);
        gv.RenderControl(htm);
        Response.Write(sw.ToString());
        Response.End();
    }
    public override void VerifyRenderingInServerForm(Control control)
    {

    }
}

}

score 0 · Answer 4 · edited May 23 '17 at 11:54

You're asking how to save the exported file... Your code puts the rendered contents of the gridview (HTML) into the Response. In this case your browser (client-side) will receive this response and pops-up a dialog asking where to save it. So your browser will save the file for you.

If you want to save it server side you must not put the rendered gridview into the response. Write it to a file on the local hard disc instead (like the answer above shows).

Remember that, in an environment other than your own development machine (i.e. a production environment), the ASP.NET worker process might not have enough access rights to write to the specified location on the hard disc. Here are a couple of answers that adresses that problem:

ASP.net user account permissions
ASP.NET + Access to the path is denied
System.UnauthorizedAccessException: Access to the path is denied

score 0 · Answer 5 · edited May 23 '17 at 12:03

Folowed the link: C# Excel file OLEDB read HTML IMPORT

Use the Extended Properties=\"HTML Import;HDR=No;IMEX=1 the select * from [tablename], tablename is returned from GetOleDbSchemaTable.

Note: This would not load the normal excel. For that use Extended Properties=\"Excel 8.0;HDR=No;IMEX=1\, where table name will be with $ sign.

Check sample code: Can't read excel file after creating it using File.WriteAllText() function

score 0 · Answer 6 · answered Feb 27 '14 at 06:59

One another solution for this problem solved here : http://codentq.wordpress.com/2014/02/13/write-excel-file-from-dataset/

you have to get your grid view data in data set by method mention on this article : http://codentq.wordpress.com/2014/02/13/get-data-from-grid-to-datatable-or-dataset/

score -1 · Answer 7 · answered Jun 25 '17 at 19:36

first add EPPLUS reference library into application and add using OfficeOpenXml;

//business object class

class bocls {

    string name;

    public string NAME
    {
        get { return name; }
        set { name = value; }
    }
    string id;

    public string ID
    {
        get { return id; }
        set { id = value; }
    }



    public bocls() { }
    public bocls(string name, string id)
    {
        this.name = name;
        this.id = id;         

    }

//in export button click event

protected void lbtnExport_Click(object sender, EventArgs e) {

            List<bocls> list6 =  new List<bocls>();
           //copy the grid view values into list
            list6 = (from row in dataGridView1.Rows.Cast<DataGridViewRow>()
            from cell in row.Cells.Cast<DataGridViewCell>()
            select new 
            {
                //project into your new class from the row and cell vars.
            }).ToList();
    }
            ExcelPackage excel = new ExcelPackage();
            var workSheet = excel.Workbook.Worksheets.Add("Products");
            var totalCols = GridView1.Rows[0].Cells.Count;
            var totalRows = GridView1.Rows.Count;
            var headerRow = GridView1.HeaderRow;
            for (var i = 1; i <= totalCols; i++)
            {
                workSheet.Cells[1, i].Value = headerRow.Cells[i - 1].Text;
            }
            for (var j = 1; j <= totalRows; j++)
            {
                for (var i = 1; i <= totalCols; i++)
                {
                    var item = list6.ElementAt(j - 1);

                    workSheet.Column(1).Width = 13;
                    workSheet.Column(2).Width = 10;

                    workSheet.Cells[j + 1, i].Style.WrapText = true;

                    if (headerRow.Cells[i - 1].Text == "ID")
                        workSheet.Cells[j + 1, i].Value = item.GetType().GetProperty("id").GetValue(item, null);
                    else if (headerRow.Cells[i - 1].Text == "NAME")
                        workSheet.Cells[j + 1, i].Value = item.GetType().GetProperty("name").GetValue(item, null);

                    workSheet.Cells[j + 1, i].Value = workSheet.Cells[j + 1, i].Value.ToString().Replace("<br/>", "");
                }
            }
            using (var memoryStream = new MemoryStream())
            {

                Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                string filename = Guid.NewGuid().ToString() + ".xlsx";
                Response.AddHeader("content-disposition", "attachment;  filename=" + filename);
                excel.SaveAs(memoryStream);   
                //add your destination folder
                FileStream fileStream = new FileStream(@"C:\Users\karthi\Downloads\New folder\" + filename, FileMode.Create,FileAccess.Write,FileShare.Write);
                memoryStream.WriteTo(fileStream);
                fileStream.Close();
                memoryStream.WriteTo(Response.OutputStream);
                memoryStream.Close();
                memoryStream.WriteTo(Response.OutputStream);
                Response.Flush();
                Response.End();
            }

    }

Export Grid view to excel and save excel file to folder

7 Answers7

Linked