Export Gridview Data & Save in Excel File on the Server

Question

User-519136805 posted

Hi all,

I have code to export the grid to excel . i have taken it from

http://mattberseth.com/blog/2007/04/export_gridview_to_excel_1.html

now i want to save excel file directly without asking user to save it or open it at client side.

i want to save it on server folder. how to do that .

any one have any idea regarding this issue.

Answer 1

User1633691049 posted

Hi,

I've made a quick adjustment to GridViewExportUtil:

    /// <summary>
    /// 
    /// </summary>
    /// <param name="fileName"></param>
    /// <param name="gv"></param>
    public static void Export(string fileName, GridView gv)
    {
        HttpContext.Current.Response.Clear();
        HttpContext.Current.Response.AddHeader(
            "content-disposition", string.Format("attachment; filename={0}", fileName));
        HttpContext.Current.Response.ContentType = "application/ms-excel";

        using (StringWriter sw = new StringWriter())
        {
            Export(sw, gv);
            //  render the htmlwriter into the response
            HttpContext.Current.Response.Write(sw.ToString());
            HttpContext.Current.Response.End();
        }
    }

    public static void ExportToFile(string fileName, GridView gv)
    {
        using (StreamWriter streamWriter = new StreamWriter(fileName)) 
        {
            using (StringWriter sw = new StringWriter())
            {
                Export(sw, gv);
                streamWriter.Write(sw.ToString());
            }
        }
    }

    /// <summary>
    /// 
    /// </summary>
    /// <param name="sw"></param>
    public static void Export(StringWriter sw, GridView gv)
    {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a table to contain the grid
                Table table = new Table();

                //  include the gridline settings
                table.GridLines = gv.GridLines;

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);
            }
    }

    /// <summary>
    /// Replace any of the contained controls with literals
    /// </summary>
    /// <param name="control"></param>
    private static void PrepareControlForExport(Control control)
    {
     // code unchanged
    }

To save data to server, in a folder Upload:

GridViewExportUtil.ExportToFile(Server.MapPath("~/Upload/myGrid.xls"), this.GridView1);

This will save the GridView1 data to the server Upload folder as myGrid.xls. ExportToFile requires the physical file name. So you need to call Server.MapPath to translate the relative server path to the server physical path.

I hope this will help.

Cheers,

Florin

Answer 2

User-519136805 posted

hi florinlabou,

Thanks a lot for providing code to me. i have also implemented same code as u posted here but slitly different way & now it is working for me.

//Export Gridview Data to Excel File and Save Excel file to Server Folder Rather than
    //allowing user to Open or Save it.    
    public static void ExportToFolder(string fileName, GridView gv)
    {
        System.Text.StringBuilder sb = new System.Text.StringBuilder();
        using (StringWriter sw = new StringWriter(sb))
        {
            using (HtmlTextWriter htw = new HtmlTextWriter(sw))
            {
                //  Create a form to contain the grid
                Table table = new Table();

                //  add the header row to the table
                if (gv.HeaderRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.HeaderRow);
                    table.Rows.Add(gv.HeaderRow);
                }

                //  add each of the data rows to the table
                foreach (GridViewRow row in gv.Rows)
                {
                    GridViewExportUtil.PrepareControlForExport(row);
                    table.Rows.Add(row);
                }

                //  add the footer row to the table
                if (gv.FooterRow != null)
                {
                    GridViewExportUtil.PrepareControlForExport(gv.FooterRow);
                    table.Rows.Add(gv.FooterRow);
                }

                //  render the table into the htmlwriter
                table.RenderControl(htw);

                //Create file
                System.IO.TextWriter w = new System.IO.StreamWriter(HttpContext.Current.Server.MapPath("~") + "\\" + fileName);
                w.Write(sb.ToString());
                w.Flush();
                w.Close();

            }
        }
    }