locked
Export To Excel On Button Click I'm Getting This Dialogue Box RRS feed

  • Question

  • User2033107836 posted

    Hello

    This is my code to export data to excel 

     Response.Clear();
            Response.AddHeader("content-disposition", "attachment;filename= Report.xls");
            Response.Charset = "";
            Response.ContentType = "application/vnd.ms-excel";
            System.IO.StringWriter stringWrite = new System.IO.StringWriter();
            System.Web.UI.HtmlTextWriter htmlWrite = new HtmlTextWriter(stringWrite);
            pnlContents.RenderControl(htmlWrite);
            Response.Write(stringWrite.ToString());
            Response.End();
    

    On button click its coming like this dialogue

    Why this dialogue is coming..?

    Sunday, August 13, 2017 8:26 AM

Answers

  • User2103319870 posted

    I'm exporting panel inside repeater control are there then how would be the quer

    You can try with the below code

    HTML

     <asp:Repeater ID="Repeater1" runat="server">
                    <HeaderTemplate>
                        <table>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                            </tr>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblLastName" runat="server" Text='<%#Eval("LastName") %>' />
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
    
    
                <asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="Button1_Click" />

    C#:

    Loading Repeater with data

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
                    string sSQL = "Select top 10 FirstName,LastName FROM DimCustomer";
                    SqlCommand cmd3 = new SqlCommand(sSQL, con);
                    con.Open();
                    cstable = new DataTable();
                    cstable.Load(cmd3.ExecuteReader());
    
                    Repeater1.DataSource = cstable;
                    Repeater1.DataBind();
    
    
                }
            }

    Code to Export Repeater Data to Excel

    protected void Button1_Click(object sender, EventArgs e)
            {
                //Get the Repeater data to datatable
                DataTable data = new DataTable("RepeaterData");
                //Add columns from Repeater to Datatable
                data.Columns.Add(new DataColumn("FirstName", typeof(String)));
                data.Columns.Add(new DataColumn("LastName", typeof(String)));
    
                //Loop through each items in repeater and add to datatable
                foreach (RepeaterItem item in Repeater1.Items)
                {
                    //Adding new row
                    DataRow dr = data.NewRow();
                    //Find  FirstName label control
                    Label lblFirstName = (Label)item.FindControl("lblFirstName");
                    //Add the value to datatable
                    dr["FirstName"] = lblFirstName.Text;
                    Label lblLastName = (Label)item.FindControl("lblLastName");
                    dr["LastName"] = lblLastName.Text;
                    data.Rows.Add(dr);
    
                }
    
                //Export Repeater data to excel using OpenXML
                XLWorkbook wb = new XLWorkbook();
                var ws = wb.Worksheets.Add("RepeaterData");
                ws.Cell(2, 1).InsertTable(data);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", "RepeaterData"));
    
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    wb.SaveAs(memoryStream);
                    memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                    memoryStream.Close();
                }
    
                HttpContext.Current.Response.End();
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 14, 2017 1:32 AM

All replies

  • User2103319870 posted

    Why this dialogue is coming..?

    This is an excel issue and not from your code. You can find more details of this exception from this link

    From MSDN : The warning message is a user-notification function that was added to Excel 2007. The warning message can help prevent unexpected problems that might occur because of possible incompatibility between the actual content of the file and the file name extension.

    As a solution you could use a Third Party library called Closed XML to generate the Excel file. 

    Please try with below implementation

     protected void Button2_Click(object sender, EventArgs e)
            {
                //Get the Gridview data to datatable
                DataTable data = new DataTable("GridView_Data");
                for (int i = 0; i < GridView1.Columns.Count; i++)
                {
                    data.Columns.Add(GridView1.Columns[i].HeaderText);
                }
    
                foreach (GridViewRow row in GridView1.Rows)
                {
                    data.Rows.Add();
                    for (int i = 0; i < row.Cells.Count; i++)
                    {
                        data.Rows[data.Rows.Count - 1][i] = row.Cells[i].Text;
                    }
                }
    
                //Export gridview data to excel using OpenXML
                XLWorkbook wb = new XLWorkbook();
                var ws = wb.Worksheets.Add("GridviewData");
                ws.Cell(2, 1).InsertTable(data);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", "GridviewData"));
    
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    wb.SaveAs(memoryStream);
                    memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                    memoryStream.Close();
                }
    
                HttpContext.Current.Response.End();
    
            }

    Reference URL

    Sunday, August 13, 2017 1:14 PM
  • User2103319870 posted

    You can install Closed XML in your solution using Nugget  from here : https://www.nuget.org/packages/ClosedXML/

    Sunday, August 13, 2017 1:16 PM
  • User2033107836 posted
    I'm not exporting gridview.. I'm exporting panel inside repeater control are there then how would be the quer
    Sunday, August 13, 2017 5:37 PM
  • User2103319870 posted

    I'm exporting panel inside repeater control are there then how would be the quer

    You can try with the below code

    HTML

     <asp:Repeater ID="Repeater1" runat="server">
                    <HeaderTemplate>
                        <table>
                            <tr>
                                <th>First Name</th>
                                <th>Last Name</th>
                            </tr>
                    </HeaderTemplate>
                    <ItemTemplate>
                        <tr>
                            <td>
                                <asp:Label ID="lblFirstName" runat="server" Text='<%#Eval("FirstName") %>' />
                            </td>
                            <td>
                                <asp:Label ID="lblLastName" runat="server" Text='<%#Eval("LastName") %>' />
                            </td>
                        </tr>
                    </ItemTemplate>
                    <FooterTemplate>
                        </table>
                    </FooterTemplate>
                </asp:Repeater>
    
    
                <asp:Button ID="Button1" runat="server" Text="Export to Excel" OnClick="Button1_Click" />

    C#:

    Loading Repeater with data

    protected void Page_Load(object sender, EventArgs e)
            {
                if (!IsPostBack)
                {
                    SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["AdventureWorksConnectionString"].ToString());
                    string sSQL = "Select top 10 FirstName,LastName FROM DimCustomer";
                    SqlCommand cmd3 = new SqlCommand(sSQL, con);
                    con.Open();
                    cstable = new DataTable();
                    cstable.Load(cmd3.ExecuteReader());
    
                    Repeater1.DataSource = cstable;
                    Repeater1.DataBind();
    
    
                }
            }

    Code to Export Repeater Data to Excel

    protected void Button1_Click(object sender, EventArgs e)
            {
                //Get the Repeater data to datatable
                DataTable data = new DataTable("RepeaterData");
                //Add columns from Repeater to Datatable
                data.Columns.Add(new DataColumn("FirstName", typeof(String)));
                data.Columns.Add(new DataColumn("LastName", typeof(String)));
    
                //Loop through each items in repeater and add to datatable
                foreach (RepeaterItem item in Repeater1.Items)
                {
                    //Adding new row
                    DataRow dr = data.NewRow();
                    //Find  FirstName label control
                    Label lblFirstName = (Label)item.FindControl("lblFirstName");
                    //Add the value to datatable
                    dr["FirstName"] = lblFirstName.Text;
                    Label lblLastName = (Label)item.FindControl("lblLastName");
                    dr["LastName"] = lblLastName.Text;
                    data.Rows.Add(dr);
    
                }
    
                //Export Repeater data to excel using OpenXML
                XLWorkbook wb = new XLWorkbook();
                var ws = wb.Worksheets.Add("RepeaterData");
                ws.Cell(2, 1).InsertTable(data);
                HttpContext.Current.Response.Clear();
                HttpContext.Current.Response.ContentType = "application/vnd.openxmlformats-officedocument.spreadsheetml.sheet";
                HttpContext.Current.Response.AddHeader("content-disposition", String.Format(@"attachment;filename={0}.xlsx", "RepeaterData"));
    
                using (MemoryStream memoryStream = new MemoryStream())
                {
                    wb.SaveAs(memoryStream);
                    memoryStream.WriteTo(HttpContext.Current.Response.OutputStream);
                    memoryStream.Close();
                }
    
                HttpContext.Current.Response.End();
            }

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Monday, August 14, 2017 1:32 AM