how to save data from database or DataTable To Excel File without Response in .net 2.0

Question

I have an old website developed in asp.net 2.0 and C#, I want to save data from database or DataTable To Excel File.

I searched a lot in the internet but ONLY found solutions using the HttpResponce "Responce.Write" but this solution will not work if the file is big so I want a solution to save the excel file physically on the hard drive.

Answer 1

i found the solution if i have no Excel installed on the machine and decided to share it with public

Solution 1 (if you need to save the excel file physically on the hard disk)

you will need a gridview to put the data in it first without any css or styles just get the selected data from database with the format you need on excel file

<!-- GridView to bind data from DatasSet-->
<asp:GridView ID="GridView2" runat="server" Visible="False"></asp:GridView>
<!-- HyperLink to put the link of the saved file -->
<asp:HyperLink ID="HyperLink1" runat="server" Visible="False">HyperLink</asp:HyperLink>

code behind

    private void ConvertDataSetToExcelFile(DataSet ds)
    {
        GridView2.DataSource = ds.Tables[0];
        GridView2.DataBind();
        string FileName = DateTime.Now.Ticks.ToString() + ".xls";
        string FilePath = Server.MapPath("~/upload/excel/");

        GridView2.Visible = true;
        System.IO.StringWriter sw = new System.IO.StringWriter();
        System.Web.UI.HtmlTextWriter htw = new System.Web.UI.HtmlTextWriter(sw);
        GridView2.RenderControl(htw);
        string renderedGridView = sw.ToString();
        System.IO.File.WriteAllText(FilePath + FileName, renderedGridView);
        GridView2.Visible = false;

        HyperLink1.Visible = true;
        HyperLink1.NavigateUrl = "~/upload/excel/" + FileName;
        HyperLink1.Text = "Download File";
    }

Just Note you need to make the GridView visible true first to render it to file if it is visible false then nothing to render then you can make it false again as it is used only to hold data then render it to file

Solution 2 (if you need to just create the file on the fly then just download it to client directly)

Just note that i found these functions on my search but i don't try then as i need the first solution but only wanted to share the other solution for public as well

public static void ExportToExcel(ref GridView gv, string _filename, string cmplbl)
{

    string style = @"<style>.text{mso-number-format:\@;text-align:center;};.Nums{mso-number-format:_(* #,###.00_);};.unwrap{wrap:false}</style>";
    //string style = @"<style> .text { mso-number-format:\@; } </style> ";

    // "<style>.text{mso-number-format:\@;text-align:center;};.Nums{mso-number-format:_(* #,##0.00_);};.unwrap{wrap:false}</style>"

    string attachment = "attachment; filename=" + _filename;
    HttpContext.Current.Response.ClearContent();

    HttpContext.Current.Response.AddHeader("content-disposition", attachment);
    // If you want the option to open the Excel file without saving then;
    // comment out the line below
    // Response.Cache.SetCacheability(HttpCacheability.NoCache);

    HttpContext.Current.Response.ContentType = "application/ms-excel";
    //Response.AddHeader("Content-Disposition", "attachment;Filename=Orders.xls");

    //Response.Write ("<meta http-equiv=""Content-Type"" content=""text/html; charset=Utf-8"">")

    StringWriter sw = new StringWriter(); HtmlTextWriter htw = new HtmlTextWriter(sw);
    gv.RenderControl(htw);
    StringBuilder name = new StringBuilder();
    name.Append(cmplbl);
    HttpContext.Current.Response.Write(style);
    HttpContext.Current.Response.Write(name.Append(sw.ToString()));
    //HttpContext.Current.Response.Write(sw.ToString());

    HttpContext.Current.Response.End(); 

    //HttpContext.Current.Response.WriteFile(_filename);
    //Response.ContentType = "application/vnd.ms-excel"
    //Response.AddHeader("Content-Disposition", "attachment;Filename=Orders.xls")
    //Response.Write ("<meta http-equiv=""Content-Type"" content=""text/html; charset=Utf-8"">")
    //dbGrid_Orders.RenderControl(hw)
    //Response.Write(tw.ToString())
    //Response.End()
}

private void ExportGridView(GridView gvFiles, string filePath, string fileName)
{
    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(filePath + fileName, renderedGridView);

    //Response.Clear(); 
    //Response.ContentType = "application/octect-stream";
    //Response.AppendHeader("content-disposition", "filename=" + fileName);
    //Response.TransmitFile(filePath + fileName); 
    //Response.End();

    System.Web.HttpResponse response = System.Web.HttpContext.Current.Response;
    response.ClearContent();
    response.Clear();
    response.ContentType = "text/plain";
    response.AddHeader("Content-Disposition",
                       "attachment; filename=" + fileName + ";");
    response.TransmitFile(filePath + fileName);
    response.Flush();
    response.End();
}